mysql-主从复制

MySQL一主一从配置

修改主机和从机的mysql配置文件。这里假设主机在windows环境中,从机在linux环境中。

要求

主机和从机的mysql版本一致,主从都配置在[mysqld]节点下,主机和从机都关闭防火墙。

步骤

  1. 主机my.ini配置文件
    必配置项

    • 主服务器ID唯一:server-id=1
    • 配置二进制日志:log-bin=path

    可选配置项

    • 主机可读可写:read-only=0
    • 启用错误日志:log-err=path/mysqlerr.log
    • 临时目录:tmpdir=path
    • 数据目录:datadir=”path/Data/“
    • 设置不要复制的数据库:binlog-ignore-db=databaseName
    • 设置需要复制的主数据库名字:binlog-do-db=databaseName
    • 根路径:basedir=自己本地路径(如basedir=”D:/Program Files/MySQL/MySQL Server 5.6/“)
    • 如图:
  2. 从机my.cnf配置文件
    必配置项

    • 配置中继日志:replay-log=path

    • 从服务器ID唯一:server-id=2

    可选配置项

    • 从机只读:read-only=1
    • 配置二进制日志:log-bin=path
  3. 更改主从机配置后重启服务

  4. 在主机建立账户并授权slave

    GRANT REPLICATION SLAVE ON . TO ‘zhangsan’@’从机器数据库IP’ IDENTIFIED BY ‘123456’;
    flush privileges;

    查询master的状态:show master status; 记录File和Position的值。

  5. 在从机上配置需要复制的主机

    CHANGE MASTER TO MASTER_HOST=’主机IP’,MASTER_USER=’zhangsan’,
    MASTER_PASSWORD=’123456’,MASTER_LOG_FILE=’File名字’,MASTER_LOG_POS=Position值;

    启动从服务器复制功能:start slave;

    查询slave的状态:show slave status\G;
    Slave_IO_RunningSlave_SQL_Running都为Yes表示主从配置成功!

  6. 停止复制功能:stop slave;

bin-log主从复制配置

1
2
3
4
5
6
binlog_format           = MIXED                         //binlog日志格式,mysql默认采用statement,建议使用mixed
log-bin = /data/mysql/mysql-bin.log //binlog日志文件
expire_logs_days = 7 //binlog过期清理时间
max_binlog_size = 100m //binlog每个日志文件大小
binlog_cache_size = 4m //binlog缓存大小
max_binlog_cache_size = 512m //最大binlog缓存大小

如果没有对log-bin指定log文件,默认在 /var/lib/mysql目录下以mysqld-bin.00000X等作为名称。而 mysqld-bin.index则记录了所有的log的文件名称。
使用时则使用mysqlbinlog /var/lib/mysql|grep "mysqld-bin.00000X"等来追踪database的操作。

系统变量binlog_format 指定二进制日志的类型。分别有STATEMENT、ROW、MIXED三种值。MySQL 5.7.6之前默认为STATEMENT模式。MySQL 5.7.7之后默认为ROW模式。这个参数主要影响主从复制。

bingo格式

STATEMENT

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化,减少了binlog日志量,节约IO,提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)

ROW模式

不记录每条sql语句的上下文信息,仅需记录哪条数据被修改了,修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志,尤其是alter table的时候会让日志暴涨。

MIXED模式

以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。

对于执行的SQL语句中包含now()这样的时间函数,会在日志中产生对应的unix_timestamp()*1000的时间字符串,slave在完成同步时,取用的是sqlEvent发生的时间来保证数据的准确性。另外对于一些功能性函数slave能完成相应的数据同步,而对于上面指定的一些类似于UDF函数,导致Slave无法知晓的情况,则会采用ROW格式存储这些Binlog,以保证产生的Binlog可以供Slave完成数据同步。

binlog_format的三种模式

MySQL复制主要有三种方式:

  • 基于SQL语句的复制(statement-based replication, SBR)
  • 基于行的复制(row-based replication, RBR)
  • 混合模式复制(mixed-based replication, MBR)。

对应于binlog的三种格式:STATEMENT,ROW,MIXED。

SBR(基于SQL)

优点
  • 历史悠久,技术成熟
  • binlog文件较小
  • binlog中包含了所有数据库更改信息,可以据此来审核数据库的安全等情况
  • binlog可以用于实时的还原,而不仅仅用于复制
  • 主从版本可以不一样,从服务器版本可以比主服务器版本高
