一、Slave_SQL_Running错误
在执行SQL语句的时候发生错误
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.100
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1927
Relay_Log_File: relay-log.000034
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_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: 1062
Last_Error: Error 'Duplicate entry '6' for key 'PRIMARY'' on query. Default database: 'zxy'. Query: 'insert into zxy values (6,'e')'
Skip_Counter: 0
Exec_Master_Log_Pos: 1717
Relay_Log_Space: 1243
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '6' for key 'PRIMARY'' on query. Default database: 'zxy'. Query: 'insert into zxy values (6,'e')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 56f00bc3-a8c3-11eb-b9ff-000c29a4a77e
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 220514 13:03:48
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
解决方式一：跳过当前错误
跳过当前错误，适用于少数误差的情况
## 先关闭slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
## 通过命令跳过当前错误语句，sql_slave_skip_counter可以选择跳过多少步
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
## 启动slave
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
## 当前已正常
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.100
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 1927
Relay_Log_File: relay-log.000035
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
解决方式二：重新同步从库
主从之间出现大量的数据不一致，为了快速的恢复业务，对从库进行重新同步
1. 从库：重置从库的同步设置，清除该数据库
## 停止slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
## 重置slave
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
## 删除需要同步的数据库
mysql> drop database zxy;
Query OK, 7 rows affected (0.19 sec)
2. 主库：重置主库同步设置，锁定主库，备份主库
通过show master status查看，如果业务数据一直在变化，那File和Position也会一直变化
这种情况下，需要等待业务数据少的时候，锁库处理
或者主从不一致紧急的情况下，通知业务暂停，进行锁库处理
## 通过show master status查看，如果业务数据一直在变化，那File和Position也会一直变化
## 这种情况下，需要等待业务数据少的时候，锁库处理
## 或者主从不一致紧急的情况下，通知业务暂停，进行锁库处理
## 1.查看主机状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 2563 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
## 2.重置主库同步配置
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
## 再次查看主机状态，发现File和Position都重置了
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
## 3.锁定主库，只能查Read
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
## 锁定主库后，File和Position也就不会再变化
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
## 4.备份指定数据库，指定IP，Port，数据库账号密码,目标数据库名
[root@hadoop mysql]# mysqldump -h 192.168.130.100 -P 3306 -uroot -p111213 zxy > ./master-zxy.sql
## 5.scp将主机的master-zxy.sql备份文件，发送到从机的/var/lib/mysql目录
[root@hadoop mysql]# scp master-zxy.sql root@192.168.130.101:/var/lib/mysql
master-zxy.sql 100% 4571 3.9MB/s 00:00
[root@hadoop mysql]#
## 6.解锁主库
## 6.1 解锁前，插入一条数据会报错
mysql> insert into zxy values(10,'c'); ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
## 6.2 解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
## 6.3 解锁后测试
mysql> insert into zxy values(11,'j');
Query OK, 1 row affected (0.00 sec)
3. 从库：恢复数据
恢复方式一：进入MySQL命令行Source命令
## 创建数据库
mysql> create database zxy;
Query OK, 1 row affected (0.00 sec)
## 进入数据库
mysql> use zxy;
Database changed
## 恢复数据
mysql> source /var/lib/mysql/master-zxy.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
恢复方式二：Linux命令行mysql命令
## 创建数据库
mysql> create database zxy;
Query OK, 1 row affected (0.00 sec)
## 进入数据库
mysql> use zxy;
Database changed
## mysql命令与mysqldump命令相互配合
[root@hadoop mysql]# mysql -h192.168.130.101 -P3306 -uroot -p111213 zxy < master-zxy.sql
Warning: Using a password on the command line interface can be insecure.
[root@hadoop mysql]#
4. 从库：修改同步配置
MASTER_HOST为主机IP，MASTER_PORT为主机MySQL端口，MASTER_USER是主机为从机同步创建的用户，MASTER_PASSWORD是同步用户对应的而密码，MASTER_LOG_FILE是在主机通过show master status中File字段对应的内容，MASTER_LOG_POS在主机通过show master status中Position字段对应的内容
mysql> CHANGE MASTER TO MASTER_HOST='192.168.130.100',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='slave',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=330;
操作
## 更改配置
mysql> CHANGE MASTER TO MASTER_HOST='192.168.130.100',
-> MASTER_PORT=3306,
-> MASTER_USER='slave',
-> MASTER_PASSWORD='slave',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=330;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
## 启动从机
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
## 查看从机状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.130.100
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 330
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
二、语句记录功能preformance_schema
1. 查看语句记录功能开启状态
## 在preformance_schema中语句时间记录表中针对每一条语句的执行状态都记录了较为详细的信息，其中就包含了执行错误的信息
## events_statements_current，默认只记录每个线程最近的一条SQL信息
## events_statements_history，默认记录每个线程最近的十条SQL信息
## events_statements_history_long，默认记录每个线程最近的10000条SQL信息
mysql> select * from performance_schema.setup_consumers where name like 'events_statements%';
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | NO |
+--------------------------------+---------+
3 rows in set (0.09 sec)
2. 开启语句记录功能
mysql> update performance_schema.setup_consumers set enabled = 'YES' where name in ('events_statements_history','events_statements_history_long');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from performance_schema.setup_consumers where name like 'events_statements%';
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_statements_current | YES |
| events_statements_history | YES |
| events_statements_history_long | YES |
+--------------------------------+---------+
3 rows in set (0.00 sec)
3. 根据错误号查找详情
mysql> select * from performance_schema.events_statements_history_long where mysql_errno=1062\G;
*************************** 1. row ***************************
THREAD_ID: 28
EVENT_ID: 2
END_EVENT_ID: 2
EVENT_NAME: statement/sql/insert
SOURCE: log_event.cc:4896
TIMER_START: 5512737119549000
TIMER_END: 5512737245104000
TIMER_WAIT: 125555000
LOCK_TIME: 49000000
SQL_TEXT: insert into zxy values (6,'e')
DIGEST: 70f8ef4bc1dc9d8a7c6020e9dbb0d2c7
DIGEST_TEXT: INSERT INTO `zxy` VALUES (...)
CURRENT_SCHEMA: zxy
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 1062
RETURNED_SQLSTATE: 23000
MESSAGE_TEXT: Duplicate entry '6' for key 'PRIMARY'
ERRORS: 1
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
1 row in set (0.00 sec)
