Note: These instructions will actually work for most MySql connection, not just WordPress.
When you are developing a WordPress theme, it’s best to work on it offline. Many times you do not want to have to download the entire MySQL database and set it up in Mamp. When that’s the case, you can download the site files and connect to the WordPress database remotely. Establishing the remote connection is easy just follow these steps.
Establishing a Remote Database connection for local WordPress development
mysql> GRANT ALL ON databasename.* TO databasename@109.6.225.165
IDENTIFIED BY ‘QVFF0BwA9rt2dL5N’;
I’m running an Ubuntu 12. with Nginx
First you need to edit your my.cnf file. Go to etc/mysql/my.cnf and look for the
bind address 127.0.0.1
comment out the line so it looks like this
#bind adress 127.0.0.1
$ sudo service mysql restart
go to your mysql database
$ mysql -u [user] -p
After that add a user to access your database such as:
mysql> GRANT ALL ON database_name.* TO user@xx.xxx.xx.xx IDENTIFIED BY ‘your_password’;
Replace xx.xx.xx.xx with your local IP address of your laptop/desktop
or if it is dynamic you can add them either by: ‘191.151.0.%’ as a dynamic C-class. (this is less secure, but will allow you to connect from more locations)
mysql> FLUSH PRIVILEGES;
mysql> exit
$ sudo service mysql restart
You might also have to whitelist your computers IP Address on your server depending on your security. If you have IP Tables and Firewalls, you are going to have
Next Steps:
From here the operations will vary based on your server and local environments.
Change the bind address to the ip address of your database server. If your webserver and database server are the same, this should still work.
From etc/mysql/my.cnf, look for the port number for mysql. Then double check what port is running on the database server by running the following command in terminal.
$ netstat -plutn
MySQL should be listening on 0.0.0.0:3306.
Back in your local install, open up your wp-config.php file and add you remote database ip address and port to the DB_HOST directive.
/** MySQL hostname */
define(‘DB_HOST’, ‘xxx.xxx.xx.xxx:3306’);
That should do the trick!
If you are looking for a professional WordPress Developer in New York City contact DigiMix Web Design NYC. We’re expert WordPress developers, web designers and digital strategist helping our clients achieve success online.