MySQL主从复制-利用冷备份搭建从库

1、启动从库，目录文件配置路径：

basedir=/u01/mysql/base
datadir=/u01/mysql/data
tmpdir=/u01/mysql/tmp

2、将冷备份data目录复制到新实例下的，datadir

$ ll
total 20
drwxr-xr-x.   9 mysql mysql   169 Oct 10 23:28 base
drwxrwxr-x. 397 mysql mysql 16384 Oct 11 09:32 data
drwxrwxr-x.   2 mysql mysql    81 Sep 23 17:29 init.d
lrwxrwxrwx.   1 mysql mysql    15 Sep 23 22:07 logs 
drwxrwxr-x.   2 mysql mysql    98 Oct 10 23:33 run
drwxrwxr-x.   2 mysql mysql    59 Oct 11 09:31 tmp

3、修改auto.cnf

$ mv auto.cnf　　auto.cnf.bk

4、启动数据库，并配置主从关系。

mysql> reset master;
mysql> set global gtid_purged = '6780264b-a74d-11ec-97c2-b8cef6724950:1-97347636';

mysql> CHANGE MASTER TO
> MASTER_HOST='10.172.160.1',
> MASTER_USER='repl',
> MASTER_PASSWORD='repl',
> MASTER_PORT=3306,
> MASTER_AUTO_POSITION=1;

启动从库sql_thread,io_thread

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.172.160.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

**gtid_purged 值的确定

1、根据冷备份的binlog确定事务

$ mysqlbinlog -vv master-bin.023136 |grep GTID_NEXT

SET @@SESSION.GTID_NEXT= '6780264b-a74d-11ec-97c2-b8cef6724950:97347636'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

2、查询主库binlog记录的事务

mysql> show master status;
+-------------------+-----------+--------------+------------------+--------------------------------------------------+
| File              | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                |
+-------------------+-----------+--------------+------------------+--------------------------------------------------+
| master-bin.025046 | 329296789 |              |                  | 6780264b-a74d-11ec-97c2-b8cef6724950:1-101655711 |
+-------------------+-----------+--------------+------------------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

3、确定从库gtid_purged 为：

6780264b-a74d-11ec-97c2-b8cef6724950:1-97347636

若未确定正确的gtid_purged ，启动slave的报错信息如下：

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'