认 识mysql复制


MySQL Replication


复制应用场景：



  • 主库顶不住压力，读写分离分担压力
  • 主库故障，从库接管业务
  • 使用从库作为备份
  • 特殊SQL统计


读写分离：首先要处理读次数最多的几个sql


mysql主从复制概述


mysql支持：单向，双向，链式级联，异步复制，半同步复制(mysql5.5后)。


复制过程中：一台服务器当主master，而一个或多个服务器为slave


复制可以是单向：M-->S，也可以是双向M<-->M。




如果设置了链式级联复制，那么从服务器本身除了充当slave之外，还是其下面从服务器的主服务器，结构是：M-->S1-->S2-->S3的形式。


021、主从复制介绍_sql



021、主从复制介绍_log日志_02



021、主从复制介绍_log日志_03


主从复制原理


  2. 主库的DML、DDL记录到binlog中
  3. 从库通过I/O thread发起请求读取主库的binlog
  4. 主库通过I/O dump thread将binlog传递到从库
  5. 从库通过I/O thread将发过来的binlog转存到中继日志relay log中
  6. 从库的SQL thread发起请求读取relay log，并应用到数据库中，来和主库达成一致的状态。



021、主从复制介绍_服务器_04


工作原理的剖析：


就是完全备份和二进制日志备份的还原。二进制日志的还原操作基本上是实时进行，但不是完全的实时，而是异步的实时，其中存在主从服务器之间的执行延时，如果主服务器的压力很大，则这个延时可能会更长。





主从中的线程介绍：

  2. 主服务器的工作线程：IO dump thread，作用是：在接收到从库发来的请求后，负责给salve服务器发送二进制日志；
  3. 从服务器的工作线程：IO thread，负责读取主服务器的binlog日志，并将其保存到自己的中继日志文件relay log中；
  4. 从服务器的工作线程：SQL thread，发起请求读取relay log，并应用到数据库中。





mysql主从复制方法介绍

1、异步复制


异步复制：mysql复制默认是异步复制，master将事件写入binlog，但并不知道slave是否已经接收且已处理。在异步复制的机制下，如果master宕机，事务在master上已提交，但很可能这些事务没有传到任何的slave上。假设有master-->slave故障转移的机制，此时slave可能会丢失事务。



2、半同步复制



  2. 当slave主机连接到master时，能够查看其是否处于半同步复制的机制；
  3. 当master上开启半同步复制的功能时，至少应该有一个slave开启其功能。此时，一个线程在master上提交事务将受到阻塞，直到得知一个已开启半同步复制功能的slave已收到此事务的所有事件，或等待超时；
  4. 当一个事务的时间都已写入其relay log中且已刷新到磁盘上，slave才会告知已收到；
  5. 如果等待超时，也就是master没被告知已收到，此时master会自动转换为异步复制的机制。当至少一个半同步的slave赶上了，master与其slave自动转换为半同步复制的机制；
  6. 半同步复制的功能要在mater，slave都开启，半同步才会起作用；否则，只开启一边，它依然为异步复制。



3、GTID模式


MySQL 5.6.10之后出现了GTID模式，全局事务ID，记录的是事务号。


GTID模式不再需要binlog和position号了，而是直接自动去寻找事务id号，搭建非常方便。


限制：不支持create table as select * from之类的语句。





主从是一套架构，一般情况下，一套主从称为一组(group)，主节点和从节点必须使用相同的端口号；


假如现在，有另一套主从架构，则强烈建议跟之前的主从架构使用不同的端口号，以避免误操作数据库：

--例如，连接数据库的命令中，IP地址写错，还可以有端口号防止进入错误的数据库
[root@ning test]# mysql -uroot -proot -P 3306 -h 192.168.100.111





binlog日志格式


MySQL的主从复制是基于日志的，binlog日志有3种格式：statement、row、mixed。实际生产中基本都是row格式。


statement：基于操作的SQL语句会记录到binlog中，记录的是sql语句。(statement-based replication，SBR)


row：基于行的变更情况记录，会记录变更前的样子和变更后的内容。(row-based replication，RBR)


mixed：混合使用row和statement格式，对于DDL记录成statement，对于DML是row格式。(mixed-based replication，MBR)




如果使用innodb表，事务级别使用read committed或read uncommitted，日志级别只能使用row格式。但在使用row格式中DDL语句不是会记录成statement格式。


statement格式


基于操作的SQL语句会记录到binlog中，记录的是sql语句。(statement-based replication，SBR)


优点：


  • 历史悠久，技术成熟
  • binlog文件较小
  • binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全情况
  • binlog可以用于实时的还原，而不仅仅用于复制
  • 主从版本可以不一样，从服务器可以比主服务器版本高



