Centos7 MySQL5.6.29 主从同步配置、数据备份还原

OS: Centos 7 3.10.0-862.el7.x86_64

MySQL: 5.6.29-log

背景: 开发环境被多人使用，有时候为出现故障导致大多数人无法使用数据库，严重影响开发节奏。故做一个数据备份和结构备份机制。用于快速恢复开发环境MySQL。

一、主从设置

1.安装MySQL

下载rpm包：

wget -i -c https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/mysql-community-common-5.7.26-1.el7.x86_64.rpm
wget -i -c https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/mysql-community-client-5.7.26-1.el7.x86_64.rpm
wget -i -c https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/mysql-community-libs-5.7.26-1.el7.x86_64.rpm
wget -i -c https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql57-community-el7/mysql-community-server-5.7.26-1.el7.x86_64.rpm

安装：

yum install -y mysql-community-*.rpm

2. 配置机器Master(192.168.1.1) My.cnf

在[mysqld] 节点下添加如下内容：

#服务器id标识
server-id=1

#数据存放目录
datadir=/data/mysql/data

监听ip和端口
bind-address = 0.0.0.0
port=3306


######################
######bing log 配置
######################

#开启mysql的binlog日志功能
log-bin = mysql-bin
#控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
sync_binlog = 1
#binlog日志格式，mysql默认采用statement，建议使用mixed
binlog_format = mixed
#binlog过期清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大
max_binlog_cache_size= 512m
#不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
#binlog-ignore-db=mysql 


# 自增值的偏移量
auto-increment-offset = 1
# 自增值的自增量
auto-increment-increment = 1
#跳过从库错误
slave-skip-errors = all

3.配置机器Slave(192.168.1.2) My.cnf

在[mysqld] 节点下添加如下内容：

server-id=2

#数据存放目录
datadir=/data/mysql/data

监听ip和端口
bind-address = 0.0.0.0
port=3306


#bing log 配置
log-bin=mysql-bin
relay-log = mysql-relay-bin

expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大

#忽略同步的库
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

4.启动MySQL、设置同步配置（跳过了密码设置和用户添加步骤，请自行处理。）

 systemctl start mysqld

查看Master服务器的同步信息

[root@192.168.1.1 root]# mysql -uroot -proot1234 -e "show master status\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

记下 File 和 Position 的值。

再执行如下命令，设置Slave同步配置

mysql -h192.168.1.2 -u用户名 -p密码 -e "CHANGE MASTER TO MASTER_HOST = '192.168.1.1',  MASTER_USER = '用户名', MASTER_PASSWORD = '密码',MASTER_PORT = 3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1;"

MASTER_LOG_FILE 对应 File， MASTER_LOG_POS 对应 Position 。

启动同步功能：

mysql -h192.168.1.2 -u用户名 -p密码 -e "start slave"

查看Slave服务的同步状态：

[root@192.168.1.2 root]# mysql -uroot -proot1234 -e "show slave status\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 1
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%

如上文红色标记内容，Slave_IO_Running: Yes , Slave_SQL_Running: Yes 表示同步开始了，已设置成功。

后面就是你测试一下是否如实进行同步了。

二、进行数据备份、还原

1.编写备份脚本，备份Slave服务器的数据。

#!/bin/bash

CURRENT_DIR=$(pwd)
CURRENT_DAY=$(date +%Y%m%d)
DELETE_DAY=$(date -d "2 days ago" +%Y%m%d)

MYSQL_DATA_DIR="/data/mysql/data"
BACKUP_PARENT_DIR="/data/mysql/backup";
BACKUP_DIR_NAME="mysql_data"

BACKUP_FILE_PATH="${BACKUP_PARENT_DIR}/${BACKUP_DIR_NAME}_${CURRENT_DAY}.7z"
OLD_BACKUP_FILE_PATH="${BACKUP_PARENT_DIR}/${BACKUP_DIR_NAME}_${DELETE_DAY}.7z"

#安装7z压缩
if [ ! -f "/usr/bin/7za" ];then
    yum install -y p7zip
fi

#
echo "cd ${BACKUP_PARENT_DIR}"
cd ${BACKUP_PARENT_DIR}
if [ ! -d "${BACKUP_PARENT_DIR}" ];then
    mkdir ${BACKUP_PARENT_DIR}
fi

#清理昨天的日志
echo "开始删除旧备份文件"
if [ -f "${OLD_BACKUP_FILE_PATH}" ];then
    echo "rm -f ${OLD_BACKUP_FILE_PATH}";
    /usr/bin/rm ${OLD_BACKUP_FILE_PATH}
fi

#
STOP_MYSQL_CMD="/usr/bin/systemctl stop mysqld";
echo "停止MySQL服务: ${STOP_MYSQL_CMD}"
eval ${STOP_MYSQL_CMD}


