欢迎访问我的博客,你的支持,是我最大的动力!

Mysql主从架构(主从复制)的原理及配置

Mysql-笔记 小马奔腾 15258℃ 评论
目录:
[显示]

一、Mysql复制的原理

将Mysql数据库中的数据从一台服务器复制到一台或多台服务器的过程就是复制。主服务器叫Master,从服务器叫Slave。Master将更新(增/删/改)写入二进制日志文件中,并维护文件的一个索引,以跟踪日志循环。这些日志记录可以发送到Slave,Slave依据日志进行更新。当Slave连接到Master时,Slave会告诉Master自己在日志中最后读取记录的位置,然后从那之后开始取新的更新。Master有新的更新时,也会主动通知Slave。

简单的讲,复制就是将Master上执行过的更新(增/删/改)语句在Slave上重跑一遍。所以,主从复制的过程中,从服务器不能有额外的更新操作,否则会造成主备数据不一致。

复制的三种类型:

  • 基于语句的复制:默认方式 binlog_format='STATEMENT'
  • 基于行的复制:binlog_format='ROW'
  • 混合类型的复制:默认采用基于语句的复制,当发现基于语句的无法精确复制时,改用基于行的复制 binlog_format='MIXED'

复制的工作原理:

(1)Master将改变记录到二进制日志文件(binary log);

(2)Slave将Master的binary log拷贝到它自己的中继日志(relay log);

(3)Slave重做中继日志中的事件。

第一步是Master记录二进制日志。在每个事务提交前,先会记录到日志文件中。记录的方式是串行的,即使事务中语句是交叉执行的。在事件写入日志完成后,Master通知存储引擎提交事务。下一步是Slave将二进制日志拷贝到它自己的中继日志。Slave通过一个普通的连接(I/O线程发起的)进行binlog dump 操作。如果已跟上Master的节奏,它会睡眠并等待Master产生新的事件。取回的数据写入中继日志中,中继日志通常位于系统缓存中。最后一步是Slave重做relay log中的操作。

二、配置实验环境

实验环境
角色 IP MySQL密码 系统版本
Master 192.168.10.110 Root@110 CentOS Linux release 7.3.1611 (Core)
Slave1 192.168.10.120 Root@120 CentOS Linux release 7.3.1611 (Core)
Slave2 192.168.10.130 Root@130 CentOS Linux release 7.3.1611 (Core)

系统为最小化安装全新系统

下载包:

