mysql学习笔记(9)之主从复制

内容来源为六星教育，这里仅作为学习笔记

主从复制概述

MySQL数据库支持单向、双向、链式级联、环状等不同业务场景的复制。在复制过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL重新更新到从服务器，使得主从服务器数据达到一致。

应用场景

MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写称为可能，同时有效地保护了物理服务器宕机场景的数据备份。

应用场景1：从服务器作为主服务器的实时数据备份

主从服务器架构的设置，可以大大加强MySQL数据库架构的健壮性。例如：当主服务器出现问题时，我们可以人工或设置自动切换到从服务器继续提供服务，此时从服务器的数据和宕机时的主数据库几乎是一致的。

这类似NFS存储数据通过inotify+rsync同步到备份的NFS服务器，只不过MySQL的复制方案是其自带的工具。

利用MySQL的复制功能做备份时，在硬件故障、软件故障的场景下，该数据备份是有效的，但对于人为地执行drop、delete等语句删除数据的情况，从库的备份功能就没有用了，因为从服务器也会执行删除的语句。

应用场景2：主从服务器实时读写分离，从服务器实现负载均衡

主从服务器架构可通过程序（PHP、Java等）或代理软件（mysql-proxy、Amoeba）实现对用户（客户端）的请求读写分离，即让从服务器仅仅处理用户的select查询请求，降低用户查询响应时间及读写同时在主服务器上带来的访问压力。对于更新的数据（例如update、insert、delete语句）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

应用场景3：把多个从服务器根据业务重要性进行拆分访问

可以把几个不同的从服务器，根据公司的业务进行拆分。例如：有为外部用户提供查询服务的从服务器，有内部DBA用来数据备份的从服务器，还有为公司内部人员提供访问的后台、脚本、日志分析及供开发人员查询使用的从服务器。这样的拆分除了减轻主服务器的压力外，还可以使数据库对外部用户浏览、内部用户业务处理及DBA人员的备份等互不影响

优点与解决的问题

主从复制的优点

1. 如果主库出现问题，可以快速切换到从库提供服务
2. 可以在从库执行查询操作，降低主库的访问压力。
3. 可以在从库进行备份，以免备份期间影响主库的服务。

主从复制解决的问题

1. 数据分布 (Data distribution )
2. 负载平衡(load balancing)
3. 数据备份(Backups) ，保证数据安全
4. 高可用性和容错行(High availability and failover)
5. 实现读写分离，缓解数据库压力

注意：由于 mysql 实现的异步复制，所以主库和从库数据之间存在一定的差异，在从库执行查询操作需要考虑这些数据的差异，一般只有更新不频繁和对实时性要求不高的数据可以通过从库查询，实行要求高的仍要从主库查询。

主从复制原理

MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示

主节点 log dump 线程

当从节点连接主节点时，主节点会为其创建一个log dump 线程，用于发送和读取bin-log的内容。在读取bin-log中的操作时，log dump线程会对主节点上的bin-log加锁，当读取完成，在发送给从节点之前，锁会被释放。主节点会为自己的每一个从节点创建一个log dump 线程。

从节点 I/O线程

当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点的blog dump进程发来的更新之后，保存在本地relay-log（中继日志）中。

从节点 SQL线程

SQL线程负责读取relay-log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

对于每一个主从连接，都需要这三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。

要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。

因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：

复制的基本过程

1. 在从节点上执行sart slave命令开启主从复制开关，开始进行主从复制。从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；

2. 主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程（log dump 线程）根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position（bin-log中的下一个指定更新位置）；

3. 从节点的I/O进程接收到主节点发送过来的日志内容、日志文件及位置点后，将接收到的日志内容更新到本机的relay-log（中继日志）的文件（Mysql-relay-bin.xxx）的最末端，并将读取到的binary log（bin-log）文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；

4. Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在主节点上实际执行过SQL语句，然后在本数据库中按照解析出来的顺序执行，并在relay-log.info中记录当前应用中继日志的文件名和位置点。

主从复制备份

不同类型的数据库备份，所能应付的情况是不一样的， 而且，数据库的备份同时也还具有其他很多的作用。相信每个人对数据库备份作用的理解都会有差别。下面就列举我个人理解的要用到数据库备份的常见情况：

