mysql复制

目录

复制概述

复制初始环境准备

主备文件配置

主库：

备库：

启动复制

常用参数介绍

主备一致性检查

主备一致性修复

主节点二进制日志损坏

---

---

参考博客、书籍，致谢：

Mysql主从数据不一致?那咋办嘛？_攻城狮悠扬的博客-CSDN博客

技术分享 | 数据校验工具 pt-table-checksum - 简书

MySQL主从(二)--数据校验_独孤清扬玩DB的博客-CSDN博客

《MYSQL高性能》

复制概述

复制就是让一台服务器的数据与其他服务器的数据保持同步，一台主库的数据可以同步到多台备库上面，而同步实现的方式就是通过主库上面记录的二进制日志，在备库进行重放日志的方式实现复制的。

复制初始环境准备

【提醒】以下操作皆建立在两台数据库库表数据相同的情况下

主库：    		        备库：               
ip:[120.25.101.*]       ip:[192.168.184.*] 
用户:"user"		        用户:"user"
权限:"admin" 		    权限:"admin"	
密码:lyn711711		    密码:lyn711711

【提醒】涉及到的基础命令以及温馨提示：（贴出以供参考）

-- Mysql的配置 my.cnf位置  //没有输出内容则使用的就是默认位置
ps aux|grep mysql|grep 'my.cnf'

-- 默认配置my.cnf位置
mysql --help|grep 'my.cnf'

-- 启动mysql服务
systemctl start mysqld

-- 关闭mysql服务
systemctl stop mysqld

-- 重启mysql服务
systemctl restart mysqld 

-- 查看 MySQL 运行状态
systemctl status mysqld

--快速下载安装mysql服务传送门
https://blog.csdn.net/lyouhuan/article/details/124868523

主备文件配置

主库：

my.cnf

log_bin=mysql_bin -- 指定二进制日志名称
server-id=1				-- 服务器唯一ID

mysql命令

mysql> GRANT REPLICATION SLAVE ON *.* TO root@'192.168.184.*' IDENTIFIED BY 'Ljh2823312!';  
-- 授权同步账户
mysql> SHOW MASTER LOGS;
-- 查看二进制文件
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mysql_bin.000001 	 |       4407|  #记录此位置opt，从服务器从这里开始同步
+--------------------+-----------+

备库：

my.cnf

log_bin=mysql_bin                         -- 指定二进制日志名称
server-id=2								  -- 服务器唯一ID
relay_log=/var/lib/mysql/mysql-relay-bin  -- 指定中继日志的位置和命名
log_slave-updates=1						  -- 允许备库将重放事件记录到自身二进制文件中
read_only=1								  -- 阻止没有权限的用户线程修改库数据

配置备库连接主库并重放二进制文件的方式：

mysql> CHANGE MASTER TO
    -> MASTER_HOST='120.25.101.*',  				-- 指定主库IP
    -> MASTER_USER='root',  					    -- 同步用户的用户名
    -> MASTER_PASSWORD='testpass',  				-- 同步用户的密码
    -> MASTER_PORT=3306,							-- 同步服务端口
    -> MASTER_LOG_FILE='mysql_bin.000001',          -- 同步以上记录的二进制日志文件
    -> MASTER_LOG_POS=4407,  						-- 同步以上记录的二进制日志文件位置
    -> MASTER_CONNECT_RETRY=10;  				    -- 同步重试时间10秒

启动复制

【备库】查看主从状态

mysql> show slave STATUS;

可以查看其中这几列，Slave_IO_State、Slave_IO_Running和Slave_SQL_Running 这三列显示当前备库复制是还没有开始执行的，我们可以运行下面的命令来让备库开始复制。

【备库】开始同步主库复制

mysql> start slave;

再次运行 show slave STATUS; 观察几个重要参数就可以很清晰的看出此时已经正常的运行起来了，如果出现异常请查看Last_Errno和Last_Error 并观察下列参数找出错误。

运行成功之后尝试修改主库数据发现备库已经成功同步

常用参数介绍

Slave_IO_State

这里显示了当前slave I/O线程的状态(slave连接到master的状态)

slave I/O线程的状态，有以下几种：

1) waiting for master update

这是connecting to master状态之前的状态

2) connecting to master

I/O线程正尝试连接到master

3) checking master version

在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。

4) registering slave on master

在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。  

5) requesting binlog dump

在与master建立连接后，会出现该状态。该状态出现的时间非常短暂。在这个状态下，I/O线程向master发送请求，请求binlog，位置从指定的binglog 名字和binglog的position位置开始。

6) waiting to reconnect after a failed binlog dump request

如果因为连接断开，导致binglog的请求失败，I/O线程会进入睡眠状态。然后定期尝试重连。尝试重连的时间间隔，可以使用命令"change master to master_connect_trt=X;"改变。

