mysql.user细节三问

2022-07-09 07:47:10

一、如何拒绝用户从某个精确ip访问数据库
假如在mysql.user表中存在用户'mydba'@'192.168.85.%'，现在想拒绝此用户从某个精确ip访问数据库

# 创建精确ip用户，分配不同的密码

mysql> create user 'mydba'@'192.168.85.1' identified by 'otherpwd';

# 精确匹配，这个ip进来的密码为otherpwd，由于密码不匹配拒绝登录

mydba@192.168.85.132,3306 [(none)]> select user,host,authentication_string from mysql.user where user='mydba';

+-------+--------------+-------------------------------------------+

| user  | host         | authentication_string                     |

+-------+--------------+-------------------------------------------+

| mydba | 192.168.85.% | *A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5 |

| mydba | 192.168.85.1 | *33D5FAF1A32909300D21AB5A38FA4F215D9FCB26 |

+-------+--------------+-------------------------------------------+

2 rows in set (0.00 sec)

mydba@192.168.85.132,3306 [(none)]> select password('mysql5719'),password('otherpwd');

+-------------------------------------------+-------------------------------------------+

| password('mysql5719')                     | password('otherpwd')                      |

+-------------------------------------------+-------------------------------------------+

| *A7E26519238B6EA2F943D5FAC3CD7812AD8F87E5 | *33D5FAF1A32909300D21AB5A38FA4F215D9FCB26 |

+-------------------------------------------+-------------------------------------------+

1 row in set, 2 warnings (0.00 sec)

此时用户'mydba'@'192.168.85.1'，即用户名mydba从192.168.85.1机器*问，必须使用otherpwd才能登录
二、使用mysqladmin修改用户密码，当存在同名user(不同host)时，修改的是哪个用户的密码

mydba@192.168.85.132,3306 [(none)]> show master status;

+------------------+----------+--------------+------------------+----------------------------------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |

+------------------+----------+--------------+------------------+----------------------------------------------+

| mysql-bin.000116 |     3175 |              |                  | 8ab82362-9c37-11e7-a858-000c29c1025c:1-69371 |

+------------------+----------+--------------+------------------+----------------------------------------------+

1 row in set (0.00 sec)

mydba@192.168.85.132,3306 [(none)]> exit

Bye

[root@ZST1 ~]# mysqladmin -h192.168.85.132 -P3306 -umydba -p password newpwd

Enter password:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

[root@ZST1 ~]#

此时修改的是哪个用户密码？'mydba'@'192.168.85.1'还是'mydba'@'192.168.85.%'？还是两者都修改了？
先说结论：看你从哪个客户端发起命令，仅修改与其匹配的current_user()对应的密码。mysqladmin中需要提供user+password以登录到MySQL中，在登录验证过程中MySQL已经对其进行精确匹配
2.1、current_user()为mydba@192.168.85.%，对应修改'mydba'@'192.168.85.%'的密码

# 前面的mysqladmin在ZST1(192.168.85.132)上执行，对应的current_user()为mydba@192.168.85.%

mydba@192.168.85.132,3306 [(none)]> select user,host,authentication_string from mysql.user where user='mydba';

+-------+--------------+-------------------------------------------+

| user  | host         | authentication_string                     |

+-------+--------------+-------------------------------------------+

| mydba | 192.168.85.% | *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 |

| mydba | 192.168.85.1 | *33D5FAF1A32909300D21AB5A38FA4F215D9FCB26 |

+-------+--------------+-------------------------------------------+

2 rows in set (0.01 sec)

2.2、current_user()为mydba@192.168.85.1，对应修改'mydba'@'192.168.85.1'的密码

# 从192.168.85.1上执行mysqladmin，要使用otherpwd密码才能执行

C:\Users\Administrator>mysqladmin -h192.168.85. -P3306 -umydba -p password newpwd1

Enter password: newpwd

mysqladmin: connect to server at '192.168.85.132' failed

error: 'Access denied for user 'mydba'@'192.168.85.1' (using password: YES)'

C:\Users\Administrator>mysqladmin -h192.168.85. -P3306 -umydba -p password newpwd1

Enter password: otherpwd

Warning: Using a password on the command line interface can be insecure.

Warning: Server version is 5.7 or greater. The password will be sent to server in plain text. Upgrade the mysqladmin to a version that matches the server''s version.

C:\Users\Administrator>

我们查看修改后的密码，以及binlog

# 查看修改后的密码，分别对应是不从客户端修改

mydba@192.168.85.132, [(none)]> select user,host,authentication_string from mysql.user where user='mydba';

+-------+--------------+-------------------------------------------+

| user  | host         | authentication_string                     |

+-------+--------------+-------------------------------------------+

| mydba | 192.168..% | *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 |

| mydba | 192.168.85.1 | *12AB8416B918C0EC1528FD04A686AE12D97A4A5D |

+-------+--------------+-------------------------------------------+

 rows in set (0.01 sec)

mydba@192.168.85.132, [(none)]> select password('newpwd'),password('newpwd1');

