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.0port=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.0port=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.000001Position:1Binlog_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: Waitingformaster to send event
Master_Host:192.168.1.1Master_User: root
Master_Port:3306Connect_Retry:60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos:1Relay_Log_File: mysql-relay-bin.000001Relay_Log_Pos:1Relay_Master_Log_File: mysql-bin.000001Slave_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 p7zipfi#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} 还在运行，请重新执行脚本！";
exit1
fiCOMPRESS_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:"HOSTif [ "${HOST}"x == ""x ]; then
echo "ip不能为空!"exit0
firead-t 30 -p "请输入用户名:"USERif [ "${USER}"x == ""x ]; then
echo "用户名不能为空!"exit0
fi#提示“请输入密码”并等待30秒，把输入保存入变量中，输入内容隐藏
read-t 30 -s -p "请输入用户密码:"PASSWORDif [ "${PASSWORD}"x == ""x ]; then
echo "用户密码不能为空!"exit0
fi
echo -e "\n"
echo "目标主机ip:${HOST}"
echo "用户名为:${USER}"read-t 60 -p "确认要恢复远程主机:${HOST}的MySQL的数据吗？确认[y/n]:"CONFIRM_EXECif [ "${CONFIRM_EXEC}"x != "y"x ] && [ "${CONFIRM_EXEC}"x != "Y"x ]; thenexit0
fiREMOTE_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 p7zipfi#安装sshpass压缩if [ ! -f "/usr/bin/sshpass" ];then
yum install -y sshpassfi
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} 还在，请重新直接脚本";
exit1
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 ""exit0
单个数据库的还原：
#创建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：
202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
