mysql主从复制原理面试 mysql主从复制问题

转载

mob64ca14173efa 2023-09-10 16:41:34

文章标签 mysql主从复制原理面试 mysql 服务器 MySQL 文章分类 MySQL 数据库

1、快速复习主从复制

1.1 Master节点配置

单mysql问题：
1.性能问题
2.数据备份问题

多mysql好处：
1.性能问题 – 不一定提高
2.数据冗余

MySOL支持一台主服务器同时向多台从服务器进行复制操作，从服务器同时可以作为其他从服务器的主服务器，如果MySQL主服务器访问量比较大，可以通过复制数据，然后在从服务器上进行查询操作，从而降低主服务器的访问压力，同时从服务器作为主服务器的备份，可以避免主服务器因为故障数据丢失的问题。

MySQL数据库复制操作大致可以分成三个步骤：

主服务器将数据的改变记录到二进制日志（binary log）中。
从服务器将主服务器的binary log events 复制到它的中继日志（relay log）中。
从服务器重做中继日志中的事件，将数据的改变与主服务器保持同步。

首先，主服务器会记录二进制日志，每个事务更新数据完成之前，主服务器将这些操作的信息记录在二进制日志里面在事件写入二进制日志完成后主服务器通知存储引擎提交事务。

二进制日志记录了MySQL所有修改数据库的操作，然后以二进制的形式记录日志在日志文件中，其中还包括每条语句所执行的时间和消耗的资源，以及相关的事务信息。默认情况下二进制日志功能是没有开启的，启动可以配置log-bin[=file_name]开启

mysql主从复制原理面试 mysql主从复制问题_mysql

作用就是

增量备份(不是所有数据备份，而是最近的写操作)
用于MySQL主从复制

[root@localhost panel]# vi /etc/my.cnf

主要就是配置文件中添加如下配置

[mysqld] 
log-bin=mysql-bin 
server-id=1

1.2 Slave节点配置

注意：对于使用虚拟机的同学–注意克隆之后的系统你需要稍微修改一下系统的ip 地址

[root@localhost ~]# vi /etc/sysconfig/network-scripts/ifcfg-ens33
IPADDR=192.168.153.131
NETMASK=255.255.255.0
GATEWAY=192.168.153.2
[root@localhost ~]# systemctl restart network

在进行配置之前，回顾一下过程

要先明确配置的架构Master-slave

配置主节点
1.1 配置账号
1.2 开启binlog日志
配置从节点
2.1 配置同步日志
2.2 指定主节点的ip，端口，用户…
2.3 启动从节点

修改配置

