MySQL利用xtrabackup搭建主从复制

我不是女神ヾ 2022-02-04 09:57 210阅读 0赞

利用xtrabackup备份主库，并在异机恢复，并配置主从复制。  
1.主库备份

[root@master mysqlData]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 22
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sam                |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    备份前主库的sam.test表有如下数据：
    mysql> select * from sam.test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    |  2 | tom  |
    |  3 | ken  |
    +----+------+
    3 rows in set (0.00 sec)
    
    --全库备份
    [root@master mysqlData]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=sam123 /tmp --stream=tar|gzip - > /tmp/full.tar.gz
    190507 14:23:07 innobackupex: Starting the backup operation
    
    IMPORTANT: Please check that the backup run completes successfully.
               At the end of a successful backup run innobackupex
               prints "completed OK!".
    
    190507 14:23:07  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/usr/local/mysql/mysql.sock' as 'root'  (using password: YES).
    190507 14:23:07  version_check Connected to MySQL server
    190507 14:23:07  version_check Executing a version check against the server...
    190507 14:23:07  version_check Done.
    190507 14:23:07 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /usr/local/mysql/mysql.sock
    Using server version 5.7.23-log
    innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /mysqlData/data
    xtrabackup: open files limit requested 0, set to 1024
    xtrabackup: using the following InnoDB configuration:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = ./
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    InnoDB: Number of pools: 1
    190507 14:23:07 >> log scanned up to (3923803021)
    ...
    190507 14:23:17 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
    xtrabackup: The latest check point (for incremental): '3923803012'
    xtrabackup: Stopping log copying thread.
    .190507 14:23:17 >> log scanned up to (3923803021)
    
    190507 14:23:17 Executing UNLOCK TABLES
    190507 14:23:17 All tables unlocked
    190507 14:23:17 [00] Streaming ib_buffer_pool to <STDOUT>
    190507 14:23:17 [00]        ...done
    190507 14:23:17 Backup created in directory '/tmp'
    MySQL binlog position: filename 'mysql-bin.017725', position '2063', GTID of the last change 'f4859d1f-f82f-11e8-afbe-000c2999b048:1-382772'
    190507 14:23:17 [00] Streaming backup-my.cnf
    190507 14:23:17 [00]        ...done
    190507 14:23:17 [00] Streaming xtrabackup_info
    190507 14:23:17 [00]        ...done
    xtrabackup: Transaction log of lsn (3923803012) to (3923803021) was copied.
    190507 14:23:18 completed OK!
    
    --备份完成后，向sam.test插入数据，用于校验主从复制效果
    [root@master mysqlData]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 27
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> insert into sam.test values (4,'jay');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from sam.test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    |  2 | tom  |
    |  3 | ken  |
    |  4 | jay  |
    +----+------+
    4 rows in set (0.00 sec)

2.异机恢复

