MySQL5.7主从复制教程

? 简述：主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的 业务数据库、事务处理库，从库做查询库。

? 复制过程简单的说就是 master 将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志行数据操作

1、什么是主从复制

? 主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的业务数据库。在最常用的mysql数据库中，支持单向、异步复制。在复制过程中，一个服务器充当主服务器，而另外一台服务器充当从服务器；此时主服务器会将更新信息写入到一个特定的二进制文件中。

? 并会维护文件的一个索引用来跟踪日志循环。这个日志可以记录并发送到从服务器的更新中去。当一台从服务器连接到主服务器时，从服务器会通知主服务器从服务器的日志文件中读取最后一次成功更新的位置。然后从服务器会接收从哪个时刻起发生的任何更新，然后锁住并等到主服务器通知新的更新。

2、主从复制的作用

1. 是确保数据安全；做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据的丢失。

2. 是提升I/O性能；随着日常生产中业务量越来越大,I/O访问频率越来越高，单机无法满足，此时做多库的存储，有效降低磁盘I/O访问的频率，提高了单个设备的I/O性能。

3. 是读写分离，使数据库能支持更大的并发；在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

3、主从复制的原理三步曲进行：

主数据库有个bin log二进制文件，记录了所有增删改SQL语句。（binlog线程）

从数据库把主数据库的bin log文件的SQL语句复制到自己的中继日志relay log（io线程）

从数据库的relay log重做日志文件，再执行一次这些sql语句。（sql执行线程）

一、环境描述

服务器优化注意事项

Ⅰ、服务器的大磁盘必须挂载到/opt目录下，服务的目录以及数据都保存在/opt/software下

Ⅱ、磁盘挂载必须默认写入/etc/fstab

首先在两个服务器上创建两个数据库

操作系统	IP地址	数据库	数据库版本
CentOS 7.9	1.0.0.11	MySQL主	5.7.38
CentOS 7.9	1.0.0.12	MySQL从	5.7.38
CentOS 7.9	1.0.0.13	MySQL从	5.7.38

二、Linux基础配置

# 查看服务器版本
cat /etc/redhat-release
# 查看服务器磁盘空间使用情况
df -h
# 新建文件夹package存放压缩包
mkdir /opt/package
# 新建文件夹software存放解压后的软件
mkdir /opt/software
# 新建文件夹存放项目
mkdir /opt/project
# 新建文件夹apr存放文件
mkdir /opt/software/apr
# 新建文件夹apr-util存放文件
mkdir /opt/software/apr-util

关闭防火墙

# 查看防火墙状态
firewall-cmd --state
# 关闭防火墙（重启服务器会开启）
systemctl stop firewalld.service
# 永久关闭防火墙
systemctl disable firewalld.service

关闭SELinux

# 关闭SELinux修改配置文件需要重启机器：
# 修改/etc/selinux/config 文件
vim /etc/selinux/config
将SELINUX=enforcing 改为：SELINUX=disabled
# 重启机器生效
reboot

配置IP、主机名映射

vim /etc/hosts
1.0.0.11 nwtest1
1.0.0.12 nwtest2
1.0.0.13 nwtest3

设置时间同步

# 联网
# 设置东八区时区为当前时区
rm -rf /etc/localtime
cp /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
# 手动同步下网络时间
ntpdate -u cn.pool.ntp.org
# 内网状态时间设置
# 查看当前系统时间
date
# 查看硬件时间
hwclock
# 修改当前系统时间
date -s "2023-1-1 8:08:08"
# 修改硬件时间
hwclock --set --date "2023-1-1 8:08:08"
# 同步系统时间和硬件时间
hwclock --hctosys
# 保存时钟
clock -w
# 重启机器生效
reboot

三、安装MySQL5.7.38

Master/Slave部署??

主从服务器均需要进行本MySQL安装步骤！！！

部署路径：/opt/software/mysql

