数据库主从同步架构

数据库主从同步架构

（1）常见主从同步异常情况

IO线程异常

-- IO线程状态显示为：connecting
异常问题原因01: 连接主库配置错误，主库网络或用户信息匹配从库配置信息
异常问题原因02: 主从之间网络通讯被阻止
异常问题原因03: 主从之间连接没有空闲连接数，可以实现主从建立连接了

-- IO线程状态显示为：no
异常问题原因01：主从之间标识信息冲突 （server_id/server_uuid）
	可以修改server_id/可以通过删除数据目录中 auto.cnf文件，重启数据库服务，可以获取新的uuid信息
异常问题原因02：需要加载的binlog日志在主库中找不到了
	可以重新将主库数据做全备，从库重新加载全备数据，根据全备中位置点信息做主从重新配置

SQL线程异常

-- SQL线程状态显示：no
异常问题原因：不能正确读取中继日志信息，回放SQL语句
情况01：中继日志信息丢失损坏了
       可以根据当前从库完成事务情况，对比主库中事务完成情况，回去对应事务位置点，利用位置点进行主从重新建立 
       reset slave all; change master to master_binlog=''  master_posxxxx='738';start slave; 
情况02：回放SQL语句在从库中已经执行过了
       当需要手动在数据库中写入数据信息时，现在从库做了操作，后又在主库中做了操作   

解决SQL线程异常方法：

1）将SQL线程回放冲突信息删除清理
2) 将冲突数据错误信息进行忽略
   vim /etc/my.cnf
   slave_skip_errors=1007

避免以上SQL线程回放数据冲突情况：
1）在从库上设置只读功能，避免连接数据库错误导致SQL线程回放异常
   read_only             - 针对普通用户设置只读功能
   super_read_only       - 针对管理员设置只读功能
2) 不要进行主从搭建时，主从数据库实例使用不同版本
3) 在实现高可用架构搭建时，必须实现数据补偿功能
4）在实现双主架构搭建时，需要避免相同业务数据实现同步功能

（2）数据库主从架构搭建过程

方法一：利用传统方式实现主从同步
方法二：利用GTID方式实现主从同步
GTID实现主从同步原理
-- 当开启GTID功能，会为每个数据库中完成的事务做编号处理  
-- 主从数据库中都会加载应用binlog日志
-- 实现主从之间binlog日志信息对比，就能自动完成数据主从同步

步骤一：编写配置文件开启GTID功能
主从数据库都需要开启
vim /etc/my.cnf
[mysqld]
gtid-mode=on
enforce-gtid-consistency=true
-- 以上配置项表示开启GTID功能（主从数据库都需要配置）
log-slave-updates=1  
-- 以上配置项表示从库SQL线程回放数据操作后，会将操作信息记录到从库binlog日志中 （从库配置即可）

/etc/init.d/mysqld restart

步骤二：主库备份数据/从库恢复数据
create table xiaoA.t1 (id int);
create table xiaoA.t2 (id int);
-- 备份数据前，需要在主库中创建一些测试数据

mysqldump -A --source-data >/backup/all-03-06.sql
scp -rp /backup/all-03-06.sql 10.0.0.52:/backup
mysql >source /backup/all-03-06.sql;

步骤三：设置主从同步功能
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_PORT=3306, 
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  master_auto_position=1;

步骤四：激活主从功能并查看主从同步状态
start slave;
show slave status\G

Retrieved_Gtid_Set: 1adc0957-1862-11f1-8fae-000c29fd5e80:3-4
-- 表示主库给从库发送的事务信息
Executed_Gtid_Set: 1adc0957-1862-11f1-8fae-000c29fd5e80:1-4
-- 表示从库执行的事务信息

mysqldump -A --source-data >/backup/all-03-06.sql
Warning: 
A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, 
even those that changed suppressed parts of the database. 
当数据库开启GTID功能后，会为每个备份文件中事务信息设置事务编号，并存储到备份文件中

If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. 
数据库开启GTID功能后，如果不想存储事务编号信息到备份文件，请在备份时添加 --set-gtid-purged=OFF 

To make a complete dump, pass --all-databases --triggers --routines --events. 
想实现完成数据备份，请在备份时添加 --all-databases --triggers --routines --events


mysqldump -A --source-data --set-gtid-purged=OFF >/backup/all-03-06.sql
-- 可以没有警告
-- 在备份文件中是没有GTID事务编号信息，可以用于数据异常修复

