MySQL database open remote connection method

The separation of the web from the MySQL database is a good choice, avoiding the fact that a large number of database queries consume the CPU and the Web resources are not sufficient, while the Web server's resources are provided with the greatest possible browsing service, while the database server only handles database things alone.

Scope of application: have independent host permissions.

Hardware configuration: Two servers, where:

A is a Web server (assuming IP is: 192.192.192.192)

b for MySQL data server (assuming IP is: 168.168.168.168)

Start Action:

1. In Web Server A, configure the Web service with IP: 192.192.192.192.

2. Install the MySQL service on database server B with IP: 168.168.168.168.

3. Now the new version of MySQL generally does not allow remote connections by default. You need to set up a remote connection account.

Use the root account to enter MySQL on the command line.

Mysql-uroot-ppass

Choose to go to MySQL database

Use ' MySQL ';

View all existing accounts and addresses.

SELECT ' Host ', ' user ' from ' user ';

For example, mine is:

+-----------+------+

| Host | User |

+-----------+------+

|      localhost | |

| localhost | PMA |

+-----------+------+

In other words, there are three (localhost) accounts that allow only local connections, namely root, PMA, and empty users.

Now that you decide to let root have the remote link permission for the Web server A above, then:

UUPDATE ' user ' SET ' Host ' = ' 192.192.192.192 ' WHERE ' user ' = ' root ' LIMIT 1;

This 192.192.192.192 the Web server to connect to the database server remotely. If you want any remote machine to be able to connect to this database, swap 192.192.192.192 for%. But it's not recommended, because you know!

If you want to create a new user New_user with remote link permissions, that's it.

INSERT into ' user ' (' Host ', ' user ', ' Password ', ' select_priv ', ' insert_priv ', ' update_priv ', ' delete_priv ', ' creat E_priv ', ' drop_priv ', ' reload_priv ', ' shutdown_priv ', ' process_priv ', ' file_priv ', ' grant_priv ', ' References_priv ' , ' Index_priv ', ' alter_priv ', ' show_db_priv ', ' super_priv ', ' create_tmp_table_priv ', ' lock_tables_priv ', ' EXECUTE_PR IV ', ' repl_slave_priv ', ' repl_client_priv ', ' ssl_type ', ' ssl_cipher ', ' x509_issuer ', ' x509_subject ', ' max_questions ', ' max_updates ', ' max_connections ') VALUES (' 192.192.192.192 ', ' New_user ', PASSWORD (' New_user_password '), ' y ', ' y ', ' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ', ', ', ', ', ' 0 ', ' 0 ', ' 0 ');

Change the New_user to the name you want, the password is: New_user_password, of course, you can set it freely.

Both of the above methods need to do the following steps:

Flush privileges;//to make the permissions effective immediately

Or simply restart the MySQL service.

In practice, it is best to have two machines in the same computer room in the agreed network segment/firewall. Of course, if possible, it would be better to place the database server in a local area network within a Web server.

MySQL database open remote connection method