关系型数据库－MySQL 进阶（一）主从同步

一　Mysql 主从同步　：配置（主）授权　配置（从）指定主　客户端测试
二　构建主从同步　互为主从

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊********************
主从mysql 复制优势
１　高性能
２　数据安全
３　远程数据分享
（2）mysql复制解决的问题

 a.数据分布（data distribution）

 b.负载平衡（load balancing）

 c.数据备份（backup），保证数据安全

 d.高可用性与容错行（high availability and failover）

 e.实现读写分离，缓解数据库压力

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
主从同步
Master : binlog
Slave : _i/o将master　binlog里 sql命令复制到relay-log (中继日志)文件里
_sql 执行读取relay-log(中继日志) 实现与master 一致

mysql主从复制原理
master服务器将数据的改变会记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，

如果发生改变，则开始一个I/O Thread请求master二进制事件（binlog events），同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒。

复制常用架构
Mysql 复制环境 90%以上都是一个 Master 带一个或者多个 Slave 的架构模式,主要用于缓解压力
比较大的应用的数据库端廉价扩展解决方案。因为只要 master 和 slave 的压力不是太大(尤其
是 slave 端压力)的话,异步复制的延时一般都很少很少。尤其是自 slave 端的复制方式改成
两个进程处理之后,更是减小了 slave 端的延时。而带来的效益是,对于数据实时性要求不是
特别的敏感度的应用,只需要通过廉价的 pc server 来扩展 slave 的数量,将读压力分散到多
台 slave 的机器上面,即可解决数据库端的读压力瓶颈。这在很大程度上解决了目前很多中小
型网站的数据库压力瓶颈问题,甚至有些大型网站也在使用类似方案解决数据库瓶颈。

构建主从
配置主库：启用binlog配置文件　＼　授权用户＼查看binlog日志
配置从服务器：　确保和主数据一致
配置文件Server_id＼ 指定主库（change）　启动slave

一其中192.168.4.51是主服务器,另一台192.168.4.52作为从服务器，通过调取主服务器上的binlog日志，在本地重做对应的库、表，实现与主服务器的数据同步

配置主服务器　　５１
１　配置主配置文件
启用binlog日志，并重启
Vim /etc/my.cnf
Log_bin=master51　　
Server_id=51
重启;Systemctl

2 授权用户
Mysql > grant replication slave on . to repluster@”%”
Identifeid by “123456”

replication
(绘画等的)复制;拷贝;重复(实验);(尤指对答辩的)回答

3查看日志
Mysql >Show master status;

二
配置从服务器　５２
51 备份数据
(1)mysqldump -uroot -p123456 --master-data db5 > /root/db5.sql

–master-data 对应备份数据偏移量（position）
恢复数据：　mysqldump -uroot -p123456 db5 < /root/db5.sql
chakan (2)vim /root/db5.sql
Master51.00001 -441
mysqlbinlog --start-position=2232 --stop-position=3656 /mylog/db50.000002 | mysql -uroot -p123456
52
Vim /root/db5.sql /在从服务器查看备份文件中的binlog日志信息
CHANGE MASTER TO MASTER_LOG_FILE=‘master51.000001’, MASTER_LOG_POS=1199;

Ls
（3）Scp /root/db5.sql root @192.168.4.52；/root

52 操作
(1)配置文件
Vim /etc/my.cnf
Server_id=52

(2) mysql -uroot -p123456 　 -e “create database db5”
(3) mysql -uroot -p123456 db5 < /root/db5.sql
(4) mysql -uroot -p123456 -e “select * from db5.a”

显示是否为从服务器
Show slave status 　 -查看slave 状态

grep master51 /root/db5.sql
CHANGE MASTER TO MASTER_LOG_FILE=‘master51.000001’, MASTER_LOG_POS=1199;

(5)指定主服务器：Change master to master_host=”192.168.4.51” , master_user=”repluster” ,master_password=”123456” ,master_log_file=”master51.000001” , master_log_pos=11211;

(6)Start slave; -开启slave
(7)Show slave status＼G; -查看slave状态

Binlog 相关文件
-bin.index
-bin.000001

Cat master.info
Cat reloy-log.info
cat mysql52-relay-bin 000002
Cat mysql52-relay-bin.index

拍错：
[52]Cd /var/lib/mysql
[52]Rm -rf master.info relay-bin relay-log.onfo
[52]Systemctl start mysql
51 mysql > show slave status;

52
Stop slave;
Mysql>Change master
Start slave
Show slave status;

克隆的虚拟机
：cat /var/lib/mysql/auto.cnf

三
测试客户端：　4.50
4.51主服务器添加客户端用户
Grant all on . to yaya99@”%”
Identified by “123456”

Mysql -h192.168.4.51 -uroot -p123456
mysql> insert into db3.user(name,uid) values(“lili”,288);

客户端50主机使用授权用户连接从服务器可以看到和主服务器同样的数据
]# mysql -h192.168.4.52 –uadmin -p123456
mysql> select name,uid from db3.user where name=“lili”;
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
主从
一主多从
主从从
主从同步复制
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＆＊＊＊＊＊
主丛丛:
１
(53)配置Vim
启用bin-log 日志
　
重启Systemctl