[root@slave tmp]# tar -izxvf full.tar.gz -C /tmp/tarout/
    ibdata1
    sys/sys_config.ibd
    ...
    ib_buffer_pool
    backup-my.cnf
    xtrabackup_info
    xtrabackup_logfile
    xtrabackup_checkpoints
    [root@slave tmp]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=sam123 --apply-log /tmp/tarout/
    190507 14:34:15 innobackupex: Starting the apply-log operation
    
    IMPORTANT: Please check that the apply-log run completes successfully.
               At the end of a successful apply-log run innobackupex
               prints "completed OK!".
    
    innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
    xtrabackup: cd to /tmp/tarout/
    xtrabackup: This target seems to be not prepared yet.
    InnoDB: Number of pools: 1
    xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(3923803012)
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = .
    xtrabackup:   innodb_log_files_in_group = 1
    xtrabackup:   innodb_log_file_size = 8388608
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = .
    xtrabackup:   innodb_log_files_in_group = 1
    xtrabackup:   innodb_log_file_size = 8388608
    xtrabackup: Starting InnoDB instance for recovery.
    xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
    InnoDB: PUNCH HOLE support available
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Uses event mutexes
    InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Number of pools: 1
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    InnoDB: Completed initialization of buffer pool
    InnoDB: page_cleaner coordinator priority: -20
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 3923803012
    InnoDB: Doing recovery: scanned up to log sequence number 3923803021 (0%)
    InnoDB: Doing recovery: scanned up to log sequence number 3923803021 (0%)
    InnoDB: Database was not shutdown normally!
    InnoDB: Starting crash recovery.
    InnoDB: xtrabackup: Last MySQL binlog file position 2063, file name mysql-bin.017725
    InnoDB: Creating shared tablespace for temporary tables
    InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    InnoDB: File './ibtmp1' size is now 12 MB.
    InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
    InnoDB: 32 non-redo rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: 5.7.13 started; log sequence number 3923803021
    InnoDB: xtrabackup: Last MySQL binlog file position 2063, file name mysql-bin.017725
    
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 3923803395
    InnoDB: Number of pools: 1
    xtrabackup: using the following InnoDB configuration for recovery:
    xtrabackup:   innodb_data_home_dir = .
    xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
    xtrabackup:   innodb_log_group_home_dir = .
    xtrabackup:   innodb_log_files_in_group = 2
    xtrabackup:   innodb_log_file_size = 50331648
    InnoDB: PUNCH HOLE support available
    InnoDB: Mutexes and rw_locks use GCC atomic builtins
    InnoDB: Uses event mutexes
    InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
    InnoDB: Compressed tables use zlib 1.2.3
    InnoDB: Number of pools: 1
    InnoDB: Using CPU crc32 instructions
    InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
    InnoDB: Completed initialization of buffer pool
    InnoDB: page_cleaner coordinator priority: -20
    InnoDB: Setting log file ./ib_logfile101 size to 48 MB
    InnoDB: Setting log file ./ib_logfile1 size to 48 MB
    InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
    InnoDB: New log files created, LSN=3923803395
    InnoDB: Highest supported file format is Barracuda.
    InnoDB: Log scan progressed past the checkpoint lsn 3923803660
    InnoDB: Doing recovery: scanned up to log sequence number 3923803669 (0%)
    InnoDB: Doing recovery: scanned up to log sequence number 3923803669 (0%)
    InnoDB: Database was not shutdown normally!
    InnoDB: Starting crash recovery.
    InnoDB: xtrabackup: Last MySQL binlog file position 2063, file name mysql-bin.017725
    InnoDB: Removed temporary tablespace data file: "ibtmp1"
    InnoDB: Creating shared tablespace for temporary tables
    InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    InnoDB: File './ibtmp1' size is now 12 MB.
    InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
    InnoDB: 32 non-redo rollback segment(s) are active.
    InnoDB: Waiting for purge to start
    InnoDB: 5.7.13 started; log sequence number 3923803669
    xtrabackup: starting shutdown with innodb_fast_shutdown = 1
    InnoDB: FTS optimize thread exiting.
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed; log sequence number 3923803688
    190507 14:34:20 completed OK!
    [root@slave tmp]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=sam123 --copy-back /tmp/tarout/
    190507 14:34:53 innobackupex: Starting the copy-back operation
    
    IMPORTANT: Please check that the copy-back run completes successfully.
               At the end of a successful copy-back run innobackupex
               prints "completed OK!".
    
    innobackupex version 2.4.5 based on MySQL server 5.7.13 Linux (x86_64) (revision id: e41c0be)
    190507 14:34:53 [01] Copying ib_logfile0 to /mysqlData/data/ib_logfile0
    190507 14:34:54 [01]        ...done
    ...
    190507 14:34:58 [01]        ...done
    190507 14:34:58 [01] Copying ./ibtmp1 to /mysqlData/data/ibtmp1
    190507 14:34:58 [01]        ...done
    190507 14:34:58 completed OK!
    [root@slave tmp]# 
    [root@slave tmp]# 
    [root@slave tmp]# chown mysql:mysql -R /mysqlData/
    [root@slave tmp]# chown mysql:mysql -R /mysqlLog/
    [root@slave mysqlData]# 2019-05-07T06:38:16.766756Z mysqld_safe error: log-error set to '/mysqlLog/logs/error.log', however file don't exists. Create writable for user 'mysql'.
    
    [root@slave mysqlData]# touch /mysqlLog/logs/error.log
    [root@slave mysqlData]# chown mysql:mysql -R /mysqlLog/logs/error.log
    [root@slave mysqlData]# service mysql start
    Starting MySQL                                             [  OK  ]
    
    --检验恢复的数据是否正确
    [root@slave data]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.23-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | sam                |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> select * from sam.test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    |  2 | tom  |
    |  3 | ken  |
    +----+------+
    3 rows in set (0.01 sec)

