How to allow remote access to MySQL Databases in Linux

For security reasons, remote access to MySQL is usually disabled, but sometimes the service for remote access to the database needs to be provided. The following describes two methods to enable remote connection to mysql.

For security reasons, remote access to MySQL is usually disabled, but sometimes the service for remote access to the database needs to be provided. The following describes two methods to enable remote connection to mysql.

By default, the root user of Mysql can only access the mysql database locally, but cannot remotely connect to and manage the mysql database. How can I enable Mysql remote connection in Linux? The procedure is as follows:
1. GRANT command to create a remote connection to mysql.

The Code is as follows:
Mysql-u root-p Mysql> grant all privileges on *. * TO itlogger @ localhost identified by 'www .itlogger.com 'with grant option; Mysql> grant all privileges on *. * TO itlogger @ "%" identified by 'www .itlogger.com 'with grant option;

In the first sentence, the itlogger user is added to authorize access through the Local Machine (localhost) with the password "www.itlogger.com ". The second sentence is to authorize the itlogger user to initiate access from any other host (wildcard % ).
2. Set the firewall to allow port 3306
Vi/etc/sysconfig/IPtables
Add A RH-Firewall-1-INPUT-m state-state NEW-m tcp-p tcp-dport 3306-j ACCEPT
(Note that before-A RH-Firewall-1-INPUT-j REJECT-reject-with icmp-host-prohibited, otherwise the rule may not take effect)
Restart Firewall service iptables restart
3. Appendix: FAQs about Mysql remote connection failure

1) check whether the Mysql port is correct. Use netstat-ntlp to check the port usage. Generally, the port is 3306.

2) ERROR: ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.51.112' (192)
Check whether skip-networking has been injected in/etc/my. cnf.
3) ERROR: ERROR 2003 (HY000): Can't connect to MySQL server on '192. 168.51.112' (192)
Check whether iptables does not allow mysql connection. Use: service iptables stop to temporarily disable the test to check whether normal remote access is allowed. If yes, set iptable as above to allow port 3306.
4) Slow remote access to mysql
Modify/etc/my. cnf or my. ini
Add under [mysqld]

The Code is as follows:
Skip-name-resolve Skip-grant-tables

Method 2 modify my. ini

Step 1: Modify the my. cnf File

Use a text editor to edit the MySQL server configuration file my. cnf

If you use Debian Linux, the file is located at:/etc/mysql/my. cnf

If you use Red Hat Linux/Fedora/Centos Linux, the file is located at:/etc/my. cnf.

If you use FreeBSD, the file is located at/var/db/mysql/my. cnf.

If you use VI for editing, directly use the command

The Code is as follows:
# Vi/etc/my. cnf

Step 2: If the file is opened, follow the steps below

[Mysqld]

Make sure that the skip-networking is commented out or deleted, and then add the following line of content.

Bind-address = your server IP address

For example, if your server IP address is 65.55.55.2, configure the following content:

The Code is as follows:
[Mysqld] User = mysql Pid-file =/var/run/mysqld. pid Socket =/var/run/mysqld. sock Port = 3306 Basedir =/usr Datadir =/var/lib/mysql Tmpdir =/tmp Language =/usr/share/mysql/English Bind-address = 65.55.55.2 # Skip-networking .... .. ....

Here

Bind-address: the IP address to bind.

Skip-networking: enabling the skip-networking option can completely disable MySQL's TCP/IP connection mode. In some documents, it is also recommended to enable this option for MySQL running on a single machine, unreliable.

Step 3: Save and close the file

Restart your MySQL server and output it in the command line.

#/Etc/init. d/mysql restart

Step 4: bind the management permission of the remote IP Address

Connect to the MySQL server:

$ Mysql-u root-p mysql

Bind permissions to the new data table (this step can be done simply using tools such as phpmyadmin. Here is just an example)

To bind a remote ip address 202.54.10.20 to the bar user in the newly created foo database, enter:

The Code is as follows:
Mysql> create database foo; Mysql> grant all on foo. * TO bar @ '2014. 54.10.20 'identified by 'Password ';

How to bind an existing database?

The Code is as follows:
Mysql> update db set Host = '2014. 54.10.20 'where Db = 'webdb '; Mysql> update user set Host = '2014. 54.10.20 'where user = 'webadmin ';

Step 4: Launch MySQL

Enter the following command:

The Code is as follows:
Mysql> quit; Step 5: Open 3306

Port

You Need To Enable TCP port 3306 and use the iptables or BSD pf firewall.

Iptables example in Linux

/Sbin/iptables-a input-I eth0-p tcp -- destination-port 3306-j ACCEPT

Or if you only need to allow a specific server with the ip address 10.5.1.3, you can do this:

/Sbin/iptables-a input-I eth0-s 10.5.1.3-p tcp -- destination-port 3306-j ACCEPT

Or, you can only allow the remote connection range in your subnet to be 192.168.1.0/24.

/Sbin/iptables-a input-I eth0-s 192.168.1.0/24-p tcp -- destination-port 3306-j ACCEPT

Last save all rules

# Service iptables save

FreeBSD/OpenBSD pf rules (/etc/pf. conf)

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

Or allow ip Address: 10.5.1.3

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

Step 6: Test

Open cmd on your remote host and enter:

Mysql-u webadmin-h 65.55.55.2-p

Here

-U webadmin: webadmin is a MySQL Server user.

-H IP or server name: 65.55.55.2 is MySQL Server IP Address

-P: Password

You can also use telnet to connect to port 3306.

$ Telnet 65.55.55.2 3306.