07_MySQL主从架构实现

一、主从架构概述

1. 场景说明

某同学刚入职公司，在熟悉公司业务环境的时候，发现他们的数据库架构是一主两从，但是两台从数据库和主库不同步。询问得知，已经好几个月不同步了，但是每天会全库备份主服务器上的数据到从服务器上，由于数据量不是很大，所以一直没有人处理主从不同步的问题。这次正好问到了，于是乎就安排该同学处理一下这个主从不同步的问题。

主服务器对外提供业务数据，负责业务数据的增删改查操作。

从服务器默认不对外提供服务，和主服务器一样，都处于长时间运行状态，在运行过程中，从服务器会自动从主服务器拉取并同步数据，提供了一个在线热备解决方案。

2. 什么是主从复制？

主从复制可以实现将数据从一台数据库服务器（master）复制到一台到多台数据库服务器(slave)

默认情况下，属于异步复制，所以无需维持长连接。

解决问题：

① 数据实时备份

② 缓解服务器压力

简单来说：

master将数据库的改变写入二进制日志（Binary Log）；

slave同步这些二进制日志，并根据这些二进制日志进行数据重演操作，实现数据异步同步。

3. 主从复制原理（背诵）

binlog二进制日志 vs relaylog中继日志（负责把主服务器的DML在slave服务器重写执行一遍）

binlog保存了用户对数据库的增删改事务操作（SQL语句）、relaylog中继日志，当从服务器从主服务器拉取到二进制日志数据时，会首先写入到relaylog中继日志中。

详细描述：

前提：主服务器开启binlog二进制日志，从服务器开启relaylog中继日志。

① slave端的IO线程发送请求给master端的binlog dump线程

② master端binlog dump线程获取二进制日志信息(文件名和位置信息)发送给slave端的IO线程

③ slave端IO线程获取到的内容依次写到slave端relay log里，并把master端的bin-log文件名和位置记录到master.info里

④ slave端的SQL线程，检测到relay log中内容更新，就会解析relay log里更新的内容，并执行这些操作，从而达到和master数据一致

master：主服务器；slave：从服务器。

二、传统主从复制（AB复制）设计

环境准备

传统AB复制架构(M-S)，说明：mysql数据库，版本为8.x

环境说明：

IP	主机名	角色
192.168.50.213	node1	master(主)
192.168.50.214	node2	slave(从)

安装前准备：

① 安装必备软件，如：rsync dnf install -y rsync

② 配置IP、主机名

ip：nmcli

主机名: hostnamectl set-hostname 主机名

③ 配置IP与主机映射 => /etc/hosts

④ 关闭防火墙与SELinux

firewalld: systemctl stop firewalld

selinux:

永久关闭 /etc/selinux/config

临时关闭 setenforce 0

2. 搭建思路

1. master、slave必须安装相同版本的mysql数据库软件 2. master端必须开启二进制日志；slave端必须开启relay log中继日志 3. master端和slave端的server-id号不能一致 => my.cnf => server-id = 10 4. 同步master端数据之前，slave端删除data数据目录下的auto.cnf文件 => uuid编号（每个mysql实例唯一的） 5. slave端配指向master来源同步数据

master端必须创建一个复制用户

保证master和slave端初始数据一致

配置主从复制（slave端）

3. MySQL8主从复制实践

3.1 配置Master主服务器

安装MySQL软件，并对其初始化操作

#!/bin/bash

# 检查内存是否满足安装要求
free_mem=$(free -m | awk '/^Mem:/ {print $2}')
if [ $free_mem -lt 3500 ]; then
    echo "内存过小"
    exit 1
fi

# 检查cpu必须要大于2核
cpu_num=$(nproc)
if [ $cpu_num -lt 2 ]; then
    echo "CPU核数过少"
    exit 1
fi

# 磁盘/空间最少50G及以上
disk_size=$(df -h / | awk '/\/$/{print $4}'| tr -d G)
if [ $disk_size -lt 20 ]; then
    echo "磁盘空间过小"
    exit 1
fi

# 检查一下selinux是否开启，如果开启，需要关闭
sestatus=$(getenforce)
if [ $sestatus = "Enforcing" ]; then
    echo "selinux开启，需要关闭"
    sed -i.bak 's/^SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config
    setenforce 0
fi

