MySQL 利用停机冷备异地恢复并搭建主从复制

怼烎@ 2022-02-04 11:39 307阅读 0赞

本文将从主库master停机冷备，然后在从库slave恢复并搭建主从复制。  
1.主库停机冷备，数据目录为/mysqlData/data,日志目录为/mysqlLog/logs，参数文件为/etc/my.cnf

[root@master ~]# service mysql stop
    Shutting down MySQL..                                      [  OK  ]
    [root@master ~]# ps -ef|grep mysql
    root      4820  4761  0 16:29 pts/1    00:00:00 grep mysql
    
    --打包并传输，需要备份的文件为数据目录，redo log,undo log，ibdata，my.cnf
    [root@master logs]# scp /tmp/data.tar.gz root@192.168.204.129:/tmp
    root@192.168.204.129's password: 
    data.tar.gz                                                                                                                         100%   51MB  50.9MB/s   00:01    
    [root@master logs]# scp /etc/my.cnf root@192.168.204.129:/tmp
    root@192.168.204.129's password: 
    my.cnf 
    
    --开启主库实例

2.从库利用冷备恢复

--将my.cnf放到/etc目录下，并修改server_id，避免与主库冲突
    [root@slave mysqlData]# cp /tmp/my.cnf /etc/my.cnf
    [root@slave mysqlData]# vi /etc/my.cnf
    
    --解压冷备
    [root@slave tmp]# tar -zxvf data.tar.gz -C /
    mysqlData/data/
    mysqlData/data/performance_schema/
    ...
    
    --根据my.cnf创建相关目录
    [root@slave data]# cat /etc/my.cnf
    log-error=/mysqlLog/logs/error.log
    datadir=/mysqlData/data
    tmpdir=/mysqlData/tmp
    
    [root@slave data]# mkdir -p /mysqlLog/logs
    [root@slave data]# mkdir -p /mysqlData/tmp
    [root@slave data]# chown mysql:mysql -R /etc/my.cnf
    [root@slave data]# chown mysql:mysql -R /mysqlData/
    [root@slave data]# chown mysql:mysql -R /mysqlLog/
    
    --启动数据实例
    [root@slave data]# service mysql start
    Starting MySQL.2019-05-07T08:59:25.993526Z mysqld_safe error: log-error set to '/mysqlLog/logs/error.log', however file don't exists. Create writable for user 'mysql'.
    The server quit without updating PID file (/usr/local/mysql[FAILED]id).
    [root@slave data]# cd /mysqlLog/logs/
    [root@slave logs]# ls
    [root@slave logs]# touch error.log
    [root@slave logs]# chown mysql:mysql -R error.log 
    [root@slave logs]# service mysql start
    Starting MySQL..                                           [  OK  ]
    [root@slave logs]# ls
    error.log  mysql-bin.000001  mysql-bin.index

3.配置主从复制

[root@master logs]# 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 master status\G
    *************************** 1. row ***************************
                 File: mysql-bin.017726
             Position: 194
         Binlog_Do_DB: 
     Binlog_Ignore_DB: mysql,information_schema,performance_schema,sys
    Executed_Gtid_Set: f4859d1f-f82f-11e8-afbe-000c2999b048:1-382773
    1 row in set (0.00 sec)
    --因为当前实例完全是从主库拷贝过来的，所以GTID相关信息都是准确的，无需再做设置，那么直接搭建主从关系试试
    [root@slave data]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3
    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> 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.02 sec)
    
    mysql> show warnings;
    +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                                                                                                                                                                                                              |
    +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
    | Note  | 1760 | 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. |
    +-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: 
                      Master_Host: 192.168.204.128
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: 
              Read_Master_Log_Pos: 4
                   Relay_Log_File: slave-relay-bin.000001
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: 
                 Slave_IO_Running: No
                Slave_SQL_Running: No
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 0
                  Relay_Log_Space: 154
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 0
                      Master_UUID: 
                 Master_Info_File: /mysqlData/data/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: 
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                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> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: 
                      Master_Host: 192.168.204.128
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: 
              Read_Master_Log_Pos: 4
                   Relay_Log_File: slave-relay-bin.000001
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: 
                 Slave_IO_Running: No
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 0
                  Relay_Log_Space: 154
                  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: 1593
                    Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 128
                      Master_UUID: 
                 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: 190507 17:05:28
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                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)
    
    --可以看到出现错误these UUIDs must be different for replication to work，也就是当前实例与主库实例的uuid是一样的，所以主从复制异常了。
    --解决这个问题是因为冷备传输过来时，数据目录里有一个auto.cnf文件，里面记录着当前实例的uuid，如果这个文件一直存在，那么当前实例的uuid就会一直使用里面的值
    [root@slave data]# ls
    auto.cnf    client-cert.pem  ibdata1      ibtmp1       master-slow.log     private_key.pem  sam              slave-relay-bin.000001
    ca-key.pem  client-key.pem   ib_logfile0  master.info  mysql               public_key.pem   server-cert.pem  slave-relay-bin.index
    ca.pem      ib_buffer_pool   ib_logfile1  master.log   performance_schema  relay-log.info   server-key.pem   sys
    [root@slave data]# cat auto.cnf 
    [auto]
    server-uuid=f4859d1f-f82f-11e8-afbe-000c2999b048
    
    --解决方法是把auto.cnf文件删除，重启实例即可，重启以后会重新生成该文件
    [root@slave data]# service mysql stop
    Shutting down MySQL..                                      [  OK  ]
    [root@slave data]# ls
    auto.cnf    client-cert.pem  ibdata1      master.info      mysql               public_key.pem  server-cert.pem         slave-relay-bin.index
    ca-key.pem  client-key.pem   ib_logfile0  master.log       performance_schema  relay-log.info  server-key.pem          sys
    ca.pem      ib_buffer_pool   ib_logfile1  master-slow.log  private_key.pem     sam             slave-relay-bin.000001
    [root@slave data]# rm -f auto.cnf 
    [root@slave data]# service mysql start
    Starting MySQL..                                           [  OK  ]
    [root@slave data]# ls
    auto.cnf    client-cert.pem  ibdata1      ibtmp1       master-slow.log     private_key.pem  sam              slave-relay-bin.000002  sys
    ca-key.pem  client-key.pem   ib_logfile0  master.info  mysql               public_key.pem   server-cert.pem  slave-relay-bin.000003
    ca.pem      ib_buffer_pool   ib_logfile1  master.log   performance_schema  relay-log.info   server-key.pem   slave-relay-bin.index
    [root@slave data]# cat auto.cnf 
    [auto]
    server-uuid=008d32b9-70a8-11e9-b377-000c29d2f92c
    
    --开启复制，一切正常
    mysql> start slave;
    Query OK, 0 rows affected, 1 warning (0.00 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.017726
              Read_Master_Log_Pos: 194
                   Relay_Log_File: slave-relay-bin.000003
                    Relay_Log_Pos: 367
            Relay_Master_Log_File: mysql-bin.017726
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 194
                  Relay_Log_Space: 574
                  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: 
                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)