从零到一：手把手搭建高可用MySQL主从复制架构

任务背景真实案例某同学刚入职公司,在熟悉公司业务环境的时候,发现他们的数据库架构是一主两从,但是两台从数据库和主库不同步询问得知,已经好几个月不同步了,但是每 天会全库备份主服务器上的数据到从服务器上,由于数据量不是很大,所以一直没 有人处理主从不同步的问题这次正好问到了,于是就安排该同学处理一下这个主从不同步的问题案例背后的核心技术熟悉 MySQL 数据库常见的主从架构理解MySQL 主从架构的实现原理掌握 MySQL 主从架构的搭建任务场景随着业务量不断增长,公司对数据的安全性越来越重视,由于常规的备份不能够实 时记录下数据库的所有状态,为了能够保障数据库的实时备份冗余,希望将现有单 机数据库变成双机热备任务要求备份数据库搭建双机热备数据库架构 M-S任务目标了解什么是 MySQL 的 Replication理解 MySQL 的 Replication 的架构原理掌握 MySQL 的基本复制架构的搭建M-S 重点了解和掌握基于 GTIDs 的复制特点及搭建MySQL 集群概述集群的主要类型高可用集群(High Available Cluster,HA Cluster)高可用集群是指通过特殊的软件把独立的服务器连接起来,组成一个能够提供故障切换功能的集群如何衡量高可用可用性级别(指标)年度宕机时间描述叫法99%3.65天/年基本可用系统2个999.9%8.76小时/年可用系统3个999.99%52.6分钟/年高可用系统4个999.999%5.3分钟/年抗故障系统5个999.9999%32秒/年容错系统6个9计算方法$ 1 年 365 天 8760 小时 $$ 99 %8760 * 1 %8760 * 0.0187.6 小时 3.65 天 $$ 99.98760 * 0.1 %8760 * 0.0018.76 小时 $$ 99.998760 * 0.00010.876 小时 0.876 * 6052.6 分钟 $$ 99.9998760 * 0.000010.0876 小时 0.0876 * 605.26 分钟 $常用的集群架构MySQL Replication 主从架构MySQL Cluster 集群架构MySQL Group Replication (MGR) 多主一从MariaDB Galera ClusterMHA|Keepalived|HeartBeat|Lvs,HAproxy等技术构建高可用集群MySQL 复制什么是 MySQL 复制Replication可以实现将数据从一台数据库服务器(master)复制到一台到多台数据库服务器(slave)默认情况下,属于异步复制,所以无需维持长连接MySQL 复制原理简单来说,master将数据库的改变写入二进制日志,slave同步这些二进制日志, 并根据这些二进制日志进行数据重演操作,实现数据异步同步master:主slave:从详细描述:当主从同步配置完毕后:slave端的IO线程发送请求给master端的binlog dump线程master端binlog dump线程获取二进制日志信息(文件名和位置信息)发送给 slave端的IO线程salve端IO线程获取到的内容依次写到slave端relay log(中继日志)里,并把 master端的bin-log文件名和位置记录到master.info里salve端的SQL线程,检测到relay log中内容更新,就会解析relay log里更新的内容,并执行这些操作,从而达到和master数据一致relay log 中继日志作用记录从slave服务器接收来自主master服务器的二进制日志场景用于主从复制master主服务器将自己的二进制日志发送给slave从服务器,slave先保存在自己的中继日志中,然后再执行自己本地的relay log里的sql达到数据库更改和master保持一致如何开启默认中继日志没有开启可以通过修改配置文件完成开启。vimmy.cnf[mysqld]# 指定二进制日志存放位置及文件名relay-log/var/lib/mysql/relaylogMySQL 复制架构双机热备AB 复制默认情况下,master接收读写请求,slave只接收读请求以减轻master的压力级联复制优点:进一步分担读压力缺点:slave 1出现故障,后面的所有级联slave服务器都会同步失败并联复制(一主多从)优点:解决上面的slave1的单点故障,同时也分担读压力缺点:间接增加master的压力双主复制特点从名称上来看,两台master好像都能接受读写请求,但实际上,往往运作的过程中,同一时刻只有其中一台master会接收请求,另外一台只接收读请求MySQL 主从复制的搭建AB 复制传统 AB 复制架构说明在配置 MySQL 主从架构时必须保障数据库的版本一致统一版本为 8.0.46环境规划编号主机名称主机地址角色信息1master.it.cn192.168.19.134MASTER主服务器2slave.it.cn192.168.19.135SLAVE从服务器安装前准备工作第一步克隆两台全新的数据库服务器MASTER/SLAVE第二步首先启动MASTER然后启动SLAVE更改主机名称Masterhostnamectl set-hostname master.it.cnSlave:hostnamectl set-hostname slave.it.cn第三步更改静态IP地址把Master和Slave配置与规划一致Rocky 9.5Masternmcli con mod ens160 ipv4.address192.168.19.128 nmcli con up ens160Slavenmcli con mod ens160 ipv4.address192.168.19.129 nmcli con up ens160设置完成后重启网络然后使用shell或MX远程连接第四步由于两台机器处于集群架构需要相互连接。绑定主机名称与IP地址到/etc/hostsMaster/Slavevim/etc/hosts192.168.19.134 master.it.cn192.168.19.135 slave.it.cn第五步关闭防火墙与SElinuxsystemctl stop firewalld systemctl disable firewalld setenforce0vim/etc/selinux/configSELINUXdisable第六步配置yum源建议使用腾讯源(CentOS 7 配置yuminstallwget-ymv/etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOSBase.repo.backupwget-O/etc/yum.repos.d/CentOS-Base.repo http://mirrors.cloud.tencent.com/repo/centos7_base.repo yum clean all yum makecache第七步时间同步ntpdate ntp.aliyun.comMySQL 主从复制核心思路slave必须安装相同版本的mysql数据库软件master端必须开启二进制日志slave端必须开启relay log日志master端和slave端的server-id号不能一致slave端配置向master来同步数据master端必须创建一个复制用户保证master和slave端初始数据一致配置主从复制slave端MySQL 主从复制的具体实践master 下载安装 MySQL 并初始化安装需求选项值安装路径/usr/local/mysql数据路径/usr/local/mysql/data端口号3306master 的 mysql.sh 脚本#!/bin/bashyuminstalllibaio-ytar-xfmysql-8.0.46-linux-glibc2.28-x86_64.tar.xzrm-rf/usr/local/mysqlmvmysql-8.0.46-linux-glibc2.28-x86_64 /usr/local/mysqluseradd-r-s/sbin/nologin mysqlrm-rf/etc/my.cnfcd/usr/local/mysqlmkdirmysql-fileschownmysql:mysql mysql-fileschmod750mysql-files bin/mysqld--initialize--usermysql--basedir/usr/local/mysql/root/password.txt bin/mysqld--ssl--datadir/usr/local/mysql/data--usermysqlcpsupport-files/mysql.server /etc/init.d/mysqldservicemysqld startechoexport PATH$PATH:/usr/local/mysql/bin/etc/profilesource/etc/profilesourcemysql.shshell脚本其实就是命令的堆砌把一堆Linux命令写在同一个文件中一起执行安全配置mysql_secure_installation在确认输入密码后前面两个输入 N后面一直输入 Y 即可在 my.cnf 中做如下配置重点开启二进制是日志master 的 my.cnf 配置[mysqld]basedir/usr/local/mysqldatadir/usr/local/mysql/datasocket/tmp/mysql.sockport3306log-error/usr/local/mysql/data/master.err log-bin/usr/local/mysql/data/binlog# 一定要开启二进制日志server-id1character_set_serverutf8mb4# utf8mb4相当于utf8升级版在 slave 从服务器端安装 mysql 软件不需要初始化slave 的 my.cnf 配置[mysqld]basedir/usr/local/mysqldatadir/usr/local/mysql/datasocket/tmp/mysql.sockport3306log-error/usr/local/mysql/data/slave.err relay-log/usr/local/mysql/data/relaylog server-id2character_set_serverutf8mb4把master主服务器的数据目录同步到slave从服务器上把 master 服务器中的 mysqld 停掉servicemysqld stop把 master 服务器中的/usr/local/mysql/data 目录下的 auto.cnf 文件删除rm-rf/usr/local/mysql/data/auto.cnf每安装一个mysql软件其data数据目录都会产生一个auto.cnf文件里面是一个唯一性的编号相当于我们每个人的身份证号码把master服务器中的/usr/local/mysql中的data目录拷贝一份到slave从服务器的/usr/local/mysql目录rsync-av/usr/local/mysql/data root192.168.20.60:/usr/local/mysql同步完成后把主服务器与从服务器中的mysqld启动servicemysqld start配置 master-slave 主从同步在 master 主服务器中创建一个账户专门用于 实现数据同步createuserslave192.168.19.%identifiedbySlave123;grantreplicationslaveon*.*toslave192.168.19.%;fiushprivileges;在 master 中锁表然后 查看二进制文件的名称及位置flushtableswithreadlock;showmasterstatus;在 slave 从服务器中使用 change master to 指定主服务器并实现数据同步change master tomaster_host192.168.19.128,master_userslave,master_passwordSlave123.,master_log_filemysql-log.000017,master_log_pos157;MASTER_HOST主机的IP地址MASTER_USER主机的user账号MASTER_PASSWORD主机的user账号密码MASTER_PORT主机MYSQL端口号MASTER_LOG_FILE二进制文件名称MASTER_LOG_POS二进制文件位置主从复制的change master to语句不知道怎么写或记不住怎么办答求帮助mysql help change master to;启动 slave 数据同步startslave;showslavestatus\G从库 IO 和 SQL 进程都正常运行主从复制才算成功主 master 服务解锁unlocktables;常见问题解决方案问题一遇到以上错误时这表明从服务器副本在尝试从存储库初始化应用程序元数据结构时失败。以下是可能的原因及相应的解决办法元数据损坏原因从服务器上存储复制元数据的表通常是 mysql.slave_relay_log_info 和 mysql.slave_master_info可能已损坏。这可能是由于异常关机、磁盘故障或软件错误导致的。解决办法停止复制进程在从服务器上执行以下命令停止主从复制。stop slave;清空元数据手动清空相关的元数据表。DELETEFROMmysql.slave_relay_log_info;DELETEFROMmysql.slave_master_info;DELETEFROMmysql.slave_worker_info;重置从服务器重置从服务器的复制配置。RESET SLAVEALL;然后重新配置主从复制再次使用change master to命令指定主服务器的参数然后启动复制。启动同步startslave;showslavestatus\G问题二从以上错误可知从服务器在连接主服务器时因caching_sha2_password认证插件需要安全连接但当前连接并非安全连接从而导致认证失败。以下为你提供几种可行的解决办法修改主服务器上复制用户的认证插件可以把主服务器上复制用户的认证插件从caching_sha2_password改成mysql_native_password该插件不要求安全连接。ALTERUSERslave192.168.19.%IDENTIFIEDWITHmysql_native_passwordBYSlave123.;在从服务器上重启复制进程登录从服务器的 MySQL 客户端执行以下命令重启复制STOP SLAVE;STARTSLAVE;问题三slave 从服务器不小心写入数据解决方案正常情况下master 即可以读也可以写。但是 slave 从服务器只能执行读取操作。一旦我们在从服务器写入数据则主从架构会失败。slaveshowslavestatus\G遇到以上问题:如果数量较少,还可以通过当前语句的方式解决,但是如果从 服务器写入数据过多,则以上架构必须要重新搭建解决方案:如果由于人为操作或者其他原因直接将数据更改到从服务器导致数据同步失效,怎么解决?答:可以通过变量sql_slave_skip_counter 临时跳过事务进行处理SET GLOBAL sql_slave_skip_counter N N代表跳过N个事务举例说明SETGLOBALsql_slave_skip_counter1;stop slave;startslave;注意:跳过事务应该在slave上进行传统的AB复制方式可以使用变量:sql_slave_skip_counter,基于GTIDs的方式不支持基于 GTIDs 的 AB 复制架构M-SGTIDs 概什么是 GTIDs 以及有什么优点GTIDsGlobal transaction identifiers全局事务标识符是mysql 5.6新加入的一项技术当使用GTIDs时每一个事务都可以被识别并且跟踪添加新的slave或者当发生故障需要将master身份或者角色迁移到slave上时都无需考虑是哪一个二进制日志以及哪个position值极大简化了相关操作GTIDs是完全基于事务的因此不支持MYISAM存储引擎GTID由source_id和transaction_id组成source_id来自于server_uuid,可以在auto.cnf中看到transation_id是一个序列数字自动生成.使用GTIDs的限制条件有哪些不支持非事务引擎MyISAM因为可能会导致多个gtid分配给同一个事务create table … select 语句不支持主库语法报错create/drop temporary table 语句不支持必须使用enforce-gtid-consistency参数sql-slave-skip-counter不支持(传统的跳过错误方式)GTID复制环境中必须要求统一开启和GTID或者关闭GTID在mysql 5.6.7之前使用mysql_upgrade命令会出现问题基于 GTIDs 的主从复制在生产环境中大多数情况下使用的MySQL5.6基本上都是从5.5或者更低的版本升级而来这就意味着之前的mysql replication方案是基于传统的方式部署并且已经在运行因此接下来我们就利用已有的环境升级至基于GITDs的Replication〇 思路修改配置文件支持GTIDs (主从)重启数据库 (主从)为了保证数据一致性master和slave设置为只读模式 (主从)从服务器上重新配置同步 从基于 GTIDs 的主从复制实践修改配置文件支持 GTIDsmaster# vim my.cnf...gtid-modeonlog-slave-updates1enforce-gtid-consistencyslaverm-rfdata/binlog.*vimmy.cnfmy.cnflog-bin/usr/local/mysql/data/binlog// 必须要开启二进制gtid-modeonlog-slave-updates1enforce-gtid-consistency skip-slave-start// 当MASTER主服务器GTIDs没有0启动时,跳过SLAVE服务器的启动说明:1)开启GITDs需要在master和slave上都配置gtid-mode,log-bin,log-slaveupdates,enforce-gtid-consistency(该参数在5.6.9之前是–disable-gtidunsafe-statement)2)其次,slave还需要增加skip-slave-start参数,目的是启动的时候,先不要把 slave起来,需要做一些配置3)基于GTIDs复制从服务器必须开启二进制日志!重新启动 mysqld 服务systemctl restart mysqld主从配置只读模式setglobal.read_onlyON;slave 重新配置 change master tostop slave;reset slave;change mastertomaster_host192.168.19.128,master_userslave,master_passwordSlave123.,master_port3306,master_auto_position1;注意:1确保又复制用户2主要区别于传统复制的参数是:change master to master_host‘192.168.19.128’,master_user‘slave’,master_password‘Slave123.’,master_p ort3306,master_auto_position1;startslave;showslavestatus\G关闭主从服务器的只读模式setglobal.read_onlyOFF;测试验证往主服务器在写入部分数据验证一下insertintodb_fq.tb_studentvalues(null,j);select*fromdb_fq.tb_student;slave 从服务器不小心写入数据解决方案方法一重新同步data目录重新change master to…方法二跳过事务指定需要跳过的GTIDs编号SET GTID_NEXT‘aaa-bbb-ccc-ddd:N’;开始一个空事务BEGIN;COMMIT;使用下一个自动生成的全局事务IDSET GTID_NEXT‘AUTOMATIC’;举例说明:insertintodb_fq.tb_studentvalues(null,h);SETSESSION.GTID_NEXT23e8dd07-b12d-11ee-bcfa-000c29e151df:3/!/;stop slave;SETSESSION.GTID_NEXT23e8dd07-b12d-11ee-bcfa-000c29e151df:4/!/;BEGIN;commit;SETSESSION.GTID_NEXTAUTOMATIC;startslave;showslavestatus\G说明:需要跳过哪个事务,需要手动查看relaylog文件得到