您的位置:金沙游乐场85155 > 大数据库 > 利用binlog server及Xtrabackup备份集来恢复误删表(

利用binlog server及Xtrabackup备份集来恢复误删表(

发布时间:2019-10-02 09:54编辑:大数据库浏览(155)

     

     

    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.

     

     

    Framework

    Framework

     

     

    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

     

     

    Procedure

    Precedure

     

     

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

    Step 1: Create binlog server.

     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 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 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.**

    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@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.**

    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 [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)
    

     

     

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

    **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@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)
    

     

     

    Step 2: Destroy the table.

    **Check the table on node zlm2.**

     

     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 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)
    

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

     

     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@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!
    

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

     

    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)
    

    **Restore backup on node zlm4.**

     

     

    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
    

     

     

    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.
    

     

     

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

    *Step 3: Rescue data.**

      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)
    

     

     

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

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

     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!
    

     

     

    本文由金沙游乐场85155发布于大数据库,转载请注明出处:利用binlog server及Xtrabackup备份集来恢复误删表(

    关键词:

上一篇:SqlServer递归查询金沙85155登入:

下一篇:没有了