数据丢失应用场景：
(1)人为操作失误造成某些数据被误操作;
(2)软件BUG造成数据部分或全部丢失;
(3)硬件故障造成数据库数据部分或全部丢失;
(4)因安全漏洞，入侵者将数据恶意破坏。

非数据丢失应用场景：
(1)特殊应用场景下基于时间点的数据恢复;
(2)开发测试环境数据库搭建;
(3)相同数据库的新环境搭建;
(4)数据库或数据迁移。

上面所列出的只是一些常见的应用场景，除了这几种场景，数据库备份还会有很多其他应用场景。

没有哪一种数据库备份能够解决以上列举的所有场景，即使仅只是数据丢失的各种场景都无法通过某一种数据库 备份完美解决，当然就更不用说能够解决所有备份的应用场景了。比如：

1. 当我们遇到磁盘故障，丢失了整个数据库的所有数据，并且无法从已经出现故障的硬盘上面恢复出来的时候，就可能必须通过一个实时或有短暂时间差的复制备份数据库来进行恢复。当然如果没有这样的一个数据库，就必须有最近时间点的整个数据库的物理或逻辑备份数据，并且有该备份之后的所有物理或逻辑增量备份，以期望尽可能地将数据恢复到出现故障之前最近的时间点。
2. 而当我们遇到操作失误造成数据被误操作时，就要有一个能恢复到错误操作时间点之前的瞬间备份，当然这个备份可能是整个数据库的备份，也可以仅仅是被误操作的表的备份。
3. 而当我们要做跨平台的数据库迁移时，则需要一个逻辑的数据库备份，因为平台的差异可能使物理备份的文件格式在两个平台上无法兼容。

既然没有哪一种数据库备份能够完美地解决所有应用场景的需要，而每个数据库环境要面对的数据库备份应用场景又可能各不一样，也许只是须要面对很多种场景中的某种或儿种， 那么就非常有必要指定一个合适的备份方案和备份策略，通过最简单的技术和最低廉的成本来满足我们的需求。

冷备份与恢复

逻辑备份

数据库逻辑备份就是备份软件按照最初设计的逻辑关系，以数据库的逻辑结构对象为单位，将数据库中的数据按照预定义的逻辑关联格式一条一条生成相关的文本文件，以达到备份的目的。

逻辑备份可以说是最简单，也是目前中小型系统最常使用的备份方式。在MySQL中常用的：

mysqldump
mydumper

第一步：
创建一个数据库mysql_php;

mysql> create database mysql_php;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mysql_php          |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

第二步：
在从服务上通过连接主服务器上的数据库，通过mysqldump备份数据到从数据库中
在主服务器上，设置读锁定有效，这个操作为了确保没有数据库操作，以便获得一致性的快照。

mysql> flush tables with read lock;

然后在从服务器上进行数据的备份，并同步导入备份数据

[root@localhost bin]# ./mysqldump -h192.168.29.1 -ustarsky -p master_slave > /home/master_slave.sql
Enter password:
[root@localhost bin]# mysql -f -uroot -p master_slave < /home/master_slave.sql
Enter password:
[root@localhost bin]# mysql -uroot -p
Enter password:
mysql> use master_slave;
Database changed
mysql> show tables;
+------------------------+
| Tables_in_master_slave |
+------------------------+
| products               |
+------------------------+
1 row in set (0.01 sec)

在数据备份完成之后这个时候就可以恢复主数据库的写操作执行命令如下：

mysql> unlock tables;

物理备份

停止主库，然后复制主库中的data放到从库中…

使用mydumper

mydumper是一个针对MySQL和drizzle的高性能多线程的备份和恢复工具。此工具的开发人员分别来自MySQL、facebook、skysql公司、目前已经有一些大型产 品业务测试并使用了该工具。我们在恢复数据库时也可使用myloader工具。 Mydumper的主要特性包括：

