MySQL8.0主从复制（一主一从）

MySQL8.0主从复制（一主一从）

前言

1、主从复制解释

将主数据库的增删改查等操作记录到二进制日志文件中，从库接收主库日志文件，根据最后一次更新的起始位置，同步复制到从数据库中，使得主从数据库保持一致。

2. 主从复制作用

* 高可用性：主数据库异常可切换到从数据库
* 负载均衡：实现读写分离
* 备份：进行日常备份(注意：主从复制不能代替备份)

3、主从复制过程

（1）主数据库在每次事务完成前，将该操作记录到binlog日志文件中；
（2）从数据库中有一个I/O线程，负责连接主数据库服务，并读取binlog日志变化，如果发现有新的变动，则将变动写入到relay-log，否则进入休眠状态；
（3）从数据库中的SQL Thread读取中继日志，并串行执行SQL事件，使得从数据库与主数据库始终保持一致。

部署环境及版本

系统版本：CentOS Linux release 7.9.2009
Mysql版本：8.0.30

准备

主机ip	角色
192.168.102.201	master
192.168.102.207	slave

1、关闭自带防火墙，用iptables

# 关闭SELinux
setenforce 0 
sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

# 关闭Firewalld并禁止自启动
systemctl stop firewalld
systemctl disable firewalld

# 安装iptables
yum install -y iptables-services

# 启动iptables服务，并设置开机自启动（修改规则在/etc/sysconfig/iptables）
systemctl start iptables
systemctl enable iptables.service

2、时间同步

yum -y install ntp
systemctl enable ntpd
systemctl start ntpd
timedatectl set-timezone Asia/Shanghai
ntpdate -u time.nist.gov
date

一、MySQL8.0部署（主、从都部署）

1、安装libaio

yum install libaio -y

2、官网下载MySQL安装包

#官网地址
https://downloads.mysql.com/archives/community/

3、将安装包下载下来，放在对应目录下

cd /server/tools
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.30-el7-x86_64.tar.gz

4、将mysql的压缩包解压到/data目录下，并设置软连接

tar xvf mysql-8.0.30-el7-x86_64.tar.gz -C /data
cd /data && ln -sv mysql-8.0.30-el7-x86_64 mysql

5、创建work用户，将mysql目录的用户换成work

useradd work
chown -R work.work /data/mysql
chown -R work.work /data/mysql-8.0.30-el7-x86_64
su - work

6、配置环境变量

vim ~/.bash_profile （注释原来PATH，添加新的PATH）

#PATH=$PATH:$HOME/bin
MYSQL_HOME=/data/mysql
PATH=$MYSQL_HOME/bin:$PATH:$HOME/bin

vim ~/.bashrc（新增以下两行）

alias mysql='LANG=en_US.UTF-8; mysql --defaults-file=/data/mysql/my.cnf'
shopt -s expand_aliases

#配置文件生效

source ~/.bash_profile
source ~/.bashrc

7、创建需要的文件和目录

mkdir /data/mysql/binlog
mkdir /data/mysql/log
touch /data/mysql/log/mysqld.err
# 创建my.cnf 、 startMysql.sh 、stopMysql.sh，可以直接上传使用，若没有参考下面配置
chown -R work.work /data/mysql

vim /data/mysql/my.cnf

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_heap_table_size=128M
table_open_cache=8000
tmp_table_size = 16M
read_rnd_buffer_size=16M
datadir = /data/mysql/data
socket = /data/mysql/mysql.sock
#lower_case_table_names = 1
open_files_limit = 100000
binlog_cache_size = 20971520
log-error = /data/mysql/log/mysqld.err
pid-file = /data/mysql/mysqld.pid
#skip_grant_tables
innodb_flush_log_at_trx_commit = 0
port = 33099
expire_logs_days=5
character-set-server = UTF8MB4
user = work
binlog-ignore-db=mysql
log-slave-updates = on
slave-skip-errors=all
sync_binlog=100
max_connections=500
max_connect_errors=1000000
default-storage-engine=INNODB
max_allowed_packet=128M
#innodb_buffer_pool_size=100G
#innodb_buffer_pool_instances=50
#innodb_buffer_pool_chunk_size=1G
sort_buffer_size=16M
max_length_for_sort_data=8096
innodb_sort_buffer_size=64M
myisam_sort_buffer_size=64M
join_buffer_size=8M
read_buffer_size=8M
log-bin=/data/mysql/binlog/mysql-bin.log
wait_timeout=36000
interactive_timeout=36000
mysqlx=0
mysqlx_interactive_timeout=36000
mysqlx_wait_timeout=36000
#skip-grant-tables
 
 
[mysql]
default-character-set = utf8mb4
socket = /data/mysql/mysql.sock
 
 
[client]
socket = /data/mysql/mysql.sock
port = 33099
default-character-set = UTF8MB4

vim /data/mysql/startMysql.sh

#!/bin/sh
LANG=en_US.UTF-8
MYSQL_HOME=/data/mysql
PATH=$MYSQL_HOME/bin:$PATH
mysqld --defaults-file=/data/mysql/my.cnf &

vim /data/mysql/stopMysql.sh

#!/bin/sh
LANG=en_US.UTF-8
MYSQL_HOME=/data/mysql
PATH=$MYSQL_HOME/bin:$PATH
mysqladmin --defaults-file=/data/mysql/my.cnf  -uroot -p  shutdown

8、初始化数据库实例

cd /data/mysql/bin
./mysqld --initialize-insecure --user=work --basedir=/data/mysql --datadir=/data/mysql/data

