1、新版 MySQL 权限问题：
问题：SQL Error (1130): Host '192.168.1.100' is not allowed to connect to this MySQL server
说明所连接的用户帐号没有远程连接的权限，只能在本机(localhost)登录。 需更改 mysql 数据库里的 user表里的 host项：把localhost改称% mysql>use mysql; mysql>update user set host = '%' where user ='root'; mysql>flush privileges; mysql>select 'host','user' from user where user='root'; 旧版本 MySQL 可以 IP 授权与修改密码同时进行： GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 注意0：授权可以用上述语句，但是修改密码新版 MySQL 不能再用 WITH GRANT OPTION 了：Using GRANT statement to modify existing user properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. ALTER USER 'root'@'%' IDENTIFIED BY 'pwd';注意1：MySQL 用户权限标示是 user 和 host 组成的二元组，上述语句需要确保该二元组存在，否则会报错：
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'pwd';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%' mysql> create user 'root'@'%' identified by 'pwd'; Query OK, 0 rows affected (0.00 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)mysql> show grants for current_user();
+---------------------------------------------------------------------+ | Grants for root | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) 如果使用 ALTER USER 'root'@'%' IDENTIFIED WITH sha256_password BY 'pwd'; 指定加密方式则可能在客户端连接时有问题： 注意2：ERROR 2059 (HY000): Authentication plugin 'sha256_password' cannot be loaded: No such file or directory update user set plugin='mysql_native_password' where user = 'root' and host = '%'; update mysql.user set password=PASSWORD("pwd") where User='root'; 注意3：ERROR 1054 (42S22): Unknown column 'password' in 'field list' update mysql.user set authentication_string=password("pwd") where user='root'; 注意4：'PASSWORD' is deprecated and will be removed in a future release. password 即将被废弃，官方不建议用继续使用了，建议使用第1点中的 ALTER USER 语法去管理用户属性。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 privilegesIf 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.sqlSET @@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_passwordmysql> 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 2、主从复制遇到的权限及异常问题
1、主从均需重启mysql服务/etc/init.d/mysqld restart或者：sudo service mysqld restart 2、主配置：增加从机复制账户并授权，以便从机远程登录过来复制 binlogcreate 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\G4、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\GSlave_IO_Running: YesSlave_SQL_Running: YesSeconds_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.sqlUNLOCK TABLES;（2）slave操作:nc -l 12345 < <(cat mysqldump.sql) ##主nc -n 10.48.186.32 12345 > mysqldump.sql STOP SLAVE;mysql -uroot -pPASSWD < mysqldump.sqlshow 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 Refer：
[1] mysql 权限与安全
[2] mysql-5.7主从同步安装配置
[3] CentOS 7 下MySQL 5.7.12主从复制架构配置记录（亲自验证可行）
[4] MySQL 5.7的多源复制
[5] Slave_SQL_Running: No mysql同步故障解决方法
[6] Slave_SQL_Running: No mysql同步故障解决方法
[7] 有没有办法让从msyql主动从零开始在主mysql那里同步数据
[8] How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?
[9] Any option for mysqldump to ignore databases for backup?
[10] mysql的binlog详解
[11] 在什么时候可以调用reset master?
[12] mysql只读模式的设置方法与实验
[13] MySql 创建只读账号