mysql主从同步可以同步哪些资源

概述

mysql主从同步可以同步哪些资源？视图、存储过程、存储函数、触发器等这些资源到底能不能同步呢，这里我们做一下深入研究，通过查看binlog日志的方式解析每个操作。

做实验前需要了解两个基础知识：

（1）mysql主从同步的工作线程是“Binlog Dump”，slave节点读取master节点的bin_log日志来保存slave节点数据一致。

         binlog的日志格式有两种：ROW和statement

 

（2）查看binlog日志

mysqlbinlog --no-defaults  --base64-output=DECODE-ROWS -v /var/lib/mysql/master-bin.000002

 

操作

建表

首先 创建一个库和几个表用来具体验证：

create database test;

create table t1(name varchar(10),age int);

CREATE TABLE tb1(a1 INT);

CREATE TABLE tb2(a2 INT);

CREATE TABLE `aa` (
  `clock` int(11) NOT NULL DEFAULT '0',
  `message` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

 

 

存储过程

创建一个存储过程

delimiter //
CREATE procedure p1 (IN name varchar(10),IN age int)
 BEGIN
 insert into test.t1 values(name,age);
END//
delimiter ;

 

临时修改日志格式：

修改binlog日志格式为statement，

set session binlog_format='row';

 

查看当前日志格式

show variables like '%binlog_format%';

 

执行存储过程

call p1('tom',10);

此时查看binlog日志日志格式为row

可以看到实际执行的是一个sql语句，如果从库没有这个函数也是可以正常同步的。

 

 

call p1('tom',11);

日志格式为statement

可以看到执行的是一个sql语句，如果从库没有这个函数也是可以正常同步的。

 

存储函数

创建存储函数

CREATE FUNCTION f1 (string VARCHAR(5))

RETURNS VARCHAR(20) DETERMINISTIC

RETURN CONCAT('f1',string);

 

#执行存储函数

insert into t1(name) values(f1('steve'));

 

查看binlog日志，在binlog日志格式为row

在row日志格式下，存储函数可以正常同步

 

 

修改binlog日志格式为statement，

set session binlog_format='statement';

查看当前日志格式

show variables like '%binlog_format%';

 

#执行存储函数

insert into t1(name) values(f1('stevd'));

日志内容如下，日志格式为'statement' ：

可以看到，在statement日志格式下，日志中记录的是原语句，存储过程和存储函数都是存储在系统库中，如果从库中没有同步系统库，从库肯定是不能正常执行这个语句的。

存储函数不能正常同步

视图

-- 创建视图

CREATE VIEW view_info AS SELECT * FROM aa;

可以正常同步

 

触发器

创建一个触发器

delimiter //
CREATE TRIGGER t_tb1 BEFORE INSERT ON tb1
FOR EACH ROW
BEGIN
INSERT INTO tb2 SET a2 = NEW.a1;
END;
//
delimiter ;

insert into tb1 values(1);

日志内容如下，日志格式为row：

row情况下，即使从库没有这个触发器，数据也可以正常同步。

日志格式为statement，日志内容如下：

statement情况下，日志中只记录的执行的语句，没有记录触发的语句，所有如果从库没有这个触发器，就不能正常同步数据了。

 

总结

	statement	ROW
存储过程	可以同步	可以同步
存储函数	不可以	可以同步
触发器	不可以	可以同步
视图	可以同步	可以同步

 

所有说binlog日志格式还是使用ROW好些。

 

参考文档：

https://blog.csdn.net/w892824196/article/details/102999620