IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
    • 登录 注册

    MySQL复制备忘

    枯木 (1988.wulei@gmail.com)发表于 2014-02-17 22:50:00
    love 0
    • 复制的用途
    • 复制工作过程
    • 复制配置
      • 创建复制账号
      • 配置主库和备库
      • 启动复制
    • 线上运行后主从配置
    • 一些重要选项
    • 参考

    环境说明

    MySQL支持两种复制方式：基于行的复制和基于语句的复制，两种方式都是通过在主库上记录二进制日志，在被库重复日志的方式来实现异步的数据复制。

    复制的用途：

    • 数据分布
    • 负载均衡
    • 备份
      • 复制只是备份的一种技术补充，但是不能取代备份
    • 高可用性和故障切除
      • 避免单点故障
    • MySQL升级测试

    复制工作过程：

    • 1、主库把数据更改记录到二进制日志[Binary Log]中
    • 2、备库将主库上的日志复制到自己的中继日志[Relay Log]中
    • 3、备库读取中继日志中的事件，将其重放到备库数据之上

    第一步是主库记录二进制日志。在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中，MySQL会按照事务提交的顺序而不是语句执行顺序来记录二进制日志。在记录二进制日志后，主库会告诉存储引擎可以提交事务了。然后备库复制二进制日志到其本地中继日志中，首先备库会启动一个工作线程，称为I/O线程，I/O和主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储[binlog dump]线程[无对应SQL命令]，该线程会读取主库上二进制日志的事件，如果该线程追赶上主库将睡眠状态，直到主库发送信号量通知其有新事件产生才会被唤醒，备库I/O线程会将收到的事件记录到中继日记。备库复制过程有两个线程，一个SQL线程和一个IO线程。

    复制配置：

    要求：主备拥有同样的数据

    过程：

    • 1、在每台服务器上创建复制账号[推荐这么做]
    • 2、配置主库和备库
    • 3、通知备库连接到主库并从主库复制数据

    创建复制账号

    主从推荐都加，其实主只需要REPLICATION SLAVE权限就可以了，主要目的是:

    • 1、监控和管理复制的账号需要REPLICATION CLIENT权限，使用同一账号方便管理
    • 2、方便角色切换
    1
2

    mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
    -> TO repl@'192.168.0.%' IDENTIFIED BY 'pass';

    配置主库和备库

    主库：打开二进制日志选项，指定唯一的server id，在配置文件my.cnf修改如下

    1
2
3

    [mysqld]
log-bin=bin
server-id=1     # server id值应该唯一，防止相同

    如果之前没有开启二进制日志，则需要重启MySQL服务，为确保二进制是否已经创建，使用SHOW MASTER STATUS命令检查

    1
2
3
4
5
6
7

    mysql> SHOW MASTER STATUS\G
+----------------+-----------+--------------+------------------+-------------------+
| File           | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+-----------+--------------+------------------+-------------------+
| bin-log.000001 | 400       |              |                  |                   |
+----------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    备库修改配置如下：

    1
2
3
4
5
6
7

    [mysqld]
log-bin=bin
server-id=2
relay-log=mysqld-relay-bin
log-slave-updates=1
# 如果是A -> B -> C架构需要启用log-slave-updates该选项，允许备库将其重放的事件也记录到自身的二进制日志中
read_only=1     # 防止从库被修改

    log_slave_updates选项可以让备库变成其它服务器的主库,下图为发送复制事件到其它备库

    启动复制

    从库启动复制配置

    1
2
3
4
5
6
7

    mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.10',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='pass',
    -> MASTER_LOG_FILE='bin-log.000001',
    -> MASTER_LOG_POS=400,
    -> MASTER_CONNECT_RETRY=10;

    查看当前状态

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

    mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.0.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000001
          Read_Master_Log_Pos: 400
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 400
        Relay_Master_Log_File: bin-log.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
            ...
            Seconds_Behind_Master: NULL

    START SLAVE

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

    mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin-log.000001
          Read_Master_Log_Pos: 400
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 400
        Relay_Master_Log_File: bin-log.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ...
            Seconds_Behind_Master: 0

    线上服务器配置主从

    很多情况下,一些主库已经运行很长时间了，需要做主从同步，这时从库还没有数据，那么就要把主的数据导入到从。

    冷备：

    最基本的方法是关闭主库，把数据复制到备库。然后重启主库后，会使用一个新的二进制文件，在备库执行CHANGE MASTER TO指向这个文件的起始处即可。[缺点很明显，关闭主库]

    使用热备份：

    *   如果只使用MyISAM表，可以使用mysqldump等方式保持数据一致性备份

    使用mysqldump：

    *   如果只包含InnoDB表，那么使用mysqldump如下命令备份数据

    1
2

    mysqldump -hxx.xx.xx.xx --single-transaction --all-databases \
    --master-data=1 > all-databases.sql

    或者边导出边写入

    1
2

    mysqldump -hmaster_ip --single-transaction --all-databases \
    --master-data=1 | mysql -hslave_ip

    master-data默认值是1，加上之后会记录Position点并执行CHANGE MASTER TO MASTER_LOG_FILE='bin-log.xxxx', MASTER_LOG_POS=xxxx;语句，为2则记录但会注释CHANGE MASTER语句，不会被执行。关于备份可以使用其它的工具如Percona Xtrabackup等等

    • A-B-C方式
      • B为从和主身份时，B实例上需要设置logs-slave-updates选项，这个选项告诉从服务器将其SQL线程执行的更新记录到从服务器自己的二进制日志上，当然前提是需要开启binlog

    一些重要的选项

    • [主]主库上二进制日志最重要的选项:sync_binlog，推荐设置成1，保证在服务器崩溃时不丢失事件，虽然会降低性能
    • [主从]innodb_flush_log_at_trx_commit根据实际情况设置
    • 二进制日志推荐使用指定命名，如log-bin=bin[主从]，中继日志指定命名relay-log=mysqld-relay-bin[从]
    • [从]skip_slave_start选项可以阻止备库崩溃后自动启动复制，预留时间修复
    • [从]read_only，可以阻止大部分用户更改非临时表，除了复制SQL线程和其它拥有超级权限的用户之外

    关于主从复制的一些问题，这里暂不讨论

    参考

    • 《高性能MySQL第三版》
    • MySQL 5.6 Reference Manual

    –EOF–



沪ICP备19023445号-2号
友情链接