MySQL Forums
Forum List  »  Backup

full backup from slave, relay logs not purged
Posted by: Chris Cheshire
Date: July 24, 2018 01:15PM

I have a master/slave replication setup for MySQL 5.7. For a long time I was doing weekly full backups off the master database using mysqldump, and incrementally copying the binary logs.

The locking started to cause issues with users so I moved it to the slave. Since then, relay logs are not getting purged from the slave. When the dump happened on the master, the slave's relay logs were getting purged automatically.

The slave backup script does the following:

mysqladmin --login-path=admin stop-slave > /dev/null
mysqldump --login-path=admin --single-transaction --flush-privileges \
--dump-slave=2 --include-master-host-port --all-databases \
--routines --triggers --events --flush-logs \
| gzip -9 -c > ${BACKUP_DIR}/${DUMP_FILE}
mysqladmin --login-path=admin start-slave > /dev/null
scp -q ${BACKUP_DIR}/${DUMP_FILE} ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_BACKUP_DIR}/


When I was dumping the master, it had two scripts instead
1) Full dump, run weekly :

# create a dump of the entire database
mysqldump --login-path=admin --single-transaction --flush-privileges \
--master-data=2 --all-databases --routines --triggers --events \
--flush-logs | gzip -9 -c > ${BACKUP_DIR}/${DUMP_FILE}

# copy this to the remote server
scp -q ${BACKUP_DIR}/${DUMP_FILE} ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_BACKUP_DIR}/


# purge all the binary logs created before previous backup
mysql --login-path=admin -e "purge master logs before date_sub(now(), interval 1 week);"

# remove the copied binary logs and sync this with the backup to remove all previous
# backed up binary logs
rm -fr ${BINLOG_DIR}/*
rsync -azhq --delete ${BINLOG_DIR}/ ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_BINLOG_DIR}/


2) Incremental copying of binary logs, done daily :
# get the list of current binary logs to copy
FILES=`cat ${INDEX_FILE}`

# flush the binary logs to start a new one
mysqladmin --login-path=admin flush-logs

# copy the previous files to the local backup directory
cd ${DATA_DIR}
for FILE in ${FILES}; do
rsync -azqh ${FILE} ${BINLOG_DIR}/
done

# sync the local backup directory to the remote backup
rsync -azqh ${BINLOG_DIR}/ ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_BINLOG_DIR}/



Any ideas why the relay logs aren't getting purged when dumping from the slave? The slave is up to date (compared GTIDs from slave status to master), and the relay_log_purge global variable is ON.

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
full backup from slave, relay logs not purged
1163
July 24, 2018 01:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.