Tag: mysql backup

  • MySQL Backup using mysqldump

    Create file

    mkdir /usr/serverok/
vi /usr/serverok/mysqldump-backup.sh

    Add

    #!/bin/bash
# Author: Yujin Boby
# Email: admin@serverok.in
# 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: admin@serverok.in
# 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

    backup

  • automysqlbackup

    Auto backup all your MySQL databases daily, weekly and monthly.

    To install on Debian/Ubuntu, run

    apt install automysqlbackup -y

    To manually backup, run

    automysqlbackup

    MySQL backups will be stored in folder

    /var/lib/automysqlbackup/

    See backup

  • Plesk MySQL Daily Backup Script

    Create file

    mkdir /usr/serverok/
vi /usr/serverok/plesk-mysql-backup

    With the following content

    #!/bin/bash
# Author: admin@serverok.in
# Web: https://serverok.in/plesk-mysql-daily-backup-script

if [ ! -d "/mysql-backup/" ]
then
    mkdir /mysql-backup/
fi

mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "show databases" | grep -v "+-------------" | grep -v "Database" | grep -v "information_schema" | grep -v "performance_schema" > /tmp/sok-dbs.txt

for db in `cat /tmp/sok-dbs.txt`
do
    /usr/bin/mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` --opt --triggers --routines --events $db > /mysql-backup/$db.sql
    if [ $? -ne 0 ]; then
        # if you need email alert when backup fail, uncomment following line, add your email address
        # echo "database backup $db failed - `date`" | mail -s 'MySQL Backup failed' you@your-email.com
        echo "Backup failed"
    fi
done

    If you need multiple copies of MySQL database kept, use

    https://gist.github.com/serverok/5a247a5b8485a6a29764f12118a7f727

    Make it executable

    chmod 755 /usr/serverok/plesk-mysql-backup

    Set it to run as cronjob

    1 1 * * * /usr/serverok/plesk-mysql-backup > /var/log/sok-mysqldb-backup.log 2>&1