1、安装MySQL所需的yum源

yum -y install gcc-c++ ncurses-devel cmake make perl gcc autoconf automake zlib libgcrypt libtool bison
# 因为CentOS7操作系统默认安装mariadb-libs库，它与MySQL冲突，影响初始化
yum -y remove mariadb-libs

2、创建MySQL组以及MySQL用户

# 创建MySQL组
groupadd mysql
# 创建MySQL用户
useradd -g mysql mysql
# 设置密码
passwd mysql
# 本机设置为:
123456

3、解压MySQL文件包

# 将MySQL安装包放在opt目录下
cd /opt/package
# 在当前目录解压mysql安装包
tar -zxvf mysql-5.7.38.tar.gz -C /opt/software/
# 修改MySQL目录名称
mv /opt/software/mysql-5.7.38-linux-glibc2.12-x86_64 /opt/software/mysql

4、安装MySQL数据库

# 进行mysql安装目录下
cd /opt/software/mysql
# 新建文件夹并授权
mkdir -p /opt/software/mysql/data
mkdir -p /opt/software/mysql/logs
echo "" > /opt/software/mysql/logs/mysql_err.log
mkdir -p /opt/software/mysql/logbin
mkdir -p /opt/software/mysql/tmp
chown -R mysql:mysql /opt/software/mysql
chmod +x /opt/software/mysql

修改配置文件

1>vim /etc/my.cnf 或 cat > /etc/my.cnf <<EOF