安装MySQL5.7(下载地址:https://dev.mysql.com/downloads/mysql/
yum install wget
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.20-1.el7.x86_64.rpm  或者
curl -O -L https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.20-1.el7.x86_64.rpm
O选项为使用URL中文件名保存文件到本地;L选项为让curl使用地址重定向
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.20-1.el7.x86_64.rpm
mysql-community-devel-5.7.20-1.el7.x86_64.rpm  #这个是开发用的
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.20-1.el7.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.20-1.el7.x86_64.rpm

安装mysql,注意顺序:

yum remove mariadb-libs    #因为mariadb的库与mysql的库有冲突,所以先卸载这个库
rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm
yum install perl
rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm

启动mysql服务,并设置权限:

systemctl start mysqld
systemctl enable mysqld
mysql启动后,会生成root用户的密码,密码位于日志文件中:[Note] A temporary password is generated for root@localhost: p2PqX.lbw0Cv
grep 'temporary password' /var/log/mysqld.log   #得到root密码:p2PqX.lbw0Cv
使用临时密码登录,并设置成自己的密码:
mysql -uroot -p
alter user 'root'@'localhost' identified by 'Root@110';  #因为有validate_password插件,所以密码必须包含大写字母、小写字母、数字、特殊符号并不少于8位
grant all privileges on *.* to 'root'@'%' identified by 'Root@110' with grant option;   #为数据库授权远程访问
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload

调整sshd及文件传输:

vim /etc/ssh/sshd_config  #修改ClientAliveInterval 300和ClientAliveCountMax 2,防止短时间不操作系统自动挂断
systemctl restart sshd 或者systemctl reload sshd

scp mysql-community-* root@192.168.10.120:/root
scp mysql-community-* root@192.168.10.130:/root

三、主从复制的配置

要点:

  • 初始状态时,Master和Slave中数据信息相同
  • 主服务器必须激活二进制日志功能
  • 从服务器必须有连接到主服务器并请求二进制日志的权限

目标:将主服务器数据库testdb同步到两台从服务器

Master上创建复制帐号

在Master上创建备份帐户:backupuser 供Salve连接时使用,该帐户需要拥有REPLICATION SLAVE(replication slave)权限。Master的连接信息存储于master.info文件中(Slave数据库目录)

grant replication slave,reload,super on *.* to 'backupuser'@'192.168.10.120' identified by 'Back@110';
grant replication slave,reload,super on *.* to 'backupuser'@'192.168.10.130' identified by 'Back@110';
flush privileges;
初始数据一致性

需要确保初始数据一致。可以通过拷贝数据的方式进行,始果是全新安装,则不需要操作,因为新安装的数据是一样的。

导出数据库时最好进行锁表,导完再解锁。

先造一个数据库出来

mysql> create database testdb;
mysql> use testdb;
mysql>source /root/testdb.sql;

接下来模拟导出数据

mysql> flush tables with read lock;
mysql> exit
mysqldump testdb -uroot -p > mydump.sql
mysql> unlock tables;
scp mydump.sql root@192.168.10.120:/root
scp mydump.sql root@192.168.10.130:/root

在Slave上还原数据

mysql> create database testdb;
mysql> use testdb;
mysql> source /root/mydump.sql;

配置Master[192.168.10.110]

主要内容为激活二进制日志,指定唯一的Server ID。

vim /etc/my.cnf

log-bin=mysql-bin  #将二进制日志取名为mysql-bin
binlog_format=mixed   #日志格式,共三种:statement/row/mixed
server-id=110   #服务器ID

binlog-do-db=testdb   #需要记录的数据库【最好不要写这句】
binlog-ignore-db=mysql,information_schema,performance_schema,sys   #忽略的数据库

 

systemctl restart mysqld

mysql> show master status;   #查看状态

记录下File和Position的值,并保证在配置完从服务器前不要对主服务器进行任何操作,每次操作数据库这两个值均可能发生改变

配置Slave[192.168.10.120/130]

vim /etc/my.cnf

#log_bin=mysql-bin   #如果要使用级联复制,则需要配置该项
server_id=120  #服务器ID,必须
relay_log=mysql-relay-bin    #relay日志名称
#log_slave_updates=1    #Slave将复制事件写进自己的二进制日志,级联复制使用
#read_only=1  #防止因意外在Slave上进行更新操作

systemctl restart mysqld

 

stop slave;  #关闭slave,如果以前配置过主从的话

mysql> change master to master_host='192.168.10.110',
-> master_user='backupuser',
-> master_password='Back@110',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=154;
或者:change master to master_host='192.168.10.110',master_user='backupuser',master_password='Back@110',master_log_file='mysql-bin.000002',master_log_pos=154;

start slave;
show slave status \G   #查看Slave状态

如果看到 Slave_IO_Running: Yes;Slave_SQL_Running: Yes并且没有报错信息,则运行正常

状态查看及注意事项

查看线程状态:show processlist \G

主库和从库的数据库名必须相同;
主库和从库的复制可以精确到表,但是在需要更改主库或者从库的数据结构时,需要立即重启slave;
不能在mysql配置文件中直接写master的信息,而需要使用change master命令来完成;
指定replication_do_db必须在my.cnf里配置;
如果不及时清理,日积月累,二进制日志文件可能会把磁盘占满,在配置文件里添加expire_logs_days=7只保留7天的日志;
当slave不再使用时,使用reset slave可以取消relaylog

可以写一个监控脚本,用来监控slave中的两个'yes',如果只有一个或者零个,则说明主从有问题

错误处理

分析错误代码的重要性,如果不重要,可以跳过该位置:

stop slave;
set @@global.sql_slave_skip_counter=10;  忽略10步
start slave;
show slave status \G

指定从新的位置开始:

mysqlbinlog /var/lib/mysql/mysql-bin.000001 |more  #查看二进制日志
mysqlbinlog /var/lib/mysql/mysql-bin.000001 |grep -A 10 'end_log_pos 12588'   #定位到某个位置

 

stop slave;
change master to master_log_file='mysql-bin.000001',master_log_pos=424503;
start slave;
show slave status \G

级联复制

当设置log_slave_updates时,可以让Slave扮演其它Slave的Master。此时,Slave将SQL线程执行的事件写进自己的二进制日志,然后它的Slave可以获取这些事件并执行。

复制过滤

复制过滤可以允许只复制一部分数据,有两种过滤:在Master上过滤二进制日志中的事件;在Slave上过滤中继日志的事件。

四、复制的拓扑结构

复制体系结构的基本原则:

  • 每个Slave只能有一个Master
  • 每个Slave只能有唯一的服务器ID
  • 每个Master可以有多个Slave
  • 如果设置log_slave_updates,Slave可以是其它Slave的Master
一主多从

配合读写分离,可以有效缓解Master的读压力,将读分散到Slave端。写操作很少,读操作很多时,非常适用。要求应用对数据实时性要求不严苛(其实时延非常小)。但是当Slave增加到一定数量后,Slave对Master的压力及网络带宽不能忽视。使用建议:

  • 不同Slave扮演不同作用,如使用不同的索引、不同的存储引擎
  • 用一个Slave作为备用Slave,只进行复制
  • 用一个远程的Slave,用于灾备
主动模式的Master-Master

因为有server_id,所以即使开启了log_slave_update也不会产生循环复制的问题。这种结构最大的问题是解决更新冲突。

主动-被动模式的Master-Master

这种结构避免了Master-Master的缺点,是一种具有容错和高可用的系统。它的不同点在于其中一台服务只能进行只读操作。

级联复制Master-Slave-Slave

解决Master端因附属太多Slave而成为瓶颈的问题。开启log_slave_update。

带从服务器的Master-Master

优点是提供冗余。

五、主从一致性检查

工具:Percona-Toolkit

wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/redhat/7/x86_64/percona-toolkit-3.0.4-1.el7.x86_64.rpm  #CentOS7
wget https://www.percona.com/downloads/percona-toolkit/3.0.4/binary/redhat/6/x86_64/percona-toolkit-3.0.4-1.el6.x86_64.rpm  #CentOS6

因为需要libmysqlclient.so.18,而默认是libmysqlclient.so.20,所以下载兼容包:
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm

yum install percona-toolkit-3.0.4-1.el7.x86_64.rpm   #使用yum安装,解决perl的依赖关系  也可以使用yum localinstall per...

percona-toolkit是一组高级命令行工具的集合,主要用来执行通过手工执行非常复杂的mysql和系统任务,包括:检查master和slave的一致性;有效地对记录进行归档;查找重复索引;对服务器信息进行汇总;分析来自日志和tcpdump的查询;当系统出现问题时收集重要信息等。

pt-table-checksum原理:

pt-table-checksum会智能分析表上的索引,然后把表中的数据splite成若干chunk,以chunk为单位,对每一个chunk,把chunk中每行每列的值都转换成字符串,然后连接起来,计算checksum值。该值会被插入percona库的checksums表中,因为是主从环境,checksums表也会复制到从库,然后比对表中数据,验证主从是否一致。

添加percona库的主从复制:vim /etc/my.cnf binlog-do-db=testdb,percona 最好是只设置需要忽略的数据库列表,这样所有新建的库都会复制

建立主从校验所需帐户:
grant select,process,super,replication slave on *.* to 'perconauser'@'%' identified by 'Root@100';
grant all privileges on percona.* to 'perconauser'@'%' identified by 'Root@100';

pt-table-checksum --user=perconauser --password=Root@100 --recursion-method=processlist --databases=testdb --nocheck-binlog-format --nocheck-replication-filters --replicate-check
pt-table-checksum --user=perconauser --password=Root@100 --recursion-method=processlist --databases=testdb --nocheck-binlog-format --nocheck-replication-filters --replicate-check --chunk-size=1
默认chunk-size是1000条

查看差异:
SELECT percona.checksums.db,percona.checksums.tbl,percona.checksums.this_cnt,percona.checksums.master_cnt,percona.checksums.chunk FROM percona.checksums WHERE(master_cnt <> this_cnt OR master_crc <> this_crc OR isnull(master_crc) <> isnull(this_crc))ORDER BY percona.checksums.tbl ASC
注意,查看差异需要到在Slave上执行。

消除差异

pt-table-sync是高效的同步MySQL表之间数据的工具,它可以做单向和双向同步,可以同步单个表,也可以同步整个库,但它不同步表结构、索引或者其它任何模式对象,所以在修复前,需要保证表存在。

pt-table-sync --print --replicate=percona.checksums h=192.168.10.110,u=perconauser,p=Root@100,P=3306 h=192.168.10.120,u=perconauser,p=Root@100,P=3306
前面是主,后面是从;print仅打印,并不执行修改动作

pt-table-sync --print  --execute --replicate=percona.checksums h=192.168.10.110,u=perconauser,p=Root@100,P=3306 h=192.168.10.120,u=perconauser,p=Root@100,P=3306
前面是主,后面是从;打印并执行,这需要perconauser用户有相应权限

pt-table-sync --print --execute --sync-to-master h=192.168.10.120,u=perconauser,p=Root@100,P=3306 --databases=testdb --tables=wp_users

pt-table-sync工具很复杂也很强大。它修改数据库的内容,所以可能造成安全事故。为了安全,建议:使用pt-table-checksum定期检测数据的一致性,并手动重建损坏较为严重的从库,最后才用pt-table-sync修复剩下的从库。

参考文章:

1、高性能Mysql主从架构的复制原理及配置详解

2、MySQL主从配置详解

 

转载请注明:轻风博客 » Mysql主从架构(主从复制)的原理及配置

喜欢 (0)or分享 (0)