Living Slowly - 热爱技术，热爱生活 - 知识归纳

Living out of the way you want.

0%

MySQL 主从之 Xtrabackup 利器

> > 阅读次数： 字数： 4.8k 时长 ≈ 4 分钟

使用 Xtrabackup 搭建 MySQL 主从

MySQl 主从基本知识

Replication 原理

MySQL 复制过程是 slave 主动向 master 拉取，而不是 master 推送，所以理想情况下做搭建主从时不需要 master 做出任何改变甚至停止服务， slave 失败也不影响主库。

复制类型

  • 基于语句的复制
    STATEMENT，在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句，有可能会由于 SQL 执行上下文环境不同而是数据不一致， 例如调用 NOW() 函数。MySQL 在 5.7.7 以前默认采用基于语句的复制，在 5.7.7 及以后版本默认改用 row-based。
  • 基于行的复制
    ROW，把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从 MySQL 5.0 开始支持，能够严格保证数据完全一致，但此时用 MySQLbinlog 去分 析日志就没啥意义。因为任何一条 update 语句，都会把涉及到的行数据全部污染，所以 binlog 文件会比较大。
  • 混合类型的复制
    MIXED，默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

复制方式

  • 异步复制
    通常都是异步，即主库执行完 Commit 后，在主库写入 Binlog 日志后即可成功返回客户端，无需等待 Binlog 日志传送给从库，但当主库宕机， 有可能会丢失日志。

  • 半同步复制
    半同步复制，是等待其中一个从库接收到 Binlog 事务并成功写入 Relay Log 之后，才返回 Commit 操作成功给客户端； 这也保证了事务成功提交后至少有两份日志记录，一份在主库 Binlog 上，另一份在从库的 Relay Log 上，从而进一步保证数据完整性； 半同步复制很大程度取决于主从网络 RTT（往返时延），以插件 semisync_master/semisync_slave 形式存在。
    原理：

    • master 将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
      在每个事务更新数据完成之前，master 在二进制日志记录这些改变。MySQL 将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。 在事件写入二进制日志完成后，master 通知存储引擎提交事务。
    • slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)；
      slave 开始一个 I/O 线程。该线程在 master 创建一个连接，请求从指定日志文件的指定位置之后的日志内容，然后开始 binlog dump process。
    • slave 执行中继日志中的事件，将改变反映为数据。
      Binlog dump process 从 master 的二进制日志中读取事件，如果已经跟上 master，它会睡眠并等待 master 产生新的事件。I/O 线程将这些 事件写入中继日志。SQL slave thread（SQL 从线程）处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 slave 的 数据，使其与master中的数据一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。

    此外，在 master 中也有一个工作线程,和其它 MySQL 的连接一样，slave 在 master 中打开一个连接也会使得 master 开始一个线程。 复制过程有一个很重要的限制——复制在 slave 上是串行化的，也就是说 master 上的并行更新操作不能在 slave 上并行操作。

MySQL 5.7 开始加入了多源复制，这个特性对同时有很多个 MySQL 实例是很有用的，阿里云 RDS（迁移）实现了类似的方式。从 MySQL 5.6.2 开始， MySQL binlog 支持 checksum 校验，并且 5.6.6 默认启用（CRC32），这对自己模拟实现 MySQL 复制的场景有影响。

Binlog

MySQL 中的 mysql 库里面表的日志记录格式说明：

  • 通过如 INSERT、UPDATE、DELETE、TRUNCATE 等方式直接修改数据的语句，使用 binlog_format 指定的方式记录
  • 使用 GRANT、ALTER、CREATE、RENAME 等改动的 MySQL 库里数据的，会强制使用 statement-based 方式记录 binlog。 可以在线修改二进制日志类型，如 SET SESSION binlog_format=MIXED;需要 SUPER 权限。

基本要求

  • 主从数据库的版本一致
  • 主库创建复制帐号并赋予复制权限（REPLICATION）
  • 确保开启 binlog 及主从 server_id 唯一

主库授权

  • 创建复制账号
1
2
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

使用 Xtrabackup 配置 MySQL 主从

安装和具体使用，见 Xtrabackup。

  • 使用 Percona-Xtrabackup 恢复数据
    假设一个简单的环境：全量备份，全量恢复，不涉及增量。

创建备份用户并赋予权限：

1
2
GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,PROCESS,SUPER ON *.* TO 'backup_user'@'allow_host' identified by 'password';
FLUSH PRIVILEGES;

数据备份与恢复

具体操作可查看上述的文章 Xtrabackup

配置从库

修改权限

