MySQL Multi-Master实现方式

1月 14th, 2011 | Posted by | Filed under 数据库, 程序设计

本文内容遵从CC版权协议, 可以随意转载, 但必须以超链接形式标明文章原始出处和作者信息及版权声明
网址: http://www.penglixun.com/tech/database/how_to_mysql_multi_master.html

MySQL Mutil-Master Replication喊了很久了,但是MySQL一直没有去,虽然在MySQL源码中有注释将实现Multi-Master,mi结构体也为Multi-Master做好了准备,但是却一直不见MySQL发布。
但是Multi-Master –> Slave的Repliction确实非常有用,例如一台集中备份机备份所有Master的数据。

实现Multi-Master有几种思路:
1. 修改MySQL源码:修改sql_yacc.yy, sql_lex.cc支持多Master的CHANGE MASTER TO语法,然后修改slave相关的slave.cc,支持开启多个Slave, 将slave io/ slave thread线程扩展为一个slave_list。
2. 利用mysqlbinlog之类的工具,远程注册到Master获取binlog,导入本地Slave服务器。

从效率看,肯定第一种方式效率高,但是风险太大了,并且MySQL版本更新,可能需要变动自己的代码以适应新的MySQL Source, MySQL官方的实现方式肯定是第一种,从源码中的注释可以看出他们的设计思路。但是他们考虑的问题可能是多个Master复制如何处理冲突等异常,因而迟迟不发布。

为了避免过多的入侵MySQL,我采用第二种方式,用一个脚本或者程序等等,去调用mysqlbinlog,用-R远程请求到–to-last-log,然后稍微修改一下啊mysqlbinlog的源码,在日志切换后计数一下,在输出文件末尾打上切换日志的个数,例如:

insert into a values (8)
/*!*/;
# at 1070
#110114 16:16:11 server id 3  end_log_pos 1097 	Xid = 36
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
-- Rorate binlog count: 1

— Rorate binlog count: 1就是日志切换信息,表示切换了一次日志(即传入Master的日志号没有用完)然后tail末尾的end_pos来查看本次同步到哪里了,写到*.info的文件中。

我的脚本需要配置一个multi_master.conf文件,配好每个Master的信息,例如:

#cat multi_master.conf 
[master1]
MASTER_HOST=1.2.3.4
MASTER_USER=plx
MASTER_PASSWORD=plx
MASTER_PORT=3306
MASTER_LOG_NAME=mysql-bin
MASTER_LOG_IDX=000002
MASTER_LOG_POS=521
RELAY_LOG_DIR=/tmp/
RELAY_LOG_NAME=1-relay-bin

[master2]
MASTER_HOST=2.3.4.5
MASTER_USER=plx
MASTER_PASSWORD=plx
MASTER_PORT=3306
MASTER_LOG_NAME=mysql-bin
MASTER_LOG_IDX=000002
MASTER_LOG_POS=581
RELAY_LOG_DIR=/tmp/
RELAY_LOG_NAME=2-relay-bin

[slave]
SLAVE_USER=plx
SLAVE_PASSWORD=plx

SLAVE默认导入本地,所以没有提供主机选项。
配置文件的含义是,定义了master1和master2两个Master,名称其实只要不是slave都行,[slave]中定义了本地导入的用户名和密码。
特有的参数我解释下,没解释的跟MySQL一样,
MASTER_LOG_NAME和MASTER_LOG_IDX组成MySQL中的Master_log_file,RELAY_LOG_DIR表示取回的binlog文件放哪个目录,RELAY_LOG_NAME是Relay文件的文件名,会加上标号,跟MySQL一样,这个脚本会自动处理。
一旦执行过一次,就会生成master1.info之类的文件,来表示当前同步到哪里了,例如下面这个例子:

MASTER_LOG_POS=1482
NAME=master1
MASTER_USER=plx
RELAY_LOG_NAME=1-relay-bin
MASTER_LOG_IDX=2
MASTER_HOST=1.2.3.4
MASTER_LOG_NAME=mysql-bin
MASTER_PORT=3306
RELAY_LOG_DIR=/tmp/
MASTER_PASSWORD=plx
RELAY_LOG_IDX=3

只有找不到*.info的时候,才会使用multi_master.conf。

现在每次调度multi_master_repl.pl都只会运行一次,可以不断的调度multi_master_repl.pl,因为还没有完全搞定KILL信号在Perl脚本的处理,用C重写后会解决,不能暴力kill -9,会导致不知道复制到哪里了。

这是下载地址,切勿用在生产环境,这只是个验证想法的程序。

  MultiMasterRepl.tar.gz (798.9 KiB, 891 hits)

下一步我想用C重新实现,在mysqlbinlog源码基础上修改,获取到的日志直接写入到sock或直接导入远程mysql,避免多写一次文件,也欢迎提供新思路。

这是一次执行的日志:

