This article uses examples to illustrate the principles and implementation methods of MySQL account management. Share with you for your reference, the details are as follows:

Account Management

When operating the database in a production environment, you must never use the root account to connect. Instead, create a specific account, grant this account specific operating permissions, and then connect to operate. The main operation is the CRUD of data.
MySQL account system: According to the different permissions of the account, MySQL accounts can be divided into the following types of service instance level accounts:, a mysqld is started, which is a database instance; if a user, such as root, has the permissions assigned at the service instance level, then the account can delete all databases, including the tables in these databases. Database level account: perform all operations such as adding, deleting, modifying and querying on a specific database. Table level account: perform all operations such as adding, deleting, modifying and querying on a specific table. Field level permissions: perform operations on specific fields of certain tables. Stored program level accounts: perform operations such as adding, deleting, modifying and querying on stored programs. Account operations mainly include creating accounts, deleting accounts, changing passwords, and authorizing permissions. Note:

When performing account operations, you need to log in with the root account. This account has the highest instance-level permissions and usually uses database-level operation permissions.

Grant permissions

You need to log in with an instance-level account, taking root as an example.

The main operations include:

View all users Modify password Delete user

1. View all users

All user and permission information is stored in the user table of the MySQL database. View the structure of the user table
desc user;
Main field description:
Host indicates the host that is allowed to access
User represents the user name
authentication_string represents the password, which is the encrypted value to view all users

select host,user,authentication_string from user;

result

mysql> select host,user,authentication_string from user;
+-----------+------------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+-------------------------------------------+
| localhost | root | *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | debian-sys-maint | *EFED9C764966EDB33BB7318E1CBD122C0DFE4827 |
+-----------+------------------+-------------------------------------------+
3 rows in set (0.00 sec)

2. Create an account and authorize

You need to use an instance-level account to log in and operate. Taking root as an example, common permissions include: create, alter, drop, insert, update, delete, select
If you want to assign all permissions, you can use all privileges

2.1 Create Account & Authorize

grant permission list on database to 'user name'@'access host' identified by 'password';

2.2 Example 1

Create an account named laowang with a password of 123456. The account can only be accessed locally and can only read all tables in the jing_dong database.

Step 1: Log in as root

mysql -uroot -p
Enter the password and then press Enter

Step 2: Create an account and grant all permissions

grant select on jing_dong.* to 'laowang'@'localhost' identified by '123456';

illustrate

All tables of the python database can be operated in the following way: jing_dong.*
The access host usually uses the percent sign % to indicate that this account can use any IP host to log in and access this database. The access host can be set to localhost or a specific IP, indicating that only the local machine or specific host is allowed to access. View the permissions that the user has

show grants for laowang@localhost;

step3: Log out of root

quit

Step 4: Log in using laowang account

mysql -ulaowang -p
Enter the password and then press Enter

The effect after logging in is as follows

2.3 Example 2

Create a laoli account with a password of 12345678, which can be accessed from any computer and has full permissions on all tables in the jing_dong database.

grant all privileges on jing_dong.* to "laoli"@"%" identified by "12345678"

Account Operations

1. Modify permissions

grant permission name on database to account@host with grant option;

2. Change password

Log in as root and modify the user table in the MySQL database

Use the password() function to encrypt the password

update user set authentication_string=password('new password') where user='user name';
example:
update user set authentication_string=password('123') where user='laowang';

Note that you need to refresh permissions after the modification is completed

Flush privileges

3. Remote login (dangerous, use with caution)

If you want to use the msyql command in an Ubuntu to remotely connect to another MySQL server, you can do it in the following way, but this method is only for understanding, do not use it in an actual production environment

Modify the /etc/mysql/mysql.conf.d/mysqld.cnf file

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Then restart msyql

service mysql restart

Test the connection in another Ubuntu

If you still cannot connect, possible reasons:

1. Network is not working

You can use ping xxx.xxx.xx.xxx to find out whether the network is normal.

2) Check whether the database is configured with the bind_address parameter

Log in to the database locally to view the my.cnf file and the current parameters of the database show variables like 'bind_address';

If bind_address=127.0.0.1 is set, you can only log in locally

3) Check whether the skip_networking parameter is set in the database

If this parameter is set, you can only log in to the MySQL database locally

4) Is the port specified correctly?

4. Deleting your account

Syntax 1: Log in as root

drop user 'username'@'host';
example:
drop user 'laowang'@'%';

Syntax 2: Log in as root and delete the data in the user table of the MySQL database

delete from user where user='username';
example:
delete from user where user='laowang';

-- After the operation is completed, you need to flush privileges

It is recommended to use syntax 1 to delete users. If syntax 1 fails to delete users, use syntax 2.

3. What to do if you forget the root account password!!

It is usually not our turn to manage the root account, so don't be so blind

Solution to forgetting the mysql root user password (skip-grant-tables):

skip-grant-tables
As the name implies, when the database is started, the restrictions of the permission table are skipped and you can log in directly without verifying the password.

Notice:

This situation is only used when you forget the root password and have to restart the database. Use with caution in the live network environment, as it requires restarting the database and its security is difficult to guarantee.

1. Modify configuration parameters

/etc/my.cnf

exist

[mysqld] Add below:

skip-grant-tables
Configuration items.

2. Restart MySQL

Make the parameters effective:

service mysqld restart

3. Notes

At this time, all users can log in to the current database without a password, so the security of the database is very low.

4. Change password

Specific methods:

https://www.jb51.net/article/169143.htm

5. Remove parameters

a. After changing the password, remove skip-grant-tables from the configuration file.

b. Restart the database again.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.