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.
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
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.
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