MySQL 备份、迁移、设置主从复制

定时备份

MySQL备份脚本 mysql_backup.sh

#!/bin/bash

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin


retain_files_num=10
log=/data/backup/mysql/mysql_backup.log
time_str=`date "+%Y_%m_%d_%H_%M_%S"`


# 清理日志文件 mysql_backup.log
log_size=`ls -la /data/backup/mysql/mysql_backup.log | awk '{print $5}'`

# 如果文件大于30M，那么删除
if [ $log_size -ge 31457280 ]
then
    rm -rf /data/backup/mysql/mysql_backup.log
    printf "`date "+%Y-%m-%d %H:%M:%S"` mysql_backup.log size $log_size 大于30M 删除此文件\n" >> ${log}
else
    printf "`date "+%Y-%m-%d %H:%M:%S"` mysql_backup.log size $log_size 小于30M 不做处理\n" >> ${log}
fi


# 备份mysql数据
echo "`date "+%Y-%m-%d %H:%M:%S"` 开始备份，本次备份的文件是 ${time_str}.sql" >> ${log}

docker exec mysql_server mysqldump -hip -P3306 -uroot -p'密码' --single-transaction -E -R --triggers --all-databases --master-data=2 --add-drop-database --add-drop-table --verbose > /data/backup/mysql/${time_str}.sql

echo "`date "+%Y-%m-%d %H:%M:%S"` 结束备份" >> ${log}


# 删除旧的备份
files=`ls -lt /data/backup/mysql/ | grep sql  | grep -v sh | grep -v log | awk '{print $NF}'`
num=0
for one_file in ${files}
do
    ((num++))
    echo "`date "+%Y-%m-%d %H:%M:%S"` 循环处理文件 num: ${num} one_file: ${one_file}" >> ${log}
    if [ ${num} -le ${retain_files_num}  ]
    then
       echo "`date "+%Y-%m-%d %H:%M:%S"` 文件${one_file} 符合最新的${retain_files_num}个范围内，保留" >> ${log} 
       continue
    fi
    
    echo ${one_file} | grep sql >/dev/null 2>&1
    if [ $? -eq 0 ]; then
        echo "`date "+%Y-%m-%d %H:%M:%S"` 文件${one_file} 超过最新的${retain_files_num}个范围内，删除" >> ${log} 
        rm -rf /data/backup/mysql/${one_file} 
    else
        echo "`date "+%Y-%m-%d %H:%M:%S"` 文件${one_file} 不包含sql字符，不删除" >> ${log} 
    fi
done
echo "" >> ${log} 
echo "" >> ${log} 

crontab任务

crontab -e     
#每日1点、13点执行
0 1,13 * * * sh /data/backup/mysql/mysql_backup.sh

一次性迁移（备份）、设置主从（mysqldump）

在master上执行

mysqldump -h127.0.0.1 -P3306 -uroot -p'xxxx' --single-transaction -E -R --triggers --all-databases --master-data=2 --add-drop-database --add-drop-table --verbose> xxx.sql

在slave上执行

reset master;
stop slave;
reset slave all;
show slave status\G;
change master to master_auto_position=0;
mysql -h 127.0.0.1 -P3306 -uroot -p'xxxx' -e 'reset master; source xxx.sql;'
CHANGE MASTER TO MASTER_HOST='xxxx', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='xxxx', master_auto_position=1;
start slave;

一次性迁移（备份）、设置主从（xtrabackup）

在master上导出

xtrabackup --host=ip --port=3306 --user=root --password='xxx' --backup --target-dir=备份的目录 --datadir=mysql数据目录

传到另外一台机器

scp -r

在另一台恢复

停掉mysql
mysql原目录删除，或者mv移动
xtrabackup --decompress --target-dir=备份的目录
xtrabackup --prepare --target-dir=备份的目录
xtrabackup --copy-back --target-dir=备份的目录 --datadir=将要恢复的mysql数据目录
查看备份的目录/xtrabackup_info，找到GTID of the last change 'xxx'
启动mysql
登录mysql命令行
reset master;
stop slave;
reset slave all;
show slave status\G;
change master to master_auto_position=0;
SET @@GLOBAL.GTID_PURGED='xxx'
CHANGE MASTER TO MASTER_HOST='xxx', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='xxx', master_auto_position=1;
再检查
show slave status\G;

start slave;

解决 Relay log read failure ...

排查是否是relay 文件损坏 stackoverflow.com/questions/7…

解决 从库的relay log文件损坏

1、删除从库的relay log 

2、重启从库 

3、如果不行，那么从库设置 

stop slave;
reset slave all;
CHANGE MASTER TO MASTER_HOST='xxxx', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='xxxx', master_auto_position=1;
start slave;

4、再观察 

5、如果开始复制了，但出现了主键冲突错误，加上--slave-skip-errors=1062 启动 

6、待从库复制追上后，再去掉--slave-skip-errors参数