mysql主从同步配置与原理

  • 一、原理
  • 1、binlog模式
  • SBR模式
  • RBR模式（ROW-BASED REPLICATION）
  • 2、作用
  • 3、主从模式
  • 二、主从配置
  • 1、master节点
  • 配置文件
  • 创建用户
  • 查看id和文件名称
  • 2、slave节点
  • slave配置
  • 设置replication
  • 三、主从同步测试


一、原理

  MYSQL主从复制的原理是主服务器数据库的每次操作都会记录在mysql下的data目录中二进制文件mysql-bin.xxx里，从服务器的I/O线程使用专用账号登录到主服务器中读取该二进制文件，并将文件内容写入到从服务器的relay-log日志文件中，然后从服务器的SQL线程会根据relay-log日志中的内容执行SQL语句。

1、binlog模式

SBR模式

  SBR(statement-based replication)只执行SQl语句，SBR的日志量取决于SQl的情况，SBR不会记录每一行的变化，节约了binlog日志量,减少IO,提高性能。

  • SBR缺点
      1、 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候，
      2、调用具有不确定因素的UDF时复制也可能出问题
      3、使用以下函数语句无法复制：
    * LOAD_FILE()
    * UUID()
    * USER()
    * FOUND_ROWS()
    * SYSDATE()
      4、执行INSERT … SELECT 会产生比RBR更多的行级锁
      5、复制需要进行全表扫描的UPDATE时，需要比RBR请求更多的行级锁
      6、对于有AUTO_INCREMENT字段的INNODB表，INSERT语句会阻塞其他的INSERT.
      7、对于一些复杂的语句，在服务器上的耗资情况会更严重，而RBR模式下，只会对那个发生变化的记录产生影响
      8、从节点mysql数据库的数据表必须和主服务器一致才可以，否则容易导致同步出错。

RBR模式（ROW-BASED REPLICATION）

  • 优点
  • 任何情况都可以被复制，对于数据来说是最安全可靠的，和大多数数据库系统复制技术一样
  • 从服务器上的表如果有主键，复制会快很多
  • 复制一下语句时行级锁少
  • INSERT … SELECT
  • 包含AUTO_INCREMENT字段的INSERT日志
  • 没有附带条件或者没有修改很多记录的UPDATE、DELETE语句
  • 执行INSERT、UPDATE、DELETE语句时锁更少
  • 服从器上采用多线程来执行复制称为可能
  • RBR缺点
  • binlog日志大了很多
  • 复杂的回滚时binlog中包含大量的数据
  • 主服务器上执行UPDATE语句时，所有发生变化的记录都会写入到binlog中，而SBR只会写一次，这会导致频繁发生binlog并发写问题
  • UDF产生的大量BLOB值会导致复制变慢
  • 无法从binlog中看到都复制写了什么语句
  • 当在非事物表上执行一段堆积的SQL语句时，最好采用SBR模式，否则容易导致主从服务器的数据不一致情况发生

2、作用

  • 数据分布
  • 负载均衡
  • 备份
  • 高可用性和容错行

3、主从模式

本案例讲述为一主一从

  • 一主一从
  • 一主多从
  • 多主一从

二、主从配置

1、master节点

配置文件

  1、修改mysql的配置文件。
vim /etc/my.cnf

log-bin = mysql-bin
server_id = 1
binlog-do-db = hain_res #如果有多个库同步，重复设置此参数
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 1
expire_logs_days = 7
log_bin_trust_function_creators = 1

2、重启mysql
  重启mysql使配置生效。

systemctl restart mysql

创建用户

1、创建同步专用账号
%：代表所有的地址都可以使用此用户进行访问，建议换成指定的从库的地址。

GRANT REPLICATION SLAVE ON *.* to 'mysyxs'@'%' identified by '****';

2、刷新

flush privileges;

查看id和文件名称

1、登录mysql

mysql -h127.0.0.1 -uroot -p****

2、复制id和日志文件名称

show master status;

2、slave节点

slave配置

  1、开启relaylog
修改/etc/my.cnf配置文件

vim /etc/my.cnf
relay-log = /data/3307/relay-bin
server-id = 2

2、重启mysql服务

systemctl resatrt mysqld

设置replication

1、登录mysql

mysql -h127.0.0.1 -uroot -p****

2、设置replication

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1',  
    -> MASTER_PORT=3306,
    -> MASTER_USER='mysqlxs',
    -> MASTER_PASSWORD='*****',
    -> MASTER_LOG_FILE='mysql-bin.0003',根据master的日志文件名进行修改
    -> MASTER_LOG_POS=337;#根据master的id进行修改

3、启动slave

mysql> start slave;

4、查看slave

mysql> show slave status\G

三、主从同步测试

1、在master节点创建表并插入数据（略）
2、在slave节点进行查看结果（略）