MySQL scheduled backup database

One, MySQL data backup 1.1, mysqldump command backup data

Provides a handy tool for command line export of database data and files in MySQL mysqldump, we can directly implement the export of database content through the command line dump, first we briefly understand the mysqldump command usage:

-U root-p--databases database 1 database 2 > Xxx.sql

1.2, Mysqldump Common operation example

1. Back up the data and structure of all databases

Mysqldump-uroot-p123456-a >/data/mysqldump/mydb.sql

2. Backing up the structure of all databases (plus-d parameter)

Mysqldump-uroot-p123456-a-D >/data/mysqldump/mydb.sql

3. Backing up data from all databases (plus-t parameters)

Mysqldump-uroot-p123456-a-T >/data/mysqldump/mydb.sql

4. Backing up the data and structure of a single database (, database name MyDB)

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

5. Backing up the structure of a single database

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

6. Backing up data from a single database

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

7. Backup multiple tables of data and structure (data, the structure of a separate backup method and the same)

mysqldump-uroot-p123456 mydb T1 T2 >/data/mysqldump/mydb.sql

8. Back up multiple databases at a 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 of which is on the MySQL command line, and the second is to complete the restore using the shell line

1. On the system command line, enter the following to implement the restore:

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

2. In the login into the MySQL system, through the source command to find the corresponding system files to restore:

Mysql> Source/data/mysqldump/mydb.sql

Second, write a script to maintain the backup database files

In Linux, it is common to use BASH scripts to write what needs to be done, plus timed execution of commands crontab automate the generation of logs.

The following code function is for MySQL backup, with crontab, to achieve the contents of the backup for nearly one months (31 days) of the daily MySQL database records.

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 Backup count, backup 31 days data number= to#备份保存路径backup_dir=/root/mysqlbackup# DateDD=`Date+%y-%m-%d-%h-%m-%S ' #备份工具tool=mysqldump# User name username=root# Password Password=tankb214# The database that will be backed up database_name=edoctor# Create if the folder does not existif[ ! -d $backup _dir];  Then         mkdir-p $backup _dir;fi#简单写法 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 logEcho "Create $backup _dir/$database _name-$dd. DUPM">> $backup _dir/log.txt# Find the backup delfile that needs to be deleted=`ls-L-CRT $backup _dir/*. sql | awk ' {print $9} ' | head-1 ' #判断现在的备份数量是否大于 $numbercount = ' ls-l-crt $backup _dir/*.sql | awk ' {print $9} ' | wc -L ' If [$count-gt $number]then #删除最早生成的备份, keep only the number of backup RM $delfile #写删除文件日志 echo "Delete $delfile" >> $backu P_dir/log.txtfi

The main meanings of the above code are as follows:

1. First set up the parameters, such as number of the most need to backup, backup path, user name, password and so on.

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

3. Define the files that need to be deleted: The ls command gets the nineth column, the file name, and

Head -1

Implement the file that you want to delete the last time you defined the operation.

4. Define the number of backups: with the LS command plus

WC -L

Counts the number of lines of files ending in SQL.

5. Delete the oldest created SQL file if the file exceeds the limit size

2.2. Use crontab to perform regular backup scripts

In Linux, the tasks performed by the cycle are typically 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" and is shorthand for "cron table".

First, cron service
Cron is a timed execution tool under Linux that can run a job without human intervention.
Service Crond Start//Startup services
Service Crond stop//Shutdown services
Service Crond restart//Restart services
Service Crond Reload//Reload Configuration
Service Crond Status//view services statuses

Second, crontab Grammar

The crontab command is used to install, delete, or list tables used to drive a cron background process. The user places the sequence of commands that need to be executed in the crontab file for execution. Each user can have their own crontab file. The crontab file under/var/spool/cron cannot be created directly or modified directly. The crontab file was created with the crontab command.
How to enter the command and time to execute in the crontab file. Each row in the file contains six fields, the first five of which are the time the specified command was executed, and the last field is the command to be executed.
Spaces or tabs are used to separate each field. 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 the numbers there are several special symbols that are "*", "/" and "-", ",", * represent all the values within the range of the number, "/" for each meaning, "/" for each of the 5 units, "-" represents from a number to a number, "," separate several discrete numbers.

-L Displays the current crontab on the standard output.
-R Deletes the current crontab file.
-e Edit the current crontab file using the editor referred to in visual or editor environment variables. When you finish editing, the edited file is automatically installed.

Iii. Creating a cron script
The first step: Write a cron script file named Mysqlrollback.cron.
15,30,45,59 * * * * echo "xgmtest ..." >> Xgmtest.txt, every 15 minutes, executes the print once command
Step Two: Add a scheduled task. Executes the command "Crontab Crontest.cron". Get
Step three: "Crontab-l" to see if the timed task is successful or if the corresponding cron script is generated under/var/spool/cron

Note: This is a direct replacement for the user under the crontab, not the new

Periodically execute scripted scheduled task scripts ( Remember to execute permissions on the shell script first )

0 2 * * */root/mysql_backup_script. SH

Timed scripts that are then written using the crontab command for periodic instructions

Crontab Mysqlrollback.cron

Then check whether the scheduled task was created by command:

Crontab-l

Examples of the use of the attached crontab:

1.6 o ' Day in the morning

0 6 Echo " Good morning. " // Note that echo alone does not show any output from the screen, because cron emails any output to the root mailbox. 

2. Every two hours

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

3. From 11 o'clock to 8 a.m. every two hours and eight a.m.

0 7/2,8echo"a good dream" >>/ Tmp/test.txt

4.4th on each month and Monday to Friday at three a.m. 11

0  One 4 1-3 command line

5.1 months, 1st, morning, 4.

0 4 1 1 //

6. Execute scripts within/etc/cron.hourly per hour

 * * * * * Root run-parts/etc/cron.hourly

7. Daily execution of scripts within/etc/cron.daily

 Geneva 4

8. Execute scripts within/etc/cron.weekly every week

 A 4 0

9. Every month to execute scripts within the/etc/cron.monthly

 the 4 1

Note: "Run-parts" This parameter, if you remove this parameter, you can later write to run a script name, not the folder name.

10. Every day 4 o'clock in the afternoon, 5, 6 points of 5 min, min, min, min, min, min, when the execution of orders.

5,(+), +---- * * command

11. Every Monday, three, five 3:00 the system enters the maintenance state and restarts the system.

xx  the 1,3,5 shutdown-r +5

12.10 minutes per hour, 40 minutes execute the innd/bbslin command in the user directory:

Ten, * * * * * Innd/bbslink

13.1 minutes per hour execute the bin/account command in the user directory:

1 * * * * bin/account

Iii. Effect of implementation

Here is the effect of my test per minute, with the corresponding code as follows:

* * * * * */root/mysql_backup_script. SH

Effect:

The detailed log of the operations where the Log.txt records backups:

This article refers to:

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

2. Backup of MySQL database with shell script: https://www.cnblogs.com/mracale/p/7251292.html (blog Park)

3.Linux crontab timed execution of task commands in detail: https://www.cnblogs.com/longjshz/p/5779215.html (Blog Park)

MySQL scheduled backup database