ySQL remote access is disabled by default for security reasons. However, there may be times where it is necessary to enable it to provide access from a remote location or a web server. This article will describe the process in detail, and is intended for users familiar with SSH and MySQL.
Connect to the MySQL Server
MySQL is most commonly entered through SSH. Once the connection to the server has been accomplished, a second command provides access to the MySQL server. The procedure to make the connection is as follows:
Connect to the server via SSH. Then connect to MySQL. This involves the insertion of the username and password given specifically for MySQL use. The command to start the connection is:
| mysql -u root -p |
Enter the MySQL password when prompted.
Once connected, you will be given the prompt mysql>. From the mysql prompt you are free to execute the various commands of the MySQL language. You must connect to the database before you are able to modify it. The following is an example connection to a database called mydbname:
| mysql> use mydbname;
Result: Database changed Mysql> |
Note that the command was followed by a semi-colon (;). Almost all commands in MySQL are followed by a semi-colon. Additional commands can be viewed simply by typing help at the command line.
Enable Remote Access to MySQL from an External IP
The following command will allow access to the MySQL database from a remote IP address:
| mysql> GRANT ALL PRIVILEGES ON *.* TO user_name@HOST IDENTIFIED BY ‘pass_word’; |
Replace the username and password in this command with the relevant data.
Enable Remote Access to MySQL from an External Dynamic IP
If a user is on a dynamic IP address they can be granted privileges when connecting from another host. To do this, use‘%’ for the HOST portion of the command. This is not recommended because it is less secure since it allows connections from anywhere.
| GRANT ALL PRIVILEGES ON *.* TO user_name@’%’ IDENTIFIED BY ‘pass_word’; |
Replace the username and password in this command with the relevant data.
Flush the Privileges
After changing grant table data, reload the privileges:
| mysql> flush privileges; |
Add a new user that is tied to your remote host, and test it by trying to connect remotely with that username and password.
Use phpMyAdmin to Add a User
- You can add a user using phpMyAdmin instead of the MySQL command prompts. To add a user using phpMyAdmin:
- Go to http://domainname/mysqladmin/ (replace domainname with your domain name).
- Login using the MySQL User ID and Password.
- Select databases from the drop down on the left side.
- Select Privileges.
- Select Add new User.
- Enter the user name and password into the fields.
- Select the privileges you wish to give the user under data and structure.
- Check GRANT and click the Go button.
Add a user with MySQL commands
From the MySQL prompt enter the following command. Edit the variables ‘$customers_IP‘, ‘$userid‘, ‘$encrypted_password‘ in the below MySQL commands, and restart MySQL.
| INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`) VALUES (‘$customers_IP‘, ‘$userid‘, ‘$encrypted_password‘, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, ‘0’, ‘0’, ‘0’); |
The command below is the same as above, except it allows for any IP address to connect by changing the Host entry to %.. This is not recommended because it is less secure since it allows connections from anywhere.
| INSERT INTO `user` (`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`) VALUES (‘%’, ‘$userid‘, ‘$encrypted_password‘, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ‘Y’, ”, ”, ”, ”, ‘0’, ‘0’, ‘0’); |
For more information:
http://dev.mysql.com/doc/refman/5.1/en/connection-access.html
Disable Remote Connections for VPSv3/MPS v3/Linux Servers for MySQL 5.x
Payment industry policy forbids exposing databases containing cardholder data directly to the Internet. Previously, the MySQL 5 default configuration file (my.cnf) allowed remote connections to its databases. PCI Compliance recommends that MySQL remote connections (which can potentially expose cardholder information) be disabled.
The default MySQL 5.x configuration file (/etc/my.cnf) for new accounts (created in June 2010 or later) will be modified to include the following line to disable remote connections:
| bind-address=127.0.0.1 |
Existing accounts can make the change manually by doing the following:
Connect to your server through SSH. Edit the /etc/my.cnf file to include the following line:
| bind-address=127.0.0.1 |
Restart MySQL by executing the following command:
| mysqladmin -u root -p shutdown |
Start the server using the following command:
v3:
| /usr/local/etc/rc.d/./mysql-server restart |
Linux:
| /etc/rc.d/init.d/./mysql start |