#检测mysqld进程
MYSQL_PID=$(ps aux | grep mysqld | grep -v grep | awk '{print $2}')
if [ "$MYSQL_PID"x != ""x ]; then
    echo "mysqld 进程: ${MYSQL_PID} 还在运行，请重新执行脚本！";
    exit 1
fi


COMPRESS_DATA_CMD="/usr/bin/7za a -t7z ${BACKUP_FILE_PATH} ${MYSQL_DATA_DIR} -xr\!auto.cnf";
echo "执行压缩: ${COMPRESS_DATA_CMD}"
eval ${COMPRESS_DATA_CMD}


#重启
START_MSYQL_CMD="/usr/bin/systemctl start mysqld";
echo "重启mysql：${START_MSYQL_CMD}";
eval ${START_MSYQL_CMD}

echo "Return to source directory:${CURRENT_DIR}";
cd ${CURRENT_DIR}

echo ""
echo ""
echo "MySql Backup is Successfully @$(date "+%Y-%m-%d %H:%M:%S")!";
echo "=============================================================="
echo ""
echo ""

2.设置 crontab 定时任务

#每天凌晨2点过一分进行mysql备份
1 2 * * * /data/mysql_backup.sh >> /data/mysql/backup/backup.log

3.编写还原脚本(被还原机器需要安装了 7za 压缩工具)

#!/bin/bash

CURRENT_DIR=$(pwd)
CURRENT_TIME=$(date "+%Y%m%d%H%M%S")

MYSQL_DATA_PARENT_DIR="/data/mysql/"
MYSQL_DATA_DIR="${MYSQL_DATA_PARENT_DIR}data"
ZIP_7Z_MYSQL_DATA_FILE="${MYSQL_DATA_PARENT_DIR}scp_mysql_bak.7z"

START_MYSQL_CMD="/usr/bin/systemctl start mysqld"
STOP_MYSQL_CMD="/usr/bin/systemctl stop mysqld"



#提示“请输入”并等待30秒，把用户的输入保存入变量中
read -t 30 -p "请输入需要恢复的主机ip:" HOST
if [ "${HOST}"x == ""x ]; then
    echo "ip不能为空!"
    exit 0
fi

read -t 30 -p "请输入用户名:" USER
if [ "${USER}"x == ""x ]; then
    echo "用户名不能为空!"
    exit 0
fi

#提示“请输入密码”并等待30秒，把输入保存入变量中，输入内容隐藏
read -t 30 -s -p "请输入用户密码:" PASSWORD
if [ "${PASSWORD}"x == ""x ]; then
    echo "用户密码不能为空!"
    exit 0
fi
echo -e "\n"

echo "目标主机ip:${HOST}"
echo "用户名为:${USER}"
read -t 60 -p "确认要恢复远程主机:${HOST}的MySQL的数据吗？确认[y/n]:" CONFIRM_EXEC
if [ "${CONFIRM_EXEC}"x != "y"x ] && [ "${CONFIRM_EXEC}"x != "Y"x ]; then
    exit 0
fi


REMOTE_SSH_CMD="/usr/bin/sshpass -p ${PASSWORD} /usr/bin/ssh ${USER}@${HOST}"
REMOTE_SCP_CMD="/usr/bin/sshpass -p ${PASSWORD} /usr/bin/scp"

#安装7z压缩
if [ ! -f "/usr/bin/7za" ];then
    yum install -y p7zip
fi


#安装sshpass压缩
if [ ! -f "/usr/bin/sshpass" ];then
    yum install -y sshpass
fi

echo ""
echo "开始执行远程mysql恢复"

cd ${MYSQL_DATA_PARENT_DIR}

echo "关闭当前服务器mysld"
echo "${STOP_MYSQL_CMD}";
eval ${STOP_MYSQL_CMD}


#检查mysql是否已经关闭
MYSQL_PID=$(ps aux | grep mysqld | grep -v grep | awk '{print $2}')
if [ "${MYSQL_PID}"x != ""x ]; then
    echo "mysqld 进程: ${MYSQL_PID} 还在，请重新直接脚本";
    exit 1
fi


echo "开始压缩打包数据目录"
zip_mysql_data_cmd="/usr/bin/7za a -t7z ${ZIP_7Z_MYSQL_DATA_FILE} ${MYSQL_DATA_DIR} -xr\!auto.cnf"
echo "${zip_mysql_data_cmd}"
eval ${zip_mysql_data_cmd}


echo "开始同步压缩文件至目标服务器"
sync_zip_to_remote_mysql_cmd="${REMOTE_SCP_CMD} ${ZIP_7Z_MYSQL_DATA_FILE} ${USER}@${HOST}:${MYSQL_DATA_PARENT_DIR}"
echo "${sync_zip_to_remote_mysql_cmd}"
eval ${sync_zip_to_remote_mysql_cmd}


