关系型数据库之主从复制应用

一、MySQL简单复制相关概念：

10多年的寿光网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。营销型网站建设的优势是能够根据用户设备显示端的尺寸不同，自动调整寿光建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联公司从事“寿光网站设计”,“寿光网站推广”以来，每个客户项目都认真落实执行。

1. mysql复制的意义：Mysql复制是使得mysql完成高性能应用的前提

2. mysql复制的机制：    

Slave端线程: 

IO thread: 向主服务请求二进制日志中的事件

当读取完毕后，IO线程将进行睡眠，当主服务器有新数据时，则主服务器唤醒从服务器的IO线程

SQL thread：从中继日志读取事件并在本地执行，

如果二进制日志开启式，同样会记录二进制日志，但为了节约空间和提高性能，需要关闭

从服务器不能执行写操作，如果执行写操作则和主服务器不同步。 

Master端:

binlog dump: 将IO thread请求的事件发送给对方；

默认为异步工作模式：主要主服务器自己写完，不管从服务器是否写完，就将返回

二、MySQL复制的常见构架

1.主从构架

1)对于一从多主，只有新版本Mysql可以实现。 通常来说，一个从只能有一个主服务器，但是可以轮换。在某一时刻只能有一台主服务器。 

MariaDB-10：支持多主模型，多源复制(multi-source replication)

2)一主多从：执行写操作只能对主服务器进行。 多从会增大主服务器压力。 

此时需要一个调度器，来分离读写请求到主从服务器上。所谓读写分离

3)读写分离：主从模型下，让前端分发器能识别读/写，并且按需调度至目标主机；

amoeba，mysql-proxy可以实现读写分离调度

读服务器进行负载均衡，使用一致性哈希算法，虚拟节点来分配访问。

2.双主构架

1)使用server_id来避免循环赋值

2)必须设定双方的自动增长属性，以避免冲突， 一个使用偶数一个实用技术

第一台服务器：

  auto_increment_increment=1 定义自动增长字段起始值

  auto_increment_offset=2 步长

第二台服务器

auto_increment_increment=2 定义自动增长字段起始值

auto_increment_offset=2 步长

通过以上设定来解决冲突问题。 

3)数据不一致； 在双主模型下某些时刻会导致数据不同步。

第一个服务器锁定第一个字段改第二个字段

第二台服务器锁定第二个字段改第一个字段

例如:一个表包含： Age, Salary

如果一个人的年龄为31而工资2900，执行以下操作

A： update t1 set Salary=salary+1000 WHERE Age>=30;

B: update t1 set Age=Age-3 WHERE Salary < 3000;

会导致：

服务器A Salary 变为3900，年龄31

服务器B Salary 变为年龄28， 工资2900

4)功能：

不能分摊写请求，两个服务器写压力一致。 

三、复制构架扩展

1.主从服务器之间的差距

长时间运行后，主从可能不同步。 因为主服务器可以写并发，但是从服务器的同步只能是但进程。 

从服务器落后，有时候需要认为设计，来做备份。 

2.一主多从的环境： 

1)一主多从的环境中，为了利用各从服务器的缓存能力。需要一些负载均衡算法，来绑定特定查询到特定服务器上，来使得缓存命中。这是这样做使得均衡效果被打破，使得有的服务器过于繁忙。

 

2)为了解决此问题，可以引入中心缓存服务器。

 

3)由于换从服务器工作在旁路模式下，所以是否缓存取决于客户端程序。

memcached：可以提供缓存能力+API 

公共缓存服务器，性能比Mysql自身差

3.多级复制： 主指向一个从，从同时也作为其他从服务器的主 

master –> slave/master —> slave

中间服务器需要开启二进制日志和中继日志 

多级复制可以降低主服务器产生mysqldump的压力，把压力分摊给下一集。 但是可能使得数据不能更好跟新。 

可以把中间服务器的读写引擎改为black hole，来降低本地压力，只生成二进制日志作为中继服务器（relay server），然后把二进制日志发送给下游服务器

4.模拟一从多主模型： 

        根据时间，来却换不同的主。 

        通常用于在不同服务器之间做数据收集。 

