Detailed explanation of MySQL creating new users and enabling remote connection access-Mysql Tutorial-php.cn

Detailed explanation of MySQL creating new users and enabling remote connection access

藏色散人

Release： 2020-01-30 19:50:26

forward

7420 people have browsed it

The following mysql tutorial will introduce to you how to create a new user in MySQL and enable remote connection access. I hope it will be helpful to friends in need!

Detailed explanation of MySQL creating new users and enabling remote connection access

##MySQL creates a new user for the remote database and enables remote connection access

Each project has a database. It is very dangerous if the same MySQL user is used to access each database. Once a project is hacked, its The database account password can also be used to access other databases, which is simply too dangerous! Therefore, for the sake of database security, it is best to create a separate user for each database. Each user can only access the database used for its own project.

1. Create a user

First log in to the remote server and use root to enter MySQL to perform operations.

CREATE USER &#39;username&#39;@&#39;host&#39; IDENTIFIED BY &#39;password&#39;;

Copy after login

username: The user name you will create

host: Specify which host the user can log in to. If it is a local user, localhost can be used. If you want the user to log in from any remote host To log in, you can use the wildcard %

password: the user’s login password. The password can be empty. If it is empty, the user can log in to the server without a password.

2. Authorization:

GRANT privileges ON databasename.tablename TO &#39;username&#39;@&#39;host&#39;

Copy after login

privileges: User’s operation permissions, such as SELECT, INSERT, UPDATE, etc. If you want to grant all permissions, use ALL

databasename: database name

tablename: table name. If you want to grant the user corresponding operation permissions on all databases and tables, you can use * to represent it, such as *.*

3. Local login connection test, remote login connection test:

Perform a local login test directly on the remote host:

mysql -u username -p

Copy after login

Then enter the password. If you log in successfully, the user is created successfully.

Change a host and test remote login:

mysql -u username -h host -P port -p

Copy after login

username: the user name you just created

host: the host domain name or IP address of your remote database

port is the port number, usually 3306

Note: The host for this test must have MySQL software locally, otherwise the mysql command cannot be executed.

4. My operation:

CREATE USER &#39;jal&#39;@&#39;%&#39; IDENTIFIED BY &#39;123456&#39;;
GRANT ALL ON jalprodatabase.* TO &#39;jal&#39;@&#39;%&#39;;

Copy after login

This is created. I created a MySQL user. The username is jal and the password is 123456. I can connect to this remote jalprodatabase database on any host.

Test on another computer:

mysql -u jal -h www.jiailing.com -P 3306 -p

Copy after login

Press Enter and enter the password 123456 to log in.

The above is the detailed content of Detailed explanation of MySQL creating new users and enabling remote connection access. For more information, please follow other related articles on the PHP Chinese website!