(51)备份数据
Mysqldump -uroot -p123456 --master-data db5 > /root/db5.sql
Scp -r 4.53
这样就可以保留 file 和 position 的信息，在新搭建一个slave的时候，还原完数据库， file 和 position 的信息也随之更新，接着再start slave 就可以很迅速
的完成增量同步！

(53) 建库 mysql -uroot -p123456 -e “create database db5”
恢复数据 mysql -uroot -p123456 db5 < /root/db5.sql
指定主库 mysql > change master to master_host=”192.168.4.51” ,
Master_user=”repluster” , master_password=”123456” , master
启动slave
查看show slave status;

---

主从从结构

恢复５3
１systemctl stop mysqld
2 cd /var/lib/mysql/
3 rm -rf relay-log.info master.info relay-bin
4 vim /etc/my.cnf
5 systemctl
6 mysql > -uroot -=p123456
Drop database db5;

配置５３主服务器

１配置文件
２授权grant rplicastion slave
３查看master show master status;

配置５４主＼从服务器
1 查看　
Mysql > show databases;
Show master status;
Show slave status’
2 配置文件　　（５４即是主又是从）
Server_id=54
Log-bin=db54
Log_slave_updates

3 重启systemctl
Ls /var/lib/mysql/bd54*
4 mysql -uroot -p12346
授权Grant replication slave on . to repluster@”%” identified by “13456”
查看Show master status ;

Show
指定主服务器Change master to master_host=”192.16.4.53”
启动Start slave;
瞎看Show slave status\G;

配置５５从服务器
１配置文件
２　重启
３　指定主服务器
change master to master_host=“192.168.4.54” ,master_user=“repluseter” , master_password=“123456” ,master_log_file=“db54.000001” ,master_log_pos=880;
４　查看slave
Start slave
Show slave status\G;

客户端验证配置

在主库授权访问数据的连接用户;户端连接主库执行与权限匹配的sql操作;
授权用户连接第1台从库，可以看到主库的数据;权用户连接第2台从库，可以看到主库的数据

---

复制模式
１异步（默认）：主库完成一次事物后．立刻将结果返回给客户端＼不关心从库是否接受和处理

复制模式都有哪些?
异步复制( Asynchronous replication ) – 主库在执行完客户端提交的事务后会立即将结果返给 客户端,并不关心从库是否已经接收并处理。

全同步复制( Fully synchronous replication ) – 当主库执行完一个事务,所有的从库都
执行了该事务 才返回给客户端。

半同步复制( Semisynchronous replication ) – 介于异步复制和全同步复制之间,主库在
执行完客户 端提交的事务后不是立刻返回给客户端,而是等待至 少一个从库接收到并写到
relay log 中才返回给客户端

配置半同步复制模式
命令行配置模式立即生效

(1)查看是否允许动态加载模块
show variables like “have_dynamic_loading”
(2)命令行安装模块
Install plugin rpl_semi_sync_master soname “semisync_master.so”;

(3)查看模块安装状态
Mysql> Select plugin_name plugin_status from information_schema.plugins where plugin_name like “%semi%”;
(4)启用半同步
Set global rpl_semi_sync_master_enabled=1;
Set global rpl_semi_sync_slave_enabled=1;
(5)永久启用两模块
Vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so
Plugin-load=rpl_semi_sync_slave=semisync_slave.so

“”
(6)同时启用
Plugin-load=”rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so”
Rpl_semi_sync_master_enabled=1
Rpl_semi_sync_slave_enabled=1

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
总结:

主从数据完成同步的过程：

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中记录当前应用中继日志的文件名和位置点

需要注意：

1）主从复制是异步的逻辑的SQL语句级的复制

2）复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程

3）实现主从复制的必要条件是主库要开启记录binlog功能

4）作为复制的所有Mysql节点的server-id都不能相同

5）binlog文件只记录对数据库有更改的SQL语句（来自主库内容的变更），不记录任何查询（select，show）语句

…

彻底解除主从复制关系

1)stop slave;

2)reset slave; 或直接删除master.info和relay-log.info这两个文件；

3)修改my.cnf删除主从相关配置参数。

让slave不随MySQL自动启动

修改my.cnf 在[mysqld]中增加 skip-slave-start 选项。

position 的信息也随之更新，接着再start slave 就可以很迅速
的完成增量同步！

MySQL复制：

 MySQL内建的复制功能是构建大型高性能应用程序的基础。将MySQL的数据分布到多个系统上去，这种分布的机制，是通过将mysql的某一台主机的数据复制到其它主机（slave）上，并重新执行一遍来实现。

复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维护文件的一个索引以跟踪日志循坏，这些日志可以记录发送到从服务器的更新。当一个从服务器

连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知的更新。

需注意的是：

在进行mysql复制时，所有对复制中的表的更新必须在主服务器上进行。否则必须要小心，以避免用户对主服器上的表进行更新与对从服务器上的表所进行更新之间的冲突。