Several methods of MySQL scheduled backup are stable!

Author: letcafe

1, MySQL data backup

1.1. Backup data with mysqldump command

MySQL provides a convenient tool mysqldump for exporting database data and files from the command line. We can directly export the database content through the command line. First, let's briefly understand the usage of mysqldump command:

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

1.2 common operation examples of mysqldump

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

1.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

2, Write scripts to maintain backed up database files

In linux, BASH script is usually used to write the content to be executed, and crontab is executed regularly to realize automatic log generation.

The following code function is to backup mysql. In cooperation with crontab, the backup content is the daily MySQL database records in recent one month (31 days).

2.1. Prepare BASH to maintain a fixed number of backup files

In Linux, 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

The main meanings of the above codes are as follows:

1. First set various parameters, such as number, the maximum number to be backed up, backup path, user name, password, etc.

2. Execute mysqldump to save the backup file, print the operation to log.txt in the same directory, and mark the operation log.

3. Define the file to be deleted: get the ninth column, that is, the file list, through the ls command, and then

head -1

The implementation defines the file to be deleted with the latest operation time.

4. Define the number of backups: add by ls command

wc -l

Count the number of lines of files ending in sql.

5. If the file size exceeds the limit, delete the sql file created the earliest

2.2. Use crontab to execute backup scripts regularly

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. The cron configuration file is called "crontab", which is short for "cron table".

cron service

cron is a timed execution tool under linux, which can run jobs without human intervention.

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

The crontab command is used to install, delete, or list the tables used to drive the cron daemon. The user puts the command sequence to be executed into the crontab file for execution. Each user can have its own crontab file/ Crontab files under var/spool/cron cannot be created or modified directly. The crontab file is created by the crontab command.

How to enter the command and time to execute in the crontab file. Each line in the 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.

-l Displays the current on standard output crontab.  

-r Delete current crontab File. 

-e use VISUAL perhaps EDITOR The editor to which the environment variable refers edits the current crontab File. When you finish editing and leave, the edited file will be installed automatically. 
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 the command "crontab crontest.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

Note: this operation is to directly replace the crontab under the user instead of adding a new one

Execute the scheduled task script written regularly (remember to give the shell script execution permission first)

0 2 * * * /root/

The crontab command is then used to periodically script the instructions

crontab mysqlRollback.cron

Then check whether the scheduled task has been created through the command:

crontab -l

An example of using crontab is attached:

1. At 6 o'clock every morning

0 6 * * * echo "Good morning." >> /tmp/test.txt //Note that if you simply echo, you can't see any output from the screen, because cron email ed any output to the root mailbox.

2. Every two hours

0 */2 * * * echo "Have a break now." >> /tmp/test.txt  

3. Every two hours and 8 a.m. between 11 p.m. and 8 a.m

0 23-7/2,8 * * * echo "Have a good dream" >> /tmp/test.txt

4. The 4th of each month and 11 a.m. from Monday to Wednesday every week

0 11 4 * 1-3 command line

5.1 at 4 a.m

0 4 1 1 * command line SHELL=/bin/bash PATH=/sbin:/bin:/usr/sbin:/usr/bin MAILTO=root //If there is an error or data output, the data will be sent to this account HOME as an email=/ 

6. Execute the script in / etc/cron.hourly every hour

01 * * * * root run-parts /etc/cron.hourly

7. Execute the script in / etc/cron.daily every day

02 4 * * * root run-parts /etc/cron.daily 

8. Execute the script in / etc/cron.weekly every week

22 4 * * 0 root run-parts /etc/cron.weekly 

9. Execute the script in / etc/cron.monthly every month

42 4 1 * * root run-parts /etc/cron.monthly 

Note: the "run parts" parameter is. If you remove this parameter, you can write the name of a script to run instead of the folder name.  

10. Execute the command at 4:00 p.m., 5:00 p.m. and 5 min, 15 min, 25 min, 35 min, 45 min and 55 min at 6:00 p.m. every day.

5,15,25,35,45,55 16,17,18 * * * command

11. At 3:00 p.m. on Monday, Wednesday and Friday, the system enters the maintenance state and restarts the system.

00 15 * * 1,3,5 shutdown -r +5

12. Execute the command innd/bbslin in the user directory at 10 and 40 minutes every hour:

10,40 * * * * innd/bbslink

13. Execute the bin/account command in the user directory at 1 minute every hour:

1 * * * * bin/account

3, Screenshot of execution effect

The following is the screenshot effect of my test every minute, and the corresponding code is as follows:

* * * * * /root/

Effect screenshot:

log.txt records the detailed log of backup operation:


Recent hot article recommendations:

1.1000 + Java interview questions and answers (2021 latest version)

2.Stop playing if/ else on the full screen. Try the strategy mode. It's really fragrant!!

3.what the fuck! What is the new syntax of xx ≠ null in Java?

4.Spring Boot 2.5 heavy release, dark mode is too explosive!

5.Java development manual (Songshan version) is the latest release. Download it quickly!

Feel good, don't forget to like + forward!

Tags: Java

Posted on Fri, 24 Sep 2021 19:48:29 -0400 by jana