网站建设教程出售用苏州久远网络,网站建设参考书籍,做海报找素材网站,微盟公司简介MYSQL复制的几种模式 MySQL 5.1 中#xff0c;在复制方面的改进就是引进了新的复制技术#xff1a;基于行的复制。MYSQL复制的几种模式 MySQL 5.1 中#xff0c;在复制方面的改进就是引进了新的复制技术#xff1a;基于行的复制。 简言之#xff0c;这种新技术就是关注表中… MYSQL复制的几种模式 MySQL 5.1 中在复制方面的改进就是引进了新的复制技术基于行的复制。MYSQL复制的几种模式 MySQL 5.1 中在复制方面的改进就是引进了新的复制技术基于行的复制。 简言之这种新技术就是关注表中发生变化的记录而非以前的照抄 binlog 模式。 从 MySQL 5.1.12 开始可以用以下三种模式来实现 -- 基于SQL语句的复制(statement-based replication, SBR) -- 基于行的复制(row-based replication, RBR) -- 混合模式复制(mixed-based replication, MBR)。 相应地binlog的格式也有三种STATEMENTROWMIXED。 MBR 模式中SBR 模式是默认的。 在运行时可以动态低改变binlog的格式除了以下几种情况 . 存储过程或者触发器中间 . 启用了NDB . 当前会话试用 RBR 模式并且已打开了临时表 如果binlog采用了 MIXED 模式那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式。 . 当DML语句更新一个NDB表时【NDB为分布式所创】. 当函数中包含 UUID() 时. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时. 行任何 INSERT DELAYED 语句时. 用 UDF 时. 视图中必须要求使用 RBR 时例如创建视图是使用了 UUID() 函数设定主从复制模式的方法非常简单只要在以前设定复制配置的基础上再加一个参数binlog_formatSTATEMENT #binlog_formatROW#binlog_formatMIXED当然了也可以在运行时动态修改binlog的格式。例如mysql SET SESSION binlog_format STATEMENT;mysql SET SESSION binlog_format ROW;mysql SET SESSION binlog_format MIXED;mysql SET GLOBAL binlog_format STATEMENT;mysql SET GLOBAL binlog_format ROW;mysql SET GLOBAL binlog_format MIXED; 两种模式各自的优缺点 SBR 的优点 历史悠久技术成熟 binlog文件较小 binlog中包含了所有数据库更改信息可以据此来审核数据库的安全等情况 binlog可以用于实时的还原而不仅仅用于复制 主从版本可以不一样从服务器版本可以比主服务器版本高 SBR 的缺点 不是所有的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 语句 执行 INSERTUPDATEDELETE 语句时锁更少从服务器上采用多线程来执行复制成为可能 RBR 的缺点 binlog 大了很多 复杂的回滚时 binlog 中会包含大量的数据 主服务器上执行 UPDATE 语句时所有发生变化的记录都会写到 binlog 中而 SBR 只会写一次这会导致频繁发生 binlog 的并发写问题 UDF 产生的大 BLOB 值会导致复制变慢无法从 binlog 中看到都复制了写什么语句(加密过的) 当在非事务表上执行一段堆积的SQL语句时最好采用 SBR 模式否则很容易导致主从服务器的数据不一致情况发生 另外针对系统库 mysql 里面的表发生变化时的处理规则如下如果是采用 INSERTUPDATEDELETE 直接操作表的情况则日志格式根据 binlog_format 的设定而记录 如果是采用 GRANTREVOKESET PASSWORD 等管理语句来做的话那么无论如何都采用 SBR 模式记录 注采用 RBR 模式后能解决很多原先出现的主键重复问题。 实例: 对于insert into db_allot_ids select * from db_allot_ids 这个语句: 在BINLOG_FORMATSTATEMENT 模式下: BINLOG日志信息为: ----------------------------------------- BEGIN /*!*/; # at 173 #090612 16:05:42 server id 1 end_log_pos 288 Query thread_id4 exec_time0 error_code0 SET TIMESTAMP1244793942/*!*/; insert into db_allot_ids select * from db_allot_ids /*!*/; ----------------------------------------- 在BINLOG_FORMATROW 模式下: BINLOG日志信息为: ----------------------------------------- BINLOG hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAD8AQEAAAD8AQEAAAA /*!*/; ----------------------------------------- 确实看不懂里面记的是啥. 二、Replication 实现级别 由于MySQL Replication 是基于 Binary Log 实现的所以Replication 的实现级别实际上是由Binary Log 的存储格式所决定。Binary Log 中记录 Eent 的方式可以是基于一条语句(Statement Level)也可以是基于一条记录(Row level)这可以在 MySQL 的配置参数(—binlog-format)中设定这个格式。 1. Row LevelBinary Log 中会记录成每一行数据被修改的形式然后在 Slave 端再对相同的数据进行修改。 优点在 Row Level 模式下Binary Log 中可以不记录执行的sql语句的上下文相关的信息仅仅只需要记录那一条记录被修改了修改成什么样了。所以 Row Level 的日志内容会非常清楚的记录下每一行数据修改的细节非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。 缺点Row Level下所有的执行的语句当记录到 Binary Log 中的时候都将以每行记录的修改来记录这样可能会产生大量的日志内容比如有这样一条update语句UPDATE group_message SET group_id 1 where group_id 2执行之后日志中记录的不是这条update语句所对应的事件(MySQL以事件的形式来记录 Binary Log 日志)而是这条语句所更新的每一条记录的变化情况这样就记录成很多条记录被更新的很多个事件。自然Binary Log 日志的量就会很大。尤其是当执行ALTER TABLE 之类的语句的时候产生的日志量是惊人的。因为MySQL对于 ALTER TABLE 之类的 DDL 变更语句的处理方式是重建整个表的所有数据也就是说表中的每一条记录都需要变动那么该表的每一条记录都会被记录到日志中。 2. Statement Level:每一条会修改数据的 Query 都会记录到 Master的 Binary Log 中。Slave在复制的时候 SQL 线程会解析成和原来 Master 端执行过的相同的 Query 来再次执行。 优点Statement Level下的优点首先就是解决了Row Level下的缺点不需要记录每一行数据的变化减少 Binary Log 日志量节约了 IO 成本提高了性能。因为他只需要记录在Master上所执行的语句的细节以及执行语句时候的上下文的信息。 缺点由于他是记录的执行语句所以为了让这些语句在slave端也能正确执行那么他还必须记录每条语句在执行的时候的一些相关信息也就是上下文信息以保证所有语句在slave端杯执行的时候能够得到和在master端执行时候相同的结果。另外就是由于Mysql现在发展比较快很多的新功能不断的加入使mysql得复制遇到了不小的挑战自然复制的时候涉及到越复杂的内容bug也就越容易出现。在statement level下目前已经发现的就有不少情况会造成mysql的复制出现问题主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现比如sleep()函数在有些版本中就不能真确复制在存储过程中使用了last_insert_id()函数可能会使slave和master上得到不一致的id等等。由于row level是基于每一行来记录的变化所以不会出现类似的问题。 3. Mixed Level: 从 5.1.8 版本开始MySQL 提供了除Statement Level和Row Level之外的第三种 Mixed Level实际上就是前两种模式的结合。在Mixed模式下MySQL会根据执行的每一条具体的 Query 语句来区分对待记录的日志形式也就是在Statement和Row之间选择一种。除了MySQL认为通过STATEMENT方式可能造成复制过程中Master与Slave之间产生不一致数据(如特殊Procedure和Function的使用UUID()函数的使用等特殊情况)的时候MySQL会选择ROW的模式来记录变更之外都会使用STATEMENT模式来记录变更。当然这里需要排除的特殊情况并不仅仅只有上面所描述的这几种具体请参考 MySQL 官方的详细手册。 老版本的 MySQL 一直都只有基于 Statement 的复制模式直到5.1.5版本的 MySQL 才开始支持Row Level的复制。从5.0开始MySQL 的复制已经解决了大量老版本中出现的无法正确复制的问题。但是由于存储过程的出现给 MySQL 的复制又带来了更大的新挑战。另外看到官方文档说从5.1.8版本开始MySQL 开始提供 Mixed Level新版本中的Statment level还是和以前一样仅仅记录执行的语句。而新版本的Mysql中队Row Level模式也被做了优化并不是所有的修改都会以Row Level来记录像遇到表结构变更的时候就会以statement模式来记录如果 Query 语句确实就是 UPDATE 或者 DELETE 等修改数据的语句那么还是会记录所有行的变更。 来源http://blog.csdn.net/adparking/article/details/7586054 转载于:https://blog.51cto.com/longzhiyi/903894
