Set Ubuntu to allow remote connection to MySQL

Set Ubuntu to allow remote connection to MySQL

Set Ubuntu to allow remote connection to MySQL. For security reasons, MySql-Server only allows access from local machines (localhost, 127.0.0.1. this is no problem for the website architecture of both Web-Server and MySql-Server on the same Server. however, as the website traffic increases, the Server architecture may put the Web-Server and MySql-Server on independent servers in the later stage to improve the performance, in this case, MySql-Server must be modified to allow Web-Server for remote connection.

1. log on to Mysql-Server to connect to local mysql (only local connections are allowed by default)
Microsoft Windows XP [version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents ents and Settings \ kuco> mysql-h localhost-u root-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 13
Server version: 5.1.45-community-log MySQL Community Server (GPL)

Type 'help; 'or' \ H' for help. type' \ C' to clear the current input statement.

Mysql>

2. Modify the user configuration of Mysql-Server.

Mysql> USE mysql; -- switch to mysql DB
Database changed
Mysql> SELECT User, Password, Host FROM user; -- view existing users, passwords, and hosts allowed to be connected
+ ------ + ---------- + ----------- +
| User | Password | Host |
+ ------ + ---------- + ----------- +
| Root | localhost |
+ ------ + ---------- + ----------- +
1 row in set (0.00 sec)

Mysql> -- only one default root user. The password is blank and only localhost connection is allowed.
Mysql> -- Next we will add another root user. The password is blank and only 192.168.1.100 connections are allowed.
Mysql> grant all privileges on *. * TO 'root' @ '192. 168.1.100 'identified by' 'with grant option;
Or allow access from ALL ip addresses: mysql> grant all privileges on *. * TO root @ "%" identified '';

Mysql> -- of course, we can also directly use UPDATE to UPDATE the root user Host, but it is not recommended. The SQL is as follows:
Mysql> -- UPDATE user SET Host = '192. 168.1.100 'WHERE User = 'root' AND Host = 'localhost' LIMIT 1;

3. Modify the Mysql configuration file my. ini.

Sudo vim/etc/mysql

Bind-address = 127.0.0.1
Comment out the bind-address = 127.0.0.1 line and modify it:
# Bind-address = 127.0.0.1
The Mysql-Server configuration is complete.

Restart MySQL
$> Sudo/etc/init. d/mysql restart
* Starting MySQL database server mysqld
... Done.
* Checking for login upt, not cleanly closed and upgrade needing tables.
Bkjia @ ubuntu :~ $ Sudo/etc/init. d/mysql start
* Starting MySQL database server mysqld
... Done.

After this step is configured, you can use the Mysql client tool to remotely connect to the client.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------