MySQL- Connect Your Database Remotely

For a client programme to be able to get connected with MySQL server,It must use proper connection string,which includes server host name and your database username and password. Some applications such as WordPress, Joomla and WHMCS generally have a default configuration file which you can easily override with your own specific values. Below is the example of this access information:

// ** MySQL settings ** //
 /** The name of the database for WordPress */
 define('DB_NAME', 'yourdatabasename');
/** MySQL database username */
 define('DB_USER', 'yourdatabase username');
/** MySQL database password */
 define('DB_PASSWORD', 'yourdatabase password');
/** MySQL hostname */
 define('DB_HOST', 'localhost');
 

Remote Access To MySQL Database Server Within cPanel :

By default remote access to MySQL database server is disable due to security reasons,but sometimes we need to provide remote access to database server.

If you try to connect your database using third party software that does not run on the server,First you have to allow the connection from cPanel for the IP address that from you are trying to connect. 

There are some programmes that you can use to connect your database remotely, Dream Weaver can connect your databases remotely.

To allow remote MySQL connection you need to make sure you have an IP address that will access the database.Once you have an IP address that you wish to connect remotely,Login cPanel and click on ” Remote MySQL”

remote access to MySQL

Next, you’ll need to add the IP address into the field and select “Add Host“, Now you will be able to connect to the databases remotely. You can add as many IP addresses as you need.

Moreover if your IP address changes regularly you may add a wildcard by using the

% symbol. This will add all IP addresses in that range.

MySQL Server Remote Access if not using cpanel :

If you would like to access the local MySQL server from any external host, you can allow remote access to MySQL server as follows:

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

Once connected,Start with editing mysql config file using any of your favorite editor.

Generally file is located at /etc/my.cnf location.

To edit the file run:

nano /etc/my.cnf

Locate the line that read as:

[mysqld]

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

bind-address=YOUR-SERVER-IP

bind-address    = 10.11.11.10

Save and close the file.

Restart MySQL server:

service mysql restart

Finally on the last step,We needed to grant remote access to the MySQL user within the MySQL server:

$ mysql -u root -p
Enter password:
mysql> use mysql
mysql> GRANT ALL ON *database* to user@'192.168.1.4' IDENTIFIED BY 'password'; 

Also, update firewall rules to make sure port# 3306 is open on the server that is running the mysql database.

After the above changes, you can connect to the mysql database from a remote client.

 

 

 

 

 

 

Leave a Reply

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