ubiq

Build dashboards & reports in minutes

  • Blog
  • What is Ubiq
  • Free Trial

Enable remote access to MySQL database

November 26, 2013September 14, 2020 Ubiq

Usually remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to MySQL database server from home or a web server. You can follow these steps to enable it.

Step 1 : Log in using SSH (if server is outside your environment or intranet)

First, login over ssh to remote MySQL database server from windows using PuTTy or from Linux using SSH

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:

  • On Debian Linux file is located at /etc/mysql/my.cnf location.
  • On Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnflocation.

To edit /etc/my.cnf, run:

# vi /etc/my.cnf
  • On Windows, my.ini file is located at C:\Program Files\MySQL\MySQL Server X.Y (X.Y is version number of MySQL server)

For windows, open my.ini file in notepad

Step 3: Once file is opened, locate line that reads 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 173.234.21.12 then entire block will look like as follows:

[mysqld]
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    = 173.234.21.12
# skip-networking
....

Where,

  • 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 be removed from my.cnf or commented.

Step 4: Save and Close the file

On Debian / Ubuntu Linux, type the following command to restart the mysql server:

# /etc/init.d/mysql restart

On RHEL / CentOS / Fedora / Scientific Linux, type the following command to restart the mysql server:

# /etc/init.d/mysqld restart

On Windows, open command prompt as Administrator and type

net stop MySQL
net start MySQL

Step 5: Grant access to remote IP address

Grant access to a new database

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

mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'162.72.20.23' IDENTIFIED BY 'PASSWORD';

Grant Access To An Existing Database

Let us assume that you are always making connection from remote IP called 162.72.20.23 to database called webdb for user webadmin. To grant access to this IP address for existing database type the following command at mysql prompt:

mysql> update db set Host='162.72.20.23' where Db='webdb';
mysql> update user set Host='162.72.20.23' where user='webadmin';

Step 6: Logout of MySQL

Type exit command to logout of mysql:

mysql> exit

Step 7: Open port 3306

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 162.72.20.23:

/sbin/iptables -A INPUT -i eth0 -s 162.72.20.23 -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

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

On windows, go to Control Panel > Windows Firewall > Add inbound rule for port 3306

Step 8: Test remote access to MySQL database

From your remote system or your desktop type the following command:

$ mysql -u webadmin –h 173.234.21.12 –p

On windows, run the MySQL command-line program, and log in as an administrator by typing the following command from the MySQL root folder (e.g Program Files\MySQL\MySQL Server 5.5\bin):

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -u webadmin –h 173.234.21.12 –p

Where,

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

About Ubiq

Ubiq is a dashboard & reporting platform that makes it easy to get insights from data. Build dashboards, charts & reports for your business in minutes. Try it for free today!

Related posts:

How to Import CSV to MySQL Workbench
Copy data into new table in MySQL
How to Convert UTC to Local Time in MySQL
MySQL amazon, bind address, cloud database, cnf file, database security, howto, linux, mac, mysql, putty database access, remote user access, remote-access, skip networking, ssh database login, tcp connection, tips, unix sockets. permalink.

Post navigation

How to create a read-only MySQL user?
How to backup and restore MySQL database?

About Us



Ubiq is a business intelligence & reporting tool that makes it easy to get insights from data. Build business dashboards, charts & reports in minutes.Try it Today!




  • Popular
  • Recent
  • transpose rows columns dynamically
    How to Transpose Rows to Columns Dynamically in MySQL Feb 12, 2020
  • calculate percentage of column in mysql
    How to Calculate Percentage of Column in MySQL Jan 6, 2020
  • calculate total sales per month in mysql
    How to Calculate Total Sales Per Month in MySQL? Mar 25, 2020
  • How to create a read-only MySQL user? Nov 25, 2013
  • get last record in each group
    How To Get Last Record In Each Group In MySQL May 27, 2020
  • calculate moving average mysql
    How to Calculate Moving Average in MySQL Feb 17, 2020
  • import csv to mysql workbench
    How to Import CSV to MySQL Workbench Apr 30, 2020
  • check value in postgresql array
    How to Check if PostgreSQL Array Contains Value Jan 19, 2021
  • coalesce in mysql
    How to Use Coalesce in MySQL Jan 19, 2021
  • query json column in mysql
    How to Query JSON column in MySQL Jan 18, 2021
  • avoid inserting duplicate records in mysql
    How To Avoid Inserting Duplicate Records in MySQL Jan 18, 2021
  • update array in postgresql
    How to Update Array in PostgreSQL Jan 15, 2021
  • get multiple count in mysql
    How to Get Multiple Counts With Single Query in MySQL Jan 15, 2021
  • convert datetime to utc in mysql
    How to Convert datetime to UTC in MySQL Jan 14, 2021
dazzling Theme by Colorlib Powered by WordPress