How Do I Enable Remote Access To MySQL Database Server?

By default, remote access to the MySQL database server is disabled for security reasons. However, sometimes you need to provide remote access to database server from home or a web server. This post will explain how to setup a user account and access a MySQL server remotely on a Linux or Unix-like systems.

Task: MySQL Server Remote Access

You need type the following commands which will allow remote connections to a mysql server.

Step # 1: Login Using SSH (if server is outside your data center)

First, login over ssh to remote MySQL database server. You may need to login to your MySQL server as the root user:

### login as the root using su or sudo ##
# or use sudo  ##
sudo -i

OR directly login as root user if allowed:


Step # 2: Edit the my.cnf file

Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi:

  • If you are using Debian/Ubuntu 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 location.

Edit the /etc/my.cnf, run:
# vi /etc/my.cnf

Step # 3: Once file opened, locate line that read as follows


Make sure line skip-networking is commented (or remove line) and add following line


For example, if your MySQL server IP is then entire block should be look like as follows:

user            = mysql
pid-file        = /var/run/mysqld/
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    =
# skip-networking


  • bind-address: IP address to bind to.
  • skip-networking : Do not 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 be removed from my.cnf or put it in comment state.

Step# 4 Save and Close the file

If you are using Debian / Ubuntu Linux, type the following command to restart the mysql server:
# /etc/init.d/mysql restart
# systemctl restart mysql
If you are using RHEL / CentOS / Fedora / Scientific Linux, type the following command to restart the mysql server:
# /etc/init.d/mysqld restart
# systemctl restart mysqld
If you are using FreeBSD, type the following command to restart the mysql server:
# /usr/local/etc/rc.d/mysql-server restart
# service mysql-server restart

Step # 5 Grant access to remote IP address

Connect to mysql server:
$ mysql -u root -p mysql

Grant access to a new database

If you want to add a new database called foo for user bar and remote IP then you need to type the following commands at mysql> prompt:mysql> CREATE DATABASE foo;

How Do I Grant Access To An Existing Database?

Let us assume that you are always making connection from remote IP called for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:
mysql> update db set Host='' where Db='webdb';
mysql> update user set Host='' where user='webadmin';

Step # 6: Logout of MySQL

Type exit command to logout mysql:mysql> exit

Step # 7: Open port 3306

You need to open TCP 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

/sbin/iptables -A INPUT -i eth0 -s -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet

/sbin/iptables -A INPUT -i eth0 -s -p tcp --destination-port 3306 -j ACCEPT

Finally save all rules (RHEL / CentOS specific command):
# service iptables save

A sample FreeBSD / OpenBSD / NetBSD pf firewall rule ( /etc/pf.conf)

Use the following to open port # 3306 on a BSD based systems:

pass in on $ext_if proto tcp from any to any port 3306

OR allow only access from your web server located at

pass in on $ext_if proto tcp from to any port 3306  flags S/SA synproxy state

Step # 8: Test it

From your remote system or your desktop type the following command:
$ mysql -u webadmin -h -p

  • -u webadmin: webadmin is MySQL username
  • -h IP or hostname: is MySQL server IP address or hostname (FQDN)
  • -p : Prompt for password

You can also use the telnet or nc command to connect to port 3306 for testing purpose:
$ echo X | telnet -e X 3306
$ nc -z -w1 3306
Sample outputs:

Connection to 3306 port [tcp/mysql] succeeded!

