MYSQL—主从同步概述与配置

一、MYSQL主从同步概述

1、什么是MySQL主从同步?

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

主服务器(master): 接受客户端访问连接
从服务器(slave):自动同步主服务器数据

2、主从同步原理

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

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

Master(主服务器):

1》开启binlog日志,记录所有除查询以外的SQL命令

Slave(从服务器):

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

3、主从同步结构模式

基本应用
单向复制:一主 <—— 一从
扩展应用
一主多从:从 <—— 主 ——> 从
链式复制:主 <—— 从 <—— 从
互为主从:主 <——> 主

4、MySQL主从同步配置

拓扑结构
MYSQL---主从同步概述与配置

5、实施

步骤一:根据host50克隆虚拟机出host51,配置IP地址为192.168.4.51,主机名为host51,删除51,52上面多余的数据库,只保留默认的四个库

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

步骤二:host51配置主服务器

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

步骤三:重启数据库服务,让配置生效

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

步骤四:host52配置从服务器

# 修改主配置文件,指定server_id号
[root@host52 ~]# vim /etc/my.cnf
 [mysqld]
server_id=52          #指定id号,默认与IP地址的主机位相同
[root@host52 ~]# systemctl restart mysqld
# 指定主服务器信息
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;

步骤五:启动从服务器

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

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

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

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

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

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

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

步骤七:在host52上查看

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

相关文件

存放在数据库目录下/var/lib/mysql/
删除文件,重启数据库服务,可把主机恢复为独立的数据库服务器;
MYSQL---主从同步概述与配置

记录主服务器的信息

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

查看中继日志信息

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

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

拓扑结构
MYSQL---主从同步概述与配置

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

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

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

host51:安装innobackupex相关软件包

