使用gtid部署主从复制(master_auto_position)

环境:
OS:Centos7
db:5.7

 

1. 从库安装mysql
在从库上部署mysql，配置参数跟主库保持一致

2 安装xtrabackup
可以到官网下载安装介质
percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
主从库上都需要进行安装
解压安装
[root@localhost soft]# tar -xvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
[root@localhost soft]# mv percona-xtrabackup-2.4.7-Linux-x86_64 /opt/xtrabackup247

3. 主库创建同步账号
grant replication slave, replication client on *.* to 'ureplsync'@'%' identified by '密码';

4. 备份主库
[root@host01 bin]#mkdir -p /tmp/xtrabackup_file
[root@host01 bin]#/opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/home/middle/mysql57/conf/my.cnf --host=localhost --user=root --password=mysql -P13306 -S /tmp/mysql.sock /tmp/xtrabackup_file

我们将该目录打包,传送到目标的机器上
[root@localhost xtrabackup_file]#cd /tmp/xtrabackup_file
[root@localhost xtrabackup_file]#tar -cvf 2022-03-02_20-24-44.tar ./2022-03-02_20-24-44
[root@localhost xtrabackup_file]#scp 2022-03-02_20-24-44.tar root@192.168.56.192:/tmp/xtrabackup_file/

 

5. 备库恢复
从库tar文件解压缩
[root@localhost xtrabackup_file]# cd /tmp/xtrabackup_file
[root@localhost tmp]# tar -xvf 2022-03-02_20-24-44.tar

6.停掉备库(之前有主备环境的情况下)
[root@localhost mysql5733_slave]# /home/middle/mysql57/bin/mysqladmin -h localhost -uroot -P13306 -pmysql -S /tmp/mysql.sock shutdown

7.备份data目录并创建新的data目录
[root@localhost mysql5733_slave]#cd /home/middle/mysql57
[root@localhost mysql5733_slave]# mv data bakdata
同时创建新的目录
[root@localhost mysql5733_slave]# mkdir data

8.恢复(备库也事先安装好xtrabackup软件)
[root@localhost ]# /opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/home/middle/mysql57/conf/my.cnf --user=root --apply-log /tmp/xtrabackup_file/2022-03-02_20-24-44
完成后会有ok提示
[root@localhost]# /opt/xtrabackup-2.4.7/bin/innobackupex --defaults-file=/home/middle/mysql57/conf/my.cnf --user=root --copy-back --rsync /tmp/xtrabackup_file/2022-03-02_20-24-44
完成后会有ok提示

 

9.修改权限
[root@localhost /]# cd /home/middle
[root@localhost opt]# chown -R mysql:mysql ./mysql57

10.启动从库
[root@localhost opt]# su - mysql
[mysql@localhost ~]$ /home/middle/mysql57/bin/mysqld_safe --defaults-file=/home/middle/mysql57/conf/my.cnf --user=mysql &

 

11.从库设置gtid
mysql> reset slave;
mysql> reset master;
上面步骤目的是去掉从库备份过来已有的gtid值,下面重新设置gtid的值

mysql> set global gtid_purged='2839d3fc-9960-11ec-bd39-080027e0fbb1:1-16';
上面的值可以从xtrabackup_info文件中获取:GTID of the last change '2839d3fc-9960-11ec-bd39-080027e0fbb1:1-16'

mysql> select * from mysql.gtid_executed;
查询是否与xtrabackup_info记录的一致

查询gtid情况
show global variables like '%gtid%';

 

12.从库部署同步

change master to master_host='192.168.56.191',
       master_user='ureplsync',
       master_password='mysql',
       master_port=13306,
       master_auto_position=1;

 

13. 启动从库
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

 

14.查看同步情况

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.191
                  Master_User: ureplsync
                  Master_Port: 13306
                Connect_Retry: 60
              Master_Log_File: binlog.000007
          Read_Master_Log_Pos: 683
               Relay_Log_File: relaylog-binlog.000002
                Relay_Log_Pos: 850
        Relay_Master_Log_File: binlog.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 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: 683
              Relay_Log_Space: 1057
              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: 1
                  Master_UUID: 2839d3fc-9960-11ec-bd39-080027e0fbb1
             Master_Info_File: /home/middle/mysql57/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: 2839d3fc-9960-11ec-bd39-080027e0fbb1:17-18
            Executed_Gtid_Set: 2839d3fc-9960-11ec-bd39-080027e0fbb1:1-18
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

 

说明:

主库修改了ip后,从库变更,这种方式比pos好处就是,不需要指定开始复制的binlog和pos位置
mysql> stop slave;
mysql> change master to master_host='192.168.56.191';
mysql> start slave;