MySQL的主从复制、半同步复制、主主复制详解

标签: js框架 mysql主从复制 | 发表时间:2015-03-04 02:15 | 作者:admin
出处:http://blog.haohtml.com

复制其最终目的是让一台服务器的数据和另外的服务器的数据保持同步,已达到数据冗余或者服务的负载均衡。一台主服务器可以连接多台从服务器,并且从服务器也可以反过来作为主服务器。主从服务器可以位于不同的网络拓扑中,由于mysql的强大复制功能,其复制目标可以是所有的数据库,也可以是某些数据库,甚至是某个数据库中的某些表进行复制。

MySQL支持的两种复制方案:基于语句复制,基于行复制
基于语句复制基于行复制,这两种复制方式都是通过记录主服务器的二进制日志中任何有可能导致数据库内数据发生改变的SQL语句到中继日志,并且在从服务器上执行以下中继日志内的SQL语句,而达到与主服务器的数据同步。不同的是,当主服务器上执行了一个基于变量的数据并将其更新到数据库中,如now()函数,而此时基于语句复制时记录的就是该SQL语句的整个语法,而基于行复制就是将now()更新到数据库的数值记录下来。
例如:在主服务器上执行以下语句:
mysql>update user set createtime=now() where sid=16;
假如此时now()返回的值是:2012-04-16 20:46:35
基于语句的复制就会将其记录为:update user set createtime=now() where sid=16;
基于行复制的就会将其记录为:update user set createtime='2012-04-16 20:46:35' where sid=16;

进行主从复制启动的三个线程
Binlog dump线程:将二进制日志的内容发送给从服务器
I/O从线程:将接受的的数据写入到中继日志
SQL线程:一次从中继日志中读出一句SQL语句在从服务器上执行

一、主从复制:
准备工作:
1.修改配置文件(server_id一定要修改)
2.建立复制用户
3.启动从服务器的从服务进程

规划:
Master:IP地址:172.16.4.11    版本:mysql-5.5.20
Slave:IP地址:172.16.4.12    版本:mysql-5.5.20
这里需注意,mysql复制大部分都是后向兼容,所以, 从服务器的版本一定要高于或等于主服务器的版本。
1、Master
修改配置文件,将其设为mysql主服务器
#vim /etc/f
server_id=11                #修改server_id=11
log_bin=mysql-bin            #开启二进制日志
sync_binlog=1               #任何一个事务提交之后就立即写入到磁盘中的二进制文件
innodb_flush_logs_at_trx_commit=1       #任何一个事物提交之后就立即写入到磁盘中的日志文件

保存退出
#service mysql reload             #重新载入mysql的配置文件

2、Master上创建用户,授予复制权限
mysql>grant replication client,replication slave on *.* to [email protected] identified by '135246';
mysql>flush privileges;
3、Slave
修改配置文件,将其设置为一个mysql从服务器
#vim /etc/f
server_id=12                #修改server_id=12
#log-bin                #注释掉log-bin,从服务器不需要二进制日志,因此将其关闭
relay-log=mysql-relay                #定义中继日志名,开启从服务器中继日志
relay-log-index=mysql-relay.index     #定义中继日志索引名,开启从服务器中继索引
read_only=1                    #设定从服务器只能进行读操作,不能进行写操作

保存退出
#service mysql reload             #重新载入mysql的配置文件

4、验证Slave上中继日志以及server_id是否均生效
mysql>show variables like 'relay%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| relay_log             | relay-bin       |
| relay_log_index       | relay-bin.index |
| relay_log_info_file   |   |
| relay_log_purge       | ON              |
| relay_log_recovery    | OFF             |
| relay_log_space_limit | 0               |
+-----------------------+-----------------+
mysql>show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 12    |
+---------------+-------+

5、启动从服务器的从服务进程
场景一、如果主服务器和从服务器都是新建立的,并没有新增其他数据,则执行以下命令:
mysql>change master to \
master_host='172.16.4.11',
master_user='repl',
master_password='135246';
mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 25520
Relay_Log_Space: 2565465
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
mysql>start slave;
mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 360
Relay_Log_Space: 300
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11

场景二、如果主服务器已经运行过一段了,从服务器是新添加的,则需要将主服务器之前的数据导入到从服务器中:
Master:

#mysqldump -uroot -hlocalhost -p123456 --all-databases --lock-all-tables --flush-logs --master-data=2 > /backup/alldatabase.sql
mysql>flush tables with read lock;
mysql>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      360 |              |                  |
+------------------+----------+--------------+------------------+
mysql>unlock tables;
#scp /backup/alldatabase.sql 172.16.4.12:/tmp