· 采用轻量级C语言写的代码。
· 相比于mysqldump，其速度快了近10倍。
· 具有事务性和非事务性表一致的快照（适用于0.2.2+）。
· 可快速进行文件压缩（File compression on-the-fly）。
· 支持导出binlog。
· 可多线程恢复（适用于0.2.1+）。
· 可以用守护进程的工作方式，定时扫描和输出连续的二进制日志。

安装命令如下所示：

[root@localhost wwwroot]# yum install glib2-devel zlib-devel pcre-devel cmake gcc-c++
[root@localhost wwwroot]# git clone https://github.com/maxbube/mydumper.git
[root@localhost wwwroot]# cd mydumper
[root@localhost wwwroot]# cmake .
[root@localhost wwwroot]# make
[root@localhost wwwroot]# make install
[root@localhost mydumper-0.9.1]# mydumper -V
mydumper 0.9.1, built against MySQL 5.5.65-MariaDB

Mydumper中的主要参数如下：

· -host，-h：连接的MySQL服务器。
· -user，-u：用户备份的连接用户。
· -password，-p：用户的密码。
· -port，-P：连接端口。
· -socket，-S：连接socket文 件。
· -database，-B：需要备份的数据库。
· -table-list，-T：需要备份的表，用逗号（，）分隔。
· -outputdir，-o：输出的目录。
· -build-empty-files，-e：默 认无数据则只有表结构文件。
· -regex，-x：支持正则表达式，如mydumper-regex’（2l（mysqltest）’。
· -ignore-engines，-i：忽略的存储引擎。
· -no- schemas，-m：不导出表结构。
· -long-query-guard：长查询，默认60s。
· -kill-long-queries，-k：可以设置kill长查询。
· -verbose，-v：0=silent，1=errors， 2=warmings，3=info，默认是2。
· -binlogs，-b：导出binlog。
· -daemon，-D：启用守护进程模式。
· -snapshot-interval，-I:dump快照间隔时间，默认60s。
· -logfile，-L:mysaqldumper的目志输出，一般在Daemon模式下使用。

mydumper与mysqldump 备份数据对比

 [root@localhost home]# time mydumper -u root -p root -B laravel-shop -o /home/laravel-shop2.sql

 real 0m0.039s
 user 0m0.004s
 sys 0m0.035s

 [root@localhost home]# time mysqldump -u root -p laravel-shop > /home/laravel-shop3.sql
 Enter password:

 real 0m2.093s
 user 0m0.016s
 sys 0m0.047s

对比mysql与myloader数据还原

[root@localhost home]# time mysql -f -u root -p laravel-shop < /home/laravel-shop3.sql
Enter password:

real 0m2.511s
user 0m0.017s
sys 0m0.033s

[root@localhost home]# time myloader -u root -p root -B laravel-shop -d /home/laravel-shop3.sql
** (myloader:9506): CRITICAL **: 06:38:43.292: the specified directory is not a mydumper backup

real 0m0.006s
user 0m0.003s
sys 0m0.003s 2.2

热备份与恢复

xtrabackup手册：https://www.percona.com/doc/percona-xtrabackup/2.4/installation/yum_repo.html

热备份的方式也是直接复制数据物理文件，和冷备份一样，但热备份可以不停机直接复制，一般用于7×24小时不间断的重要核心业务。MySQL社区版的热备份 工具ImnoDB Hot Backup是付费的，只能试用30天，只有购买企业版才可以得到永久使用权。Percona公司发布了一个xtrabackup热备份工具，和官方付费版的 功能一样，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDBHot Backup的一个很好的替代品。下面具体介绍一下这个软件的使用方法。 xtrabackup是Percona公司的开源项目，用以实现类似ImnoDB官方的热备份工具ImmoDB Hot Backup的功能，它能非常快速地备份与恢复MySQL数据库。

下面来看看xtrabackup的安装方法，安装命令如下：

[root@localhost file]$ yum localinstall percona-xtrabackup-80-8.0.14-1.el7.x86_64.rpm
[root@localhost file]# xtrabackup

中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
–host 指定主机
–user 指定用户名
–password 指定密码
–port 指定端口
–databases 指定数据库
–incremental 创建增量备份
–incremental-basedir 指定包含完全备份的目录
–incremental-dir 指定包含增量备份的目录
–apply-log 对备份进行预处理操作 一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。 因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
–redo-only 不回滚未提交事务
–copy-back 恢复备份目录