7) reconnecting after a failed binglog dump request

I/O进程正在尝试连接master

8) waiting for master to send event

说明，已经成功连接到master，正等待二进制日志时间的到达。如果master 空闲，这个状态会持续很长时间。如果等待的时间超过了slave_net_timeout(单位是秒)的值，会出现连接超时。在这种状态下，I/O线程会人为连接失败，并开始尝试重连

9) queueing master event to the relay log

此时，I/O线程已经读取了一个event，并复制到了relay log 中。这样SQL 线程可以执行此event

10) waiting to reconnect after a failed master event read

读取时出现的错误(因为连接断开)。在尝试重连之前，I/O线程进入sleep状态，sleep的时间是master_connect_try的值(默认是60秒)

11) reconnecting after a failed master event read

I/O线程正尝试重连master。如果连接建立，状态会变成"waiting for master to send event"

12) waiting for the slave sql thread to free enough relay log space

这是因为设置了relay_log_space_limit，并且relay log的大小已经整张到了最大值。I/O线程正在等待SQL线程通过删除一些relay log，来释放relay log的空间。

13) waiting for slave mutex on exit

I/O线程停止时会出现的状态，出现的时间非常短。

Master_Host

mysql主库的ip地址

Master_User

mysql主库用来负责主从复制的用户

Master_Port

master服务器的端口

Connect_Retry

连接中断后，重新尝试连接的时间间隔。默认值是60秒。

Master_Log_File

当前I/O线程正在读取的主服务器二进制日志文件的名称

Read_Master_Log_Pos

当前I/O线程正在读取的二进制日志的位置

Relay_Log_File

当前slave SQL线程正在读取并执行的中继日志的文件名

Relay_Log_Pos

当前slave SQL线程正在读取并执行的中继日志的位置

Relay_Master_Log_File

SQL线程从relay日志中读取的正在执行的sql语句，对应主库的sql语句记录在主库的哪个binlog日志中

Slave_IO_Running

I/O线程是否被启动并成功地连接到主服务器上

Slave_SQL_Running

SQL线程是否被启动

seconds_Behind_Master

slave当前的时间戳和master记录该事件时的时间戳的差值

Last_IO_Errno

I/O线程或者SQL线程的错误号和错误消息

Master_Server_Id

主库服务器id号

Master_UUID

主库服务器的UUID

Master_Info_File

从库中保存主库服务器相关的目录位置

SQL_Delay

表示秒数，Slave滞后多少秒于master

Slave_SQL_Running_State

SQL线程运行状态

1) Reading event from the relay log

线程已经从中继日志读取一个事件，可以对事件进行处理了。

2) Has read all relay log; waiting for the slave I/O thread to update it

线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

3) Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

主备一致性检查

主备环境正常，备库数据与主库不一致

his.user表（数据正常）											his.user表（数据错误）
+---------+-----------+---------+					+---------+-----------+---------+
| 	 id	  |    name   | 	age	|					| 	 id	  | 	 name | 	age	|
+---------+-----------+---------+					+---------+-----------+---------+
| 	 id	  |    name   | 	age |					| 	 id	  | 	 name | 	age	|
+---------+-----------+---------+					+---------+-----------+---------+
| 	 1	  |    小欧   |  	22	|					| 	 1	  |    小欧   |  	22  |
+---------+-----------+---------+					+---------+-----------+---------+
| 	 1	  |    小布   |  	20	|					| 	 1	  |    小布   |  	20  |
+---------+-----------+---------+					+---------+-----------+---------+
| 	 1	  |    小天   |  	90	|					| 	 1	  |  小天12   |  	90  |
+---------+-----------+---------+					+---------+-----------+---------+

1.安装工具

工具简单说明：

pt-table-checksum是percona-toolkit系列工具中的一个，可以用来检测主、从数据库中数据的一致性。

通过在主节点执行一致性分析来生成检验结果表，将一张大表分成多个chunk，每次针对一个chunk进行校验，同时将校验的结果通过REPLACE INTO语句写入到percona.checksums表中，然后该语句通过主从复制，在SLAVE中同样执行一次，校验的结果同样是写入到percona.checksums表中，最后，通过查询percona.checksums来获取主从不一致的信息。

2.在mysql主节点检查数据同步情况

pt-table-checksum --user=用户名 --password=密码 --nocheck-replication-filters --no-check-binlog-format --databases=库名 --replicate=生成的库表（库.表） --create-replicate-table

3.参数说明