+-------------------------------------------+-------------------------------------------+

| password('newpwd')                        | password('newpwd1')                       |

+-------------------------------------------+-------------------------------------------+

| *1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4 | *12AB8416B918C0EC1528FD04A686AE12D97A4A5D |

+-------------------------------------------+-------------------------------------------+

 row in set,  warnings (0.00 sec)

# 查看binlog

[root@ZST1 logs]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin. --start-position=

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at

# :: server id   end_log_pos  CRC32 0xf0c57f78   GTID    last_committed=       sequence_number=      rbr_only=no

SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:69372'/*!*/;

# at

# :: server id   end_log_pos  CRC32 0x8a596597   Query   thread_id=    exec_time=     error_code=

SET TIMESTAMP=/*!*/;

SET @@session.pseudo_thread_id=/*!*/;

SET @@session.foreign_key_checks=, @@session.sql_auto_is_null=, @@session.unique_checks=, @@session.autocommit=/*!*/;

SET @@session.sql_mode=/*!*/;

SET @@session.auto_increment_increment=, @@session.auto_increment_offset=/*!*/;

/*!\C utf8 *//*!*/;

SET @@session.character_set_client=,@@session.collation_connection=,@@session.collation_server=/*!*/;

SET @@session.lc_time_names=/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

======================== 在ZST1上执行mysqladmin修改用户密码 ========================

ALTER USER 'mydba'@'192.168.85.%' IDENTIFIED WITH 'mysql_native_password' AS '*1FA85AA204CC12B39B20E8F1E839D11B3F9E6AA4'

/*!*/;

# at

# :: server id   end_log_pos  CRC32 0xfdf414d2   GTID    last_committed=       sequence_number=      rbr_only=no

SET @@SESSION.GTID_NEXT= '8ab82362-9c37-11e7-a858-000c29c1025c:69373'/*!*/;

# at

# :: server id   end_log_pos  CRC32 0xe72ec7fb   Query   thread_id=    exec_time=     error_code=

SET TIMESTAMP=/*!*/;

======================== 在宿主机上执行mysqladmin修改用户密码 ========================

ALTER USER 'mydba'@'192.168.85.1' IDENTIFIED WITH 'mysql_native_password' AS '*12AB8416B918C0EC1528FD04A686AE12D97A4A5D'

/*!*/;

SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

DELIMITER ;

# End of log file

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@ZST1 logs]#

两个ALTER USER对应两次mysqladmin修改用户密码
三、drop user 'mydba'，没有指明host，它删除是哪个用户

mydba@192.168.85.132,3306 [(none)]> drop user 'mydba';

ERROR 1396 (HY000): Operation DROP USER failed for 'mydba'@'%'

mydba@192.168.85.132,3306 [(none)]>

根据错误信息，它删除的是用户'mydba'@'%'

18:17 2018/01/08 补充
四、如何备份用户权限信息
4.1、show create user，得到一致的用户名、密码

mydba@192.168.85.132,3306 [(none)]> show create user 'repl'@'192.168.85.%';

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| CREATE USER for repl@192.168.85.%                                                                                                                                            |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| CREATE USER 'repl'@'192.168.85.%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

迁移到其他实例可以使用相同的用户名、密码
4.2、show grants for，可以得到用户的权限，只需将返回结果保存起来即可。如果user个数较多，建议拼接语句

# 拼接show grants语句

mydba@192.168.85.132,3306 [(none)]> select concat('show grants for ''',user,'''@''',host,''';') from mysql.user;

+------------------------------------------------------+

| concat('show grants for ''',user,'''@''',host,''';') |

+------------------------------------------------------+

| show grants for 'mydba'@'192.168.85.%';              |

| show grants for 'repl'@'192.168.85.%';               |

| show grants for 'mysql.session'@'localhost';         |

| show grants for 'mysql.sys'@'localhost';             |

| show grants for 'root'@'localhost';                  |

+------------------------------------------------------+

5 rows in set (0.01 sec)

# 执行show grants语句得到权限语句

mydba@192.168.85.132,3306 [(none)]> show grants for 'repl'@'192.168.85.%';

+---------------------------------------------------------+

| Grants for repl@192.168.85.%                            |

+---------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.85.%' |

+---------------------------------------------------------+

1 row in set (0.00 sec)

grant语句执行的前提：先创建对用user，或者grant xxx identified by 'pwd'
4.3、mysqlpump备份用户信息

# 使用mysqlpump直接备份用户信息

[root@ZST1 ~]# mysqlpump -h127.0.0. -P3306 -uroot -p --exclude-databases=% --users -A >/data/backup/user_pump_1323306_`date +%Y%m%d`.sql

[root@ZST1 ~]# more /data/backup/user_pump_1323306_`date +%Y%m%d`.sql

...

CREATE USER 'repl'@'192.168.85.%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.85.%';

mysqlpump可以生成create、grant用户语句，mysqldump、mydumper是生成对应的insert语句

码农公寓

相关文章