Connecting Remotely to a MySQL Database

I want to be able to remotely access the MySQL database server on my old linux-based T42 laptop. To do this, I followed these steps:

Step 1:

The MySQL server is usually listening for external requests on port 3306 by default. Check it with:

netstat -tlpn | grep mysq

This should get a result that looks something like this:

tcp  0  0  127.0.0.1:3306  0.0.0.0:*  LISTEN 2707/mysqld

Step 2:

Log in to your router (typing 192.168.0.1 into a browser will usually get you to the login screen) then set Port 3306 to forward to the IP address for the server computer.

Step 3:

Find your MySQL config file (my.cnf is usually in /etc/mysql) and comment out the following line by putting a hash character in front of it as shown:

# bind-address = 127.0.0.1

Save the file, and restart the mysql server.

sudo service mysql reload

Step 4:

NOTE: This is where I pick up if I want to connect to a MySQL database on one of the DigitalOcean servers, since steps 1 to 3 will already be taken care of on their end.

Log in to mysql as root…

sudo mysql -u root 

Now select the ‘mysql’ database, then create a new user and grant privileges as follows:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'some_password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'
    ->     WITH GRANT OPTION;

Step 5:

Now open your client software (i.e. HeidiSQL) and set up access to the MySQL database using the same username/some_password combo you just created.

That’s all there is to it:-)