缺点：


  • 不是所有的update语句都能被复制，尤其是包含不确定操作的时候
  • 调用具有不确定因素的UDF(User-Defined Functions)时复制也可能出问题
  • 使用以下函数的语句无法被复制：load_file()，uuid()，user()，found_rows()，sysdate()(除非启动时启用了--sysdate-is-now参数)
  • insert ... select会产生比RBR更多的行级锁
  • 复制需要进行全表扫描的update时，需要比RBR请求更多的行级锁
  • 对于有auto_increment字段的innodb表而言，insert语句会阻塞其他insert语句
  • 对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而在RBR格式下，只会对那个发生变化的记录产生影响
  • 存储函数(不是存储过程)在被调用的同时也会执行一次now()函数，这个可以说是坏事也可能是好事
  • 确定了的UDF(User-Defined Functions)也需要在从服务器上执行
  • 数据表必须几乎和主服务器保持一致，否则可能会导致复制出错
  • 执行复杂语句如果出错，会消耗更多资源





row格式


row：基于行的变更情况记录，会记录变更前的样子和变更后的内容。(row-based replication，RBR)


优点：


  • 数据库变化采用行模式记录，slave上数据很少会不一致
  • 任何情况都可以被复制，这对复制来说是最安全可靠的
  • 和其他大多数数据库的复制技术一样
  • 多数情况下，从服务器上的表如果有主键，复制就会块很多
  • 复制以下几种语句时的行锁更少：insert ... select，包含auto_increment字段的insert，没有附带条件或没有修改很多记录的update或delete
  • 执行insert，update，delete语句时锁更少
  • 从服务器上采用多线程来执行复制成为可能
  • MySQL 5.6以后，使用以下参数，可以在row格式下查看完整的sql语句：binlog_row_query_log_events



缺点：


  • binlog日志文件大了很多
  • 复杂的回滚时binlog中会包含大量的数据
  • 主服务器上执行update时，所有发生变化的记录都会写道binlog中，而SBR只会写一次，这会导致发生binlog的并发写问题
  • UDF(User-Defined Functions)产生的大BLOB值会导致复制变慢
  • 无法从binlog中看到都复制了些什么语句
  • 当在非事务表执行一段堆积的sql语句时，对好采用SBR模式，否则很容易导致主从服务器的数据不一致情况发生。




master中重要的参数设置



  • log_bin：binlog是否开启的参数
  • server_id：唯一的标识某个数据库实例，并在链式或双主复制结构中用它来避免sql语句的无限循环。
  • log_bin_index：binlog日志的索引文件路径
  • binlog_format：binlog日志格式
  • binlog_cache_size：binlog缓存大小，事务在没有提交（uncommitted）的时候，产生的日志，记录到Cache中，一般为几M大小
  • max_binlog_cache_size：binlog缓存最大可使用的内存大小，有大SQL时或者LOAD DATA时用到
  • max_binlog_size：binlog日志文件的最大的大小
  • binlog_stmt_cache_size：非事务语句缓存大小
  • max_binlog_stmt_cache_size：非事务语句缓存最大的大小
  • sync_binlog：binlog日志的刷新条件：0、交由操作系统决定，1、实时刷新，N、每N个事务刷新
  • expire_logs_days：binlog日志保留的天数
  • log_bin_trust_function_creators：可动态修改，开启binlog时，是否允许创建存储程序(除非有super权限，或者指定deterministic、reads sql data、nosql)
  • auto_increment_increment：自增字段每次增长的步长
  • auto_increment_offset：自增字段初始值
  • binlog_do_db=a，只记录a库的binlog。
  • binlog_ignore_db=b，忽略b库的binlog，即b库的操作不会记录到binlog中，结果是不会复制到从库中。这两个参数互斥，只需要在一个参数设置值即可
  • binlog_direct_non_transactional_updates：表示一个事务里的非事务语句执行完后，马上写入binlog。




binlog_do_db和binlog_ignore_db，在跨库操作时容易丢失数据，使用以下参数解决：


replicate_wild_do_table=test.%


replicate_wild_ignore_table =mysql.%


下面的这些表都建议过滤掉，只复制生产环境数据。


replicate_wild_ignore_table =mysql.%


replicate_wild_ignore_table =test.%


replicate_wild_ignore_table =information_schema.%


replicate_wild_ignore_table =performance_schema.%




slave中重要的参数


  • log_slow_slave_statements：记录sql thread中执行时间超过long_query_time的sql
  • log_slave_updates：为了让slave也能充当master，写relay log时也会写道binlog
  • relay_log_purge：relay log在sql thread执行完后被清理掉
  • slave_net_timeout：slave连接master超时时长




主流架构：


1、主-->从


2、MHA：MySQL的高可用集群，master-slave1-slave2-manager，manager监控主从的状态，监控到主从故障，自动切换到从库。原理是：MHA会虚拟出一个IP，相当于Oracle的vip，主库故障时，vip自动漂移到从库，对于应用来说，该步骤是透明的，应用无需更改连接到数据库的IP地址(vip)。


3、MMM：master-master-monitor，已被废弃。


4、PXC：在前三者基础上改进，开启了多个SQL thread，改善了主从延时的问题。mysql 5.7之后，数据库本身可以添加多个sql thread，PXC架构就使用的少了。


5、中间件：mycat，oneproxy，读写分离，请求分发。