MySQL复制实践

ForInWay
213 阅读4分钟

1：复制原理

 复制即是让一台服务器的数据与其他服务器保持同步。一台主库的数据可以同步到多台备库上，备库本身也可以配置为另一台服务器的主库，mysql的复制原理可以分为两种，一种是基于行的复制；一种是基于语句的复制。复制分为三个步骤：

  • 名词解释：二进制日志：BINARY_LOG,中继日志：RELAY_LOG
  • 1：在每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中，mysql会按事务提交的顺序而非每条语句执行的顺序来记录二进制日志。
  • 2：备库会启动一个IO线程，跟主库建立一个客户端连接，然后在主库上启动一个特殊的二进制转储线程，该二进制转储线程会监听读取主库二进制日志中的事件，一旦有新的日志事件产生就会发送信号量通知IO线程，IO线程会将接收到的事件记录到中继日志中,如果没有新的日志事件产生，二进制转储线程会进入休眠状态，直到产生新的事件被再次唤醒
  • 3：备库SQL线程会读取中继日志中的事件，并把它重放到备库数据库上(串行执行)

1.1：基于语句的复制

 在基于语句的复制模式下，主库会记录那些造成数据更改的查询，当备库读取并重放这些事件时，实际上只是把主库中执行过的SQL再执行一遍。好处是不用占用太多带宽，对于更新一个好几兆的数据在二进制日志中可能只占几十个字节，坏处是对于一些复杂的程式，例如存储过程、触发器在复制时可能存在问题，而且对于SQL线程重放时，是串行执行的，会占用更多的锁

1.2：基于行的复制

 在基于行的复制模式下，会将实际数据变更记录在二进制日志中，好处是对于存储过程、触发器等复制没有问题，且能够减少锁的使用，对于一些特别的更新也能够很有效，坏处是二进制日志可能会变得很大，占用带宽较多且不能清楚记录更新语句，实际MYSQL会在两种方式中根据需要进行切换

2：复制实践

2.1：前期准备

  • 1：准备两个mysql数据库实例(192.168.4.23,192.168.4.56)
  • 2: 从服务器的版本必须和主服务器版本相同或者高于主服务器版本
  • 3：主服务器的端口必须开放
  • 4：两台数据库的数据库Scheme必须一致

2.2：主服务器配置

  • 1：创建授权账号
 GRANT replication slave ON *.* TO repl@"%" IDENTIFIED BY "repl123"; // 赋予权限
 flush privileges; // 刷新权限
  • 2：配置主库my.cnf文件
// 设置二进制日志文件名(最好显式设置文件名，不然会根据机器名来命令，当机器名发生变化，可能会发生一些预期之外的情况)
log_bin=mysql_bin
// 设置mysql实例id，主从数据库示例server-id必须不一致，默认为1
server-id=1
// 指定mysql的binlog记录的数据库，即可以同步的数据库，可以配置多个，没有配置则同步所有数据库
binlog-do-db=bootdo
// 配置不同步的数据库
binlog-ignore-db=test
// 配置二进制日志日志格式
binlog_format=mixed
// 配置是否只读(0:不是,1:是)
read-only
  • 3：重启数据库
systemctl restart mysqld

2.3：从服务器配置

  • 1：配置主库my.cnf文件
// 设置二进制日志文件名，从服务器最好也可以设置二进制日志，方便之后进行主备切换，名称最好也一样，方便在各服务器间移动
log_bin=mysql_bin
// 设置mysql实例id
server-id=2
// 配置二进制日志日志格式
binlog_format=mixed
// 配置是否只读(0:不是,1:是)
read-only=1
// 指定中继日志存放的目录及文件名(规则同bin_log文件名规则一样)
relay_log=/var/log/mysql/mysql-relay-bin
// 允许备库将其重放的事件也记录到自身的二进制日志中
log_slave_updates=1
  • 2：重启数据库
systemctl restart mysqld

2.4：启动复制

  • 1：在主数据库查看binlog文件名(File)及偏移量(position)
show master status;

  • 2：在从数据库配置启动程序
// 停止slave线程
mysql->stop slave;
// 命令行配置主数据库配置
mysql->change master to 
       master_host = '192.168.4.23', 
       master_user = 'repl',
       master_password = 'repl123',
       master_log_file = 'mysql-bin.000002',
       master_log_pos = 154;
// 开启slave线程
mysql->start slave;
// 查看从服务器状态，如果Slave_IO_Running=Yes，Slave_SQL_Running=Yes则表示配置成功
show slave status;

3：在主数据库执行一些变更，看从数据库是否发生相应的变更

avatar
文章
阅读
粉丝