利用当天的xtrabackup全量备份文件搭建slave库

环境:

单台机器开启2个mysql实例 3306和3307,
3306 和3307实例都开启了Gtid,并且要保证server-id不同

全量备份命令:

time innobackupex --defaults-file=/data/mysql5.7/my3306.cnf  -ubackupuser -p123456ccs  --host=127.0.0.1  -S /tmp/3306.sock /data/backup/
  • 1.

当天的xtrabackup全量备份文件记录的binlog的位置点:

[root@e ~]# cat /data/backup/2019-08-16_17-59-10/xtrabackup_binlog_info 
mysql-bin.000009	117268	efdcb9c7-bf3d-11e9-b0bf-90b11c20454d:1-876
  • 1.
  • 2.

恢复备份到新的实例mysql3307的上:

innobackupex  --apply-log /data/backup/2019-08-16_17-59-10/
innobackupex --defaults-file=/data/mysql5.7/my3307.cnf --copy-back /data/backup/2019-08-16_17-59-10/
  • 1.
  • 2.

授权新实例data目录mysql的权限:

chown -R mysql.mysql /data/mysql5.7/3307/data 
  • 1.

提示:/data/mysql5.7/my3307.cnf 配置文件要开启gtid,并且要注释掉定时器:#event_scheduler = ON,默认定时器是关闭的

启动mysql 3307 实例:

/usr/local/mysql5.7/bin/mysqld --defaults-file=/data/mysql5.7/my3307.cnf &

mysql> show variables like 'event_scheduler'
    -> ;
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)


[root@e data]# mysql -uroot -p'lnmp.org#25199' -S /tmp/3307.sock -e "select @@port"
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|   3307 |
+--------+
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.

在3306库上创建复制账户:

grant replication slave *.* to 'rep'@'192.168.0.%' identified by '654321testrep';flush privileges;
  • 1.

** 清除3307实例的Gtid信息:**

mysql> reset master;

当天的xtrabackup全量备份文件记录的binlog的位置点以及事物gtid执行结束的位置:

[root@e ~]# cat /data/backup/2019-08-16_17-59-10/xtrabackup_binlog_info 
mysql-bin.000009	117268	efdcb9c7-bf3d-11e9-b0bf-90b11c20454d:1-876
  
  • 1.
  • 2.
  • 3.

3307 slave实例上要让gtid号从efdcb9c7-bf3d-11e9-b0bf-90b11c20454d:1-876 以后开始记录:

  mysql> set global gtid_purged='efdcb9c7-bf3d-11e9-b0bf-90b11c20454d:1-876';
  mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: efdcb9c7-bf3d-11e9-b0bf-90b11c20454d:1-876
1 row in set (0.00 sec)
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.

3307 slave实例上执行:

  CHANGE MASTER TO
  MASTER_HOST='192.168.0.1',
  MASTER_USER='rep',
  MASTER_PASSWORD='654321testrep',
  MASTER_PORT=3306,
  MASTER_AUTO_POSITION = 1;
  
start slave;show slave status\G
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.

slave库上查看:

[root@e data]# mysql -uroot -p'lnmp.org#25199' -S /tmp/3307.sock -e "show slave status\G"|egrep "Slave_IO|Slave_SQL"
Warning: Using a password on the command line interface can be insecure.
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more update
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.

到此处主从复制搭建完成

posted @ 2019-08-20 17:42  勤奋的蓝猫  阅读(3)  评论(0编辑  收藏  举报  来源