🐧 Please support my work on Patreon or with a donation.
🐧 Get the latest tutorials on Linux, Open Source & DevOps via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
172 comments… add one
  • Tymanthius May 26, 2017 @ 15:24

    How would I go about allowing connection from anywhere?

    I have a ham radio logging software that I’d like to run portable and connect back to my home server running mysql to save the logs. My IP could be anything.

    I can change the ports or use NAT to avoid port scanning, but I have no idea how to modify the update commands to allow connections from anywhere.

  • Jacob Van Diest Mar 10, 2017 @ 15:43

    The internet on the website is not working.

  • Aurelio Jan 12, 2017 @ 18:31

    Thank you so much for this. I did the obvious, enabling remote access in the mysql privileges, but was unable to access due to the local host bind.
    I am wondering if we can set the bind to a specific IP range, like the local network subnet, and if that would effectively limit listening for login attempts to the local network at least.
    This helped so much though, being able to actually connect. thank you!

  • santosh Nov 14, 2016 @ 20:48

    Changing the cnf file causes mysql server to stop working. In my case I followed from step 5 and everything works. Thanks

  • Gilbert Sep 26, 2016 @ 6:43


  • Logics Jan 15, 2016 @ 19:29

    I see that this is an old post but it has current relevance so please update this for Ubuntu (among the top two most popular Linux distros) for where the file is located and how to restart the server.

    File location:

    Server restart:
    sudo service mysq

    Also, in MySQL 5.6+, “skip-networking” is no longer the default so you will not find that line to comment out nor remove. It is removed by default but by default, they bind it to the loopback address,

    so either comment out the “bind-to” line or change it to the resolved address of your server.

    If you want it to be available for remote (in-front your firewall) entry, this produces one of two issues; set it to the private IP address and everyone outside is blocked or set it to the public IP address and everyone inside is blocked (and your server is exposed).

    The solution is to bind it to the private IP address then your remote computer needs to VPN into the network first where it can see the private IP address of the server.

    Alternately, remove/comment the “bind-to” line from your config file.

  • PHK Corporation Dec 9, 2015 @ 15:06

    When changing this on a production system your local data will not be able to connect if you reference the host as localhost. To fix that, change your database settings for host to your server IP that you specified in my.cnf file. For example, for wordpress, change the DB_HOST in wp-config.php from localhost to you server IP.

  • ruan Sep 29, 2015 @ 16:02

    This is a great post.
    But the iptables step is not necessary for me

  • Isaac Aug 30, 2015 @ 20:25

    I have my own server at home with Ubuntu Server to test my apps made on PHP. And I need to enable remote access to MySQL server but I’m confused with the bind-address, I have to put the Public Ip or the local IP of the pc?

  • Fede Feb 18, 2015 @ 4:26

    Thank you, I’ve been facing this remote connection issue for days and nothing I read and tried worked. Your post did. Thank you.

  • Manisha Oct 10, 2014 @ 8:39

    For Windows XP Professional, how to access to mySQL Server

  • arjun Jun 27, 2014 @ 11:20

    my mysql connection doesnot have a password so how will i connect?

  • Dave Apr 21, 2014 @ 2:45

    After your step #3, mysqld will no longer start for me.

  • Lappro Feb 15, 2014 @ 21:49

    I had some trouble with this and after some googling and experimentation I think I have found the solution.
    The problem was mysql didn’t want to start anymore after changing the bind-ip address. Turns out I didn’t need to fill in my external IP, but my internal IP (so not localhost but the ip it has in the network). After that mysql did start again and by opening the correct port it is accessible from the internet.

    • Lappro Feb 15, 2014 @ 21:55

      Never mind, looks like it still refuses connections.

      • Lappro Feb 15, 2014 @ 22:10

        Alright, figured it out for reals this time. Made the stupid error of making a user for the same database with username@%, but then I didn’t add any privileges to the user so it wasn’t allowed to do anything.
        However I now have this address for the bind ip: (found it somewhere online and is used as the address for all addresses (ipv4)), which is nice since * didn’t work.
        Haven’t tested again for the internal ip address if that works too, since it works now and I’m fine with that.

  • Pawan Sep 26, 2013 @ 3:51

    Suppose I have some couple of my sql users..??and many machines run mysql so i want..
    tom user and all other mysql user on this Machine to be accessible only from jack@ not anywhere else.
    and plz how can this be done?

  • blanc Sep 12, 2013 @ 7:57

    bind-address can be commented.
    just remember to make a static IP for your machine 🙂

  • Nirupma Sep 4, 2013 @ 9:37

    When I modify my my.cnf bind-address with my IP address after restarting mysql service getting error message
    Shutting down MySQL
    .. *
    Starting MySQL
    ….. * The server quit without updating PID file (/var/lib/mysql/

    And on accessing mysql -u root -p ‘password’
    It gives error : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)

  • Yeaoh Aug 12, 2013 @ 7:51

    On Debian 7, The my.cnf is in /etc/mysql/

  • Tom Jun 23, 2013 @ 10:26

    Need Help:

    update db set Host=’xx’ where Db=’xx’;
    update user set Host=’xx’ where user=’root’;

    After this commands my websites are down and cannot access localhost.
    How to undo?? Pls help 🙂

  • Dan Jun 17, 2013 @ 16:08

    You are awesome, thanks for sharing this. Always nice to see fixes for little things like this from other people in the trenches.

  • Maciek Apr 10, 2013 @ 18:52

    It took me half a day to find this hint. Google should do a much better job here – cause anything else was crap!


  • anabela Mar 29, 2013 @ 14:43

    great tut, keep going.

  • Jeff Jan 4, 2013 @ 22:18

    Thanks, this is exactly what I needed.

    Wanted to point out a little syntax error in step 5, which slowed me down since I am a command line mysql newbie. There should not be a space between the command option ‘-p’ and the password. Therefore the line should look like:

    $ mysql -u root -pmysql

  • Gilank Dec 14, 2012 @ 9:57

    All useful post at nix craft is inspiring me! thanks alot, i’ve figured it out from my stuck connection where mysql db in different machine

  • viki Oct 25, 2012 @ 6:02

    THANK YOU…..

  • Liam Alford Oct 22, 2012 @ 9:38

    Having some issues on an Ubuntu 12.04 EC2 server. When i get to the stage to edit remote access to the DB.

    mysql> update db set Host=’′ where Db=’webdb’;
    mysql> update user set Host=’′ where user=’webadmin’;

    There doesn’t seem to be any entries into the db table (i am assuming we are looking at the root mysql database). Though my websites database is set up and working as its now a live system works with MySQL connections locally. I am trying to set up remote access to my development (home) network so i can run updated versions of the website locally without effecting the live version, but still use the live database.

    Any idea where i am going wrong?

  • Ayasoft Sep 24, 2012 @ 16:11

    One comment on #4

    For me worked this one:
    /etc/init.d/mysql restart

    I am not sure it’s a system depended, or just a small bug.
    Overall this guide is brilliant. Thanks for writing.

  • Arun Jun 18, 2012 @ 6:24

    Thanks you solve my problem. Your blog is very nice. Thanks once again.

  • Eric Jun 10, 2012 @ 3:53

    How about if my client IP dinamic? How to set to mysql allow all IP?

  • Eric Nguyen May 29, 2012 @ 18:38

    bind-adress should be set to the IP address from which the database is going be access, not IP address of the server itself.

    Setting bind-address to IP address of the server will cause mysql deamon fail to start and setting bind-address= (accepting all IP addresses) should solve the problem. Based on your example, bind-address could be set to too.
    This is at least true to MySQL servers on Amazon EC2 (Centos) instances.

    Please update your tutorial if you agree with my comment.

    Thanks a lot for your detailed and useful tutorial anyway 🙂

  • Nicolas May 22, 2012 @ 18:32

    Ok, I think i found mysql-large.cnf (though mine is called my-large.cnf). But i don’t have a etc folder at /usr/local/etc. Even tough I have one at /usr/etc, it haven’t got rc.d in it.. Help please!

  • Nicolas May 22, 2012 @ 8:40


    I actually do not have any mysql directory under /usr/local/ you know where else it could be? :/

  • 🐧 nixCraft May 19, 2012 @ 20:33


    Use a text editor such as vi. The default files are located in /usr/local/share/mysql/ directory. In this example copy /usr/local/share/mysql/mysql-large.cnf to /usr/local/etc/my.cnf and edit using vi:

    ## copy it ##
    cp /usr/local/share/mysql/mysql-large.cnf /usr/local/etc/my.cnf
    ## edit it ##
    vi /usr/local/etc/my.cnf
    ## restart the server ##
    /usr/local/etc/rc.d/mysql-server restart

    Hope this helps!

  • Nicolas May 19, 2012 @ 18:04

    If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf


  • Michael Mar 27, 2012 @ 8:25

    You need to issue a FLUSH PRIVILEGES command on SQL server after you grant access to whatever. otherwise the new permissions are not yet valid.

  • subramanian Mar 1, 2012 @ 5:28

    This is for ubuntu 11.10
    create a user in mysql as root@staticip
    root@franchexpress-2010:# sudo ufw allow 22
    root@franchexpress-2010:# sudo ufw allow 3306
    root@franchexpress-2010:# mysql -u root -p
    mysql>GRANT ALL PRIVILEGES ON *.* to ‘root’@’%’ IDENTIFIED BY ‘mysql password’;
    mysql>update user set Host=’′ where user=’root’;
    mysql>update db set Host=’′ where Db=’franch_co’;

  • subhash Feb 7, 2012 @ 7:50

    How to connect mysql database in php with use the ip address

    Thnx in advance

    • swapan paul Feb 7, 2012 @ 8:32

      Try this:
      mysql_connect(, ‘admin’, ‘admin’) or die (“Could not connect: “.mysql_error());

      • swapan paul Feb 7, 2012 @ 11:03

        Here it is:
        mysql_connect(‘ip address’, ‘admin’, ‘admin’) or die (“Could not connect: “.mysql_error());

  • Remote database mad Jan 29, 2012 @ 1:44

    Very nice article, the series of comments which has went over here, made many things clears. Thanks for sharing

  • premiso Jan 19, 2012 @ 23:24

    Why do all this…if you have SSH Access, just setup a tunnel, unless you need people without SSH Access to access the database. But an SSH tunnel will be way more secure than opening it up to the world, and you don’t have to do any of this simply:

    ssh -L 3307:localhost:3306

    Using 3307, incase you have a local MySQL server installed.

    • Aly Oct 17, 2012 @ 9:28

      Thanks a lot. It is really a good idea to use a tunnel in this case.

  • risdiyanto Jan 12, 2012 @ 8:11

    you can comment bind-address:

    to skip networking

  • Asif Oct 18, 2011 @ 9:54

    Hi, I have edited my.cnf according to your Step-3. But while restarting it says “MySQL Daemon failed to start.”. Just for the note, my server is running on amazon ec2. Any idea what went wrong?

  • Kevinal Sep 13, 2011 @ 8:56

    How to allow multiple users in local network to share a single MySQL database

  • Vijay Kumar Aug 28, 2011 @ 13:56

    Hello To all,
    mysql is work proparly,but for remotly access i make file as you write but after this changes mysql is not start,i am using centos 5
    /etc/ini.d/mysqld restart
    so please help me.As Soon as possible.

  • Cecil Aug 26, 2011 @ 4:24

    Thanks for this excellently written article. I was going to write my own, but I think I’ll just link to yours.

  • albin Jul 1, 2011 @ 9:58

    i have configured multiple instance of mysql having new instance port 3307. But am not able to connect to this port from a mysql client. Where should i change the configuration?

  • Caleb May 15, 2011 @ 23:48

    The variable skip-name-resolve will also keep remote access from working. Was pulling my hair out trying to get this to work, comment out that variable and viola.

  • Bruce May 11, 2011 @ 1:23

    everything seem to work fine but Step # 6: Open port 3306

    /sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT ??

  • stephen white Apr 21, 2011 @ 0:35

    I know this article is not about SSH but I just wanted to mention that those that are confused with these condifuration changes or skittish about doing them can just use an SSH client (I use Putty) and use tunneling to then access MySQL data as ‘localhost’. Keeps everything locked down and I use the MySQL administrator and Query Browser without any issues on a windows 7 machine. I do have a local dev MySQL server running on my machine so I do have to stop that service when I tunnel in to the remote MySQL debian linux machines. If I forget the user/pass’s will trip me up so not a serious mistake waiting to happen 🙂

  • Chris Apr 7, 2011 @ 12:14

    Hi there, I have a query that needs your support.

    This is the ERROR I get while creating a NEW SERVER INSTANCE: in Windows.

    1.………… error ………………………………………………………………………
    Connecting to MySQL server localhost…
    Cannot start SSH tunnel manager
    Connecting to MySQL server localhost…
    Cannot start SSH tunnel manager
    Connecting to MySQL server localhost…
    Cannot start SSH tunnel manager


    And also

    2. ……………………………………………………………………………………………

    In order to manage a MYSQL server it must be installed as a service.

    The wizard could not find MYSQL service on the target machine, hence
    the server instance cannot be created.



    How do I make sure that MYSQL 5.1 is running before I use the MYSQL
    Administrator to configure new Server Instance.

    I will be grateful to be answered the above question in reference to
    the errors the program is giving me in the Windows environment.

  • koniak Mar 22, 2011 @ 16:43

    Hello admin… just U save my life I was found how to access to my mysql BD for the enterily day and finally I found this page… It’s so clear and really helpful so thanks

  • gumuruh Mar 11, 2011 @ 10:21

    and…. how could I grant all ip address range –
    to connect into that mysql server?
    Don’t say to me repeat that command over 10 times. That’s not the answer, tough.

  • Shaul Amran Mar 6, 2011 @ 10:09

    Thank you for this tutorial!
    I followed every single step and it worked!

  • Satya Jan 19, 2011 @ 6:37

    Yes ! without configuring /etc/my.cnf aslo it can work . It’ll help to update remote host mysql db .

    Best Regards,

  • jessica Jan 7, 2011 @ 23:41

    I am trying to allow external connections to an existing database. When I issue the command:

    update db set Host=’′ where Db=’webdb’;
    (I changed the IP to my host and webdb to my database name), I always get:
    “ERROR 1046 (3D000): No database selected”.

    If I select my database first (by doing “use mydb”;), and issue the above command again, then I get:
    “ERROR 1146 (42S02): Table ‘mydb.db’ doesn’t exist”

    What am I missing?

  • webb Jan 3, 2011 @ 9:17

    Thanks, this has helped me figure out how to deal with remote connections.

  • Live Dec 20, 2010 @ 7:07

    I wonder what’s the difference of just connecting via SSH, use a command line and access the mysql prompt from there.

    Any benefits of this tutorial’s approach than what I do with mine?

  • techno mixx Dec 14, 2010 @ 7:29

    I was looking for any method to access remotely a db and update local database with every change occur. is it possible? thanks in advance

  • hari Nov 15, 2010 @ 9:28

    Neat. Easy to follow. Got my job done. Thanks

  • Satan Enterprises Oct 31, 2010 @ 18:20

    Anyone else getting this error:
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

    When you run step 5 on Ubuntu 10.04?

  • neill Oct 20, 2010 @ 7:45

    For ubuntu 9.10 and later

    /etc/init.d/mysql restart


    sudo service mysql restart

  • Bassem Oct 9, 2010 @ 11:47

    How can I grant access to my local database to a remote connection via Workbench 5.2?

    Thanks a lot

  • Vamsi Sep 12, 2010 @ 4:50

    Hi Sir, I have this in my .conf ( OS is debian and was installed from apt-get )
    # Instead of skip-networking the default is now to listen only on

  • hari Aug 3, 2010 @ 8:01

    Fantastic writeup. I had multiple issues and this one solved everything…

  • harlan Jul 29, 2010 @ 4:44

    Thanks, useful.

  • sandip Jul 27, 2010 @ 10:26

    hi i m confused with your query for the solution you have provided for the existing database grant to the existing user that he can logged with his original password or should we have to update the password.
    mysql> update db set Host=’′ where Db=’webdb’;
    mysql> update user set Host=’′ where user=’webadmin’;

    • mona Aug 2, 2010 @ 8:28

      Thanks, by doing an update solved my problem Much appreciated.

  • mona Jul 21, 2010 @ 5:13

    It is a very helpful document, it did help me from the start and I’m facing the next step now; collaboration and backup from a remote machine&domain. They want to do a login from another domain and with port forwarding to copy a few DBs. Something like: $ ssh -L freeport:mysql-server-ip:3306 and on the remotehost.domain in a new terminal window $ mysql -h localhost –port=freeport -u mysqluser -p
    comes back with : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
    The can login successfully with: $mysql -h mysql-server-ip -u mysqluser -p only the client port forwarding need something.

    Can anyone help with this ? Thanks in advance.

  • mrudula Jun 2, 2010 @ 12:50

    U can write similar query for db2 also. It will provide user to access only db1 and db2 databases.

    Is that ur query?

    • swapan paul Jun 3, 2010 @ 5:23

      yes ,thnkx a lot .It worked for me . One thing i would like to share is that seperate create statement for user creation is not required in case of grant statement.grant command create user as well.

      Anyways thnkx ..thnkx a lot..

  • Anonymous Jun 2, 2010 @ 12:48

    yes. This query will provide access to db1. Write a similar query for db2 as well. It will help.

  • swapan paul Jun 2, 2010 @ 6:12

    Thankx for ur reply.
    I wanted ‘user’ to have access only on db1 and db2 databases only (all tables) out of several other databases, not to a particular db1 in ur example.
    Kindly help.

  • Mrudula Jun 2, 2010 @ 4:01


    Create user n then grant priviledges.

    create user ‘user’@’localhost’ identified by ‘password’;

    grant SELECTon db1.* to ‘user’@’’ identified by ‘password’;

    Here user is the username
    password is the password for user to connect to mysql
    db1.* refers to all tables of db1 refers to ip address from where the user connects

    You can also give ALL priviledges or SELECT,INSERT,UPDATE,DELETE,CREATE,DROP priviledges

  • swapan paul Jun 1, 2010 @ 7:15

    what is the Grant command to give access to a ‘user’ on ‘db1’ and ‘db2’ ,all tables?

  • Frank Apr 18, 2010 @ 10:25

    Thanks for the article – sorted my problem easily!

  • Artem Apr 17, 2010 @ 22:45

    Great manual, thanks a lot!

  • brahimhackman Apr 14, 2010 @ 12:21

    im agree with you thanks for u gays ,but the big problem is not the binding-ip ,the problem is how to access to this file if you are a client .
    the hosting company only can access to this file.

    • beau Jul 7, 2010 @ 18:14

      Typically hosting companies will use some type of web client that will allow you to make these changes to a mysql database located on their system. I am not familiar with all of the systems out there, but in cpanel there should be a section of icons listed under the heading databases. Select “Remote MySQL” and enter the ip address or host name of the system you are allowing access. You will also need to do this if you are designing a website that will be database driven so that you can make connections to the database through a web page.

  • Ken F Apr 9, 2010 @ 1:38

    Thanks a ton. Just in case anyone needs help, granting my permissions worked fine on my existing database. (the instruction for making a new database) I skipped the next two instructions. Also, service iptables didn’t work for me, but my distro had some instructions for saving the new iptables rule to a new file in the /etc directory and putting a line in the /etc networking configuration to load that file at every boot; so search your distro for saving iptables if service iptables doesn’t work for you either.

    • Michael Jun 7, 2010 @ 15:45

      hello i am new to using mysql can you tell me the command to allow remote access to a server on the mysql command line.

  • Elton Mar 28, 2010 @ 1:00

    Great Article.
    When I add the bind-address to my.cnf
    mysqld will not restart.

    In the log I have the following:
    100327 13:12:58 mysqld started
    100327 13:12:58 InnoDB: Started; log sequence number 0 43654
    100327 13:12:58 [ERROR] Can’t start server: Bind on TCP/IP port: Cannot assign requested address
    100327 13:12:58 [ERROR] Do you already have another mysqld server running on port: 3306 ?
    100327 13:12:58 [ERROR] Aborting

    100327 13:12:58 InnoDB: Starting shutdown…
    100327 13:12:59 InnoDB: Shutdown completed; log sequence number 0 43654
    100327 13:12:59 [Note] /usr/libexec/mysqld: Shutdown complete

    I do not have any IPtables or firewall that I can find. This is on Centos4

  • diaz Mar 25, 2010 @ 21:43

    geat.. sory i can’t speak english with Good
    i am a hamachi user ::
    so how if i want use 2 IP to remote mysql

    when first i use ip in local homework
    and Ip in hamachi group


  • ravindra Mar 20, 2010 @ 9:55

    please can you tell me how to configure jdbc connection in linux…. oracle version is

  • Conrado Mar 20, 2010 @ 0:34

    I have to do a program in C# that access a mysql database but i have no idea how to can you tell me a website that can help with this????

  • villalvilla Mar 17, 2010 @ 12:31

    try writing “flush privileges;” before loging out mysql in step 5 😉

    • Alex Jul 14, 2010 @ 14:26

      Ditto what villalvilla said– gotta flush privileges!

  • Peter S. Mar 3, 2010 @ 16:16

    Following step 5 did not work for me because my db was created in MySQL Workbench as a blank db. Does this instance need the tables from the ‘mysql’ instance copied over before I can perform this step. There is no table named db in my newly created instance; hypothetically “foo”.

  • mrudula Feb 26, 2010 @ 7:03

    I have made the bind-address entry in my.cnf and granted access to host and db in mysql db as per the instructions above.
    Both the mysql server and remote host r in same network, still i get an error like this

    ERROR 2003 (HY000): Can’t connect to MySQL server on ‘’ (113)

  • Nabil Jan 31, 2010 @ 4:33

    I suggest that you remove the –bind-address from my.cnf :


    The IP address to bind to. Only one address can be selected. If this option is specified multiple times, the last address given is used.
    If no address or is specified, the server listens on all interfaces.

  • Vinidog Jan 15, 2010 @ 18:54

    Nice… great article


  • Pat Jan 8, 2010 @ 19:06

    Thank you. This saved me a lot of time and aggravation.

  • JORGE VALVERDE Dec 1, 2009 @ 17:25

    Muchísimas gracias por tu ayuda, alfín he podido configurar mi entorno de desarrollo.

    Thanks a lot for your help, finally I could configure my development environment.


  • Mohd Anas Oct 30, 2009 @ 9:15

    Hi all,
    To get the above task done, there isn’t any need to change the configuration file . It can be done simply by typing the below command from mysql console:


    By default, mysql allows only local connections, using ‘%’ sign this setting can be
    changed to allow remote connections and local connection as well.

  • P R Oct 21, 2009 @ 18:29

    my error was very simple. the host name of the second host was wrong 🙂

    P R

  • PG Oct 20, 2009 @ 20:02

    Thank you for your good article.

    But I can’t access MySQL-Server remotely.

    The bind-address is added

    The user ‘test’ has grants and can be accessed remotely.

    But I can access MySQL-Server from the second host, because the access is not allowed from the host.

    Can you tell me please what I’m doing wrong?

    Thank you

  • PG Oct 20, 2009 @ 20:00

    Thank you for your good article.

    But I can�t access MySQL-Server remotely.
    The bind-address is added

    The user �test� has grants and can be accessed remotely.

    But I can access MySQL � Server from the second host, because the access is not allowed from the host.

    Can you tell me please what I�m doing wrong?

    Thank you

  • NioNio Sep 9, 2009 @ 11:38

    hello, how could i allow access only to one remote ip?
    i noticed that i can login to all my mysql accounts remote..

  • Manimal Aug 4, 2009 @ 15:17

    Thank you, thank you, thank you. I’m running Ubuntu 8.04 and this was perfect.

  • pankaj Jun 20, 2009 @ 7:49

    nice tutorial……. keep it up….. thanks for ur help….

  • Anonymous Jun 14, 2009 @ 10:16

    Hi, this is a pretty good article even though I had to make a few adjustments to setup the MySQL server on my Mac but the instructions are nonetheless still good.

  • Marko Matić May 19, 2009 @ 23:11

    Hi, your article helped me a lot! Thanx! 🙂

    Just in the section “How Do I Grant access to existing database?” you first must select MYSQL database, and then you can update or alter tables USER and DB. So:

    mysql> use mysql
    mysql> update db set Host=’′ where Db=’webdb’;
    mysql> update user set Host=’′ where user=’webadmin’;

  • namrata Mar 24, 2009 @ 7:54

    below is my code containg database connectivity

    String connectionURL = "jdbc:mysql://";
    Connection connection = null;
    PreparedStatement pstatement = null;
    int updateQuery = 0;
    if(subject!=null && enquirydetails!=null && firstname!=null && lastname!=null && companyname!=null && companyaddress!=null && zipcode!=null && phoneno!=null && emailadd!=null)
    if(subject!="" && enquirydetails!="" && firstname!="" && lastname!="" && companyname!="" && companyaddress!="" && zipcode!="" && phoneno!="" && emailadd!="")
    try {
    /* Create a connection by using getConnection()
    method that takes parameters of string type
    connection url, user name and password to connect
    to database. */
    connection = DriverManager.getConnection
    (connectionURL, "root", "XXXX");
    // sql query to insert values in the secified table.
    String queryString = "INSERT INTO enquiry(subject,enquirydetails,firstname,lastname,companyname,companyaddress,zipcode,phoneno,emailadd) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    /* createStatement() is used for create statement
    object that is used for
    sending sql statements to the specified database. */
    pstatement = connection.prepareStatement(queryString);
    pstatement.setString(1, subject);
    pstatement.setString(2, enquirydetails);
    pstatement.setString(3, firstname);
    pstatement.setString(4, lastname);
    pstatement.setString(5, companyname);
    pstatement.setString(6, companyaddress);
    pstatement.setString(7, zipcode);
    pstatement.setString(8, phoneno);
    pstatement.setString(9, emailadd);
    updateQuery = pstatement.executeUpdate();
    if (updateQuery != 0) { %>
  • namrata Mar 24, 2009 @ 7:52

    i hav designed a website using JSP and mysql, there is an enquiry form in that website ,when one fills the form the details automaticaly go into databse as the logic goes..but my problem is on localhost(tomcat) when i fill the form and submit, a retrieved form is displayed which shows my entry..but when i hosted the website the data is not being retrieved …which means it is not geting connected to the server database which i created on putty.. can anyone please help me on this.i am new to jsp ..i just cannot get the logic.i mean on local host alls well but nt on the server..can anyone help step by step on this..please.
    my website link is
    u can try and fill the form ..
    it will give you a blank retrieved form..

  • cfscg Feb 11, 2009 @ 22:06

    Just a follow up.
    Thanks for your help.
    I did re install the MYsql 5 and I had to copy the My-huge.cnf to /etc/my.cnf

  • DieZ Feb 7, 2009 @ 2:52

    Thanks, useful article!

    And how to grant access to ANY ip address?
    E.g. several users with different unpredictable IPs should access to database…

  • Kenan Sevindik Feb 6, 2009 @ 9:40

    saved my day, thanks a lot…

  • 🐧 nixCraft Feb 2, 2009 @ 23:19

    May be file is deleted or replaced. Just do forceful installation again.

  • cfscg Feb 2, 2009 @ 22:03

    Yes. I have MySQL 5 installed. I am able to log into the test and mysql databases when I am on the machine (not remotely).
    but I am trying to connected to this server remotely and obviously I need to work on the my.cnf

    Below is what yum command returned. Thanks for your help.

    [root@localhost /]# yum search mysql
    Loaded plugins: rhnplugin, security
    ====================================================================== Matched: mysql =======================================================================
    mod_auth_mysql.i386 : Basic authentication for the Apache web server using a MySQL database.
    qt-MySQL.i386 : MySQL drivers for Qt's SQL classes.
    MySQL-client-enterprise-gpl.i386 : MySQL - Client
    MySQL-python.i386 : An interface to MySQL
    MySQL-server-enterprise-gpl.i386 : @COMMENT@ for Red Hat Enterprise Linux 5
    freeradius-mysql.i386 : MySQL bindings for freeradius
    libdbi-dbd-mysql.i386 : MySQL plugin for libdbi
    mysql.i386 : MySQL client programs and shared libraries.
    mysql-bench.i386 : MySQL benchmark scripts and data.
    mysql-connector-odbc.i386 : ODBC driver for MySQL
    mysql-devel.i386 : Files for development of MySQL applications.
    mysql-server.i386 : The MySQL server and related files.
    mysql-test.i386 : The test suite distributed with MySQL.
    perl-DBD-MySQL.i386 : A MySQL interface for perl
    php-mysql.i386 : A module for PHP applications that use MySQL databases.
    php-pdo.i386 : A database access abstraction module for PHP applications
    qt4-mysql.i386 : MySQL drivers for Qt's SQL classes
    rsyslog.i386 : Enhanced system logging and kernel message trapping daemons
    rsyslog-mysql.i386 : MySQL support for rsyslog
    unixODBC.i386 : A complete ODBC driver manager for Linux.
    [root@localhost /]#
  • 🐧 nixCraft Feb 2, 2009 @ 21:40

    Do you have mysql installed?

    yum list installed mysql

  • cfscg Feb 2, 2009 @ 21:39

    I can not find the my.cnf anywhere in the system. I have installed RHEL5.1.
    I ran this command: find / -iname my.cnf, but returns nothing.
    Am I missing something? Do I need to actually create a file called my.cnf under etc?

    Thanks for your inputs.

  • prashant Jan 28, 2009 @ 12:51

    This is a very informative article. It helped me a lot.

  • Sivan Dec 18, 2008 @ 15:39

    Yes this is help me out and thanks a lot

  • Alex1980 Oct 20, 2008 @ 20:53

    Just to say: Great!

    Solved my problem at all!
    Thanks for writing it!

  • aphplearner Sep 25, 2008 @ 21:19

    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

  • Max Sep 24, 2008 @ 19:01

    Thank you, just what I was looking for

  • Henry Sep 15, 2008 @ 9:22

    Thanks to commenters’ tips to open port 3306 – fixed my problem.

    Alternative to directly editing the iptables file:

    –>add the port at the bottom


  • n Sep 5, 2008 @ 7:46

    great article. I could setup the remote mysql admin by editing the my.cnf fie.

  • mohammed Falah Sep 1, 2008 @ 17:20

    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 so you need to change the do the following in A pc to get the conection to B PC:
    good luck for all

  • Veggie Aug 11, 2008 @ 11:14

    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.

    veggie@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.

  • ali Jul 10, 2008 @ 8:21

    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.

  • khawar Jul 4, 2008 @ 19:18


    what if I could not find the file my.cnf on my server?…..I am actually using a web hosting server.



  • Martin Jun 23, 2008 @ 13:02

    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.

    MySQL remote Access Control

  • Adrian Lozano Jun 5, 2008 @ 18:14

    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

  • MyIkram May 31, 2008 @ 12:00

    Nice tutorial up there !

    Keep up the good work

  • kenneth Apr 30, 2008 @ 13:06

    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, I can’t access it remotely.

    • khsaf Mar 28, 2017 @ 20:57

      Same here!

  • kenneth Apr 30, 2008 @ 2:19

    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.

  • kokki Apr 16, 2008 @ 6:32

    heloo admin ,
    i want to find the ip address of the my sqlserver in fedoro 5 how to find it can u guide me .

  • pathan Mar 26, 2008 @ 7:36

    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. and linux server has on single LAN.

  • SEM Expert Feb 17, 2008 @ 5:11

    Nice addition. Simply great,expect same will help me a lot.

  • Seo Freelancer India Jan 26, 2008 @ 0:48

    Hey i am having a problem with Bad Handshake!!
    Can any body help me please!!

  • pamchi Oct 12, 2007 @ 3:49

    Look, what if a want that all my lan have acces to the db?

    • Torsten Apr 14, 2015 @ 15:01

      Quote from
      “ You can specify wildcards in the host name. For example, user_name@’’ applies to user_name for any host in the domain, and user_name@’192.168.1.%’ applies to user_name for any host in the 192.168.1 class C subnet.

      The simple form user_name is a synonym for user_name@’%’.

      MySQL does not support wildcards in user names. To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:

      GRANT ALL ON test.* TO ”@’localhost’ …;

      In this case, any user who connects from the local host with the correct password for the anonymous user will be permitted access, with the privileges associated with the anonymous-user account.

      For additional information about user name and host name values in account names, see Section 6.2.3, “Specifying Account Names”.”

  • Everah Sep 7, 2007 @ 16:03

    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 `` 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.

  • hari vishnunu Sep 4, 2007 @ 2:52


    Simply great,expect same again.

  • George P. Aug 26, 2007 @ 0:07

    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

  • amin Aug 17, 2007 @ 7:35

    you know what, now am not be able to login as root on localhost. How can I revoke those update above

    • Ryan Nov 6, 2010 @ 18:31

      Yeah exact same thing happened to me. I try running the same command to give me back rights and it wont work.
      SyntaxError: invalid syntax
      >>> update user set Host=’′ where user=’root’;
      File “”, line 1
      update user set Host=’′ where user=’root’;

  • amin Aug 17, 2007 @ 4:01

    I also cannot find my iptables file on redhat enterprise

  • amin Aug 17, 2007 @ 3:55

    mysql> update user set Host=’′ where user=’webadmin’;

    I always receiving error when performing above command. Could u advise

    • sachind Aug 26, 2010 @ 12:39

      mysql> update user set Host=’′ where User=’webadmin’;

  • Frankp Jul 24, 2007 @ 0:19

    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.

  • 🐧 nixCraft Apr 11, 2007 @ 14:23


    Yup, you need to open port 3306 for communication.

    Appreciate your post!

  • CptBeluga Apr 10, 2007 @ 21:10

    Using Fedora 6 as host and added port 3306 to
    /etc/sysconfig/ip6tables and /etc/sysconfig/iptables

    For 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

  • Jimaco Mar 7, 2007 @ 7:10

    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

  • matt Jan 18, 2007 @ 0:01

    thanks, this helped me out.

  • mac Oct 21, 2006 @ 1:45

    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 host

    do i have a problem with my ipaddr?

    • roshan Mar 25, 2011 @ 6:58

      first provide the static ip add man
      then it will connect automatic

    • Dan Nov 21, 2013 @ 7:15

      You will need to provide the port number to telnet to, as well. Telnet defaults to port 21, and that’s not the port MySQL uses. Also, ipaddr will need to be the IP address of the MySQL machine.

  • mac Oct 21, 2006 @ 1:30

    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 use

    I check connectivity using ping host
    and got response
    icmp_seq=0 ttl=64 time=0.542 ms
    meaning i have connectivity

    Is there something i missed?

  • 🐧 nixcraft Sep 25, 2006 @ 23:42

    /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

  • Anonymous Sep 25, 2006 @ 15:34

    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.cnf

    please help.

    • roshan Mar 25, 2011 @ 6:53

      just through out ur machine and buy some real man

      • Phaedrus Nov 21, 2013 @ 5:30

        Roshan, learn some English please..

    • Dan Nov 21, 2013 @ 7:10

      It sounds like you’re not saving your file but instead backgrounding it. To save, :wq will save and quit in vi/vim.

  • Anonymous Sep 25, 2006 @ 15:34

    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.cnf

    please help.

  • 🐧 nixcraft Sep 24, 2006 @ 3:15

    vivek is username and is server IP. You need to setup correct permission using GRANT command (see above for an example).

  • Abject Eminence Sep 23, 2006 @ 8:18

    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 you need to type command as follows:
    $ mysql –u vivek –h -p
    OR if you with to use MySQL server ip address (
    $ mysql –u vivek –h -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.

  • 🐧 nixcraft May 4, 2006 @ 11:29

    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 you need to type command as follows:
    $ mysql –u vivek –h -p

    OR if you with to use MySQL server ip address (

    $ mysql –u vivek –h -p

    • Jhunivegn Saavedra Jul 29, 2010 @ 7:59

      Absolutely right! because I’d tried it how many times. And and I found it was right.

    • sachind Aug 26, 2010 @ 12:37

      There is no need to setup in my.cnf, without configuration it works fine for remote address.

      • Rahul Gamit Oct 4, 2011 @ 8:14

        Sachid: can you please tell me how can i configure without updating my.cnf file ?

        • Sohaib Jun 15, 2013 @ 15:50

          run mysql_secure_installation and follow the simple wizard

    • mysqlhost Apr 10, 2015 @ 19:35

      You are absolutely right. i wasn’t comfortable updating the my.cnf, grant user, iptables, telnet.
      Good thing I just tried your one command and it works.

  • van May 4, 2006 @ 4:23

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre> for code samples. Problem posting comment? Email me @