MySQL Backup using mysqldump
Create file
|
1 2 |
mkdir /usr/serverok/ vi /usr/serverok/mysqldump-backup.sh |
Add
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
#!/bin/bash # Author: Yujin Boby # Email: [email protected] # Web: https://serverok.in/mysqldump-backup DB_BACKUP="/home/serverok-mysql-backup/`date +%Y-%m-%d`" mkdir -p $DB_BACKUP find /home/serverok-mysql-backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \; for db in $(mysql -e 'show databases' -s --skip-column-names); do if [ $db == "performance_schema" ] || \ [ $db == "information_schema" ] || \ [ $db == "phpmyadmin" ]; then continue fi echo "Backing up $db" /usr/bin/mysqldump --opt --events --routines --triggers $db | gzip -9 > "$DB_BACKUP/$db.sql.gz"; #/usr/bin/mysqldump --opt --events --routines --triggers $db > "$DB_BACKUP/$db.sql"; done # To back to Amazon S3 # /usr/local/bin/aws s3 cp --recursive /home/serverok-mysql-backup/ s3://BUCKET_NAME/mysql/ |
Make it executable
|
1 |
chmod 755 /usr/serverok/mysqldump-backup.sh |
Set cronjob
|
1 |
30 4 * * * /usr/serverok/mysqldump-backup.sh 2>&1>> /dev/null |
Backing up to Google Cloud Storage
Add following line to end of the script. Make sure you have Installed Google Cloud SDK and configured gsutil.
|
1 |
/usr/bin/gsutil -m rsync /home/serverok-mysql-backup/ gs://BUCKET_NAME/ |
Create a Googe Cloud Storage bucket of type Nearline. Set life cycle policy to delete files after 30 days.
Store Backups on Same directory
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
#!/bin/bash # Author: Yujin Boby # Email: [email protected] # Web: https://serverok.in/mysqldump-backup BACKUP_DIR="/backups/" BACKUP_DATE=$(date +%Y-%m-%d-%H%M) mkdir -p $BACKUP_DIR for db in $(mysql -e 'show databases' -s --skip-column-names); do if [ $db == "performance_schema" ] || \ [ $db == "information_schema" ] || \ [ $db == "mysql" ] || \ [ $db == "phpmyadmin" ]; then continue fi echo "Backing up $db" /usr/bin/mysqldump --opt --events --routines --triggers $db | gzip -9 > "${BACKUP_DIR}/${db}-${BACKUP_DATE}.sql.gz"; done # Delete backups older than 20 days find /backups/ -type f -name "*.sql.gz" -mtime +20 -delete |