cat > /etc/my.cnf <<EOF
# 客户端设置,即客户端默认的连接参数
[client]
# 默认连接端口
port = 6603
# 程序与mysqlserver处于同一台机器,发起本地连接时可用
socket = /opt/software/mysql/mysql.sock
# 数据库字符集
default_character_set = utf8mb4
# 服务端基本设置
#主从同步配置
[mysqld]
# 本机数据库 ID 标示，主从配置中ID要唯一
server_id = 1
# MySQL监听端口
port = 6603
# 为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
socket = /opt/software/mysql/mysql.sock
# pid文件所在目录
pid_file = /opt/software/mysql/mysql.pid
# 使用该目录作为根目录（安装目录）
basedir = /opt/software/mysql
# 数据文件存放的目录
datadir = /opt/software/mysql/data
# MySQL存放临时文件的目录
tmpdir = /opt/software/mysql/tmp
# 服务端默认编码（数据库级别）
character_set_server = utf8mb4
# 服务端默认的比对规则,排序规则
collation_server = utf8mb4_general_ci
# 错误日志路径
log_error = /opt/software/mysql/logs/mysql_err.log
# 开启二进制日志功能,binlog数据位置
log_bin = /opt/software/mysql/logbin/mysql_bin
# 混合模式复制
binlog_format = mixed
# 超过7天的binlog删除
expire_logs_days = 7
# binlog每个日志文件大小
max_binlog_size = 1G
# 这个表示只同步某个库, 二进制需要同步的数据库名 （如果没有此项，表示同步所有的库）
# binlog-do-db = xxxx
# 不可以被从服务器复制的库
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schem
### 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
### 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
### 控制binlog的写入频率。每执行多少次事务写入一次
### 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失，为0表示不控制
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# 指定是否开启慢查询日志
slow_query_log = 1
# 指定慢日志文件存放位置,可以为空,系统会给一个缺省的文件host_name_slow.log
slow_query_log_file = /opt/software/mysql/logs/slow.log
# 设定慢查询的阀值,超出次设定值的SQL即被记录到慢查询日志
long_query_time = 8
# 参数 log_output 指定了慢查询输出的格式,默认为 FILE,你可以将它设为 TABLE,然后就可以查询 mysql 架构下的 slow_log表了
log_output = TABLE
# 如果运行的SQL语句没有使用索引,则mysql数据库同样会将这条SQL语句记录到慢查询日志文件中
log_queries_not_using_indexes
# 跳过外部锁定,防止文件目录不可用,该选项默认开启
skip_external_locking
# 禁用域名DNS查找,不能在mysql的授权表中使用主机名或域名,只能使用IP或localhost
skip_name_resolve
# 表名的大小写敏感选项,默认为0,即开启大小写敏感,1为大小写不敏感,看需求设置
lower_case_table_names = 1
# MySQL的最大连接数,如果服务器的并发连接请求量比较大,建议调高此值
max_connections = 3000
# 指定MySQL可能的连接数量。当MySQL主线程在很短的时间内得到非常多的连接请求,该参数就起作用
back_log = 1024
# 对于同一主机,如果有超出该参数值个数的中断错误连接,则该主机将被禁止连接。如需对该主机进行解禁,执行：FLUSH HOST
max_connect_errors = 6000
# mysql支持的基本语法及校验规则
sql_mode = 'ANSI_QUOTES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
# 指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能
key_buffer_size = 256M
# 表描述符缓存大小,可减少文件打开/关闭次数
table_open_cache = 1024
# MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段
sort_buffer_size = 256M
# 读查询操作所能使用的缓冲区大小。和 sort_buffer_size 一样,该参数对应的分配内存也是每连接独享！
read_buffer_size = 32M
# 线程池缓存大小
thread_cache_size = 64
# MySQL的随机读缓冲区大小
read_rnd_buffer_size = 32M
# 指定MySQL 查询缓冲区的大小
query_cache_size = 64M
# 明确时间戳默认null方式
explicit_defaults_for_timestamp = true
# 指定单个查询能够使用的缓冲区大小,默认1M
query_cache_limit = 5M
# 默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:4000M;ibdata2:2000M:autoextend
# InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘 I/O。
innodb_buffer_pool_size=2G
# 此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据。MySQL开发人员建议设置为1－8M之间
innodb_log_buffer_size = 4M
# 此参数确定数据日志文件的大小,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间
innodb_log_file_size = 32M
# 为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
innodb_log_files_in_group = 3
# innodb主线程刷新缓存池中的数据,使脏数据比例小于90%
innodb_max_dirty_pages_pct = 90
# InnoDB事务在被回滚之前可以等待一个锁定的超时秒数。
innodb_lock_wait_timeout = 120 
# 批量插入缓存大小, 这个参数是针对MyISAM存储引擎来说的。适用于在一次性插入100_1000+条记录时, 提高效率。默认值是8M。可以针对数据量的大小,翻倍增加。
bulk_insert_buffer_size = 8M
# MyISAM设置恢复表之时使用的缓冲区的尺寸,当在REPAIR TABLE或用CREATE INDEX创建索引或ALTER TABLE过程中排序 MyISAM索引分配的缓冲区
myisam_sort_buffer_size = 16M
# 如果临时文件会变得超过索引,不要使用快速排序索引方法来创建一个索引。注释：这个参数以字节的形式给出
myisam_max_sort_file_size = 10G
# 如果该值大于1,在Repair by sorting过程中并行创建MyISAM表索引(每个索引在自己的线程内)
myisam_repair_threads = 1
# 服务器关闭交互式连接前等待活动的秒数。默认值：28800秒（8小时）
interactive_timeout = 28800
# 限制server接受的数据包大小;指代mysql服务器端和客户端在一次传送数据包的过程当中数据包的大小
max_allowed_packet = 256M

[mysqldump]
# 支持较大数据库的转储,在导出非常巨大的表时需要此项。增加该变量的值十分安全,这是因为仅当需要时才会分配额外内存。
# 例如,仅当你发出长查询或mysqld必须返回大的结果行时mysqld才会分配更多内存。
# 该变量之所以取较小默认值是一种预防措施,以捕获客户端和服务器之间的错误信息包,并确保不会因偶然使用大的信息包而导致内存溢出。
# 如果你正是用大的BLOB值,而且未为mysqld授予为处理查询而访问足够内存的权限,也会遇到与大信息包有关的奇怪问题。
# 如果怀疑出现了该情况,请尝试在mysqld_safe脚本开始增加ulimit _d 256000,并重启mysqld。
quick
# 限制server接受的数据包大小;指代mysql服务器端和客户端在一次传送数据包的过程当中数据包的大小
max_allowed_packet = 256M
# TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行
net_buffer_length = 16384

