一、如何拒绝用户从某个精确ip访问数据库
假如在mysql.user表中存在用户'mydba'@'192.168.85.%'，现在想拒绝此用户从某个精确ip访问数据库
# 创建精确ip用户，分配不同的密码
mysql> create user 'mydba'@'192.168.85.1' identified by 'otherpwd';
# 精确匹配，这个ip进来的密码为otherpwd，由于密码不匹配拒绝登录 [email protected],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) [email protected],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)时，修改的是哪个用户的密码
[email protected],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) [email protected],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()为[email protected].%，对应修改'mydba'@'192.168.85.%'的密码
# 前面的mysqladmin在ZST1(192.168.85.132)上执行，对应的current_user()为[email protected].%
[email protected],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()为[email protected]，对应修改'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
# 查看修改后的密码，分别对应是不从客户端修改
[email protected], [(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) [email protected], [(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，它删除是哪个用户
[email protected],3306 [(none)]> drop user 'mydba';
ERROR 1396 (HY000): Operation DROP USER failed for 'mydba'@'%'
[email protected],3306 [(none)]>
根据错误信息，它删除的是用户'mydba'@'%'
18:17 2018/01/08 补充
四、如何备份用户权限信息
4.1、show create user，得到一致的用户名、密码
[email protected],3306 [(none)]> show create user 'repl'@'192.168.85.%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for [email protected].% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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语句
[email protected],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语句得到权限语句
[email protected],3306 [(none)]> show grants for 'repl'@'192.168.85.%';
+---------------------------------------------------------+
| Grants for [email protected].% |
+---------------------------------------------------------+
| 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语句