3.配置从库

--主库创建复制用户
    grant replication slave to repl@'%' identified by 'sam123';
    
    --从库进入数据目录，查看xtrabackup_info文件，注意信息```binlog_pos = filename 'mysql-bin.017725', position '2063', GTID of the last change 'f4859d1f-f82f-11e8-afbe-000c2999b048:1-382772'```
    [root@slave mysqlData]# cd data/
    [root@slave data]# ls
    auto.cnf  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  ibtmp1  mysql  performance_schema  sam  sys  xtrabackup_binlog_pos_innodb  xtrabackup_info
    [root@slave data]# cat xtrabackup_info 
    uuid = 9a5d7e5d-7090-11e9-85d7-000c2999b048
    name = 
    tool_name = innobackupex
    tool_command = --defaults-file=/etc/my.cnf --user=root --password=... /tmp --stream=tar
    tool_version = 2.4.5
    ibbackup_version = 2.4.5
    server_version = 5.7.23-log
    start_time = 2019-05-07 14:23:07
    end_time = 2019-05-07 14:23:17
    lock_time = 0
    binlog_pos = filename 'mysql-bin.017725', position '2063', GTID of the last change 'f4859d1f-f82f-11e8-afbe-000c2999b048:1-382772'
    innodb_from_lsn = 0
    innodb_to_lsn = 3923803012
    partial = N
    incremental = N
    format = tar
    compact = N
    compressed = N
    encrypted = N
    
    --可以根据相应的信息通过传统指定binlog和pos的方式，或者GTID的方式来配置主从复制，这里采用GTID的方式
    mysql> show master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.000001
             Position: 154
         Binlog_Do_DB: 
     Binlog_Ignore_DB: 
    Executed_Gtid_Set: f4859d1f-f82f-11e8-afbe-000c2999b048:1-382765
    1 row in set (0.00 sec)
    
    mysql> reset master;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> set @@global.gtid_purged='f4859d1f-f82f-11e8-afbe-000c2999b048:1-382772';
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> change master to 
        -> master_host='192.168.204.128',
        -> master_port=3306,
        -> master_user='repl',
        -> master_password='sam123',
        -> master_auto_position=1;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.204.128
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.017725
              Read_Master_Log_Pos: 2320
                   Relay_Log_File: slave-relay-bin.000002
                    Relay_Log_Pos: 671
            Relay_Master_Log_File: mysql-bin.017725
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 2320
                  Relay_Log_Space: 878
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 128
                      Master_UUID: f4859d1f-f82f-11e8-afbe-000c2999b048
                 Master_Info_File: /mysqlData/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: f4859d1f-f82f-11e8-afbe-000c2999b048:382773
                Executed_Gtid_Set: f4859d1f-f82f-11e8-afbe-000c2999b048:1-382773
                    Auto_Position: 1
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    --检查备份后的数据是否同步过来了
    mysql> select * from sam.test;
    +----+------+
    | id | name |
    +----+------+
    |  1 | sam  |
    |  2 | tom  |
    |  3 | ken  |
    |  4 | jay  |
    +----+------+
    4 rows in set (0.00 sec)