5.环状模型： 

每台服务器都是下一台服务器的主服务器同时也是上一台服务器的从服务器，使得每台服务器都是主从，形成传递环。

每台服务器的修改都会同步到环上任何一台服务器中。 

server_id 不能相同。 

6.常见mysql构架

    1)一主一从，并且读取分离 

    2)一主多从，主负责读，从负责写

    3)一主多从外加一个冷备服务器， 只用于备份。 每一段时间关掉进行备份。       

    4)多主模型： 通过心跳信息探测主服务器的健康状态，如果一个主挂掉，马上切换另一个主

潜在问题： 第一台服务器上未提交的事务，切换时将被回滚。 

GTID（GLOBAL TRANSACTION id）： 保证在特定服务器上的事务是完整的，如果执行不成功就回滚。 

7.高级应用架构： 

读写分离+负载均衡： 读直到从服务器上面读，写只在主服务器上面写。 需要在读写分离器之前加memocached

amoeba, mysql proxy

8.异地同步: 主要避免自然灾害

四、 简单主从构架配置过程

1.配置需留意:

1)版本注意：主从版本一致，或者主版本高于从版本

2)从哪儿开始复制：

1.都从0开始：

2.主服务器已经运行一段时间，并且存在不小的数据集：

把主服务器备份，然后在从服务恢复，从主服务器上备份时所处的位置开始复制；

应用案例(1):配置主从复制简单架构

Master服务器配置如下:

--停止mysql服务
# service mysqld stop  
--创建mysql服务日志目录及权限授予                           
# mkdir -pv /mydata/binlogs/
# chown -R mysql.mysql /mydata/binlogs/
--修改my.cnf配置文件:
[mysqld]
server-id       = 1
log-bin=/mydata/binlogs/master-bin
--启动mysql服务
# service mysqld start
--查询二进制日志参数是否启动
mysql > SHOW GLOBAL VARIABLES LIKE 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
+---------------------------------+-------+
--创建拥有复制权限的用户
mysql > GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass';
mysql > FLUSH PRIVILEGES;

Slave服务器配置如下:

--停止mysql服务
# service mysqld stop  
--创建mysql服务日志目录及权限授予                           
# mkdir -pv /mydata/relaylogs/
# chown -R mysql.mysql /mydata/relaylogs/
--修改my.cnf配置文件:
[mysqld]
server-id       = 11
relay-log       = /mydata/relaylogs/relay-bin
#log-bin=OFF
--启动mysql服务
# service mysqld start
--查询二进制中继日志参数是否启动
> SHOW GLOBAL VARIABLES LIKE 'relay%';
+-----------------------+-----------------------------+
| Variable_name         | Value                       |
+-----------------------+-----------------------------+
| relay_log             | /mydata/relaylogs/relay-bin |
| relay_log_index       |                             |
| relay_log_info_file   | relay-log.info              |
| relay_log_purge       | ON                          |
| relay_log_recovery    | OFF                         |
| relay_log_space_limit | 0                           |
+-----------------------+-----------------------------+

Slave服务器连接主服务器:

mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass';
命令:	CHANGE MASTER TO            
	MASTER_HOST = '',  主服务器地址
	MASTER_USER='', 使用哪个用户连接
	MASTER_PASSWORD='',  密码
	MASTER_LOG_FILE='',  用来复制特定的哪一个二进制文件
	MASTER_LOG_POS=;从这个二进制文件的哪个位置开始复制
最重要的参数只有前三个，就可以开始工作.

连接主服务器后，查询Master服务器线程及日志位置:

mysql > SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host      | db   | Command | Time | State | Info             | Progress |
+----+------+-----------+------+---------+------+-------+------------------+----------+
|  2 | root | localhost | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |    0.000 |
+----+------+-----------+------+---------+------+-------+------------------+----------+
--可以看到目前没有线程启动

mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      496 |              |                  |
+-------------------+----------+--------------+------------------+

连接主服务器后，查询Slave服务器线程状态及日志位置:

--查询Slave服务器线程状态及Slave复制日志状态
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: 
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: 
Slave_IO_Running: No
Slave_SQL_Running: No

