MYSQL---主从同步概述与配置

观云阁

一、MYSQL主从同步概述

1、什么是MySQL主从同步？

实现数据自动同步的服务结构

1. 主服务器（master）: 接受客户端访问连接
2. 从服务器（slave)：自动同步主服务器数据

复制代码

2、主从同步原理

Maste：启用binlog 日志
Slave：Slave_IO: 复制master主机binlog 日志文件的SQL命令到本机的relay-log(中继日志) 文件里。
Slave_SQL: 执行本机 relay-log(中继日志) 文件里的SQL语句，实现与 Master 数据一致。

Master(主服务器)：

1》开启binlog日志，记录所有除查询以外的SQL命令
Slave(从服务器):

1》从服务器上的I/O thread(读写线程) 负责读取主服务器binlog日志中的SQL命令，并将其写入到 Relay log(中继日志中)
2》从服务器中的SQL thread(SQL 线程)读取中继日志中的SQL命令，并将其写入到Slave的数据库中
3、主从同步结构模式


基本应用
单向复制：一主    grant replication slave on *.* to repluser@"%" identified by "123qqq...A";mysql> show master status;[/code]步骤四：host52配置从服务器

1. [root@host51 ~]# mysql -uroot -p123qqq...A
2. mysql> drop database bbsdb;
3. [root@host52 ~]# mysql -uroot -p123qqq...A
4. mysql> drop database db4;

复制代码

步骤五：启动从服务器

1. # 修改主配置文件，开启binlog日志
2. [root@host51 ~]# vim /etc/my.cnf
3. [mysqld]
4. #binlog_format="mixed"        # 加上注释
5. server_id=50                                #指定id号，默认与IP地址的主机位相同
6. log_bin=master51                        #指定binlog日志名，日志文件在/var/lib/mysql下     

复制代码

如果IO线程为NO，错误提示如下

这是因，auto.cnf 存放的是主服务器的uuid号 ，因为是克隆出的虚拟机，uuid号都一样，会进行冲突，可以任意修改其中一个或多个字符，重启数据库服务即可

1. [root@host51 ~]# systemctl restart mysqld
2. # 用户授权，给replication slave复制数据的权限，授权用户为: repluser
3. [root@host51 ~]# mysql -uroot -p123qqq...A
4. mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
5. mysql> show master status;

复制代码

步骤六：host51：测试主从同步

1. # 修改主配置文件，指定server_id号
2. [root@host52 ~]# vim /etc/my.cnf
3. [mysqld]
4. server_id=52          #指定id号，默认与IP地址的主机位相同
5. [root@host52 ~]# systemctl restart mysqld
6. # 指定主服务器信息
7. mysql> change master to master_host="192.168.4.51", master_user="repluser", master_password="123qqq...A", master_log_file="master51.000001", master_log_pos=441;

复制代码

步骤七：在host52上查看

1. mysql> start slave;               
2. mysql> show slave status\G;                #确认IO线程、SQL线程都是 Yes 状态

复制代码

相关文件

存放在数据库目录下/var/lib/mysql/
删除文件，重启数据库服务，可把主机恢复为独立的数据库服务器；

记录主服务器的信息

1. [root@host52 ~]# vim /var/lib/mysql/auto.cnf
2. [auto]
3. server-uuid=d45877cc-f4ab-11ea-b2a1-000c291f7055
4. [root@host52 ~]# systemctl restart mysqld

复制代码

查看中继日志信息

1. [root@host51 ~]# mysql -uroot -p'123qqq...A'
2. mysql> create database db1;
3. mysql> create table db1.user (name char(10));
4. mysql> insert into db1.user values("tom");

复制代码

6、配置MySQL一主多从：解决从服务器宕机之后，无法备份数据问题

拓扑结构

步骤一：准备数据库host53，可以直接用之前的host53，删除创建数据库，保留默认的四个库

