Access WordPress MySQL database remotely

Question

I want to access a MySql database on a foreign host. I have permission from the owner, but they are clueless with regards to technology (and it seems, so am I).

I went through the WordPress codebase and may have found the database credentials in a file called wp-config.php.default. Other places look improbable.

define('DB_NAME', '[redacted]');
define('DB_USER', 'db_user');
define('DB_PASSWORD', '[redacted]');
define('DB_HOST', '[redacted]');

Attempting to connect via command line fails.

$ mysql --host='[redacted]' --user='db_user' --password='[redacted]' '[redacted]'
ERROR 2003 (HY000): Can't connect to MySQL server on '[redacted]' (60)

Do I have the wrong credentials? If so, how can I find them? There's no evident database settings section in the super admin interface.

Is my IP Address blocked? If so, how can I configure WordPress to accept it? Most of the examples online have already achieved an Sql prompt.

Answer 1

You must grant access to a user from a remote host on the mysql server.

This tutorial will walk you through setting up a user on your MySQL server to connect remotely.

The following items are assumed:

You have access to login as the 'root' MySQL user

Contents

1 Getting your IP address
2 Granting Access
3 Testing Remotely
4 Notes

Getting your IP address

You will need to know what the IP address you are connecting from. To find this you can go to one of the following sites:

http://www.ipchicken.com
http://www.whatismyip.com

Granting Access

Granting access to a user from a remote host is fairly simple and can be accomplished from just a few steps. First you will need to login to your MySQL server as the root user. You can do this by typing the following command:

mysql -u root -p

This will prompt you for your MySQL root password.

Once you are logged into MySQL you need to issue the GRANT command that will enable access for your remote user. In this example we will be creating a brand new user (fooUser) that will have full access to the fooDatabase database.

Keep in mind that this statement is not complete and will need some items changed. Please change 1.2.3.4 to the IP address that we obtained above. You will also need to change my_password with the password that you would like to use for fooUser.

mysql> GRANT ALL ON fooDatabase.* TO fooUser@'1.2.3.4' IDENTIFIED BY 'my_password';

This statement will grant ALL permissions to the newly created user fooUser with a password of 'my_password' when they connect from the IP address 1.2.3.4.

Testing Remotely

Now you can test your connection remotely. You can access your MySQL server from another Linux server:

mysql -u fooUser -p -h 44.55.66.77
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> _

Note that the IP of our MySQL server is 44.55.66.77 in this example.

Notes

There are a few things to note when setting up these remote users:

When setting up users a local user is not the same thing as a remote user. For instance fooUser@localhost is not the same as [email protected]. You will have to duplicate permissions if you want them to have the same permissions.

Granting ALL permissions is not advised. Using GRANT SELECT,INSERT,UPDATE,DELETE is a wise alternative for a normal user.

If you would like to grant only to a specific table you can use database.table instead of database.*. In respect to our example above you could put fooDatabase.fooTable.

If you are using iptables you will need to make an entry in your firewall for TCP port 3306. When creating your firewall rule you can simply use the name 'mysql' for the port number.

Source: http://www.rackspace.com/knowledge_center/article/mysql-connect-to-your-database-remotely

Answer 2

Try this

mysql -h(ip of the mysql server) -u(username)-p(Password)

hope this helps!

Answer 3

Not many web hosts open up MySQL inbound ports, so you got to be sure a connection to the server is possible. I'm guessing you have a web instance running php, so you could also upload phpMyAdmin (which is basically MySQL Administrator Software) to their server, configure it with your DB credentials and access it through a specific URL.

There are many ways to install phpMyAdmin, some Linux Distributions can install it through a simple command. Check if it's not installed in the server you are trying to access.