[mysql]
# auto_rehash是自动补全的意思
auto_rehash

# isamchk数据检测恢复工具
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

EOF

2>初始化数据库

cd /opt/software/mysql/
# 初始化数据库
bin/mysqld --initialize --user=mysql --basedir=/opt/software/mysql --datadir=/opt/software/mysql/data
# 查看MySQL初始密码
grep 'temporary password' /opt/software/mysql/logs/mysql_err.log

5、启动MySQL服务

cd /opt/software/mysql/support-files/
# 然后启动MySQL
./mysql.server start
# 登录MySQL
cd /opt/software/mysql/bin/
./mysql -uroot -p
# 输入密码，初始化产生的密码。进入界面
# 修改root密码
mysql> alter user 'root'@'localhost' identified by 'root';
# 允许远程机器连接
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
# 立即生效
mysql> flush privileges;
# 退出
mysql> quit;

6、配置环境变量

# 修改配置文件
vim /etc/profile
# 在最后面加入
export MYSQL_HOME=/opt/software/mysql
export PATH=$PATH:$MYSQL_HOME/bin
# 使配置文件立即生效
source /etc/profile

7、将MySQL设置为开机自启

# 将 mysql.server 文件复制到 /etc/rc.d/init.d/ 目录下
cp /opt/software/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
# 赋予可执行权限
chmod +x /etc/init.d/mysqld
# 添加为服务
chkconfig --add mysqld
# 查看服务列表
chkconfig --list
# 看到3、4、5状态为开或者为 on 则表示成功,如果是 关或者 off 则执行一下
chkconfig --level 345 mysqld on
# 重启服务器
reboot
# 再次查看服务列表或者查看6603端口号
netstat -na | grep 6603

MySQL常用命令

# 启动MySQL服务
service mysqld start
systemctl start mysqld
# 关闭MySQL服务
service mysqld stop
systemctl stop mysqld
# 查看MySQL状态
service mysqld status
systemctl status mysqld

四、配置MySQL主从

1、Master配置

1、修改主服务器my.cnf

修改/etc/my.cnf,在[mysqld]模块下添加以下内容

# 主从同步配置
[mysqld]
# 本机数据库 ID 标示，主从配置中ID要唯一
server_id = 1
# MySQL监听端口
port = 6603
# 为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
socket = /opt/software/mysql/mysql.sock
# pid文件所在目录
pid_file = /opt/software/mysql/mysql.pid
# 使用该目录作为根目录（安装目录）
basedir = /opt/software/mysql
# 数据文件存放的目录
datadir = /opt/software/mysql/data
# MySQL存放临时文件的目录
tmpdir = /opt/software/mysql/tmp
# 服务端默认编码（数据库级别）
character_set_server = utf8mb4
# 服务端默认的比对规则,排序规则
collation_server = utf8mb4_general_ci
# 错误日志路径
log_error = /opt/software/mysql/logs/mysql_err.log
# 开启二进制日志功能,binlog数据位置
log_bin = /opt/software/mysql/logbin/mysql_bin
# 混合模式复制
binlog_format = mixed
# 超过7天的binlog删除
expire_logs_days = 7
# binlog每个日志文件大小
max_binlog_size = 1G
# 这个表示只同步某个库, 二进制需要同步的数据库名 （如果没有此项，表示同步所有的库）
# binlog-do-db = xxxx
# 不可以被从服务器复制的库
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schem
### 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
### 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
### 控制binlog的写入频率。每执行多少次事务写入一次
### 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失，为0表示不控制
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1 

