Ubiq Blog

Build business dashboards & reports in minutes with Ubiq

  • What’s Ubiq
  • Features
  • Pricing
  • Try Ubiq now!
  • We’re Hiring
MENUMENU
  • Website Administration
  • Apache
  • Nginx
  • MySQL
  • AWS
November 26, 2013February 8, 2019 Sreeram Sreenivasan

Enable remote access to MySQL database


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

About Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq, a business dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build business dashboards & reports for your business. Try it for free today!

Related posts:

How to connect to MySQL on Amazon EC2 from Linux / Mac?
How to get MySQL table size for tables in database?
MySQL Tutorial : MySQL IN clause (Basic)
Calculate decile in MySQL based on totals
MySQL, MySQL Tips, MySQL Tutorials 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, windows

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 platform for small & medium businesses. Build business dashboards & reports in minutes.Try it Today!




  • Popular
  • Recent
  • speed up your website
    7 Ways To Speed Up Your Website and Reduce Page Load Time Nov 8, 2018
  • ssl certificate providers for your website
    Top 7 SSL Certificate Providers for Your Website Nov 8, 2018
  • Prevent image hotlinking
    How to Prevent Image Hotlinking using .htaccess Nov 12, 2018
  • enable mod_rewrite for apache in ubuntu
    How to Enable mod_rewrite for Apache in Ubuntu Nov 13, 2018
  • Site Structure for SEO
    How to Create a Site Structure for SEO Nov 13, 2018
  • how to set cookie expiration in nginx
    How to Set Cookie Expiration in NGINX Feb 11, 2019
  • set cookie expiration in apache
    How to Set Cookie Expiration in Apache Feb 11, 2019
  • delete cookie in nginx
    How to Delete Cookie in NGINX Feb 11, 2019
  • delete cookie in apache
    How to Delete Cookie in Apache Feb 11, 2019
  • set nginx request headers conditionally
    How to Set Request Headers Conditionally in NGINX Feb 11, 2019
dazzling Theme by Colorlib Powered by WordPress