恢复误删除表黑科技之relay log大法（续）
2024-09-04 22:10:08
Preface
I've stuck twice in my previous experiments in backing up dropped tables.I am still not sure that why I got failure yesterday.Therefore,I decide to do that again with the same steps on another environment.Let's see the details.
Procedure
1. All the operations on master zlm1.
//Check data at first.
zlm@192.168.56.100: [sysbench]>show tables;
+--------------------+
| Tables_in_sysbench |
+--------------------+
| sbtest1 |
| sbtest2 |
| sbtest3 |
| sbtest4 |
| sbtest5 |
+--------------------+
rows in set (0.00 sec) zlm@192.168.56.100: [sysbench]>select count(*) from sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec) zlm@192.168.56.100: [sysbench]>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
| mysql-bin. | |
+------------------+-----------+
rows in set (0.01 sec) zlm@192.168.56.100: [sysbench]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin. | | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:- |
+------------------+----------+--------------+------------------+-------------------------------------------------+
row in set (0.00 sec) //Generate a Xtrabackup then send it to the slave zlm2.
[root@zlm1 :: /data/backup]
#innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup [root@zlm1 :: /data/backup]
#scp -r --31_16--/ zlm2:/data/backup/ //Continue to do some operations(DML).
zlm@192.168.56.100: [sysbench]>delete from sbtest5 limit ;
Query OK, rows affected (0.19 sec) zlm@192.168.56.100: [sysbench]>delete from sbtest5 limit ;
Query OK, rows affected (0.04 sec) //Drop the table.
zlm@192.168.56.100: [sysbench]>drop table sbtest5;
Query OK, rows affected (0.04 sec) zlm@192.168.56.100: [sysbench]>flush logs;
Query OK, rows affected (0.05 sec) zlm@192.168.56.100: [sysbench]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin. | | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:- |
+------------------+----------+--------------+------------------+-------------------------------------------------+
row in set (0.00 sec) //Shutdown the master.
zlm@192.168.56.100: [sysbench]>exit
Bye [root@zlm1 :: ~]
#mysqladmin shutdown //Send the binlogs which contain the operations above to slave zlm2 either.
[root@zlm1 :: ~]
#cd /data/mysql/mysql3306/logs [root@zlm1 :: /data/mysql/mysql3306/logs]
#ls -l
total
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.
-rw-r----- mysql mysql Jul : mysql-bin.index [root@zlm1 :: /data/mysql/mysql3306/logs]
#scp mysql-bin.{,} zlm2:/data/backup
mysql-bin. % 933KB .6KB/s :
mysql-bin. % .2KB/s :
2. Operations on slave zlm2.
//Prepare to restore the backup of Xtrabackup.
[root@zlm2 :: /data/backup]
#innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/--31_16--/ [root@zlm2 :: /data/mysql/mysql3306/data]
#ls -l
total
-rw-r----- mysql mysql Jul : auto.cnf
-rw-r----- mysql mysql Jul : error.log
-rw-r----- mysql mysql Jul : ib_buffer_pool
-rw-r----- mysql mysql Jul : ibdata1
-rw-r----- mysql mysql Jul : ib_logfile0
-rw-r----- mysql mysql Jul : ib_logfile1
-rw-r----- mysql mysql Jul : ib_logfile2
-rw-r----- mysql mysql Jul : master.info
drwxr-x--- mysql mysql Jul : mrbs
drwxr-x--- mysql mysql Jul : mysql
drwxr-x--- mysql mysql Jul : performance_schema
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.index
-rw-r----- mysql mysql Jul : relay-log.info
-rw-r----- mysql mysql Jul : slow.log
drwxr-x--- mysql mysql Jul : sys
drwxr-x--- mysql mysql Jul : sysbench
-rw-r----- mysql mysql Jul : xtrabackup_binlog_pos_innodb
-rw-r----- mysql mysql Jul : xtrabackup_info
-rw-r----- mysql mysql Jul : xtrabackup_master_key_id [root@zlm2 :: /data/mysql/mysql3306/data]
#ps aux|grep mysqld
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld [root@zlm2 :: /data/mysql/mysql3306/data]
#rm -rf * //Copy back the datafiles.
[root@zlm2 :: /data/backup]
#innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/--31_16--/ [root@zlm2 :: /data/mysql/mysql3306/data]
#ls -l
total
-rw-r----- root root Jul : ib_buffer_pool
-rw-r----- root root Jul : ibdata1
-rw-r----- root root Jul : ib_logfile0
-rw-r----- root root Jul : ib_logfile1
-rw-r----- root root Jul : ib_logfile2
-rw-r----- root root Jul : ibtmp1
drwxr-x--- root root Jul : mrbs
drwxr-x--- root root Jul : mysql
drwxr-x--- root root Jul : performance_schema
drwxr-x--- root root Jul : sys
drwxr-x--- root root Jul : sysbench
-rw-r----- root root Jul : xtrabackup_binlog_pos_innodb
-rw-r----- root root Jul : xtrabackup_info
-rw-r----- root root Jul : xtrabackup_master_key_id [root@zlm2 :: /data/mysql/mysql3306/data]
#sh /root/mysqld.sh [root@zlm2 :: /data/mysql/mysql3306/data]
#mysql
ERROR (HY000): Can't connect to MySQL server on '192.168.56.101' (111) [root@zlm2 :: /data/mysql/mysql3306/data]
#chown -R mysql.mysql * [root@zlm2 :: /data/mysql/mysql3306/data]
#sh /root/mysqld.sh [root@zlm2 :: /data/mysql/mysql3306/data]
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. zlm@192.168.56.101: [(none)]>select count(*) from sysbench.sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.03 sec) //Continue to restore the incremental data with sql_thread.
[root@zlm2 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_16--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin. [root@zlm2 :: /data/backup]
#for i in $(ls mysql-bin.*)
> do
> ext=$(echo $i | cut -d'.' -f2);
> cp $i relay-bin.$ext;
> done [root@zlm2 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_16--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r----- root root Jul : relay-bin. [root@zlm2 :: /data/backup]
#ls ./relay-bin.* > relay-bin.index [root@zlm2 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_16--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r--r-- root root Jul : relay-bin.index [root@zlm2 :: /data/backup]
#cat relay-bin.index
./relay-bin.
./relay-bin. [root@zlm2 :: /data/backup]
#chown mysql.mysql relay* [root@zlm2 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_16--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r--r-- mysql mysql Jul : relay-bin.index [root@zlm2 :: /data/backup]
#cp relay* /data/mysql/mysql3306/data [root@zlm2 :: /data/backup]
#cd /data/mysql/mysql3306/data [root@zlm2 :: /data/mysql/mysql3306/data]
#ls -l
total
-rw-r----- mysql mysql Jul : auto.cnf
-rw-r----- mysql mysql Jul : error.log
-rw-r----- mysql mysql Jul : ib_buffer_pool
-rw-r----- mysql mysql Jul : ibdata1
-rw-r----- mysql mysql Jul : ib_logfile0
-rw-r----- mysql mysql Jul : ib_logfile1
-rw-r----- mysql mysql Jul : ib_logfile2
-rw-r----- mysql mysql Jul : ibtmp1
-rw-r----- mysql mysql Jul : innodb_status.
-rw-r----- mysql mysql Jul : innodb_status.
drwxr-x--- mysql mysql Jul : mrbs
drwxr-x--- mysql mysql Jul : mysql
-rw-r----- mysql mysql Jul : mysql.pid
drwxr-x--- mysql mysql Jul : performance_schema
-rw-r----- root root Jul : relay-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r--r-- root root Jul : relay-bin.index
-rw-r----- mysql mysql Jul : slow.log
drwxr-x--- mysql mysql Jul : sys
drwxr-x--- mysql mysql Jul : sysbench
-rw-r----- mysql mysql Jul : xtrabackup_binlog_pos_innodb
-rw-r----- mysql mysql Jul : xtrabackup_info
-rw-r----- mysql mysql Jul : xtrabackup_master_key_id [root@zlm2 :: /data/mysql/mysql3306/data]
#chown mysql.mysql relay* [root@zlm2 :: /data/mysql/mysql3306/data]
#ls -l
total
-rw-r----- mysql mysql Jul : auto.cnf
-rw-r----- mysql mysql Jul : error.log
-rw-r----- mysql mysql Jul : ib_buffer_pool
-rw-r----- mysql mysql Jul : ibdata1
-rw-r----- mysql mysql Jul : ib_logfile0
-rw-r----- mysql mysql Jul : ib_logfile1
-rw-r----- mysql mysql Jul : ib_logfile2
-rw-r----- mysql mysql Jul : ibtmp1
-rw-r----- mysql mysql Jul : innodb_status.
-rw-r----- mysql mysql Jul : innodb_status.
drwxr-x--- mysql mysql Jul : mrbs
drwxr-x--- mysql mysql Jul : mysql
-rw-r----- mysql mysql Jul : mysql.pid
drwxr-x--- mysql mysql Jul : performance_schema
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r--r-- mysql mysql Jul : relay-bin.index
-rw-r----- mysql mysql Jul : slow.log
drwxr-x--- mysql mysql Jul : sys
drwxr-x--- mysql mysql Jul : sysbench
-rw-r----- mysql mysql Jul : xtrabackup_binlog_pos_innodb
-rw-r----- mysql mysql Jul : xtrabackup_info
-rw-r----- mysql mysql Jul : xtrabackup_master_key_id zlm@192.168.56.101: [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin. | | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:- |
+------------------+----------+--------------+------------------+-------------------------------------------------+
row in set (0.00 sec) zlm@192.168.56.101: [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=,master_host='xxx';
Query OK, rows affected (0.01 sec) zlm@192.168.56.101: [(none)]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: xxx
Master_User:
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos: //No data here yet.
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: /data/mysql/mysql3306/data/master.info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: //No data here.
Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) [root@zlm2 :: /data/backup]
#ls -l
total
drwxr-x--- root root Jul : --31_16--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r--r-- mysql mysql Jul : relay-bin.index //Find out the end position to set util clause.
[root@zlm2 :: /data/backup]
#mysqlbinlog --base64-output=decode-rows relay-bin. > .log [root@zlm2 :: /data/backup]
#tail - .log
# :: server id end_log_pos CRC32 0xe3e63622 Delete_rows: table id flags: STMT_END_F
# at
# :: server id end_log_pos CRC32 0x84cf5136 Xid =
COMMIT/*!*/;
# at //This position is which I need to set until to.
# :: server id end_log_pos CRC32 0xef03ef84 GTID last_committed= sequence_number= rbr_only=no
SET @@SESSION.GTID_NEXT= '2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699'/*!*/;
# at
# :: server id end_log_pos CRC32 0x0309b10f Query thread_id= exec_time= error_code=
use `sysbench`/*!*/;
SET TIMESTAMP=/*!*/;
DROP TABLE `sbtest5` /* generated by server */
/*!*/;
# at
# :: server id end_log_pos CRC32 0xc1ca182a Rotate to mysql-bin. pos:
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; zlm@192.168.56.101: [(none)]>start slave sql_thread until relay_log_file='relay-bin.000103',relay_log_pos=;
Query OK, rows affected (0.00 sec) zlm@192.168.56.101: [(none)]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: xxx
Master_User:
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos: //This is the terminal relay log position I set just now.
Relay_Log_Space:
Until_Condition: Relay
Until_Log_File: relay-bin.
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: /data/mysql/mysql3306/data/master.info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) zlm@192.168.56.101: [(none)]>select count(*) from sysbench.sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
3. The supplemented experiment.
//Shudown the instance.
[root@zlm2 :: /data/mysql/mysql3306/data]
#ps aux|grep mysqld
mysql 0.0 20.0 pts/ Sl : : mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
root 0.0 0.0 pts/ R+ : : grep --color=auto mysqld [root@zlm2 :: /data/mysql/mysql3306/data]
#mysqladmin shutdown [root@zlm2 :: /data/mysql/mysql3306/data] //Copy back the data one more time(no need to executed with option "--apply-log" first this time).
[root@zlm2 :: /data/backup]
#innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/--31_16--/ //Copy the relay logs to datadir directory again.
[root@zlm2 :: /data/backup]
#ls -l
total
-rw-r--r-- root root Jul : .log
drwxr-x--- root root Jul : --31_16--
-rw-r----- root root Jul : mysql-bin.
-rw-r----- root root Jul : mysql-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r----- mysql mysql Jul : relay-bin.
-rw-r--r-- mysql mysql Jul : relay-bin.index [root@zlm2 :: /data/backup]
#cp relay* /data/mysql/mysql3306/data [root@zlm2 :: /data/mysql/mysql3306/data]
#ls -l
total
-rw-r----- root root Jul : ib_buffer_pool
-rw-r----- root root Jul : ibdata1
-rw-r----- root root Jul : ib_logfile0
-rw-r----- root root Jul : ib_logfile1
-rw-r----- root root Jul : ib_logfile2
-rw-r----- root root Jul : ibtmp1
drwxr-x--- root root Jul : mrbs
drwxr-x--- root root Jul : mysql
drwxr-x--- root root Jul : performance_schema
-rw-r----- root root Jul : relay-bin.
-rw-r----- root root Jul : relay-bin.
-rw-r--r-- root root Jul : relay-bin.index
drwxr-x--- root root Jul : sys
drwxr-x--- root root Jul : sysbench
-rw-r----- root root Jul : xtrabackup_binlog_pos_innodb
-rw-r----- root root Jul : xtrabackup_info
-rw-r----- root root Jul : xtrabackup_master_key_id [root@zlm2 :: /data/mysql/mysql3306/data]
#chown -R mysql.mysql * [root@zlm2 :: /data/mysql/mysql3306/data]
#sh /root/mysqld.sh [root@zlm2 :: /data/mysql/mysql3306/data]
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. zlm@192.168.56.101: [(none)]>select count(*) from sysbench.sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.04 sec) zlm@192.168.56.101: [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin. | | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:- |
+------------------+----------+--------------+------------------+-------------------------------------------------+
row in set (0.00 sec) zlm@192.168.56.101: [(none)]>reset master;
Query OK, rows affected (0.03 sec) zlm@192.168.56.101: [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin. | | | | |
+------------------+----------+--------------+------------------+-------------------+
row in set (0.00 sec) zlm@192.168.56.101: [(none)]>show slave status\G
Empty set (0.00 sec) zlm@192.168.56.101: [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696';
Query OK, rows affected (0.00 sec) zlm@192.168.56.101: [(none)]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------------+
| mysql-bin. | | | | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:- |
+------------------+----------+--------------+------------------+-------------------------------------------------+
row in set (0.00 sec) zlm@192.168.56.101: [(none)]>change master to relay_log_file='relay-bin.000103',relay_log_pos=,master_host='xxx';
Query OK, rows affected (0.01 sec) zlm@192.168.56.101: [(none)]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: xxx
Master_User:
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: None
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: /data/mysql/mysql3306/data/master.info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) zlm@192.168.56.101: [(none)]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715699';
Query OK, rows affected (0.51 sec) zlm@192.168.56.101: [(none)]>show slave status\G
*************************** . row ***************************
Slave_IO_State:
Master_Host: xxx
Master_User:
Master_Port:
Connect_Retry:
Master_Log_File:
Read_Master_Log_Pos:
Relay_Log_File: relay-bin.
Relay_Log_Pos:
Relay_Master_Log_File:
Slave_IO_Running: No
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:
Last_Error:
Skip_Counter:
Exec_Master_Log_Pos:
Relay_Log_Space:
Until_Condition: SQL_BEFORE_GTIDS //This time we use this option to start slave sql_thread.
Until_Log_File:
Until_Log_Pos:
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:
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id:
Master_UUID:
Master_Info_File: /data/mysql/mysql3306/data/master.info
SQL_Delay:
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count:
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:-
Auto_Position:
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
row in set (0.00 sec) zlm@192.168.56.101: [(none)]>select count(*) from sysbench.sbtest5;
+----------+
| count(*) |
+----------+
| |
+----------+
row in set (0.00 sec)
Ultimately,the incremental data comes back again.The experiment was finished successfully.
最新文章
- Python中的join()函数的用法
- 安装R语言扩展包diveRsity-1
- mvc通过ActionFilterAttribute做登录检查
- Servlet/JSP-08 EL表达式
- 迭代器iterator（三）：Listlterator遍历arraylist，并用逆序输出结果
- Linux命令行提示符设置
- linux磁盘清理方法 Linux 下垃圾清理工具 BleachBit
- checkbox操作
- Android 之 资源文件的介绍及使用
- IOS obj-c、c、c++混编
- (step5.1.3)hdu 1213( How Many Tables——1213)
- Ubuntu超好用软件：剪贴板
- LeetCode 674. Longest Continuous Increasing Subsequence （最长连续递增序列）
- Xamarin Android Gestures详解
- Java基础学习笔记七 Java基础语法之继承和抽象类
- 看完这篇Linux基本的操作就会了
- IntelliJ IDEA 使用 Git 并将 GitHub 作为远程代码仓库
- 部署crm项目
- Mac使用Xcode配置openGL
- MySQL出现Access denied for user ‘root’@’localhost’ (using password:YES)