简单易懂！MySQL主从复制的搭建指南

最编程 2024-08-09 22:43:04

...

MySQL 主从（MySQL replication），主要用于 MySQL 的实时备份或者读写分离。主从复制可以将 MySQL 主数据库中的数据实时复制到一个或多个 MySQL 从数据库中。

MySQL 复制首先将 MySQL 主数据库(master) 的数据通过 binlog 日志的方式经过网络发送到一台或多台 MySQL 从数据库上(slave)，然后在 slave 上重放传送过来的日志（relay log），以达到和 master 数据同步的目的。

首先确保 master 数据库上开启了二进制日志，这是复制的前提。

在 slave 准备开始复制时，首先要执行 change master to 语句设置连接到 master 服务器的连接参数，在执行该语句的时候要提供一些信息，包括如何连接和要从哪复制 binlog，这些信息在连接的时候会记录到 slave 的 datadir 下的 master.info 文件中，以后再连接 master 的时候将不用再提供这新信息而是直接读取该文件进行连接。（当然也可以基于 GTID 的方式，就不需要指定 binlog 文件和 position 了，只需要指定一个全局唯一的 GTID）。
在 slave 上有两种线程，分别是 IO 线程和 SQL 线程。

IO 线程用于连接 master，监控和读取 master 的 binlog。当启动 IO 线程成功连接 master 时，master 会同时启动一个 dump 线程，该线程将 slave 请求要复制的 binlog 给 dump 出来，之后 IO 线程负责监控并接收 master 上 dump 出来的 binlog 日志，当 master 上 binlog 有变化的时候，IO 线程就将其复制过来并写入到自己的中继日志(relay log)文件中。
slave上的另一个线程 SQL 线程用于监控、读取并重放 relay log 中的日志，将数据写入到自己的数据库中。

站在 slave 的角度上看，过程如下：

网络异常，图片无法展示

本文将会介绍两种同步方式：

1.基于 binlog + postion 的传统的同步方式。
2.基于 GTID 的同步方式（推荐）。

在同步之前会先往主数据库中插入数据，然后分别介绍在建立主从之前通过 mysqldump 和 xtrabackup 全量同步数据的两种方式。当然你可以直接建立主从以后再开始写入数据，这样就可以省略全量同步这个步骤了。

传统异步主从复制

机器规划

主机名	IP地址	端口号	角色
mysql-master	192.168.1.36	3306	master（主库）
mysql-slave	192.168.1.37	3306	slave（从库）

准备工作

准备工作在主库和从库的服务器上都要执行。

创建相关目录

#创建用户
userdel -r mysql
groupadd mysql 
useradd -r -g mysql -s /bin/false mysql 
#创建目录
# /mysql/app/                                   MySQL 数据库软件根目录
# /mysql/data/3306/data/                        MySQL 数据文件目录
# /mysql/log/3306/binlog                        MySQL 二进制日志目录
# /mysql/log/3306/relaylog                      MySQL 中继日志目录
# /mysql/backup/3306/xtrabackup/target_dir      MySQL xtrabackup物理备份目录
# /mysql/backup/3306/mysqldump                  MySQL mysqldump逻辑备份目录
# /mysql/script                                 MySQL 常用脚本存放目录
mkdir -p /mysql/app/                                    
mkdir -p /mysql/data/3306/data/                                 
mkdir -p /mysql/log/3306/binlog                                 
mkdir -p /mysql/log/3306/relaylog                               
mkdir -p /mysql/backup/3306/xtrabackup/target_dir               
mkdir -p /mysql/backup/3306/mysqldump                           
mkdir -p /mysql/script                                          
#给目录授权
chown -R mysql:mysql /mysql

下载并解压 MySQL 安装包

MySQL 压缩包下载地址：https://dev.mysql.com/downloads/mysql/5.7.html

#解压压缩包
tar zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz -C /mysql/app
mv /mysql/app/mysql-5.7.29-linux-glibc2.12-x86_64 /mysql/app/mysql
chown -R mysql:mysql /mysql

配置环境变量

##将MySQL目录添加环境变量##
cat >> ~/.bash_profile <<-EOF
export PATH=$PATH:/mysql/app/mysql/bin
EOF
source ~/.bash_profile

初始化主库

主库配置文件，主库需要指定 log_bin 开启 binlog 以及设置 server_id。

