Install the software
Before we can configure backups and the like, you’ll need to install the MySQL Enterprise Backup software:$ tar xvzf meb-3.9.0-linux2.6-x86-64bit.tar.gz
meb-3.9.0-linux2.6-x86-64bit/
meb-3.9.0-linux2.6-x86-64bit/bin/
meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup
meb-3.9.0-linux2.6-x86-64bit/README.txt
meb-3.9.0-linux2.6-x86-64bit/LICENSE.mysql
meb-3.9.0-linux2.6-x86-64bit/manual.html
meb-3.9.0-linux2.6-x86-64bit/mysql-html.css
I then placed the mysqlbackup
binary in my MySQL “bin” directory :$ cp meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup /usr/local/mysql/bin/
$ which mysqlbackup
/usr/local/mysql/bin/mysqlbackup
Now we’ve installed the software, we can go on and prepare our database for backup.Gather information, create backup user
In this example, I needed the following information:- MySQL port (often “3306“)
- Backup user and password (“backup/mysupersecret” in this example)
- Location for backup data (“/var/backups/mysql/backups” in this example)
$ mysql -u root -pWith these steps complete, we can now create a shell script to automate our backups.
mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY 'mysupersecret';
mysql> GRANT RELOAD ON *.* TO 'backup'@'localhost';
mysql> GRANT CREATE, INSERT, DROP, UPDATE ON mysql.ibbackup_binlog_marker TO 'backup'@'localhost';
mysql> GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'backup'@'localhost';
mysql> GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'backup'@'localhost';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
mysql> GRANT SUPER ON *.* TO 'backup'@'localhost';
mysql> GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'backup'@'localhost';
mysql> FLUSH PRIVILEGES;
Shell script for full backup
Based on your backup concept (you have one, right?), you might want to schedule the following shell script to run multiple times per day, daily or weekly. The most important command in the script is the following:/usr/local/mysql/bin/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-logThis command calls the
mysqlbackup
binary with the following arguments :Argument | Description |
---|---|
--port |
Port used to connect to the database instance during backup operations. |
--protocol |
Protocol used to connect to the database. |
--user , --password |
ID and password of privileged MySQL user. |
--with-timestamp |
Creates a subdirectory underneath the backup directory, with a name formed from the timestamp of the backup operation. Useful to maintain a single backup directory containing many backup snapshots. |
--backup-dir |
The directory under which to store the backup data. This is a crucial parameter required for most kinds of backup operations. |
backup-and-apply-log |
This option performs an extra stage after the initial backup, to bring all InnoDB tables up-to-date with any changes that occurred during the backup operation, so that the backup is immediately ready to be restored. |
#!/bin/bashYou can then schedule it to run daily (
BACKUP_DIR=/var/backups/mysql/backups
BACKUP_PASS=mysupersecret
BACKUP_USER=backup
DATE_DAY=$(date +"%Y-%m-%d")
DATE_HOUR=$(date +"%H")
EMAIL_RECIPIENT=kiloccnp@gmail.com
/usr/local/mysql/bin/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log
NO_OF_COMPLETE_OK_MESSAGES=$(cat $BACKUP_DIR/${DATE_DAY}_${DATE_HOUR}*/meta/MEB_${DATE_DAY}.${DATE_HOUR}*.log | grep "mysqlbackup completed OK" | wc -l)
# Note that the string "mysqlbackup completed OK" must occur 2 times in the log in order for the backup to be OK
if [ $NO_OF_COMPLETE_OK_MESSAGES -eq 2 ]; then
# Backup successful, find backup directory
echo "Backup succeeded"
exit 0
else
echo "MySQL backup failed, please check logfile" | mail -s "ERROR: MySQL Backup Failed!" ${EMAIL_RECIPIENT}
exit 1
fi
crontab -e
) at 04:00 in the morning for example:0 4 * * * /var/backups/mysql/make-mysql-backup.sh
For more information on the topic of MySQL Enterprise Backup
Không có nhận xét nào:
Đăng nhận xét