9、启动MySQL，登录mysql，创建work用户并授权

sh /data/mysql/startMysql.sh
mysql
mysql> CREATE USER 'work'@'%' IDENTIFIED BY 'xxxxx';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'work'@'%';
mysql> flush privileges;
mysql> exit

这里创建的用户就是下面 第 八.2 步中配置的

10、验证

mysql -uwork -h内网ip -P33099 -p
mysql> show databases; #正常访问
mysql> exit
ss -ntl  #33099端口存在

二、主库配置

1、修改mysql配置文件

vim /data/mysql/my.cnf

#配置主库唯一ID，启用二进制日志（上面已经写了这就不加了）。
[mysqld]
# 主服务器唯一ID
server-id=1

2、重启mysql

#停止mysql数据库
su - work
sh /data/mysql/stopMysql.sh
#启动mysql数据库
su - work
sh /data/mysql/startMysql.sh
#重启mysql数据库
su - work
sh /data/mysql/stopMysql.sh
sh /data/mysql/startMysql.sh

三、从库配置

1、修改mysql配置文件

vim /data/mysql/my.cnf

#配置从库唯一ID，启用中继日志，启用二进制日志（上面已经写了这就不加了）。
[mysqld]
# 从服务器唯一ID
server-id=2
# 启用中继日志
relay-log=mysql-relay

2、重启mysql

#停止mysql数据库
su - work
sh /data/mysql/stopMysql.sh
#启动mysql数据库
su - work
sh /data/mysql/startMysql.sh
#重启mysql数据库
su - work
sh /data/mysql/stopMysql.sh
sh /data/mysql/startMysql.sh

四、保证主从数据库可以互相远程连接

1、iptables放行33099(主从都要执行)

#vim /etc/sysconfig/iptables
-A INPUT -s 192.168.102.0/24 -p tcp -j ACCEPT
#重启iptables
systemctl restart iptables

2、测试主从数据库互相远程连接

#主数据库服务器测试从数据库
su - work
mysql -uwork -h192.168.102.207 -P33099 -p
#从数据库服务器测试主数据库
su - work
mysql -uwork -h192.168.102.201 -P33099 -p

五、主数据库创建用户slave并授权

1、创建用户slave并授权

#登录
su - work
mysql
#创建用户
create user 'slave'@'%' identified with mysql_native_password by 'xxxxxxx';
#授权
grant replication slave on *.* to 'slave'@'%';
#刷新权限
flush privileges;

2、从数据库验证slave用户是否可用

#从数据库服务器测试主数据库
su - work
mysql -uslave -h192.168.102.201 -P33099 -p

六、主数据库查询服务ID及Master状态

#登录
su - work
mysql
#查询server_id是否可配置文件中一致
show variables like 'server_id';
#若不一致，可设置临时ID（重启失效）
set global server_id = 1;
#查询Master状态，并记录 File 和 Position 的值
show master status;
#注意：执行完此步骤后退出主数据库，防止再次操作导致 File 和 Position 的值发生变化

七、从数据库中设置主数据库

#登录
su - work
mysql
#查询server_id是否可配置文件中一致
show variables like 'server_id';
#若不一致，可设置临时ID（重启失效）
set global server_id = 2;
#设置主数据库参数(File 和 Position的值必须和Master查出来的一致)
change master to master_host='192.168.102.201',master_port=33099,master_user='slave',master_password='xxxxxx',master_log_file='mysql-bin.000002',master_log_pos=156;
#开始同步
start slave;
#若出现错误，则停止同步，重置后再次启动
stop slave;
reset slave;
start slave;
#查询Slave状态
show slave status\G
#查看是否配置成功
#查看参数 Slave_IO_Running 和 Slave_SQL_Running 是否都为yes，则证明配置成功。若为no，则需要查看对应的 Last_IO_Error 或 Last_SQL_Error 的异常值。

八、测试

#在主数据库创建数据库test
create database test;
#从数据库查看
show databases;
#在主数据库创建表
use test;
create table t_user(id int, name varchar(20));
#插入数据
insert into t_user values(1, 'Test');
#在从数据库查看
use test;
select * from t_user;
#其他增删改查操作请自行测试

附加：数据库全量备份

1、数据库备份脚本

#vim /home/work/db_bak/db_bak.sh
#!/bin/sh
if [ ! -d "/home/work/db_bak/DB" ];then
    mkdir -pv /home/work/db_bak/DB
else
    echo "文件夹已经存在"
fi
cd /home/work/db_bak
/data/mysql/bin/mysqldump -h 127.0.0.1 -uwork -p"******" -P 33099 test > /home/work/db_bak/DB/test-`date +%Y-%m-%d`.sql

echo "数据库导出成功 `date -d today +"%Y-%m-%d %T"`" >> /home/work/db_bak/backup.log
tar -zcvf db-`date +%Y-%m-%d`.tar.gz DB
echo "数据库打包成功 `date -d today +"%Y-%m-%d %T"`" >> /home/work/db_bak/backup.log
rm -f DB/*.sql 
echo "数据库删除成功 `date -d today +"%Y-%m-%d %T"`" >> /home/work/db_bak/backup.log  
PATH_=/home/work/db_bak
cd $PATH_
find ./ -mtime +3 -name "db-*.tar.gz" -exec rm -rf {} \;
echo "清理四天前的tar包 `date -d today +"%Y-%m-%d %T"`" >> /home/work/db_bak/backup.log

2、设置定时任务

su - work
0 1 * * * /home/work/db_bak/db_bak.sh