MySQL复制概述(二)

前言

在上一文章中，我们了解了mysql5.5x系列的mysql复制，这篇文章我们主要介绍mysql5.7版本下的GTID主从复制

什么是GTID
GTID = source_id:transaction_id
开启GTID的必备条件
gtid_mod=ON
enforce-gtid-consistency
log_bin=ON(可选)
log-slave-updates=ON(可选)

a)标准安装步骤

• 同步所有servers

  mysql> set @@global.read_only = ON;
  注： 执行前slave务必执行完所以更新
  

• 关闭所有servers

  shell> mysqladmin -uusername -p shutdown
  

• 重启服务，并开启GTID

  shell> mysqld --gtid-mode=ON --log-bin --enforce-gtid-consistency &
  

• 执行同步命令

  mysql> change master to
      master_host = host,
      master_port = port,
      master_user = user,
      master_password = password,
      master_auto_position = 1;
  mysql> start slave;
  

• 取消master的只读模式

  mysql> set @@global.read_only = OFF;
  

b)从备份中恢复

• 备份

  mysqldump --all-databases --single-transaction --trigger --routines --user=root -p >backup.sql
  # grep PURGED backup.sql
  SET @@GLOBAL.GTID_PURGED='9a511b7b-7059-11e2-9a24-08002762b8af:1-13';
  

• 在slave上reset master ,导入备份
  注：如果是逻辑导入，请设置sql_log_bin=off

  mysql> reset master;
  mysql> show global variables like 'gtid_executed';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | gtid_executed |       |
  +---------------+-------+
  mysql> show global variables like 'gtid_purged';
  +---------------+-------+
  | Variable_name | Value |
  +---------------+-------+
  | gtid_purged   |       |
  +---------------+-------+
  mysql> source backup.sql
  
  mysql> show global variables like 'gtid_executed';
  +---------------+-------------------------------------------+
  | Variable_name | Value                                     |
  +---------------+-------------------------------------------+
  | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
  +---------------+-------------------------------------------+
  mysql> show global variables like 'gtid_purged';
  +---------------+-------------------------------------------+
  | Variable_name | Value                                     |
  +---------------+-------------------------------------------+
  | gtid_purged   | 9a511b7b-7059-11e2-9a24-08002762b8af:1-13 |
  +---------------+-------------------------------------------+
  

• 执行同步命令

  mysql> change master to
      master_host = host,
      master_port = port,
      master_user = user,
      master_password = password,
      master_auto_position = 1;
  mysql> start slave;
  

• 主从复制常见错误
  Slave_IO_Running: No
  Slave_SQL_Running: Yes
  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.’

  在master上查看GTID_EXECUTED

  mysql> show global variables like 'GTID_EXECUTED';
  +---------------+-------------------------------------------+
  | Variable_name | Value                                     |
  +---------------+-------------------------------------------+
  | gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-14 |
  +---------------+-------------------------------------------+
  

  解决：

  mysql>reset master;
  mysql>show global variables like 'GTID_EXECUTED';
      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | gtid_executed |       |
      +---------------+-------+
  mysql> set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:1-14";
  mysql> start slave io_thread;
  mysql> show slave status\G;
  

• 工具
  Percona XtraBackup

---

您的鼓励是我写作最大的动力

俗话说，投资效率是最好的投资。 如果您感觉我的文章质量不错，读后收获很大，预计能为您提高 10% 的工作效率，不妨小额捐助我一下，让我有动力继续写出更多好文章。