Slave:
#mysql -uroot -p123456 < /tmp/alldatabase.sql
mysql>change master to \
master_host='172.16.4.11',
master_user='repl',
master_password='135246',
master_log_file='mysql-bin.000004',
master_log_pos=360;
mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 360
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 360
Relay_Log_Space: 107
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
mysql>start slave;

mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.16.4.11
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 360
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 360
Relay_Log_Space: 300
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11

说明MySQL的主从复制架构成功

注1:MySQL的复制可以基于某个数据库或库中的默写表进行复制,要想实现该功能,只需在其配置文件中添加以下配置:
Master:
binlog-do-db=db_name        只复制db_name数据库
binlog-ignore-db=db_name    不复制db_name数据库

注2:在Master上定义过滤规则,意味着,任何不涉及到该数据库相关的写操作都不会被记录到二进制日志中,因此不建议在 Master上定义过滤规则,并且不建议binlog-do-db与binlog-ignore-db同时定义。

Slave:
replicate_do_db=db_name            只复制db_name数据库
replicate_ignore_db=db_name        不复制db_name数据库
replicate_do_table=tb_name        只复制tb_name表
replicate_ignore_table=tb_name        只复制tb_name表
replicate_wild_do_table=test%        只复制以test为开头并且后面跟上任意字符的名字的表
replicate_wild_ignore_table=test_    只复制以test为开头并且后面跟上任意单个字符的名字的表

注3:如果需要指定多个db或table时,则只需将命令多次写入

二、半同步复制

由于Mysql的复制都是基于异步进行的,在特殊情况下不能保证数据的成功复制,因此在mysql 5.5之后使用了来自google补丁,可以将Mysql的复制实现半同步模式。所以需要为主服务器加载对应的插件。在Mysql的安装目录下的lib/plugin/目录中具有对应的插件semisync_master.so,semisync_slave.so

在Master和Slave的mysql命令行运行如下命令:

Master:
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
mysql> set global rpl_semi_sync_master_enabled = 1;
mysql> set global rpl_semi_sync_master_timeout = 1000;
mysql> show variables like '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

Slave:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> stop slave;
mysql> start slave;
mysql> show variables like '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

检查半同步是否生效:
Master:
mysql> show global status like 'rpl_semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
说明半同步成功。

让半同步功能在MySQL每次启动都自动生效,在Master和Slave的f中编辑:
Master:
[mysqld]
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000     #1秒

Slave:
[mysqld]
rpl_semi_sync_slave_enabled=1

也可通过设置全局变量的方式来设置是否启动半同步插件:
Master:
mysql> set global rpl_semi_sync_master_enabled=1
取消加载插件
mysql> uninstall plugin rpl_semi_sync_master;

Slave:
mysql> set global rpl_semi_sync_slave_enabled = 1;
mysql> uninstall plugin rpl_semi_sync_slave;

三、主主复制架构
1、在两台服务器上各自建立一个具有复制权限的用户;
Master:
mysql>grant replication client,replication slave on *.* to [email protected] identified by '135246';
mysql>flush privileges;

Slave:
mysql>grant replication client,replication slave on *.* to [email protected] identified by '135246';
mysql>flush privileges;

2、修改配置文件:
Master:
[mysqld]
server-id = 11
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
relay-log=mysql-relay
relay-log-index=mysql-relay.index

Slave:
[mysqld]
server-id = 12
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 2
relay-log=mysql-relay
relay-log-index=mysql-relay.index

3、如果此时两台服务器均为新建立,且无其它写入操作,各服务器只需记录当前自己二进制日志文件及事件位置,以之作为另外的服务器复制起始位置即可
Master:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      360 |              |                  |
+------------------+----------+--------------+------------------+

Slave:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+

4、各服务器接下来指定对另一台服务器为自己的主服务器即可:
Master:
mysql>change master to \
master_host='172.16.4.12',
master_user='repl',
master_password='135246',
master_log_file='mysql-bin.000005',
master_log_pos=107;

Slave:
mysql>change master to \
master_host='172.16.4.11',
master_user='repl',
master_password='135246',
master_log_file='mysql-bin.000004',
master_log_pos=360;

5、启动从服务器线程:
Master:
mysql>start slave;

Slave:
mysql>start slave;

到此主主架构已经成功!

如有错误,欢迎指出!

相关 [mysql 复制 同步] 推荐:

MySQL半同步复制(Semisynchronous Replication)

- - IT技术博客大学习
MySQL5.5引入了半同步复制(Semi-synchronous Replication),以下是对于半同步复制的认知和理解:. 半同步启动需要主从两端都需要加载安装各自对应的semi模块,从库端支持半同步功能的数量至少一台;主库端当一个事务成功提交后,并不及时反馈给前端用户,该线程会被临时block,等待由从库端返回确认该条事务也同时成功写入到relay log中的receipt(回执确认),这时主库线程才返回给当前session告知操作完成,半同步复制并不关心在从库一端该事务是否都被执行并被提交完成.

MySQL中的半同步复制

- - 学习日志
MySQL当前存在的三种复制模式有:异步模式、半同步模式和组复制模式. 注意:MySQL复制模式没有“同步复制”这一项的,文章中只是为了读者方便理解半同步复制的概念才介绍了同步复制概念 https://dev.mysql.com/doc/refman/8.0/en/replication-semisync.html.

MySQL的主从复制、半同步复制、主主复制详解

- - 学习笔记
复制其最终目的是让一台服务器的数据和另外的服务器的数据保持同步,已达到数据冗余或者服务的负载均衡. 一台主服务器可以连接多台从服务器,并且从服务器也可以反过来作为主服务器. 主从服务器可以位于不同的网络拓扑中,由于mysql的强大复制功能,其复制目标可以是所有的数据库,也可以是某些数据库,甚至是某个数据库中的某些表进行复制.

MySQL 同步复制及高可用方案总结

- - SegmentFault 最新的文章
mysql作为应用程序的数据存储服务,要实现mysql数据库的高可用. 必然要使用的技术就是数据库的复制,如果主节点出现故障可以手动的切换应用到从节点,这点相信运维同学都是知道,并且可以实现的. 但是这种情况只是手动的切换,对可用性有要求的业务需要分别实现主库和从库的高可用,保障在数据库出现down机的情况下,可以自动实现数据库的故障转移,保障应用的可用性和用户体验.

MySQL 数据库双向同步复制 - mindwind - 博客园

- -
MySQL 复制问题的最后一篇,关于双向同步复制架构设计的一些设计要点与制约. 数据库的双主双写并双向同步场景,主要考虑数据完整性、一致性和避免冲突. 对于同一个库,同一张表,同一个记录中的同一字段的两地变更,会引发数据一致性判断冲突,尽可能通过业务场景设计规避. 双主双写并同步复制可能引发主键冲突,需避免使用数据库自增类主键方案.

JAVA通过Gearman实现MySQL到Redis的数据同步(异步复制)

- - 企业架构 - ITeye博客
MySQL到Redis数据复制方案. 无论MySQL还是Redis,自身都带有数据同步的机制,像比较常用的 MySQL的Master/Slave模式 ,就是由Slave端分析Master的binlog来实现的,这样的数据复制其实还是一个异步过程,只不过当服务器都在同一内网时,异步的延迟几乎可以忽略.

mysql主从复制

- - SQL - 编程语言 - ITeye博客
从库的配置,mysql5.5不支持配置文件的配置了,问了数据库的人,用命令行指定. 修改从库的配置 #default-storage-engine = InnoDB #修改 default-storage-engine = blackhole server-id = 11215004 #新增 replicate-do-db = test log-bin = mysql-bin #新增 binlog_format = row.

MySQL 双活同步复制的四种方案_咸鱼的梦想专栏-CSDN博客_mysql双机同步复制

- -
对于数据实时同步,其核心是需要基于日志来实现,是可以实现准实时的数据同步,基于日志实现不会要求数据库本身在设计和实现中带来任何额外的约束. 基于MySQL原生复制主主同步方案  . 这是常见的方案,一般来说,中小型规模的时候,采用这种架构是最省事的. 两个节点可以采用简单的双主模式,并且使用专线连接,在master_A节点发生故障后,应用连接快速切换到master_B节点,反之也亦然.

同步mysql数据到hive

- - ITeye博客
地址为:http://archive.cloudera.com/cdh/3/下载相应版本,如sqoop-1.2.0-CDH3B4.tar.gz. 地址为:http://archive.cloudera.com/cdh/3/,版本可以为hadoop-0.20.2-CDH3B4.tar.gz. 3.解压 sqoop-1.2.0-CDH3B4.tar.gz ,hadoop-0.20.2-CDH3B4.tar.gz 到某目录如/home/hadoop/,解压后的目录为.