# firewalld防火墙是否开启，如果开启，需要关闭
firewall_status=$(systemctl is-active firewalld)
if [ $firewall_status = "active" ]; then
    echo "firewalld开启，需要关闭"
    systemctl stop firewalld
    systemctl disable firewalld
fi

# 检查是否能远程连接和上网
if ! ping -c1 www.baidu.com &>/dev/null; then
    echo "无法连接网络"
    exit 1
fi

# 检查 mariadb 是否已安装，如果有则删除
rpm -qa | grep mariadb
if [ $? -eq 0 ];then
    dnf remove mariadb* -y
fi

rpm -qa | grep mysql
if [ $? -eq 0 ];then
    echo "卸载mysql服务"
    dnf remove mysql* -y
fi

if [ -f /etc/my.cnf ]; then
    rm -f /etc/my.cnf
fi

# 安装依赖
rpm -qa | grep libaio &>/dev/null
if [ $? -ne 0 ]; then
    echo "libaio libaio-devel 安装依赖"
    dnf install -y libaio libaio-devel &>/dev/null
fi

rpm -qa | grep expect &>/dev/null
if [ $? -ne 0 ]; then
    echo "expect  安装"
    dnf install -y expect &>/dev/null
fi

# 创建本地用户
id mysql &>/dev/null || useradd -r -s /sbin/nologin mysql

# 强制要求在/soft 有下载好的mysql安装二进制包
ls -l /soft | grep mysql  &>/dev/null
if [ $? -ne 0 ]; then
    echo "没有mysql安装包"
    exit 1
fi

if ss -tunpl | grep 3306 &>/dev/null;then
    echo "3306端口已被占用"
    pkill -9 mysqld
    rm -rf /usr/local/mysql
fi

# 安装的mysql名称
tarmysqlname=$(ls -l /soft | grep mysql | awk '{print $NF}')
mysqlname=$(echo $tarmysqlname | awk -F".tar" '{print $1}')

# 进入
cd /soft
tar xf $tarmysqlname -C /usr/local/

cd /usr/local

[ -d /usr/local/mysql ] && rm -rf /usr/local/mysql

mv $mysqlname mysql

cd mysql/

mkdir data

chown mysql.mysql data

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 2>&1 | tee /tmp/mysqld.log

initPass=$(awk '/password/{print $NF}' /tmp/mysqld.log)

echo "初始化密码：$initPass"

cat <<-MYSQL >/etc/my.cnf
[client]
user=root
password=Aa123456.

[mysql]
socket=/tmp/mysql.sock
prompt="\\u@\\h [\\d]> "

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8mb4

max_connections=300
thread_cache_size=4
wait_timeout=200
interactive_timeout=28800

#default_storage_engine=myisam
default_storage_engine=innodb

innodb_file_per_table=1
innodb_data_file_path=ibdata1:12M;ibdata2:100M:autoextend

innodb_log_file_size=50M
innodb_log_files_in_group=2
innodb_log_group_home_dir=/usr/local/mysql/data

slow_query_log=1
long_query_time=0.1
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log
#log_queries_not_using_indexes=1

#general_log=1
#general_log_file=/opt/3306/data/general.log

binlog_format=statement

server-id=1
MYSQL

cat <<-MYSQL >/etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=network.target

[Service]
Type=forking
User=mysql
Group=mysql

ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/data/mysqld.pid
ExecStop=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown

PIDFile=/usr/local/mysql/data/mysqld.pid

TimeoutSec=600

Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
MYSQL

systemctl daemon-reload
systemctl restart mysqld

expect <<EOF
spawn /usr/local/mysql/bin/mysqladmin -uroot password "Aa123456." -p
expect "password:"
send "$initPass\r"
expect "mysql>"
send "exit\r"

expect eof
EOF

grep '/usr/local/mysql/bin' /etc/profile &>/dev/null
if [ $? -ne 0 ];then
    echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
fi

echo -e "\033[1;32m安装成功\033[0m"
echo -e "\033[1;33m账号：root\033[0m"
echo -e "\033[1;33m密码：Aa123456.\033[0m"
echo -e "\033[1;33msource /etc/profile\033[0m"

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

3.2 配置Slave从服务器

只需要安装MySQL软件，不需要初始化，也不需要启动MySQL软件。

#!/bin/bash

