Implementation of MySQL scheduled database backup (full database backup)

1. MySQL data backup

1.1, mysqldump command to back up data

MySQL provides a convenient tool mysqldump for exporting database data and files through the command line. We can directly export and dump the database content through the command line. First, let's take a brief look at the usage of the mysqldump command:

#MySQLdump commonly used 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 the entire database (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 data of a single database

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

7. Back up the data and structure of multiple tables (the method for backing up data and structure separately is the same as above)

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

8. Back up multiple databases at once

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. In the system command line, enter the following to restore:

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

2. After logging into the MySQL system, use the source command to find the file in the corresponding system and restore it:

mysql> source /data/mysqlDump/mydb.sql

2. Write scripts to maintain backup database files

In Linux, BASH scripts are usually used to write the content that needs to be executed, and the crontab command is used to execute the command at a scheduled time to realize automatic log generation.

The following code function is to back up MySQL and, in conjunction with crontab, back up the daily MySQL database records for the past month (31 days).

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

In Linux, use vi or vim to write the script content and name it: mysql_dump_script.sh

#!/bin/bash

#Save the number of backups, back up 31 days of data number=31
#Backup save path backup_dir=/root/mysqlbackup
#Date dd=`date +%Y-%m-%d-%H-%M-%S`
#Backup tool tool=mysqldump
#Username username=root
# Password password = TankB214
#The database to be backed up database_name=edoctor

#If the folder does not exist, create it if [ ! -d $backup_dir ]; 
then     
    mkdir -p $backup_dir; 
fi

#Simple way to 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 to 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`

#Judge 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 ]
then
  #Delete the earliest generated backup and keep only number of backups rm $delfile
  #Write the delete file log echo "delete $delfile" >> $backup_dir/log.txt
fi

The main meanings of the above code are as follows:

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

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

3. Define the files to be deleted: Use the ls command to obtain the ninth column, which is the file name column, and then use

head -1

The file with the latest operation time is defined by the implementation and needs to be deleted.

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

wc -l

Count the number of lines in files ending with sql.

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

2.2. Use crontab to execute backup scripts regularly

In LINUX, periodic tasks are usually handled by the cron daemon process [ps -ef | grep cron]. Cron reads one or more configuration files that contain command lines and the times they are called.
The cron configuration file is called "crontab", which is short for "cron table".

1. Cron service

Cron is a scheduled execution tool under Linux that can run jobs without human intervention.

  service crond start //Start the service service crond stop //Shut down the service service crond restart //Restart the service service crond reload //Reload the configuration service crond status //Check the service status

2. Crontab Syntax

The crontab command is used to install, remove, or list the tables used to drive the cron daemon. The user puts the command sequence to be executed in the crontab file to get it executed. Each user can have his or her own crontab file. The crontab file in /var/spool/cron cannot be created or modified directly. The crontab file is created by the crontab command.
How to enter the commands and time to be executed in the crontab file. Each line in this file includes six fields, the first five fields specify the time when the command is to be executed, and the last field is the command to be executed.
Each field is separated by spaces or tabs. The format is as follows:
minute hour day-of-month month-of-year day-of-week commands
Legal values: 00-59 00-23 01-31 01-12 0-6 (0 is Sunday)
In addition to numbers, there are several special symbols, namely "*", "/", "-", "", "*" represents all numbers within the value range, "/" means every, "/5" means every 5 units, "-" represents from a certain number to a certain number, and "," separates several discrete numbers.

• -l Display the current crontab on standard output.
• -r Delete the current crontab file.
• -e Edit the current crontab file using the editor specified by the VISUAL or EDITOR environment variable. When you finish editing and exit, the edited file will be automatically installed.

3. Create a cron script

Step 1: Write a cron script file and name it mysqlRollBack.cron.
15,30,45,59 * * * * echo "xgmtest....." >> xgmtest.txt means that the print command will be executed once every 15 minutes. Step 2: Add a scheduled task. Execute the command "crontab crontest.cron". Finish the third step: "crontab -l" to check whether the scheduled task is successful or check whether the corresponding cron script is generated in /var/spool/cron

Note: This operation directly replaces the crontab of the user, rather than adding a new one

Regularly execute the scheduled task script (remember to give the shell script execution permission first)

0 2 * * * /root/mysql_backup_script.sh

Then use the crontab command to write a scheduled script regularly

crontab mysqlRollback.cron

Then use the command to check whether the scheduled task has been created:

crontab -l

Attached is an example of using crontab:

1. 6am every day

0 6 * * * echo "Good morning." >> /tmp/test.txt //Note that with just echo, you cannot see any output on the screen because cron emails any output to root's mailbox.

2. Every two hours

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

3. Every two hours between 11pm and 8am and at 8am

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

4. On the 4th of every month and every Monday to Wednesday at 11am

0 11 4 * 1-3 command line

5. January 1 at 4 a.m.

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

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. If you remove this parameter, you can write the name of a script to be run instead of the folder name.

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

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

11. The system will enter maintenance mode and restart at 3:00 pm every Monday, Wednesday and Friday.

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

12. At 10 and 40 minutes of every hour, execute the command innd/bbslin in the user directory:

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 are the screenshots of my test every minute, and the corresponding code is as follows:

* * * * * /root/mysql_backup_script.sh

Effect screenshots:

The log.txt file records the detailed log of the backup operation:

References for this article:

1. Common commands for MySQLdump: https://www.cnblogs.com/smail-bao/p/6402265.html (Blog Park)

2. Use Shell script to back up the MySQL database: https://www.cnblogs.com/mracale/p/7251292.html (Blog Park)

3. Detailed explanation of Crontab scheduled execution task command under Linux: https://www.cnblogs.com/longjshz/p/5779215.html (Blog Park)

This concludes this article on the implementation of scheduled MySQL database backup (full database backup). For more relevant MySQL scheduled backup content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!