Shulou(Shulou.com)06/03 Report--

Editor to share with you how MySQL regularly back up the database, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

I. MySQL data backup

1.1.The mysqldump command backs up the data

Mysqldump, a convenient tool for exporting database data and files on the command line, is provided in MySQL. We can export dump of database content directly through the command line. First of all, let's have a brief understanding of the usage of mysqldump commands:

# MySQLdump commonly used mysqldump-u root-p-- databases database 1 Database 2 > xxx.sql1.2, mysqldump common operation examples

1. Back up the data and structure of all databases

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

two。 Back up the structure of all databases (plus-d parameter)

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

3. Back up the data of all databases (plus-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. Backing up the structure of a single database

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

6. Back up data from a single database

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

7. Back up 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. Back up multiple databases at a time

Mysqldump-uroot-p123456-- databases db1 db2 > / data/mysqlDump/mydb.sql1.3, restore mysql backup contents

There are two ways to restore, the first is on 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

Source / data/mysqlDump/mydb.sql II. Write scripts to maintain backup database files

In linux, BASH scripts are usually used to write what needs to be executed, plus regular execution of the command crontab to achieve automatic log generation.

The following code function is to back up mysql, together with crontab, to achieve daily mysql database records for nearly a month (31 days).

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

In Linux, script content using vi or vim and name it: mysql_dump_script.sh

#! / number of bin/bash# saved backups, 31 days data number=31# backup save path backup_dir=/root/mysqlbackup# date +% Ymure% MMI% dmurf% Hafe% Mmury% S` # backup tool tool=mysqldump# username username=root# password password=TankB214# database database_name=edoctor# to be backed up if the folder does not exist, create if [!-d $backup_dir]; then mkdir-p $backup_dir Fi# abbreviated as 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 out the backups delfile= `ls-l-crt $backup_dir/*.sql that need to be deleted | awk'{print $9}'| head-1` # determine the number of backups now Whether it is greater than $numbercount= `ls-l-crt $backup_dir/*.sql | awk'{print $9}'| wc-l`if [$count-gt $number] then # Delete the earliest backup Keep only the number of number backups rm $delfile # write delete file log echo "delete $delfile" > > $backup_dir/log.txtfi

The main meaning of the code above is as follows:

1. First, set the parameters, such as the maximum number of backups required by number, backup path, user name, password, etc.

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

3. Define the files to be deleted: get the ninth column, the file name column, through the ls command, and then use the

Head-1

The implementation defines the file that needs to be deleted at the latest operation time.

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

Wc-l

Counts the number of lines of files that end in sql.

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

2.2.Using crontab to execute backup scripts periodically

In LINUX, periodic tasks are usually handled by the cron daemon [ps-ef | grep cron]. Cron reads one or more configuration files that contain the command line and its call time.

The configuration file for cron is called "crontab", which is short for "cron table".

I. cron service

Cron is a timed execution tool under linux that can run jobs without human intervention.

Service crond start / / start the service service crond stop / / close the service service crond restart / / restart the service service crond reload / / reload the configuration service crond status / / view the service status II. Crontab syntax

The crontab command is used to install, delete, or list the tables used to drive cron background processes. The user puts the sequence of commands that need to be executed into the crontab file for execution. Each user can have their own crontab file. Crontab files under / var/spool/cron cannot be created or modified directly. The crontab file is created with the crontab command.

How to enter the command and time you need to execute in the crontab file. Each line in the file contains six fields, the first five of which are the specified time when the command is executed, and the last 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 value 00-59 00-23 01-31 01-120-6 (0 is sunday)

In addition to numbers, there are several special symbols that are "*", "/" and "-", * represent all the numbers in the range of values, "/" means each, "/ 5" means every five units, "-" represents from a number to a number, "," separate several discrete numbers.

-l displays the current crontab on standard output.

-r Delete the current crontab file.

-e use the editor referred to by the VISUAL or EDITOR environment variable to edit the current crontab file. When the finished editor leaves, the edited file will be installed automatically.

Create a cron script

Step 1: write a cron script file named mysqlRollBack.cron.

15 xgmtest.txt 30 echo 45 50 59 * print "xgmtest." > > 15 minutes, execute the print command

Step 2: add scheduled tasks. Execute the command "crontab crontest.cron". Done.

Step 3: "crontab-l" check whether the scheduled task is successful or check whether the corresponding cron script is generated under / var/spool/cron

Note: this operation is to directly replace the crontab under this user, not to add

Execute scheduled task scripts on a regular basis (remember to give shell script execution permission first)

0 2 * / root/mysql_backup_script.sh

A timing script that is then written with periodic instructions using the crontab command

Crontab mysqlRollback.cron

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

Crontab-l

Attached are examples of the use of crontab:

1. 6: 00 every morning

0 6 * echo "Good morning." > / tmp/test.txt / / Note simple echo, no output can be seen on the screen, because cron email any output to the mailbox of root.

two。 Every two hours

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

3. Every two hours and 8 a.m. between 11:00 and 8 a.m.

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

4. 11:00 on the 4th of every month and from Monday to Wednesday

0 11 4 * 1-3 command line

5. January 1st at 4: 00 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 there is data output, the data will be sent to this account HOME=/ by email

6. Execute scripts in / etc/cron.hourly every hour

01 * root run-parts / etc/cron.hourly

7. Execute scripts in / etc/cron.daily every day

02 4 * root run-parts / etc/cron.daily

8. Execute scripts in / etc/cron.weekly weekly

22 4 * 0 root run-parts / etc/cron.weekly

9. Execute the scripts in / etc/cron.monthly every month

42 4 1 * * root run-parts / etc/cron.monthly

Note: "run-parts" this parameter, 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 5 min, 15 min, 25 min, 35 min, 45 min, 55 min at 4: 00, 5: 00, and 6: 00 p.m.

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

11. The system goes into maintenance state at 3:00 every Monday, Wednesday and Friday, and restarts the system.

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

twelve。 Execute the innd/bbslin instruction in the user directory at 10 minutes and 40 minutes per hour:

10pr 40 * innd/bbslink

13. Execute the bin/account instruction in the user directory at 1 minute per hour:

1 * bin/account 3. Screenshot of execution effect

The following is the screenshot effect of my test per minute, and the corresponding code is as follows:

* / root/mysql_backup_script.sh

Screenshot of the effect:

The log.txt records the operation details of the backup:

The above is all the contents of the article "how to back up the database regularly by MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.