MySQL:备份策略 - Master or Slave？

Question

我们已经设置了 MySQL(5.1 版)和主从​​设置。我们需要提出一个备份策略，我们将使用 mysqldump。

我们打算使用flushlogs、master-date=2 和single-transaction 选项。

我只想知道从哪个节点运行备份更好，主节点还是从节点？

Answer 1

您应该在 Slave 上运行备份，这样 Master 就不会以任何方式中断，Master 可以保持生产模式。

这里是一个脚本，用于在Slave上备份mysql数据，并且只保留最后7个备份

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
BACKUP_HOME=/some/path/to/your/backups
BACKUP_TIME=`date +"%Y%m%d_%H%M%S"`
BACKUP_FILE=${BACKUP_HOME}/MySQLData_${BACKUP_TIME}.sql
BACKUP_GZIP=${BACKUP_FILE}.gz

MYSQLDUMP_OPTIONS="--single--transaction --routines --triggers"
#
# Stop Replication
# Dump the Data
# Resume Replication
#
mysql ${MYSQL_CONN} -ANe"STOP SLAVE"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -A | gzip > ${BACKUP_GZIP}
mysql ${MYSQL_CONN} -ANe"START SLAVE"

#
# Rotate Out Old Backups based on BACKUPS_TO_KEEP
#
cd ${BACKUP_HOME}
FILES_TO_DELETE=/tmp/BackupFilesToDelete.txt
BACKUPS_TO_KEEP=7
BACKUPS_ON_HAND=`ls MySQLData_*.gz | wc -l`
if [ ${BACKUPS_ON_HAND} -gt ${BACKUPS_TO_KEEP} ]
then
    (( DIFF = BACKUPS_ON_HAND - BACKUPS_TO_KEEP ))
    ls -l MySQLData_*.gz | head -${DIFF} > ${FILES_TO_DELETE}
    for FIL in `cat ${FILES_TO_DELETE}` ; do rm -f ${FIL} ; done
fi

您不能使用 --master-data=2 因为备份来自 Slave 而不是 Master。如果升级 MySQL 5.5，可以使用 --dump-slave在 MYSQLDUMP_OPTIONS 中从 SHOW SLAVE STATUS\G 中捕获 Master 的二进制日志和位置。您将在转储的第 23 行找到 --dump-slave 的输出。

如果你想继续使用 MySQL 5.1 并且你想记录来自 Master 的坐标，使用这个

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
BACKUP_HOME=/some/path/to/your/backups
BACKUP_TIME=`date +"%Y%m%d_%H%M%S"`
BACKUP_COOR=${BACKUP_HOME}/MySQLCoor_${BACKUP_TIME}.txt
BACKUP_FILE=${BACKUP_HOME}/MySQLData_${BACKUP_TIME}.sql
BACKUP_GZIP=${BACKUP_FILE}.gz

MYSQLDUMP_OPTIONS="--single--transaction --routines --triggers"
#
# Stop Replication
# Dump the Data
# Capture Master Coordinates
# Resume Replication
#
mysql ${MYSQL_CONN} -ANe"STOP SLAVE"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -A | gzip > ${BACKUP_GZIP}
mysql ${MYSQL_CONN} -Ae"SHOW SLAVE STATUS\G" > /tmp/SSS.txt
grep "Relay_Master_Log_File:" /tmp/SSS.txt  > ${BACKUP_COOR}
grep "Exec_Master_Log_Pos:"   /tmp/SSS.txt >> ${BACKUP_COOR}
mysql ${MYSQL_CONN} -ANe"START SLAVE"
#
# Rotate Out Old Backups based on BACKUPS_TO_KEEP
#
cd ${BACKUP_HOME}
FILES_TO_DELETE=/tmp/BackupFilesToDelete.txt
BACKUPS_TO_KEEP=7
BACKUPS_ON_HAND=`ls MySQLData_*.gz | wc -l`
if [ ${BACKUPS_ON_HAND} -gt ${BACKUPS_TO_KEEP} ]
then
    (( DIFF = BACKUPS_ON_HAND - BACKUPS_TO_KEEP ))
    ls -l MySQLData_*.gz | head -${DIFF} > ${FILES_TO_DELETE}
    for FIL in `cat ${FILES_TO_DELETE}` ; do rm -f ${FIL} ; done
fi

如果想在Master上自动旋转二进制日志，只需在Master上配置即可

[mysqld]
expire-logs-days=7

试一试!!!