Tag Archives: mysql database

How to Backup MySQL database regularly

Requirement: regularly back up MySQL specific database

Pre:

MySQL socket path;

# netstat -ln | grep mysql
unix  2      [ ACC ]     STREAM     LISTENING     37194    /tmp/mysql.sock

Backup command, named after dbname + date:

mysqldump --socket=/tmp/mysql.sock -u root -ppassword dbname > dbname_back_`date +%F`.sql

Mount to the public disk, there is no need for SCP;

Prepare the SH file, back up DB to a specific path, package it, and delete the backup for more than 30 days:

#!/bin/bash
backdir=/mnt/backup_db
d=`date +%F`

mysqldump --socket=/tmp/mysql.sock -u root -ppassword dbname > $backdir/dbname_back_$d.sql

cd $backdir
gzip *_$d.sql
find ./ -name "*.gz" -mtime +30 |xargs rm

Set sh file as executable:

chmod +x /mnt/backup_db/mysql_backup.sh

Determine the backup frequency and execute it at 7:59 a.m. every Friday;

# crontab -e
59 07 * * 5 /bin/bash /mnt/backup_db/mysql_backup.sh >/tmp/mysql_backup.log 2>/tmp/mysql_backup.log