Wednesday, February 22, 2012

Setup Remote Access to Local MySQL Server

Do Following:
  1. open /etc/my.cnf for edit
    If my.cnf does not exist, copy a sample from /usr/local/mysql/support-files.
    >sudo cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf 
  2. In my.cnf file
    Comment out skip-networking
    If the server has dedicated IP, Add
    bind-address = server.ip.address //i.e. 34.23.115.234
    If the server is connected within the local network, this line is not required
  3. Restart MySQL server
    >sudo /usr/local/mysql/support-files/mysql.server restart
  4. Grant remote access to user and remote IP
    >mysql -u root -p
    >create database mydb
    >grant all on mydb.* to leo@11.22.33.44 identified by 'password'
  5. Now you can connect remotely to mydb from 11.22.33.44 using 'leo'