Allow Remote Connections to Mysql Server Ubuntu

Allow Remote Connections to Mysql Server Ubuntu

To Allow Remote Connections to Mysql Server, we have manually add some code to the configurations of the mysql server. This article is about Allow Remote Connections to Mysql Server Ubuntu operating system (Debian based). Ok. Let’s start, At the time of installation MySQL is already available to the localhost of the system.To allow remote connections or other than the localhost, you have to enable or uncomment the /etc/mysql/my.cnf and assigned to your computers IP address and not loopback address.

Or add a bind address like this, if you don’t want to specify the IP

Note: If you can’t find the bind-address in the above specified location, look at the following location too.
/etc/mysql/mysql.conf.d/mysqld.cnf. It should be there. Also you can easily locate any file using the command locate filename

Now stop and restart MySQL with the new my.cnf entry. After restarting the MySQL server type the following comments in the terminal :

It will give an output, something like this with your actual IP in the xxx’s

mysqld 1046 mysql 10u IPv4 5203 0t0 TCP xxx.xxx.xxx.xxx:3306 (LISTEN)

Note: Even if not displaying above, it may be functional (seems working on some server, which do not give an output like this). If the above statement returns correctly, which means that you will then be able to accept remote users.

Now, to connect a remote user with correct privileges, you need to have that user created in both the localhost and ‘%’ as in.

Log into MySQL Terminal, type the command

and enter the password.

Note: securepass or whatever you give.Now, we have to grant privileges to that user, as follows.

as a final step,

The “%” indicate that, can access from any machine publicly or from any IP.(Since, this involves risk, use it care fully).
Also, you can specify a mysql database like this ‘mydb.*’ instead of ‘*.* , if you want to restrict that user to a particular database.

Note : After completing above steps successfully, restart MySQL and Apache ( Without restarting Apache, it’s not working sometimes).

To restart MySQL and Apache, to view their status use this comments on terminal:

To restart MySQL

or

To view status

To test the remote connection to your server, use this command and type your MySQL server password:

And select the database,

Hope you understand everything. Allow Remote Connections to Mysql Server, this post is for Ubuntu only. If you have doubts, drop your comments below.Thanks for reading.

2 Comments

  1. James Fred

    Very useful article is this. Allow Remote Connections to Mysql Server Ubuntu, really helped me.

    Reply
  2. Joe

    Good article. Really helped me. Thanks…

    Reply

Leave a Comment

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