Click on "end of life", pay attention to the official account.
Daily technical dry goods, delivered at the first time!
In the process of operating data, it may lead to data errors and even database collapse, and effective scheduled backup can well protect the database. This article mainly describes several methods for MySQL scheduled database backup.
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 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
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
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).
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 and back up the 31 day data
number=31
#Backup save path
backup_dir=/root/mysqlbackup
#date
dd=`date +%Y-%m-%d-%H-%M-%S`
#Backup tools
tool=mysqldump
#user name
username=root
#password
password=TankB214
#Database to be backed up
database_name=edoctor
#Create if folder does not exist
if [ ! -d $backup_dir ];
then
mkdir -p $backup_dir;
fi
#Simple writing 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`
#Judge whether the current backup quantity is greater than $number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`
if [ $count -gt $number ]
then
#Delete the oldest generated backup and keep only number Number of backups
rm $delfile
#Write delete file log
echo "delete $delfile" >> $backup_dir/log.txt
fi
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 the mysqldump command to save the backup file and print the operation to log in the same directory Txt.
3. Define the file to be deleted: get the ninth column, that is, the file list, through the ls command, and then define the file to be deleted with the latest operation time.
4. Define the number of backups: add with ls command to 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
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.
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 crontab on the standard output.
-r delete the current crontab file.
-e edit the current crontab file using the EDITOR indicated by the VISUAL or EDITOR environment variable. 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/mysql_backup_script.sh
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:
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 / etc / cron every hour Script in hourly
01 * * * * root run-parts /etc/cron.hourly
7. Execute / etc / cron. Every day Scripts in daily
02 4 * * * root run-parts /etc/cron.daily
8. Execute / etc / cron. Every week Script in weekly
22 4 * * 0 root run-parts /etc/cron.weekly
9. Execute / etc / cron. Every month Scripts in monthly
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:
The following is the screenshot effect of my test every minute, and the corresponding code is as follows:
* * * * * /root/mysql_backup_script.sh
Effect screenshot:
Where log Txt record the detailed log of backup operation:
Reference:
1. MySQL dump common commands
www.cnblogs.com/smail-bao/p/6402265.html
2. Use Shell script to backup mysql database:
www.cnblogs.com/mracale/p/7251292.html
3. Detailed explanation of cronab regularly executing task command under Linux:
www.cnblogs.com/longjshz/p/5779215.html
PS: in case you can't find this article, you can collect some likes for easy browsing and searching.