2、修改完配置，重启主库的MySQL

service mysqld restart

3、登录MySQL

mysql -uroot -proot

4、主数据库授权从数据库，授权从数据库能够读取主数据库二进制文件

#创建同步用户slave，用户名slave，密码slave@2023
mysql> create user 'slave'@'%' identified by 'slave@2023';
#给同步账户赋予复制权限
mysql> grant replication slave on *.* to 'slave'@'%' identified by 'slave@2023';
#刷新权限
mysql> flush privileges;
# 查看主库的状态
mysql> show master status;

说明：??

replication 复制权限，也可以是all；

slave 表示从库；

*. 表示同步所有数据库，也可以指定某一个库；

slave 用户仅是mysql数据库的一个认证用户；

% 全网都能登陆访问，可指定也可以设置成某一个网段都可以访问。

2、Slave配置

1、修改从服务器my.cnf

修改/etc/my.cnf,在[mysqld]模块下添加以下内容

# 修改/etc/my.cnf,在[mysqld]模块下添加以下内容
# 主从同步配置
#这里server-id的值可以是除1之外的任意数字，切记不能为1
server-id=2
# 其他内容和主库保持一致即可

2、修改完配置，重启主库的MySQL

service mysqld restart

3、执行主从初始化

# 登录MySQL
mysql -uroot -proot
# 在mysql命令行输入指令，执行初始化
change master to master_host='1.0.0.11',master_user='slave',master_password='slave@2023',master_port=6603;
# 开启从库(stop slave：关闭从库）
mysql> start slave;
# 查看状态
mysql> show slave status\G;

说明：??

master_host='1.0.0.11' # 指定主数据库地址

master_user='slave' # 指定连接用户

master_password='slave@2023' # 指定连接密码

master_port=6603 # 指定连接端口（端口切记不可加引号）

4、都为Yes的时候表示配置成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Slave_IO：复制master主机binlog日志文件里的SQL到本机的relay-log文件里

Slave_SQL：执行本机relay_log文件里的SQL语句重现Master的数据操作

还可通过 netstat -nltpa 查看从库与主库通信端口。

千万注意：??

由于这里我们同步的是最新的二进制文件，所以在该二进制文件之前生成的数据库是无法进行同步的；若要将全部数据库同步，需先将所有数据库导出。

五、验证MySQL主从

1、在Master主库进行数据库创建，执行建表语句

# 新增数据库datadb
mysql> create database datadb;
# 进入数据库datadb
mysql> use datadb;
# 数据库datadb新增test表结构
mysql> create table test(id int not null,username varchar(32));
# 在test表插入一条数据
mysql> insert into test(id,username) values(1,'root');
mysql> insert into test(id,username) values(2,'从库看得见嘛');
# 刷新权限
mysql> flush privileges;
mysql> quit;

2、在Slave从库上查看是否会自动同步该库，表结构以及数据

# 查看数据库
mysql> show databases;
# 进入数据库datadb
mysql> use datadb;
# 数据库datadb新增test表结构
mysql> create table test(id int not null,username varchar(32));
# 查看表test
mysql> show tables;
# 查询表test内容
mysql> select * from test \G
mysql> quit;

可以看到，slave完成了对master的复制。

--------------------------------------至此MySQL主从复制部署完成^_--------------------------------------

六、MySQL主从部署错误记录

错误一、show slave status\G;查询后报错：ERROR: No query specified

解决办法：

在\G后面加入；分号虽然查询出来了但是会报错

ERROR:

No query specified

去掉\G后面加；分号就不会报错了。

错误二、MySQL主从同步故障：Slave_SQL_Running:No

解决办法：

Slave_SQL_Running: No

1.程序可能在slave上进行了写操作；

2.也可能是slave机器重起后，事务回滚造成的；

一般是事务回滚造成的。

解决办法：

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;