--启动复制线程:
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 496
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 781
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
--查询线程服务启动信息，它会日志信息记录在错误信息日志文件中
--查询日志位置目录:
# ps aux | grep mysqld
--查询错误日志
# tail /mydata/data/slave.samlee.com.err 
160621 17:43:48 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='172.16.100.7', master_port='3306', master_log_file='', master_log_pos='4'.

根据以上信息查询可以清楚的知道Slave服务当前的状态及位置信息。

以上为Mysql主从复制简单应用部署过程。

主从复制架构应用测试(1):从0开始复制-->

步骤(1)：Master服务器上创建数据库写入操作

mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      496 |              |                  |
+-------------------+----------+--------------+------------------+ 
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      496 |              |                  |
+-------------------+----------+--------------+------------------+
mysql > CREATE DATABASE mydb;
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      579 |              |                  |
+-------------------+----------+--------------+------------------+

步骤(2)：Slave服务执行查询操作

--查询Slave服务器状态信息可以知道Slave服务器已经同步至579的位置
> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 579            --同步日志位置
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 864
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

步骤(3)：Master服务器上创建表插入数据写入操作

mysql > USE mydb;
mysql > CREATE TABLE t1(id int);
mysql > INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      864 |              |                  |
+-------------------+----------+--------------+------------------+

步骤(4)：Slave服务执行查询操作

--查询Slave服务器状态信息可以知道Slave服务器已经同步至864的位置
> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 864
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1149
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
--查询所示
mysql > USE mydb;
mysql > SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| t1             |
+----------------+
mysql > SELECT * FROM t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

主从复制架构应用测试(2):从指定位置开始复制

步骤(1)：测试准备操作:

Slave服务器准备:

mysql > STOP SLAVE;
mysql > DROP DATABASE mydb;

Master服务器准备操作:

mysql > DROP DATABASE mydb;

步骤(2):

为了演示效果，我们让Master服务器上直接产生大批的数据,然后让Slave服务器不从0开始复制。

Master服务器上操作:

--导入数据
# mysql < hellodb.sql 
MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |     8544 |              |                  |
+-------------------+----------+--------------+------------------+
mysql > DROP DATABASE mydb;
mysql > CREATE DATABASE mydb;
mysql > USE hellodb;
mysql > CREATE TABLE t1 (id int);
--锁定日志位置执行备份
# mysqldump --all-databases --flush-logs --master-data=2 --lock-all-tables > all.sql
--传送至Slave服务器
# scp all.sql root@172.16.100.8:/root

Slave服务器上操作:

--在停止Slave服务下恢复数据库
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: 
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 864
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1149
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: No
lave_SQL_Running: No
--恢复备份
# mysql < all.sql 
--查询主从起始位置信息，要去备份SQL中查询
# cat all.sql | grep 'master-bin'
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=245;

--指定从备份位置开始同步主从数据
mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_LOG_FILE='master-bin.000002',MASTER_LOG_POS=245;

--启动Slave复制线程
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 245
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Master服务器上操作:

--创建samleedb数据库
mysql > CREATE DATABASE samleedb;
--查询日志事件位置
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 |      336 |              |                  |
+-------------------+----------+--------------+------------------+

Slave服务器上操作:

--查询Slave服务状态信息
> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002        --可以查看到是从master-bin.000002日志文件同步
Read_Master_Log_Pos: 336              --日志事件位置为:336
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 621
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

简单主从复制实验过程的一些总结:
1)主服务器二进制日志如果在数据库初始化的时候开启了，则从服务器需要指定初始化结束后的日志进行复制。否则貌似会出现一些奇怪的错误。也许是因为无法再次创建系统库所致

2)主服务器最好在系统库初始化完成后再开启二进制日志。 这样从服务器就可以直接从第一个二进制日志开始复制。

3)如果错误日志中出现错误通常就是slave sql 线程，Slave I/O 产生的。 如果slave I/O出问题通常与权限和链接有关。 Slave sql出问题通常与二进制日志在从服务器执行有关。

