MySQL高可用主从复制

hudeyong926

浏览: 2033336 次
来自: 武汉

最近访客更多访客>>

loginboot

xiaoshiyier

lncyfug

gaoshaoye

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

架构
面试

MySQL Linux SQL Server Windows 防火墙

mysql主从复制灵活

一主一从
主主复制
一主多从---扩展系统读取的性能，因为读是在从库读取的；
多主一从---5.7开始支持
联级复制---

用途及条件

mysql主从复制用途

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从部署必要条件：

主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连通主库

主从原理

MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个Mysql数据库（我们称之为Master）复制到另一个Mysql数据库（我们称之为Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和IO线程）在Slave端，另一个线程（I/O线程）在Master端。

要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后再在Slave上以相同顺序执行获取的binlog日志中的记录的各种SQL操作

1）在Slave 服务器上执行sart slave命令开启主从复制开关，开始进行主从复制。

2）此时，Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器，并请求从执行binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容

3）Master服务器接收到来自Slave服务器的IO线程的请求后，其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外，还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。

4）当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（Mysql-relay-bin.xxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容

5）Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这样sql语句，并在relay-log.info中记录当前应用中继日志的文件名和位置点

生产场景下轻松部署MySQL主从复制

快速步骤MySQL主从复制
1）安装好配置从库的数据库，配置好log-bin和server-id参数

[mysqld]
#主库配置
log_bin  = /var/log/mariadb/mysql-bin
server-id  = 1

2）无需配置主库my.cnf，主库的log-bin和server-id参数默认就是配置好的。

[mysqld]
#从库配置
server-id = 2
relay_log = relay-log
relay_log_index = relay-log.index
read_only = 1

3）登录主库，增加从库连接同步的账户，例如：rep，并授权replication同步的权限

mysql>grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
mysql> flush privileges;

4）使用mysqldump命令带-x和–master-data=2（锁表）的命令及参数全备数据，把它恢复到从库。备份前查看下二进制的版本号，备份完成后再比对是否一致。

主库>mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -F -R -x --master-data=1 -A --events|gzip >/server/backup/rep3307_(date +%F).sql.gz
从库>mysql -uroot -p123456 -S /data/3307/mysql.sock <./repo3307_2016-07-03.sql

5）从库执行CHANGE MASTER TO….语句，需要binlog文件及对应点（因为–master-data=2已经带了）

mysql>CHANGE MASTER TO
    MASTER_HOST='www.abcdocker.com',
    MASTER_PORT=3306,
    MASTER_USER='rep',
    MASTER_PASSWORD='123456';

6）从库开启同步开关，start slave
7）从库show slave status\G，检查同步状态，并在主库更新测试

mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...

确认 Slave_IO_Running 和 Slave_SQL_Running 两个参数都为 Yes 状态。

问题及解决方法

mysql主从复制存在的问题：

主库宕机后，数据可能丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

半同步复制---解决数据丢失的问题
并行复制----解决从库复制延迟的问题MySQL 5.6中SQL线程支持多个,设置参数slave_parallel_workers=4

半同步复制

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写relay log中才返回给客户端。如果从库超过10秒没有来获取binlog日志。主库自动转换为异步。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

MySQL常用高可用方案
mysql+Heartbeat+DRBD高可用场景

基于主从复制的高可用MHA/MMM方案
基于Galera协议的高可用方案PXC：强一致性、无同步延迟

小结：
主从复制是异步的逻辑的SQL语句级的复制复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程
实现主从复制的必要条件是主库要开启记录binlog功能.作为复制的所有Mysql节点的server-id都不能相同
binlog文件只记录对数据库有更改的SQL语句（来自主库内容的变更），不记录任何查询（select，show）语句

一个主库的从库太多，导致复制延迟。建议从库数量3-5 为宜，要复制的从节点数量过多，会导致复制延迟

mysql中有自增长字段，在做数据库的主主同步时需要设置自增长的两个相关配置：auto_increment_offset和auto_increment_increment。

auto_increment_offset表示自增长字段从那个数开始，他的取值范围是1 .. 65535
auto_increment_increment表示自增长字段每次递增的量，其默认值是1，取值范围是1 .. 65535

在主主同步配置时，需要将两台服务器的auto_increment_increment增长量都配置为2，而要把auto_increment_offset分别配置为1和2.这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突

[root@localhost][(none)]> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
rows in set (0.00 sec)

auto_increment_increment：自增值。auto_increment_offset：漂移值,也就是步长
由于auto_increment_increment 属于全局可变的变量，故此可以通过修改自增值来达到测试目的

[root@localhost][(none)]> create table boss.autoinc1(col int not null auto_increment primary key);
Query OK, 0 rows affected (1.03 sec)

[root@localhost][(none)]> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)

[root@localhost][(none)]> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+
rows in set (0.00 sec)

从上面可以看到，自增从10开始，那么此时插入数据会是什么结果？

[root@localhost][(none)]> insert into boss.autoinc1 values(null),(null),(null),(null);
Query OK, 4 rows affected (0.29 sec)
Records: 4  Duplicates: 0  Warnings: 0

[root@localhost][(none)]> select col from boss.autoinc1;
+-----+
| col |
+-----+
|   1 |
|  11 |
|  21 |
|  31 |
+-----+
rows in set (0.00 sec)