mysqldump -A --source-data --set-gtid-purged=ON >/backup/all-03-06.sql
-- 会显示警告
-- 在备份文件中是具有GTID事务编号信息，可以用于主从数据迁移

方法三：实现主从延迟同步架构搭建
主从延迟架构：表示实现主从之间数据同步，不是实时同步，可以控制一定时间后再进行同步
主从延迟目的：可以实现主库数据的修复（DDL误操作）

搭建主从延迟架构
步骤一：构建好正常的主从同步架构
基于传统方式/基于GTID方式

步骤二：调整从库配置，实现延迟功能
stop slave;
change master to master_delay=300;
start slave;

步骤三：主库中创建数据测试延迟功能
........

利用延迟从库修复数据

步骤一：模拟删除数据信息
create database xiaoB;  5
create database xiaoC;  6
create database xiaoD;  7
drop database oldboy;   8

步骤二：利用延迟从库修复数据
stop slave sql_thread; / stop slave;
mysqldump -B oldboy --set-gtid-purged=OFF >/backup/oldboy.sql
scp -rp /backup/oldboy.sql 10.0.0.51:/backup

mysql> set global sql_log_bin=0;
mysql> source /backup/oldboy.sql 
mysql> set global sql_log_bin=1;    
-- 在主库中恢复延迟中的数据信息

步骤三：将延迟从库进行恢复
start slave sql_thread; / start slave;

change master to master_delay=0;
-- 将延迟功能关闭
start slave until sql_before_gtids="xxxx5:7";
-- 只是将延迟事务中 5-7事务快速恢复
mysqldump -B oldboy --set-gtid-purged=OFF >/backup/oldboy.sql

start slave sql_thread; / start slave;

搭建数据库服务高可用架构

搭建高可用架构目的：实现数据服务主库异常后，可以将从库自动切换为主库；

搭建高可用主从架构：
架构环境规划 -- centos 7.9
主库    10.0.0.51    8.0.36  MHA-node
从库    10.0.0.52    8.0.36  MHA-node
从库    10.0.0.53    8.0.36  MHA-node 
mgr     10.0.0.54    8.0.36  MHA-node MHA-manager 

MHA-manager： 管理程序包  负责监控和控制主从架构 
MHA-node   ： 节点程序包  和管理程序进行对接


步骤一：在虚拟环境中部署安装数据库和MHA程序
主库安装程序信息
安装数据库8.0.36 程序 并实现程序正常运行 （二进制包）
安装MHA-node程序
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

从库安装程序信息
安装数据库8.0.36 程序 并实现程序正常运行 （二进制包）
安装MHA-node程序
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm


管理节点程序信息
安装数据库8.0.36 程序 不需要程序正常运行 （二进制包）
安装MHA-node程序
yum install perl-DBD-MySQL -y
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

yum localinstall -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

安装MHA-manager程序
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm 

yum localinstall -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm


步骤二：在数据库和管理节点中做高可用环境配置
准备好主从同步架构环境 -- GTID（推荐）
实现主从数据库之间互信 -- ssh互信      
主库操作 -- 10.0.0.51
rm -rf /root/.ssh 
ssh-keygen
cd /root/.ssh 
mv id_rsa.pub authorized_keys
scp  -r  /root/.ssh  10.0.0.52:/root 
scp  -r  /root/.ssh  10.0.0.53:/root
ssh 10.0.0.52 date
ssh 10.0.0.53 date
-- 以上操作完毕，表示52和53都信任了51

ssh 10.0.0.51 date
ssh 10.0.0.53 date
-- 以上操作完毕，表示51和53都信任了52

ssh 10.0.0.51 date
ssh 10.0.0.52 date
-- 以上操作完毕，表示51和52都信任了53

总结

01 数据库主从架构搭建
   1）数据库主从常见异常情况： IO线程异常（connecting/no） SQL线程异常（no）
   2）数据库主从架构搭建扩展：
      1）利用GTID方式可以实现主从架构搭建
      2）利用延迟从库实现主库数据修复  
02 数据库高可用架构搭建
   1）搭建高可用架构目的
   2）搭建高可用架构环境准备 （MHA-manger MHA-node）
   3）搭建高可用架构安装程序 （环境配置--主从配置/实现主从互信）