--nocheck-replication-filters：不检查复制的过滤规则，比如replicate-ignore-db、replicate-wild-do-table。
--no-check-binlog-format：不检查复制的binlog模式，如果binlog模式是row模式，需要启用该参数。
--create-replicate-table：第一次进行checksum需要启用该参数，会进行checksum表的创建，用于存放结果。
--replicate=test.checksums：存放checksum结果的表。
--databases：表示要检查的库。
--tables(-t):表示要检查的表。
--replicate-check-only：表示只显示不同步的表。
--recursion-method：正常情况下工具会自动识别从库，如果识别失败，可以用该参数指定查找slave的方法，参数有四种，分别是processlist、hosts、dsn=DSN、no四种，用来决定查找slave的方式是通过show processlist、show slave hosts还是通过dsn=DSN的方式。（不同使用方式不一一实验了，生产环境）

使用工具pt-table-checksum检查一致性报错：

Diffs cannot be detected because no slaves were found.  Please read the --recursion-method documentation for information.

解决办法：以上错误即可工具识别从库失败，需要手工指定从库

            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
02-17T14:39:29      0      1        3          0       1       0   0.016 his.user

分析完成之后如上所示his.user表数据不一致，也可以观察生成的检验结果表数据。

主备一致性修复

1.安装工具

工具简单说明：

通过pt-table-checksum 检查找到了不一致的数据表，那么如何同步数据呢，利用另外一个工具pt-table-sync。高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据。他可以同步单个表，也可以同步整个库。它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证他们表存在。他是通过结合pt-table-checksum生成的checksum表来进行数据修复的

2.打印出不同步的信息（可选）

pt-table-sync --print --databases=his --replicate gffp_om.checksums  h='192.168.184.133',u='root',p='Ljh2823312!',P=3306

3.执行主备数据同步

pt-table-sync --execute --databases=his --replicate gffp_om.checksums  h='192.168.184.133',u='root',p='Ljh2823312!',P=3306

再次查看user表【数据已经恢复一致】

his.user表（数据正常）											his.user表（数据错误）
+---------+-----------+---------+					+---------+-----------+---------+
| 	 id	  |    name   | 	age	|					| 	 id	  | 	 name | 	age	|
+---------+-----------+---------+					+---------+-----------+---------+
| 	 id	  |    name   | 	age |					| 	 id	  | 	 name | 	age	|
+---------+-----------+---------+					+---------+-----------+---------+
| 	 1	  |    小欧   |  	22	|					| 	 1	  |    小欧   |  	22  |
+---------+-----------+---------+					+---------+-----------+---------+
| 	 1	  |    小布   |  	20	|					| 	 1	  |    小布   |  	20  |
+---------+-----------+---------+					+---------+-----------+---------+
| 	 1	  |    小天   |  	90	|					| 	 1	  |    小天   |  	90  |
+---------+-----------+---------+					+---------+-----------+---------+

4.参数说明

【常用】
--print 	--打印出不同步信息
--execute --执行同步																				
--databases=his--检查的数据库
--replicate gffp_om.checksums --pt-table-checksum检验结果表
--h='192.168.184.133',u='root',p='Ljh2823312!',P=3306 --master信息

【更多参数】

--[no]bin-log
默认值：yes
指定同步操作记录二进制日志，相当于执行SET SQL_LOG_BIN=1。如果指定'--no-bin-log'，则对应执行SET SQL_LOG_BIN=0。

--channel
指定当主从复制环境是多源复制时需要进行同步哪个主库的数据，适用于多源复制中多个主库对应一个从库的情形。

--charset，-A
指定连接字符集。

--[no]check-master
默认值：yes
指定当选项'--sync-to-master'使用时，尝试验证工具连接到的主库是否是真正的主库。

--[no]check-slave
默认值：yes
指定检查目标服务器是否是从库所在服务器。
如果目标服务器是从库，则对其进行变更是不安全的，但某些情况却必须这么做，比如当主库需要同步的表上没有唯一索引时，指定选项'--replace'是不会进行工作的，所以在这种情况下无法对主库进行变更。默认情况下如果需要对从库上进行变更，则工具会有提示，可以指定选项'--no-check-slave'禁止检查。

--chunk-size
默认值：1000
指定表分块的chunk大小，每个chunk对应的表行数，也可以是数据块大小，当指定大小时允许的后缀单位为k、M、G。

--host，-h
指定连接的数据库IP地址。

--port，-P
指定连接的数据库Port端口。

--user，-u
指定连接的数据库用户。

--password，-p
指定连接的数据库用户密码。

--socket，-S
指定使用SOCKET文件连接。

--databases，-d
指定需要进行同步的数据库，如有多个则用','(逗号)隔开。

--tables，-t
指定仅需要进行同步的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。

--columns，-c
指定进行比较的表字段，如有多个则用','(逗号)隔开。

--where
通过where语句条件限制表的同步内容。

