How to remote access mysql via ssh

You need to let mysql listening on the whole Internet (or the IP address you want to restrict to). By default mysql only allows inbound traffic from localhost.

To configure that, first you need to locate the configuration file that mysql takes

mysql --help --verbose | more

# You will find a paragraph like:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

For example, `/etc/mysql/my.cnf` contains `bind_address: 127.0.0.1`. Then we need to change it to `bind_address: 0.0.0.0`.

 

Then, for example in mysql, you have a user name `root`. You should go into mysql shell and grant the user `root` with previlige to be used on your server machine’s Internet IP address:

# substitute 1.2.3.4 by the server machine's Internet IP address
GRANT ALL PRIVILEGES ON *.* TO 'root'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

Reboot mysql to make sure everything goes effective:

sudo /etc/init.d/mysqld restart

 

Now, ssh tunnel on the machine you are using:

#3306 is default mysql port

# Explanation can be found here: http://www.revsys.com/writings/quicktips/ssh-tunnel.html

ssh -f -l <usr_name> -L 3306:<server_ip_addr>:3306 <server_ip_addr> -N

 

Finally, connect to mysql shell on the machine you are using:

mysql -u root -p -h 127.0.0.1