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:
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
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.
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
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;
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:-)