script
1
chown  -R  mysql.mysql  /data/mysql  # /data/mysql 为数据库业务路径地址

  • 从库的配置文件中的 server­id 一定不能与主库相同，否则会出现如下错误：
    Slave: received end packet FROM server, apparent master shutdown

  • 从库一般作为只读库使用，所以为安全起见，设置为只读。

    1
    set global read_only=1;  -- 临时生效，即仅运行期间，重启后失效。

    也可以在从服务器的 my.cnf 里加入 read-only 参数来实现这一点
    read-only 仅对没有超级权限的用户有效。所以最好核对一下连接从服务器的用户，确保其没有 super 权限。 关于从库的事件 MySQL Replication 可以很好的达到你的预期：从库的事件不会自己去执行，主库会把 event 执行的结果直接同步。 在 statement 模式下，复制的是 event BODY 里的 SQL，在 row 模式下是主库事件执行完成后影响的行精确复制。 从库 event_scheduler 参数是被忽略的，并且每个 event 状态会是 SLAVESIDE_DISABLED ，但 CREATE/ALTER EVENT 等操作语句是会复制。 主从切换后，从库事件状态会变成 ENABLE。参数调整从库是不允许写入的，否则数据就不一致了。 主从创建完成后，默认情况下次启动从库，会自动启动复制进程，一般这也正是我们需要的，但在维护阶段时你可能不想从库启动后立即开始复制， –skip-slave-start 选项可以帮到你。 log-slave-updates 正常情况从库是不需要写回放日志产生的binlog，无形中增加服务器压力。 但如果你想要实现级联复制即 A -> B -> C ，B 同时是 A 的从库，也是 C 的主库，就需要开启 log-bin 和 log-slave-updates。
    另外，建议显示设置 log-bin=MySQL-bin 确保主从正常切换。

    1
    show variables like 'log%';  -- 查看当前值

启动从库启动数据库，注意看日志

1
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/my.cnf &

change master

从库上执行如下 SQL

1
2
3
4
5
6
change master to master_host=MASTER_HOST,
master_port=3306,
master_user='slave_user',
master_password='slave_pass',
master_log_file='MySQL-bin.000135',
master_log_pos=262484673;

上面的 master_log_file 和 master_log_pos 即是输出的值，也可以在新的数据目录下 xtrabackup_binlog_info 找到信息。

1
2
show slave status\G;
start slave;

验证同步延迟

从库执行

1
show slave status\G;

节选： Slave_IO_State: Waiting for master to send event Master_Log_File: MySQL-bin.000004 Read_Master_Log_Pos: 931 Relay_Log_File: slave1-relay-bin.000056 Relay_Log_Pos: 950 Relay_Master_Log_File: MySQL-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 931 Relay_Log_Space: 408 Seconds_Behind_Master: 0

Master_Log_File： I/O线程当前正在读取的主服务器二进制日志文件的名称
Read_Master_Log_Pos：本机I/O线程读取主服务器二进制日志位置 上面2各值，与在主库执行show master status;看到的值如果基本接近，
说明从库IO线程已经赶上了主库的binlog。
Relay_Master_Log_File: 由SQL线程执行的包含多数近期事件的主服务器二进制日志文件的名称
Exec_Master_Log_Pos: SQL线程执行来自master的二进制日志最后一个事件位置 与上面的Relay_Master_Log_File一起，同Master_Log_File、
Read_Master_Log_Pos比较，能看
到SQL线程是否已经赶上从库本地的IO线程。
Slave_IO_Running：I/O线程是否启动并成功连接到主服务器上 一般和下面的Slave_IO_Running和Seconds_Behind_Master一起监控主从健康状态
Slave_SQL_Running：SQL线程是否启动
Seconds_Behind_Master: 从属服务器“落后”多少秒 
官网的解释是：The number of seconds that the slave SQL thread is behind processing the
master binary log。但是当 SBM 为 0 时也不代表一定没有延迟，因为可能因为网络慢的缘故，从库的IO线程传输binlog太慢，
它的SQL线程应用日志很容易就赶上relay log，但实际主库产生的binlog比传输的快，就会造成为0的假象。
有时你反复status会发现 Seconds_Behind_Master 的值在0与一个很大的数之间波动，有可能是主库上执行了一个非常大的event，
没执行完毕的时候从库SBM显示为0，event执行完成并传输完binlog后，就会显示 SBM非常巨大。
（我在从机房迁移MySQL到阿里云上部分库老出现这种情况，应该跟网络和大event都有关系）。
另外，relay log 中event记录的时间戳是主库上的时间戳，而SQL thread的时间戳是从库上的，如果主库和从库的时间偏差较大，那么这个SBM的意义就基本不存在了。
推荐文章