# 检查内存是否满足安装要求
free_mem=$(free -m | awk '/^Mem:/ {print $2}')
if [ $free_mem -lt 3500 ]; then
    echo "内存过小"
    exit 1
fi

# 检查cpu必须要大于2核
cpu_num=$(nproc)
if [ $cpu_num -lt 2 ]; then
    echo "CPU核数过少"
    exit 1
fi

# 磁盘/空间最少50G及以上
disk_size=$(df -h / | awk '/\/$/{print $4}'| tr -d G)
if [ $disk_size -lt 20 ]; then
    echo "磁盘空间过小"
    exit 1
fi

# 检查一下selinux是否开启，如果开启，需要关闭
sestatus=$(getenforce)
if [ $sestatus = "Enforcing" ]; then
    echo "selinux开启，需要关闭"
    sed -i.bak 's/^SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config
    setenforce 0
fi

# firewalld防火墙是否开启，如果开启，需要关闭
firewall_status=$(systemctl is-active firewalld)
if [ $firewall_status = "active" ]; then
    echo "firewalld开启，需要关闭"
    systemctl stop firewalld
    systemctl disable firewalld
fi

# 检查是否能远程连接和上网
if ! ping -c1 www.baidu.com &>/dev/null; then
    echo "无法连接网络"
    exit 1
fi

# 检查 mariadb 是否已安装，如果有则删除
rpm -qa | grep mariadb
if [ $? -eq 0 ];then
    dnf remove mariadb* -y
fi

rpm -qa | grep mysql
if [ $? -eq 0 ];then
    echo "卸载mysql服务"
    dnf remove mysql* -y
fi

if [ -f /etc/my.cnf ]; then
    rm -f /etc/my.cnf
fi

# 安装依赖
rpm -qa | grep libaio &>/dev/null
if [ $? -ne 0 ]; then
    echo "libaio libaio-devel 安装依赖"
    dnf install -y libaio libaio-devel &>/dev/null
fi

# 创建本地用户
id mysql &>/dev/null || useradd -r -s /sbin/nologin mysql

# 强制要求在/soft 有下载好的mysql安装二进制包
ls -l /soft | grep mysql  &>/dev/null
if [ $? -ne 0 ]; then
    echo "没有mysql安装包"
    exit 1
fi

if ss -tunpl | grep 3306 &>/dev/null;then
    echo "3306端口已被占用"
    pkill -9 mysqld
    rm -rf /usr/local/mysql
fi

# 安装的mysql名称
tarmysqlname=$(ls -l /soft | grep mysql | awk '{print $NF}')
mysqlname=$(echo $tarmysqlname | awk -F".tar" '{print $1}')

# 进入
cd /soft
tar xf $tarmysqlname -C /usr/local/

cd /usr/local

[ -d /usr/local/mysql ] && rm -rf /usr/local/mysql

mv $mysqlname mysql

cd mysql/

mkdir data

chown mysql.mysql data

cat <<-MYSQL >/etc/my.cnf
[client]
user=root
password=Aa123456.

[mysql]
socket=/tmp/mysql.sock
prompt="\\u@\\h [\\d]> "

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
character_set_server=utf8mb4

max_connections=300
thread_cache_size=4
wait_timeout=200
interactive_timeout=28800

#default_storage_engine=myisam
default_storage_engine=innodb

innodb_file_per_table=1
innodb_data_file_path=ibdata1:12M;ibdata2:100M:autoextend

innodb_log_file_size=50M
innodb_log_files_in_group=2
innodb_log_group_home_dir=/usr/local/mysql/data

slow_query_log=1
long_query_time=0.1
slow_query_log_file=/usr/local/mysql/data/mysql-slow.log
#log_queries_not_using_indexes=1

#general_log=1
#general_log_file=/opt/3306/data/general.log

binlog_format=statement

relay-log=/usr/local/mysql/data/relaylog

server-id=2

MYSQL

cat <<-MYSQL >/etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=network.target

[Service]
Type=forking
User=mysql
Group=mysql

ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/data/mysqld.pid
ExecStop=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown

PIDFile=/usr/local/mysql/data/mysqld.pid

TimeoutSec=600

Restart=on-failure
RestartSec=5

[Install]
WantedBy=multi-user.target
MYSQL

grep '/usr/local/mysql/bin' /etc/profile &>/dev/null
if [ $? -ne 0 ];then
    echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