1. [root@host52 ~]# mysql -uroot -p'123qqq...A'
2. mysql> show databases;
3. mysql> select * from db1.user;

复制代码

步骤二：没有配置之前，要确保从与主服务器数据一致

host51：安装innobackupex相关软件包

1. [root@host52 ~]# cat /var/lib/mysql/master.info

复制代码

host53使用innobackupex恢复数据

1. # host52-relay-bin.000001 是中继日志文件：记录从主服务器拷贝过来的sql命令
2. # host52-relay-bin.index  是中继日志索引文件
3. [root@host52 ~]# ls /var/lib/mysql/host52*
4. # 查看中继日志文件内容
5. [root@host52 ~]# cd /var/lib/mysql
6. [root@host52 mysql]# cat relay-log.info
7. 7
8. ./host52-relay-bin.000004                #本机正在使用的中继日志文件
9. 319                                                        #中继日志记录主服务器sql命令的偏移量
10. master51.000001                                #中继日志从哪个文件中拷贝sql命令(主服务器)
11. 441                         #此为主服务器最近的binlog日志的偏移量
12. # 查看中继日志索引文件，有几个中继日志文件，就记录几条
13. [root@host52 ~]# cd /var/lib/mysql
14. [root@host52 mysql]# cat host52-relay-bin.index

复制代码

1. [root@host53 ~]# mysql -uroot -p123qqq...A
2. mysql> show databases;
3. mysql> drop database db4;

复制代码

步骤三：配置从服务器

