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

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!

##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 'username'@'host' IDENTIFIED BY 'password';

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 'username'@'host'

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

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

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 'jal'@'%' IDENTIFIED BY '123456';
GRANT ALL ON jalprodatabase.* TO 'jal'@'%';

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

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!