#./multi_master_repl.pl 
(DEBUG) Enter: get_config()
	Info: begin
	(DEBUG) get_config --> master1
	(DEBUG) get_config --> multi_master.conf --> master1:MASTER_HOST=1.2.3.4
	(DEBUG) get_config --> multi_master.conf --> master1:MASTER_USER=plx
	(DEBUG) get_config --> multi_master.conf --> master1:MASTER_PASSWORD=plx
	(DEBUG) get_config --> multi_master.conf --> master1:MASTER_PORT=3306
	(DEBUG) get_config --> multi_master.conf --> master1:MASTER_LOG_NAME=mysql-bin
	(DEBUG) get_config --> multi_master.conf --> master1:MASTER_LOG_IDX=000002
	(DEBUG) get_config --> multi_master.conf --> master1:MASTER_LOG_POS=521
	(DEBUG) get_config --> multi_master.conf --> master1:RELAY_LOG_DIR=/tmp/
	(DEBUG) get_config --> multi_master.conf --> master1:RELAY_LOG_NAME=1-relay-bin
	(DEBUG) get_config --> Found master1.info, Read it
	(DEBUG) get_config --> master1.info --> master1:MASTER_LOG_POS=1097
	(DEBUG) get_config --> master1.info --> master1:NAME=master1
	(DEBUG) get_config --> master1.info --> master1:MASTER_USER=plx
	(DEBUG) get_config --> master1.info --> master1:RELAY_LOG_NAME=1-relay-bin
	(DEBUG) get_config --> master1.info --> master1:MASTER_LOG_IDX=2
	(DEBUG) get_config --> master1.info --> master1:MASTER_HOST=1.2.3.4
	(DEBUG) get_config --> master1.info --> master1:MASTER_LOG_NAME=mysql-bin
	(DEBUG) get_config --> master1.info --> master1:MASTER_PORT=3306
	(DEBUG) get_config --> master1.info --> master1:RELAY_LOG_DIR=/tmp/
	(DEBUG) get_config --> master1.info --> master1:MASTER_PASSWORD=plx
	(DEBUG) get_config --> master1.info --> master1:RELAY_LOG_IDX=2
	(DEBUG) get_config --> Push[master1] to Master_Info_List
	(DEBUG) get_config --> master2
	(DEBUG) get_config --> multi_master.conf --> master2:MASTER_HOST=2.3.4.5
	(DEBUG) get_config --> multi_master.conf --> master2:MASTER_USER=plx
	(DEBUG) get_config --> multi_master.conf --> master2:MASTER_PASSWORD=plx
	(DEBUG) get_config --> multi_master.conf --> master2:MASTER_PORT=3306
	(DEBUG) get_config --> multi_master.conf --> master2:MASTER_LOG_NAME=mysql-bin
	(DEBUG) get_config --> multi_master.conf --> master2:MASTER_LOG_IDX=000002
	(DEBUG) get_config --> multi_master.conf --> master2:MASTER_LOG_POS=581
	(DEBUG) get_config --> multi_master.conf --> master2:RELAY_LOG_DIR=/tmp/
	(DEBUG) get_config --> multi_master.conf --> master2:RELAY_LOG_NAME=2-relay-bin
	(DEBUG) get_config --> Found master2.info, Read it
	(DEBUG) get_config --> master2.info --> master2:MASTER_LOG_POS=1541
	(DEBUG) get_config --> master2.info --> master2:NAME=master2
	(DEBUG) get_config --> master2.info --> master2:MASTER_USER=plx
	(DEBUG) get_config --> master2.info --> master2:RELAY_LOG_NAME=2-relay-bin
	(DEBUG) get_config --> master2.info --> master2:MASTER_LOG_IDX=2
	(DEBUG) get_config --> master2.info --> master2:MASTER_HOST=2.3.4.5
	(DEBUG) get_config --> master2.info --> master2:MASTER_LOG_NAME=mysql-bin
	(DEBUG) get_config --> master2.info --> master2:MASTER_PORT=3306
	(DEBUG) get_config --> master2.info --> master2:RELAY_LOG_DIR=/tmp/
	(DEBUG) get_config --> master2.info --> master2:MASTER_PASSWORD=plx
	(DEBUG) get_config --> master2.info --> master2:RELAY_LOG_IDX=2
	(DEBUG) get_config --> Push[master2] to Master_Info_List
	(DEBUG) get_config --> multi_master.conf --> slave:SLAVE_USER=plx
	(DEBUG) get_config --> multi_master.conf --> slave:SLAVE_PASSWORD=plx
(DEBUG) Enter: get_config()
	Info: exit
(DEBUG) Enter: create_slave_threads()
	Info: begin
	(DEBUG) create_slave_threads --> Creating run_slave Threads...
(DEBUG) Enter: run_slave()
	Info: begin [tid: 1]
	(DEBUG) run_slave(0) --> NO KILL SIGNAL --> g_is_killed =>0
	(DEBUG) run_slave --> mysqlbinlog: ./mysqlbinlog -h1.2.3.4 -uplx -pplx -R -t --start-position=1097 mysql-bin.000002 > /tmp/1-relay-bin.000002
Warning: ./mysqlbinlog: unknown variable 'loose_default-character-set=utf8'
	(DEBUG) run_slave(0) --> NO KILL SIGNAL --> g_is_killed =>0
