跳到主要内容

主从

待完善和验证

用 Replication 机制来实现。同步原理：mysql 开启 bin-log 日志，主服务器所有的增删改操作会记录到 bin-log 日志；然后主服务器把 bin-log 日志发送 给 从服务器 ， 从服务器重放 bin-log 日志确保数据同步

有两种方式：

  • 主从（Master 到 Slave）
  • 双机热备（Master 互通 Slave）

主从的话，数据从 A 单向同步到 B。当黑客删除数据库 A 时，B 也会被删除，所以还是要做全备份、增量备份。

适用场景：主服务器负责增删改 ， 从服务器负责查询

双机热备，就是要保持两个数据库的状态自动同步。对任何一个数据库的操作都自动应用到另外一个数据库，始终保持两个数据库数据一致。

  • 可以做灾备，其中一个坏了可以切换到另一个。
  • 可以做负载均衡，可以将请求分摊到其中任何一台上，提高网站吞吐量。
    • 这个可能要做 id 偏移和自增步数

准备​

  • 服务器 A。假设 IP 为 192.168.1.1，主服务器的数据库用户backup，密码passwordA
  • 服务器 B。假设 IP 为 192.168.1.2，从服务器的数据库用户backup，密码passwordB 两台服务器。建议异地。

主从步骤​

  1. 服务器A、服务器B 装好 MySQL（建议相同版本）
  2. 服务器A 上创建一个备份用户给服务器 B 连接使用
    1. GRANT REPLICATION SLAVE,RELOAD,SUPER ON . TO backup@'192.168.1.2' IDENTIFIED BY 'passwordA';
    2. flush privileges;
  3. 开放连接
    1. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
      1. bind-address = 0.0.0.0
    2. 服务器 B 测试是否能连接 服务器 A
      1. 测试连接是否成功 mysql -h192.168.1.1 -ubackup -ppasswordA
  4. 导入主服务器的数据库后，关闭主服务器的数据库写入，只允许读取
    1. FLUSH TABLES WITH READ LOCK;，当前窗口不可关闭，关闭的话，命令就失效了，Position 的值就会变化。
  5. 编辑 /etc/mysql/mysql.conf.d/mysqld.cnf，设置为主服务器 
    server-id=1
    binlog_format = mixed
    log-bin=mysql-bin

    read-only=0
    binlog-do-db=mediawiki
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
  6. 重启 MySQL
    1. show master status
    2. 这里记住File的值：mysql-bin.0000和Position的值：，比如 mysql-bin.000001 和 145
  7. 导入和主服务器相同的数据库
  8. 编辑服务器 B 中的 /etc/mysql/mysql.conf.d/mysqld.cnf，设置为从服务器 
    server-id=2 
    binlog_format = mixed
    log-bin=mysql-bin

    replicate-do-db=mediawiki
    relay_log=mysqld-relay-bin
    log-slave-updates=ON
  9. 重启 MySQL
  10. CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='backup', MASTER_PASSWORD='passwordA', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=145;
    1. 格式如下：CHANGE MASTER TO MASTER_HOST = '主服务器A的IP', MASTER_USER = '备份的用户名', MASTER_PASSWORD = '用户密码', MASTER_LOG_FILE='主服务器的File',MASTER_LOG_POS=主服务器A的Position;
  11. start slave;
  12. 是否成功，show slave status 1. 
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    1. 以上这两个参数的值为Yes，即说明配置成功！当看到 conecting 为正在连接。
  13. 主服务器，解锁 unlock tables;
  14. 测试。当数据库 A 改变，B 是否改变，会改变即为成功。

双机热备(基于上面的主从配置，再次设置，设置 B为 master，A为 slave)​

  1. 服务器 B
    1. GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.1.1' IDENTIFIED BY 'passwordB';
    2. flush privileges;
  2. 开放连接
    1. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 1bind-address = 0.0.0.0
    2. 服务器 A 测试是否能连接 服务器 B
      1. 测试连接是否成功 mysql -h192.168.1.2 -ubackup -ppasswordB
  3. 导入后，关闭服务器 B 的写入，只允许读取
    1. FLUSH TABLES WITH READ LOCK;
  4. 服务器 B
    1. 在服务器 B 添加 
          read-only=0
          binlog-do-db=mediawiki
          binlog-ignore-db=mysql
          binlog-ignore-db=information_schema
          binlog-ignore-db=performance_schema
    2. 重启 MySQL
  5. show master status
    1. 这里记住File的值：mysql-bin.0000和Position的值：，比如 mysql-bin.000002 和 145
  6. 服务器 A
    1. 在服务器 A 添加 
        replicate-do-db=mediawiki
        relay_log=mysqld-relay-bin
        log-slave-updates=ON
    2. 重启服务器 A 的 MySQL
    3. CHANGE MASTER TO MASTER_HOST='192.168.1.2', MASTER_USER='backup', MASTER_PASSWORD='passwordB', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=145;
    4. start slave;
    5. 是否成功，show slave status
  7. 服务器 B 解锁 unlock tables;
  8. 互备测试。当数据库 A 改变，B 是否改变，当数据库 B 改变，A 是否改变。会改变即为成功。

备忘​

show binlog events

replicate-ignore-db=

log-bin=mysql-bin //启用二进制日志

不管是黑名单（binlog-ignore-db、replicate-ignore-db）还是白名单（binlog-do-db、replicate-do-db）只写一个就行了，如果同时使用那么只有白名单生效。

网友说replicate-do-db的使用中可能会出些问题（http://blog.knowsky.com/19696...），自己没有亲自去测试。猜想binlog-do-db参数用于主服务器中，通过过滤Binary Log来过滤掉配置文件中不允许复制的数据库，也就是不向Binary Log中写入不允许复制数据的操作日志；而replicate-do-db用于从服务器中，通过过滤Relay Log来过滤掉不允许复制的数据库或表，也就是执行Relay Log中的动作时不执行那些不被允许的修改动作。这样的话，多个从数据库服务器的情况：有的从服务器既从主服务器中复制数据，又做为主服务器向另外的从服务器复制数据，那它的配置文件中应该可以同时存在binlog-do-db、replicate-do-db这两个参数才对。一切都是自己的预测，关于binlog-do-db、replicate-do-db的具体使用方法还得在实际开发中一点点摸索才可以。

网上有说，复制时忽略某些数据库或者表的操作最好不要在主服务器上面进行，因为主服务器忽略之后就不会再往二进制文件中写了，但是在从服务器上面虽然忽略了某些数据库但是主服务器上面的这些操作信息依然会被复制到从服务器上面的relay log里面，只是不会在从服务器上面执行而已。我想这个意思应该是建议在从服务器中设置replicate-do-db，而不要在主服务器上设置binlog-do-db。```ini

# 把命令改为配置 // 未测试
master-host=192.168.1.1
master-user=backup
master-password=passwordA
master-port=3306
master-connect-retry=60

# 负载均衡的时候使用
auto-increment-offset=2 # 偏移。第一条数据，id 为2
auto-increment-increment=10 # 步增。第二条数据为2+10=12。

参考​