MySQL Backup using mysqldump
Create file
mkdir /usr/serverok/ vi /usr/serverok/mysqldump-backup.sh
Add
#!/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
chmod 755 /usr/serverok/mysqldump-backup.sh
Set cronjob
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.
/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
#!/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