1. Log on to mysql

Normally, root users have the right to create users (they can be given the same privileges as root), so we first log in to root users.
mysql has a user name of root and a password of 123456.

mysql -u root -p123456
# or
mysql -u root -p
Enter password:

1.1 Remote login mysql

mysql -u test -h 113.186.203.114 -p 123456

2. Creating Users

The key step is to create users. First of all, what we need to know is that there are two ways to access mysql

• Local IP access. For example, mysql is installed on 113.219.186.18, and the service has been started. Then you need to access mysql on this machine, which is called local IP access.
• External IP access. For example, mysql is installed on the 113.219.186.181 machine, and the service has been started. Then you can remotely access mysql on 113.219.186.181 on 103.219.186.181 machine, which is the IP access of the extranet.

2.1 Creating Local Users

Create a local user named admin with a password of 123456.

mysql> create user 'admin'@'localhost' identified by '123456';

Mode two

MySQL > grant [permission 1, permission 2... ] on a library. A table to a new user name @'host name / IP address'identified by'password';

1.2 Create users:
    Format: grant select on database. * to username @logon host identified by'password'
Examples:
Example 1: Add a user test1 password to abc so that he can log on to any host and have access to all databases
 The right to query, insert, modify or delete. First connect to MySQL with root user, and then type the following command:
　　　　　　grant select,insert,update,delete on *.* to root@localhost identified by 'mysql';
Or
　　　　　　grant all privileges on *.* to root@localhost identified by 'mysql';
Then refresh the permission settings.
　　　　　　flush privileges;

Example 2: If you don't want root to have a password to manipulate the tables in the database "mydb", you can call another command to remove the password.
　　　　　　grant select,insert,update,delete on mydb.* to root@localhost identified by '';

2.2 Creating Accessible Users on Extranet

Create a user name admin, password 123456, accessible users of the external network.

mysql> create user 'admin'@'%' identified by '123456';

Mode two

2.3 Refresh Authorization

mysql> flush privileges;

It is worth mentioning that local users are needed for local access and external users are needed for external access.

3. Modify user password

3.1 change local user password

The following code is a modified password for the local admin user.

mysql> set password for 'admin'@'localhost'=password('111111');
# perhaps
mysql> UPDATE mysql.user SET Password=PASSWORD('111111') WHERE
User='admin' and host='localhost';
# perhaps
mysql> use mysql; 
mysql> update user set password=password('123') where user='admin' and host='%'; 

3.2 Modify the password of the users in the external network

mysql> set password for 'admin'@'%'=password('111111');
# perhaps
mysql> UPDATE mysql.user SET Password=PASSWORD('111111') WHERE
User='admin' and host='%';
# perhaps
mysql> use mysql; 
mysql> update user set password=password('123') where user='admin' and host='%'; 

3.3 Refresh Authorization

It just changed the password, but it hasn't worked yet. So you need to refresh the authorization.

mysql> flush privileges;

3.4 Special case, forget root user password

3.4.1 Modify the configuration file to skip password validation

vim /etc/my.cnf 
# Skp-grant-tables are added at the end of the file to indicate that password validation is skipped.

3.4.2 Log in to root users

mysql -u root -p
Enter password:
# Enter the mysql database directly

3.4.3 Modify Password

The following code is a modified password for the local admin user.

mysql> set password for 'root'@'localhost'=password('111111');
# perhaps
mysql> UPDATE mysql.user SET Password=PASSWORD('111111') WHERE
User='root' and host='localhost';
# perhaps
mysql> use mysql; 
mysql> update user set password=password('111111') where user='root' and host='localhost'; 

Modify the password of the users in the external network

mysql> set password for 'root'@'%'=password('111111');
# perhaps
mysql> UPDATE mysql.user SET Password=PASSWORD('111111') WHERE
User='root' and host='%';
# perhaps
mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='%'; 

3.4.4 Refresh Authorization

mysql> flush privileges;

3.4.5 Restart mysql service

Comment out the new configuration in the configuration file / etc/my.cnf

vim /etc/my.cnf 
......
# skip-grant-tables

Restart mysql service

service mysqld start
# perhaps
service mysqld restart

4. Operating User Rights

4.1 View permissions

show grants for 'user'@'IP address';

# Example 1
mysql> show grants for 'admin'@'localhost'; 
# Example 2
mysql> show grants for 'admin'@'%'; 

4.2 Modify permissions

mysql> grant  Jurisdiction on data base.surface to   'user'@'IP address';




# Example 1
# Grant users full access to the database through an extranet IP
grant all privileges on testdb.* to 'test'@'%' identified by '123456';

# Example 2
# Grant the user full access to the database on the local server
grant all privileges on testdb.* to 'test'@'localhost' identified by '123456';
# 

4.3 Delete permissions

MySQL > revoke permission on database. table from'user'@'IP address';

Example 1

Remember to refresh permissions

mysql> flush privileges;

5. Modifying Users

mysql> rename user 'admin'@'localhost' to 'new_admin'@'%';

# Refresh authorization
mysql> flush privileges;

6. Delete users

# Delete local users
mysql> drop user 'admin'@'localhost';
# Delete extranet users
mysql> drop user 'admin'@'%';

# Refresh authorization
mysql> flush privileges;

perhaps

# Delete local users
mysql> delete from user Where User='admin' and Host='localhost';
# Delete extranet users
mysql> delete from user Where User='admin' and Host='%';

Create a database

mysql> create database editestdb DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Reference

[1] MySQL adds new users, creates databases for users, and assigns permissions to new users
[2] The Method and Command of Modifying User Password in mysql
[3] Access denied for user'root'@'localhost' (using password: YES) denies access when MySQL logs in and can modify MySQL password
[4] Create new mysql users and grant privilege management.
[5] Control of User Rights, Library Rights and Table Rights in MySQL
[6] mysql adds users, modifies permissions, and modifies login permissions ip