Monday, August 20, 2012

How to enable MySQL remote access on Ubuntu Server 12.04

By default MySQL on Ubuntu Server 12.04 can only be accessed from localhost (127.0.0.1). 

If we need to access it from another computer on the network, we must do the following step

Edit MySQL Configuration

$ sudo vim /etc/mysql/my.cnf

#comment out this line (add # in front of the line)
#bind-address           = 127.0.0.1

Restart the server

$ sudo service mysql restart
mysql stop/waiting
mysql start/running, process 13081

Then we need to add user info to the database

$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 77
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all on [database_name].* to [user]@[ip_address] identified by '[password]'


Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye


That's all!

Reference:

http://ubuntuforums.org/showthread.php?t=799386

7 comments:

Remote access said...

Thanks for sharing the information regarding remote access! I was also searching for the valuable post.

Anonymous said...

I had to uncomment at snmpd.conf
agentAddress udp:161,udp6:[::1]:161

to listen on all interfaces

Anonymous said...

Ignore the above post from "anon". It opens up the snmp daemon and will get your box compromised.

Anonymous said...

Thanks very much for this. I've spent the last 3 hours trying to find the problem (didn't comment out the bind line) to realise all the other tutorials were for a different version of Ubuntu.

Anonymous said...

If you still can't connect, then do the telnet test from outside the db server, as shown in the UbuntuForums link on this and you should see goobly gook chars appear. If not, and it just sits there, then you have no connection. You should then go to the console of the db server and run the netstat command as shown in the UbuntuForums link to see if it's listening.

In my case, the db server was listening properly to the outside world, telnet from my connecting server was failing, and I couldn't ping the db server from the connecting server. Turned out -- RackSpace had to adjust a router config that was messed up between the two servers.

Croose Hackle said...

ARINET DBA Services is a Chicago, IL based organization established in 2013. Uniting more than 100+ years of combined involvement in giving quality Oracle database Support administrations to American organizations, we've given the diverse options about remote dba masters, remote dba organizations, remote dba reinforce, remote database, prophet remote, dba remote, database association, prophet dba reinforce, Oracle sponsorship and Oracle Consultants.

koi singh said...

thanks for sharing the good information, i got it too much..


จีคลับ
goldenslot