[FreeBSD] MySQL Backup
This article is adapted from SpinupWP - How to Set Up MySQL Incremental Backups.
By end of this article, you should be able to set up Incremental backup for your MySQL instance and restore the database. Please read aforementioned article on snapshot backup and incremental backup.
This article assumes you are running MySQL in a FreeBSD system planning for effective backup process. -- edit `/usr/local/etc/mysql/my.cnf` with following entries.
log-bin = mysql-bin
binlog-format = row
expire_logs_days = 7
server-id = 1
Restart your MySQL instance.
-- To check if the settings have been applied , log into your MySQL instance with the root user, and run following MySQL commands:
show binary logs;
show variables like 'server_id';
show variable like 'expire_logs_days';
show variable like 'binlog_format';
Create First Full Backup
Create a backup directory and run following command:
`mysqldump --set-gtid-purged=OFF --flush-logs --delete-master-logs --single-transaction --all-databases -uroot -p<PASSWORD> > /mnt/backup/databaseSQLBackup/$(date +%d-%m-%Y_%H-%M-%S)_databases.sql`
This will dump your database in a SQL statement.
It is important to understand what this command is doing.
- The --flush-logs option flushes the MySQL server log files and starts writing a new binary log file.
- The
`--delete-master-logs`` `option deletes any old binary log files. If you are using a version of MySQL after 8.0.26, you should use` ``--delete-source-logs`` `instead, as` ``--delete-master-logs`was deprecated in MySQL 8.0.26. - The
`--single-transaction`option is needed if you have tables that use the InnoDB storage engine. -
The
`--all-databases`option tells the command to dump all databases.You will notice that binary logs has changed in
`/var/db/mysql/`.
Backup Binary Logs
It is best to first flush the binary logs, to do this log into your MySQL instance and run the following commands:
`FLUSH BINARY LOGS;`If you inspect the contents of the`/var/db/mysql`directory, you will see new log file created.
Now we back up all the files that are not the current binary log file.
After copying the relevant binary log files, we need to delete them from the log directory. This is to ensure that we do not duplicate already backed up data the next time we copy the binary log files. We can use the PURGE BINARY LOGS statement, which deletes all the binary log files listed in the log index file prior to the specified log file name or date. In our case we want to delete all the binary log files except the current one, so we log into the MySQL server and run the following command:
`PURGE BINARY LOGS TO '<CURRENT_BINARY_LOGFILE>';`
If we inspect the contents of the `/var/db/mysql` directory, we will see that the log file we copied has been deleted.
Restore from Binary Logs
To restore the binary logs from a specific point in time, we first need to restore the most recent full backup: `mysql -u root -p<PASSWORD> < /mnt/backup/databaseSQLBackup/$(date +%d-%m-%Y_%H-%M-%S)_databases.sql`
Now we can restore the binary logs we copied after the last full backup. We do this by using the `mysqlbinlog`` `utility to view the log data for the specific database, and piping that output to the` ``mysql` utility for the database in question.
`mysqlbinlog --no-defaults mysql-bin.<VERSION> | mysql -u root -p<PASSWORD>`
Note Following script was obtained from SpinupWP website, adapt to your FreeBSD MySQL instance
Given that these commands are run as the root user from the terminal, it’s also possible to automate all of this with a batch script. At the same time, we’d want to allow the script to back up multiple possible binary log files.
#!/bin/sh
# set up the date variable
NOW=$(date +%Y%m%d%H%M%S)
BINLOG_BACKUP=${NOW}_binlog.tar.gz
# set up the database credentials
DB_USER=root
DB_PASSWORD=root_password
# binary log files directory path
BINLOGS_PATH=/var/log/mysql/
# flush the current log and start writing to a new binary log file
mysql -u$DB_USER -p$DB_PASSWORD -E --execute='FLUSH BINARY LOGS;' mysql
# get a list of all binary log files
BINLOGS=$(mysql -u$DB_USER -p$DB_PASSWORD -E --execute='SHOW BINARY LOGS;' mysql | grep Log_name | sed -e 's/Log_name://g' -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')
# get the most recent binary log file
BINLOG_CURRENT=`echo "${BINLOGS}" | tail -n -1`
# get list of binary logs to be backed up (everything except the most recent one)
BINLOGS_FOR_BACKUP=`echo "${BINLOGS}" | head -n -1`
# create a list of the full paths to the binary logs to be backed up
BINLOGS_FULL_PATH=`echo "${BINLOGS_FOR_BACKUP}" | xargs -I % echo $BINLOGS_PATH%`
# compress the list of binary logs to be backed up into an archive in the backup location
tar -czvf /sites/backups/$BINLOG_BACKUP $BINLOGS_FULL_PATH
# delete the binary logs that have been backed up
echo $BINLOG_CURRENT | xargs -I % mysql -u$DB_USER -p$DB_PASSWORD -E --execute='PURGE BINARY LOGS TO "%";' mysql
By now you can set up cronjob to automate the backup of the database SQL and Binary log files.