在PXC中重新添加掉线节点

作者: www.9159.com  发布:2019-09-11

 

 

 

 

Preface

Preface

Preface

Preface

 

 

 

 

    When we add a new node into PXC structure,it will estimate the mothed(IST/SST) to transfer data to the new node.It's no wonder that sst will be automatically choosed.What if a dropped node in PXC by accident want to rejoin the cluster?Especially it has been a long period of time after it was dropped out.We should avoid starting up the node directly.As the SST(either xtrabackup-v2 or rsync mode) will be choosed to resynchronize the dropped node instead of IST.Generally speaking,SST may cause to decline the perormance of the whole cluster.It's recommend to use another way to deal with that kind of issue.That is,slave replication.

    Today I'm gonna test how to rescue a dropped table from binlog server based on a full Xtrabackup backup set.

    In my previous blogs,I've demonstrated several mothods of how to rescue a dropped table(or truncated table as well).

    How to rescue a dropped or truncated table online?Dropping or truncating is ddl operation which cannot be flashed back by the populare flashback tools like MyFlash,binlog2mysql,mysqldump_backup,etc.Therefore,the conventional method is restoring the database to a newly initialized instance on another server with backup(physical or logical).Whatif the backup set is rather huge for example the mysqldump backup is more than 200G?It will cost a long time to rescue the dropped table back.Is there an effective way to accomplish the issue?Let's see the tests below.

 

 

  •     full mysqldump backup binlog on master(master was normally running)
  •     full Xtrabackup backup binlog on master(master was normally running)**
  •     full Xtrabackup backup binlog on binlog server( binlog server had acquired binlogs before master crashed.)

 

Framework

Framework

 

Framework

 

 

    Les't see another way to achieve the goal more simply.

 

Hostname IP Port OS Version MySQL Version Xtrabackup version
zlm2 192.168.1.101 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm3 192.168.1.102 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
zlm4 192.168.1.103 3308 CentOS 7.0 PXC 5.7.22 2.4.12 Linux (x86_64)
Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm1 192.168.56.100/3306 master CentOS 7.0 5.7.21 on row
zlm2 192.168.56.101/3306 slave CentOS 7.0 5.7.21 on row
zlm3 192.168.56.102/3306 binlog server CentOS 7.0 5.7.21 on row

 

Hostname IP/Port Identity OS Version MySQL Version GTID Mode Binlog Format
zlm2 192.168.1.101/3306 master CentOS 7.0 5.7.21 on row
zlm3 192.168.1.102/3306 slave CentOS 7.0 5.7.21 on row

 

 

Procedure

 

Procedure

Precedure

 

Precedure

 

 

***Step

 

what's show in error log of initiating SST/IST transfer operation on a new added node.

Step 1: Create binlog server.

  1. Destroy***

**Test1:Rescue a table after dropping it based on a new mysqldump backup.**

 1 2018-08-09T07:23:32.568794 01:00 0 [Note] WSREP: Initiating SST/IST transfer on JOINER side (wsrep_sst_xtrabackup-v2 --role 'joiner' --address '192.168.1.103' --datadir '/data/mysql/mysql3308/data/' --defaults-file '/data/mysql/mysql3308/my3308.cnf' --defaults-group-suffix '' --parent '4433' --binlog '/data/mysql/mysql3308/logs/mysql-bin' )
 2 2018-08-09T07:23:33.225673 01:00 2 [Note] WSREP: Prepared SST/IST request: xtrabackup-v2|192.168.1.103:4444/xtrabackup_sst//1
 3 2018-08-09T07:23:33.225697 01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 3) (Increment: 1 -> 3)
 4 2018-08-09T07:23:33.225704 01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
 5 2018-08-09T07:23:33.225721 01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
 6 2018-08-09T07:23:33.225760 01:00 0 [Note] WSREP: Service thread queue flushed.
 7 2018-08-09T07:23:33.226619 01:00 2 [Note] WSREP: Check if state gap can be serviced using IST
 8 2018-08-09T07:23:33.226638 01:00 2 [Note] WSREP: Local UUID: 00000000-0000-0000-0000-000000000000 != Group UUID: bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 9 2018-08-09T07:23:33.226677 01:00 2 [Note] WSREP: State gap can't be serviced using IST. Switching to SST
10 2018-08-09T07:23:33.226683 01:00 2 [Note] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group state UUID (bd5525ab-9a15-11e8-aa0f-4b830c783fc7): 1 (Operation not permitted)
11      at galera/src/replicator_str.cpp:prepare_for_IST():538. IST will be unavailable.
12 2018-08-09T07:23:33.228003 01:00 0 [Note] WSREP: Member 2.0 (zlm4) requested state transfer from '*any*'. Selected 0.0 (zlm2)(SYNCED) as donor.
13 2018-08-09T07:23:33.228029 01:00 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 22)
14 2018-08-09T07:23:33.228088 01:00 2 [Note] WSREP: Requesting state transfer: success, donor: 0
15 2018-08-09T07:23:33.228108 01:00 2 [Note] WSREP: GCache history reset: 00000000-0000-0000-0000-000000000000:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22
16     2018-08-09T05:23:33.781389Z WSREP_SST: [INFO] Proceeding with SST.........
17     2018-08-09T05:23:33.808866Z WSREP_SST: [INFO] ............Waiting for SST streaming to complete!

 

 

 

 

Check the position on master 

Check the table on master.

Generate the test data with sysbench.

**Check the PXC status at first.**

1 zlm@192.168.56.100:3306 [sysbench]>show master status;
2  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
3 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
4  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
5 | mysql-bin.000098 |      363 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
6  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
7 1 row in set (0.00 sec)
 1 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 2 ERROR 2006 (HY000): MySQL server has gone away
 3 No connection. Trying to reconnect...
 4 Connection id:    4
 5 Current database: sysbench
 6 
 7  -------------------- 
 8 | Tables_in_sysbench |
 9  -------------------- 