开始进行备份操作,进入 主库 中

[root@centos ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password=root --port=3306 --backup --target-dir=/home/master_slave
[root@localhost home]# ll /home/laravel-shop/2019-09-26_12-43-34/
总用量 75832
-rw-r----- 1 root root 425 9月 26 12:43 backup-my.cnf
drwxr-x--- 2 root root 58 9月 26 12:43 bin
-rw-r----- 1 root root 361 9月 26 12:43 ib_buffer_pool
-rw-r----- 1 root root 77594624 9月 26 12:43 ibdata1
drwxr-x--- 2 root root 4096 9月 26 12:43 laravel@002dshop
drwxr-x--- 2 root root 4096 9月 26 12:43 mysql
drwxr-x--- 2 root root 8192 9月 26 12:43 performance_schema
drwxr-x--- 2 root root 8192 9月 26 12:43 sys
drwxr-x--- 2 root root 46 9月 26 12:43 test
-rw-r----- 1 root root 24 9月 26 12:43 xtrabackup_binlog_info
-rw-r----- 1 root root 113 9月 26 12:43 xtrabackup_checkpoints
-rw-r----- 1 root root 508 9月 26 12:43 xtrabackup_info
-rw-r----- 1 root root 2560 9月 26 12:43 xtrabackup_logfile

[root@localhost home]# scp -r /home/laravel-shop/ root@192.168.153.127:/home/laravel-shop

然后呢进入 从库中

[root@localhost server]# ll /home/laravel-shop/2019-09-26_12-43-34/
总用量 75832
-rw-r----- 1 root root 425 9月 27 21:54 backup-my.cnf
drwxr-x--- 2 root root 58 9月 27 21:54 bin
-rw-r----- 1 root root 361 9月 27 21:54 ib_buffer_pool
-rw-r----- 1 root root 77594624 9月 27 21:54 ibdata1
drwxr-x--- 2 root root 4096 9月 27 21:54 laravel@002dshop
drwxr-x--- 2 root root 4096 9月 27 21:54 mysql
drwxr-x--- 2 root root 8192 9月 27 21:54 performance_schema
drwxr-x--- 2 root root 8192 9月 27 21:54 sys
drwxr-x--- 2 root root 46 9月 27 21:54 test
-rw-r----- 1 root root 24 9月 27 21:54 xtrabackup_binlog_info
-rw-r----- 1 root root 113 9月 27 21:54 xtrabackup_checkpoints
-rw-r----- 1 root root 508 9月 27 21:54 xtrabackup_info
-rw-r----- 1 root root 2560 9月 27 21:54 xtrabackup_logfile
[root@localhost server]#
[root@localhost server]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost server]# mv /www/server/data /www/server/data2
[root@localhost server]# xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/home/mysql_php/mysql_php
[root@localhost server]# chown -R mysql:mysql /usr/local/mysql/data
[root@localhost server]# /etc/init.d/mysqld start Starting MySQL.Logging to '/www/server/data/localhost.localdomain.err'. . SUCCESS!
[root@localhost server]# mysql -u root -p
mysql> show databases;
+--------------------+
|     Database       |
+--------------------+
| information_schema |
| bin                |
| laravel-shop       |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.02 sec

对于热备份实现解释

1. innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；
2. xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的 checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待 状态（等待文件被创建）
3. xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）
4. innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、 MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。
5. 当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；
6. xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；
7. innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；
8. 最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

主从复制实现方式

主节点与从节点配置

Master节点配置

单MySQL问题：

1. 性能问题
2. 数据备份问题

多MySQL好处

1. 性能问题–不一定提高；
2. 数据冗余

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

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

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

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

准备： 了解binlog日志，MySQL用户-权限mysql服务器配置复制不难，但是因为场景不同可能会存在一定的差异化，总的来说分为一下几步：

1. 在服务器上创建复制账号。
2. 通知备库连接到主库并从主库复制数据。