[root@localhost ~]# find / -name my.cnf /etc/my.cnf
[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# find / -name mysqld /etc/rc.d/init.d/mysqld /www/server/mysql/bin/mysqld
[root@localhost ~]# /etc/rc.d/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

在配置文件中添加

# 配置从节点
server-id = 2
relay_log = /usr/local/mysql/data/mysql-relay-bin
relay_log-index = /usr/local/mysql/data/mysql-relay-bin.index
log_slave_updates = 1
read_only = 1

参数介绍：

server_id:这是服务id系统会自动命名的，但如果导致问题。可以将你主库和备库上的log-bin设置为相同的值。
relay_log:指定中继日志的位置和命名

指定主节点的ip，端口，用户

mysql> CHANGE MASTER TO master_host =‘192.168.81.132’,
 master_port = 3306,
 master_user =‘slave’,
 master_password =‘slave’,
 master_log_file =‘mysql-bin.000002’,
 master_log_pos = 155;
 Query OK, 0 rows affected, 2 warnings (0.02 sec)

启动从节点

mysql> start slave;
 Query OK, 0 rows affected (0.01 sec)
 mysql> show slave status \G;

对于我们来说其中的信息主要是关注

Slave_IO_Running : Connecting
 Slave_SQL_Running : Yes

reset slave all 清楚slave信息，测试的方法就是在主服务器中，添加一些数据测试观察从服务其中的数据变化情况。

2、主从一致性问题校验

在理想情况下，备库和主库的数据应该是完全一样的。但事实上备库可能发生错误并导致数据不一致。即使没有明显的错误，备库同样可能因为MySQL自身的特性导致数据不一致，例如MySQL的Bug感、网络中断、服务器崩溃，非正常关闭或者其他一些错误。

按照我们的经验来看，主备一致应该是一种规范，而不是例外，也就是说，检查你的主备库一致性应该是一个日常工作，特别是当使用备库来做备份时尤为重要，因为肯定不希望从一个已经损坏的备库里获得备份数据。

我们可以使用percona-toolkit工具做校验，而该工具包含

1.pt-table-checksum 负责检测MySQL主从数据一致性
2.pt-table-sync负责帮助从数据不一致时修复数据，让他们保存数据的一致性
3. pt-heartbeat 负责监控MySQL主从同步延迟

安装

[root@localhost home]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
 [root@localhost home]# yum install percona-toolkit-3.0.3-1.el6.x86_64.rpm
 [root@localhost home]# yum list | grep percona-toolkit
 percona-toolkit.x86_64 3.0.3-1.el6 installed
 percona-toolkit.noarch 2.2.20-1 percona-release-noarch
 percona-toolkit.x86_64 3.1.0-2.el7 percona-release-x86_64
 percona-toolkit-debuginfo.x86_64 3.0.13-1.el7 percona-release-x86_64
 [root@localhost home]# pt-table-checksum --help

使用

pt-table-checksum [options] [dsn]

pt-table-checksum：在主（master）上通过执行校验的查询对复制的一致性进行检查，对比主从的校验值，从而产生结果。DSN指向的是主的地址，该工具的退出状态不为零，如果发现有任何差别，或者如果出现任何警告或错误，更多信息请查看官方资料。

现在我们可以准备一个动作：来模拟数据不一致的问题，同时需要确保主从是配置好了的 -》思路就是创建一个test的库随便添加一个t表

create database `mytest`; 
create table t ( id int primary key, name varchar(20) );

首先配置的是主库 192.168.81.144

mysql> use mytest; mysql> insert into t values(1,6); mysql> insert into t values(2,2); mysql> insert into t values(4,4); mysql> select * from t; ±—±-----+ | id | name | ±—±-----+ | 1 | 6 | | 2 | 2 | | 4 | 4 | ±—±-----+ 3 rows in set (0.00 sec)

3、pt-table-sync工具恢复数据

我们可以通过使用另一个工具pt-table-sync进行数据的同步

手册地址：https://www.percona.com/doc/percona-toolkit/LATEST/pt-table-sync.html

在主库中执行

[root@localhost home]# pt-table-sync --sync-to-master h=192.168.81.140,u=root_sync,p=root,P=3306 --databases=mytest --print DELETE FROM test.t WHERE id=‘3’ LIMIT 1 /percona-toolkit src_db:test src_tbl:t src_dsn:h=127.0.0.1,p=…,u=root dst_db:test dst_tbl:t dst_dsn:h=192.168.153.131,p=…,u=root lock:1 transaction:1 changing_src:check_data.checksums replicate:check_data.checksums bidirectional:0 pid:14262 user:root host:localhost.localdomain/;
[root@localhost home]# pt-table-sync --sync-to-master h=192.168.81.140,u=root_sync,p=root,P=3306 --databases=mytest --execute

如上的操作解释：

pt-table-sync [options] dsn [dsn]

该工具先maseter的信息，然后再是从库上的信息；参数建议

–replicate= ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用。
–databases= : 指定执行同步的数据库，多个用逗号隔开。
–tables= ：指定执行同步的表，多个用逗号隔开。
–sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1 ：服务器地址，命令里有2个ip，第一次出现的是Master的地址，第2次是Slave的地址。
u=root ：帐号。
p=123456 ：密码。
–print ：打印，但不执行命令。
–execute ：执行命令。

建议:
1. 修复数据的时候，用–print打印出来，这样就可以知道那些数据有问题
2. 修复数据之前一定要备份数据库；然后再手动执行或者添加 --execute

我们也可以把这个编辑成脚本，定期通过centos定时器定期检查, 对于我们来说我们执行在意的是通过pt-table-checksums 显示信息中的DIFFS信息

[root@localhost ~]# pt-table-checksum --tables=t --socket=/tmp/mysql.sock --databases=mytest --user=root --password=‘root’ – replicate=check_data.checksum --no-check-binlog-format --recursion-method dsn=t=mytest.dsns,h=192.168.81.140,P=3306,u=slave_check,p=root TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 09-28T16:16:52 0 1 3 1 0 0.015 test.t

对于centos来说我们可以通过 awk 命令获取到 DIFFS 中的值，然后判断这个值是否不等于0；则可以判断是否一致

[root@localhost ~]# pt-table-checksum --tables=t --socket=/tmp/mysql.sock --databases=mytest --user=root --password=‘root’ – replicate=check_data.checksum --no-check-binlog-format --recursion-method dsn=t=mytest.dsns,h=192.168.81.140,P=3306,u=slave_check,p=root | awk ‘NR>1{sum+=$3}END{print sum}’ 1

下一步要做的就是编辑shell脚本 - 额外解释，所谓的sh脚本就是可以直接模拟centos执行我们在命令太执行的命令然后根据返回的结果进行相应的逻辑处理，我们可以创建一个pt-table-checksums；注意！！！windows下编辑的sh脚本在linux中执行可能会存在一定的问题，推荐可以直接在xshell中编辑sh脚本这样问题会少很多

[root@localhost ~]# touch pt-check-sync.sh [root@localhost ~]# vi pt-check-sync.sh #!/usr/bin/env bash NUM=pt-table-checksum --tables=t --socket=/tmp/mysql.sock --databases=mytest --user=root --password='root' -- replicate=check_data.checksum --no-check-binlog-format --recursion-method dsn=t=mytest.dsns,h=192.168.81.140,P=3306,u=slave_check,p=root | awk 'NR>1{sum+=$3}END{print sum}' if [ $NUM -eq 0 ] ;then echo “Data is ok!” elseecho “Data is error!” pt-table-sync --sync-to-master h=192.168.81.140,u=root_sync,p=root,P=3306 --databases=mytest --print pt-table-sync --sync-to-master h=192.168.81.140,u=root_sync,p=root,P=3306 --databases=mytest --execute fi[root@localhost ~]# sh pt-check-sync.sh

然后可以通过编辑crontab -e 定时执行这个脚本就好

20 23 * * * /www/wwwroot/192.168.153.128/shell/pt-check-sync.sh

表示每天晚上23:20运行这个脚本

4、对于mysql的延迟处理

master 服务器和 slave 服务器连接时，创建 Binlog dump thread 以发送 bin log 数据：

1. 一个 Binlog dump thread 对应一个 slave 服务器；
2. Binlog dump thread 从 bin log 获取数据时会加锁，获取到数据后，立即释放锁。

当 slave 服务器收到 START_SLAVE 命令时，会创建 I/O thread 和 SQL thread：

1. I/O thread 以拉的方式，从 master 读取事件，并存储到 slave 服务器的 relay log 中；
2. SQL thread 从 relay log 中读取事件并执行；
3. slave 可以按照自己的节奏读取和更新数据，也可以随意操作复制进程(启动和停止)。

在percona toolkit 产品中也提供了可以对于MySQL主从延时检查的工具pt-heartbeat, pt-heartbeat 的工作原理是通过使用时间戳方式在主库上更新特定表，然后
再从库上读取呗更新的时间戳然后与本地系统时间对比来得出其延迟。

具体流程：

1. 在住上创建一张hearteat表，按照一定的时间频率更新改表的子弹。监控操作运行后，heartbeat表能促使主从同步
 2. 连接到从库上检查复制的时间记录，和从库的当前系统时间进行比较，得出时间的差异。 注意在使用的方式就是需要在主库中创建这个表；

use test;
CREATE TABLE heartbeat (
		ts VARCHAR (26) NOT NULL,
		server_id INT UNSIGNED NOT NULL PRIMARY KEY,
		file VARCHAR (255) DEFAULT NULL, -- SHOW MASTER STATUS
	position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
	relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
	exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);

通过pt-heartbeat可以对于mysql中的heartbeat表每隔多久更新一次（注意这个启动操作要在主库服务器上执行）

$ pt-heartbeat --user=root --ask-pass --create-table --database test --interval=1 --interval=1 --update --replace --daemonize
 $ ps -ef | grep pt-heartbeat

在主库运行监测同步延迟

$ pt-heartbeat --database test --table=heartbeat --monitor --user=root --password=root --master-server-id=1
 0.02s [ 0.00s, 0.00s, 0.00s ]
 0.00s [ 0.00s, 0.00s, 0.00s ]

这其中 0.02s 表示延迟了，没有延迟是为0 而 [ 0.00s, 0.00s, 0.00s ] 则表示1m,5m,15m的平均值，而这期中需要注意的是 --master-server-id 为主服务器的服务id
就是在my.cnf中配置的 server_id的值

4.2 处理延迟问题

对于从库的延时问题最为重要的就是主库与从库之间连接的网络环境，从库的写入熟读这两个点 - 其次就是对于主从的架构的优化；

注意：一旦使用了主从必然是会有一定的延时问题，因此我们就需要考虑程序对于延迟的容忍度。 如果是0容忍的话建议还是不用主从了

MySQL从库产生配置

网络环境跳过，，，从库的写入主要是指insert，update，delete的语句的执行速度这些语句的执行速度我们就需要考虑MySQL的执行SQL语句的一个特点 -》对
于每一个写的sql会默认开启事务并提交事务；而事务是会影响到io的消耗的这和innodb_flush_log_at_trx_commit参数有关系。默认为1 我们可以尝试设置为0或
2可以提高效率，另一个就是sync_binlog

sync_binlog 配置说明：

sync_binlog”：这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。对
于“sync_binlog”参数的各种设置的说明如下： sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息
到磁盘，而让Filesystem自行决定什么时候来做同步，或者 cache满了之后才同步到磁盘。 sync_binlog=n，当每进行n次事务提交之后，MySQL将进行
一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在
binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失
binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

innodb_flush_log_at_trx_commit 配置说明：默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电
池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍
然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会
在整个操作系统挂了时才可能丢数据。