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

What are the ways of regular backup of MySQL database? many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

one。 Mysqldump command to back up 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.sql II. Examples of common operations in mysqldump

Back up the data and structure of all databases

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

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

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

Back up the data of all databases (plus-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

Back up data from a single database

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

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

Back up multiple databases at a time

Mysqldump-uroot-p123456-- databases db1 db2 > / data/mysqlDump/mydb.sql III. 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

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

Mysql-uroot-p123456

Source / data/mysqlDump/mydb.sql

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

1. Write 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 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 delfile= `ls-l-crt $backup_dir/*.sql that needs to be deleted | awk'{print $9}'| head-1` # determine the current number of backups No 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 file that needs to be deleted: get the ninth column, the file name column, through the ls command, and then define the file that needs to be deleted with the latest operation time through the implementation.

4. Define the number of backups: count the number of lines of files ending in sql through the ls command plus wc-l.

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

2. Execute backup scripts on a regular basis using crontab

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

2.1cron service

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

Service crond start / / start service service crond stop / / close service service crond restart / / restart service service crond reload / / reload configuration service crond status / / View service status 2.2crontab 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:

Legal value of minute hour day-of-month month-of-year day-of-week commands 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.

2.3 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:

Fourth, attach an example 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.

(2)。 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). 4: 00 a. M. on January 1st

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

(12)。 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

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:

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.