10 | sbtest1            |
11 | sbtest2            |
12 | sbtest3            |
13 | sbtest4            |
14 | sbtest5            |
15  -------------------- 
16 5 rows in set (0.00 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
19  ---------- 
20 | count(*) |
21  ---------- 
22 |    10000 |
23  ---------- 
24 1 row in set (0.01 sec)
 1 [root@zlm2 07:30:58 ~/sysbench-1.0/src/lua]
 2 #sysbench oltp_read_write.lua --mysql-host=192.168.1.101 --mysql-port=3306 --mysql-user=zlm --mysql-password=zlmzlm --mysql-db=sysbench --tables=10 --table-size=10000 --mysql-storage-engine=innodb prepare
 3 sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
 4 
 5 Creating table 'sbtest1'...
 6 Inserting 10000 records into 'sbtest1'
 7 Creating a secondary index on 'sbtest1'...
 8 Creating table 'sbtest2'...
 9 Inserting 10000 records into 'sbtest2'
10 Creating a secondary index on 'sbtest2'...
11 Creating table 'sbtest3'...
12 Inserting 10000 records into 'sbtest3'
13 Creating a secondary index on 'sbtest3'...
14 Creating table 'sbtest4'...
15 Inserting 10000 records into 'sbtest4'
16 Creating a secondary index on 'sbtest4'...
17 Creating table 'sbtest5'...
18 Inserting 10000 records into 'sbtest5'
19 Creating a secondary index on 'sbtest5'...
20 Creating table 'sbtest6'...
21 Inserting 10000 records into 'sbtest6'
22 Creating a secondary index on 'sbtest6'...
23 Creating table 'sbtest7'...
24 Inserting 10000 records into 'sbtest7'
25 Creating a secondary index on 'sbtest7'...
26 Creating table 'sbtest8'...
27 Inserting 10000 records into 'sbtest8'
28 Creating a secondary index on 'sbtest8'...
29 Creating table 'sbtest9'...
30 Inserting 10000 records into 'sbtest9'
31 Creating a secondary index on 'sbtest9'...
32 Creating table 'sbtest10'...
33 Inserting 10000 records into 'sbtest10'
34 Creating a secondary index on 'sbtest10'...
35 
36 (zlm@192.168.1.101 3306)[sysbench]>show tables;
37  -------------------- 
38 | Tables_in_sysbench |
39  -------------------- 
40 | sbtest1            |
41 | sbtest10           |
42 | sbtest2            |
43 | sbtest3            |
44 | sbtest4            |
45 | sbtest5            |
46 | sbtest6            |
47 | sbtest7            |
48 | sbtest8            |
49 | sbtest9            |
50  -------------------- 
51 10 rows in set (0.00 sec)
 1 (zlm@192.168.1.101 3308)[(none)]>show global status like '%wsrep%';
 2  ---------------------------------- ---------------------------------------------------------- 
 3 | Variable_name                    | Value                                                    |
 4  ---------------------------------- ---------------------------------------------------------- 
 5 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
 6 | wsrep_protocol_version           | 8                                                        |
 7 | wsrep_last_applied               | 22                                                       |
 8 | wsrep_last_committed             | 22                                                       |
 9 | wsrep_replicated                 | 0                                                        |
10 | wsrep_replicated_bytes           | 0                                                        |
11 | wsrep_repl_keys                  | 0                                                        |
12 | wsrep_repl_keys_bytes            | 0                                                        |
13 | wsrep_repl_data_bytes            | 0                                                        |
14 | wsrep_repl_other_bytes           | 0                                                        |
15 | wsrep_received                   | 21                                                       |
16 | wsrep_received_bytes             | 2733                                                     |
17 | wsrep_local_commits              | 0                                                        |
18 | wsrep_local_cert_failures        | 0                                                        |
19 | wsrep_local_replays              | 0                                                        |
20 | wsrep_local_send_queue           | 0                                                        |
21 | wsrep_local_send_queue_max       | 1                                                        |
22 | wsrep_local_send_queue_min       | 0                                                        |
23 | wsrep_local_send_queue_avg       | 0.000000                                                 |
24 | wsrep_local_recv_queue           | 0                                                        |
25 | wsrep_local_recv_queue_max       | 2                                                        |
26 | wsrep_local_recv_queue_min       | 0                                                        |
27 | wsrep_local_recv_queue_avg       | 0.047619                                                 |
28 | wsrep_local_cached_downto        | 0                                                        |
29 | wsrep_flow_control_paused_ns     | 0                                                        |
30 | wsrep_flow_control_paused        | 0.000000                                                 |
31 | wsrep_flow_control_sent          | 0                                                        |
32 | wsrep_flow_control_recv          | 0                                                        |
33 | wsrep_flow_control_interval      | [ 173, 173 ]                                             |
34 | wsrep_flow_control_interval_low  | 173                                                      |
35 | wsrep_flow_control_interval_high | 173                                                      |
36 | wsrep_flow_control_status        | OFF                                                      |
37 | wsrep_cert_deps_distance         | 0.000000                                                 |
38 | wsrep_apply_oooe                 | 0.000000                                                 |
39 | wsrep_apply_oool                 | 0.000000                                                 |
40 | wsrep_apply_window               | 0.000000                                                 |
41 | wsrep_commit_oooe                | 0.000000                                                 |
42 | wsrep_commit_oool                | 0.000000                                                 |
43 | wsrep_commit_window              | 0.000000                                                 |
44 | wsrep_local_state                | 4                                                        |
45 | wsrep_local_state_comment        | Synced                                                   |
46 | wsrep_cert_index_size            | 0                                                        |
47 | wsrep_cert_bucket_count          | 22                                                       |
48 | wsrep_gcache_pool_size           | 1712                                                     |
49 | wsrep_causal_reads               | 0                                                        |
50 | wsrep_cert_interval              | 0.000000                                                 |
51 | wsrep_ist_receive_status         |                                                          |
52 | wsrep_ist_receive_seqno_start    | 0                                                        |
53 | wsrep_ist_receive_seqno_current  | 0                                                        |
54 | wsrep_ist_receive_seqno_end      | 0                                                        |
55 | wsrep_incoming_addresses         | 192.168.1.101:3308,192.168.1.102:3308,192.168.1.103:3308 |
56 | wsrep_desync_count               | 0                                                        |
57 | wsrep_evs_delayed                |                                                          |
58 | wsrep_evs_evict_list             |                                                          |
59 | wsrep_evs_repl_latency           | 0/0/0/0/0                                                |
60 | wsrep_evs_state                  | OPERATIONAL                                              |
61 | wsrep_gcomm_uuid                 | 13eae368-9b79-11e8-9053-338307f4c6cc                     |
62 | wsrep_cluster_conf_id            | 11                                                       |
63 | wsrep_cluster_size               | 3                                                        | //It means I've got three nodes in PXC.
64 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
65 | wsrep_cluster_status             | Primary                                                  |
66 | wsrep_connected                  | ON                                                       |
67 | wsrep_local_bf_aborts            | 0                                                        |
68 | wsrep_local_index                | 0                                                        |
69 | wsrep_provider_name              | Galera                                                   |
70 | wsrep_provider_vendor            | Codership Oy <info@codership.com>                        |
71 | wsrep_provider_version           | 3.26(r)                                                  |
72 | wsrep_ready                      | ON                                                       |
73  ---------------------------------- ---------------------------------------------------------- 
74 68 rows in set (0.00 sec)

 

 

 

 

**Make binlog server begin to receive binlog.**

**Generate a full Xtrabackup of master.**

**Backup the database sysbench with mysqldump.**

Shutdown MySQL instance on zlm4.

 1 [root@zlm3 16:25:01 /data]
 2 #mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098 &
 3 [1] 4375
 4 mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
 5 
 6 [root@zlm3 16:26:24 /data]
 7 #ls -l
 8 total 4
 9 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
10 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
11 -rw-r----- 1 root  root  363 Jul 29 16:26 mysql-bin.000098
 1 [root@zlm2 06:28:44 /data/mysql/mysql3306/data]
 2 #innobackupex --default-file=/data/mysql/mysql3306/my.cnf --host=localhost -uroot -pPassw0rd /data/backup
 3 xtrabackup: recognized server arguments: 
 4 xtrabackup: recognized client arguments: 
 5 180731 06:29:03 innobackupex: Starting the backup operation
 6 ... //Omitted.
 7 
 8 180731 06:29:31 Executing UNLOCK TABLES
 9 180731 06:29:31 All tables unlocked
10 180731 06:29:31 [00] Copying ib_buffer_pool to /data/backup/2018-07-31_06-29-03/ib_buffer_pool
11 180731 06:29:31 [00]        ...done
12 180731 06:29:31 Backup created in directory '/data/backup/2018-07-31_06-29-03/'
13 MySQL binlog position: filename 'mysql-bin.000043', position '190', GTID of the last change '1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229'
14 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/backup-my.cnf
15 180731 06:29:31 [00]        ...done
16 180731 06:29:31 [00] Writing /data/backup/2018-07-31_06-29-03/xtrabackup_info
17 180731 06:29:31 [00]        ...done
18 xtrabackup: Transaction log of lsn (10141400402) to (10141400411) was copied.
19 180731 06:29:31 completed OK!
 1 [root@zlm2 07:32:01 ~]
 2 #mysqldump --single-transaction --master-data=2 -A > db3306_`date  %Y%m%d`.sql
 3 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 4 
 5 [root@zlm2 07:32:09 ~]
 6 #ls -l
 7 total 34744
 8 drwxr-xr-x   2 root root     4096 Jul 23 10:10 20180723
 9 -rw-------.  1 root root     1431 Jul 16  2015 anaconda-ks.cfg
10 -rw-r--r--   1 root root 20390934 Jul 26 07:32 db3306_20180726.sql
11 -rw-r--r--   1 root root  7333548 Jul 24 02:48 db.sql
12 -rwxr-xr-x   1 root root       54 Jun 13 04:16 mysqld.sh
13 -rwxr-xr-x   1 root root  7829340 Jul 24 10:02 percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
14 drwxr-xr-x  13 root root     4096 Jul  4 03:21 sysbench-1.0
15 
16 [root@zlm2 07:32:11 ~]
17 #scp db3306_20180726.sql zlm3:/data/backup
18 root@zlm3's password: 
19 db3306_20180726.sql                                                                                                100%   19MB  19.5MB/s   00:00    
20 
21 [root@zlm2 07:33:35 ~]
 1 [root@zlm4 09:02:18 /data/mysql/mysql3308]
 2 #!ps
 3 ps aux|grep mysqld
 4 mysql     5367  0.2 21.9 1574708 223476 pts/0  Sl   08:00   0:07 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
 5 root      5809  0.0  0.0 112640   960 pts/0    R    09:02   0:00 grep --color=auto mysqld
 6 
 7 [root@zlm4 09:02:22 /data/mysql/mysql3308]
 8 #pkill mysqld
 9 
10 [root@zlm4 09:02:43 /data/mysql/mysql3308]
11 #!ps
12 ps aux|grep mysqld
13 root      5827  0.0  0.0 112640   960 pts/0    R    09:02   0:00 grep --color=auto mysqld
14 [1]   Done                    mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf

 

 

 

 

**Flush two logs on master.**

**Continue to executing some dml operations on the target table and then kill the mysqld.**

**Drop one table in database "sysbench".**

Check the error log on node zlm3.

 1 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 2 Query OK, 0 rows affected (0.06 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>flush logs;
 5 Query OK, 0 rows affected (0.01 sec)
 6 
 7 zlm@192.168.56.100:3306 [sysbench]>show master status;
 8  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
 9 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
10  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
11 | mysql-bin.000100 |      194 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715693 |
12  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
13 1 row in set (0.00 sec)
 1 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 2000;
 2 Query OK, 2000 rows affected (0.10 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest5 limit 3000;
 5 Query OK, 3000 rows affected (0.07 sec)
 6 
 7 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest5;
 8  ---------- 
 9 | count(*) |
10  ---------- 
11 |     5000 |
12  ---------- 
13 1 row in set (0.00 sec)
14 
15 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest5;
16 Query OK, 0 rows affected (0.01 sec)
17 
18 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
19 Query OK, 0 rows affected (0.02 sec)
20 
21 (zlm@192.168.1.101 3306)[sysbench]>show master status;
22  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
23 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
24  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
25 | mysql-bin.000044 |      190 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730232 |
26  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
27 1 row in set (0.00 sec)
28 
29 [root@zlm2 06:38:14 ~]
30 #pkill mysqld
31 
32 [root@zlm2 06:38:18 ~]
33 #ps aux|grep mysqld
34 root      4050  0.0  0.0 112640   956 pts/0    R    06:38   0:00 grep --color=auto mysqld

 

 1 [root@zlm3 09:01:42 /data/mysql/mysql3308/data]
 2 #tail -f error.log 
 3 2018-08-09T09:02:44.880772 01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://192.168.1.103:4567 
 4 2018-08-09T09:02:44.880866 01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 5 2018-08-09T09:02:44.880877 01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
 6 2018-08-09T09:02:44.880899 01:00 0 [Note] WSREP: (23fb8f7a, 'tcp://0.0.0.0:4567') turning message relay requesting off
 7 2018-08-09T09:02:44.884451 01:00 0 [Note] WSREP: Node 13eae368 state primary
 8 2018-08-09T09:02:44.887086 01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 9 view (view_id(PRIM,13eae368,12)
10 memb {
11     13eae368,0
12     23fb8f7a,0
13     }
14 joined {
15     }
16 left {
17     }
18 partitioned {
19     74f3db69,0
20     }
21 )
22 2018-08-09T09:02:44.887205 01:00 0 [Note] WSREP: Save the discovered primary-component to disk
23 2018-08-09T09:02:44.887402 01:00 0 [Note] WSREP: forgetting 74f3db69 (tcp://192.168.1.103:4567)
24 2018-08-09T09:02:44.887688 01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 1, memb_num = 2
25 2018-08-09T09:02:44.887705 01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
26 2018-08-09T09:02:44.888444 01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe
27 2018-08-09T09:02:44.889150 01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 0 (zlm2)
28 2018-08-09T09:02:44.889187 01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: 37401cc1-9ba2-11e8-87aa-2f9e83444bbe from 1 (zlm3)
29 2018-08-09T09:02:44.889198 01:00 0 [Note] WSREP: Quorum results:
30     version    = 4,
31     component  = PRIMARY,
32     conf_id    = 11,
33     members    = 2/2 (primary/total),
34     act_id     = 22,
35     last_appl. = 0,
36     protocols  = 0/8/3 (gcs/repl/appl),
37     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
38 2018-08-09T09:02:44.889206 01:00 0 [Note] WSREP: Flow-control interval: [141, 141]
39 2018-08-09T09:02:44.889210 01:00 0 [Note] WSREP: Trying to continue unpaused monitor
40 2018-08-09T09:02:44.889304 01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
41 2018-08-09T09:02:44.889315 01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:22, view# 12: Primary, number of nodes: 2, my index: 1, protocol version 3
42 2018-08-09T09:02:44.889319 01:00 2 [Note] WSREP: Setting wsrep_ready to true
43 2018-08-09T09:02:44.889324 01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 2 -> 2) (Increment: 3 -> 2)
44 2018-08-09T09:02:44.889328 01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
45 2018-08-09T09:02:44.889336 01:00 2 [Note] WSREP: Assign initial position for certification: 22, protocol version: 3
46 2018-08-09T09:02:44.889355 01:00 0 [Note] WSREP: Service thread queue flushed.
47 2018-08-09T09:02:50.000210 01:00 0 [Note] WSREP:  cleaning up 74f3db69 (tcp://192.168.1.103:4567)

 

 

 1 (zlm@192.168.1.101 3306)[sysbench]>drop table sbtest10; 
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>show tables;
 5  -------------------- 
 6 | Tables_in_sysbench |
 7  -------------------- 
 8 | sbtest1            |
 9 | sbtest2            |
10 | sbtest3            |
11 | sbtest4            |
12 | sbtest5            |
13 | sbtest6            |
14 | sbtest7            |
15 | sbtest8            |
16 | sbtest9            |
17  -------------------- 
18 9 rows in set (0.00 sec)
19 
20 (zlm@192.168.1.101 3306)[sysbench]>

 

**Check whether the newly generated binlogs are successfully transmited to binlog server.**

**Scp the Xtrabackup backup to another server zlm3 with newly initialized instance**

 

**Do some DML operations on node zlm3.**

1 [root@zlm3 16:26:27 /data]
2 #ls -l
3 total 12
4 drwxr-xr-x 2 mysql mysql  32 Jun 10 03:41 backup
5 drwxr-xr-x 3 mysql mysql  22 Mar 18 16:05 mysql
6 -rw-r----- 1 root  root  410 Jul 29 16:27 mysql-bin.000098
7 -rw-r----- 1 root  root  241 Jul 29 16:27 mysql-bin.000099
8 -rw-r----- 1 root  root  194 Jul 29 16:27 mysql-bin.000100
1 [root@zlm2 06:43:42 ~]
2 #scp -r /data/backup/2018-07-31_06-29-03/ zlm3:/data/backup
3 root@zlm3's password: 
4 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
5 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02    
6 plugin.ibd                                                                                                         100%   96KB  96.0KB/s   00:00    
7 servers.ibd                                                                                                        100%   96KB  96.0KB/s   00:00
8 ... //Omitted.

Create a rescue environment in an initialized instance on zlm3.

 1 [root@zlm3 09:07:05 /data/mysql/mysql3308/data]
 2 #mysql -uzlm -pzlmzlm -h192.168.1.102 -P3308
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or g.
 5 Your MySQL connection id is 5
 6 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 7 
 8 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 9 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
10 
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14 
15 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
16 
17 (zlm@192.168.1.102 3308)[(none)]>show databases;
18  -------------------- 
19 | Database           |
20  -------------------- 
21 | information_schema |
22 | mysql              |
23 | performance_schema |
24 | sys                |
25  -------------------- 
26 4 rows in set (0.01 sec)
27 
28 (zlm@192.168.1.102 3308)[(none)]>create database zlm;
29 Query OK, 1 row affected (0.01 sec)
30 
31 (zlm@192.168.1.102 3308)[(none)]>use zlm;
32 Database changed
33 (zlm@192.168.1.102 3308)[zlm]>create table t1(
34     -> id int,
35     -> name char(10)
36     -> ) engine=innodb charset=utf8mb4;
37 Query OK, 0 rows affected (0.04 sec)
38 
39 (zlm@192.168.1.102 3308)[zlm]>insert into t1 values(1,'MySQL'),(2,'Oracle'),(3,'PostgreSQL');
40 ERROR 1105 (HY000): Percona-XtraDB-Cluster prohibits use of DML command on a table (zlm.t1) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER //DML of tables need explicit primary key in PXC.
41 (zlm@192.168.1.102 3308)[zlm]>alter table t1 add primary key(id);
42 Query OK, 0 rows affected (0.08 sec)
43 Records: 0  Duplicates: 0  Warnings: 0
44 
45 (zlm@192.168.1.102 3308)[zlm]>insert into t1 values(1,'MySQL'),(2,'Oracle'),(3,'PostgreSQL');
46 Query OK, 3 rows affected (0.01 sec)
47 Records: 3  Duplicates: 0  Warnings: 0
48 
49 (zlm@192.168.1.102 3308)[zlm]>select * from t1;
50  ---- ------------ 
51 | id | name       |
52  ---- ------------ 
53 |  1 | MySQL      |
54 |  2 | Oracle     |
55 |  3 | PostgreSQL |
56  ---- ------------ 
57 3 rows in set (0.00 sec)

 

 

 1 (zlm@192.168.1.102 3306)[(none)]>show databases;
 2  -------------------- 
 3 | Database           |
 4  -------------------- 
 5 | information_schema |
 6 | mysql              |
 7 | performance_schema |
 8 | sys                |
 9  -------------------- 
10 4 rows in set (0.00 sec)
11 
12 (zlm@192.168.1.102 3306)[(none)]>create database sysbench; //Create a same name database.
13 Query OK, 1 row affected (0.00 sec)
14 
15 (zlm@192.168.1.102 3306)[(none)]>show databases;
16  -------------------- 
17 | Database           |
18  -------------------- 
19 | information_schema |
20 | mysql              |
21 | performance_schema |
22 | sys                |
23 | sysbench           |
24  -------------------- 
25 5 rows in set (0.00 sec)
26 
27 (zlm@192.168.1.102 3306)[(none)]>create user rescue@'192.168.1.%' identified by 'rescue'; //Create a rescue user called "rescue".
28 Query OK, 0 rows affected (0.00 sec)
29 
30 (zlm@192.168.1.102 3306)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges to user ""rescue.
31 ERROR 1142 (42000): GRANT command denied to user 'zlm'@'zlm3' for table 'sbtest10' //It seems current user does not has the privilege to grant.
32 (zlm@192.168.1.102 3306)[(none)]>exit
33 Bye
34 
35 [root@zlm3 07:49:50 ~]
36 #mysql -uroot -pPassw0rd -hlocalhost -S /tmp/mysql3306.sock //Login with root user.
37 mysql: [Warning] Using a password on the command line interface can be insecure.
38 Welcome to the MySQL monitor.  Commands end with ; or g.
39 Your MySQL connection id is 6
40 Server version: 5.7.21-log MySQL Community Server (GPL)
41 
42 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
43 
44 Oracle is a registered trademark of Oracle Corporation and/or its
45 affiliates. Other names may be trademarks of their respective
46 owners.
47 
48 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
49 
50 (root@localhost mysql3306.sock)[(none)]>grant all privileges on sysbench.sbtest10 to rescue@'192.168.1.%'; //Grant privileges again.It works.
51 Query OK, 0 rows affected (0.00 sec)

 

Step 2: Destroy the table.

**Step 2. Rescue***

 

**Check the table on node zlm2.**

 


**Check the backup set and import it.**

 1 [root@zlm2 09:08:15 ~]
 2 #mysql -uzlm -pzlmzlm -h192.168.1.101 -P3308
 3 mysql: [Warning] Using a password on the command line interface can be insecure.
 4 Welcome to the MySQL monitor.  Commands end with ; or g.
 5 Your MySQL connection id is 9
 6 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 7 
 8 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 9 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
10 
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14 
15 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
16 
17 (zlm@192.168.1.101 3308)[(none)]>select * from zlm.t1;
18  ---- ------------ 
19 | id | name       |
20  ---- ------------ 
21 |  1 | MySQL      |
22 |  2 | Oracle     |
23 |  3 | PostgreSQL |
24  ---- ------------ 
25 3 rows in set (0.00 sec)
26 
27 //The PXC is working normally now.

Check target table on master.

 

 1 [root@zlm3 07:59:28 /data/backup]
 2 #ls -l|grep db3306
 3 -rw-r--r-- 1 root  root   20390934 Jul 26 07:33 db3306_20180726.sql
 4 
 5 [root@zlm3 07:59:42 /data/backup]
 6 #mysql -urescue -prescue -h192.168.1.102 -P3306 -f < db3306_20180726.sql 
 7 mysql: [Warning] Using a password on the command line interface can be insecure.
 8 ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
 9 ERROR 1227 (42000) at line 24: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
10 ERROR 1044 (42000) at line 36: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
11 ERROR 1044 (42000) at line 38: Access denied for user 'rescue'@'192.168.1.%' to database 'mysql'
12 ERROR 1046 (3D000) at line 44: No database selected
13 
14 //A bundle of "No database seelcted" message has been omitted.
15 
16 ERROR 1046 (3D000) at line 915: No database selected
17 ERROR 1044 (42000) at line 935: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
18 ERROR 1142 (42000) at line 943: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest1'
19 ERROR 1142 (42000) at line 946: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest1'
20 ERROR 1044 (42000) at line 960: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
21 ERROR 1142 (42000) at line 961: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
22 ERROR 1142 (42000) at line 962: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
23 ERROR 1142 (42000) at line 963: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest1'
24 ERROR 1142 (42000) at line 964: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest1'
25 ERROR 1044 (42000) at line 988: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
26 ERROR 1142 (42000) at line 999: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest2'
27 ERROR 1142 (42000) at line 1002: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest2'
28 ERROR 1044 (42000) at line 1016: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
29 ERROR 1142 (42000) at line 1017: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
30 ERROR 1142 (42000) at line 1018: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
31 ERROR 1142 (42000) at line 1019: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest2'
32 ERROR 1142 (42000) at line 1020: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest2'
33 ERROR 1142 (42000) at line 1027: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest3'
34 ERROR 1142 (42000) at line 1030: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest3'
35 ERROR 1044 (42000) at line 1044: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
36 ERROR 1142 (42000) at line 1045: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
37 ERROR 1142 (42000) at line 1046: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
38 ERROR 1142 (42000) at line 1047: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest3'
39 ERROR 1142 (42000) at line 1048: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest3'
40 ERROR 1142 (42000) at line 1055: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest4'
41 ERROR 1142 (42000) at line 1058: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest4'
42 ERROR 1044 (42000) at line 1072: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
43 ERROR 1142 (42000) at line 1073: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
44 ERROR 1142 (42000) at line 1074: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
45 ERROR 1142 (42000) at line 1075: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest4'
46 ERROR 1142 (42000) at line 1076: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest4'
47 ERROR 1142 (42000) at line 1083: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest5'
48 ERROR 1142 (42000) at line 1086: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest5'
49 ERROR 1044 (42000) at line 1100: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
50 ERROR 1142 (42000) at line 1101: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
51 ERROR 1142 (42000) at line 1102: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
52 ERROR 1142 (42000) at line 1103: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest5'
53 ERROR 1142 (42000) at line 1104: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest5'
54 ERROR 1142 (42000) at line 1111: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest6'
55 ERROR 1142 (42000) at line 1114: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest6'
56 ERROR 1044 (42000) at line 1128: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
57 ERROR 1142 (42000) at line 1129: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
58 ERROR 1142 (42000) at line 1130: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
59 ERROR 1142 (42000) at line 1131: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest6'
60 ERROR 1142 (42000) at line 1132: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest6'
61 ERROR 1142 (42000) at line 1139: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest7'
62 ERROR 1142 (42000) at line 1142: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest7'
63 ERROR 1044 (42000) at line 1156: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
64 ERROR 1142 (42000) at line 1157: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
65 ERROR 1142 (42000) at line 1158: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
66 ERROR 1142 (42000) at line 1159: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest7'
67 ERROR 1142 (42000) at line 1160: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest7'
68 ERROR 1142 (42000) at line 1167: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest8'
69 ERROR 1142 (42000) at line 1170: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest8'
70 ERROR 1044 (42000) at line 1184: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
71 ERROR 1142 (42000) at line 1185: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
72 ERROR 1142 (42000) at line 1186: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
73 ERROR 1142 (42000) at line 1187: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest8'
74 ERROR 1142 (42000) at line 1188: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest8'
75 ERROR 1142 (42000) at line 1195: DROP command denied to user 'rescue'@'zlm3' for table 'sbtest9'
76 ERROR 1142 (42000) at line 1198: CREATE command denied to user 'rescue'@'zlm3' for table 'sbtest9'
77 ERROR 1044 (42000) at line 1212: Access denied for user 'rescue'@'192.168.1.%' to database 'sysbench'
78 ERROR 1142 (42000) at line 1213: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
79 ERROR 1142 (42000) at line 1214: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
80 ERROR 1142 (42000) at line 1215: INSERT command denied to user 'rescue'@'zlm3' for table 'sbtest9'
81 ERROR 1142 (42000) at line 1216: ALTER command denied to user 'rescue'@'zlm3' for table 'sbtest9'
82 ERROR 1044 (42000) at line 1223: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
83 ERROR 1044 (42000) at line 1225: Access denied for user 'rescue'@'192.168.1.%' to database 'zlm'
84 ERROR 1227 (42000) at line 1226: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
85 
86 //The other tables in backup set will be skipped except for table "sbtest10".

 

 1 zlm@192.168.56.100:3306 [sysbench]>show tables;
 2  -------------------- 
 3 | Tables_in_sysbench |
 4  -------------------- 
 5 | sbtest1            |
 6 | sbtest2            |
 7 | sbtest3            |
 8 | sbtest4            |
 9 | sbtest5            |
10 | sbtest6            |
11  -------------------- 
12 6 rows in set (0.00 sec)
13 
14 zlm@192.168.56.100:3306 [sysbench]>select count(*) from sbtest6;
15  ---------- 
16 | count(*) |
17  ---------- 
18 |        0 |
19  ---------- 
20 1 row in set (0.00 sec)
21 
22 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(1,1,'a','b');
23 Query OK, 1 row affected (0.00 sec)
24 
25 zlm@192.168.56.100:3306 [sysbench]>select * from sbtest6;
26  ---- --- --- ----- 
27 | id | k | c | pad |
28  ---- --- --- ----- 
29 |  1 | 1 | a | b   |
30  ---- --- --- ----- 
31 1 row in set (0.00 sec)

Restore the backup on zlm3.

 

**Generate a backup set by Xtrabackup on node zlm3.**

 

 1 [root@zlm3 06:47:52 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --apply-log /data/backup/2018-07-31_06-29-03/
 3 ... //Omitted.
 4 
 5 [root@zlm3 06:46:39 ~]
 6 #cd /data/mysql/mysql3306/data/
 7 
 8 [root@zlm3 06:46:44 /data/mysql/mysql3306/data]
 9 #ls -l
10 total 409716
11 -rw-r----- 1 mysql mysql        56 Jul 27 11:15 auto.cnf
12 -rw-r----- 1 mysql mysql     19677 Jul 27 11:25 error.log
13 -rw-r----- 1 mysql mysql      2005 Jul 27 11:25 ib_buffer_pool
14 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ibdata1
15 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:25 ib_logfile0
16 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile1
17 -rw-r----- 1 mysql mysql 104857600 Jul 27 11:14 ib_logfile2
18 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 mysql
19 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 performance_schema
20 -rw-r----- 1 mysql mysql       276 Jul 27 11:18 relay-bin.000003
21 -rw-r----- 1 mysql mysql      2771 Jul 27 11:25 relay-bin.000004
22 -rw-r----- 1 mysql mysql       292 Jul 27 11:25 relay-bin.000005
23 -rw-r----- 1 mysql mysql       454 Jul 27 11:25 relay-bin.000006
24 -rw-r----- 1 mysql mysql       344 Jul 27 11:25 relay-bin.000007
25 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_applier.000001
26 -rw-r----- 1 mysql mysql        45 Jul 27 11:15 relay-bin-group_replication_applier.index
27 -rw-r----- 1 mysql mysql       169 Jul 27 11:25 relay-bin-group_replication_recovery.000001
28 -rw-r----- 1 mysql mysql        46 Jul 27 11:15 relay-bin-group_replication_recovery.index
29 -rw-r----- 1 mysql mysql        95 Jul 27 11:25 relay-bin.index
30 -rw-r----- 1 mysql mysql       334 Jul 27 11:25 slow.log
31 drwxr-x--- 2 mysql mysql      8192 Jul 27 11:15 sys
32 drwxr-x--- 2 mysql mysql      4096 Jul 27 11:15 sysbench
33 -rw-r----- 1 mysql mysql        24 Jul 27 11:15 xtrabackup_binlog_pos_innodb
34 -rw-r----- 1 mysql mysql       587 Jul 27 11:15 xtrabackup_info
35 -rw-r----- 1 mysql mysql         1 Jul 27 11:15 xtrabackup_master_key_id
36 
37 [root@zlm3 06:46:45 /data/mysql/mysql3306/data]
38 #rm -rf *
39 
40 [root@zlm3 06:46:50 /data/mysql/mysql3306/data]
41 #ps aux|grep mysqld
42 root      3913  0.0  0.0 112640   960 pts/1    R    06:50   0:00 grep --color=auto mysqld
43 
44 [root@zlm3 06:51:00 ~]
45 #innobackupex --defaults-file=/data/mysql/mysql3306/my.cnf --copy-back /data/backup/2018-07-31_06-29-03/
46 ... //Omitted.
47 
48 180731 06:51:36 [01] Copying ./ib_buffer_pool to /data/mysql/mysql3306/data/ib_buffer_pool
49 180731 06:51:36 [01]        ...done
50 180731 06:51:36 [01] Copying ./xtrabackup_info to /data/mysql/mysql3306/data/xtrabackup_info
51 180731 06:51:36 [01]        ...done
52 180731 06:51:36 [01] Copying ./xtrabackup_binlog_pos_innodb to /data/mysql/mysql3306/data/xtrabackup_binlog_pos_innodb
53 180731 06:51:36 [01]        ...done
54 180731 06:51:36 [01] Copying ./xtrabackup_master_key_id to /data/mysql/mysql3306/data/xtrabackup_master_key_id
55 180731 06:51:36 [01]        ...done
56 180731 06:51:36 [01] Copying ./ibtmp1 to /data/mysql/mysql3306/data/ibtmp1
57 180731 06:51:36 [01]        ...done
58 180731 06:51:36 completed OK!
59 
60 [root@zlm3 06:50:14 /data/mysql/mysql3306/data]
61 #ls -l
62 total 421936
63 -rw-r----- 1 root root      1017 Jul 31 06:51 ib_buffer_pool
64 -rw-r----- 1 root root 104857600 Jul 31 06:51 ibdata1
65 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile0
66 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile1
67 -rw-r----- 1 root root 104857600 Jul 31 06:51 ib_logfile2
68 -rw-r----- 1 root root  12582912 Jul 31 06:51 ibtmp1
69 drwxr-x--- 2 root root      4096 Jul 31 06:51 mysql
70 drwxr-x--- 2 root root      8192 Jul 31 06:51 performance_schema
71 drwxr-x--- 2 root root      8192 Jul 31 06:51 sys
72 drwxr-x--- 2 root root      4096 Jul 31 06:51 sysbench
73 -rw-r----- 1 root root        22 Jul 31 06:51 xtrabackup_binlog_pos_innodb
74 -rw-r----- 1 root root       600 Jul 31 06:51 xtrabackup_info
75 -rw-r----- 1 root root         1 Jul 31 06:51 xtrabackup_master_key_id
76 drwxr-x--- 2 root root       120 Jul 31 06:51 zlm
77 
78 [root@zlm3 06:53:49 /data/mysql/mysql3306/data]
79 #chown -R mysql.mysql *

**Check the rescued table "sbtest10".**

 1 [root@zlm3 09:25:44 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3308/my3308.cnf --user=root --password=Passw0rd --host=localhost -S /tmp/mysql3308.sock --port=3308 /data/backup
 3 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3308/data --server-id=1023308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
 4 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3308/data --server-id=1023308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
 5 180809 09:26:05 innobackupex: Starting the backup operation
 6 
 7 ... //Omitted.
 8 
 9 180809 09:26:17 Finished backing up non-InnoDB tables and files
10 180809 09:26:17 Executing LOCK BINLOG FOR BACKUP... //Here's the difference of lock mode between community and percona version of MySQL.
11 180809 09:26:17 [00] Writing /data/backup/2018-08-09_09-26-05/xtrabackup_binlog_info
12 180809 09:26:17 [00]        ...done
13 180809 09:26:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
14 xtrabackup: The latest check point (for incremental): '2626843'
15 xtrabackup: Stopping log copying thread.
16 .180809 09:26:17 >> log scanned up to (2626852)
17 
18 180809 09:26:17 Executing UNLOCK BINLOG
19 180809 09:26:17 Executing UNLOCK TABLES
20 180809 09:26:17 All tables unlocked
21 180809 09:26:17 [00] Copying ib_buffer_pool to /data/backup/2018-08-09_09-26-05/ib_buffer_pool
22 180809 09:26:17 [00]        ...done
23 180809 09:26:17 Backup created in directory '/data/backup/2018-08-09_09-26-05/'
24 MySQL binlog position: filename 'mysql-bin.000023', position '1107', GTID of the last change '42aada54-65ea-ee17-55f0-b47cf387c038:1-23'
25 180809 09:26:17 [00] Writing /data/backup/2018-08-09_09-26-05/backup-my.cnf
26 180809 09:26:17 [00]        ...done
27 180809 09:26:17 [00] Writing /data/backup/2018-08-09_09-26-05/xtrabackup_info
28 180809 09:26:17 [00]        ...done
29 xtrabackup: Transaction log of lsn (2626827) to (2626852) was copied.
30 180809 09:26:17 completed OK!

Generate Xtrabackup backup set.

 

 1 (root@localhost mysql3306.sock)[(none)]>use sysbench
 2 Reading table information for completion of table and column names
 3 You can turn off this feature to get a quicker startup with -A
 4 
 5 Database changed
 6 (root@localhost mysql3306.sock)[sysbench]>show tables;
 7  -------------------- 
 8 | Tables_in_sysbench |
 9  -------------------- 
10 | sbtest10           |
11  -------------------- 
12 1 row in set (0.00 sec)
13 
14 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest10;
15  ---------- 
16 | count(*) |
17  ---------- 
18 |    10000 |
19  ---------- 
20 1 row in set (0.00 sec)
21 
22 //Because the dropping operation is just happened after my backing up with mysqldump.There's no need to backup the incremental data in the dropped table.
23 //Therefore,we can simply copy the table back with transportable tablespace method,which can be referred to my previous blog.

 

 1 [root@zlm1 16:32:14 ~]
 2 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd /data/backup
 3 180729 16:32:20 innobackupex: Error: extra argument found -pPassw0rd
 4 180729 16:32:20 innobackupex: Error: extra argument found /data/backup
 5 
 6 [root@zlm1 16:32:20 ~]
 7 #innobackupex -v
 8 innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
 9 
10 [root@zlm1 16:32:26 ~]
11 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf --user=root --password=Passw0rd /data/backup
12 180729 16:32:33 innobackupex: Starting the backup operation
13 ...
14 
15 180729 16:32:53 Backup created in directory '/data/backup/2018-07-29_16-32-33'
16 MySQL binlog position: filename 'mysql-bin.000100', position '476', GTID of the last change '2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694'
17 180729 16:32:53 [00] Writing backup-my.cnf
18 180729 16:32:53 [00]        ...done
19 180729 16:32:53 [00] Writing xtrabackup_info
20 180729 16:32:53 [00]        ...done
21 xtrabackup: Transaction log of lsn (1719676169) to (1719676178) was copied.
22 180729 16:32:53 completed OK!

**Startup the MySQL instance on zlm3.**

 

**Copy the backup set to node zlm4.**

 

 1 [root@zlm3 06:53:57 /data/mysql/mysql3306/data]
 2 #sh /root/mysqld.sh
 3 
 4 [root@zlm3 06:55:16 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 mysql     3940 20.0 17.7 1110004 180300 pts/1  Sl   06:55   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
 7 root      3975  0.0  0.0 112640   956 pts/1    R    06:55   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm3 06:55:44 /data/mysql/mysql3306/data]
10 #mysql
11 Welcome to the MySQL monitor.  Commands end with ; or g.
12 Your MySQL connection id is 3
13 Server version: 5.7.21-log MySQL Community Server (GPL)
14 
15 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
16 
17 Oracle is a registered trademark of Oracle Corporation and/or its
18 affiliates. Other names may be trademarks of their respective
19 owners.
20 
21 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
22 
23 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5;
24  ---------- 
25 | count(*) |
26  ---------- 
27 |    10000 |
28  ---------- 
29 1 row in set (0.02 sec)

Test2:Rescue a table after truncating it based on a old mysqldump backup plus binlog.

1 [root@zlm3 09:31:07 ~]
2 #scp -r /data/backup/2018-08-09_09-26-05/ zlm4:/data/backup/
3 root@zlm4's password: 
4 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
5 ibdata1                                                                                                            100%  100MB  33.3MB/s   00:03    
6 plugin.ibd                                                                                                         100%   96KB  96.0KB/s   00:00    
7 servers.ibd                                                                                                        100%   96KB  96.0KB/s   00:00
8 ... //Omitted.

Insert one record of "sbtest6" and drop it.

 

 

 

 1 zlm@192.168.56.100:3306 [sysbench]>insert into sbtest6 values(2,2,'c','d');
 2 Query OK, 1 row affected (0.00 sec)
 3 
 4 zlm@192.168.56.100:3306 [sysbench]>show master status;
 5  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
 6 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
 7  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
 8 | mysql-bin.000100 |      758 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695 |
 9  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
10 1 row in set (0.00 sec)
11 
12 zlm@192.168.56.100:3306 [sysbench]>drop tables sbtest6;
13 Query OK, 0 rows affected (0.02 sec)
14 
15 zlm@192.168.56.100:3306 [sysbench]>show master status;
16  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
17 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
18  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
19 | mysql-bin.000100 |      951 |              |                  | 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715696 |
20  ------------------ ---------- -------------- ------------------ ------------------------------------------------- 
21 1 row in set (0.00 sec)

**    The data in Xtrabackup of master has been restored on zlm3. *Notice,it doesn't contain the operations of deletion 5000 rows. Firstly,I supposed that the mysqld has crashed and it can never start again. Secondly,I don't have binlog server any more this time.Is *there any other way to restore the dropping table and guarantee the change will not lose on it?**How can we restore the data safely and simply?Surely there is.**

Execute several normal dml operations in table "sbtest9".

**Restore backup on node zlm4.**

 

    Even thought the mysqld process is down on master.I still can get the binlog files on it.How about change the master binlog files into relay log files and apply them on zlm3?Let's have a try.

 1 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
 2  ---------- 
 3 | count(*) |
 4  ---------- 
 5 |    10000 |
 6  ---------- 
 7 1 row in set (0.00 sec)
 8 
 9 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest9 limit 5000;
10 Query OK, 5000 rows affected (0.07 sec)
11 
12 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
13  ---------- 
14 | count(*) |
15  ---------- 
16 |     5000 |
17  ---------- 
18 1 row in set (0.00 sec)
19 
20 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
21 Query OK, 0 rows affected (0.04 sec)
22 
23 (zlm@192.168.1.101 3306)[sysbench]>delete from sbtest9 limit 2500;
24 Query OK, 2500 rows affected (0.04 sec)
25 
26 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
27  ---------- 
28 | count(*) |
29  ---------- 
30 |     2500 |
31  ---------- 
32 1 row in set (0.00 sec)
33 
34 (zlm@192.168.1.101 3306)[sysbench]>flush logs;
35 Query OK, 0 rows affected (0.02 sec)

 

Shutdown the mysqld on master(mimic the MySQL instance down).

 

 

 1 [root@zlm4 09:43:26 /data/backup]
 2 #innobackupex --apply-log /data/backup/2018-08-09_09-26-05/
 3 xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1023308 --redo-log-version=1 --innodb_buffer_pool_filename=ib_buffer_pool 
 4 xtrabackup: recognized client arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=104857600 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1023308 --redo-log-version=1 --innodb_buffer_pool_filename=ib_buffer_pool 
 5 180809 09:45:56 innobackupex: Starting the apply-log operation
 6 
 7 ... //Omitted.
 8 
 9 InnoDB: Waiting for purge to start
10 InnoDB: 5.7.19 started; log sequence number 2627605
11 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
12 InnoDB: page_cleaner: 1000ms intended loop took 17036ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
13 InnoDB: FTS optimize thread exiting.
14 InnoDB: Starting shutdown...
15 InnoDB: Shutdown completed; log sequence number 2627624
16 180809 09:46:17 completed OK!
17 
18 [root@zlm4 09:52:44 /data/mysql/mysql3308/data]
19 #ls -l
20 total 540792
21 -rw-r----- 1 mysql mysql        56 Aug  9 08:00 auto.cnf
22 -rw------- 1 mysql mysql      1680 Aug  9 08:00 ca-key.pem
23 -rw-r--r-- 1 mysql mysql      1120 Aug  9 08:00 ca.pem
24 -rw-r--r-- 1 mysql mysql      1120 Aug  9 08:00 client-cert.pem
25 -rw------- 1 mysql mysql      1680 Aug  9 08:00 client-key.pem
26 -rw-r----- 1 mysql mysql     38775 Aug  9 09:02 error.log
27 -rw-r----- 1 mysql mysql 134219048 Aug  9 09:02 galera.cache
28 -rw-r----- 1 mysql mysql       113 Aug  9 09:02 grastate.dat
29 -rw-r----- 1 mysql mysql       280 Aug  9 09:02 ib_buffer_pool
30 -rw-r----- 1 mysql mysql 104857600 Aug  9 09:02 ibdata1
31 -rw-r----- 1 mysql mysql 104857600 Aug  9 09:02 ib_logfile0
32 -rw-r----- 1 mysql mysql 104857600 Aug  9 08:00 ib_logfile1
33 -rw-r----- 1 mysql mysql 104857600 Aug  9 08:00 ib_logfile2
34 drwxr-x--- 2 mysql mysql      4096 Aug  9 08:00 mysql
35 drwxr-x--- 2 mysql mysql      8192 Aug  9 08:00 performance_schema
36 -rw------- 1 mysql mysql      1676 Aug  9 08:00 private_key.pem
37 -rw-r--r-- 1 mysql mysql       452 Aug  9 08:00 public_key.pem
38 -rw-r--r-- 1 mysql mysql      1120 Aug  9 08:00 server-cert.pem
39 -rw------- 1 mysql mysql      1676 Aug  9 08:00 server-key.pem
40 -rw-r----- 1 mysql mysql       227 Aug  9 08:00 slow.log
41 drwxr-x--- 2 mysql mysql      8192 Aug  9 08:00 sys
42 
43 [root@zlm4 09:57:51 /data/mysql/mysql3308/data]
44 #rm -rf *
45 
46 [root@zlm4 09:59:35 /data/mysql/mysql3308/data]
47 #innobackupex --defaults-file=/data/mysql/mysql3308/my3308.cnf --copy-back /data/backup/2018-08-09_09-26-05/
48 xtrabackup: recognized server arguments: --datadir=/data/mysql/mysql3308/data --server-id=1033308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
49 xtrabackup: recognized client arguments: --datadir=/data/mysql/mysql3308/data --server-id=1033308 --open_files_limit=65536 --log_bin=/data/mysql/mysql3308/logs/mysql-bin --innodb_data_file_path=ibdata1:100M:autoextend --innodb_buffer_pool_filename=ib_buffer_pool --innodb_log_files_in_group=3 --innodb_log_file_size=100M --innodb_file_per_table=1 --innodb_open_files=65535 --innodb_page_size=16k --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_io_capacity=200 --innodb_doublewrite=1 --innodb_log_buffer_size=128M --innodb_flush_log_at_trx_commit=1 --innodb_buffer_pool_size=100M --innodb_flush_method=O_DIRECT 
50 180809 10:00:09 innobackupex: Starting the copy-back operation
51 
52 [root@zlm4 10:01:16 /data/mysql/mysql3308/data]
53 #ls -l
54 total 421936
55 -rw-r----- 1 root root       293 Aug  9 10:00 ib_buffer_pool
56 -rw-r----- 1 root root 104857600 Aug  9 10:00 ibdata1
57 -rw-r----- 1 root root 104857600 Aug  9 10:00 ib_logfile0
58 -rw-r----- 1 root root 104857600 Aug  9 10:00 ib_logfile1
59 -rw-r----- 1 root root 104857600 Aug  9 10:00 ib_logfile2
60 -rw-r----- 1 root root  12582912 Aug  9 10:00 ibtmp1
61 drwxr-x--- 2 root root      4096 Aug  9 10:00 mysql
62 drwxr-x--- 2 root root      8192 Aug  9 10:00 performance_schema
63 drwxr-x--- 2 root root      8192 Aug  9 10:00 sys
64 -rw-r----- 1 root root        22 Aug  9 10:00 xtrabackup_binlog_pos_innodb
65 -rw-r----- 1 root root        39 Aug  9 10:00 xtrabackup_galera_info
66 -rw-r----- 1 root root       650 Aug  9 10:00 xtrabackup_info
67 -rw-r----- 1 root root         1 Aug  9 10:00 xtrabackup_master_key_id
68 drwxr-x--- 2 root root        45 Aug  9 10:00 zlm
69 
70 [root@zlm4 10:02:23 /data/mysql/mysql3308/data]
71 #chown -R mysql.mysql *
 1 [root@zlm1 16:56:54 /data/backup]
 2 #mysqladmin shutdown
 3 
 4 [root@zlm1 16:57:05 /data/backup]
 5 #ps aux|grep mysqld
 6 mysql     3804  0.0 20.7 1072396 210620 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
 7 root      4512  0.0  0.0 112640   960 pts/2    R    16:57   0:00 grep --color=auto mysqld
 8 
 9 [root@zlm1 16:57:10 /data/backup]
10 #ps aux|grep mysqld
11 mysql     3804  0.0 20.1 1038828 204780 pts/0  Sl   15:52   0:02 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
12 root      4518  0.0  0.0 112640   956 pts/2    R    16:57   0:00 grep --color=auto mysqld
13 
14 [root@zlm1 16:57:15 /data/backup]
15 #mysqladmin shutdown
16 mysqladmin: connect to server at '192.168.56.100' failed
17 error: 'Can't connect to MySQL server on '192.168.56.100' (111)'
18 Check that mysqld is running on 192.168.56.100 and that the port is 3306.
19 You can check this by doing 'telnet 192.168.56.100 3306'
20 
21 [root@zlm1 16:57:23 /data/backup]
22 #ps aux|grep mysqld
23 root      4529  0.0  0.0 112640   960 pts/2    R    16:57   0:00 grep --color=auto mysqld

**Step

**Truncate the table to mimic the miss operation.**

 

 

  1. Special technique*
 1 (zlm@192.168.1.101 3306)[sysbench]>truncate table sbtest9;
 2 Query OK, 0 rows affected (0.02 sec)
 3 
 4 (zlm@192.168.1.101 3306)[sysbench]>select count(*) from sbtest9;
 5  ---------- 
 6 | count(*) |
 7  ---------- 
 8 |        0 |
 9  ---------- 
10 1 row in set (0.00 sec)
11 
12 (zlm@192.168.1.101 3306)[sysbench]>show master status;
13  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
14 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
15  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
16 | mysql-bin.000033 |      340 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730214 |
17  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
18 1 row in set (0.00 sec)

Modify the my3308.cnf to omit the PXC relevant parameter and startup mysqld.

Copy backup set to zlm2.


 

 1 [root@zlm4 10:05:33 /data/mysql/mysql3308]
 2 #cat my3308.cnf |grep wsrep
 3 #loose-wsrep_cluster_name=pxc_wubx 
 4 #loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.1.103
 5 #loose-wsrep_node_address=192.168.1.103
 6 #loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
 7 #loose-wsrep_sst_method=xtrabackup-v2
 8 #loose-wsrep_sst_method=rsync
 9 #loose-wsrep_sst_auth=sst:zlmzlm
10 #loose-wsrep_debug=on
11 #loose-wsrep_provider_options="debug=on"
12 
13 [root@zlm4 10:08:04 /data/mysql/mysql3308]
14 #mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf &
15 [1] 6094
16 
17 [root@zlm4 10:08:07 /data/mysql/mysql3308]
18 #!ps
19 ps aux|grep mysqld
20 mysql     6094 24.3 17.9 1238476 182592 pts/0  Sl   10:08   0:00 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
21 root      6128  0.0  0.0 112640   956 pts/0    R    10:08   0:00 grep --color=auto mysqld
 1 [root@zlm1 16:32:53 ~]
 2 #cd /data/backup/
 3 
 4 [root@zlm1 16:37:19 /data/backup]
 5 #ls -l
 6 total 4
 7 drwxr-x--- 7 root root 4096 Jul 29 16:32 2018-07-29_16-32-33
 8 
 9 [root@zlm1 16:37:24 /data/backup]
10 #scp -r 2018-07-29_16-32-33 zlm2:/data/backup
11 xtrabackup_logfile                                                                                                 100% 2560     2.5KB/s   00:00    
12 ibdata1                                                                                                            100%  100MB  50.0MB/s   00:02
13 ... //Omitted.

 

**Clear the environment and grant the right privileges.**

 

 

Make sure which binlogs we need and copy them to zlm3.

1 (root@localhost mysql3306.sock)[sysbench]>drop table sbtest10;
2 Query OK, 0 rows affected (0.03 sec)
3 
4 (root@localhost mysql3306.sock)[sysbench]>revoke all privileges on sysbench.sbtest10 from rescue@'192.168.1.%';
5 Query OK, 0 rows affected (0.00 sec)
6 
7 (root@localhost mysql3306.sock)[sysbench]>grant all privileges on sysbench.sbtest9 to rescue@'192.168.1.%';
8 Query OK, 0 rows affected (0.00 sec)

Implement a master-slave structure between node zlm3 and zlm4.

*Step 3: Rescue data.**

 1 [root@zlm3 07:23:49 /data/backup/2018-07-31_06-29-03]
 2 #cat xtrabackup_binlog_info
 3 mysql-bin.000043    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 //It means that the binlog we need should begin from mysql-bin.000043.
 4 
 5 [root@zlm2 06:38:09 /data/mysql/mysql3306/data]
 6 #cd ../logs
 7 
 8 [root@zlm2 07:20:00 /data/mysql/mysql3306/logs]
 9 #ls -l
10 total 64848
11 -rw-r----- 1 mysql mysql      233 Jul 23 11:21 mysql-bin.000023
12 -rw-r----- 1 mysql mysql      209 Jul 23 11:21 mysql-bin.000024
13 -rw-r----- 1 mysql mysql      233 Jul 24 11:27 mysql-bin.000025
14 -rw-r----- 1 mysql mysql      209 Jul 24 11:27 mysql-bin.000026
15 -rw-r----- 1 mysql mysql      233 Jul 25 06:12 mysql-bin.000027
16 -rw-r----- 1 mysql mysql      209 Jul 25 06:12 mysql-bin.000028
17 -rw-r----- 1 mysql mysql  5727732 Jul 25 11:33 mysql-bin.000029
18 -rw-r----- 1 mysql mysql      209 Jul 25 11:33 mysql-bin.000030
19 -rw-r----- 1 mysql mysql 58202858 Jul 26 09:12 mysql-bin.000031
20 -rw-r----- 1 mysql mysql   477279 Jul 26 09:13 mysql-bin.000032
21 -rw-r----- 1 mysql mysql      383 Jul 26 11:21 mysql-bin.000033
22 -rw-r----- 1 mysql mysql      209 Jul 26 11:21 mysql-bin.000034
23 -rw-r----- 1 mysql mysql   954930 Jul 27 07:59 mysql-bin.000035
24 -rw-r----- 1 mysql mysql     2566 Jul 27 11:25 mysql-bin.000036
25 -rw-r----- 1 mysql mysql      209 Jul 27 11:25 mysql-bin.000037
26 -rw-r----- 1 mysql mysql     1394 Jul 30 11:29 mysql-bin.000038
27 -rw-r----- 1 mysql mysql      209 Jul 30 11:29 mysql-bin.000039
28 -rw-r----- 1 mysql mysql      418 Jul 31 04:37 mysql-bin.000040
29 -rw-r----- 1 mysql mysql      233 Jul 31 04:37 mysql-bin.000041
30 -rw-r----- 1 mysql mysql      233 Jul 31 04:37 mysql-bin.000042
31 -rw-r----- 1 mysql mysql   954479 Jul 31 06:35 mysql-bin.000043
32 -rw-r----- 1 mysql mysql      209 Jul 31 06:38 mysql-bin.000044
33 -rw-r----- 1 mysql mysql      968 Jul 31 06:35 mysql-bin.index
34 
35 [root@zlm2 07:28:32 /data/mysql/mysql3306/logs]
36 #scp mysql-bin.00004{3,4} zlm3:/data/backup37 root@zlm3's password: 
38 mysql-bin.000043                                                                                                   100%  932KB 932.1KB/s   00:00    
39 mysql-bin.000044                                                                                                   100%  209     0.2KB/s   00:00

 

  1 //Create a replication user on node zlm3.
  2 (zlm@192.168.1.102 3308)[zlm]>grant all privileges on *.* to repl@'192.168.1.%' identified by 'repl4slave';
  3 ERROR 1045 (28000): Access denied for user 'zlm'@'192.168.1.%' (using password: YES)
  4 (zlm@192.168.1.102 3308)[zlm]>q
  5 Bye
  6 
  7 [root@zlm3 10:11:28 /data/mysql/mysql3308/data]
  8 #mysql -uroot -pPassw0rd -hlocalhost -P3308
  9 mysql: [Warning] Using a password on the command line interface can be insecure.
 10 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3306.sock' (2)
 11 
 12 [root@zlm3 10:11:48 /data/mysql/mysql3308/data]
 13 #mysql -uroot -pPassw0rd -hlocalhost -P3308 -S /tmp/mysql3308.sock
 14 mysql: [Warning] Using a password on the command line interface can be insecure.
 15 Welcome to the MySQL monitor.  Commands end with ; or g.
 16 Your MySQL connection id is 8
 17 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 18 
 19 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 20 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 21 
 22 Oracle is a registered trademark of Oracle Corporation and/or its
 23 affiliates. Other names may be trademarks of their respective
 24 owners.
 25 
 26 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 27 
 28 (root@localhost mysql3308.sock)[(none)]>grant all privileges on *.* to repl@'192.168.1.%' identified by 'repl4slave';
 29 Query OK, 0 rows affected, 1 warning (0.00 sec)
 30 
 31 (root@localhost mysql3308.sock)[(none)]>select user,host from mysql.user;
 32  --------------- ------------- 
 33 | user          | host        |
 34  --------------- ------------- 
 35 | repl          | 192.168.1.% |
 36 | zlm           | 192.168.1.% |
 37 | mysql.session | localhost   |
 38 | mysql.sys     | localhost   |
 39 | root          | localhost   |
 40 | sst           | localhost   |
 41  --------------- ------------- 
 42 6 rows in set (0.01 sec)
 43 
 44 //Execute "change master to ... " on node zlm4.
 45 [root@zlm4 10:14:37 /data/mysql/mysql3308]
 46 #mysql -uzlm -pzlmzlm -h192.168.1.103 -P3308
 47 mysql: [Warning] Using a password on the command line interface can be insecure.
 48 Welcome to the MySQL monitor.  Commands end with ; or g.
 49 Your MySQL connection id is 2
 50 Server version: 5.7.22-22-29.26-log Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
 51 
 52 Copyright (c) 2009-2018 Percona LLC and/or its affiliates
 53 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
 54 
 55 Oracle is a registered trademark of Oracle Corporation and/or its
 56 affiliates. Other names may be trademarks of their respective
 57 owners.
 58 
 59 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
 60 
 61 (zlm@192.168.1.103 3308)[(none)]>change master to master_host='192.168.1.102',master_port=3308,master_user='repl',master_password='repl4slave',master_auto_position=1;
 62 Query OK, 0 rows affected, 1 warning (0.02 sec)
 63 
 64 (zlm@192.168.1.103 3308)[(none)]>start slave;
 65 Query OK, 0 rows affected (0.02 sec)
 66 
 67 (zlm@192.168.1.103 3308)[(none)]>show slave statusG
 68 *************************** 1. row ***************************
 69                Slave_IO_State: Waiting for master to send event
 70                   Master_Host: 192.168.1.102
 71                   Master_User: repl
 72                   Master_Port: 3308
 73                 Connect_Retry: 60
 74               Master_Log_File: mysql-bin.000023
 75           Read_Master_Log_Pos: 1397
 76                Relay_Log_File: zlm4-relay-bin.000002
 77                 Relay_Log_Pos: 367
 78         Relay_Master_Log_File: mysql-bin.000023
 79              Slave_IO_Running: Yes
 80             Slave_SQL_Running: No
 81               Replicate_Do_DB: 
 82           Replicate_Ignore_DB: 
 83            Replicate_Do_Table: 
 84        Replicate_Ignore_Table: 
 85       Replicate_Wild_Do_Table: 
 86   Replicate_Wild_Ignore_Table: 
 87                    Last_Errno: 1007
 88                    Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
 89                  Skip_Counter: 0
 90           Exec_Master_Log_Pos: 194
 91               Relay_Log_Space: 1776
 92               Until_Condition: None
 93                Until_Log_File: 
 94                 Until_Log_Pos: 0
 95            Master_SSL_Allowed: No
 96            Master_SSL_CA_File: 
 97            Master_SSL_CA_Path: 
 98               Master_SSL_Cert: 
 99             Master_SSL_Cipher: 
100                Master_SSL_Key: 
101         Seconds_Behind_Master: NULL
102 Master_SSL_Verify_Server_Cert: No
103                 Last_IO_Errno: 0
104                 Last_IO_Error: 
105                Last_SQL_Errno: 1007
106                Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
107   Replicate_Ignore_Server_Ids: 
108              Master_Server_Id: 1023308
109                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
110              Master_Info_File: /data/mysql/mysql3308/data/master.info
111                     SQL_Delay: 0
112           SQL_Remaining_Delay: NULL
113       Slave_SQL_Running_State: 
114            Master_Retry_Count: 86400
115                   Master_Bind: 
116       Last_IO_Error_Timestamp: 
117      Last_SQL_Error_Timestamp: 180809 10:18:58
118                Master_SSL_Crl: 
119            Master_SSL_Crlpath: 
120            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-24
121             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-19
122                 Auto_Position: 1
123          Replicate_Rewrite_DB: 
124                  Channel_Name: 
125            Master_TLS_Version: 
126 1 row in set (0.00 sec)
127 
128 //The error log shows below.
129 2018-08-09T10:15:45.368412 01:00 2 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=zlm4-relay-bin' to avoid this problem.
130 2018-08-09T10:15:45.382500 01:00 2 [Note] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='192.168.1.102', master_port= 3308, master_log_file='', master_log_pos= 4, master_bind=''.
131 2018-08-09T10:18:58.164370 01:00 3 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
132 2018-08-09T10:18:58.173589 01:00 3 [Note] Slave I/O thread for channel '': connected to master 'repl@192.168.1.102:3308',replication started in log 'FIRST' at position 4
133 2018-08-09T10:18:58.180721 01:00 4 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
134 2018-08-09T10:18:58.180761 01:00 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'FIRST' at position 0, relay log './zlm4-relay-bin.000001' position: 4
135 2018-08-09T10:18:58.202347 01:00 5 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350; Error 'Can't create database 'zlm'; database exists' on query. Default database: 'zlm'. Query: 'create database zlm', Error_code: 1007
136 2018-08-09T10:18:58.202651 01:00 4 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000023' position 194
137 
138 //Check the "replication_applier_status_by_worker" table for detail.
139 (zlm@192.168.1.103 3308)[(none)]>select * from performance_schema.replication_applier_status_by_workerG
140 *************************** 1. row ***************************
141          CHANNEL_NAME: 
142             WORKER_ID: 1
143             THREAD_ID: NULL
144         SERVICE_STATE: OFF
145 LAST_SEEN_TRANSACTION: 42aada54-65ea-ee17-55f0-b47cf387c038:20
146     LAST_ERROR_NUMBER: 1007
147    LAST_ERROR_MESSAGE: Worker 1 failed executing transaction '42aada54-65ea-ee17-55f0-b47cf387c038:20' at master log mysql-bin.000023, end_log_pos 350; Error 'Can't create database 'zlm'; database exists' on query. Default database: 'zlm'. Query: 'create database zlm'
148  LAST_ERROR_TIMESTAMP: 2018-08-09 16:18:58
149 *************************** 2. row ***************************
150          CHANNEL_NAME: 
151             WORKER_ID: 2
152             THREAD_ID: NULL
153         SERVICE_STATE: OFF
154 LAST_SEEN_TRANSACTION: 
155     LAST_ERROR_NUMBER: 0
156    LAST_ERROR_MESSAGE: 
157  LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
158 *************************** 3. row ***************************
159          CHANNEL_NAME: 
160             WORKER_ID: 3
161             THREAD_ID: NULL
162         SERVICE_STATE: OFF
163 LAST_SEEN_TRANSACTION: 
164     LAST_ERROR_NUMBER: 0
165    LAST_ERROR_MESSAGE: 
166  LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
167 *************************** 4. row ***************************
168          CHANNEL_NAME: 
169             WORKER_ID: 4
170             THREAD_ID: NULL
171         SERVICE_STATE: OFF
172 LAST_SEEN_TRANSACTION: 
173     LAST_ERROR_NUMBER: 0
174    LAST_ERROR_MESSAGE: 
175  LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
176 4 rows in set (0.00 sec)
177 
178 //Check the target database and table.
179 (zlm@192.168.1.103 3308)[(none)]>show databases;
180  -------------------- 
181 | Database           |
182  -------------------- 
183 | information_schema |
184 | mysql              |
185 | performance_schema |
186 | sys                |
187 | zlm                |
188  -------------------- 
189 5 rows in set (0.00 sec)
190 
191 (zlm@192.168.1.103 3308)[(none)]>use zlm
192 Reading table information for completion of table and column names
193 You can turn off this feature to get a quicker startup with -A
194 
195 Database changed
196 (zlm@192.168.1.103 3308)[zlm]>show tables;
197  --------------- 
198 | Tables_in_zlm |
199  --------------- 
200 | t1            |
201  --------------- 
202 1 row in set (0.00 sec)
203 
204 (zlm@192.168.1.103 3308)[zlm]>select * from t1;
205  ---- ------------ 
206 | id | name       |
207  ---- ------------ 
208 |  1 | MySQL      |
209 |  2 | Oracle     |
210 |  3 | PostgreSQL |
211  ---- ------------ 
212 3 rows in set (0.01 sec)
213 
214 //The changes on node zlm3 has been replicated to zlm4.
215 
216 [root@zlm4 10:35:51 /data/mysql/mysql3308/logs]
217 #ls -l
218 total 16
219 -rw-r----- 1 mysql mysql 194 Aug  9 08:00 mysql-bin.000024
220 -rw-r----- 1 mysql mysql 217 Aug  9 09:02 mysql-bin.000025
221 -rw-r----- 1 mysql mysql 194 Aug  9 10:08 mysql-bin.000026
222 -rw-r----- 1 mysql mysql 132 Aug  9 10:08 mysql-bin.index
223 
224 //No mysql-bin.000023 was found.Check the relay-log file.
225 [root@zlm4 10:38:16 /data/mysql/mysql3308/data]
226 #ls -l|grep relay
227 -rw-r----- 1 mysql mysql        58 Aug  9 10:18 relay-log.info
228 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.1
229 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.2
230 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.3
231 -rw-r----- 1 mysql mysql        84 Aug  9 10:18 worker-relay-log.info.4
232 -rw-r----- 1 mysql mysql       206 Aug  9 10:18 zlm4-relay-bin.000001
233 -rw-r----- 1 mysql mysql      1570 Aug  9 10:18 zlm4-relay-bin.000002
234 -rw-r----- 1 mysql mysql        48 Aug  9 10:18 zlm4-relay-bin.index
235 
236 [root@zlm4 10:38:30 /data/mysql/mysql3308/data]
237 #mysqlbinlog -v --base64-output=decode-rows zlm4-relay-bin.000002 > ~/02.log
238 
239 [root@zlm4 10:38:46 /data/mysql/mysql3308/data]
240 #cd
241 
242 [root@zlm4 10:38:49 ~]
243 #cat 02.log 
244 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
245 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
246 DELIMITER /*!*/;
247 # at 4
248 #180809 10:18:58 server id 1033308  end_log_pos 123 CRC32 0x4367bb3e     Start: binlog v 4, server v 5.7.22-22-29.26-log created 180809 10:18:58
249 # This Format_description_event appears in a relay log and was generated by the slave thread.
250 # at 123
251 #180809 10:18:58 server id 1033308  end_log_pos 154 CRC32 0xf3605911     Previous-GTIDs
252 # [empty]
253 # at 154
254 #700101  1:00:00 server id 1023308  end_log_pos 0 CRC32 0xb5bcfbb7     Rotate to mysql-bin.000023  pos: 4
255 # at 201
256 #180809  4:09:22 server id 1023308  end_log_pos 123 CRC32 0x6f7e8565     Start: binlog v 4, server v 5.7.22-22-29.26-log created 180809  4:09:22 at startup
257 ROLLBACK/*!*/;
258 # at 320
259 #180809 10:18:58 server id 0  end_log_pos 367 CRC32 0xa757553d     Rotate to mysql-bin.000023  pos: 194
260 # at 367
261 #180809  9:07:52 server id 1023308  end_log_pos 259 CRC32 0x531076d3     GTID    last_committed=0    sequence_number=1    rbr_only=no
262 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:20'/*!*/;
263 # at 432
264 #180809  9:07:52 server id 1023308  end_log_pos 350 CRC32 0x9acb4e3f     Query    thread_id=5    exec_time=0    error_code=0
265 SET TIMESTAMP=1533798472/*!*/;
266 SET @@session.pseudo_thread_id=5/*!*/;
267 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
268 SET @@session.sql_mode=1436549152/*!*/;
269 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
270 /*!C utf8 *//*!*/;
271 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
272 SET @@session.lc_time_names=0/*!*/;
273 SET @@session.collation_database=DEFAULT/*!*/;
274 create database zlm
275 /*!*/;
276 # at 523
277 #180809  9:08:36 server id 1023308  end_log_pos 415 CRC32 0xde316a34     GTID    last_committed=1    sequence_number=2    rbr_only=no
278 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:21'/*!*/;
279 # at 588
280 #180809  9:08:36 server id 1023308  end_log_pos 557 CRC32 0xb29bd4ab     Query    thread_id=5    exec_time=0    error_code=0
281 use `zlm`/*!*/;
282 SET TIMESTAMP=1533798516/*!*/;
283 create table t1(
284 id int,
285 name char(10)
286 ) engine=innodb charset=utf8mb4
287 /*!*/;
288 # at 730
289 #180809  9:13:42 server id 1023308  end_log_pos 622 CRC32 0x2a5f6414     GTID    last_committed=2    sequence_number=3    rbr_only=no
290 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:22'/*!*/;
291 # at 795
292 #180809  9:13:42 server id 1023308  end_log_pos 728 CRC32 0xa803e3aa     Query    thread_id=5    exec_time=0    error_code=0
293 SET TIMESTAMP=1533798822/*!*/;
294 alter table t1 add primary key(id)
295 /*!*/;
296 # at 901
297 #180809  9:13:46 server id 1023308  end_log_pos 793 CRC32 0xfd677245     GTID    last_committed=3    sequence_number=4    rbr_only=yes
298 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
299 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:23'/*!*/;
300 # at 966
301 #180809  9:13:46 server id 1023308  end_log_pos 869 CRC32 0x7b568d5a     Query    thread_id=5    exec_time=0    error_code=0
302 SET TIMESTAMP=1533798826/*!*/;
303 SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
304 BEGIN
305 /*!*/;
306 # at 1042
307 # at 1128
308 #180809  9:13:46 server id 1023308  end_log_pos 1002 CRC32 0x8b4e5e6b     Table_map: `zlm`.`t1` mapped to number 110
309 # at 1175
310 #180809  9:13:46 server id 1023308  end_log_pos 1076 CRC32 0x51750bc0     Write_rows: table id 110 flags: STMT_END_F
311 ### INSERT INTO `zlm`.`t1`
312 ### SET
313 ###   @1=1
314 ###   @2='MySQL'
315 ### INSERT INTO `zlm`.`t1`
316 ### SET
317 ###   @1=2
318 ###   @2='Oracle'
319 ### INSERT INTO `zlm`.`t1`
320 ### SET
321 ###   @1=3
322 ###   @2='PostgreSQL'
323 # at 1249
324 #180809  9:13:46 server id 1023308  end_log_pos 1107 CRC32 0x6033f0ee     Xid = 26
325 COMMIT/*!*/;
326 # at 1280
327 #180809 10:12:07 server id 1023308  end_log_pos 1172 CRC32 0x66f4de8c     GTID    last_committed=4    sequence_number=5    rbr_only=no
328 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:24'/*!*/;
329 # at 1345
330 #180809 10:12:07 server id 1023308  end_log_pos 1397 CRC32 0x86665c03     Query    thread_id=8    exec_time=0    error_code=0
331 SET TIMESTAMP=1533802327/*!*/;
332 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
333 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E'
334 /*!*/;
335 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
336 DELIMITER ;
337 # End of log file
338 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
339 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
340 
341 //Clean the value of variable "gtid_purged".
342 (zlm@192.168.1.103 3308)[(none)]>reset master;
343 Query OK, 0 rows affected (0.03 sec)
344 
345 (zlm@192.168.1.103 3308)[(none)]>set @@global.gtid_purged='42aada54-65ea-ee17-55f0-b47cf387c038:1-24';
346 Query OK, 0 rows affected (0.01 sec)
347 
348 (zlm@192.168.1.103 3308)[(none)]>start slave sql_thread;
349 Query OK, 0 rows affected (0.00 sec)
350 
351 (zlm@192.168.1.103 3308)[(none)]>show slave statusG
352 *************************** 1. row ***************************
353                Slave_IO_State: Waiting for master to send event
354                   Master_Host: 192.168.1.102
355                   Master_User: repl
356                   Master_Port: 3308
357                 Connect_Retry: 60
358               Master_Log_File: mysql-bin.000023
359           Read_Master_Log_Pos: 1397
360                Relay_Log_File: zlm4-relay-bin.000002
361                 Relay_Log_Pos: 1570
362         Relay_Master_Log_File: mysql-bin.000023
363              Slave_IO_Running: Yes
364             Slave_SQL_Running: Yes
365               Replicate_Do_DB: 
366           Replicate_Ignore_DB: 
367            Replicate_Do_Table: 
368        Replicate_Ignore_Table: 
369       Replicate_Wild_Do_Table: 
370   Replicate_Wild_Ignore_Table: 
371                    Last_Errno: 0
372                    Last_Error: 
373                  Skip_Counter: 0
374           Exec_Master_Log_Pos: 1397
375               Relay_Log_Space: 1776
376               Until_Condition: None
377                Until_Log_File: 
378                 Until_Log_Pos: 0
379            Master_SSL_Allowed: No
380            Master_SSL_CA_File: 
381            Master_SSL_CA_Path: 
382               Master_SSL_Cert: 
383             Master_SSL_Cipher: 
384                Master_SSL_Key: 
385         Seconds_Behind_Master: 0
386 Master_SSL_Verify_Server_Cert: No
387                 Last_IO_Errno: 0
388                 Last_IO_Error: 
389                Last_SQL_Errno: 0
390                Last_SQL_Error: 
391   Replicate_Ignore_Server_Ids: 
392              Master_Server_Id: 1023308
393                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
394              Master_Info_File: /data/mysql/mysql3308/data/master.info
395                     SQL_Delay: 0
396           SQL_Remaining_Delay: NULL
397       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
398            Master_Retry_Count: 86400
399                   Master_Bind: 
400       Last_IO_Error_Timestamp: 
401      Last_SQL_Error_Timestamp: 
402                Master_SSL_Crl: 
403            Master_SSL_Crlpath: 
404            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-24
405             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-24
406                 Auto_Position: 1
407          Replicate_Rewrite_DB: 
408                  Channel_Name: 
409            Master_TLS_Version: 
410 1 row in set (0.00 sec)

 

**Restore the table "sbtest9" from mysqldump backup.**

 

 

**Transfer the mysql-bin files into relay-bin files.**

 1 [root@zlm3 09:19:39 /data/backup]
 2 #mysql -urescue -prescue -h192.168.1.102 -P3306 -f < db3306_20180726.sql
 3 
 4 ... //Omitted.
 5 
 6 (root@localhost mysql3306.sock)[sysbench]>show tables;
 7  -------------------- 
 8 | Tables_in_sysbench |
 9  -------------------- 
10 | sbtest9            |
11  -------------------- 
12 1 row in set (0.00 sec)
13 
14 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
15  ---------- 
16 | count(*) |
17  ---------- 
18 |    10000 |
19  ---------- 
20 1 row in set (0.00 sec)
21 
22 //On account of restoring from an old mysqldump backup,we cannot rescue the incremental data in the table "sbtest9".
23 //What can we do next step?Those incremental data are all in the binlog,so we need to implement a slave first.

Insert a new record in table "t1" on node zlm3.

Prepare to restore the backup with "--apply-log" on zlm2.

 1 [root@zlm3 07:24:08 /data/backup/2018-07-31_06-29-03]
 2 #cd ..
 3 
 4 [root@zlm3 07:30:46 /data/backup]
 5 #ls -l
 6 total 944
 7 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
 8 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
 9 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
10 
11 [root@zlm3 07:30:47 /data/backup]
12 #for i in $(ls mysql-bin.0*)
13 > do
14 >     ext=$(echo $i | cut -d'.' -f2);
15 >     cp $i relay-bin.$ext;
16 > done
17 
18 [root@zlm3 07:31:19 /data/backup]
19 #ls -l
20 total 1884
21 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
22 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
23 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
24 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043
25 -rw-r----- 1 root root    209 Jul 31 07:31 relay-bin.000044 
26 
27 [root@zlm3 07:36:18 /data/backup]
28 #ls ./relay-bin.0* > relay-bin.index
29 
30 [root@zlm3 07:36:20 /data/backup]
31 #ls -l
32 total 1888
33 drwxr-x--- 7 root root   4096 Jul 31 06:48 2018-07-31_06-29-03
34 -rw-r----- 1 root root 954479 Jul 31 07:28 mysql-bin.000043
35 -rw-r----- 1 root root    209 Jul 31 07:28 mysql-bin.000044
36 -rw-r----- 1 root root 954479 Jul 31 07:31 relay-bin.000043
37 -rw-r----- 1 root root    209 Jul 31 07:31 relay-bin.000044
38 -rw-r--r-- 1 root root     38 Jul 31 07:36 relay-bin.index
39 
40 [root@zlm3 07:36:23 /data/backup]
41 #cat relay-bin.index 
42 ./relay-bin.000043
43 ./relay-bin.000044
44 
45 [root@zlm3 07:36:27 /data/backup]
46 #chown mysql.mysql relay*
47 
48 [root@zlm3 07:37:12 /data/backup]
49 #ls -l
50 total 1888
51 drwxr-x--- 7 root  root    4096 Jul 31 06:48 2018-07-31_06-29-03
52 -rw-r----- 1 root  root  954479 Jul 31 07:28 mysql-bin.000043
53 -rw-r----- 1 root  root     209 Jul 31 07:28 mysql-bin.000044
54 -rw-r----- 1 mysql mysql 954479 Jul 31 07:31 relay-bin.000043
55 -rw-r----- 1 mysql mysql    209 Jul 31 07:31 relay-bin.000044
56 -rw-r--r-- 1 mysql mysql     38 Jul 31 07:36 relay-bin.index

 

 1 (root@localhost mysql3308.sock)[(none)]>use zlm
 2 Reading table information for completion of table and column names
 3 You can turn off this feature to get a quicker startup with -A
 4 
 5 Database changed
 6 (root@localhost mysql3308.sock)[zlm]>insert into t1 values(4,'Redis');
 7 Query OK, 1 row affected (0.01 sec)
 8 
 9 (root@localhost mysql3308.sock)[zlm]>select * from t1;
10  ---- ------------ 
11 | id | name       |
12  ---- ------------ 
13 |  1 | MySQL      |
14 |  2 | Oracle     |
15 |  3 | PostgreSQL |
16 |  4 | Redis      |
17  ---- ------------ 
18 4 rows in set (0.00 sec)
 1 [root@zlm2 16:38:09 /data/backup]
 2 #ls -l
 3 total 4
 4 drwxr-x--- 7 root root 4096 Jul 29 16:37 2018-07-29_16-32-33
 5 
 6 [root@zlm2 16:38:12 /data/backup]
 7 #innobackupex -v
 8 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql 
 9 innobackupex version 2.4.12 Linux (x86_64) (revision id: 170eb8c)
10 
11 [root@zlm2 16:39:13 /data/backup]
12 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --apply-log /data/backup/2018-07-29_16-32-33/
13 ... //Omitted.
14 
15 xtrabackup: starting shutdown with innodb_fast_shutdown = 1
16 InnoDB: page_cleaner: 1000ms intended loop took 8812ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
17 InnoDB: FTS optimize thread exiting.
18 InnoDB: Starting shutdown...
19 InnoDB: Shutdown completed; log sequence number 1719676456
20 180729 16:39:39 completed OK!

 

**Implement a slave filter replication on zlm3.**

www.9159.com, 

 

**Copy these relay-bin files to the proper directory.**

  1 //Fetch the gtid_purged infomation from mysqldump backup.
  2 [root@zlm3 09:39:19 /data/backup]
  3 #grep "SET @@GLOBAL.GTID_PURGED" db3306_20180726.sql 
  4 SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';
  5 
  6 (root@localhost mysql3306.sock)[sysbench]>reset master;
  7 Query OK, 0 rows affected (0.01 sec)
  8 
  9 (root@localhost mysql3306.sock)[sysbench]>reset slave;
 10 Query OK, 0 rows affected (0.02 sec)
 11 
 12 (root@localhost mysql3306.sock)[sysbench]>show slave statusG
 13 *************************** 1. row ***************************
 14                Slave_IO_State: 
 15                   Master_Host: 192.168.1.101
 16                   Master_User: repl
 17                   Master_Port: 3306
 18                 Connect_Retry: 60
 19               Master_Log_File: 
 20           Read_Master_Log_Pos: 4
 21                Relay_Log_File: relay-bin.000001
 22                 Relay_Log_Pos: 4
 23         Relay_Master_Log_File: 
 24              Slave_IO_Running: No
 25             Slave_SQL_Running: No
 26               Replicate_Do_DB: 
 27           Replicate_Ignore_DB: 
 28            Replicate_Do_Table: 
 29        Replicate_Ignore_Table: 
 30       Replicate_Wild_Do_Table: 
 31   Replicate_Wild_Ignore_Table: 
 32                    Last_Errno: 0
 33                    Last_Error: 
 34                  Skip_Counter: 0
 35           Exec_Master_Log_Pos: 0
 36               Relay_Log_Space: 169
 37               Until_Condition: None
 38                Until_Log_File: 
 39                 Until_Log_Pos: 0
 40            Master_SSL_Allowed: No
 41            Master_SSL_CA_File: 
 42            Master_SSL_CA_Path: 
 43               Master_SSL_Cert: 
 44             Master_SSL_Cipher: 
 45                Master_SSL_Key: 
 46         Seconds_Behind_Master: NULL
 47 Master_SSL_Verify_Server_Cert: No
 48                 Last_IO_Errno: 0
 49                 Last_IO_Error: 
 50                Last_SQL_Errno: 0
 51                Last_SQL_Error: 
 52   Replicate_Ignore_Server_Ids: 
 53              Master_Server_Id: 0
 54                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
 55              Master_Info_File: mysql.slave_master_info
 56                     SQL_Delay: 0
 57           SQL_Remaining_Delay: NULL
 58       Slave_SQL_Running_State: 
 59            Master_Retry_Count: 86400
 60                   Master_Bind: 
 61       Last_IO_Error_Timestamp: 
 62      Last_SQL_Error_Timestamp: 
 63                Master_SSL_Crl: 
 64            Master_SSL_Crlpath: 
 65            Retrieved_Gtid_Set: 
 66             Executed_Gtid_Set: 
 67                 Auto_Position: 1
 68          Replicate_Rewrite_DB: 
 69                  Channel_Name: 
 70            Master_TLS_Version: 
 71 1 row in set (0.00 sec)
 72 
 73 //Set gtid_purged variable.
 74 (root@localhost mysql3306.sock)[sysbench]>SET @@GLOBAL.GTID_PURGED='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210';
 75 Query OK, 0 rows affected (0.00 sec)
 76 
 77 (root@localhost mysql3306.sock)[sysbench]>show slave statusG
 78 *************************** 1. row ***************************
 79                Slave_IO_State: 
 80                   Master_Host: 192.168.1.101
 81                   Master_User: repl
 82                   Master_Port: 3306
 83                 Connect_Retry: 60
 84               Master_Log_File: 
 85           Read_Master_Log_Pos: 4
 86                Relay_Log_File: relay-bin.000001
 87                 Relay_Log_Pos: 4
 88         Relay_Master_Log_File: 
 89              Slave_IO_Running: No
 90             Slave_SQL_Running: No
 91               Replicate_Do_DB: 
 92           Replicate_Ignore_DB: 
 93            Replicate_Do_Table: 
 94        Replicate_Ignore_Table: 
 95       Replicate_Wild_Do_Table: 
 96   Replicate_Wild_Ignore_Table: 
 97                    Last_Errno: 0
 98                    Last_Error: 
 99                  Skip_Counter: 0
100           Exec_Master_Log_Pos: 0
101               Relay_Log_Space: 169
102               Until_Condition: None
103                Until_Log_File: 
104                 Until_Log_Pos: 0
105            Master_SSL_Allowed: No
106            Master_SSL_CA_File: 
107            Master_SSL_CA_Path: 
108               Master_SSL_Cert: 
109             Master_SSL_Cipher: 
110                Master_SSL_Key: 
111         Seconds_Behind_Master: NULL
112 Master_SSL_Verify_Server_Cert: No
113                 Last_IO_Errno: 0
114                 Last_IO_Error: 
115                Last_SQL_Errno: 0
116                Last_SQL_Error: 
117   Replicate_Ignore_Server_Ids: 
118              Master_Server_Id: 0
119                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
120              Master_Info_File: mysql.slave_master_info
121                     SQL_Delay: 0
122           SQL_Remaining_Delay: NULL
123       Slave_SQL_Running_State: 
124            Master_Retry_Count: 86400
125                   Master_Bind: 
126       Last_IO_Error_Timestamp: 
127      Last_SQL_Error_Timestamp: 
128                Master_SSL_Crl: 
129            Master_SSL_Crlpath: 
130            Retrieved_Gtid_Set: 
131             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210 //After set @@global.gtid_purged operation,Executed_Gitd_Set will contain it.
132                 Auto_Position: 1
133          Replicate_Rewrite_DB: 
134                  Channel_Name: 
135            Master_TLS_Version: 
136 1 row in set (0.00 sec)
137 
138 //Start IO Thread.
139 (root@localhost mysql3306.sock)[sysbench]>start slave io_thread;
140 Query OK, 0 rows affected (0.01 sec)
141 
142 (root@localhost mysql3306.sock)[sysbench]>show slave statusG
143 *************************** 1. row ***************************
144                Slave_IO_State: Waiting for master to send event
145                   Master_Host: 192.168.1.101
146                   Master_User: repl
147                   Master_Port: 3306
148                 Connect_Retry: 60
149               Master_Log_File: mysql-bin.000033 //The newly binlog has been pulled to local server.
150           Read_Master_Log_Pos: 190
151                Relay_Log_File: relay-bin.000001
152                 Relay_Log_Pos: 4
153         Relay_Master_Log_File: 
154              Slave_IO_Running: Yes //The IO Thread working normally.
155             Slave_SQL_Running: No
156               Replicate_Do_DB: 
157           Replicate_Ignore_DB: 
158            Replicate_Do_Table: 
159        Replicate_Ignore_Table: 
160       Replicate_Wild_Do_Table: 
161   Replicate_Wild_Ignore_Table: 
162                    Last_Errno: 0
163                    Last_Error: 
164                  Skip_Counter: 0
165           Exec_Master_Log_Pos: 0
166               Relay_Log_Space: 1433264
167               Until_Condition: None
168                Until_Log_File: 
169                 Until_Log_Pos: 0
170            Master_SSL_Allowed: No
171            Master_SSL_CA_File: 
172            Master_SSL_CA_Path: 
173               Master_SSL_Cert: 
174             Master_SSL_Cipher: 
175                Master_SSL_Key: 
176         Seconds_Behind_Master: NULL
177 Master_SSL_Verify_Server_Cert: No
178                 Last_IO_Errno: 0
179                 Last_IO_Error: 
180                Last_SQL_Errno: 0
181                Last_SQL_Error: 
182   Replicate_Ignore_Server_Ids: 
183              Master_Server_Id: 1013306
184                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
185              Master_Info_File: mysql.slave_master_info
186                     SQL_Delay: 0
187           SQL_Remaining_Delay: NULL
188       Slave_SQL_Running_State: 
189            Master_Retry_Count: 86400
190                   Master_Bind: 
191       Last_IO_Error_Timestamp: 
192      Last_SQL_Error_Timestamp: 
193                Master_SSL_Crl: 
194            Master_SSL_Crlpath: 
195            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214 //The newest gtid information has been got(3730211-3730214).
196             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730210
197                 Auto_Position: 1
198          Replicate_Rewrite_DB: 
199                  Channel_Name: 
200            Master_TLS_Version: 
201 1 row in set (0.00 sec)
202 
203 //Specify the replication filter only for table "sbtest9".
204 (root@localhost mysql3306.sock)[sysbench]>CHANGE REPLICATION FILTER REPLICATE_DO_TABLE = (sysbench.sbtest9);
205 Query OK, 0 rows affected (0.00 sec)
206 
207 //Analyze the binlog on master to find out the right postion of gtid_set.
208 [root@zlm2 10:20:28 ~]
209 #mysqlbinlog -v --base64-output=decode-rows /data/mysql/mysql3306/logs/mysql-bin.000033 > 33.log
210 
211 [root@zlm2 10:20:36 ~]
212 #cat 33.log
213 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
214 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
215 DELIMITER /*!*/;
216 # at 4
217 #180726  9:13:04 server id 1013306  end_log_pos 123     Start: binlog v 4, server v 5.7.21-log created 180726  9:13:04
218 # Warning: this binlog is either in use or was not closed properly.
219 # at 123
220 #180726  9:13:04 server id 1013306  end_log_pos 190     Previous-GTIDs
221 # 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213
222 # at 190
223 #180726 10:11:52 server id 1013306  end_log_pos 251     GTID    last_committed=0    sequence_number=1    rbr_only=no
224 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214'/*!*/;
225 # at 251
226 #180726 10:11:52 server id 1013306  end_log_pos 340     Query    thread_id=16    exec_time=0    error_code=0
227 use `sysbench`/*!*/;
228 SET TIMESTAMP=1532592712/*!*/;
229 SET @@session.pseudo_thread_id=16/*!*/;
230 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
231 SET @@session.sql_mode=1436549152/*!*/;
232 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
233 /*!C utf8 *//*!*/;
234 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
235 SET @@session.lc_time_names=0/*!*/;
236 SET @@session.collation_database=DEFAULT/*!*/;
237 truncate table sbtest9 //Here's the truncate operation,we are supposed the sql_thread just stop before this operation.
238 /*!*/;
239 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
240 DELIMITER ;
241 # End of log file
242 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
243 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
244 
245 //Start SQL Thread using until clause.
246 (root@localhost mysql3306.sock)[sysbench]>start slave sql_thread until SQL_BEFORE_GTIDS='1b7181ee-6eaf-11e8-998e-080027de0e0e:3730214';
247 Query OK, 0 rows affected (0.00 sec)
248 
249 (root@localhost mysql3306.sock)[sysbench]>show slave statusG
250 *************************** 1. row ***************************
251                Slave_IO_State: Waiting for master to send event
252                   Master_Host: 192.168.1.101
253                   Master_User: repl
254                   Master_Port: 3306
255                 Connect_Retry: 60
256               Master_Log_File: mysql-bin.000033
257           Read_Master_Log_Pos: 340
258                Relay_Log_File: relay-bin.000007
259                 Relay_Log_Pos: 395
260         Relay_Master_Log_File: mysql-bin.000033
261              Slave_IO_Running: Yes
262             Slave_SQL_Running: No
263               Replicate_Do_DB: 
264           Replicate_Ignore_DB: 
265            Replicate_Do_Table: sysbench.sbtest9 //Here's the "do table" option of replication filter.
266        Replicate_Ignore_Table: 
267       Replicate_Wild_Do_Table: 
268   Replicate_Wild_Ignore_Table: 
269                    Last_Errno: 0
270                    Last_Error: 
271                  Skip_Counter: 0
272           Exec_Master_Log_Pos: 190
273               Relay_Log_Space: 821
274               Until_Condition: SQL_BEFORE_GTIDS //Here's the option of until condition of start slave clause.
275                Until_Log_File: 
276                 Until_Log_Pos: 0
277            Master_SSL_Allowed: No
278            Master_SSL_CA_File: 
279            Master_SSL_CA_Path: 
280               Master_SSL_Cert: 
281             Master_SSL_Cipher: 
282                Master_SSL_Key: 
283         Seconds_Behind_Master: NULL
284 Master_SSL_Verify_Server_Cert: No
285                 Last_IO_Errno: 0
286                 Last_IO_Error: 
287                Last_SQL_Errno: 0
288                Last_SQL_Error: 
289   Replicate_Ignore_Server_Ids: 
290              Master_Server_Id: 1013306
291                   Master_UUID: 1b7181ee-6eaf-11e8-998e-080027de0e0e
292              Master_Info_File: mysql.slave_master_info
293                     SQL_Delay: 0
294           SQL_Remaining_Delay: NULL
295       Slave_SQL_Running_State: 
296            Master_Retry_Count: 86400
297                   Master_Bind: 
298       Last_IO_Error_Timestamp: 
299      Last_SQL_Error_Timestamp: 
300                Master_SSL_Crl: 
301            Master_SSL_Crlpath: 
302            Retrieved_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:3730211-3730214
303             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730213
304                 Auto_Position: 1
305          Replicate_Rewrite_DB: 
306                  Channel_Name: 
307            Master_TLS_Version: 
308 1 row in set (0.00 sec)
309 
310 //Check the contents of rescued table.
311 (root@localhost mysql3306.sock)[sysbench]>select count(*) from sbtest9;
312  ---------- 
313 | count(*) |
314  ---------- 
315 |     2500 | //This is the correct number of records before we truncate the table on master.
316  ---------- 
317 1 row in set (0.00 sec)
318 
319 //Likewise,we can copy the rescued table back to master in a proper certain time by transportable tablespace tech(I'm not going to demonstrate here).

**Stop slave and check the value of  "Relay_Master_Log_File**","**Exec_Master_Log_Pos**" and "**Retrieved_Gtid_Set**".**

Shutdown instance and copy back.

 1 [root@zlm3 07:48:10 /data/backup]
 2 #cp relay* /data/mysql/mysql3306/data
 3 
 4 [root@zlm3 07:48:27 /data/backup]
 5 #cd /data/mysql/mysql3306/data
 6 
 7 [root@zlm3 07:48:35 /data/mysql/mysql3306/data]
 8 #ls -l|grep relay
 9 -rw-r----- 1 root  root     954479 Jul 31 07:48 relay-bin.000043 //Notice,the owner and group has been changed.
10 -rw-r----- 1 root  root        209 Jul 31 07:48 relay-bin.000044
11 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_applier.000001
12 -rw-r----- 1 mysql mysql        45 Jul 31 06:55 relay-bin-group_replication_applier.index
13 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_recovery.000001
14 -rw-r----- 1 mysql mysql        46 Jul 31 06:55 relay-bin-group_replication_recovery.index
15 -rw-r--r-- 1 root  root         60 Jul 31 07:48 relay-bin.index
16 
17 [root@zlm3 07:48:40 /data/mysql/mysql3306/data]
18 #chown mysql.mysql relay*
19 
20 [root@zlm3 07:49:45 /data/mysql/mysql3306/data]
21 #ls -l|grep relay
22 -rw-r----- 1 mysql mysql    954479 Jul 31 07:48 relay-bin.000043
23 -rw-r----- 1 mysql mysql       209 Jul 31 07:48 relay-bin.000044
24 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_applier.000001
25 -rw-r----- 1 mysql mysql        45 Jul 31 06:55 relay-bin-group_replication_applier.index
26 -rw-r----- 1 mysql mysql       150 Jul 31 06:55 relay-bin-group_replication_recovery.000001
27 -rw-r----- 1 mysql mysql        46 Jul 31 06:55 relay-bin-group_replication_recovery.index
28 -rw-r--r-- 1 mysql mysql        60 Jul 31 07:48 relay-bin.index

 

 

 1 [root@zlm2 16:39:39 /data/backup]
 2 #ps aux|grep mysqld
 3 mysql     3771  0.0 18.7 1071816 190784 pts/0  Sl   15:52   0:01 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
 4 root      4451  0.0  0.0 112640   956 pts/1    R    16:43   0:00 grep --color=auto mysqld
 5 
 6 [root@zlm2 16:43:10 /data/backup]
 7 #mysqladmin shutdown
 8 
 9 [root@zlm2 16:43:15 /data/backup]
10 #ps aux|grep mysqld
11 root      4463  0.0  0.0 112640   956 pts/1    R    16:43   0:00 grep --color=auto mysqld
12 
13 [root@zlm2 16:43:18 /data/backup]
14 #cd /data/mysql/mysql3306/data/
15 
16 [root@zlm2 16:43:36 /data/mysql/mysql3306/data]
17 #rm -rf *
18 
19 [root@zlm2 16:43:39 /data/mysql/mysql3306/data]
20 #cd ../logs
21 
22 [root@zlm2 16:43:50 /data/mysql/mysql3306/logs]
23 #rm -rf *
24 
25 [root@zlm2 16:43:53 /data/mysql/mysql3306/logs]
26 #innobackupex --defaults-file=/data/mysql/mysql3306/my3306.cnf -uroot -pPassw0rd --copy-back /data/backup/2018-07-29_16-32-33/
27 ... //Omitted.

 

Summary

 1 (zlm@192.168.1.103 3308)[(none)]>stop slave;
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.103 3308)[(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: 192.168.1.102
 8                   Master_User: repl
 9                   Master_Port: 3308
10                 Connect_Retry: 60
11               Master_Log_File: mysql-bin.000023
12           Read_Master_Log_Pos: 1718
13                Relay_Log_File: zlm4-relay-bin.000003
14                 Relay_Log_Pos: 775
15         Relay_Master_Log_File: mysql-bin.000023 //This binlog file is which one we need to analyze later on master node zlm3.
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 1718 //This position is where we need to set the Xid.
28               Relay_Log_Space: 2397
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 1023308
46                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
47              Master_Info_File: /data/mysql/mysql3308/data/master.info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-25
58             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-25
59                 Auto_Position: 1
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

 

**Restart the mysqld process.**

  • There always be some miss operations such as drop,truncate which cannot be flashed back easily by tools.We should be careful to avoid them.
  • Onlyif you have a full database backup(mysqldump or Xtraback) and vital binlog,the destroyed table could be rescued.
  • The portion of recovering imcremental data also can be used in Xtrabackup method when rescuing lost data.
  • It's recommend to rename the rescued table before copying it back to the product database with transportable tablespace.

 

Restart instance and check table.

 1 [root@zlm3 08:25:18 /data/mysql/mysql3306/data]
 2 #mysqladmin shutdown
 3 
 4 [root@zlm3 08:31:25 /data/mysql/mysql3306/data]
 5 #ps aux|grep mysqld
 6 root      4309  0.0  0.0 112640   956 pts/1    R    08:31   0:00 grep --color=auto mysqld
 7 
 8 [root@zlm3 08:31:35 /data/mysql/mysql3306/data]
 9 #sh /root/mysqld.sh
10 
11 [root@zlm3 08:31:45 /data/mysql/mysql3306/data]
12 #ps aux|grep mysqld
13 mysql     4315 11.5 17.8 1044468 181776 pts/1  Sl   08:31   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my.cnf
14 root      4348  0.0  0.0 112640   960 pts/1    R    08:31   0:00 grep --color=auto mysqld

 

Analyze the binlog file we get above(here is mysql-bin.000023).

 1 [root@zlm2 16:44:41 /data/mysql/mysql3306/logs]
 2 #cd ../data
 3 
 4 [root@zlm2 16:47:40 /data/mysql/mysql3306/data]
 5 #chown -R mysql.mysql *
 6 
 7 [root@zlm2 16:47:49 /data/mysql/mysql3306/data]
 8 #sh /root/mysqld.sh
 9 
10 [root@zlm2 16:47:56 /data/mysql/mysql3306/data]
11 #ps aux|grep mysqld
12 mysql     4514  3.2 17.9 1071804 182316 pts/1  Sl   16:47   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
13 root      4547  0.0  0.0 112640   960 pts/1    R    16:48   0:00 grep --color=auto mysqld
14 
15 [root@zlm2 16:48:03 /data/mysql/mysql3306/data]
16 #mysql
17 Welcome to the MySQL monitor.  Commands end with ; or g.
18 Your MySQL connection id is 2
19 Server version: 5.7.21-log MySQL Community Server (GPL)
20 
21 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
22 
23 Oracle is a registered trademark of Oracle Corporation and/or its
24 affiliates. Other names may be trademarks of their respective
25 owners.
26 
27 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
28 
29 zlm@192.168.56.101:3306 [(none)]>use sysbench
30 Reading table information for completion of table and column names
31 You can turn off this feature to get a quicker startup with -A
32 
33 Database changed
34 zlm@192.168.56.101:3306 [sysbench]>show tables;
35  -------------------- 
36 | Tables_in_sysbench |
37  -------------------- 
38 | sbtest1            |
39 | sbtest2            |
40 | sbtest3            |
41 | sbtest4            |
42 | sbtest5            |
43 | sbtest6            |
44  -------------------- 
45 6 rows in set (0.00 sec)
46 
47 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
48  ---- --- --- ----- 
49 | id | k | c | pad |
50  ---- --- --- ----- 
51 |  1 | 1 | a | b   |
52  ---- --- --- ----- 
53 1 row in set (0.00 sec)

 

  1 [root@zlm3 10:54:51 /data/mysql/mysql3308/logs]
  2 #mysqlbinlog -v --base64-output=decode-rows mysql-bin.000023 > ~/23.log
  3 
  4 [root@zlm3 10:55:27 /data/mysql/mysql3308/logs]
  5 #cat ~/23.log
  6 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  7 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  8 DELIMITER /*!*/;
  9 # at 4
 10 #180809  4:09:22 server id 1023308  end_log_pos 123 CRC32 0x6f7e8565     Start: binlog v 4, server v 5.7.22-22-29.26-log created 180809  4:09:22 at startup
 11 # Warning: this binlog is either in use or was not closed properly.
 12 ROLLBACK/*!*/;
 13 # at 123
 14 #180809  4:09:22 server id 1023308  end_log_pos 194 CRC32 0x43a7c3b7     Previous-GTIDs
 15 # 42aada54-65ea-ee17-55f0-b47cf387c038:1-19
 16 # at 194
 17 #180809  9:07:52 server id 1023308  end_log_pos 259 CRC32 0x531076d3     GTID    last_committed=0    sequence_number=1    rbr_only=no
 18 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:20'/*!*/;
 19 # at 259
 20 #180809  9:07:52 server id 1023308  end_log_pos 350 CRC32 0x9acb4e3f     Query    thread_id=5    exec_time=0    error_code=0
 21 SET TIMESTAMP=1533798472/*!*/;
 22 SET @@session.pseudo_thread_id=5/*!*/;
 23 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
 24 SET @@session.sql_mode=1436549152/*!*/;
 25 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 26 /*!C utf8 *//*!*/;
 27 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
 28 SET @@session.lc_time_names=0/*!*/;
 29 SET @@session.collation_database=DEFAULT/*!*/;
 30 create database zlm
 31 /*!*/;
 32 # at 350
 33 #180809  9:08:36 server id 1023308  end_log_pos 415 CRC32 0xde316a34     GTID    last_committed=1    sequence_number=2    rbr_only=no
 34 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:21'/*!*/;
 35 # at 415
 36 #180809  9:08:36 server id 1023308  end_log_pos 557 CRC32 0xb29bd4ab     Query    thread_id=5    exec_time=0    error_code=0
 37 use `zlm`/*!*/;
 38 SET TIMESTAMP=1533798516/*!*/;
 39 create table t1(
 40 id int,
 41 name char(10)
 42 ) engine=innodb charset=utf8mb4
 43 /*!*/;
 44 # at 557
 45 #180809  9:13:42 server id 1023308  end_log_pos 622 CRC32 0x2a5f6414     GTID    last_committed=2    sequence_number=3    rbr_only=no
 46 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:22'/*!*/;
 47 # at 622
 48 #180809  9:13:42 server id 1023308  end_log_pos 728 CRC32 0xa803e3aa     Query    thread_id=5    exec_time=0    error_code=0
 49 SET TIMESTAMP=1533798822/*!*/;
 50 alter table t1 add primary key(id)
 51 /*!*/;
 52 # at 728
 53 #180809  9:13:46 server id 1023308  end_log_pos 793 CRC32 0xfd677245     GTID    last_committed=3    sequence_number=4    rbr_only=yes
 54 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 55 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:23'/*!*/;
 56 # at 793
 57 #180809  9:13:46 server id 1023308  end_log_pos 869 CRC32 0x7b568d5a     Query    thread_id=5    exec_time=0    error_code=0
 58 SET TIMESTAMP=1533798826/*!*/;
 59 SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
 60 BEGIN
 61 /*!*/;
 62 # at 869
 63 # at 955
 64 #180809  9:13:46 server id 1023308  end_log_pos 1002 CRC32 0x8b4e5e6b     Table_map: `zlm`.`t1` mapped to number 110
 65 # at 1002
 66 #180809  9:13:46 server id 1023308  end_log_pos 1076 CRC32 0x51750bc0     Write_rows: table id 110 flags: STMT_END_F
 67 ### INSERT INTO `zlm`.`t1`
 68 ### SET
 69 ###   @1=1
 70 ###   @2='MySQL'
 71 ### INSERT INTO `zlm`.`t1`
 72 ### SET
 73 ###   @1=2
 74 ###   @2='Oracle'
 75 ### INSERT INTO `zlm`.`t1`
 76 ### SET
 77 ###   @1=3
 78 ###   @2='PostgreSQL'
 79 # at 1076
 80 #180809  9:13:46 server id 1023308  end_log_pos 1107 CRC32 0x6033f0ee     Xid = 26
 81 COMMIT/*!*/;
 82 # at 1107
 83 #180809 10:12:07 server id 1023308  end_log_pos 1172 CRC32 0x66f4de8c     GTID    last_committed=4    sequence_number=5    rbr_only=no
 84 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:24'/*!*/;
 85 # at 1172
 86 #180809 10:12:07 server id 1023308  end_log_pos 1397 CRC32 0x86665c03     Query    thread_id=8    exec_time=0    error_code=0
 87 SET TIMESTAMP=1533802327/*!*/;
 88 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
 89 GRANT ALL PRIVILEGES ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*872ECE72A7EBAC6A183C90D7043D5F359BD85A9E'
 90 /*!*/;
 91 # at 1397
 92 #180809 10:50:26 server id 1023308  end_log_pos 1462 CRC32 0x9e027e44     GTID    last_committed=5    sequence_number=6    rbr_only=yes
 93 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
 94 SET @@SESSION.GTID_NEXT= '42aada54-65ea-ee17-55f0-b47cf387c038:25'/*!*/;
 95 # at 1462
 96 #180809 10:50:26 server id 1023308  end_log_pos 1538 CRC32 0xe5d003cf     Query    thread_id=8    exec_time=0    error_code=0
 97 SET TIMESTAMP=1533804626/*!*/;
 98 SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2/*!*/;
 99 BEGIN
100 /*!*/;
101 # at 1538
102 # at 1594
103 #180809 10:50:26 server id 1023308  end_log_pos 1641 CRC32 0x81684c91     Table_map: `zlm`.`t1` mapped to number 110
104 # at 1641
105 #180809 10:50:26 server id 1023308  end_log_pos 1687 CRC32 0x19b42ee8     Write_rows: table id 110 flags: STMT_END_F
106 ### INSERT INTO `zlm`.`t1`
107 ### SET
108 ###   @1=4
109 ###   @2='Redis'
110 # at 1687
111 #180809 10:50:26 server id 1023308  end_log_pos 1718 CRC32 0x3429fa99     Xid = 28
112 COMMIT/*!*/;
113 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
114 DELIMITER ;
115 # End of log file
116 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
117 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
118 
119 [root@zlm3 11:00:48 /data/mysql/mysql3308/logs]
120 #ls -l
121 total 12
122 -rw-r----- 1 mysql mysql  194 Aug  9 04:09 mysql-bin.000022
123 -rw-r----- 1 mysql mysql 1718 Aug  9 10:50 mysql-bin.000023 //The 1718 bytes is right the position we need to decide which Xid we should set.
124 -rw-r----- 1 mysql mysql   88 Aug  9 04:09 mysql-bin.index
125 
126 //As there're no other transactions found in mysql-bin.000023.The last "Xid = 28" is what we need to set on node zlm4.

 

**Check out the first consistent position we need.**

 

*Step 4: **Implement a slave with binlog server.***

1 [root@zlm3 08:34:15 /data/backup/2018-07-31_06-29-03]
2 #cat xtrabackup_binlog_info
3 mysql-bin.000043    190    1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229

Modify the grastate.dat file on node zlm4(if not exist,touch a new file of it).

 

 

 1 [root@zlm4 11:07:38 /data/mysql/mysql3308/data]
 2 #vim grastate.dat
 3 
 4 [root@zlm4 11:08:01 /data/mysql/mysql3308/data]
 5 #cat grastate.dat 
 6 #cat grastate.dat
 7 # GALERA saved state
 8 version: 2.1
 9 uuid:    bd5525ab-9a15-11e8-aa0f-4b830c783fc7
10 seqno:   28
11 safe_to_bootstrap: 0
12 
13 [root@zlm4 11:08:42 /data/mysql/mysql3308/data]
14 #ls -l|grep grastate.dat 
15 -rw-r--r-- 1 root  root        131 Aug  9 11:08 grastate.dat
16 
17 [root@zlm4 11:08:03 /data/mysql/mysql3308/data]
18 #chown mysql.mysql grastate.dat 
19 
20 [root@zlm4 11:08:11 /data/mysql/mysql3308/data]
21 #ls -l|grep grastate.dat 
22 -rw-r--r-- 1 mysql mysql       131 Aug  9 11:08 grastate.dat

Start mysqld on binlog server.

**Execute "change master to" as below.**

 

 1 [root@zlm3 16:50:00 /data/mysql/mysql3306/logs]
 2 #ps aux|grep mysqld
 3 root      4405  0.0  0.0 112640   960 pts/0    R    16:50   0:00 grep --color=auto mysqld
 4 
 5 [root@zlm3 16:50:08 /data/mysql/mysql3306/logs]
 6 #sh /root/mysqld.sh
 7 
 8 [root@zlm3 16:50:13 /data/mysql/mysql3306/logs]
 9 #ps aux|grep mysqld
10 mysql     4411 16.5 15.6 498232 159408 pts/0   Rl   16:50   0:00 mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
11 root      4429  0.0  0.0 112640   960 pts/0    R    16:50   0:00 grep --color=auto mysqld
12 
13 [root@zlm3 16:51:03 /data/mysql/mysql3306/logs]
14 #ls -l
15 total 11216
16 -rw-r----- 1 root  root      4128 Jul 29 08:42 mysql-bin.000095
17 -rw-r----- 1 root  root       241 Jul 29 08:42 mysql-bin.000096
18 -rw-r----- 1 root  root  11461562 Jul 29 09:07 mysql-bin.000097
19 -rw-r----- 1 mysql mysql      154 Jul 29 16:50 mysql-bin.000098
20 -rw-r----- 1 mysql mysql       44 Jul 29 16:50 mysql-bin.index
21 
22 [root@zlm3 16:51:04 /data/mysql/mysql3306/logs]
23 #cat mysql-bin.index
24 /data/mysql/mysql3306/logs/mysql-bin.000098
 1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
 2 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization
 3 (zlm@192.168.1.102 3306)[(none)]>show master status;
 4  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
 5 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
 6  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
 7 | mysql-bin.000004 |      206 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 |
 8  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
 9 1 row in set (0.00 sec)
10 
11 (zlm@192.168.1.102 3306)[(none)]>reset master;
12 Query OK, 0 rows affected (0.02 sec)
13 
14 (zlm@192.168.1.102 3306)[(none)]>show master status;
15  ------------------ ---------- -------------- ------------------ ------------------- 
16 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
17  ------------------ ---------- -------------- ------------------ ------------------- 
18 | mysql-bin.000001 |      150 |              |                  |                   |
19  ------------------ ---------- -------------- ------------------ ------------------- 
20 1 row in set (0.00 sec)
21 
22 (zlm@192.168.1.102 3306)[(none)]>set @@global.gtid_purged='1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229';
23 Query OK, 0 rows affected (0.01 sec)
24 
25 (zlm@192.168.1.102 3306)[(none)]>show master status;
26  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
27 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                              |
28  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
29 | mysql-bin.000002 |      150 |              |                  | 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229 |
30  ------------------ ---------- -------------- ------------------ ------------------------------------------------ 
31 1 row in set (0.00 sec)
32 
33 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
34 ERROR 1380 (HY000): Failed initializing relay log position: Could not find first log during relay log initialization
35 
36 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
37 *************************** 1. row ***************************
38                Slave_IO_State: 
39                   Master_Host: xxx
40                   Master_User: test
41                   Master_Port: 3306
42                 Connect_Retry: 60
43               Master_Log_File: 
44           Read_Master_Log_Pos: 4
45                Relay_Log_File: relay-bin.000043
46                 Relay_Log_Pos: 190
47         Relay_Master_Log_File: 
48              Slave_IO_Running: No
49             Slave_SQL_Running: No
50               Replicate_Do_DB: 
51           Replicate_Ignore_DB: 
52            Replicate_Do_Table: 
53        Replicate_Ignore_Table: 
54       Replicate_Wild_Do_Table: 
55   Replicate_Wild_Ignore_Table: 
56                    Last_Errno: 0
57                    Last_Error: 
58                  Skip_Counter: 0
59           Exec_Master_Log_Pos: 0
60               Relay_Log_Space: 0
61               Until_Condition: None
62                Until_Log_File: 
63                 Until_Log_Pos: 0
64            Master_SSL_Allowed: No
65            Master_SSL_CA_File: 
66            Master_SSL_CA_Path: 
67               Master_SSL_Cert: 
68             Master_SSL_Cipher: 
69                Master_SSL_Key: 
70         Seconds_Behind_Master: NULL
71 Master_SSL_Verify_Server_Cert: No
72                 Last_IO_Errno: 0
73                 Last_IO_Error: 
74                Last_SQL_Errno: 0
75                Last_SQL_Error: 
76   Replicate_Ignore_Server_Ids: 
77              Master_Server_Id: 0
78                   Master_UUID: 
79              Master_Info_File: mysql.slave_master_info
80                     SQL_Delay: 0
81           SQL_Remaining_Delay: NULL
82       Slave_SQL_Running_State: 
83            Master_Retry_Count: 86400
84                   Master_Bind: 
85       Last_IO_Error_Timestamp: 
86      Last_SQL_Error_Timestamp: 
87                Master_SSL_Crl: 
88            Master_SSL_Crlpath: 
89            Retrieved_Gtid_Set: //No relay logs was retrieved here.
90             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
91                 Auto_Position: 0
92          Replicate_Rewrite_DB: 
93                  Channel_Name: 
94            Master_TLS_Version: 
95 1 row in set (0.00 sec)

Modify the my3308.cnf file to restore the parameter of PXC.

 

 

 1 [root@zlm4 11:10:28 /data/mysql/mysql3308]
 2 #vim my3308.cnf 
 3 
 4 [root@zlm4 11:11:03 /data/mysql/mysql3308]
 5 #cat my3308.cnf |grep wsrep
 6 loose-wsrep_cluster_name=pxc_wubx 
 7 loose-wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.1.103
 8 loose-wsrep_node_address=192.168.1.103
 9 loose-wsrep_provider=/usr/local/mysql/lib/libgalera_smm.so
10 #loose-wsrep_sst_method=xtrabackup-v2
11 loose-wsrep_sst_method=rsync
12 loose-wsrep_sst_auth=sst:zlmzlm
13 #loose-wsrep_debug=on
14 #loose-wsrep_provider_options="debug=on"

Disguise the binlog server is a fake master.

    I was stuck again,faint!T_T...

 

 1 [root@zlm3 16:52:59 /data/mysql/mysql3306/logs]
 2 #rm -f mysql-bin.00009*
 3 [1]   Done                    mysqlbinlog -R --raw -h192.168.56.100 -urepl -prepl4slave -P3306 --stop-never mysql-bin.000098  (wd: /data)
 4 (wd now: /data/mysql/mysql3306/logs)
 5 
 6 [root@zlm3 17:00:06 /data/mysql/mysql3306/logs]
 7 #ls -l
 8 total 4
 9 -rw-r----- 1 mysql mysql 44 Jul 29 16:50 mysql-bin.index
10 
11 [root@zlm3 17:00:27 /data/mysql/mysql3306/logs]
12 #ps aux|grep mysqlbinlog
13 root      4475  0.0  0.0 112640   960 pts/0    R    17:00   0:00 grep --color=auto mysqlbinlog
14 
15 [root@zlm3 17:00:38 /data/mysql/mysql3306/logs]
16 #cp /data/mysql-bin* .
17 
18 [root@zlm3 17:02:48 /data/mysql/mysql3306/logs]
19 #ls -l
20 -rw-r----- 1 root  root  410 Jul 29 17:01 mysql-bin.000098
21 -rw-r----- 1 root  root  241 Jul 29 17:01 mysql-bin.000099
22 -rw-r----- 1 root  root  951 Jul 29 17:01 mysql-bin.000100
23 -rw-r----- 1 root  root  177 Jul 29 17:03 mysql-bin.index
24 
25 [root@zlm3 17:02:54 /data/mysql/mysql3306/logs]
26 #ls -1 | awk '{print i$0}' i=`pwd`'/'|grep mysql-bin.000 > mysql-bin.index
27 
28 [root@zlm3 17:03:06 /data/mysql/mysql3306/logs]
29 #cat mysql-bin.index 
30 /data/mysql/mysql3306/logs/mysql-bin.000098
31 /data/mysql/mysql3306/logs/mysql-bin.000099
32 /data/mysql/mysql3306/logs/mysql-bin.000100
33 
34 [root@zlm3 17:16:14 /data/mysql/mysql3306/logs]
35 #chown mysql.mysql *
36 
37 [root@zlm3 17:17:23 /data/mysql/mysql3306/logs]
38 #ls -l
39 total 16
40 -rw-r----- 1 mysql mysql 410 Jul 29 17:01 mysql-bin.000098
41 -rw-r----- 1 mysql mysql 241 Jul 29 17:01 mysql-bin.000099
42 -rw-r----- 1 mysql mysql 951 Jul 29 17:01 mysql-bin.000100
43 -rw-r----- 1 mysql mysql 177 Jul 29 17:03 mysql-bin.index

 

**Startup the mysqld on node zlm4.**

 

Supplemented on August 1.

  1 [root@zlm4 11:17:09 /data/mysql/mysql3308]
  2 #!ps
  3 ps aux|grep mysqld
  4 mysql     6514 12.0 19.1 1256144 194664 pts/3  Dl   11:17   0:00 mysqld --defaults-file=/data/mysql/mysql3308/my3308.cnf
  5 root      6539  0.0  0.0 112640   960 pts/3    R    11:17   0:00 grep --color=auto mysqld
  6 
  7 //Check the error log for detail.
  8 [root@zlm4 11:17:01 /data/mysql/mysql3308/data]
  9 #tail -f error.log 
 10 
 11 2018-08-09T11:17:09.518383 01:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
 12 2018-08-09T11:17:09.519740 01:00 0 [Warning] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
 13 2018-08-09T11:17:09.519805 01:00 0 [Note] mysqld (mysqld 5.7.22-22-29.26-log) starting as process 6514 ...
 14 2018-08-09T11:17:09.522792 01:00 0 [Note] WSREP: Setting wsrep_ready to false
 15 2018-08-09T11:17:09.522812 01:00 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
 16 2018-08-09T11:17:09.522818 01:00 0 [Note] WSREP: wsrep_load(): loading provider library '/usr/local/mysql/lib/libgalera_smm.so'
 17 2018-08-09T11:17:09.528196 01:00 0 [Note] WSREP: wsrep_load(): Galera 3.26(r) by Codership Oy <info@codership.com> loaded successfully.
 18 2018-08-09T11:17:09.528328 01:00 0 [Note] WSREP: CRC-32C: using hardware acceleration.
 19 2018-08-09T11:17:09.528860 01:00 0 [Note] WSREP: Found saved state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28, safe_to_bootstrap: 0
 20 2018-08-09T11:17:09.534206 01:00 0 [Note] WSREP: Passing config to GCS: base_dir = /data/mysql/mysql3308/data/; base_host = 192.168.1.103; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.join_retrans_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.user_send_window = 4; evs.view_forget_timeout = PT24H; gcache.dir = /data/mysql/mysql3308/data/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /data/mysql/mysql3308/data//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1; gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.segment = 0; gmcast.version = 0; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.npvo = false; pc.recovery = 1; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 8; socket.checksum = 2; socket.recv_buf_size = 212992; 
 21 2018-08-09T11:17:09.547103 01:00 0 [Note] WSREP: GCache history reset: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:0 -> bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28
 22 2018-08-09T11:17:09.551477 01:00 0 [Note] WSREP: Assign initial position for certification: 28, protocol version: -1
 23 2018-08-09T11:17:09.551517 01:00 0 [Note] WSREP: Preparing to initiate SST/IST
 24 2018-08-09T11:17:09.551524 01:00 0 [Note] WSREP: Starting replication
 25 2018-08-09T11:17:09.551542 01:00 0 [Note] WSREP: Setting initial position to bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28
 26 2018-08-09T11:17:09.551666 01:00 0 [Note] WSREP: Using CRC-32C for message checksums.
 27 2018-08-09T11:17:09.551752 01:00 0 [Note] WSREP: gcomm thread scheduling priority set to other:0 
 28 2018-08-09T11:17:09.551861 01:00 0 [Warning] WSREP: Fail to access the file (/data/mysql/mysql3308/data//gvwstate.dat) error (No such file or directory). It is possible if node is booting for first time or re-booting after a graceful shutdown
 29 2018-08-09T11:17:09.551870 01:00 0 [Note] WSREP: Restoring primary-component from disk failed. Either node is booting for first time or re-booting after a graceful shutdown
 30 2018-08-09T11:17:09.552488 01:00 0 [Note] WSREP: GMCast version 0
 31 2018-08-09T11:17:09.553245 01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') listening at tcp://0.0.0.0:4567
 32 2018-08-09T11:17:09.553297 01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') multicast: , ttl: 1
 33 2018-08-09T11:17:09.553883 01:00 0 [Note] WSREP: EVS version 0
 34 2018-08-09T11:17:09.554031 01:00 0 [Note] WSREP: gcomm: connecting to group 'pxc_wubx', peer '192.168.1.101:,192.168.1.102:,192.168.1.103:'
 35 2018-08-09T11:17:09.555884 01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') connection established to fe2c0efc tcp://192.168.1.103:4567
 36 2018-08-09T11:17:09.555917 01:00 0 [Warning] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') address 'tcp://192.168.1.103:4567' points to own listening address, blacklisting
 37 2018-08-09T11:17:09.557320 01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') connection established to 13eae368 tcp://192.168.1.101:4567
 38 2018-08-09T11:17:09.557398 01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: 
 39 2018-08-09T11:17:09.558019 01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') connection established to 23fb8f7a tcp://192.168.1.102:4567
 40 2018-08-09T11:17:09.789760 01:00 0 [Note] WSREP: declaring 13eae368 at tcp://192.168.1.101:4567 stable
 41 2018-08-09T11:17:09.789795 01:00 0 [Note] WSREP: declaring 23fb8f7a at tcp://192.168.1.102:4567 stable
 42 2018-08-09T11:17:10.791694 01:00 0 [Note] WSREP: Node 13eae368 state primary
 43 2018-08-09T11:17:10.793677 01:00 0 [Note] WSREP: Current view of cluster as seen by this node
 44 view (view_id(PRIM,13eae368,15)
 45 memb {
 46     13eae368,0
 47     23fb8f7a,0
 48     fe2c0efc,0
 49     }
 50 joined {
 51     }
 52 left {
 53     }
 54 partitioned {
 55     }
 56 )
 57 2018-08-09T11:17:10.793720 01:00 0 [Note] WSREP: Save the discovered primary-component to disk
 58 2018-08-09T11:17:11.055114 01:00 0 [Note] WSREP: gcomm: connected
 59 2018-08-09T11:17:11.055201 01:00 0 [Note] WSREP: Shifting CLOSED -> OPEN (TO: 0)
 60 2018-08-09T11:17:11.055278 01:00 0 [Note] WSREP: Waiting for SST/IST to complete.
 61 2018-08-09T11:17:11.055571 01:00 0 [Note] WSREP: New COMPONENT: primary = yes, bootstrap = no, my_idx = 2, memb_num = 3
 62 2018-08-09T11:17:11.055600 01:00 0 [Note] WSREP: STATE EXCHANGE: Waiting for state UUID.
 63 2018-08-09T11:17:11.055637 01:00 0 [Note] WSREP: STATE EXCHANGE: sent state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99
 64 2018-08-09T11:17:11.055645 01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99 from 0 (zlm2)
 65 2018-08-09T11:17:11.055653 01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99 from 1 (zlm3)
 66 2018-08-09T11:17:11.061999 01:00 0 [Note] WSREP: STATE EXCHANGE: got state msg: fee39480-9bb4-11e8-a6f3-7b3d75fc5b99 from 2 (zlm4)
 67 2018-08-09T11:17:11.062036 01:00 0 [Note] WSREP: Quorum results:
 68     version    = 4,
 69     component  = PRIMARY,
 70     conf_id    = 14,
 71     members    = 3/3 (primary/total),
 72     act_id     = 28,
 73     last_appl. = -1,
 74     protocols  = 0/8/3 (gcs/repl/appl),
 75     group UUID = bd5525ab-9a15-11e8-aa0f-4b830c783fc7
 76 2018-08-09T11:17:11.062046 01:00 0 [Note] WSREP: Flow-control interval: [173, 173]
 77 2018-08-09T11:17:11.062050 01:00 0 [Note] WSREP: Trying to continue unpaused monitor
 78 2018-08-09T11:17:11.062055 01:00 0 [Note] WSREP: Restored state OPEN -> JOINED (28)
 79 2018-08-09T11:17:11.062282 01:00 2 [Note] WSREP: REPL Protocols: 8 (3, 2)
 80 2018-08-09T11:17:11.062308 01:00 2 [Note] WSREP: New cluster view: global state: bd5525ab-9a15-11e8-aa0f-4b830c783fc7:28, view# 15: Primary, number of nodes: 3, my index: 2, protocol version 3
 81 2018-08-09T11:17:11.062314 01:00 2 [Note] WSREP: Setting wsrep_ready to true
 82 2018-08-09T11:17:11.062329 01:00 0 [Note] WSREP: SST complete, seqno: 28
 83 2018-08-09T11:17:11.063803 01:00 0 [Note] WSREP: Member 2.0 (zlm4) synced with group.
 84 2018-08-09T11:17:11.063831 01:00 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 28)
 85 2018-08-09T11:17:11.066318 01:00 0 [Warning] InnoDB: Using innodb_locks_unsafe_for_binlog is DEPRECATED. This option may be removed in future releases. Please use READ COMMITTED transaction isolation level instead; Please refer to http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
 86 2018-08-09T11:17:11.066413 01:00 0 [Note] InnoDB: PUNCH HOLE support available
 87 2018-08-09T11:17:11.066426 01:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 88 2018-08-09T11:17:11.066439 01:00 0 [Note] InnoDB: Uses event mutexes
 89 2018-08-09T11:17:11.066444 01:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
 90 2018-08-09T11:17:11.066448 01:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
 91 2018-08-09T11:17:11.066453 01:00 0 [Note] InnoDB: Using Linux native AIO
 92 2018-08-09T11:17:11.066471 01:00 0 [Note] InnoDB: Adjusting innodb_buffer_pool_instances from 4 to 1 since innodb_buffer_pool_size is less than 1024 MiB
 93 2018-08-09T11:17:11.067081 01:00 0 [Note] InnoDB: Number of pools: 1
 94 2018-08-09T11:17:11.067235 01:00 0 [Note] InnoDB: Using CPU crc32 instructions
 95 2018-08-09T11:17:11.068926 01:00 0 [Note] InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
 96 2018-08-09T11:17:11.071673 01:00 0 [Note] InnoDB: Completed initialization of buffer pool
 97 2018-08-09T11:17:11.074414 01:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
 98 2018-08-09T11:17:11.093895 01:00 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite
 99 2018-08-09T11:17:11.095767 01:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
100 2018-08-09T11:17:11.229162 01:00 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/mysql3308/data/xb_doublewrite, size 3932160 bytes
101 2018-08-09T11:17:11.369285 01:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
102 2018-08-09T11:17:11.369369 01:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
103 2018-08-09T11:17:11.773899 01:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
104 2018-08-09T11:17:11.774714 01:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
105 2018-08-09T11:17:11.774734 01:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
106 2018-08-09T11:17:11.775107 01:00 0 [Note] InnoDB: Waiting for purge to start
107 2018-08-09T11:17:11.845618 01:00 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.22-rel22 started; log sequence number 2641145
108 2018-08-09T11:17:11.845884 01:00 0 [Note] Plugin 'FEDERATED' is disabled.
109 2018-08-09T11:17:11.848626 01:00 0 [Note] InnoDB: Loading buffer pool(s) from /data/mysql/mysql3308/data/ib_buffer_pool
110 2018-08-09T11:17:11.882535 01:00 0 [Note] InnoDB: Buffer pool(s) load completed at 180809 11:17:11
111 2018-08-09T11:17:11.907836 01:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
112 2018-08-09T11:17:11.907856 01:00 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
113 2018-08-09T11:17:11.908353 01:00 0 [Warning] CA certificate ca.pem is self signed.
114 2018-08-09T11:17:11.908405 01:00 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
115 2018-08-09T11:17:11.908482 01:00 0 [Note] Server hostname (bind-address): '*'; port: 3308
116 2018-08-09T11:17:11.908505 01:00 0 [Note] IPv6 is available.
117 2018-08-09T11:17:11.908513 01:00 0 [Note]   - '::' resolves to '::';
118 2018-08-09T11:17:11.908527 01:00 0 [Note] Server socket created on IP: '::'.
119 2018-08-09T11:17:11.910446 01:00 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
120 2018-08-09T11:17:11.910481 01:00 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
121 2018-08-09T11:17:11.910489 01:00 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
122 2018-08-09T11:17:11.910495 01:00 0 [Warning] 'user' entry 'sst@localhost' ignored in --skip-name-resolve mode.
123 2018-08-09T11:17:11.910512 01:00 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
124 2018-08-09T11:17:11.910516 01:00 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
125 2018-08-09T11:17:11.910522 01:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
126 2018-08-09T11:17:11.911126 01:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
127 2018-08-09T11:17:11.911142 01:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
128 2018-08-09T11:17:11.913579 01:00 0 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=zlm4-relay-bin' to avoid this problem.
129 2018-08-09T11:17:11.925857 01:00 0 [Note] Event Scheduler: Loaded 0 events
130 2018-08-09T11:17:11.926371 01:00 0 [Note] mysqld: ready for connections.
131 Version: '5.7.22-22-29.26-log'  socket: '/tmp/mysql3308.sock'  port: 3308  Percona XtraDB Cluster binary (GPL) 5.7.22-29.26, Revision da86071, wsrep_29.26
132 2018-08-09T11:17:11.930200 01:00 2 [Note] WSREP: Initialized wsrep sidno 2
133 2018-08-09T11:17:11.930223 01:00 2 [Note] WSREP: Auto Increment Offset/Increment re-align with cluster membership change (Offset: 1 -> 3) (Increment: 1 -> 3)
134 2018-08-09T11:17:11.930233 01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
135 2018-08-09T11:17:11.930250 01:00 2 [Note] WSREP: Assign initial position for certification: 28, protocol version: 3
136 2018-08-09T11:17:11.930317 01:00 0 [Note] WSREP: Service thread queue flushed.
137 2018-08-09T11:17:11.930499 01:00 2 [Note] WSREP: Synchronized with group, ready for connections
138 2018-08-09T11:17:11.930505 01:00 2 [Note] WSREP: Setting wsrep_ready to true
139 2018-08-09T11:17:11.930507 01:00 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
140 2018-08-09T11:17:13.056460 01:00 0 [Note] WSREP: (fe2c0efc, 'tcp://0.0.0.0:4567') turning message relay requesting off

Change the server-uuid of binlog server to be equal with master.

 

 

 1 [root@zlm3 18:01:50 /data/mysql/mysql3306]
 2 #cd ../data
 3 
 4 [root@zlm3 18:01:53 /data/mysql/mysql3306/data]
 5 #cat auto.cnf
 6 [auto]
 7 server-uuid=e00ef9f5-6c4b-11e8-8389-080027de0e0e
 8 
 9 [root@zlm3 18:04:11 /data/mysql/mysql3306/data]
10 #vim auto.cnf
11 
12 [root@zlm3 18:04:26 /data/mysql/mysql3306/data]
13 #cat auto.cnf
14 [auto]
15 server-uuid=2a4b3562-2ab6-11e8-be7a-080027de0e0e

    After discussing with my classmate Shuaibing Zhang,I found out that the reason why I got failure above was due to not executing "reset slave all;".Therefore,it meantioned that "Could not find the first log..." when I Executed "change master to ... ".

**Check the status of PXC and the data of table t1.**

 

 

 1 (zlm@192.168.1.103 3308)[(none)]>show global status like '%wsrep%';
 2  ---------------------------------- ---------------------------------------------------------- 
 3 | Variable_name                    | Value                                                    |
 4  ---------------------------------- ---------------------------------------------------------- 
 5 | wsrep_local_state_uuid           | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
 6 | wsrep_protocol_version           | 8                                                        |
 7 | wsrep_last_applied               | 28                                                       |
 8 | wsrep_last_committed             | 28                                                       |
 9 | wsrep_replicated                 | 0                                                        |
10 | wsrep_replicated_bytes           | 0                                                        |
11 | wsrep_repl_keys                  | 0                                                        |
12 | wsrep_repl_keys_bytes            | 0                                                        |
13 | wsrep_repl_data_bytes            | 0                                                        |
14 | wsrep_repl_other_bytes           | 0                                                        |
15 | wsrep_received                   | 2                                                        |
16 | wsrep_received_bytes             | 279                                                      |
17 | wsrep_local_commits              | 0                                                        |
18 | wsrep_local_cert_failures        | 0                                                        |
19 | wsrep_local_replays              | 0                                                        |
20 | wsrep_local_send_queue           | 0                                                        |
21 | wsrep_local_send_queue_max       | 1                                                        |
22 | wsrep_local_send_queue_min       | 0                                                        |
23 | wsrep_local_send_queue_avg       | 0.000000                                                 |
24 | wsrep_local_recv_queue           | 0                                                        |
25 | wsrep_local_recv_queue_max       | 1                                                        |
26 | wsrep_local_recv_queue_min       | 0                                                        |
27 | wsrep_local_recv_queue_avg       | 0.000000                                                 |
28 | wsrep_local_cached_downto        | 0                                                        |
29 | wsrep_flow_control_paused_ns     | 0                                                        |
30 | wsrep_flow_control_paused        | 0.000000                                                 |
31 | wsrep_flow_control_sent          | 0                                                        |
32 | wsrep_flow_control_recv          | 0                                                        |
33 | wsrep_flow_control_interval      | [ 173, 173 ]                                             |
34 | wsrep_flow_control_interval_low  | 173                                                      |
35 | wsrep_flow_control_interval_high | 173                                                      |
36 | wsrep_flow_control_status        | OFF                                                      |
37 | wsrep_cert_deps_distance         | 0.000000                                                 |
38 | wsrep_apply_oooe                 | 0.000000                                                 |
39 | wsrep_apply_oool                 | 0.000000                                                 |
40 | wsrep_apply_window               | 0.000000                                                 |
41 | wsrep_commit_oooe                | 0.000000                                                 |
42 | wsrep_commit_oool                | 0.000000                                                 |
43 | wsrep_commit_window              | 0.000000                                                 |
44 | wsrep_local_state                | 4                                                        |
45 | wsrep_local_state_comment        | Synced                                                   |
46 | wsrep_cert_index_size            | 0                                                        |
47 | wsrep_cert_bucket_count          | 22                                                       |
48 | wsrep_gcache_pool_size           | 1320                                                     |
49 | wsrep_causal_reads               | 0                                                        |
50 | wsrep_cert_interval              | 0.000000                                                 |
51 | wsrep_ist_receive_status         |                                                          |
52 | wsrep_ist_receive_seqno_start    | 0                                                        |
53 | wsrep_ist_receive_seqno_current  | 0                                                        |
54 | wsrep_ist_receive_seqno_end      | 0                                                        |
55 | wsrep_incoming_addresses         | 192.168.1.101:3308,192.168.1.102:3308,192.168.1.103:3308 |
56 | wsrep_desync_count               | 0                                                        |
57 | wsrep_evs_delayed                |                                                          |
58 | wsrep_evs_evict_list             |                                                          |
59 | wsrep_evs_repl_latency           | 0/0/0/0/0                                                |
60 | wsrep_evs_state                  | OPERATIONAL                                              |
61 | wsrep_gcomm_uuid                 | fe2c0efc-9bb4-11e8-82b0-6b01a1a0030d                     |
62 | wsrep_cluster_conf_id            | 15                                                       |
63 | wsrep_cluster_size               | 3                                                        | //The size of PXC turned out to be three again.
64 | wsrep_cluster_state_uuid         | bd5525ab-9a15-11e8-aa0f-4b830c783fc7                     |
65 | wsrep_cluster_status             | Primary                                                  |
66 | wsrep_connected                  | ON                                                       |
67 | wsrep_local_bf_aborts            | 0                                                        |
68 | wsrep_local_index                | 2                                                        |
69 | wsrep_provider_name              | Galera                                                   |
70 | wsrep_provider_vendor            | Codership Oy <info@codership.com>                        |
71 | wsrep_provider_version           | 3.26(r)                                                  |
72 | wsrep_ready                      | ON                                                       |
73  ---------------------------------- ---------------------------------------------------------- 
74 68 rows in set (0.00 sec)
75 
76 (zlm@192.168.1.103 3308)[(none)]>select * from zlm.t1;
77  ---- ------------ 
78 | id | name       |
79  ---- ------------ 
80 |  1 | MySQL      |
81 |  2 | Oracle     |
82 |  3 | PostgreSQL |
83 |  4 | Redis      |
84  ---- ------------ 
85 4 rows in set (0.01 sec)
86 
87 //The fourth record of "Redis" has been shown correctly on node zlm4.

Execute "change master to ... " on zlm2.

Execute "change master to ... "

 

1 zlm@192.168.56.101:3306 [sysbench]>change master to 
2     -> master_host='192.168.56.102',
3     -> master_port=3306,
4     -> master_user='repl',
5     -> master_password='repl4slave',
6     -> master_auto_position=1;
7 Query OK, 0 rows affected, 2 warnings (0.01 sec)
 1 (zlm@192.168.1.102 3306)[(none)]>change master to relay_log_file='relay-bin.000043',relay_log_pos=190,master_host='xxx';
 2 Query OK, 0 rows affected (0.01 sec)
 3 
 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: xxx
 8                   Master_User: 
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: 
12           Read_Master_Log_Pos: 4
13                Relay_Log_File: relay-bin.000043
14                 Relay_Log_Pos: 190
15         Relay_Master_Log_File: 
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 0
28               Relay_Log_Space: 954838
29               Until_Condition: None
30                Until_Log_File: 
31                 Until_Log_Pos: 0
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 0
46                   Master_UUID: 
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730229
59                 Auto_Position: 0
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)

**Reset slave replication information on node zlm4.(optional)**

 

 

 1 (zlm@192.168.1.103 3308)[(none)]>show slave statusG
 2 *************************** 1. row ***************************
 3                Slave_IO_State: 
 4                   Master_Host: 192.168.1.102
 5                   Master_User: repl
 6                   Master_Port: 3308
 7                 Connect_Retry: 60
 8               Master_Log_File: mysql-bin.000023
 9           Read_Master_Log_Pos: 1718
10                Relay_Log_File: zlm4-relay-bin.000003
11                 Relay_Log_Pos: 775
12         Relay_Master_Log_File: mysql-bin.000023
13              Slave_IO_Running: No
14             Slave_SQL_Running: No
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 1718
25               Relay_Log_Space: 2831
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: NULL
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 0
38                 Last_IO_Error: 
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 0
43                   Master_UUID: 3ba41aa7-9b79-11e8-ad75-080027de0e0e
44              Master_Info_File: /data/mysql/mysql3308/data/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: 
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:20-25
55             Executed_Gtid_Set: 42aada54-65ea-ee17-55f0-b47cf387c038:1-25
56                 Auto_Position: 1
57          Replicate_Rewrite_DB: 
58                  Channel_Name: 
59            Master_TLS_Version: 
60 1 row in set (0.00 sec)
61 
62 (zlm@192.168.1.103 3308)[(none)]>reset slave all;
63 Query OK, 0 rows affected (0.03 sec)
64 
65 (zlm@192.168.1.103 3308)[(none)]>show slave statusG
66 Empty set (0.00 sec)
67 
68 //This step is merely used to make the environment clean and tidy.It's not necessary.

Start IO_Thread on zlm2.

Analyze the relay-bin file to find out the until postion before dropping operation.

 

 1 [root@zlm2 17:12:39 /data/backup/2018-07-29_16-32-33]
 2 #cat xtrabackup_binlog_info
 3 mysql-bin.000100    476    2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 4 
 5 zlm@192.168.56.101:3306 [sysbench]>reset master;
 6 Query OK, 0 rows affected (0.00 sec)
 7 
 8 zlm@192.168.56.101:3306 [sysbench]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
 9 Query OK, 0 rows affected (0.00 sec)
10 
11 zlm@192.168.56.101:3306 [sysbench]>start slave io_thread;
12 Query OK, 0 rows affected (0.00 sec)
13 
14 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
15 *************************** 1. row ***************************
16                Slave_IO_State: 
17                   Master_Host: 192.168.56.102
18                   Master_User: repl
19                   Master_Port: 3306
20                 Connect_Retry: 60
21               Master_Log_File: 
22           Read_Master_Log_Pos: 4
23                Relay_Log_File: relay-bin.000001
24                 Relay_Log_Pos: 4
25         Relay_Master_Log_File: 
26              Slave_IO_Running: No
27             Slave_SQL_Running: No
28               Replicate_Do_DB: 
29           Replicate_Ignore_DB: 
30            Replicate_Do_Table: 
31        Replicate_Ignore_Table: 
32       Replicate_Wild_Do_Table: 
33   Replicate_Wild_Ignore_Table: 
34                    Last_Errno: 0
35                    Last_Error: 
36                  Skip_Counter: 0
37           Exec_Master_Log_Pos: 0
38               Relay_Log_Space: 154
39               Until_Condition: None
40                Until_Log_File: 
41                 Until_Log_Pos: 0
42            Master_SSL_Allowed: No
43            Master_SSL_CA_File: 
44            Master_SSL_CA_Path: 
45               Master_SSL_Cert: 
46             Master_SSL_Cipher: 
47                Master_SSL_Key: 
48         Seconds_Behind_Master: NULL
49 Master_SSL_Verify_Server_Cert: No
50                 Last_IO_Errno: 1236
51                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
52                Last_SQL_Errno: 0
53                Last_SQL_Error: 
54   Replicate_Ignore_Server_Ids: 
55              Master_Server_Id: 1023306
56                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
57              Master_Info_File: /data/mysql/mysql3306/data/master.info
58                     SQL_Delay: 0
59           SQL_Remaining_Delay: NULL
60       Slave_SQL_Running_State: 
61            Master_Retry_Count: 86400
62                   Master_Bind: 
63       Last_IO_Error_Timestamp: 180729 18:00:08
64      Last_SQL_Error_Timestamp: 
65                Master_SSL_Crl: 
66            Master_SSL_Crlpath: 
67            Retrieved_Gtid_Set: 
68             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
69                 Auto_Position: 1
70          Replicate_Rewrite_DB: 
71                  Channel_Name: 
72            Master_TLS_Version: 
73 1 row in set (0.00 sec)
 1 [root@zlm3 04:11:50 /data/mysql/mysql3306/data]
 2 #cd /data/backup/
 3 
 4 [root@zlm3 04:12:17 /data/backup]
 5 #mysqlbinlog --base64-output=decode-rows relay-bin.000043 > 43.log
 6 
 7 [root@zlm3 04:12:47 /data/backup]
 8 #tail -20 43.log
 9 #180731  6:34:54 server id 1013306  end_log_pos 954224     Delete_rows: table id 222 flags: STMT_END_F
10 # at 954224
11 #180731  6:34:54 server id 1013306  end_log_pos 954251     Xid = 58
12 COMMIT/*!*/;
13 # at 954251
14 #180731  6:35:09 server id 1013306  end_log_pos 954312     GTID    last_committed=2    sequence_number=3    rbr_only=no
15 SET @@SESSION.GTID_NEXT= '1b7181ee-6eaf-11e8-998e-080027de0e0e:3730232'/*!*/;
16 # at 954312
17 #180731  6:35:09 server id 1013306  end_log_pos 954436     Query    thread_id=13    exec_time=0    error_code=0
18 use `sysbench`/*!*/;
19 SET TIMESTAMP=1533011709/*!*/;
20 DROP TABLE `sbtest5` /* generated by server */ //Here's the dropping operation.Therefore,the util position we need is "954251" which just below the "COMMIT/*!*/;"
21 /*!*/;
22 # at 954436
23 #180731  6:35:29 server id 1013306  end_log_pos 954479     Rotate to mysql-bin.000044  pos: 4
24 SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
25 DELIMITER ;
26 # End of log file
27 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
28 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

 

    I was stucked here.Slave IO_Thread could not be started with 1236,although I had replaced the server-uuid and set the variable of gtid_purged.Only if the slave get binlog which contains the incremental data and start the slave SQL_Thread.We can get back our dropped table together with the modification on it.

Execute start slave sql_thread util clause.

 

 1 (zlm@192.168.1.102 3306)[(none)]>start slave sql_thread until relay_log_file='relay-bin.000043',relay_log_pos=954251;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 (zlm@192.168.1.102 3306)[(none)]>show slave statusG
 5 *************************** 1. row ***************************
 6                Slave_IO_State: 
 7                   Master_Host: xxx
 8                   Master_User: 
 9                   Master_Port: 3306
10                 Connect_Retry: 60
11               Master_Log_File: 
12           Read_Master_Log_Pos: 4
13                Relay_Log_File: relay-bin.000043
14                 Relay_Log_Pos: 954251
15         Relay_Master_Log_File: 
16              Slave_IO_Running: No
17             Slave_SQL_Running: No
18               Replicate_Do_DB: 
19           Replicate_Ignore_DB: 
20            Replicate_Do_Table: 
21        Replicate_Ignore_Table: 
22       Replicate_Wild_Do_Table: 
23   Replicate_Wild_Ignore_Table: 
24                    Last_Errno: 0
25                    Last_Error: 
26                  Skip_Counter: 0
27           Exec_Master_Log_Pos: 954251
28               Relay_Log_Space: 954838
29               Until_Condition: Relay
30                Until_Log_File: relay-bin.000043
31                 Until_Log_Pos: 954251
32            Master_SSL_Allowed: No
33            Master_SSL_CA_File: 
34            Master_SSL_CA_Path: 
35               Master_SSL_Cert: 
36             Master_SSL_Cipher: 
37                Master_SSL_Key: 
38         Seconds_Behind_Master: NULL
39 Master_SSL_Verify_Server_Cert: No
40                 Last_IO_Errno: 0
41                 Last_IO_Error: 
42                Last_SQL_Errno: 0
43                Last_SQL_Error: 
44   Replicate_Ignore_Server_Ids: 
45              Master_Server_Id: 0
46                   Master_UUID: 
47              Master_Info_File: mysql.slave_master_info
48                     SQL_Delay: 0
49           SQL_Remaining_Delay: NULL
50       Slave_SQL_Running_State: 
51            Master_Retry_Count: 86400
52                   Master_Bind: 
53       Last_IO_Error_Timestamp: 
54      Last_SQL_Error_Timestamp: 
55                Master_SSL_Crl: 
56            Master_SSL_Crlpath: 
57            Retrieved_Gtid_Set: 
58             Executed_Gtid_Set: 1b7181ee-6eaf-11e8-998e-080027de0e0e:1-3730231
59                 Auto_Position: 0
60          Replicate_Rewrite_DB: 
61                  Channel_Name: 
62            Master_TLS_Version: 
63 1 row in set (0.00 sec)
64 
65 (zlm@192.168.1.102 3306)[(none)]>select count(*) from sysbench.sbtest5;
66  ---------- 
67 | count(*) |
68  ---------- 
69 |     5000 |
70  ---------- 
71 1 row in set (0.01 sec)

Supplemented on July 31:

 

 

Summary

    The previous fault of 1236 was due to the wrong configuration of variable "gtid_purged".There were two individual gtids generated by the other masters with different uuid as bellow:

 

 

The simply description of rescuing data with relay log method is shown below:

www.9159.com 1

The precondition is that the master has a up-to-date full backup.(Xtrabackup or mysqldump)

 

1. Copy all the binlog files to another server which has a newly initialized instance.

 

2. Restore the backup on new instance and check data is restored normally.

    Therefore,the configuration command should be executed like this:

3. Execute "reset slave all;" to clear the original replication information(restored from master).**

 

4. Reconfigure those binlog files with "relay-bin.xxxxxx" format together with relay-bin.index file.

1 set @@global.gtid_purged='27af30ca-6800-11e8-ad7e-080027de0e0e:1,
2 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694,
3 e00ef9f5-6c4b-11e8-8389-080027de0e0e:1';

*5. Copy **those relay logs** to the proper datadir and c*hange the ownership and group of them.**

 

6. Execute "change master to ... " with "relay_log_file" and "relay_log_pos".

    not merely set it like this:

7. Execute "change replication filter ... " if you're supposed to merely restore a single table.(optional)

 

8. Execute "start slave sql_thread until ... " to restore data until the position you need.(both relay_log_pos and sql_before_gtids is okay)

1 set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';

 

 

    Eventually,In order to make the gtid more clear,I executed "reset master" on binlog server(zlm3) and then copyed those necessary binlogs which were transfered from original master.Further more,I changed back the uuid of zlm3.Because I found that there's no need to change it at all.

    The new procedure of demonstration was shown below:

 

  1 zlm@192.168.56.101:3306 [(none)]>set @@global.gtid_purged='2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694';
  2 Query OK, 0 rows affected (0.00 sec)
  3 
  4 zlm@192.168.56.101:3306 [(none)]>change master to master_host='192.168.56.102',master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1;
  5 Query OK, 0 rows affected, 2 warnings (0.01 sec)
  6 
  7 zlm@192.168.56.101:3306 [(none)]>start slave io_thread;
  8 Query OK, 0 rows affected (0.00 sec)
  9 
 10 zlm@192.168.56.101:3306 [(none)]>show slave statusG
 11 *************************** 1. row ***************************
 12                Slave_IO_State: Waiting for master to send event
 13                   Master_Host: 192.168.56.102
 14                   Master_User: repl
 15                   Master_Port: 3306
 16                 Connect_Retry: 60
 17               Master_Log_File: mysql-bin.000105
 18           Read_Master_Log_Pos: 194
 19                Relay_Log_File: relay-bin.000001
 20                 Relay_Log_Pos: 4
 21         Relay_Master_Log_File: 
 22              Slave_IO_Running: Yes
 23             Slave_SQL_Running: No
 24               Replicate_Do_DB: 
 25           Replicate_Ignore_DB: 
 26            Replicate_Do_Table: 
 27        Replicate_Ignore_Table: 
 28       Replicate_Wild_Do_Table: 
 29   Replicate_Wild_Ignore_Table: 
 30                    Last_Errno: 0
 31                    Last_Error: 
 32                  Skip_Counter: 0
 33           Exec_Master_Log_Pos: 0
 34               Relay_Log_Space: 3360
 35               Until_Condition: None
 36                Until_Log_File: 
 37                 Until_Log_Pos: 0
 38            Master_SSL_Allowed: No
 39            Master_SSL_CA_File: 
 40            Master_SSL_CA_Path: 
 41               Master_SSL_Cert: 
 42             Master_SSL_Cipher: 
 43                Master_SSL_Key: 
 44         Seconds_Behind_Master: NULL
 45 Master_SSL_Verify_Server_Cert: No
 46                 Last_IO_Errno: 0
 47                 Last_IO_Error: 
 48                Last_SQL_Errno: 0
 49                Last_SQL_Error: 
 50   Replicate_Ignore_Server_Ids: 
 51              Master_Server_Id: 1023306
 52                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e //I changed back the uuid of zlm3 to its previous value.
 53              Master_Info_File: /data/mysql/mysql3306/data/master.info
 54                     SQL_Delay: 0
 55           SQL_Remaining_Delay: NULL
 56       Slave_SQL_Running_State: 
 57            Master_Retry_Count: 86400
 58                   Master_Bind: 
 59       Last_IO_Error_Timestamp: 
 60      Last_SQL_Error_Timestamp: 
 61                Master_SSL_Crl: 
 62            Master_SSL_Crlpath: 
 63            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696 //New gtid was received.
 64             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715694
 65                 Auto_Position: 1
 66          Replicate_Rewrite_DB: 
 67                  Channel_Name: 
 68            Master_TLS_Version: 
 69 1 row in set (0.00 sec)
 70 
 71 zlm@192.168.56.101:3306 [sysbench]>start slave sql_thread until sql_before_gtids='2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715696';
 72 Query OK, 0 rows affected (0.00 sec)
 73 
 74 zlm@192.168.56.101:3306 [sysbench]>show slave statusG
 75 *************************** 1. row ***************************
 76                Slave_IO_State: Waiting for master to send event
 77                   Master_Host: 192.168.56.102
 78                   Master_User: repl
 79                   Master_Port: 3306
 80                 Connect_Retry: 60
 81               Master_Log_File: mysql-bin.000105
 82           Read_Master_Log_Pos: 194
 83                Relay_Log_File: relay-bin.000002
 84                 Relay_Log_Pos: 696
 85         Relay_Master_Log_File: mysql-bin.000100
 86              Slave_IO_Running: Yes
 87             Slave_SQL_Running: No
 88               Replicate_Do_DB: 
 89           Replicate_Ignore_DB: 
 90            Replicate_Do_Table: 
 91        Replicate_Ignore_Table: 
 92       Replicate_Wild_Do_Table: 
 93   Replicate_Wild_Ignore_Table: 
 94                    Last_Errno: 0
 95                    Last_Error: 
 96                  Skip_Counter: 0
 97           Exec_Master_Log_Pos: 758
 98               Relay_Log_Space: 3360
 99               Until_Condition: SQL_BEFORE_GTIDS
100                Until_Log_File: 
101                 Until_Log_Pos: 0
102            Master_SSL_Allowed: No
103            Master_SSL_CA_File: 
104            Master_SSL_CA_Path: 
105               Master_SSL_Cert: 
106             Master_SSL_Cipher: 
107                Master_SSL_Key: 
108         Seconds_Behind_Master: NULL
109 Master_SSL_Verify_Server_Cert: No
110                 Last_IO_Errno: 0
111                 Last_IO_Error: 
112                Last_SQL_Errno: 0
113                Last_SQL_Error: 
114   Replicate_Ignore_Server_Ids: 
115              Master_Server_Id: 1023306
116                   Master_UUID: e00ef9f5-6c4b-11e8-8389-080027de0e0e
117              Master_Info_File: /data/mysql/mysql3306/data/master.info
118                     SQL_Delay: 0
119           SQL_Remaining_Delay: NULL
120       Slave_SQL_Running_State: 
121            Master_Retry_Count: 86400
122                   Master_Bind: 
123       Last_IO_Error_Timestamp: 
124      Last_SQL_Error_Timestamp: 
125                Master_SSL_Crl: 
126            Master_SSL_Crlpath: 
127            Retrieved_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:12715695-12715696
128             Executed_Gtid_Set: 2a4b3562-2ab6-11e8-be7a-080027de0e0e:1-12715695
129                 Auto_Position: 1
130          Replicate_Rewrite_DB: 
131                  Channel_Name: 
132            Master_TLS_Version: 
133 1 row in set (0.00 sec)
134 
135 zlm@192.168.56.101:3306 [sysbench]>select * from sbtest6;
136  ---- --- --- ----- 
137 | id | k | c | pad |
138  ---- --- --- ----- 
139 |  1 | 1 | a | b   |
140 |  2 | 2 | c | d   |
141  ---- --- --- ----- 
142 2 rows in set (0.00 sec)
143 
144 //The incremental data in "sbtest6" had been come again.It was okay this time.

  

Summary

  • Binlog server act as a master in this experiment.It can prevent data loss such as miss opeartion of dropping tables whenever the master crashes.
  • We can implement as many binlog servers as possible to continuously acquire the binlogs on different masters.
  • If the only purpose is to get back the dropping data,replication filter of "replicate_do_table" is not necessary.
  • The modification of "sever-uuid" is also neglectable operation.Each gtid contains a differten uuid is completely acceptable.It won't influence our replication except for a little bit complex in output.

 

本文由9159.com发布于www.9159.com,转载请注明出处:在PXC中重新添加掉线节点

关键词: www.9159.com 9159.com