准备服务器 角色 IP 操作系统 mysql版本 端口 复制账号 密码 主Master 192.168.81.132 CentOS7.6.1810 mysql8.0.19 3306 slave slave 从slave1 192.168.81.140 CentOS7.6.1810 mysql8.0.19 3306 … … 从slave2 192.168.81.141 CentOS7.6.1810 mysql8.0.19 3306 … …

角色	IP	操作系统	mysql版本	端口	复制账号	密码
主Master	192.168.29.102	CentOS7.6.1810	mysql8.0.21	3306	slave	slave
从slave1	192.168.29.103	CentOS7.6.1810	mysql8.0.21	3306	…	…
从slave2	192.168.29.104	CentOS7.6.1810	mysql8.0.21	3306	…	…

对于主从复制，在本质上就是通过与从数据库复制与主数据库的binlog日志文件，通过重做实现的同步； 但是一定要注意尽量保证主从服务器上安装了相同的版本的数据库，设定主从的服务器ip地址为192.168.81.132从服务器的ip地址是192.168.81.140,192.168.81.141。 然后再主服务器上设置一个复制使用的账号，并授予replication slave权限。我们可以根据ip创建账号为slave

/*创建账号sql：
create user 'username'@'localhost' identified by 'password';

授权grant [权限] on *.* to 'username'@'localhost' identified by 'password';

*/ mysql> CREATE USER 'slave'@'192.168.81.%' IDENTIFIED WITH mysql_native_password BY 'slave';
Query OK, 0 rows affected (0.04 sec)

mysql> select user,host from mysql.user;

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.81.%';
Query OK, 0 rows affected, 1 warning (0.00 sec)

对于MySQL的主从复制来说最重要的主要就是binlog日志，所以我们就需要开启binlog日志，并设置server-id的值。需要重启服务器之后才生效 二进制日志，也就是我们常说的binlog。

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

mysql> show global variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
+---------------------------------+---------------------------------------+
5 rows in set (0.03 sec)

作用就是

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

[root@localhost panel]# vi /etc/my.cnf
主要就是下配置文件中添加如下配置
[mysqld]
log-bin=mysql-bin
server-id=1

主与从的server_id不能一样

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |     1303 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

File：这是master的bin-log日志，从数据库就是从这个文件开始复制
Position：这是master的bin-log日志的最后的节点，从数据库就是从这个文件的这个节点开始复制

Slave节点配置

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

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

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

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

1. 配置主节点 1.1 配置账号 1.2 开启binlog日志
2. 配置从节点 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

参数介绍：

1. server_id:这是服务id系统会自动命名的，但如果机器名边画画肯能回答导致问题。可以讲你主库和备库上的log-bin设置为相同的值。
2. relay_log:指定 中继日志的位置和命名

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

mysql> change master to
master_host='192.168.29.102',master_port=3306,master_user='slave',master_password='slave',master_log_file='mysqlbin.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信息 测试的方法就是在主服务器中，添加一些数据测试观察从服务其中的数据变化情况。

主从复制数据一致性校验

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

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

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

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

安装

[root@localhost ~]# yum install perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl perl-DBI -y
[root@localhost home]# yum localinstall percona-toolkit-3.2.1-1.el7.x86_64.rpm
[root@localhost home]# yum list | grep percona-toolkit
percona-toolkit.x86_64                      3.2.1-1.el7                @/percona-toolkit-3.2.1-1.el7.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.29.102

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)

其次是从库 192.168.29.103,此时因为主从复制的原因，在上面主库进行的配置会复制到从库。

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

从上面可以看到实际上数据是不同步的，也就是主库的数据少于从库的数据

使用工具检测

注意常用的参数解释：

--nocheck-replication-filters ：不检查复制过滤器，建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format : 不检查复制的binlog模式，要是binlog模式是ROW，则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate= ：把checksum的信息写入到指定表中，建议直接写到被检查的数据库当中。
--databases= ：指定需要被检查的数据库，多个则用逗号隔开。
--tables= ：指定需要被检查的表，多个用逗号隔开
--host | h= ：Master的地址
--user | u= ：用户名
--passwork | p=：密码
--Post | P= ：端口

