How Do I Enable Remote Access To MySQL Database Server?
By default, MySQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home or from web server.
MySQL Remote Access
You need type the following commands which will allow remote connections:
Step # 1: Login over ssh if server is outside your IDC
First, login over ssh to remote MySQL database server
Step # 2: Enable networking
Once connected you need edit the mysql configuration file my.cfg using text editor such as vi.
- If you are using Debian Linux file is located at /etc/mysql/my.cnf location
- If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
- If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf
# vi /etc/my.cnf
Step # 3: Once file opened, locate line that read as follows
[mysqld]
Make sure line skip-networking is commented (or remove line) and add following line
bind-address=YOUR-SERVER-IP
For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:[mysqld]Where,
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 65.55.55.2
# skip-networking
....
..
....
- bind-address : IP address to bind to.
- skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should removed from file or put it in comment state.
Step# 4 Save and Close the file
Restart your mysql service to take change in effect:# /etc/init.d/mysql restart
Step # 5 Grant access to remote IP address
# mysql -u root -p mysqlGrant access to new database
If you want to add new database called foo for user bar and remote IP 202.54.10.20 then you need to type following commands at mysql> prompt:mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';
How Do I Grant access to existing database?
Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database:mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';
Step # 5: Logout of MySQL
Type exit command to logout mysql:mysql> exit
Step # 6: Open port 3306
You need to open port 3306 using iptables or BSD pf firewall.
A sample iptables rule to open Linux iptables firewall
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
OR only allow remote connection from your web server located at 10.5.1.3:
/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT
OR only allow remote connection from your lan subnet 192.168.1.0/24:
/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT
A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)
pass in on $ext_if proto tcp from any to any port 3306
OR allow only access from your web server located at 10.5.1.3:
pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306 flags S/SA synproxy state
Step # 7: Test it
From remote system or your desktop type the command:$ mysql -u webadmin –h 65.55.55.2 –p
Where,
- -u webadmin: webadmin is MySQL username
- -h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
- -p : Prompt for password
You can also use telnet to connect to port 3306 for testing purpose:$ telnet 65.55.55.2 3306
- links for 2006-10-28 at Amy Stephen
- 10.28.06 at 11:25 pm
{ 40 comments… read them below or add one }
-
1 05.04.06 at 4:23 am
-
Could you tell us how to setup proper MySQL client program on remote machine first?
As far as I now, even if we ONLY install MySQL client program on remote machine, it will generate a my.cnf file. Whenever you issue mysql command on remote machine, this file will be consulted and thus, the client will attempt to connect to a non-exist local MySQL sever rather than your remote server. -
-
To be frank you don’t need to setup my.cnf for client configuration. All you need to do is specify remote mysql host with –h option. For example to connect remote mysql server called dbserver.nixcraft.in you need to type command as follows:
$ mysql –u vivek –h dbserver.nixcraft.in -pOR if you with to use MySQL server ip address (192.168.1.101):
$ mysql –u vivek –h 192.168.1.101 -p
-
-
nixcraft said…
To be frank you don’t need to setup my.cnf for client configuration. All you need to do is specify remote mysql host with –h option. For example to connect remote mysql server called dbserver.nixcraft.in you need to type command as follows:
$ mysql –u vivek –h dbserver.nixcraft.in -p
OR if you with to use MySQL server ip address (192.168.1.101):
$ mysql –u vivek –h 192.168.1.101 -p
5/04/2006 11:29 AM+—————————————————-+
This didn’t work at all. BTW, who is “vivek”? Is that your client machine?
Anyhow, the host server keeps telling me that my client computer is not allowed to connect. There must be more to it that I am missing.
+—————————————————–+ -
-
vivek is username and 192.168.1.101 is server IP. You need to setup correct permission using GRANT command (see above for an example).
-
-
I’m having a problem accessing the file:
~
“/etc/my.cnf” [New File][1]+ Stopped vi /etc/my.cnf
[mysql5@serv mysql5]$ vi /etc/my.cnfplease help.
-
-
I’m having a problem accessing the file:
~
“/etc/my.cnf” [New File][1]+ Stopped vi /etc/my.cnf
[mysql5@serv mysql5]$ vi /etc/my.cnfplease help.
-
-
/etc/my.cnf is standard location. But location is depend upon mysql version and Linux distro. Use find command to find out my.cnf. Type the following command as root user:
find / -iname my.cnf
-
-
Before I have this response from
> mysql -h hostname -u username -p
ERROR 2003 (HY000): Can’t connect to MySQL server on (113)After following your solution. I got this response
mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]
-?, –help Display this help and exit.
-I, –help Synonym for -?
–auto-rehash Enable automatic rehashing. One doesn’t need to useI check connectivity using ping host
and got response
icmp_seq=0 ttl=64 time=0.542 ms
meaning i have connectivityIs there something i missed?
-
-
I tried telnet for connectivity
$telnet ipaddr
Trying ipaddr…
telnet: connect to address ipaddr: No route to host
telnet: Unable to connect to remote host: No route to hostdo i have a problem with my ipaddr?
-
10 01.18.07 at 12:01 am
-
thanks, this helped me out.
-
11 03.07.07 at 7:10 am
-
Do not forget to adjust your iptables file (/etc/sysconfig/iptables usually) to allow connections on that port. Typically you will find that TCP connections are enabled on port 22 (ssh) and port 80 (http). Add an entry for port 3306
-
12 04.10.07 at 9:10 pm
-
Using Fedora 6 as host and added port 3306 to
/etc/sysconfig/ip6tables and /etc/sysconfig/iptablesFor the ip6table file the formatted line is;
-A RH-Firewall-1-INPUT -m tcp -p tcp –dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m tcp -p tcp –sport 3306 -j ACCEPT -
13 04.11.07 at 2:23 pm
-
CptBeluga,
Yup, you need to open port 3306 for communication.
Appreciate your post!
-
14 07.24.07 at 12:19 am
-
I was able to access mysql after changing these tables but when I restarted the computer the files were changed back. There must be another way to do this without manually changing them.
-
15 08.17.07 at 3:55 am
-
mysql> update user set Host=’202.54.10.20′ where user=’webadmin’;
I always receiving error when performing above command. Could u advise
-
-
I also cannot find my iptables file on redhat enterprise
-
-
you know what, now am not be able to login as root on localhost. How can I revoke those update above
-
18 08.26.07 at 12:07 am
-
Bless your soul for writing this. I was endlessly modifying permissions in mysql until I read your post and realized I had to change the bind-address.
– George
-
19 09.04.07 at 2:52 am
-
Hello,
Simply great,expect same again.
-
20 09.07.07 at 4:03 pm
-
This is an awesome article. The only thing I wish it covered is how to name your server (so when you connect you could do it through `myserver.mydomain.com` and whether you can specify a range of allowed remote IPs. But still, this is a very good write up and one that I learned a lot from.
-
21 10.12.07 at 3:49 am
-
Look, what if a want that all my lan have acces to the db?
-
22 01.26.08 at 12:48 am
-
Hey i am having a problem with Bad Handshake!!
Can any body help me please!! -
23 02.17.08 at 5:11 am
-
Nice addition. Simply great,expect same again.it will help me a lot.
-
24 03.26.08 at 7:36 am
-
How can i access mysql database running on windows pc from linux server, on both machine i am using mysql 5 and perl 5.8.8 , ip of windows machine ie. 192.168.0.50 and linux server has 192.168.0.10 on single LAN.
-
25 04.16.08 at 6:32 am
-
heloo admin ,
i want to find the ip address of the my sqlserver in fedoro 5 how to find it can u guide me . -
26 04.30.08 at 2:19 am
-
I have 2 mysql servers #1 and #2 in a LAN. I want to remotely access either 1 of the 2 servers from box #3. How do I specify that server #2 is to respond and not server #1 to my request. thanks a lot.
-
-
Is there a way to make a mysql server accessible both locally AND remotely?
When I change bind-address to some ip address the ubuntu LAMP server will fail to start mysqld. When I change the bind-address to 127.0.0.1, I can’t access it remotely. -
28 05.31.08 at 12:00 pm
-
Nice tutorial up there !
Keep up the good work
-
29 06.05.08 at 6:14 pm
-
Hi there,thankyou for this article..
I made all steps and when I try to login in the WEB application that i’m setting up, there is an error:
MySQL error, Connection Lost during query
Does this mean that conection was done, but something kicked me out ?
I think yes, and I will be very glad to know if you have a clue of what it is causing this.
Thank you
-
30 06.23.08 at 1:02 pm
-
The MySQL manual has some information on this which you might find useful. It goes about it a slightly different way which some people might find easier.
-
31 07.04.08 at 7:18 pm
-
Hi,
what if I could not find the file my.cnf on my server?…..I am actually using a web hosting server.
Regards,
KK
-
32 07.10.08 at 8:21 am
-
I have problem to connect to remote server thought telnet I made and configure a user in Linux and in mysql also.
I want that the user get in directly to mysql trough Linux shell giving password not to use Linux shell, just telnet the ip address and enter password to go to mysql
For database use and when the user wants to exit the session must close. Would you please help. -
33 08.11.08 at 11:14 am
-
Every time I try and restart Mysql I ge this error. Everything to be working write I can get into mysql but can’t log on to it remotely. Please help.
Thanks,
-Veggieveggie@Server:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables. -
34 09.01.08 at 5:20 pm
-
Dear All;
to access to remote computer you need to select (Enable root access from remote machines) during the instsllation of Mysql server.
after that you must change the localhost to the IP adress for the remote computer and you will get the conection.
EX: you use the A PC and need to conect to B PC in B PC you have the database and it’s IP 1.1.1.1 so you need to change the do the following in A pc to get the conection to B PC:
“jdbc:mysql://1.1.1.1:3306/yorDBName”
good luck for all -
35 09.05.08 at 7:46 am
-
great article. I could setup the remote mysql admin by editing the my.cnf fie.
Thanks. -
36 09.15.08 at 9:22 am
-
Thanks to commenters’ tips to open port 3306 - fixed my problem.
Alternative to directly editing the iptables file:
system-config-securitylevel
–>advanced
–>add the port at the bottomThanks
-
37 09.24.08 at 7:01 pm
-
Thank you, just what I was looking for
-
38 09.25.08 at 9:19 pm
-
Thanks Google to send me to this page. Thanks for the article. I have two questions. In CPanel I think there is a remote access option. Can u not use to set up remote access? Is there any way I can create desktop application to access database in host
-
39 10.20.08 at 8:53 pm
-
Just to say: Great!
Solved my problem at all!
Thanks for writing it! -
40 12.18.08 at 3:39 pm
-
Yes this is help me out and thanks a lot
{ 1 trackback }