mysql> show replica status\G *************************** 1. row *************************** Replica_IO_State: Waiting for source to send event Source_Host: 10.154.24.73 Source_User: repl Source_Port: 3306 Connect_Retry: 60 Source_Log_File: master-binlog.000216 Read_Source_Log_Pos: 824 Relay_Log_File: bigdata74-relay-bin.000621 Relay_Log_Pos: 488351486 Relay_Source_Log_File: master-binlog.000208 Replica_IO_Running: Yes Replica_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1410 Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'cd8a108b-d01d-11f0-a603-fa163e226e71:790' at source log master-binlog.000208, end_log_pos 488351485. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Skip_Counter: 0 Exec_Source_Log_Pos: 488351261 Relay_Log_Space: 11089131865 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Source: NULL Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1410 Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'cd8a108b-d01d-11f0-a603-fa163e226e71:790' at source log master-binlog.000208, end_log_pos 488351485. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. Replicate_Ignore_Server_Ids: Source_Server_Id: 1 Source_UUID: cd8a108b-d01d-11f0-a603-fa163e226e71 Source_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Replica_SQL_Running_State: Source_Retry_Count: 10 Source_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 251223 15:02:32 Source_SSL_Crl: Source_SSL_Crlpath: Retrieved_Gtid_Set: cd8a108b-d01d-11f0-a603-fa163e226e71:1-937 Executed_Gtid_Set: cd8a108b-d01d-11f0-a603-fa163e226e71:1-789 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Source_TLS_Version: Source_public_key_path: Get_Source_public_key: 0 Network_Namespace: 1 row in set (0.00 sec)
主从同步失败:
Replica_IO_Running: Yes Replica_SQL_Running: No
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘cd8a108b-d01d-11f0-a603-fa163e226e71:790’ at source log master-binlog.000208, end_log_pos 488351485. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
主库执行GRANT ALL PRIVILEGES ON hie.* TO ‘hie‘@’%’’ WITH GRANT OPTION命令，从库执行失败.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
主库 mysql> show grants for hie@'%'; +----------------------------------------------+ | Grants for hie@% | +----------------------------------------------+ | GRANT USAGE ON *.* TO `hie`@`%` | | GRANT ALL PRIVILEGES ON `hie`.* TO `hie`@`%` | +----------------------------------------------+ 2 rows in set (0.00 sec)
从库: mysql> show grants for hie@'%'; +---------------------------------+ | Grants for hie@% | +---------------------------------+ | GRANT USAGE ON *.* TO `hie`@`%` | +---------------------------------+ 1 row in set (0.00 sec)
由于Mysql8默认不允许 root@‘localhost’用户创建带有with grant option权限的帐号，故需要修改帐号:
1 2
UPDATE user SET Host='%' WHERE User='root' AND Host='localhost'; FLUSH PRIVILEGES;
或者跳过授权表，授权root@’localhost’ with grant option权限:
1 2 3 4 5 6 7 8 9
(1)修改参数 vi /etc/my.cnf skip-grant-tables
(2)重启数据库 /etc/init.d/mysql restart
(3)重新授权 GRANT ALL PRIVILEGES ON *`.* TO root'@'localhost'' WITH GRANT OPTION
