Allow remote connections to your MySQL server - gHacks Tech News

Allow remote connections to your MySQL server

I have covered MySQL in a number of ways so far. You can read how to administer your MySQL database with MySQL Workbench (see my article "Complete database administration package MySQL Workbench"), MySQL Administrator (see my article "Creating a database with MySQL Administrator"), MySQL Navigator (see my article "Manage you MySQL databases with an easy to use GUI"), or PhpMyAdmin (see my article "Install PhpMyAdmin for easy MySQL administration"). Through my LAMP article, "How to install a LAMP server", you learned how to install MySQL. Now, it's time to see how you can extend that MySQL server to remote machines.

Why would you want to do this? A database server is a precious commodity in the world of data. To have that same server pitch other forms of data and other service could quickly become a detriment to your MySQL in both performance and security categories. Because of this it is wise to isolate that MySQL such that it is only used for that service. But to do this you have to configure that server to be able to serve up databases to remote machines. Although not terribly complicated, without knowing where to look and what to do, this task can be seemingly impossible. But after this tutorial, your databases will be accessible to user/machines you configure - and only those user/machines.

Before we begin

You will be glad to know, assuming you have MySQL up and running, there is no installation necessary for this task. You will need access to the MySQL administrators password as well as administrative access so you can edit a MySQL configuration file. Other than that simple necessity (and a terminal window), you are ready to get cracking.

Editing my.conf

Before you actually start editing the /etc/mysql/my.conf file, I would suggest you back up your current file. Do this with the command:

sudo cp /etc/mysql/my.conf /etc/mysql/my.conf.bak

Now that the file is backed up let's open this baby up for editing. When you have the file open you are going to want to look for this line:

bind-address = 127.0.0.1

What the above line does is limit connections to the localhost and only the localhost. You want to comment this line out by changing it to:

#bind-address = 127.0.0.1

Now save that file and restart MySQL with the command:

sudo /etc/init.d/mysql restart

When MySQL has restarted you now need to get to the MySQL command prompt.

The mysql prompt

In order to get to the mysql prompt you will want to issue the command:

mysql -u root -p

Where root is the MySQL administrative user (most like it is root).

You will be prompted for the MySQL administrators password. After you have successfully authenticated you will have a new prompt that looks like:

mysql>

You are now at the MySQL prompt. You only have one command to enter for this to work. You will want to enter this command carefully:

GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY "password";

Where username is the username on the remote machine that will be connecting, address is the IP address of the remote machine, and password is the password that will be used by the remote user.

When that command is issued successfully you should see something like:

Query OK, 0 rows affected (0.00 sec)

As long as you get Query OK, you should be good to go.

Now when you need to connect from a remote machine you will use the IP address of the MySQL server, the username you entered in the MySQL command prompt, and the username will be the username you entered in the MySQL command prompt.

That's it. Your MySQL server is ready to accept remote connections.

Final thoughts

It should go without saying that you will also want to make sure this MySQL server is protected with a good firewall.  For this you can check out any of the iptables articles here on Ghacks. Enjoy that MySQL server!

Summary
Article Name
Allow remote connections to your MySQL server
Description
Find out how you can allow remote connections to your MySQL database.
Author




  • We need your help

    Advertising revenue is falling fast across the Internet, and independently-run sites like Ghacks are hit hardest by it. The advertising model in its current form is coming to an end, and we have to find other ways to continue operating this site.

    We are committed to keeping our content free and independent, which means no paywalls, no sponsored posts, no annoying ad formats (video ads) or subscription fees.

    If you like our content, and would like to help, please consider making a contribution:

    Comments

    1. Saeed said on December 28, 2009 at 7:40 pm
      Reply

      Hi

      Thanks for this but don’t you think it’s much better to do this over SSH

      http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/

      Saeed

    2. Jack Wallen said on December 29, 2009 at 1:15 am
      Reply

      Saeed: Thanks for pointing that out. The only problem with that tutorial is the MySQL Query Browser has now been issued EOL.

      And if you are dealing with a secure LAN, you shouldn’t have to worry about connecting with the way I describe. If, however, you are overly paranoid, using ssh is always a good solution.

    3. Alex said on December 29, 2009 at 10:28 am
      Reply

      Any way to work around the fact that most of us have dynamic IP addresses?

    4. Ste_95 said on December 29, 2009 at 10:44 am
      Reply

      On Windows, where does the “bind-address = 127.0.0.1” is, in what file?

    5. Carolyne said on December 29, 2009 at 3:58 pm
      Reply

      Thanks for this – it was very helpful.

    6. Muluneh Awoke said on June 27, 2012 at 9:59 am
      Reply

      Works like a charm
      Thanks

    7. Martin said on December 27, 2012 at 12:44 pm
      Reply

      This worked nicely.

    8. ana said on March 29, 2013 at 11:40 am
      Reply

      nice tut, keep going

    9. Mark said on February 17, 2014 at 10:06 pm
      Reply

      Awesome, thank you! I’ve been looking for this answer for some time.

    Leave a Reply