In the process of data operation, data errors and even database crash may occur. Effective periodic backup can protect the database well. This article mainly describes several methods for the MySQL regular backup database.
The mysqldump command backs up data
MySQL > dump dump dump dump dump dump dump dump dump dump dump dump dump dump dump
-u root -p --databases 1 database 2 > xxx.sqlCopy the codeExamples of common operations for mysqldump
- Back up all database data and structures
mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql
Copy the codeSQL > alter database name;
mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql
Copy the code- Back up all database data (add -t parameter)
mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql
Copy the code4. Back up the data and structure of a single database (mydb)
mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql
Copy the code- Backing up the structure of a single database
mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql
Copy the code- Back up data for a single database
mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql
Copy the code- Back up data and structure of multiple tables (same as above)
mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql
Copy the code- Back up multiple databases at once
mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql
Copy the codeRestore MySQL Backup the content
There are two ways to restore, the first is on the MySQL command line, and the second is to use SHELL lines to complete the restore
- In the system command line, enter the following to restore:
mysql -uroot -p123456 < /data/mysqlDump/mydb.sql
Copy the code- After logging in to the mysql system, use the source command to find the files in the corresponding system for restoration:
mysql> source /data/mysqlDump/mydb.sql
Copy the codeIn Linux, BASH scripts are used to compile the content to be executed and the crontab command is periodically executed to automatically generate logs.
The following code functions are for mysql backup. With crontab, the backup content is the daily mysql database records in the last 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 Backup_dir =/root/mysqlbackup # date dd= 'date +%Y-%m-%d-%H-% m-% S' # Backup tool tool=mysqldump # username Username =root # password=TankB214 # database_name=edoctor # if [! -d $backup_dir]; then mkdir -p $backup_dir; Mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename. SQL $tool -u $username -p$password Echo "create $backup_DIR /$database_name-$dd.dupm" >> # $backup_dir/log. TXT find need to delete the backup delfile = ` ls -l - CRT $backup_dir / *. SQL | awk '{print $9}' | ` head - 1 # to judge whether the number of backup now greater than $number count = ` ls -l - CRT $backup_dir / *. SQL | awk '{print $9}' | wc -l ` if $count - gt $number then Echo "delete $delfile" >> $backup_dir/log.txtCopy the codeThe main meanings of the above code are as follows:
1. Set parameters, such as number Maximum number of backup files, backup path, user name, and password.
2. Run the mysqldump command to save the backup file and print operation logs to log. TXT in the same directory.
3. Define the file to be deleted: Run the ls command to obtain the ninth column, that is, the file name, and define the file to be deleted by implementing the latest operation.
4. Define the number of backups: Run the ls command to add the number
Count the number of rows of files that end in SQL.
5. If the size of the file exceeds the limit, delete the earliest SQL file
Use crontab to periodically execute backup scripts
In Linux, periodic tasks by cron commonly the daemon to deal with [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, short for Cron table.
Cron service
Cron is a timed execution tool under Liunx that can run jobs without human intervention.
Service crond start service crond stop service crond restart service crond reload // Reload the service Crond status // View the service status
Crontab grammar
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 in the crontab file for execution. Each user can have their own crontab file. The crontab file in /var/spool/cron cannot be created or modified directly. The crontab file is created using the crontab command.
How to enter the command and execution time in the crontab file. Each line in the file contains six fields, with the first five fields specifying when the command is to be executed and the last field specifying 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 Valid value 00-59 00-23 01-31 01-12 0-6 (0 is Sunday)
In addition to numbers, there are several special symbols: *, /, and -,”,”. * stands for all numbers in the range,”/ “stands for each,”/5″ stands for every five units,” -” stands for going from one number to another,”,” separates several discrete numbers.
-l Displays the current crontab on standard output. -r Deletes the current crontab file. -e Edit the current crontab file using the EDITOR indicated in the VISUAL or EDITOR environment variable. When you leave, the edited file will be installed automatically.
Create the cron script
Step 1: Write the cron script file named mysqlRollBack. Cron. 15,30,45,59 * * * * echo “xgmtest…..” >> xgmtest. TXT Indicates that the command is executed every 15 minutes. Step 2: Add a scheduled task. Run the crontab crontest.cron command. Step 3: run the crontab -l command to check whether the scheduled task succeeds or whether the cron script is generated in /var/spool/cron
Note: This operation directly replaces the crontab of the user, not adds it
Execute your scheduled task scripts on a regular basis (remember to give your shell script execution permission first)
0 2 * * * /root/mysql_backup_script.sh
Copy the codeThe crontab command is then used to write periodic scripts
crontab mysqlRollback.cron
Copy the codeRun the following command to check whether a scheduled task has been created:
Examples of crontab usage:
- Every morning at six o ‘clock
0 6 * * * echo "Good morning." >> / TMP /test.txt //Copy the code- Every two hours
0 */2 * * * echo "Have a break now." >> /tmp/test.txt
Copy the code- Every two hours between 11 p.m. and 8 a.m. and 8 a.m
0 23-7/2, 8 * * * echo "Have a good dream" >> / TMP /test.txtCopy the code- On the 4th of the month and every Monday through Wednesday at 11 a.m
0 11 4 * 1-3 command line
Copy the codeMay 1st at 4:00 a.m
1 1 0 4 * command line SHELL = / bin/bash PATH = / sbin: / bin: / usr/sbin, / usr/bin MAILTO = root / / if there is an error, or a data output, The data is emailed to the account HOME=/Copy the code- Hourly the /etc/cron.hourly script is executed
01 * * * * root run-parts /etc/cron.hourly
Copy the code- Run the script in /etc/cron.daily every day
02 4 * * * root run-parts /etc/cron.daily
Copy the code- Run the script in /etc/cron.weekly every week
22 4 * * 0 root run-parts /etc/cron.weekly
Copy the code- Execute the scripts in /etc/cron.monthly every month
42 4 1 * * root run-parts /etc/cron.monthly
Copy the codeNote: the “run-parts” parameter is used. If this parameter is removed, you can write the name of the script to be run instead of the folder name.
- The commands are executed at 4 PM, 5 PM, 6 PM, 5 min, 15 min, 25 min, 35 min, 45 min, and 55 min.
5,15,25,35,45,55, 16,17,18Copy the code- On Mondays, Wednesdays, and Fridays at 3:00 PM the system goes into maintenance and restarts.
00 15 * * 1，3，5 shutdown -r +5
Copy the code- Execute the innd/bbslin command in the user directory at 10 minutes, 40 minutes per hour:
10，40 * * * * innd/bbslink
Copy the code- Run the bin/account command in the user directory at 1 minute per hour:
Here is a screenshot of my test per minute, with the corresponding code as follows:
* * * * * /root/mysql_backup_script.sh
Copy the codeEffect screenshot:
Log. TXT records the backup operation details:
Reference for this article:
1. Run the MySQLdump command www.cnblogs.com/smail-bao/p/6402265.html 2. Using Shell script to the mysql database backup: www.cnblogs.com/mracale/p/7251292.html 3. Crontab regularly perform tasks under the Linux command a: www.cnblogs.com/longjshz/p/5779215.htmlCopy the code
Click the card below/wechat search, follow the public account “Tianyu Creative Music” (ID: GH_CC865e4C536b)
Tianyu Creative music school
Personal subscription number mainly provides: sharing the latest information IT tutorial free novel platform
15 original articles
The public,
This article uses the article synchronization assistant to synchronize