当前位置：网站首页>Mysqldump full recovery to another new instance, and then perform flush privileges analysis

Mysqldump full recovery to another new instance, and then perform flush privileges analysis

2022-07-19 09:25:00 【51CTO】

Preparation before experiment

mysql 1: Express mysql Example 1
mysql 2: Table is mysql Example 2

If mysqldump One library is available , A new instance is created 2, example 2 Besides root Users have no other users , however root The passwords are different , that mysql.user Can you recover successfully , perform flush privileges What's going to happen .

mysqldump be completely ready

mysql 1:root password 123456, Perform a full backup

       
       mysqldump -uroot  -p'123456'  -S /tmp/mysql.sock  --default-character-set=utf8mb4 --single-transaction --master-data=2 --set-gtid-purged=OFF --hex-blob --triggers --routines --events  -A  > /tmp/all.sql
       
1.

mysql 2：root password 234567

       
       mysqldump -uroot  -p'234567' 
       
1.

Full recovery

mysql 2： After logging in
source /tmp/all.sql To succeed

mysql 2：mysql.user Tables are also updated

       
       ([email protected]) [(none)] select user ,host from mysql.user;
       
+---------------+-----------+
       
| user          | host      |
       
+---------------+-----------+
       
| cjr           | %         |
       
| ly            | %         |
       
| lytest        | %         |
       
| mysql.session | localhost |
       
| mysql.sys     | localhost |
       
| root          | localhost |
       
+---------------+-----------+
       
6 rows in set (0.00 sec)
       
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

Resume exception analysis

       
        But check the user's permissions , perform flush privileges You can check 
       
([email protected]) [(none)] show grants for [email protected]'%';
       
ERROR 1141 (42000): There is no such grant defined for user 'cjr' on host '%'
       
([email protected]) [(none)] grant all on  *.* to [email protected]'%';     
       
Query OK, 0 rows affected (0.01 sec)
       
([email protected]) [(none)] show grants for [email protected]'%';          
       
ERROR 1141 (42000): There is no such grant defined for user 'cjr' on host '%'
       
([email protected]) [(none)] flush privileges;
       
Query OK, 0 rows affected (0.00 sec)
       
([email protected]) [(none)] show grants for [email protected]'%';
       
+------------------------------------------+
       
| Grants for [email protected]%                         |
       
+------------------------------------------+
       
| GRANT ALL PRIVILEGES ON *.* TO 'cjr'@'%' |
       
+------------------------------------------+
       
1 row in set (0.00 sec)
       
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

sign out mysql 2, Log in again , Password error found , Use mysql 1 Of root password , Login successful

       
       [[email protected] ~]# mysql -uroot -S /tmp/mysql3308.sock -p'234567'
       
mysql: [Warning] Using a password on the command line interface can be insecure.
       
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
       
[[email protected] ~]# mysql -uroot -S /tmp/mysql3308.sock -p'123456'
       
mysql: [Warning] Using a password on the command line interface can be insecure.
       
Welcome to the MySQL monitor.  Commands end with ; or \g.
       
Your MySQL connection id is 12
       
Server version: 5.7.33-log MySQL Community Server (GPL)
       
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
       
Oracle is a registered trademark of Oracle Corporation and/or its
       
affiliates. Other names may be trademarks of their respective
       
owners.
       
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
([email protected]) [(none)] 
       
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.

Analysis conclusion

Use full recovery to restore to a new instance , Because backup mysql.user surface
flush privileges Will overwrite the current database mysql 2 Passwords and permissions , Turn into mysql 1 Password and permissions
flush privileges When was it used , When the permission data in the data table is inconsistent with the permission data in memory ,
Can be used to rebuild memory data , Get to a consistent state .

flush privileges The command will clear acl_users Array , And then from mysql.user Read data from the table and reload ,
Reconstruct a acl_users Array . in other words , The data in the data sheet shall prevail , The global permission memory array will be reloaded .

therefore ,mysql.user The execution of the flush privileges It can be recovered later .

Add

mysql The permissions of are determined by mysql.user Tables and memory arrays acl_users control
mysql The connection between the client and the server will be assigned a thread , By memory acl_users Array control , Check the permissions of this user from the array in memory , And copy the permission value to the thread object . Therefore, the situation shown above will occur , The new instance imports mysql.user surface , But the new instance can still use its original root Password to login , But once flush privileges, The memory array is reloaded , Then the password permission will be overwritten .

原网站

版权声明
本文为[51CTO]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/200/202207171016287642.html