#主机名和端口号作为目录名的一部分
HostName=`hostname`
MySql_Port=3306
#IP地址
Ip=192.168.1.36
#master server_id 要和 slave 不一样
Server_Id=1
cat > /mysql/data/$MySql_Port/my.cnf <<-EOF
#------------------------------------ 
#客户端设置
#------------------------------------
[client]
port=$MySql_Port
socket =/mysql/data/$MySql_Port/mysql.sock
default-character-set=utf8 
#------------------------------------ 
#mysql连接工具设置
#------------------------------------
[mysql]
prompt="\\u@\\h \\d \\r:\\m:\\s>" #登录时显示登录的用户名、服务器地址、默认数据库名、当前时间
auto-rehash #读取表信息和列信息，可以在连上终端后开启tab补齐功能。
default-character-set=utf8 #默认字符集
#------------------------------------ 
#基本设置
#------------------------------------
[mysqld]
bind_address=0.0.0.0  #监听本地所有地址
port=$MySql_Port  #端口号
user=mysql  #用户
basedir=/mysql/app/mysql  #安装路径
datadir=/mysql/data/$MySql_Port/data  #MySQL数据目录
socket=/mysql/data/$MySql_Port/mysql.sock #用于本地连接的socket文件目录
pid-file=/mysql/data/$MySql_Port/mysql.pid #进程ID文件的目录。
character-set-server=utf8 #默认字符集
#------------------------------------ 
#log setting 日志设置
#------------------------------------
long_query_time=10 #慢查询时间，超过 10 秒则认为是慢查询
slow_query_log=ON #启用慢查询日志
slow_query_log_file=/mysql/log/$MySql_Port/${HostName}-query.log #慢查询日志目录
log_queries_not_using_indexes=1 #记录未使用索引的语句
log_slow_admin_statements=1 #慢查询也记录那些慢的optimize table，analyze table和alter table语句
log-error=/mysql/log/$MySql_Port/${HostName}-error.log #错误日志目录
#------------------------------------ 
#master modify parameter 主库复制更改参数
#------------------------------------
server_id=$Server_Id #master和slave server_id 需要不同
#二进制日志参数配置
log_bin=/mysql/log/$MySql_Port/binlog/${HostName}-binlog  #binlog目录
log_bin_index=/mysql/log/$MySql_Port/binlog/${HostName}-binlog.index  #指定索引文件的位置
binlog_format=row #行模式复制，默认是 row
binlog_rows_query_log_events=on #在 row 模式下，开启该参数,可以将把 sql 语句打印到 binlog 日志里面，方便查看
binlog_cache_size=1M #事务能够使用的最大 binlog 缓存空间。
max_binlog_size=2048M #binlog 文件最大空间，达到该大小时切分文件
expire_logs_days=7 #设置自动删除 binlog 文件的天数。
sync_binlog=1 #表示每次事务的 binlog 都会fsync持久化到磁盘，MySQL 5.7.7 之后默认为1，之前的版本默认为0
innodb_flush_log_at_trx_commit=1 #表示每次事务的 redo log 都直接持久化到磁盘，默认值为1
EOF

初始化主库：

mysqld \
--defaults-file=/mysql/data/3306/my.cnf \
--initialize --user=mysql \
--basedir=/mysql/app/mysql \
--datadir=/mysql/data/3306/data

配置 MySQL 启动脚本：

cp /mysql/app/mysql/support-files/mysql.server /etc/init.d/mysql_3306
ln -sf /etc/init.d/mysql_3306 /usr/lib/systemd/system/mysql_3306
#修改启动脚本##
vi /etc/init.d/mysql_3306
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
mysqld_pid_file_path=/mysql/data/3306/mysql.pid
#在$bindir/mysqld_safe 后面添加，注意 --defaults-file 要放在第一个
--defaults-file="/mysql/data/3306/my.cnf" 
systemctl daemon-reload

启动 MySQL，修改密码，运行远程登录：

#启动、MySQL服务
systemctl start mysql_3306
#获取MySQL临时密码
Passwd=`cat /mysql/log/3306/*-error.log |grep "root@localhost:"|awk -F ' ' '{print $11}'`
echo $Passwd
#通过本地 socket 登录、修改密码
mysql -uroot -p$Passwd -S /mysql/data/3306/mysql.sock
alter user 'root'@'localhost' identified by  "123456";
#允许远程登录
grant all privileges on *.* to root@'%' identified by '123456';
#刷新权限
flush privileges;

主库插入数据

使用存储过程每 5 秒往主库插入 10 条数据，模拟生产环境。

create database yzjtestdb;
drop table yzjtestdb.data01; 
create table yzjtestdb.data01( 
id int not null primary key auto_increment, 
name varchar(60), 
age int); 
use yzjtestdb; 
drop procedure sp_data01_2; 
#临时指定结束符为 //，因为存储过程中语句以;结束
delimiter // 
create procedure sp_data01_10() 
begin
declare n int; 
set n=0; 
repeat 
insert 
into 
yzjtestdb.data01(name,age) 
values(concat('samkeji1',n),22); 
commit; 
set n=n+1; 
until n>=10 end repeat; 
end 
//
delimiter ;  #恢复结束符
#开启事件调度器
set global event_scheduler =1; 
#每5秒执行一次存储过程，插入10条数据
create event if not exists e_data01_10 
on schedule every 5 second 
on completion preserve 
do
call sp_data01_10(); 
#5小时清空表
create event if not exists e_data01_5_truncate 
on schedule every 5 hour 
on completion preserve 
do
truncate table yzjtestdb.data01; 
#当为on completion preserve 的时候,当event到期了,event会被disable,但是该event还是会存在
#当为on completion not preserve的时候,当event到期的时候,该event会被自动删除掉.
alter event e_data01_10 on completion preserve enable; 
alter event e_data01_5_truncate on completion preserve enable; 
#停止存储过程
#alter event yzjtestdb.e_data01_10 ON COMPLETION PRESERVE DISABLE;

查看插入的数据：

mysql> select count(*) from yzjtestdb.data01;
+----------+
| count(*) |

						
							上一篇：							
								理解Hex格式与Mot格式：一份简易指南							
						
						
							下一篇：							
								超级省电模式！Skip-Attention：让Transformer计算量大减的高效轻量化方案

简单易懂！MySQL主从复制的搭建指南

传统异步主从复制

机器规划

准备工作

创建相关目录

下载并解压 MySQL 安装包

配置环境变量

初始化主库

主库插入数据

VSCode搭建TypeScript环境全面解析：简单易懂的指南

简单易懂！ipvsadm命令的实用指南

简单易懂的ipvsadm命令使用指南

简单易懂的v4l2-ctl使用指南

简单易懂！MySQL主从复制的搭建指南

简单易懂！LNMP环境搭建指南

简单易懂！用docker-compose搭建LNMP环境的步骤解析

简单易懂！LNMP环境的安装和配置指南

简单易懂的SPSS多元线性回归步骤指南

简单易懂的SPSS多元线性回归残差分析指南