(DEBUG) Enter: import_to_slave()
	Info: begin [Param: p_master_idx=>0]
	(DEBUG) import_to_slave(0) --> NO KILL SIGNAL --> g_is_killed =>0
	(DEBUG) import_to_slave(0) --> Importing Relay Log /tmp/1-relay-bin.000002 To Slave...
	(DEBUG) create_slave_threads --> Created 2 run_slave Threads
(DEBUG) Enter: run_slave()
	Info: begin [tid: 2]
	(DEBUG) run_slave(1) --> NO KILL SIGNAL --> g_is_killed =>0
	(DEBUG) run_slave --> mysqlbinlog: ./mysqlbinlog -h2.3.4.5 -uplx -pplx -R -t --start-position=1541 mysql-bin.000002 > /tmp/2-relay-bin.000002
Warning: ./mysqlbinlog: unknown variable 'loose_default-character-set=utf8'
	(DEBUG) run_slave(1) --> NO KILL SIGNAL --> g_is_killed =>0
(DEBUG) Enter: import_to_slave()
	Info: begin [Param: p_master_idx=>1]
	(DEBUG) import_to_slave(1) --> NO KILL SIGNAL --> g_is_killed =>0
	(DEBUG) import_to_slave(1) --> Importing Relay Log /tmp/2-relay-bin.000002 To Slave...
(DEBUG) Enter: update_master_info()
	Info: begin [Param: p_master_idx=>0]
(DEBUG) Enter: update_master_info()
	Info: begin [Param: p_master_idx=>1]
	(DEBUG) update_master_info(0) --> Now Master-Log is mysql-bin.000002 Pos is 1482
(DEBUG) Enter: update_master_info_file()
	Info: begin [Param: p_master_idx=>0]
	(DEBUG) update_master_info_file(0) --> NO KILL SIGNAL --> g_is_killed =>0
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> MASTER_LOG_POS=1482
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> NAME=master1
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> MASTER_USER=plx
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> RELAY_LOG_NAME=1-relay-bin
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> MASTER_LOG_IDX=2
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> MASTER_HOST=1.2.3.4
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> MASTER_LOG_NAME=mysql-bin
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> MASTER_PORT=3306
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> RELAY_LOG_DIR=/tmp/
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> MASTER_PASSWORD=plx
	(DEBUG) update_master_info_file(0) --> Writing master1.info --> RELAY_LOG_IDX=3
	(DEBUG) update_master_info_file(0) --> Created master1.info
(DEBUG) Enter: update_master_info_file(0)
	Info: exit
(DEBUG) Enter: update_master_info(0)
	Info: exit
(DEBUG) Enter: import_to_slave(0)
	Info: exit
(DEBUG) Enter: run_slave(0)
	Info: exit
	(DEBUG) update_master_info(1) --> Now Master-Log is mysql-bin.000002 Pos is 2120
(DEBUG) Enter: update_master_info_file()
	Info: begin [Param: p_master_idx=>1]
	(DEBUG) update_master_info_file(1) --> NO KILL SIGNAL --> g_is_killed =>0
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> MASTER_LOG_POS=2120
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> NAME=master2
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> MASTER_USER=plx
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> RELAY_LOG_NAME=2-relay-bin
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> MASTER_LOG_IDX=2
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> MASTER_HOST=2.3.4.5
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> MASTER_LOG_NAME=mysql-bin
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> MASTER_PORT=3306
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> RELAY_LOG_DIR=/tmp/
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> MASTER_PASSWORD=plx
	(DEBUG) update_master_info_file(1) --> Writing master2.info --> RELAY_LOG_IDX=3
	(DEBUG) update_master_info_file(1) --> Created master2.info
(DEBUG) Enter: update_master_info_file(1)
	Info: exit
(DEBUG) Enter: update_master_info(1)
	Info: exit
(DEBUG) Enter: import_to_slave(1)
	Info: exit
(DEBUG) Enter: run_slave(1)
	Info: exit
(DEBUG) Enter: create_slave_threads()
	Info: exit
  1. Luke
    1月 14th, 201118:57

    hi.是不是像oracle,mysql…这些数据库都可以通过完整的log,重现所有数据crud的过程呀?我是外行,呵呵。

    [回复]

    P.Linux 回复:

    @Luke, Oracle日志格式不公开,MySQL开源,所以日志格式公开的。

    [回复]

    yifangs 回复:

    @P.Linux, 那SQL Server的日志格式是否是公开的呢? 还有就是mysqllogbin的源码哪里可以下载呀

    [回复]

  2. lyz
    2月 24th, 201110:09

    使用一个slave按照一定的规则在不同的master之间切换效果应该跟你这样一致

    [回复]

  3. 陈子
    3月 1st, 201218:40

    CPAN上不是有MySQL::Replication么~~

    [回复]

  4. DemonHunter
    7月 2nd, 201319:00

    将slave 在不同时间点指向不同的master 并且记录每次复制到不同master的相关信息。然后不断重复进行,缺点是复制延迟会很高。不知可否??

    [回复]

  5. DemonHunter
    7月 2nd, 201319:01

    只要写一个python脚本就可以实现,但是不知道是否稳妥?

    [回复]