mysql 主主同步
本文最后更新于 2353 天前,其中的信息可能已经有所发展或是发生改变。

mysql 主主同步原理

将数据分布到多个系统上去,是通过将Mysql的某一台master主机的数据复制到其它(slave)主机上,并重新执行一遍来实现的;
复制过程中一个服务器充当master服务器,而一台或多台其它服务器充当slave服务器。master服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。
这些日志可以记录发送到slave服务器的更新。当一个slaves服务器连接master服务器时,它通知master服务器从服务器在日志中读取的最后一次成功更新的位置。slave服务器接收从那时起发生的任何更新,然后封锁并等待master服务器通知新的更新。

mysql复制的优点

在slave服务器上执行查询操作,降低master服务器的访问压力
当master服务器上出现了问题可以切换到slave服务器上,不会造成访问中断等问题
在slave服务器上进行备份,以避免备份期间影响master服务器的服务使用及日常访问
Mysql自身的复制功能:是构建大型、高性能应用程序的基础。

mysql支持的复制类型

基于语句的复制:在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制。
基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
混合类型的复制::默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

MySQL复制技术的特点

数据分布 (Data distribution )
备份(Backups)
负载平衡(load balancing)
高可用性和容错性 High availability and failover

复制的工作过程

master将改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件,binary log events);
slave将master的binary log events拷贝到它的中继日志(relay log);
slave重做中继日志中的事件,将改变反映它自己的数据。

第一步:master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务;
第二步:slave将master的binary log拷贝到自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志;
第三步:SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。

下面来实际操作

环境:Mysql version:5.1.40

A server :192.168.1.6
B server :192.168.1.12

A server 的 /etc/my.cnf 文件 [mysqld]段中加入以下内容

server-id = 1     // 唯一的值用来区分server
binlog_format=mixed   // binlog格式
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

B server 的 /etc/my.cnf 文件 [mysqld]段中加入以下内容

server-id = 2
binlog_format=mixed
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2

然后分别重启mysql服务

/etc/init.d/mysqld restart

A server 进入mysql执行以下命令

grant replication slave on *.* to 'repl'@'%' identified by '123123'; ## 创建主从同步账号
 
flush tables with read lock; ## 全局读锁定,只能读不能写。
 
show master status; #一定要记住前两列的内容,一会会用到。

命令结果如下:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000032 |      237 |              |                  |
+------------------+----------+--------------+------------------+

记住 mysql-bin.000032 和 237 待会儿会用到

B server

进入数据库执行

slave stop;

change master to master_host='192.168.1.6', master_port=3306, master_user='repl', master_password='123123', master_log_file='mysql-bin.000032', master_log_pos=237;

// IP地址指向

A server

还有 master_log_file 和 master_log_ps要填写对

B server 进入mysql执行以下命令

grant replication slave on *.* to 'repl'@'%' identified by '123123'; ## 创建主从同步账号
 
flush tables with read lock; ## 全局读锁定,只能读不能写。
 
show master status; #一定要记住前两列的内容,一会会用到。

命令结果如下:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000020 |      237 |              |                  |
+------------------+----------+--------------+------------------+

记住 mysql-bin.000032 和 237 待会儿会用到

A server 进入数据库执行

slave stop;

change master to master_host='192.168.1.12', master_port=3306, master_user='repl', master_password='123123', master_log_file='mysql-bin.000020', master_log_pos=237;

// IP地址指向

B server

还有 master_log_file 和 master_log_ps要填写对

A server

B server

分别执行

slave start;

unlock tables;

分别查看状态:

show slave status\G;

如果发现以下两项内容都是:yes,证明主主同步已经成功。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试

如果刚开始的时候主从上的数据就不一致,那么应该从主上导出sql,然后锁表,再把数据导入到从上,主上再解除锁定。

但是两个新安装的mysql就没有问题,反正也没有数据。

A server

上执行

use tset;  // mysql都自带了一个test库但是没有任何内容
create table t1 (`id` int(4), `name` char(40));  // 创建一个名为t1的表
B server 

上查看已经同步过来了

B server

上执行

use tset;  // mysql都自带了一个test库但是没有任何内容
create table t2 (`id` int(4), `name` char(40));  // 创建一个名为t2的表
A server