[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/
[root@host51 ~]# rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm 
[root@host51 ~]# yum -y install percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm
[root@host51 ~]# innobackupex --user root --password '123qqq...A' --slave-info  /allbak1  --no-timestamp
[root@host51 ~]# scp -r /allbak1/ 192.168.4.53:/opt/

host53使用innobackupex恢复数据

[root@host53 ~]# systemctl stop mysqld
[root@host53 ~]# rm -rf /var/lib/mysql/*
[root@host53 ~]# innobackupex --apply-log  /root/allbak1/		#准备恢复数据
[root@host53 ~]# innobackupex --copy-back  /root/allbak1/			#恢复数据
[root@host53 ~]# chown -R mysql:mysql /var/lib/mysql
[root@host53 ~]# systemctl start mysqld
[root@host53 ~]# mysql -uroot -p'123qqq...A' -e "show databases"
# xtrabackup_binlog_info 文件记录的是binlog日志文件名和偏移量
# 此偏移量和主服务器的偏移量一致,从服务器同步数据时从这个偏移量开始同步
# 可以查看host51的binlog日志状态,偏移量相同
[root@host53 ~]# cat /opt/alldb/xtrabackup_binlog_info 
master51.000001	1020

步骤三:配置从服务器

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

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

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;
mysql> start slave;				#启动slave
mysql> show slave status;		#查看从服务器状态信息
host51:插入数据
[root@host51 ~]# mysql -uroot -p'123qqq...A'
mysql> insert into db1.user values("tomA"),("tomB"),("tomC");

步骤五:host53测试主从同步

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

二、MYSQL主从从结构

配置MYSQL主从从结构

1、主从从结构

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

主从从结构优势:
1》【host55】是【host54】的从服务器,【host54】是【host53】的从服务器;
2》当【host53】宕机以后,用户访问的是从服务器【host54】的数据库;
3》当【host54】宕机以后,用户访问的就是从服务器【host55】的数据库;

2、主从从结构实验环境准备,根据host50克隆虚拟机host54和host55主机,配置IP如下,并还原数据库默认的四个库

主服务器     主机名:host53      IP地址: 192.168.4.53
从服务器     主机名:host54      IP地址: 192.168.4.54
从服务器     主机名:host55      IP地址: 192.168.4.55

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

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

4、测试

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

5、host53配置主服务器

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

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

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

6、host54配置从服务器

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

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

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

[root@host54 ~]# mysql -uroot -p123qqq...A
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;
mysql> start slave;
mysql> show slave status\G;

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

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

9、host55配置从服务器

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

10、指定主服务器信息

[root@host55 ~]# mysql -uroot -p123qqq...A
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;
mysql> start slave;
mysql> show slave status\G;

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

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

12、在host54上查看验证数据

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

13、在host55上查看验证数据

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

三、复制模式

复制模式介绍

异步复制 (默认的复制模式):Asynchronous replication
主服务器执行完一次事务后,立即将结果返给客户端,不关心从服务器是否已经同步数据。

案例:【host50】(主服务器) <—— 【host51】(从服务器)

用户在【host50】(主服务器)上执行插入,更新,删除等SQL命令时,【host50】(主服务器)直接将结果返回给用户,不关心【host51】(从服务器)是否同步数据成功
优点:响应速度快,用户体验很好;
缺点:主服务器宕机后,有可能会存在从服务器数据丢失的情况;

半同步复制:Semisynchronous replication
主服务器在执行完一次事务后,等待至少一台从服务器同步数据完成,才将结果返回给客户端。

案例:【host50】(主服务器) <—— 【host51】(从服务器)

用户在【host50】(主服务器)上执行插入,更新,删除等SQL命令时,【host50】(主服务器)不会立刻将结果返回给用户,而是等待至少一个从服务器将数据同步写入到本机的数据库后,才将结果返回给用户

优点:主服务器宕机后,至少有一台从服务器拥有和主服务器相同的数据,数据安全度高;
缺点:响应速度下降,用户体验度下降;

配置半同步复制 (主从服务器都要配置)

主服务器     主机名:host53      IP地址: 192.168.4.53
从服务器     主机名:host54      IP地址: 192.168.4.54
从服务器     主机名:host55      IP地址: 192.168.4.55

命令行加载模块

host53主服务器命令行加载半同步复制的master模块
mysql> show databases;
mysql> 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%";

host55安装slave模块

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

启用模块

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

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

mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show variables like "%semi%";

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

mysql> set global rpl_semi_sync_slave_enabled=1;
mysql> show variables like "%semi%";

永久配置

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

[root@host53 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_master=semisync_master.so    		#加载master模块
rpl_semi_sync_master_enabled=1                        		#启用master模块
[root@host53 ~]# systemctl restart mysqld
[root@host54 ~]# mysql -uroot -p'123qqq...A'
mysql> show variables like "%semi%";

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

[root@host54 ~]# vim /etc/my.cnf
[mysqld]
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"              							#同时加载master和slave模块
rpl_semi_sync_master_enabled=1				#启用master模块
rpl_semi_sync_slave_enabled=1				#启用slave模块
[root@host54 ~]# systemctl restart mysqld
[root@host54 ~]# mysql -uroot -p'123qqq...A'
mysql> show variables like "%semi%";

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

[root@host55 ~]# vim /etc/my.cnf
[mysqld]
plugin-load=rpl_semi_sync_slave=semisync_slave.so    		#加载master模块
rpl_semi_sync_slave_enabled=1                          #启用master模块
[root@host55 ~]# systemctl restart mysqld
[root@host55 ~]# mysql -uroot -p'123qqq...A'
mysql> show variables like '%semi%';

原文链接:https://www.cnblogs.com/sre-chan/p/17338583.html

本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MYSQL—主从同步概述与配置 - Python技术站

(0)
上一篇 2023年4月22日
下一篇 2023年4月22日

相关文章

  • centos6.9 安装mysql8

    centos6.9 安装 mysql8   # 安装mysql8 1.下载https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.16-2.el6.x86_64.rpm-bundle.tar 2.解压 tar -xvf mysql-8.0.16-2.el6.x86_64.rpm-bundle.tar rpm …

    MySQL 2023年4月12日
    00
  • winxp 安装MYSQL 出现Error 1045 access denied 的解决方法

    下面是详细讲解“winxp 安装MYSQL 出现Error 1045 access denied 的解决方法”的完整攻略: 问题描述 在安装MYSQL时,如果出现“Error 1045 access denied”错误,可能是由于没有正确设置root账户密码以及权限导致。下面将介绍如何解决这个问题。 解决方法 方法一:重置root账户密码 停止MYSQL服务…

    MySQL 2023年5月18日
    00
  • SQL语句优化方法30例(推荐)

    SQL语句优化对于数据库性能优化是非常重要的一步,让我们了解一下SQL语句优化的30种方法。 一、SQL语句优化基础 1.理解查询计划 在优化SQL语句之前,应该先了解查询计划,也就是数据库优化器生成的执行SQL语句的步骤。可以通过EXPLAIN来查看查询计划。 2.避免在WHERE子句中使用函数 在WHERE子句中使用函数会影响索引的使用效果,降低查询效率…

    MySQL 2023年5月19日
    00
  • Mysql MyISAM与InnoDB 表锁行锁以及分库分表优化

    一、 两种存储引擎:MyISAM与InnoDB 区别与作用 1. count运算上的区别: 因为MyISAM缓存有表meta-data(行数等),因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说,则没有这种缓存。 2. 是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能,每次查询具有原子性,其执行数度比…

    MySQL 2023年4月13日
    00
  • 一篇文章带你入门SQL编程

    为了让读者快速入门SQL编程,我们可以提供如下完整攻略: 一篇文章带你入门SQL编程 什么是SQL? SQL是结构化查询语言(Structured Query Language)的缩写,是一种用于管理关系型数据库的编程语言。 创建表 在SQL中,你可以使用CREATE TABLE语句来创建一个新表。下面是一个创建students表的示例: CREATE TA…

    MySQL 2023年5月18日
    00
  • MySQL8.0.27安装过程中卡在Initializing Database中并报错的解决

    下面是针对MySQL8.0.27安装过程中卡在Initializing Database中并报错的解决攻略。 问题描述 在MySQL8.0.27的安装中,有些用户可能会遇到Initializing Database过程卡住,或者在某些步骤出现错误的问题。这些问题可能是由于各种原因引起,比如系统环境不兼容、文件权限问题等。下面详细介绍解决这些问题的方法。 解决…

    MySQL 2023年5月18日
    00
  • 活动 | GreatSQL社区亮相2023 DTC 分享开源技术成果实践

    4月7-8日,年度数据库行业盛会——2023数据技术嘉年华(DTC 2023)如期而至。 此次盛会汇聚了全国各地数千名数据领域学术精英、领袖人物、技术专家、从业者和技术爱好者,共同见证行业蓬勃发展、生态融合共赢、技术迭代升级及市场风云变迁。 GreatSQL作为万里数据库主导成立的开源数据库社区,首次亮相嘉年华大会,并带来开源社区的技术成果与应用探索,助力数…

    MySQL 2023年4月17日
    00
  • 如何进行MySQL源码调试(一条select语句的执行流程)

    一、背景 MySQL是当今世上最受欢迎的使用最广泛的开源数据库,它的繁荣离不开它的开源特性。放在过去商业数据库的时代,大家都没有机会接触到数据库的源代码,但在如今开源数据库的时代,越来越多的人开始研究数据库的源码,并给社区贡献代码,MySQL官方每次发布新版本都要感谢一些在社区上贡献代码的程序员。现在新的数据库时代也给DBA提出了更高的要求,学会调试源码,通…

    MySQL 2023年5月10日
    00
合作推广
合作推广
分享本页
返回顶部