Implementing a client connection to a MySQL database server

Before learning MySQL, are deployed in this machine, so there is no client host and server host connection problem, are directly with localhost login on the line.

Today, because of the project needs, we deployed MySQL on the server, and then the client installed clients, so we encountered the client connection to the database server problem. It took a long time to find that even the default root account was not able to connect to the server. Record today's settlement process as follows:

1, after installing MySQL server on the servers, the company intranet opened 3306 ports (MySQL default port); At the same time, the server also to support external network access, but also increase access to the external network port. Finally, on the client host, through the "telnet" command, check that port 3306 is already available through the client host.

2, the client installed MySQL clients, found that the root user can not connect, that is, the client can not use Workbench to implement the database, users, data tables and other operations. Here are two ways, one is to directly authorize the root user, you can access in any host, and then create a new database, add a table, etc., the other way, through the server-side MySQL instructions, add a new database, new users, and then give users all the host connection permissions, is a step in place.

Method One: Change the root user's permissions to any host to access:

On the server, run the MySQL 5.7 command line Client, and then execute the following command:

(1) Use MySQL; Go to MySQL Database

(2) Select Host,user,password from user; Querying user information for a database

(3) Update user set host= '% ' where host= ' localhost '; Change the access host for root in the table above to all

(4) flush privileges; Refresh Permissions

(5) Exit; exit MySQL

Complete the above steps, in the client host Workbench, with Root/password, you can log in.

Method Two: Directly through the MySQL command, create database, user information, and client host access permissions settings.

On the server, run the MySQL 5.7 command line Client, and then execute the following command:

(1) Show databases; View the databases that already exist on the MySQL server. Avoid creating duplicate databases when new

(2) Create database szy; Create DATABASE "Szy"

(3) Create user szy identified by "szy"; Create szy user, set password szy

(4) Grant all privileges the Szy.*to ' szy ' @ '% ' identified by ' szy ' with GRANT option; Gives the user all permissions on the specified database and allows any client host to connect

(5) Flush privileges; Refresh the above permission settings

(6) Exit; Quit MySQL

To complete the above command, try the connection on the client as follows:

Note: The SQL command directly manages the organic on the MySQL command on the server, and each SQL command must be ";" End, otherwise the error, or quality is unsuccessful.

Implementing a client connection to a MySQL database server