检测

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --databases=test --tables=t --user=mytest --password=rot
Checking if all tables can be checksummed ...
Starting checksum ...
Replica localhost.localdomain has binlog_format MIXED which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you understand the risks, specify --no-check-binlog-format to disable this check.
Replica localhost.localdomain has binlog_format MIXED which could cause pt-table-checksum to break replication.  Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation.  If you understand the risks, specify --no-check-binlog-format to disable this check.

上面的错误信息主要是因为，检测主库与从库的binlog日志的模式 - 通常来说可以不用改binlog添加 --no-check-binlog-format 跳过检测

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=rot
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
11-11T23:27:10      0      1        3          1       1       0   0.064 mytest.t

TS ：完成检查的时间。
ERRORS ：检查时候发生错误和警告的数量。
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。
ROWS ：表的行数。
CHUNKS ：被划分到表中的块的数目。
SKIPPED ：由于错误或警告或过大，则跳过块的数目。
TIME ：执行的时间。
TABLE ：被检查的表名。

那些信息数据会记录在checksums表中

mysql> use check_data;

mysql> show tables;
+----------------------+
| Tables_in_check_data |
+----------------------+
| checksums            |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from checksums\G;
*************************** 1. row ***************************
            db: mytest
           tbl: t
         chunk: 1
    chunk_time: 0.003449
   chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
      this_crc: dbbf3106
      this_cnt: 3
    master_crc: dbbf3106
    master_cnt: 3
            ts: 2020-11-11 23:27:10
1 row in set (0.00 sec)

ERROR:
No query specified

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.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print
DELETE FROM `mytest`.`t` WHERE `id`='3' LIMIT 1 /*percona-toolkit src_db:mytest src_tbl:t src_dsn:P=3306,h=192.168.29.102,p=...,u=mytest dst_db:mytest dst_tbl:t dst_dsn:P=3306,h=192.168.29.103,p=...,u=mytest lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:76531 user:root host:localhost.localdomain*/;

如上的操作解释：

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 home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=rot
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
11-11T23:36:02      0      1        3          1       1       0   0.048 mytest.t

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

[root@localhost home]# pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=rot |  awk 'NR>1{sum+=$3}END{print sum}'
1

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

#!/usr/bin/env bash
NUM=`pt-table-checksum --nocheck-replication-filters --replicate=check_data.checksums --no-check-binlog-format --databases=mytest --tables=t --user=mytest --password=root | awk 'NR>1{sum+=$3}END{print sum}'`
if [ $NUM -eq 0 ] ;then
echo "Data is ok!"
else
echo "Data is error!"
pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.29.103,u=mytest,p=rot,P=3306 --databases=mytest --execute
pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --print
pt-table-sync --sync-to-master h=192.168.29.104,u=mytest,p=rot,P=3306 --databases=mytest --execute
fi

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

20 23 * * * /home/pt-check-sync.sh

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

对于主从延迟的处理

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 可以按照自己的节奏读取和更新数据，也可以随意操作复制进程(启动和停止)。

pt-heartbeat

在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表每隔多久更新一次（注意这个启动操作要在主库服务器上执行）

[root@localhost home]# pt-heartbeat --user=root --ask-pass --create-table --database mytest --interval=1 --interval=1 --update --replace --daemonize
[root@localhost home]#  ps -ef | grep pt-heartbeat
root      76637      1  0 23:52 ?        00:00:00 perl /usr/bin/pt-heartbeat --user=root --ask-pass --create-table --database mytest --interval=1 --interval=1 --update --replace --daemonize
root      76643  76367  0 23:53 pts/2    00:00:00 grep --color=auto pt-heartbeat

在主库运行监测同步延迟

[root@localhost home]#  pt-heartbeat --database mytest --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的值

主从延迟处理

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

注意：一旦使用了主从必然是会有一定的延时问题，因此我们就需要考虑程序对于延迟的容忍度。 如果是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只会在整个操作系统 挂了时才可能丢数据。

硬件

升级电脑配置。。。

架构

1. 可以考虑对于一些库进行单独分离。
2. 服务的基础架构在业务和MySQL之间加入memcache或者redis的cache层。
3. 从库的配置要好。。。