How to setup remote access to MySQL database

I had to do this today, so I thought I’d post a quick how to while it’s still fresh in my mind.

Tell MySQL to allow remote connections

Login to Plesk, or cPanel and find out the name of your database and the database username/password. You will need this later.

SSH into your server. If you don’t have root access to your server you may have to get your system administrator to do this part for you. There might be another way to do this with GoDaddy or ToCows, I’m not sure. I’m really just speaking to managed/unmanaged server admins here.

Open Terminal and login through SSH.

ssh root@192.168.0.1

replace 192.168.0.1 with the address of your server

Enter your password when prompted. (Note: this is your root SSH password)

Update your MySQL config file.

cd /etc
vim my.cnf

Comment out “skip-networking” if it exists. This line would cause MySQL to ignore all outside connections.

Add “port = 3306”

your final my.cnf file should look something like this:

[mysqld]
#user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#old_passwords=1
port = 3306
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
symbolic-links=0
# skip-networking

Now you need to open up port 3306 in your firewall.

Punch a hole in your firewall to allow external MySQL requests

If you are using iptables, you can edit the config file, or you can use your control panel to open port 3306. Here is how I do this with iptables.

cd /etc/sysconfig/iptables.d
vim 90psa

You need to add this line

-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

restart iptables

/etc/init.d/iptables restart

Update the MySQL user and give remote privileges to that user for your chosen database

Now you need to allow remote access for your MYSQL user. You can do this through phpMyAdmin, but the mysql command prompt makes it easier.

mysql -uadmin -p

enter your password when prompted

GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

Replace “database_name” with the name of your database
Replace “user” with the username for your database
“%” is used to represent any remote ip Address. If you have a static ip address for your remote machine I suggest using that.
Replace “password” with the password for your database user

If using WordPress, update the MySQL password

I found that with WordPress, I had to create a hashed version of my password.

login to the mysql user account for your database.

exit (to exit current mysql session)
mysql -umyuser -p
SET PASSWORD = PASSWORD('sameasbefore');

Use the same password as before. In your wp-config.php file you will use the unhashed version of your password. The brackets in terminal and the PASSWORD make it a hashed key.

FINISHED!

That’s all. You should be done now. Just restart MySQL and you’re good to go.

/etc/init.d/mysqld restart
or 
/etc/init.d/mysql restart
or 
/service mysqld restart
or
mysql restart


Posted

in

, , , ,

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.