Last updated: 19/01/2016
Kinamo Virtual Server (VPS) and Kinamo Cloud Server customers can use a simple shell script to dump the contents of MySQL databases to a chronologically ordered map.
The advantage of such an easy backup is the ease of data protection in case of a database crash and the flexibility to quickly restore an entire database, without having to ask a file level based restore by Kinamo support. Another advantage is the ease of restoring InnoDB databases in case of a system crash. Working with MySQL dump makes a restore easier, since working with IDB files can quickly become a problem when trying to do a restore.
Kinamo offers the following script, we advise you to put the script in /usr/sbin/local.
In order not to over-complexify this post we have named it kinamo_mysqlbackup.sh.
#!/bin/bash # Basic configuration: datestamp e.g. YYYYMMDD DATE=$(date +"%Y%m%d") # Location of your backups (create the directory first!) BACKUP_DIR="/backup/mysql" # MySQL login details MYSQL_USER="root" MYSQL_PASSWORD="YOURSECUREPASSWORD" # MySQL executable locations (no need to change this) MYSQL=/usr/bin/mysql MYSQLDUMP=/usr/bin/mysqldump # MySQL databases you wish to skip SKIPDATABASES="Database|information_schema|performance_schema|mysql" # Number of days to keep the directories (older than X days will be removed) RETENTION=14 # ---- DO NOT CHANGE BELOW THIS LINE ------------------------------------------ # # Create a new directory into backup directory location for this date mkdir -p $BACKUP_DIR/$DATE # Retrieve a list of all databases databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"` # Dumb the databases in seperate names and gzip the .sql file for db in $databases; do echo $db $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz" done # Remove files older than X days find $BACKUP_DIR/* -mtime +$RETENTION -delete
Copy the entire contents of the block above to a script names kinamo_mysqlbackup.sh and put it in the folder /usr/local/sbin/.
Change the constant variables as you wish (for example, your password) and make sure the folder /backup/mysql exists.
Give the script execution rights
chmod 755 kinamo_mysqlbackup.sh
Next thing is to set up a simple cronjob to schedule a backup every night at 1 am:
vi /etc/crontab 0 1 * * * root /usr/local/sbin/kinamo_mysqlbackup.sh
Save the crontab and restart the cron service with one of the following commands, depending on your Linux distro:
service cron restart service crond restart
Every night, the script will add a backup of all your databases in the folder /backup/mysql/YYYYMMDD/<yourdatabase dump>