Connect to a MySQL database remotely
Last updated on: 2018-12-19
Authored by: Rackspace Support
This article explains how to set up a user on your MySQL® server in order to connect to a MySQL database remotely.
Note: The article shows you how to connect to a MySQL instance local to a server. For the corresponding steps for Cloud Databases, see Connect to a Cloud Database instance.
In order to perform these steps, you must have local server access to log in as
root MySQL user.
Retrieve your IP address
You need to know the Internet Protocol (IP) address of the computer from which you’re connecting. You can retrieve this information by visiting one of the following sites:
Perform the following steps to grant access to a user from a remote host:
Log in to your MySQL server locally as the
rootuser by using the following command:
# mysql -u root -p
You are prompted for your MySQL root password.
Note: If you gain access to MySQL without entering a password, consider running the
mysql_secure_installationscript, which sets a MySQL root password and updates other settings to increase security. Microsoft SQL Server Managed Services can help you manage your SQL server instances.
GRANTcommand in the following format to enable access for the remote user. Ensure that you change
126.96.36.199to the IP address that you obtained previously, and
my_passwordto the password that you want
mysql> GRANT ALL ON fooDatabase.* TO fooUser@'188.8.131.52' IDENTIFIED BY 'my_password';
This statement grants
ALLpermissions to the new user when the user connects from the specified IP address by using the specified password.
Test the connection remotely
To test the connection remotely, access the MySQL server from another
Linux® server. The following example uses
184.108.40.206 as the IP address
of the MySQL server:
# mysql -u fooUser -p -h 220.127.116.11 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> _
When you set up a remote user, consider the following information:
A local user is different from a remote user. For example,
fooUser@localhostis not the same as
fooUser@18.104.22.168. If you want both users to have the same permissions, you need to duplicate permissions.
We don’t recommend granting
ALLpermissions. For standard users, we recommend granting
To grant access to only a specific table, you can use the
database.tablecommand. For example, in the preceding step, you could use
If you’re using iptables, you need to add an entry to your firewall rule for Transmission Control Protocol (TCP) port 3306. You can use the name
mysqlfor the port number.