mysql8.20版本RPM部署主从复制架构

软件包下载地址：https://dev.mysql.com/downloads/mysql/

image.png

#主库 master 192.168.64.22
tar -xvf mysql-8.0.20-1.el7.x86_64.rpm-bundle.tar
yum install mysql-community-{server,client,common,libs}-*
vim /etc/my.conf
[mysqld]
log-bin=mysql-log-bin
server-id=1
# test test1 需要复制的数据库
binlog-do-db=test
binlog-do-db=test1
# 指定mysql库不复制
binlog-ignore-db=mysql

systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql -uroot -p
alter user 'root'@'localhost' identified by 'admin123';
set global validate_password.policy=LOW; 
show variables like 'validate_password%';
# 配置可远程连接
create user 'root'@'%' identified with mysql_native_password by 'admin123'; 
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
# 建立复制账号
CREATE USER 'my_slave'@'%' IDENTIFIED BY 'admin123';
GRANT REPLICATION SLAVE ON *.* TO 'my_slave'@'%';

# 锁库
FLUSH TABLES WITH READ LOCK;
# 查看Master状态
mysql> show master status;
+----------------------+----------+--------------+------------------+-------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+-------------------+
| mysql-log-bin.000002 |     2188 | test,test1   | mysql            |                   |
+----------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

#从库 slave 192.168.64.23
tar -xvf mysql-8.0.20-1.el7.x86_64.rpm-bundle.tar
yum install mysql-community-{server,client,common,libs}-*
vim /etc/my.conf
[mysqld]
log-bin=mysql-log-bin
server-id=2
systemctl start mysqld
cat /var/log/mysqld.log | grep password
mysql -uroot -p
alter user 'root'@'localhost' identified by 'admin123';
set global validate_password.policy=LOW; 
show variables like 'validate_password%';
# 配置可远程连接
create user 'root'@'%' identified with mysql_native_password by 'admin123'; 
grant all privileges on *.* to 'root'@'%' with grant option;
# 设置连接主服务器的信息 如果使用默认的插件：caching_sha2_password
CHANGE MASTER TO
MASTER_HOST='192.168.64.22',
MASTER_USER='my_slave',
MASTER_PASSWORD='admin123',
MASTER_LOG_FILE='mysql-log-bin.000002',
MASTER_LOG_POS=2188,
MASTER_SSL=1;

start slave;
show slave status\G
stop slave；
show variables like '%ssl%';

image.png

建立测试数据库和数据表 测一下主从复制情况是否顺利

CREATE DATABASE test;
CREATE DATABASE test1;
use test;
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

insert into Student values('01' , '张三' , '1990-01-01' , '男');
insert into Student values('02' , '李四' , '1990-12-21' , '男');
insert into Student values('03' , '王妹' , '1990-05-20' , '女');
# 最后master slave主从复制成功 释放锁定
unlock tables;

image.png

mysql 定时自动backup脚本
[root@test-22 mysql_bak]# cat mybackup.sh 
#!/bin/bash
USER=root
PASSWORD=admin123
DATABASE=test
DATABASE2=test1
BACKUP_DIR=/opt/mysql_bak/backup
#数据库备份sql文件的绝对路径
LOGFILE=/opt/mysql_bak/backup/data_backup.log
#备份数据库的脚本的日志文件
DATE=`date +%Y%m%d-%H%M`
#获取当前系统时间
DUMPFILE=$DATE-$DATABASE.sql
DUMPFILE2=$DATE-$DATABASE2.sql
#备份数据库的sql文件名
ARCHIVE=$DUMPFILE-tar.gz
ARCHIVE2=$DUMPFILE2-tar.gz
#备份的数据库压缩后的文件名
MYHOST=192.168.64.22
if [ ! -d $BACKUP_DIR ];
#判断路径是否存在，不存在的话创建此路径
then
mkdir -p "$BACKUP_DIR"
fi
echo -e "\n" >> $LOGFILE
echo "------------------------------------" >> $LOGFILE
echo "BACKUP DATE:$DATE">> $LOGFILE
echo "------------------------------------" >> $LOGFILE
cd $BACKUP_DIR      #进入备份路径
/usr/bin/mysqldump -h$MYHOST -u$USER -p$PASSWORD $DATABASE > $DUMPFILE
/usr/bin/mysqldump -h$MYHOST -u$USER -p$PASSWORD $DATABASE2 > $DUMPFILE2
#使用mysqldump命令备份数据库
if [[ $? == 0 ]]; then
tar czvf $ARCHIVE $DUMPFILE >> $LOGFILE 2>&1
tar czvf $ARCHIVE2 $DUMPFILE2 >> $LOGFILE 2>&1
#判断是否备份成功，若备份成功，则压缩备份数据库，否则将错误日志写入日志文件中去。
echo "$ARCHIVE BACKUP SUCCESSFUL!" >> $LOGFILE
echo "$ARCHIVE2 BACKUP SUCCESSFUL!" >> $LOGFILE
#rm -f $DUMPFILE
else
echo "$ARCHIVE Backup Fail!" >> $LOGFILE
echo "$ARCHIVE2 Backup Fail!" >> $LOGFILE
fi

设置定时清理
[root@test-22 mysql_bak]# cat delmybackup.sh 
#!/bin/bash
BACKUPDIR="/opt/mysql_bak/backup"
#备份数据库sql文件的绝对路径
KEEPTIME=1
#定义需要删除的文件距离现在时间的天数 
DELFILE=`find $BACKUPDIR -type f -mtime +$KEEPTIME -exec ls {} \;`
for delfile in ${DELFILE}
do
rm -f $DELFILE
done

设置定时执行
#每天2点备份数据库
0 2 * * * sh -x /opt/mysql_bak/mybackup.sh
#每个月1号凌晨1点 执行删除数据库的脚本
* 1 1 * * sh -x /opt/mysql_bak/delmybackup.sh

image.png

备份脚本第2种 适合异地灾备 推荐：
# 1.yum install -y rsync
# 2.2台主机做好免密
ssh-keygen -t rsa
ssh-copy-id -i ~/.ssh/id_rsa.pub 192.168.xx.xx

[root@test-22 data]# vim mysql-all-backup.sh 

#! /bin/bash
BAKDIR="/data/mysql_backup"
MYSQLUSR="root"
MYSQLPW="admin123"
LOG=/data/mysql_backup/mysql-all-backup.log
NOW=`date +%Y-%m-%d-%H-%M`
[ ! -d $BAKDIR ] && mkdir -p $BAKDIR
echo "##########today##########" >>$LOG
echo "mysql backup begin at `date`" >>$LOG

for db in mysql test test1
  do
    mysqldump -u$MYSQLUSR -p$MYSQLPW $db | gzip >  $BAKDIR/$db-$NOW.sql.gz
    if [ $? == 0 ];then
        echo "$NOW--$db  backup succeeded!" >> $LOG
    else
        echo "$db backup failed!" >> $LOG
    fi
done

rsync -az  --delete  /data/mysql_backup/* root@192.168.64.23:/data/mysql_backup/ >/dev/null 2>&1
if [ $? == 0 ];then
        echo "$NOW Remote backup succeeded!" >> $LOG
else
        echo "$NOW Remote  backup failed!" >> $LOG
fi
# 删除2个月之前的备份数据
find $BAKDIR -type f -mtime +60 -name "*.gz"|xargs rm -rf
echo "mysql bakup end at `date`" >>$LOG

# 每个星期5晚上12.30执行一次
30 0 * * 5 cd /data/ && sh -x my_backup.sh >> my_backup.log 2>&1

image.png