上查看已经同步过来了

一些参数的解释:

binlog-do-db=db1,db2

参数含义:需要同步的库

binlog-ignore-db=db1,db2

参数含义:忽略不同步的库

slave-net-timeout=60

参数含义:当slave从主数据库读取log数据失败后,等待多久重新建立连接并获取数据(秒)。

server-id

参数含义:ID值唯一的标识了复制群集中的主从服务器,因此它们必须各不相同。master_id必须为1到232–1之间的一个正整数值,slave_id值必须为2到232–1之间的一个正整数值。

log-bin

  
参数含义:表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提。

binlog-do-db

  
参数含义:表示需要记录进制日志的数据库。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项

binlog-ignore-db

参数含义:表示不需要记录二进制日志的数据库。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项

replicate-do-db

参数含义:表示需要同步的数据库,如果有多个数据库可用逗号分隔,或者使用多个replicate-do-db选项

replicate-ignore-db=mysql

参数含义:表示不需要同步的数据库,如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db=mysql选项

log-slave-updates

参数含义:配置从库上的更新操作是否写入二进制文件,如果这台从库,还要做其他从库的主库,那么就需要打这个参数,以便从库的从库能够进行日志同步

slave-skip-errors

参数含义:在复制过程,由于各种原因导致binlog中的sql出错,默认情况下,从库会停止复制,要用户介入。可以设置Slave-skip-errors来定义错误号,如果复制过程中遇到的错误号是定义的错误号,便可以跳过。如果从库是用来做备份,设置这个参数会存在数据不一致,不要使用。如果是分担主库的查询压力,可以考虑。

sync_binlog=1 or N

  
参数含义:sync_binlog的默认值是0,这种模式下,MySQL不会同步到磁盘中去。这样的话,MySQL依赖操作系统来刷新二进制日志binary log,就像操作系统刷其他文件的机制一样。因此如果操作系统或机器(不仅仅是MySQL服务器)崩溃,有可能binlog中最后的语句丢失了。要想防止这种情况,你可以使用sync_binlog全局变量,使binlog在每N次binlog写入后与硬盘同步。当sync_binlog变量设置为1是最安全的,因为在crash崩溃的情况下,你的二进制日志binary log只有可能丢失最多一个语句或者一个事务。但是,这也是最慢的一种方式(除非磁盘有使用带蓄电池后备电源的缓存cache,使得同步到磁盘的操作非常快)。

即使sync_binlog设置为1,出现崩溃时,也有可能表内容和binlog内容之间存在不一致性。如果使用InnoDB表,MySQL服务器处理COMMIT语句,它将整个事务写入binlog并将事务提交到InnoDB中。如果在两次操作之间出现崩溃,重启时,事务被InnoDB回滚,但仍然存在binlog中。可以用–innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。(注释:在MySQL 5.1中不需要–innodb-safe-binlog;由于引入了XA事务支持,该选项作废了),该选项可以提供更大程度的安全,使每个事务的 binlog(sync_binlog =1)和(默认情况为真)InnoDB日志与硬盘同步,该选项的效果是崩溃后重启时,在滚回事务后,MySQL服务器从binlog剪切回滚的 InnoDB事务。这样可以确保binlog反馈InnoDB表的确切数据等,并使从服务器保持与主服务器保持同步(不接收回滚的语句)。

auto_increment_offset
auto_increment_increment

参数含义:auto_increment_increment和auto_increment_offset用于主-主服务器(master-to-master)复制,并可以用来控制AUTO_INCREMENT列的操作。两个变量均可以设置为全局或局部变量,并且假定每个值都可以为1到65,535之间的整数值。将其中一个变量设置为0会使该变量为1。

这两个变量影响AUTO_INCREMENT列的方式:auto_increment_increment控制列中的值的增量值,auto_increment_offset确定AUTO_INCREMENT列值的起点。

如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值被忽略。例如:表内已有一些数据,就会用现在已有的最大的自增值做为初始值。

参考:

1.http://blog.csdn.net/hguisu/article/details/7325124
2.https://lanjingling.github.io/2015/10/26/mysql-replication-master-master/

暂无评论

发送评论 编辑评论


|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