--dry-run
分析、选择同步的算法，并打印信息和退出。
意味着指定选项'--verbose'可以得出工具分析的结果。分析结果的输出格式与工具实际执行时的输出一致，但是并不会有数据被影响。

--execute
指定工具执行同步操作使表数据达成一致状态，没有此参数则不执行同步操作。
工具使用此选项意味着将同步指定出现数据不一致的表，因此表的数据将被更改，除非指定了选项'--verbose'，否则表数据的更改将以静默的方式进行。

--ignore-databases
指定需要忽略比较的数据库，如有多个则用','(逗号)隔开，系统数据库information_schema和performance_schema默认被忽略。

--ignore-engines
默认值：FEDERATED，MRG_MyISAM
指定需要忽略同步的存储引擎类型的表，如有多个则用','(逗号)隔开。

--ignore-tables
指定需要忽略同步的表，如有多个则用','(逗号)隔开。表名称可以使用数据库名加以限定。

--lock
指定哪个过程中进行锁表(LOCK TABLES)操作，主要有如下取值：
'
VALUE  MEANING
=====  ===========================================================================
0      永远不进行锁表操作；
1      每个同步周期进行锁表操作，例如锁定每次同步chunk对就的表行，这是最细粒度的锁定级别；
2      在表执行操作的时候进行锁表操作；
3      为连接的每个DSN连接的服务器进行锁表操作，指定语句FLUSH TABLES WITH READ LOCK。
'
当指定选项'--replicate'或'--sync-to-master'时，从库的表是不会被锁定的。如果指定了选项'--wait'，则主库对应的表被锁定，工具暂定执行操作直到从库追上主库才继续执行。
如果指定选项'--transaction'，则锁表操作(LOCK TABLES)不会执行，取代的方式是通过事务的开始和提交来进行锁定操作，例外情况是'--lock=3'，如果指定选项'--no-transaction'，则锁表操作(LOCK TABLES)适应所有'--lock'取值情况。

--print
指定打印工具需要执行哪些查询语句来同步表，解决数据不一致，只是打印输出，并不会真正执行。

--recursion-method
默认值：processlist，hosts
指定获取从库的方式。
'
METHOD       USES
===========  =============================================
processlist  SHOW PROCESSLIST   
hosts        SHOW SLAVE HOSTS   
none         Do not find slaves
==========================================================
'
processlist：通过SHOW PROCESSLIST方式找到slave，为默认方式，当SHOW SLAVE HOSTS不可用时。一旦实例运行在非3306端口上时，hosts方式就会变为默认方式；
hosts：通过SHOW SLAVE HOSTS方式找到slave，hosts方式要求从库配置'--report_host'和'--report_port'这两个参数。

--replicate
指定参照该选项中列出的表进行表同步操作。
工具将在该选项指定的表中去查询数据不一致表的信息并进行同步操作，这个选项跟工具pt-table-checksum当中的同名选项是相同意义的。该选项会自动设置选项'--wait=60'并确保在主库进行变更。
如果指定选项'--sync-to-master'，则工具会假设指定的连接是从库，并会寻找主库连接进行表数据同步操作，如果指定的连接不是从库，工具将使用选项'--recursion-method'中查找从库方法进行查找操作，然后再找到需要进行同步操作的表。

--sync-to-master
指定将DSN连接信息确认为从库，并同步信息到主库。
该选项将指定的服务器当作是从库，并且检查从库的主库，连接主库。将主库作为数据同步的源端，从库作为数据同步的目标端进行同步。选项会默认设置选项'--wait=60'和'--lock=1'，并且设置选项'--no-transaction'。

--timeout-ok
指定当选项'--wait'导致工具执行失败时跳过失败继续执行。
如果指定了选项'--wait'，但是从库在指定时间内还是没能追上主库且依然存在主从延迟，则工具将中止操作并退出。指定选项'--timeout-ok'则会继续执行操作。

--verbose，-v
指定打印更详细的操作信息。

主节点二进制日志损坏

如果主节点的二进制日志损坏了，备库读取二进制文件出现错误，【慎重】除了忽略掉损坏的位置意外我们别无选择，尝试进行以下方式：

① master

首先在master主库 上面执行如下命令：

flush logs;

在主库上执行 flush logs 命令后，会在主库开始一个新的二进制日志文件，你可以通过执行以下命令：

show master status;

查看当前最新的二进制日志文件以及当前日志定位位置。

② slave

在slave从库 上面执行以下命令：

stop slave;

用来停止复制，并执行以下命令：

change master to master_log_file ='二进制日志文件',master_log_pos=位置;

将备库指向新生成的二进制日志文件开始的位置，最后通过以下命令：

start slave;

重新开启执行复制。