mysql主从复制

一、主从复制介绍

1.1 什么是主从复制

将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态，称之为主从复制。
一句话表示就是，主数据库做什么，从数据库就跟着做什么。

1.2 为何要做主从

（1）为实现服务器负载均衡/读写分离做铺垫，提升访问速度

#1、什么是读写分离 
有了主从保持数据一致作为大前提，我们便可以分离读写操作，其中Master负责
写操作的负载， 也就是说一切写的操作都在Master上进行，而读的操作则分摊到
Slave上进行。

那么读写分离的作用 是什么呢？ 
#2、读写分离的作用 
先说答案：读写分离可以大大提高读取的效率。 

在一般的互联网应用中，经过一些数据调查得出结论，读/写的比例大概在 10：1左右 ，也就是说写操作非常少，
大量的数据操作是集中在读的操作（如某些应用，像基金净值预测的网站。其数据的更新都是有管理员更新的，
即更新的用户比较少。而查询的用户数量会非常的多。） 

此时我们可以制作一主多从，因为写操作很少，所以由一个主库负责即可，而大量的读操作则分配给
多个从库，这样占据比例最大的读操作的压力就被负载均衡了，因此读效率会得到了极大的提升，另
外，还有一个原因就是： 熟悉DB的研发人员都知道，写操作涉及到锁的问题，不管是行锁还是表锁
还是块锁，都是比较降低系统执行效率的事情。我们这样的分离是把写操作集中在一个节点上，而读操
作其其他的N个节点上进行，这从另一个方面有效的提高了读的效率，保证了系统的性能及高可用性。 

#3、具体做法 
方案一： 
就是主库写，从库读 

方案二： 
主库负责写，还有部分读，从库只负责读，而且是读操作的主力 

即当主服务器比较忙时，部分查询请求会自动发送到从服务器中，以降低主服务器的工作负荷。

（2）通过复制实现数据的异地备份，保障数据安全

可以定期的将数据从主服务器上复制到从服务器上，这实现了数据的异地备份。 
在传统的备份体制下，是将数据备份在本地。此时备份作业与数据库服务器运行在同一台设备
上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。
同时，将备份数据存放在本地，也不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，
此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给
企业带来比较大的损失。

（3）提高数据库系统的可用性

数据库复制功能实现了主服务器与从服务器之间数据的同步，增加了数据库系统的可用性。主库宕机后，
从库尚可用，即当主服务器出现问题时，数据库管理员可以马上让从服务器作为主服务器，用来数据的
更新与查询服务。然后回过头来再仔细的检查主服务器的问题。此时一般数据库管理员会采用两种手段。
 
• 一：从库临时取代主库，只用来读 
主服务器故障之后，虽然从服务器取代了主服务器的位置，但是对于主服务器可以采取的操作仍然做了
一些限制,例如仍然只能够进行数据的查询，而不能够进行数据的更新、删除等操作。这主要是基于从
数据的安全性考虑。如现在一些银行系统的升级，在升级的过程中，只能够查询余额而不能够取钱。 

• 二：从库永久取代主库，负责读和写 
从服务器真正变成了主服务器。当从服务器切换为主服务器之后，其地位完全与原先的主服务器相同。
此时可以实现对数据的查询、更新、删除等操作。为此就需要做好数据的安全性工作。即数据的安全
策略，要与原先的主服务器完全相同。否则的话，就可能会留下一定的安全隐患

1.3 主从复制的原理

