Linux allows MySQL database remote access method

MySQL default root user only local access, not remote connection management MySQL database, Linux How to open MySQL remote connection? Set the steps as follows:
1. Grant command create remote connection MySQL authorized user Itlogger

The code is as follows	Copy Code
Mysql-u root-p Mysql>grant all privileges in *.* to itlogger@localhost identified by ' www.itlogger.com ' with GRANT OPTION; Mysql>grant all privileges in *.* to itlogger@ "%" identified by ' www.itlogger.com ' with GRANT OPTION;

The first sentence adds Itlogger user authorization via Local machine (localhost) access, password "www.itlogger.com". The second sentence is to grant the Itlogger user access from any other host (wildcard character%).
2, set the firewall allow 3306 ports
Vi/etc/sysconfig/iptables
Add-A rh-firewall-1-input-m state–state new-m tcp-p tcp–dport 3306-j ACCEPT
(Note that the rule will not take effect until the-a rh-firewall-1-input-j reject–reject-with icmp-host-prohibited is added)
Reboot Firewall service iptables restart
3, attached: MySQL can not remotely connect the common problems

1 to see whether the MySQL port is correct, through the NETSTAT-NTLP view of the port occupancy, the general situation of the lower mouth is 3306.

2) Error: Error 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.51.112′ (111)
Check to see if the skip-networking has been/etc/my.cnf and need to be dropped.
3) Error: Error 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.51.112′ (113)
Check to see if iptables does not allow MySQL connection by: Service iptables stop temporarily turn off test for normal remote access, if possible, set Iptable to allow Port 3306
4 remote access to MySQL speed is very slow solution
Modify/ETC/MY.CNF or My.ini
Add under [mysqld]

The code is as follows	Copy Code
Skip-name-resolve Skip-grant-tables

Method two modifies My.ini

First step: Modify the My.cnf file

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

If you are using Debian Linux, the file location is:/etc/mysql/my.cnf

If you use red Hat linux/fedora/centos Linux, the file location is:/etc/my.cnf

If you use FreeBSD, the file location is:/var/db/mysql/my.cnf

If you use VI edit, use the command directly

The code is as follows	Copy Code
# VI/ETC/MY.CNF

Step two: If the file is open, follow the following

[Mysqld]

Make sure that the skip-networking is annotated or deleted, and then add the following line

bind-address= Your server IP

For example, your server IP is 65.55.55.2, and then you need to configure things like the following:

The code is as follows	Copy Code
[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 = 65.55.55.2 # skip-networking .... .. ....

This inside

Bind-address: You need to bind the IP address.

Skip-networking: Open the skip-networking option to completely shut down the MySQL TCP/IP connection, in some documents also mentioned in a stand-alone operation of the MySQL recommended to open this option, now look, not very reliable.

Step three: Save and close the file

Reboot your MySQL server and output at the command line

#/etc/init.d/mysql Restart

Step Fourth: Bind administrative permissions for remote IP addresses

To connect to a MySQL server:

$ mysql-u root-p MySQL

Binding permissions to a new datasheet (This step can be accomplished by using tools such as phpMyAdmin, which is just an example)

If we need to bind a remote IP 202.54.10.20 to a bar user under the new Foo database, enter at the command line:

The code is as follows	Copy Code
mysql> CREATE DATABASE foo; Mysql> GRANT all on foo.* to bar@ ' 202.54.10.20 ' identified by ' PASSWORD ';

How do you bind a database that already exists?

The code is as follows	Copy Code
mysql> Update db set host= ' 202.54.10.20 ' where db= ' webdb '; mysql> Update user set host= ' 202.54.10.20 ' where user= ' WebAdmin ';

Fourth step: the introduction of MySQL

Enter the following command:

The code is as follows	Copy Code
Mysql> quit; Fifth Step: Open 3306

Port

TCP port 3306 needs to be turned on using the iptables or BSD PF firewall

Examples of iptables under Linux

/sbin/iptables-a input-i eth0-p TCP--destination-port 3306-j ACCEPT

Or if you only need to allow a specific server, IP 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 just allow the remote connection range within the 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

Last Save All Rules

# Service Iptables Save

Rules for Freebsd/openbsd PF (/etc/pf.conf)

Pass in on $ext _if Proto TCP-from-to-any port 3306

or allow ip:10.5.1.3 to be allowed

Pass in $ext _if Proto TCP/10.5.1.3 to any port 3306 flags S/sa synproxy State

Step Sixth: Test

You open cmd above the remote host, enter:

Mysql-u webadmin–h 65.55.55.2–p

Over here

-U webadmin:webadmin is a user of the MySQL server

-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