1.在主从架构中跟复制相关的文件:
1)master.info: 用于保存了从服务器连接到主服务器需要的信息。每一行有一个值。 不同的mysql版本格式不一样。 如果不想链接，直接删了就好了
2)relay-log.info: 二进制日志和中继日志的位置， 日志坐标， 会不停的自动更新。由于更新不会马上执行，会存在缓冲区，如果断电，信息会丢失   

为了复制安全性，需要在从服务器上，需要在从服务器上开启以下参数:

sync_master_info  =1     
sync_relay_log    =1   
sync_relay_log_info =1

3)从服务器意外崩溃时，建议使用pt-slave-start命令来启动slave;
2. 基于行和基于语句的复制：
    1)基于语句：
      优点:数据量小，易于查看和识别，适应性强
      缺点:有些语句无法精确复制，使用触发器存储过程等代码的应用实现精确复制。
     
    2)基于行复制：
      优点:都能精确完成复制，包括出发器存储过程，能完成几乎所有的复制功能， 较少占用CPU资源，可以减少锁的使用
      缺点:通过日志无法判断执行了哪些语句， 数据可能略大。
     
    3)单独使用基于语句的场景几乎不存在，默认为混合模式。

3.从服务器落后于主服务器如何检测:

mysql> SHOW SLAVE STATUS \G; --可以查看     
Seconds_Behind_Master: 0
这里显示落后主服务器多少秒。 
如果主服务器繁忙，这个值通常都为正值， 如果此值在波动而不是变大。都可以接受。

4.评估主从服务器表数据是否一致：
   procona-toolkit里面的一些工具可以进行监控
   pt-table-checksum  在主服务器上面运行，可以自动寻找哪个表不同步

不同步的解决方法：
1.重新备份，并在从服务器上导入数据。
2.使用pt-table-sync完成同步
pt-summary 可以得到一些mysql的统计数据，有些时候有用

5.为了提高复制时的数据安全性，在主服务器上的设定：

sync_binlog = 1    
innodb_flush_log_at_trx_commit = 1
此参数的值设定为1，性能下降会较严重；因此，一般设定为2等，此时，主服务器崩溃依然有可能导致从服务器无法获取到全部的二进制日志事件；
如果主服务器意外崩溃，有二进制日志中事件没复制完损坏，可以在从服务器使用如下参数忽略： 

sql_slave_skip_counter = 0
数据目录磁盘空间不足，也可导致复制无法进行。需要结合监控工具做整体评估。 
主服务器修改过大，使得带宽不够用。

五.MySQL简单主从复制应用扩展

1.主从服务器需要保证时间同步，可以使用ntp服务

# crontab -e
*/5 * * * * /usr/sbin/ntpdate 172.16.100.9

2.如何限制从服务器只读?

read-only = ON --定义在从服务器my.cnf的[mysqld]段

扩展应用实现:只让从服务器实现只读状态

步骤(1)：

Slave服务器上操作:

# vim /etc/my.cnf
[mysqld]
read-only       = ON
# service mysqld start
mysql > SHOW GLOBAL VARIABLES LIKE '%read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+

Master服务器上操作:

--创建授权测试用户
mysql > GRANT ALL ON mydb.* TO 'testuser'@'172.16.%.%' IDENTIFIED BY 'testpass';

步骤(2)：

Slave服务器上操作:

--查询同步后测试用户
mysql > USE mysql
mysql > SELECT User,Host FROM user;
+----------+-------------------+
| User     | Host              |
+----------+-------------------+
| root     | 127.0.0.1         |
| repluser | 172.16.%.%        |
| testuser | 172.16.%.%        |
| root     | ::1               |
|          | localhost         |
| root     | localhost         |
|          | master.samlee.com |
| root     | master.samlee.com |
+----------+-------------------+

Master服务器上操作:

--远程连接Slave服务器测试只读状态是否启用
# mysql -utestuser -h272.16.100.8 -ptestpass
mysql > USE mydb;
mysql > SHOW TABLES;
Empty set (0.01 sec)
mysql > CREATE TABLE t1(id int);
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
##MariaDB的服务器运行的，只读选项不能执行该语句

Tips:仅能限制那不具有SUPER权限用户无法执行写操作；因此管理员依然有效，切勿使用管理员权限在从服务器上修改数据。

