Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use shell to write a mysql data backup script, the article is very detailed, has a certain reference value, interested friends must read it!

Train of thought

Actually, it's very simple.

Write a shell script to export the data to the corresponding sql file through the mysqldump of mysql; use the crontab of linux to run the corresponding script regularly to save the sql file to the corresponding directory; it is conceivable that with the increase of data volume and the frequency of backup, the hard disk resource utilization of the backup server will soar; in order to solve this problem, we need to clean up the backup content regularly. However, I simply use a shell script to clean up regularly through crontab; note

Here are a few issues to pay attention to:

Export the sql of the corresponding database table through mysqldump, which will inevitably result in the resource consumption of the mysql server (cup, memory, io, etc.); the default way of mysqldump will cause table locking, which is terrifying, which will lead to the interruption of online services, perhaps for a short time, but it is fatal; (it can be changed to transactional form through configuration, without locking the table) as the amount of data increases, the time for mysqldump to export sql will also increase.

Of course, for data backup, you can choose the corresponding time period and define the backup cycle according to the situation of the corresponding business, or if there are many libraries on the mysql server, you can back up data in different time periods according to different businesses. It depends on the specific business situation!

Because mine is only for development and testing, the amount of data will not be very large, so the impact can be ignored, as long as everyone is backed up after work (so what I set is: backup at 12:00 every night. A month later, I cleaned up last month)

Permission description of 1.mysqldump

Description of permissions required by mysqldump:

For table, mysqldump must have at least select permission. For view, mysqldump needs to have show view permission. For trrigger, mysqldump needs to have trriger permission. If you want to produce a consistent backup mysqldump, you need lock tables permission.

The following is the user creation estimate (if you don't understand, you can do another google, so we won't expand it any more):

Create user dumper@'127.0.0.1';grant select on tempdb.* to dumper@'127.0.0.1';grant show view on tempdb.* to dumper@'127.0.0.1';grant lock tables on tempdb.* to dumper@'127.0.0.1';grant trigger on tempdb.* to dumper@'127.0.0.1'

2.shell script (export data sql)

#! / bin/sh# Database infoDB_USER= "dumper" DB_PASS= "..." DB_HOST= "..." # Database arrayDB_NAME= ("hotel"food"foodweb") # Others varsBIN_DIR= "/ usr/bin" # the mysql bin pathBCK_DIR= "/ home/mysql-backups" # the backup file directoryDATE= `date +% F` # create filemkdir $BCK_DIR/$DATE# TODO# / usr/bin/mysqldump-opt-ubatsing-pbatsingpw-hlocalhost timepusher > / mnt/mysqlBackup/ Db_ `date +% F`.sqlfor var in ${DB_NAME [@]} Do $BIN_DIR/mysqldump-- opt-- single-transaction-- master-data=2-u$DB_USER-p$DB_PASS-h$DB_HOST $DB_NAME > $BCK_DIR/$DATE/db_$var.sqldone

Parameter description:

-- master-data [= #]

Append the location and name of the binary binlog file to the backup exported file

If the value is equal to 1, a CHANGE MASTER statement is added

If the value is equal to 2, a comment will be added before the CHAGE MASTER statement.

This parameter locks the table-- lock-all-tables, unless you specify-- single-transaction.

In this case, the table lock will only last for a short period of time at the beginning of the dump. In theory, during the dump, any action will affect the binlog file. After the end of the dump, the option will automatically turn off the table lock function.

-- single-transaction

Execute in the form of a transaction

3.shell script (batch cleared N days ago script on time)

#! / bin/shfind / home/mysql-backups-mtime + 30-name "*. *"-exec rm-Rf {}\

Description:

/ home/lifeccp/dicom/studies: any directory to be cleaned-mtime: standard statement + 10: find files from 10 days ago, where numbers represent days, and + 30 indicate files from 30 days ago. ".jpg": the type of data you want to find. ".jpg" means to find all files with the extension jpg. "" means to find all files-exec: fixed writing rm-rf: force deletion of files. Include directory {} Put the results of find into the 4.crontab timed startup script

Crontab-e0 0 * / home/sh/mysql-backups/dump.sh0 0 1 * * / home/sh/mysql-backups/del.sh is all the contents of the article "how to use shell to write a mysql data backup script". Thank you for reading! Hope to share the content to help you, more related 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.