mysql dump slave 备份_MySQL复制特性利用Slave节点创建备份

应用mysqldump创建备份

在my.cnf文件中添加

[mysqldump]

user=system

password=123456

[client]

user=system

password=123456

库级备份

1.停止Slave服务中的SQL_THREAD线程

2.记录当前接收和应用的二进制日志文件及位置

3.执行备份命令

4.再次记录当前接收和应用的二进制日志文件及位置

5.启动Slave服务中的SQL_THREAD线程

创建Slave节点的备份脚本

[mysql@mysql1 ~]$ # more /data/mysqldata/scripts/mysql_full_backup_slave.sh

#!/bin/bash

#create by allen

source /data/mysqldata/scripts/mysql_env.ini

HOST_PORT=3307

MAIN_PATH=/data/mysqldata/${HOST_PORT}

DATA_PATH=/data/mysqldata/backup/mysql_full

DATA_FILE=${DATA_PATH}/dbfullbak_`date +%F`.tar.gz

LOG_FILE=${DATA_PATH}/dbfullbak_`date +%F`.log

MYSQL_PATH=/usr/local/mysql/bin

MYSQL_CMD="${MYSQL_PATH}/mysql -S ${MAIN_PATH}/mysql.sock"

MYSQL_DUMP="${MYSQL_PATH}/mysqldump -S ${MAIN_PATH}/mysql.sock --single-transaction"

show_slave_status(){

echo -e "---- master.info: ----" >> ${LOG_FILE}

cat ${MAIN_PATH}/data/master.info | sed -n '2,3p' >> ${LOG_FILE}

echo -e "---- show slave status: ---- " >> ${LOG_FILE}

echo "show slave status\G" | ${MYSQL_CMD} | egrep "Slave_IO_Running|Slave_SQL_Running|Master_Log_File|Master_Log_File|Read_Master_Log_Pos|Exec_Master_Log_Pos|Relay_Log_File|Relay_Log_Pos" >> ${LOG_FILE}

echo -e "" >> ${LOG_FILE}

}

echo > ${LOG_FILE}

echo -e "=== Jobs started at `date +%F' '%T' '%w` ===\n" >> ${LOG_FILE}

echo -e "**** started position: ====" >> ${LOG_FILE}

echo "stop slave SQL_THREAD;" | ${MYSQL_CMD}

show_slave_status

echo -e "*** Executed command:${MYSQL_DUMP} | gzip > ${DATA_FILE}" >> ${LOG_FILE}

${MYSQL_DUMP} | gzip > ${DATA_FILE}

echo -e "*** Executed finished at `date +%F' '%T' '%w` ===" >> ${LOG_FILE}

echo -e "*** Backup file size:`du -sh ${DATA_FILE}` ===\n" >> ${LOG_FILE}

echo -e "*** recheck position: ====" >> ${LOG_FILE}

show_slave_status

echo "start slave SQL_THREAD;" | ${MYSQL_CMD}

echo -e "--- Find expired backup and delete those files ---" >> ${LOG_FILE}

for tfile in $(/usr/bin/find $DATA_PATH -mtime +6)

do

if [ -d $tfile ] ; then

rmdir $tfile

elif [ -f $tfile ] ; then

rm -f $tfile

fi

echo -e "--- Delete file:$tfile ---" >> ${LOG_FILE}

done

echo -e "\n=== Jobs ended at `date +%F' '%T' '%w` ===\n" >> ${LOG_FILE}

复制文件方式创建备份

1.停止Slave节点数据库服务

2.复制数据库主目录

3.启动Slave节点数据库服务