1. [root@host52 ~]# scp libev-4.15-1.el6.rf.x86_64.rpm  percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm 192.168.4.51:/root/
2. [root@host51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm
3. [root@host51 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
4. [root@host51 ~]# innobackupex --user root --password '123qqq...A' --slave-info  /allbak1  --no-timestamp
5. [root@host51 ~]# scp -r /allbak1/ 192.168.4.53:/opt/

复制代码

步骤四：指定主服务器信息

1. [root@host53 ~]# systemctl stop mysqld
2. [root@host53 ~]# rm -rf /var/lib/mysql/*
3. [root@host53 ~]# innobackupex --apply-log  /root/allbak1/                #准备恢复数据
4. [root@host53 ~]# innobackupex --copy-back  /root/allbak1/                        #恢复数据
5. [root@host53 ~]# chown -R mysql:mysql /var/lib/mysql
6. [root@host53 ~]# systemctl start mysqld
7. [root@host53 ~]# mysql -uroot -p'123qqq...A' -e "show databases"

复制代码

步骤五：host53测试主从同步

1. # xtrabackup_binlog_info 文件记录的是binlog日志文件名和偏移量
2. # 此偏移量和主服务器的偏移量一致，从服务器同步数据时从这个偏移量开始同步
3. # 可以查看host51的binlog日志状态，偏移量相同
4. [root@host53 ~]# cat /opt/alldb/xtrabackup_binlog_info
5. master51.000001        1020

复制代码

二、MYSQL主从从结构
配置MYSQL主从从结构

1、主从从结构

主从从结构优势：
1》【host55】是【host54】的从服务器，【host54】是【host53】的从服务器；
2》当【host53】宕机以后，用户访问的是从服务器【host54】的数据库；
3》当【host54】宕机以后，用户访问的就是从服务器【host55】的数据库；
2、主从从结构实验环境准备，根据host50克隆虚拟机host54和host55主机，配置IP如下，并还原数据库默认的四个库

1. # 修改主配置文件，指定server_id号
2. [root@host53 ~]# vim /etc/my.cnf
3. [mysqld]
4. server_id=53                        #指定id号，默认与IP地址的主机位相同
5. [root@host53 ~]# vim /var/lib/mysql/auto.cnf
6. [auto]
7. server-uuid=d45877cc-ffab-11ea-b2a1-000c291f7055
8. [root@host53 ~]# systemctl restart mysqld
9. [root@host53 ~]# mysql -uroot -p123qqq...A

复制代码

3、将host53恢复成独立的数据库服务器

1. mysql> change master to master_host="192.168.4.51", master_user="repluser", master_password="123qqq...A", master_log_file="master51.000001", master_log_pos=1020;
2. mysql> start slave;                                #启动slave
3. mysql> show slave status;                #查看从服务器状态信息
4. host51：插入数据
5. [root@host51 ~]# mysql -uroot -p'123qqq...A'
6. mysql> insert into db1.user values("tomA"),("tomB"),("tomC");

复制代码

4、测试

1. [root@host53 ~]# mysql -uroot -p'123qqq...A'
2. mysql> select * from db1.user;

复制代码

5、host53配置主服务器

1. 主服务器     主机名：host53      IP地址: 192.168.4.53
2. 从服务器     主机名：host54      IP地址: 192.168.4.54
3. 从服务器     主机名：host55      IP地址: 192.168.4.55

复制代码

这一次的授权不需要操作，因为之前53主机在做从库的时候已经同步用户了

1. [root@host53 ~]# cd /var/lib/mysql
2. [root@host53 mysql]# rm -rf master.info                         #删除连接主服务器的信息文件
3. [root@host53 mysql]# rm -rf host53-relay-bin.0*                #删除所有的中继日志文件，存放从主服务器拷贝过来的sql命令
4. [root@host53 mysql]# rm -rf host53-relay-bin.index         #删除中继日志的索引文件，按顺序记录所有的中继日志文件名
5. [root@host53 mysql]# rm -rf relay-log.info                         #删除中继日志文件，记录中继日志信息
6. [root@host53 mysql]# systemctl restart mysqld

复制代码

6、host54配置从服务器

修改配置文件、用户授权、指定主库信息、启动slave进程
修改主配置文件，指定server_id号
log_slave_updates  必须开启级联复制功能，因为【host54】同步数据是从【host53】的binlog日志中获取的，【host54】并没有直接执行sql命令，所以在【host54】的binlog日志中并没有sql命令，那么【host55】也就无法同步【host54】中的数据；而开启级联复制功能，则允许【host55】同步【host54】从【host53】同步过来的数据

1. [root@host53 ~]# mysql -uroot -p'123qqq...A'
2. mysql> show slave status;                #查看从服务器状态信息，为Empty，已经还原
3. mysql> drop database db1;                #删除db1库
4. mysql> exit

复制代码

7、连接数据库，指定主服务器信息

1. 修改主配置文件，开启binlog日志
2. [root@host53 ~]# vim /etc/my.cnf
3. [mysqld]
4. server_id=53
5. log_bin=master53
6. [root@host53 ~]# systemctl restart mysqld
7. [root@host53 ~]# mysql -uroot -p123qqq...A

复制代码

8、host54：从服务器【host55】添加授权用户

1. mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
2. mysql> select user,host from mysql.user;
3. mysql> show grants for repluser@"%";
4. mysql> show master status;

复制代码

9、host55配置从服务器

1. [root@host54 ~]# vim /etc/my.cnf
2. [mysqld]
3. #binlog_format="mixed"
4. server_id=54
5. log_bin=master54
6. log_slave_updates                #允许级联复制
7. [root@host54 ~]# vim /var/lib/mysql/auto.cnf        # auto.cnf 存放的是主服务器的uuid号 因为是克隆出的虚拟机，uuid号都一样，会冲突可以任意修改其中一个或多个字符，重启数据库服务即可
8. [auto]
9. server-uuid=d45877cc-f4ab-12ea-b2a1-000c291f7055
10. [root@host54 ~]# systemctl restart mysqld

复制代码

10、指定主服务器信息

1. [root@host54 ~]# mysql -uroot -p123qqq...A
2. mysql> change master to master_host="192.168.4.53", master_user="repluser", master_password="123qqq...A",master_log_file="master53.000001",master_log_pos=154;
3. mysql> start slave;
4. mysql> show slave status\G;

复制代码

11、测试主从从同步，在host53上建库,建表，插入记录

1. mysql> grant replication slave on *.* to jim@"%" identified by "123qqq...A";
2. mysql> show master status;

复制代码

12、在host54上查看验证数据

1. [root@host55 ~]# vim /etc/my.cnf
2. [mysqld]
3. #binlog_format="mixed"
4. server_id=55
5. [root@host55 ~]# vim /var/lib/mysql/auto.cnf       
6. [auto]
7. server-uuid=d45877cc-f4ab-12ea-b2a1-000c291f7055
8. [root@host55 ~]# systemctl restart mysqld

复制代码

13、在host55上查看验证数据

1. [root@host55 ~]# mysql -uroot -p123qqq...A
2. mysql> change master to master_host="192.168.4.54",master_user="jim", master_password="123qqq...A",master_log_file="master54.000001",master_log_pos=1554;
3. mysql> start slave;
4. mysql> show slave status\G;

复制代码

三、复制模式

复制模式介绍

异步复制 (默认的复制模式)：Asynchronous   replication
主服务器执行完一次事务后，立即将结果返给客户端，不关心从服务器是否已经同步数据。
案例：【host50】(主服务器)    desc information_schema.PLUGINS;                #查看默认库information_schema 下PLUGINS表(模块表)的表结构加载 master 模块，rpl_semi_sync_master模块类型，semisync_master.so模块名称mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME  like "%semi%";[/code]host55安装slave模块

1. [root@host53 ~]# mysql -uroot -p'123qqq...A'
2. mysql> create database bbsdb;
3. mysql> create table bbsdb.user(name char(11));
4. mysql> insert into bbsdb.user values("bob");
5. mysql> select * from bbsdb.user;

复制代码

启用模块

1. [root@host54 ~]# mysql -uroot -p'123qqq...A'
2. mysql> select * from bbsdb.user;

复制代码

host54启动半同步复制的master和slave模块

1. [root@host55 ~]# mysql -uroot -p'123qqq...A'
2. mysql> select * from bbsdb.user;

复制代码

host55：启动半同步复制的slave模块

1. 主服务器     主机名：host53      IP地址: 192.168.4.53
2. 从服务器     主机名：host54      IP地址: 192.168.4.54
3. 从服务器     主机名：host55      IP地址: 192.168.4.55

复制代码

永久配置

主服务器永久安装和启用半同步复制的master模块

1. host53主服务器命令行加载半同步复制的master模块
2. mysql> show databases;
3. mysql> desc information_schema.PLUGINS;                #查看默认库information_schema 下PLUGINS表(模块表)的表结构
4. 加载 master 模块，rpl_semi_sync_master模块类型，semisync_master.so模块名称
5. mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";
6. mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME  like "%semi%";

复制代码

主从服务器永久安装和启用半同步复制的master和slave模块,在host54上操作

1. mysql>  install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";  
2. host54因为是即做主又做从，所以要安装 matser和slave模块
3. mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";
4. mysql> install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";
5. mysql> select PLUGIN_NAME,PLUGIN_STATUS from information_schema.PLUGINS where PLUGIN_NAME  like "%semi%";

复制代码

从服务器永久安装和启用半同步复制的slave模块,在host55上操作

1. host53：模糊查询半同步复制模块是否开启
2. mysql> show variables like "%semi%";
3. mysql> set global rpl_semi_sync_master_enabled=1;                #全局设置，开启半同步复制模块
4. mysql> show variables like "%semi%";                                        #on开启

复制代码

出处：http://www.cnblogs.com/sre-chan/-------------------------------------------
个性签名：今天做了别人不想做的事，明天你就做得到别人做不到的事，尝试你都不敢，你拿什么赢！
如果觉得这篇文章对你有小小的帮助的话，记得在右下角点个“推荐”哦，博主在此感谢！

来源:https://www.cnblogs.com/sre-chan/p/17338583.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！