echo "开始关闭目标机器的mysqld服务"
stop_remote_mysql="${REMOTE_SSH_CMD} \"${STOP_MYSQL_CMD}\""
echo "${stop_remote_mysql}"
eval ${stop_remote_mysql}


echo "开始备份目标机器的mysql数据目录"
back_remote_mysql_data_cmd="${REMOTE_SSH_CMD} \"/usr/bin/7za a -t7z ${MYSQL_DATA_PARENT_DIR}auto_mysql_bak_${CURRENT_TIME}.7z ${MYSQL_DATA_DIR}\""
echo "${back_remote_mysql_data_cmd}"
eval ${back_remote_mysql_data_cmd}
#
echo "开始删除目标机器的mysql数据目录"
mv_remote_mysql_data_cmd="${REMOTE_SSH_CMD} \"/usr/bin/rm -rf ${MYSQL_DATA_DIR}\""
echo "${mv_remote_mysql_data_cmd}"
eval ${mv_remote_mysql_data_cmd}


echo "开始执行压缩文件的解压"
unzip_scp_mysql_data_cmd="${REMOTE_SSH_CMD} \"/usr/bin/7za x ${ZIP_7Z_MYSQL_DATA_FILE} -r -o${MYSQL_DATA_PARENT_DIR}\""
echo "${unzip_scp_mysql_data_cmd}"
eval ${unzip_scp_mysql_data_cmd}
#
chown_remote_mysql_data_cmd="${REMOTE_SSH_CMD} \"/usr/bin/chown -R mysql:mysql ${MYSQL_DATA_DIR}\""
echo "${chown_remote_mysql_data_cmd}"
eval ${chown_remote_mysql_data_cmd}


echo "开始启动mysqld服务"
start_remote_mysql_cmd="${REMOTE_SSH_CMD} \"${START_MYSQL_CMD}\""
echo "${start_remote_mysql_cmd}"
eval ${start_remote_mysql_cmd}


echo "启动当前服务器mysld"
echo "${START_MYSQL_CMD}";
eval ${START_MYSQL_CMD}


echo "开始清理同步文件"
rm_remote_scp_mysql_data_cmd="${REMOTE_SSH_CMD} \"/usr/bin/rm -f ${ZIP_7Z_MYSQL_DATA_FILE}\""
echo "${rm_remote_scp_mysql_data_cmd}"
eval ${rm_remote_scp_mysql_data_cmd}
#
rm_scp_mysql_data_cmd="/usr/bin/rm -f ${ZIP_7Z_MYSQL_DATA_FILE}"
echo "${rm_scp_mysql_data_cmd}"
eval ${rm_scp_mysql_data_cmd}


echo "Return to source directory:${CURRENT_DIR}";
cd ${CURRENT_DIR}

echo ""
echo ""
echo "MySql restore is Successfully @$(date "+%Y-%m-%d %H:%M:%S")!";
echo "=============================================================="
echo ""
echo ""

exit 0

单个数据库的还原：

#创建DB
mysql -h192.168.1.1 -u用户名 -p密码 -A -N -e "create database if not exists 数据库名称 CHARACTER SET utf8 COLLATE utf8_general_ci;"
#同步数据
mysqldump -h192.168.1.2 -u用户名 -p密码 --default-character-set=utf8 --opt 数据库名称 | mysql -h192.168.1.1 -u用户名 -p密码 --default-character-set=utf8 -C 数据库名称

PS：

线上不停机部署mysql主从

posted @ 2020-03-23 18:00 phpdragon 阅读(376) 评论(0) 编辑收藏举报

刷新页面返回顶部

PHPdragon

想转型的PHPer、JAVAer

Centos7 MySQL5.6.29 主从同步配置、数据备份还原

一、主从设置

1.安装MySQL

2. 配置机器Master(192.168.1.1) My.cnf

3.配置机器Slave(192.168.1.2) My.cnf

4.启动MySQL、设置同步配置（跳过了密码设置和用户添加步骤，请自行处理。）

二、进行数据备份、还原

公告

PHPdragon

想转型的PHPer、JAVAer

Centos7 MySQL5.6.29 主从同步配置 、数据备份还原

一、主从设置

1.安装MySQL

2. 配置机器Master(192.168.1.1) My.cnf

3.配置机器Slave(192.168.1.2) My.cnf

4.启动MySQL、设置同步配置（跳过了密码设置和用户添加步骤，请自行处理。）

二、进行数据备份、还原

公告

Centos7 MySQL5.6.29 主从同步配置、数据备份还原