想限制所有用户:

mysql> FLUSH TABLES WITH READ LOCK;

3.如何主从复制时的事务安全
当主服务器事务，尚未同步到二进制日志中时， 如果主服务器宕机，则从服务器无法获取完整的事务，当从服务器指向其它主服务器时候，有可能导致事务回滚。从而使得事务丢失。
在主服务器上配置一下参数解决， 只要事务提交，则立即将事务从二进制缓冲区同步到二进制日志中。

sync_binlog=1
innodb_flush_log_at_trx_commit=1

Tips:此变量设置后，会产生一些性能损耗，主要原因是MyISAM或者Innodb是默认自动提交事务的。为了提高性能可以关掉自动提交，同时开启以上选项用。

全同步过程:主服务器写操作 –> 写操作同步到二进制日志 –> 写操作被复制到从服务器的二进制日志中 –>才从服务器上执行写操作 –> 然后返回给主服务器

4.使用半同步复制（semi-synchronously）机制可以在主服务器性能一定损失的前提下实现更好地同步.
前面部分与全同步相同，只是在对于一主多从的场景中，主服务器只等待一个最快的从服务器范围写完成状态。
半同步的配置:mysql5.5以后google提供，在程序安装lib/plugin目录下的两个插件semisync_master.so, semisync_slave.so。

扩展应用实现:实现半同步复制配置及应用

Master服务器配置操作:

--装载semisync_master.so模块
mysql > INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
--装载后，将会多出几个选项:
> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
rpl_semi_sync_master_enabled      ：控制是否开启半同步
rpl_semi_sync_master_timeout      ：半同步超时时长，多长时间半同步从服务器回复，跳过半同步继续执行。单位为毫秒，默认为10秒钟
rpl_semi_sync_master_wait_no_slave：如果半同步服务器不回应，则跳过半同步服务器继续执行。

--修改前两个参数:
mysql > SET GLOBAL rpl_semi_sync_master_enabled='ON'; 
mysql > SET GLOBAL rpl_semi_sync_master_timeout=2000;

--配置完成如下:
mysql > SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 2000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

Slave服务器配置操作:

--装载semisync_slave.so模块
mysql > INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
--装载后，会出现以下参数选项:
mysql > SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
--从服务器这里只需要把第一个开启就可以了：
mysql > SET GLOBAL rpl_semi_sync_slave_enabled=1;
mysql > SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
--如果不重启I/O 线程，则半同步从服务器特性不能被主服务器识别，此时主服务器上进行操作，会超时，然后跳过半同步。
mysql > STOP SLAVE IO_THREAD;
mysql > START SLAVE IO_THREAD;

Master服务器配置操作:执行数据写入操作

mysql > USE mydb;
Database changed
mysql > CREATE TABLE t2(name char(20));
Query OK, 0 rows affected (0.09 sec)

mysql > CREATE TABLE t3(name char(20));
Query OK, 0 rows affected (0.02 sec)

mysql > CREATE TABLE t4(name char(20));
Query OK, 0 rows affected (0.01 sec)

--在主服务器验正半同步复制是否生效：
mysql > SHOW GLOBAL STATUS LIKE '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
这里显示，半同步客户端有一个，说明半同步正常使用。 
如果由于网络或其他原因，检测到半同步超时，则可以重启从服务器的1/O线程，来重启半同步功能。

5.在主从复制架构中，仅复制一部分数据解决方案，使用复制过滤器实现

主服务器过滤： 主服务器仅往二进制日志中记录像样的服务器
优缺点：主服务器磁盘I/O 带宽节约
缺点：但是其他数据库数据不安全，无法完成时间点还原

参数： 
binlog_do_db= 
binlog_igore_db=

在从服务器上，仅读取相应的信息
基于库:

replicate_do_db=        
replicate_ignore_db=

基于表

replicate_do_table=db_name.tb_name 
replicate_ignore_table=

基于表使用通配符

replicate_wild_do_table=
replicate_wild_ignore_table=

6.配置多主模型

MasterA服务器上配置:

# service mysqld stop
# vim /etc/my.cnf
log-bin=/mydata/binlogs/master-bin
relay-log = /mydata/relaylogs/relay-bin
auto_increment_offset = 1        #起始位置
auto_increment_increment = 2     #自动增长步长   
server-id       = 1
skip_slave_start
--创建二进制日志及中继日志存储目录及权限授予
# mkdir -pv /mydata/{binlogs,relaylogs}
# chown -R mysql.mysql /mydata/{binlogs,relaylogs}
#  service mysqld start
--创建拥有复制权限的用户
> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass'; affected (0.01 sec)
> FLUSH PRIVILEGES;
--查询当前服务器二进制文件及事件位置
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000004 |      506 |              |                  |
+-------------------+----------+--------------+------------------+

MasterB服务器上配置:

# service mysqld stop
# vim /etc/my.cnf
server-id       = 11
log-bin=/mydata/binlogs/master-log
relay-log       = /mydata/relaylogs/relay-bin
auto_increment_increment = 2
auto_increment_offset = 2
skip_slave_start
--创建二进制日志及中继日志存储目录及权限授予
# mkdir -pv /mydata/{binlogs,relaylogs}
# chown -R mysql.mysql /mydata/{binlogs,relaylogs}
#  service mysqld start
--创建拥有复制权限的用户
> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'172.16.%.%' IDENTIFIED BY 'replpass'; affected (0.01 sec)
> FLUSH PRIVILEGES;
--查询当前服务器二进制文件及事件位置
mysql > SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000001 |      506 |              |                  |
+-------------------+----------+--------------+------------------+

MasterB服务器连接MasterA服务器配置操作:

mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.7',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=506;
mysql > SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master-log.000001
Position: 506
Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.7
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 684
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 708
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

MasterA服务器连接MasterB服务器配置操作:

mysql > CHANGE MASTER TO MASTER_HOST='172.16.100.8',MASTER_USER='repluser',MASTER_PASSWORD='replpass',MASTER_LOG_FILE='master-log.000001',MASTER_LOG_POS=506;
mysql > SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: master-bin.000004
Position: 506
Binlog_Do_DB: 
Binlog_Ignore_DB: 
mysql > START SLAVE;
mysql > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.100.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-log.000001
Read_Master_Log_Pos: 506
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 530
Relay_Master_Log_File: master-log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试(1):

MasterA服务器操作:

mysql > CREATE DATABASE newdb;

MasterB服务器操作:

mysql > USE newdb;
mysql > CREATE TABLE t1(id int unsigned not null primary key auto_increment,name char(20));

测试(2):

MasterA服务器操作:

mysql > USE newdb;
mysql > SHOW TABLES;
+-----------------+
| Tables_in_newdb |
+-----------------+
| t1              |
+-----------------+

MasterB服务器操作:

mysql > USE newdb;
mysql > SHOW TABLES;
+-----------------+
| Tables_in_newdb |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)

mysql > INSERT INTO t1 (name) values ('jerry'),('tom'),('samlee');
mysql > SELECT * FROM t1;
+----+--------+
| id | name   |
+----+--------+
|  2 | jerry  |
|  4 | tom    |
|  6 | samlee |
+----+--------+

MasterA服务器操作:

mysql > INSERT INTO t1 (name) values ('samlee1'),('samlee2'),('samlee3');
mysql > SELECT * FROM t1;
+----+---------+
| id | name    |
+----+---------+
|  2 | jerry   |
|  4 | tom     |
|  6 | samlee  |
|  7 | samlee1 |
|  9 | samlee2 |
| 11 | samlee3 |
+----+---------+

通过测试我们看到多主模型主要是通过锁定步长值方式进行实现的。

多主模型，且高可用的解决方案：
MMM：Multi Master MySQL----请继续关注我的博客http://gzsamlee.blog.51cto.com/MHA：MySQL HA----请继续关注我的博客http://gzsamlee.blog.51cto.com/

7.基于SSL实现主从复制:

请继续关注我的博客http://gzsamlee.blog.51cto.com

当前标题：关系型数据库之主从复制应用
浏览地址：http://bzwzjz.com/article/gshegp.html