详解 MySQL 5.7 新的权限与安全问题

Access denied for user 'root'@'IP地址' ,是因为相应的主机没有对应的访问权限

--开放权限如下
use mysql;
update user u set u.host = '%' where u.user = 'root' limit 1;
flush privileges;

--查看用户权限
show grants for current_user();

--mysql不推荐通过修改表的方式修改用户密码
INSERT or UPDATE statements for the mysql.user table that refer to literal passwords are logged as is,so you should avoid such statements
--通过客户端sql修改
MariaDB [mysql]>  UPDATE user SET Password = password('123456') WHERE User = 'root' ;
--此时可在binglog中可以看到明文的密码
[root@rudy_01 3306]# mysqlbinlog binlog.000006 --start-position=4224 >/tmp/test.sql
[root@rudy_01 3306]# cat /tmp/test.sql 
SET @@session.collation_database=DEFAULT/*!*/;
UPDATE user SET Password = password('123456') WHERE User = 'root'

--在 mysql 5.7 中 password 字段已经不存在了
mysql> UPDATE user SET Password = password('123456') WHERE User = 'root' ;
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |

--注意出于安全考虑，alter user 时提示更新的是 0 条数据，但实际 password 已更新
mysql> select host,user,authentication_string,password_last_changed from user where user='root' and host='%';
+------+------+-------------------------------------------+-----------------------+
| host | user | authentication_string                     | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
| %    | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 2016-01-08 15:38:13   |
+------+------+-------------------------------------------+-----------------------+
1 row in set (0.04 sec)

--提示更新0条，使用此方法不需要再 flush privileges
If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE,SET PASSWORD, or RENAME USER, 
the server notices these changes and loads the grant tables into memory again immediately.
mysql>  alter user 'root'@'%' identified by '12345678';
Query OK, 0 rows affected (0.00 sec)
--实际已更新
mysql>  select host,user,authentication_string,password_last_changed from user where user='root' and host='%';
+------+------+-------------------------------------------+-----------------------+
| host | user | authentication_string                     | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
| %    | root | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 2016-01-08 15:53:09   |
+------+------+-------------------------------------------+-----------------------+
1 row in set (0.00 sec)

--在binlog中查出的sql如下
[root@rudy mysql]# cat /tmp/test.sql
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'


--mysql对于密码有3种检验策略，默认validate_password_policy为MEDIUM
? LOW policy tests password length only. Passwords must be at least 8 characters long.
? MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
? STRONG policy adds the condition that password substrings of length 4 or longer must not match words

--注意validate_password默认是没有安装的
If the validate_password plugin is not installed, the validate_password_xxx system variables are not available, 
passwords in statements are not checked, and VALIDATE_PASSWORD_STRENGTH() always returns 0.
 
 
--检验密码复杂度 
mysql> select VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)
--查找安装的插件，发现找不到validate_password
mysql> show plugins;
--手动安装
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.04 sec)
--再次检验密码复杂度 
mysql> select VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
|                                        50 |
+-------------------------------------------+

--安装validate_password插件后，就必需符合validate_password_policy的要求，否则语句执行出错
mysql> alter user 'root'@'%' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

1、主从均需重启mysql服务
/etc/init.d/mysqld restart
或者：
sudo service mysqld restart 

2、主配置：增加从机复制账户并授权，以便从机远程登录过来复制 binlog
create user 'replicationUsername'@'%' identified by 'Passwd';
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'name'@'%'  WITH GRANT OPTION;
show master status;

3、从配置：
stop slave;
CHANGE MASTER TO
  MASTER_HOST='110.126.103.126',
  MASTER_USER='replicationUsername',
  MASTER_PASSWORD='Passwd',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
start slave;
show slave status\G

4、Slave_SQL_Running: No
    1.程序可能在slave上进行了写操作
    2.也可能是slave机器重起后，事务回滚造成的.
    3.也可能遇到各种SQL错误导致 SQL 线程中断退出。
    解决方法:
stop slave;
set global sql_slave_skip_counter = 1 ;
start slave;
    之后Slave会和Master去同步 主要看:
从机：show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0，0就是已经同步了
主机：show processlist\G
如果出现Command: Binlog Dump,则说明配置成功.

5、测试：
create database db_test_slave;  
use db_test_slave;  
create table tb_test(id int(3), name varchar(50));  
insert into tb_test values(1,'hello slave');  
show databases;  

6、slave 从零开始同步 master 所有数据库：
（1）master操作:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

candidates=$(echo "show databases" | mysql -uroot -pPASSWD| grep -Ev "^(Database|sys|mysql|performance_schema|information_schema)$")
mysqldump -uroot -pPASSWD --databases ${candidates} --single-transaction > mysqldump.sql
UNLOCK TABLES;

（2）slave操作:
nc -l 12345 < <(cat mysqldump.sql) ##主
nc -n 10.48.186.32 12345 > mysqldump.sql 

STOP SLAVE;
mysql -uroot -pPASSWD < mysqldump.sql

show master status; --主

CHANGE MASTER TO
  MASTER_HOST='10.48.186.32',
  MASTER_USER='birepl',
  MASTER_PASSWORD='PASSWD',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=398062,
  MASTER_CONNECT_RETRY=10;
RESET SLAVE;
start slave;
SHOW SLAVE STATUS\G