There is a system with ROW-based replication.
Yesterday i have executed a heavy statement on my master accidently and found my slaves far behind master. I have interrupted the query on master, but it was still running on slaves.
So i got my slaves 15 hours behind master.
I have already tried to step over one position by resetting slave and increasing MASTER_LOG_POS, but with no luck: position wasn’t found, because relay log wasn’t read further than a heavy query event.
Read_Master_Log_Pos == Exec_Master_Log_Pos
- Is there any way to skip the heavy query?
(i don’t care about data that has to be changed by query) - Is there a way to kill a query on a slave taken from relay log?
- Is there a way to roll the slaves back in 1 position, remove the
event from master bin-log and resume the replication?
asked Jul 17, 2013 at 13:47
Try the following on the slave:
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
This will stop the slaves threads and skips the next event from the master. This you normally use when you have problems with statements to skip over them.
Also read following part of the mysql docs: set-global-sql-slave-skip-counter
answered Jul 17, 2013 at 14:10
1
First explore the binary logs on the master to find the SQL statement that is causing the issue, using the following on the master:
SHOW BINLOG EVENTS IN 'mysql-bin.000XXX' LIMIT 100;
Then set the slave to only sync up to the statement before that:
STOP SLAVE;
START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos;
When you want it to carry on replication after the bad statement (warning, this can be dangerous if the statement changed data) you can tell the slave to continue from a specific point in the masters log. To do this get the position using the first command on the master, then set the slave to go:
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000663', MASTER_LOG_POS=4;
START SLAVE;
answered Jul 8, 2014 at 22:07
A.BadgerA.Badger
4,2591 gold badge25 silver badges18 bronze badges
0
For those on Amazon RDS MySQL you can skip one error at a time on the slave with:
CALL mysql.rds_skip_repl_error;
No need to stop replication before running this.
answered Mar 18, 2016 at 18:38
Sean FaheySean Fahey
1,8283 gold badges25 silver badges36 bronze badges
I found the starting the io_thread first
start slave io_thread;
and checking the relay logs with the command
SHOW RELAYLOG EVENTS IN 'mysql-bin.000XXX' LIMIT 100;
This saved me a lot of time.
answered Feb 8, 2017 at 18:00
RodoRodo
1,5581 gold badge14 silver badges11 bronze badges
You can set a skip counter as follow:
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
To see the processlist:
mysql> show [full] processlist;
kill "number from first col";
Start slave from specific position:
START SLAVE UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
Ref: http://dev.mysql.com/doc/refman/5.0/en/start-slave.html
answered Jul 17, 2013 at 14:08
mysql master-slave replication, often encounter errors and lead to slave end replication interruption, this time generally requires manual intervention, skip errors to continue
There are two ways to skip errors:
1.1 Skip a specified number of transactions:
mysql>stop slave; mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #Skip a transaction mysql>start slave;
1.2 Modify mysql configuration file to skip all errors or specified types of errors through the slave_skip_errors parameter
vi /etc/my.cnf [mysqld] #slave-skip-errors=1062,1053,1146 #Skip the error of the specified error no type #slave-skip-errors=all #Skip all errors
2 cases
Let’s simulate an error scenario
Environment (a configured master-slave replication environment)
master database IP: 192.168.247.128
Slve database IP: 192.168.247.130
mysql version: 5.6.14
binlog-do-db = mydb
Execute the following statement on master:
mysql>use mysql; mysql>create table t1 (id int); mysql>use mydb; mysql>insert into mysql.t1 select 1;
View replication status on slave
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.247.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000017
Read_Master_Log_Pos: 2341
Relay_Log_File: DBtest1-relay-bin.000011
Relay_Log_Pos: 494
Relay_Master_Log_File: mysql-bin.000017
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: 1146
Last_Error: Error 'Table 'mysql.t1' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mysql.t1 select 1'
Skip_Counter: 0
Exec_Master_Log_Pos: 1919
Relay_Log_Space: 1254
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: 1146
Last_SQL_Error: Error 'Table 'mysql.t1' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mysql.t1 select 1'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
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: 131210 21:37:19
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
As can be seen from the results, Read_Master_Log_Pos: 2341, Exec_Master_Log_Pos: 1919 error Last_SQL_Error: Error’Table’mysql.t1’does’t exist’ on query.
Because only binlog is recorded for mydb, errors occur when tables in other databases are operated on the mydb library but do not exist on slave.
Let’s look at the transaction content in the binlog, where a row represents a transaction.
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000017' from 1919G
*************************** 1. row ***************************
Log_name: mysql-bin.000017
Pos: 1919
Event_type: Query
Server_id: 1
End_log_pos: 1999
Info: BEGIN
*************************** 2. row ***************************
Log_name: mysql-bin.000017
Pos: 1999
Event_type: Query
Server_id: 1
End_log_pos: 2103
Info: use `mydb`; insert into mysql.t1 select 1
*************************** 3. row ***************************
Log_name: mysql-bin.000017
Pos: 2103
Event_type: Xid
Server_id: 1
End_log_pos: 2134
Info: COMMIT /* xid=106 */
*************************** 4. row ***************************
Log_name: mysql-bin.000017
Pos: 2134
Event_type: Query
Server_id: 1
End_log_pos: 2213
Info: BEGIN
*************************** 5. row ***************************
Log_name: mysql-bin.000017
Pos: 2213
Event_type: Query
Server_id: 1
End_log_pos: 2310
Info: use `mydb`; insert into t1 select 9
*************************** 6. row ***************************
Log_name: mysql-bin.000017
Pos: 2310
Event_type: Xid
Server_id: 1
End_log_pos: 2341
Info: COMMIT /* xid=107 */
6 rows in set (0.00 sec)
From the above results, we need to skip two transactions (Pos: 1999 insert, Pos: 2103 commit)
Skip operation:
mysql>stop slave;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; Skip a transaction
mysql>start slave;
mysql> show slave statusG
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: mydb
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.247.128
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000017
Read_Master_Log_Pos: 3613
Relay_Log_File: DBtest1-relay-bin.000018
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3613
Relay_Log_Space: 458
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: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: f0f7faf6-51a8-11e3-9759-000c29eed3ea
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.01 sec)
The replication status is normal.
Есть два сервера баз данных, на разных интернет площадках. На каждом сервере крутится свой экземпляр MySQL. Один из серверов настроен как Slave. Недавно заметил что репликация на Slave остановилась из-за ошибки. Как эту репликацию починить или перезапустить, что бы снова начали синхронизироваться данные от master к slave?
mysql> show slave status G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xx.xx.xx
Master_User: buildbot
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.024536
Read_Master_Log_Pos: 35489509
Relay_Log_File: mysqld-relay-bin.062147
Relay_Log_Pos: 32575097
Relay_Master_Log_File: mysql-bin.014876
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: 1050
Last_Error: Error 'Table 'q2a' already exists' on query. Default database: 'dbname'. Query: 'CREATE TABLE `checklist` (
`checklist_id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(768) NOT NULL,
`url` varchar(512) NOT NULL,
`active` bit(1) NOT NULL,
`insert_date` datetime NOT NULL,
`xcred` int(11) NOT NULL,
PRIMARY KEY (`checklist_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1'
Skip_Counter: 0
Exec_Master_Log_Pos: 32574932
Relay_Log_Space: 6766019525
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: 2013
Last_IO_Error: error reconnecting to master 'user@xx.xx.xx.xx:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 1050
Last_SQL_Error: Error 'Table 'q2a' already exists' on query. Default database: 'dbname'. Query: 'CREATE TABLE `checklist` (
`checklist_id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(768) NOT NULL,
`url` varchar(512) NOT NULL,
`active` bit(1) NOT NULL,
`insert_date` datetime NOT NULL,
`xcred` int(11) NOT NULL,
PRIMARY KEY (`checklist_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1'MySQL GTID replication error can happen unexpectedly for various reasons and as often as the classic master slave, circular or multi-master replication but in this short tutorial we will learn, step by step, how to skip MySQL GTID transaction error in order to keep in sync our MySQL slave server. Before we begin we need to say that all below steps were tested in a master-slave setup using MySQL 5.6 and MySQL 5.7, we think that this MySQL skipping transaction method detailed below can be easily applied even for newer MySQL versions like 8+. Please note that all below operations were taken on the slave server only, no master queries or changes were needed.
Table of Contents
MySQL GTID Replication Error
This tutorial about MySQL GTID Replication Error contains the next sections:
Show MySQL Slave Status via CLI
Stop MySQL Slave
Fix MySQL GTID Replication Error
Skip MySQL GTID Replication Error
Start MySQL Slave
Check MySQL Slave Replication Status
Show MySQL Slave Status via CLI
Let’s start by checking our MySQL slave server status via CLI by executing show slave status G query like shown in the example below:
mysql> SHOW SLAVE STATUS G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.1.1.1
Master_User: dbrepuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000706
Read_Master_Log_Pos: 374830771
Relay_Log_File: dbm-relay-bin.000713
Relay_Log_Pos: 959223458
Relay_Master_Log_File: bin.000691
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: 1396
Last_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
Skip_Counter: 0
Exec_Master_Log_Pos: 959223257
Relay_Log_Space: 16698942535
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: 1396
Last_SQL_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bdf3bf63-0cc9-11e8-89e8-000d3a365fa6
Master_Info_File: /mnt/mysql-data/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: 180628 06:29:51
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:58869595-59928201
Executed_Gtid_Set: b2c84f0c-5dcb-11e8-a550-005056847b4c:1-13, bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:52044900-59506053
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
We can clearly see on the row called Slave_SQL_Running that our slave isn’t executing any new SQL queries received from the master due a SQL query error like shown on the row Last_SQL_Error.
Stop MySQL Slave
Our next step is to stop MySQL slave by executing a SQL query in order to fix the replication error, we won’t stop mysql/mysqld service (daemon) for this we just need to stop the process that handles the replication. Let’s run the SQL query that will stop the slave process for us:
mysql> STOP SLAVE;
By executing once again the SQL query show slave statusG we should be able now to see that our slave is stopped and no replication is happening between master and slave:
mysql> SHOW SLAVE STATUS G
...
Slave_IO_Running: No
Slave_SQL_Running: No
...
Fix MySQL GTID Replication Error
Knowing now that our MySQL replication is stopped let’s take a closer look at the error message.
Last_SQL_Error: Error 'Operation ALTER USER failed for 'dummyuser'@'10.1.%.%'' on query. Default database: 'mysql'. Query: 'ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C''
The error message says that the ALTER USER query failed for the user named dummyuser. Let’s check our mysql.user table to identify the issue with this query.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select user, host, authentication_string from user where user like '%dummyuser%';
+----------------+------+-------------------------------------------+
| user | host | authentication_string |
+----------------+------+-------------------------------------------+
| dummyuser | 10.% | *1B2319A2556FC59343FE08BC9027EFF385AD7A1D |
+----------------+------+-------------------------------------------+
1 row in set (0.00 sec)
We can now see the differences between the SQL query that’s failing and the actual data that’s in our table. First issue that we notice is present in the host column, the error shows users host as being 10.1.%.% but in our table we have 10.%. The second issue is with authentication_string column, we can see that the password has been updated and it doesn’t match anymore. The conclusion here is that the password for our dummyuser has been updated on the master. On the slave server we didn’t had that record to match the criteria, meaning that someone else updated the record on the slave manually by replacing the host entry with 10.%.
To avoid such situations make sure that you never perform any changes on slave, all SQL changes must be carried out on the master server as this is the data source for our slave.
Let’s fix this error by updating our record on the slave server.
mysql> UPDATE user SET host='10.1.%.%' WHERE host='10.%' AND user='dummyuser';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> ALTER USER 'dummyuser'@'10.1.%.%' IDENTIFIED WITH 'mysql_native_password' AS '*1D1219A2256FC58143DF98BA9457EFE385AD7B2C';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Using the queries above we’ve updated our host and authentication_string (hashed password) records now to match with the records from our master server. Now we have the same data on the slave as on the master, no data was lost.
Skip MySQL GTID Replication Error
The classic method for skipping master-slave errors won’t work in this particular case as our replication is GTID.
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
The error above is expected as we’re running a MySQL GTID replication setup.
Let’s have a look on the last transaction executed on the slave that failed:
Executed_Gtid_Set: b2c84f0c-5dcb-11e8-a550-005056847b4c:1-13, bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:52044900-59506053
We know now that the transaction number 59506053 coming from our master server having the ID bdf3bf63-0cc9-11e8-89e8-000d3a365fa6 failed.
Having now the record fixed manually on the slave we can carry on and insert an empty transaction in order to bypass the error. We’ll need to increment the transaction via a SQL query like shown below in order to bypass the error.
mysql> SET GTID_NEXT='bdf3bf63-0cc9-11e8-89e8-000d3a365fa6:59506054';
Now we’ll have to commit our change:
mysql> BEGIN; COMMIT; SET GTID_NEXT='AUTOMATIC';
Start MySQL Slave
Having everything in place like transaction number incremented and change committed we can try to start our GTID slave replication back with the next SQL query:
mysql> START SLAVE;
A successful show slave statusG will look like this:
mysql> SHOW SLAVE STATUS G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Check MySQL Slave Replication Status
We know that our slave is running now, receiving data from the master and we can check how far the slave is behind the master like this:
mysql> SHOW SLAVE STATUS G
...
Seconds_Behind_Master: 168356
...
Once the Seconds_Behind_Master value goes down to 0 seconds then we can say that our slave is fully synced.
mysql> SHOW SLAVE STATUS G
...
Seconds_Behind_Master: 0
...