Linux allows MySQL database remote access method

Source: Internet
Author: User
Tags create database iptables

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.