VERIO.COM CHAT CONTACT SUPPORT

How to enable/disable MySQL remote access

MySQL 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

  1. You can add a user using phpMyAdmin instead of the MySQL command prompts.  To add a user using phpMyAdmin:
  2. Go to http://domainname/mysqladmin/  (replace domainname with your domain name).
  3. Login using the MySQL User ID and Password.
  4. Select databases from the drop down on the left side.
  5. Select Privileges.
  6. Select Add new User.
  7. Enter the user name and password into the fields.
  8. Select the privileges you wish to give the user under data and structure.
  9. 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

 




Article Details

Last Updated
25th of January, 2013

Would you like to...

Print this page Print this page

Email this page Email this page

Post a comment Post a comment

Subscribe me

Add to favorites Add to favorites

Remove Highlighting Remove Highlighting

Quick Edit

Export to PDF

User Opinions (115 votes)

97% thumbs up 2% thumbs down

How would you rate this answer?



Thank you for rating this answer.

Related Articles

Attachments

No attachments were found.

Post a comment

To post a comment for this article, simply complete the form below. Fields marked with an asterisk are required.
   Name:
   Email:
* Comment:
* Enter the code below:
 

Continue