缺点
  • 不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
  • 调用具有不确定因素的 UDF 时复制也可能出问题
  • 使用以下函数的语句也无法被复制:
    • LOAD_FILE()
    • UUID()
    • USER()
    • FOUND_ROWS()
    • SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)
  • INSERT … SELECT 会产生比 RBR 更多的行级锁
  • 复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时,需要比 RBR 请求更多的行级锁
  • 对于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 语句会阻塞其他 INSERT 语句
  • 对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响
  • 存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数,这个可以说是坏事也可能是好事
  • 确定了的 UDF 也需要在从服务器上执行
  • 数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错
  • 执行复杂语句如果出错的话,会消耗更多资源

RBR(基于行)

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

主从复制原理

主服务器数据库的每次操作都会记录在其二进制文件mysql-bin.xxx(该文件可以在mysql目录下的data目录中看到)中,从服务器的I/O线程使用专用账号登录到主服务器中读取该二进制文件,并将文件内容写入到自己本地的中继日志relay-log文件中,然后从服务器的SQL线程会根据中继日志中的内容执行SQL语句。

简单来说,slave会从master读取binlog来进行数据同步。

分为三个步骤:

  • master将改变记录到二进制日志(binary log)。这些记录过程叫做二进制日志事件,binary log events。
  • slave将master的binary log events拷贝到它的中继日志(relay log)
  • slave重做中继日志中的事件,将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的。

主从复制基本原则

  • 每个slave只有一个master
  • 每个slave只能有一个唯一的服务器ID
  • 每个master可以有多个salve

主从不一致问题

网络的延迟

由于mysql主从复制是基于binlog的一种异步复制 通过网络传送binlog文件,理所当然网络延迟是主从不同步的绝大多数的原因,特别是跨机房的数据同步出现这种几率非常的大,所以做读写分离,注意从业务层进行前期设计。

主从两台机器的负载不一致

由于mysql主从复制是主数据库上面启动1个io线程,而从上面启动1个sql线程和1个io线程,当中任何一台机器的负载很高,忙不过来,导致其中的任何一个线程出现资源不足,都将出现主从不一致的情况。

max_allowed_packet设置不一致

主数据库上面设置的max_allowed_packet比从数据库大,当一个大的sql语句,能在主数据库上面执行完毕,从数据库上面设置过小,无法执行,导致的主从不一致。

自增键不一致

key自增键开始的键值跟自增步长设置不一致引起的主从不一致。

同步参数设置问题

mysql异常宕机情况下,如果未设置sync_binlog=1或者innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏,导致主从不一致。

版本不一致

解决主从不同步的方法

解决办法一:忽略错误后,继续同步

该方法适用于主从库数据相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况。

方式二:重新做主从,完全同步

该方法适用于主从库数据相差较大,或者要求数据完全统一的情况 解决步骤如下

  1. 先进入主库,进行锁表,防止数据写入 使用命令: flush tables with read lock;
  2. 进行数据备份 把数据备份到mysql.bak.sql文件
  3. 查看master 状态
  4. 把mysql备份文件传到从库机器,进行数据恢复
  5. 停止从库的状态 mysql> stop slave
  6. 然后到从库执行mysql命令,导入数据备份
  7. 设置从库同步,注意该处的同步点,就是主库show master status信息里的| File| Position两项
  8. 重新开启从同步 mysql> start slave;
  9. 查看同步状态 mysql> show slave status\G 查看: Slave_IO_Running: Yes Slave_SQL_Running: Yes

监控mysql主从之间的延迟

主从延迟判断的方法,通常有两种方法:Seconds_Behind_Master和mk-heartbeat

  1. 通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断,是否有发生主从延时。
    • Slave_IO_Running 该参数可作为io_thread的监控项,Yes表示io_thread的和主库连接正常并能实施复制工作,No则说明与主库通讯异常,多数情况是由主从间网络引起的问题;
    • Slave_SQL_Running 该参数代表sql_thread是否正常,具体就是语句是否执行通过,常会遇到主键重复或是某个表不存在。
  2. mk-heartbeat:Maatkit万能工具包中的一个工具,被认为可以准确判断复制延时的方法。mk-heartbeat的实现也是借助timestmp的比较实现的,它首先需要保证主从服务器必须要保持一致,通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表,里面至少有id与ts两个字段,id为server_id,ts就是当前的时间戳now(),该结构也会被复制到从库上,表建好以后,会在主库上以后台进程的模式去执行一行更新操作的命令,定期去向表中的插入数据,这个周期默认为1秒,同时从库也会在后台执行一个监控命令,与主库保持一致的周期去比较,复制过来记录的ts值与主库上的同一条ts值,差值为0表示无延时,差值越大表示延时的秒数越多。我们都知道复制是异步的ts不肯完全一致,所以该工具允许半秒的差距,在这之内的差异都可忽略认为无延时。这个工具就是通过实打实的复制,巧妙的借用timestamp来检查延时;
0%