整体上来说，复制有3个步骤：
(1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）。
(2) slave的io线程将master的binary log events拷贝到它的中继日志(relay log)；
(3) slave的sql线程解析中继日志中的事件并在从库执行，保持与主库一致。

复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

# 从库准备 
（1）从库change master to 时，ip port user password binlog position写入到 master.info进行记录 
（2）从库 start slave 时，会启动IO线程和SQL线程 

# 同步的过程 
1.从库的IO线程，读取master.info信息，获取主库信息并连接主库 
2.主库接收从库的链接请求后，会生成一个准备binlog DUMP的线程，来响应从库 
3.主库一旦有新的日志生成，会发送“信号”给主库的binlog dump线程，然后binlog dump线程会 读取binlog日志的更新 
4.TP(传送)给从从库的IO线程 
5.IO线程将收到的日志存储到了TCP/IP 缓存 
6.写入TCP/IP缓存后，立即返回ACK给主库 ，此时主库工作完成 
7.IO线程更新master.info文件binlog 文件名和postion 
8.IO线程将缓存中的数据，存储到relay-log日志文件,此时io线程工作完成 
9.从库SQL线程读取relay-log.info文件，获取到上次执行到的relay-log的位置，作为起点 
10.从库SQL线程基于从步骤9中获取到的起点，去中继日志relay-log.000001获取后续操作，在从库回放relay-log 
11.SQL线程回放完成之后，会更新relay-log.info文件，把当前操作的位置记入，作为下一次操作的起点。 
12. relay-log会有自动清理的功能。

1.4 主从复制的日志格式

这里的日志格式就是指二进制日志的三种格式
binlog_format=statement
binlog_format=row
binlog_format=mixed
其中基于row的复制方式更能保证主从库数据的一致性，但日志量较大，在设置时考虑磁盘的空间问题

1.5 主从复制的方式

MySQL的主从复制有两种复制方式，分别是异步复制和半同步复制

二 异步复制

2.1 异步复制原理介绍

我们之前介绍的就是异步复制，即客户端线程提交一个写操作，写入主库的binlog日志后就立即返回，并不需要等待从库完成同步操作，而主库的dump线程会监测binlog日志的变量然后主动将更新推送给从库。
MySQL 主从复制默认是异步的模式。

2.2 异步复制部署

前提：主从数据库版本一致(此处为5.7)

2.2.1 主库停服时制作主从

一、主库：192.168.1.51
1）在主库上创建一个用于复制的账号，并赋予replication slave权限，这里必须 . 不能指定库授权，因为 replication slave 是全局的

mysql> grant replication slave on *.* to 'yzl'@'%' identified by 'Oldboy@666'; 
mysql> flush privileges;

2. 修改主库配置文件，开启主库的Binlog，并设置server-id

[mysqld] 
# 节点ID，确保唯一 
server-id = 1 

#开启mysql的binlog日志功能 
log-bin = mysql-bin
#控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中， 性能最差，最安全 
sync_binlog = 1 
#binlog日志格式
binlog_format = row 
#binlog过期清理时间 
expire_logs_days = 7 
#binlog每个日志文件大小 
max_binlog_size = 100m 
#binlog缓存大小 
binlog_cache_size = 4m 
#最大binlog缓存大小 
max_binlog_cache_size= 512m 

#不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制黏贴下述配置项，写多行 binlog-ignore-db=mysql 

# 表中自增字段每次的偏移量 
auto-increment-offset = 1 
# 表中自增字段每次的自增量 
auto-increment-increment = 1 
#跳过从库错误 
slave-skip-errors = all

3）重启主库

systemctl restart mysql

4）备份主库，备份时锁表保证备份一致

mysqldump -uroot -pOldboy@666 -A -E -R --triggers --master-data=2 -- single-transaction > /tmp/all.sql

5）将备份文件发送给从库

scp /tmp/all.sql root@192.168.15.100:/tmp

二、从库：192.168.1.52
1）测试复制账号

mysql -uyzl -pOldboy@666 -h 192.168.1.51

2）导入初始数据

mysql -uroot -pOldboy@666 < /tmp/all.sql

3）修改从库配置文件，增加server-id，注意这个值是唯一的，要区别于主库和其他从库

[mysqld] 
server-id = 2 

relay-log = mysql-relay-bin 
replicate-wild-ignore-table=mysql.% 
replicate-wild-ignore-table=test.% 
replicate-wild-ignore-table=information_schema.% 

# 从库也可以开启binlog，但通常关闭 
# log-bin=mysql-bin

4）重启从库

systemctl restart mysqld

5）配置复制
先去主库查看一下binlog日志名与位置
mysql> show master status;

然后在从库进行配置

[root@slave1 ~]# mysql -uroot -pOldboy@666 # 登录然后执行 

change master to 
master_host='192.168.1.51', -- 库服务器的IP 
master_port=3306, -- 主库端口 
master_user='yzl', -- 主库用于复制的用户 
master_password='Oldboy@666', -- 密码 
master_log_file='mysql-bin.000001', -- 主库日志名 
master_log_pos=