mysql backup and crontab automatic backup under Linux

Using mysql dump of mysql to backup and restore data under liunx
The crontab command can help us to automatically and regularly back up the database.

Backing up a database

#Create backup directory first

mkdir -p /var/local/backup
cd /var/local/backup   

#Backup database
mysqldump -uusername -ppassword database > database_20180101.sql

Restore a database

#Log in mysql first
mysql -uroot -ppass 
#Show all databases
show databases;
#Select database to restore
use database;
#Restore a database
source /var/local/backup/database_20180101.sql;
#Quit mysql
exit;

Be careful:
Replace username with the actual user name;
Replace the password with the actual password;
Replace Database with the actual Database name;

crantab scheduled backup
1. New edit backup script

#Create a directory backup/data
mkdir /root/backup/data
#Entry directory
cd  /root/backup/data
#Create a. sh script file    
touch mysql_databse.sh
#Change script file permissions
chmod 755 mysql_databse.sh
#Edit script file
vim mysql_database.sh

Edit backup file

#!/bin/sh

DB_NAME="database"
DB_USER="username"
DB_PASS="password"

BIN_DIR="/usr/bin"
BCK_DIR="/root/baclup/data"
DATE=`date +%Y%m%d_%H%M%S`

# TODO
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS $DB_NAME   \
 > $BCK_DIR/$DB_NAME_$DATE.sql

Delete 30 days ago to backup data

find /root/backup/* -mtime +30 -exec rm {} \;

The sql file generated by dump is likely to be very large. We can also enable gzip compression. Generally speaking, it can achieve a compression ratio of 10 times: that is to say, the content output to the file is processed by the gzip program through the pipeline operator

$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS $DB_NAME  | gzip \
 > $BCK_DIR/$DB_NAME_$DATE.sql.gz

Add to crontab

crontab -e

Add a line. The root user does not need to specify the execution user name

1 1 * * * /root/backup/mysql_database.sh

View crontab

crontab -l

Test whether the task is executed

# tail -f /var/log/cron
Aug  3 15:50:01 iZ94bw0dj96Z CROND[15185]: (root) CMD (/usr/lib/sa/sa1 1 1)
Aug  3 16:00:01 iZ94bw0dj96Z CROND[15347]: (root) CMD (/usr/lib/sa/sa1 1 1)
Aug  3 16:01:01 iZ94bw0dj96Z CROND[15365]: (root) CMD (run-parts /etc/cron.hourly)
Aug  3 16:01:01 iZ94bw0dj96Z run-parts(/etc/cron.hourly)[15365]: starting 0anacron
Aug  3 16:01:01 iZ94bw0dj96Z run-parts(/etc/cron.hourly)[15374]: finished 0anacron
Aug  3 16:10:01 iZ94bw0dj96Z CROND[15520]: (root) CMD (/usr/lib/sa/sa1 1 1)
Aug  3 16:20:01 iZ94bw0dj96Z CROND[15695]: (root) CMD (/usr/lib/sa/sa1 1 1)
Aug  3 16:30:01 iZ94bw0dj96Z CROND[15864]: (root) CMD (/usr/lib/sa/sa1 1 1)
Aug  3 16:40:01 iZ94bw0dj96Z CROND[16026]: (root) CMD (/usr/lib/sa/sa1 1 1)
Aug  3 16:50:01 iZ94bw0dj96Z CROND[16196]: (root) CMD (/usr/lib/sa/sa1 1 1)

Tags: Database MySQL crontab SQL

Posted on Fri, 10 Jan 2020 09:25:06 -0800 by busin3ss