fi

echo -e "\033[1;32m安装成功\033[0m"
echo -e "\033[1;33msource /etc/profile\033[0m"

注意事项：

修改配置前先备份原文件，尤其是 SSH、Nginx、数据库和 Kubernetes 生产配置。

3.3 启动Master并创建同步账号

在master主数据库中，创建同步账号

mysql> CREATE USER 'slave'@'192.168.50.214' IDENTIFIED BY 'Aa123456.';

授予用户slave REPLICATION SLAVE权限和REPLICATION CLIENT权限，用于在主从库之间同步数据。

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.50.214';
mysql> flush privileges;

权限的目的是为了读取二进制日志以及拉取二进制中的数据信息
REPLICATION SLAVE        让 Slave 节点连接 Master 并读取 binlog，实现数据同步。
REPLICATION CLIENT       查看复制状态信息（如 binlog 位置、延迟），用于监控和诊断。

root@localhost [(none)]> use mysql;
root@localhost [mysql]> select host,user from user;
+----------------+------------------+
| host           | user             |
+----------------+------------------+
| 192.168.50.214 | slave            |
| localhost      | mysql.infoschema |
| localhost      | mysql.session    |
| localhost      | mysql.sys        |
| localhost      | root             |
+----------------+------------------+
5 rows in set (0.00 sec)

3.4 rsync同步data目录

在 master 节点上，执行

# 1、停止 master服务器 上的mysql
systemctl stop mysqld

# 2、rsync 同步 data 目录
rsync -azv /usr/local/mysql/data root@node2:/usr/local/mysql/

命令作用：

使用 systemctl 对 mysqld 服务执行 stop 操作：启动、停止、重启、查看状态或设置开机自启。

执行结果：

成功后会对 mysqld 服务执行 stop 操作；restart 会造成短暂中断，enable 影响开机自启。

关键参数：

注意事项：


**启动master和slave数据库**

master:

关键参数：

注意事项：



+---------------+----------+--------------+------------------+-------------------+
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      1207|              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

在slave服务器上，执行以下操作：

配置主从信息，但还未开始真正同步。

SOURCE_HOST='192.168.50.213', SOURCE_USER='slave', SOURCE_PASSWORD='Aa123456.', SOURCE_LOG_FILE='binlog.000003', SOURCE_LOG_POS=157, GET_MASTER_PUBLIC_KEY=1, MASTER_SSL=0;


在slave服务器上启动同步

        ......

回到master主服务器，在mysql里面，进行解锁操作



master中创建数据库、数据表并插入数据


);

在slave端验证：


![](/assets/feishu-images/48272891ec4b806609fb30ea.png)

特别注意：一旦两者配置为主从以后，主master节点既可以读数据也可以写数据。

但是一定要注意slave服务器只能读数据，不能写数据，一旦写入，集群主从马上报错！！！




Mysql数据库服务从3.x版本就开始提供复制的功能，复制是指将主数据库的`DdDL`和`DML`操作语句通过⼆进制⽇志传到复制服务器上；

然后在从库上(复制服务器)对这些⽇志重新执⾏(也叫重做)，从⽽使得从库和主库的数据保持同步；

Mysql⽀持⼀台主库同时向多台从库进⾏复制，从库同时也可以作为其他服务器的主库，实现链状的复制；

Mysql复制的优点主要包含以下3个方面：


由于Mysql实现的是异步的复制，所以主从库之间存在⼀定的差距，在从库上进⾏的查询操作需要考虑到这些数据的差异，







**前置操作**








脚本化安装，要初始化和启动

关键参数：

注意事项：


**注意事项：**

注意事项：




###############################################################

  MASTER_HOST='192.168.50.213',
  MASTER_USER='slave',
  MASTER_PASSWORD='Aa123456.',
  MASTER_PORT=3306,
  SOURCE_LOG_FILE='binlog.000002',
  SOURCE_LOG_POS=1076,
  GET_MASTER_PUBLIC_KEY=1, 
  MASTER_SSL=0,
  MASTER_CONNECT_RETRY=10;

注意事项：

);

在进行主从同步时，忽略某些指定的库不去同步。

/etc/my.cnf

[mysqld]

replicate_ignore_db=test1 replicate_ignore_db=test2


**注意事项：**



2

2

2

注意事项：