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

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

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

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

?

1 2 3 4 5 6 7 8

$ 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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记录的事务

?

1 2 3 4 5 6 7 8 9

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 为：

?

1	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.'