Several ways to schedule backup of MySQL database (comprehensive)

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

2. Examples of common mysqldump operations

Back up the data and structure of all databases

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

Back up the structure of the entire database (add -d parameter)

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

Back up all database data (add -t parameter)

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

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

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

Backing up the structure of a single database

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

Backing up a single database

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

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

Back up multiple databases at once

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, the second is to use the SHELL line to complete the restore

In the system command line, enter the following to restore :

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

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

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

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 be backed up, backup路徑,用戶名,密碼, 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, that is, the file name column, and then implement the definition of the file with the latest operation time that needs to be deleted.

4. Define the number of backups: Use the ls command plus wc -l to count the number of lines in the file ending with sql .

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

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

2.1 Cron Service

Cron is a scheduled execution tool under Liunx 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.2crontab syntax

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 crontab file to get it executed. Each user can have his or her own crontab file. The 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 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 "*" , "/" , "-" , and "," . * represents all numbers within the value range, " / " represents 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.

2.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 command will be executed and printed once every 15 minutes.

Step 2: Add a scheduled task. Execute the command "crontab crontest.cron". Done

Step 3: "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:

4. Attached is an example of using crontab:

(1) 6:00 a.m. 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 11:00 a.m.

0 11 4 * 1-3 command line

(5) January 1 at 4:00 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 minutes, 15 minutes, 25 minutes, 35 minutes, 45 minutes, and 55 minutes after 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 p.m. every Monday, Wednesday and Friday.

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

(12) At 10 and 40 minutes past 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

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

* * * * * /root/mysql_backup_script.sh

Effect screenshots:

This concludes this article about several ways to schedule backup of MySQL database (comprehensive). For more information about several ways to schedule backup of MySQL database, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!