Use shell to write a mysql data backup script and shellmysql Data Backup

Use shell to write a mysql data backup script and shellmysql Data Backup

Ideas

It's actually very simple.

Write a shell script and use mysqldump of mysql to export data to the corresponding SQL file. Use crontab of linux to regularly run the corresponding script and save the SQL statements and files to the corresponding directory, as the data volume increases and the backup frequency increases, the hard disk resource usage of the backup server will also rise. To solve this problem, we need to regularly clean up the backup content; I still simply used a shell script to regularly clean it through crontab. Note:

Note the following:

Using mysqldump to export the SQL statements of the corresponding database and table will inevitably cause resource consumption (cup, memory, io, etc.) on the mysql server; mysqldump will cause the lock table by default, this is terrible and may lead to the interruption of online services. It may take a short time, but it is fatal. (You can change the configuration to the transaction form without locking the table.) as the data volume increases, the time for mysqldump to export SQL also increases;

Of course, for data backup, you can select the corresponding time period to define the backup cycle based on the corresponding business conditions, or if there are many databases on the mysql server, backup is performed based on different business and different time periods; this is also possible... Look at the specific business situation!

Because I only use it for development and testing, and the data volume is not large, the impact can be ignored. As long as everyone is back up after work, It Doesn't Matter (so I decided: backups are backed up at 12 o'clock every night. A month later, the backups are cleared last month)

1. Permission description for mysqldump

Description of permissions required by mysqldump:

For tables, mysqldump requires the select permission at least. For view, mysqldump must have the show view permission. For trrigger, mysqldump requires the trriger permission. To generate a consistent backup, mysqldump must have the lock tables permission.

The following is the prediction of User Creation (if you do not understand it, You can google it separately, so there will be no more details ):

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 export 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 (does not work ~)
This parameter locks the -- lock-all-tables Table, unless you specify -- single-transaction
In this case, the lock table will only last for a short period of time at the beginning of dump. It is said that during dump, any action will affect the end Of the binlog file dump, option will automatically disable the table lock function

-- Single-transaction

Execute in the form of transactions

3. shell script (delete the script N days ago in batches on time)

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

Note:

/Home/lifeccp/dicom/studies, + 30 indicates searching for files 30 days ago".": Type of data to be searched ,". Jpg "indicates searching for all files with the jpg extension ,""Indicates to search for all files-exec: fixed syntax rm-rf: Force delete files, including directory {};: Put the find result in it 4. crontab timed start script

crontab -e0 0 * * * /home/sh/mysql-backups/dump.sh0 0 1 * * /home/sh/mysql-backups/del.sh