MySQL scheduled backup database

1, The mysqldump command backs up data

MySQL provides a convenient tool mysqldump for exporting database data and files from the command line. We can export database contents directly from the command line.

#MySQL dump common
mysqldump -u root -p --databases Database 1 database 2 > xxx.sql

2, mysqldump common operation examples

1. Back up the data and structure of all databases

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

2. Back up the structure of all databases (add - d parameter)

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql

3. Back up all database data (add - t parameter)

mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

4. Back up the data and structure of a single database (, database name mydb)

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql

5. Back up the structure of a single database

mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql

6. Back up the data of a single database

mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql

7. Backup the data and structure of multiple tables (the separate backup method of data and structure is the same as above)

mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql

8. Backup multiple databases at one time

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

3, Restore MySQL backup content

There are two ways to restore. The first is in the MySQL command line, and the second is to use the SHELL line to complete the restore

1. On the system command line, enter the following to restore:

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

2. After logging into the mysql system, find the files in the corresponding system through the source command to restore:

mysql> source /data/mysqlDump/mydb.sql

4, Write BASH to maintain a fixed number of backup files

Use vi or vim to write the script content and name it:


#Save the number of backups and back up the 31 day data
#Backup save path
dd=`date +%Y-%m-%d-%H-%M-%S`
#Backup tools
#user name
#Database to be backed up

#Create if folder does not exist
if [ ! -d $backup_dir ];
    mkdir -p $backup_dir;

#Simply write mysqldump - U root - p123456 users > / root / mysqlbackup / users - $filename.sql
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#Write create backup log
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#Find the backup that needs to be deleted
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#Determine whether the current number of backups is greater than $number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
  #Delete the earliest generated backups and keep only the number of backups
  rm $delfile
  #Write delete file log
  echo "delete $delfile" >> $backup_dir/log.txt

5, Use crontab to execute backup scripts periodically

In Linux, tasks executed periodically are generally handled by the cron daemon [ps -ef|grep cron]. Cron reads one or more configuration files that contain the command line and its invocation time.

cron service

service crond start    //Start service
service crond stop     //Shut down service
service crond restart  //Restart service
service crond reload   //service crond reload 
service crond status   //View service status 

crontab syntax

/Crontab files under var/spool/cron cannot be created or modified directly. The crontab file is created by the crontab command. Each line in the crontab file includes six fields. The first five fields specify the time when the command is executed, and the last field is the command to be executed.
Each field is separated by a space or tab.

The format is as follows:

minute hour day-of-month month-of-year day-of-week commands
Legal value 00-59 00-23 01-31 01-12 0-6 (0 is sunday)

In addition to numbers, there are several special symbols, namely "*", "/" and "-", "", * represents all numbers within the value range, "/" represents the meaning of each, "/ 5" represents every 5 units, "-" represents from a number to a number, "and" separates several discrete numbers.

Create cron script

Step 1: write a cron script file named mysqlRollBack.cron.

15,30,45,59 * * * * echo "xgmtest....." >> xgmtest.txt  Indicates that the print command is executed every 15 minutes 

Step 2: add scheduled tasks.

Execute command“ crontab mysqlRollBack.cron". Done 

Step 3: "crontab -l" check whether the scheduled task is successful or whether the corresponding cron script is generated under / var/spool/cron

Tags: Database MySQL Big Data

Posted on Sat, 20 Nov 2021 11:47:53 -0500 by lottos