Write an mysql data backup script with shell

Train of thought

It's actually very simple

Write an shell script and export the data to the corresponding sql file through mysqldump of mysql; Use linux crontab to run the corresponding script regularly, and save sql and files to the corresponding directory; It is conceivable that with the increase of data volume and the frequency of backup, the utilization rate of hard disk resources of backup server will also rise linearly; In order to solve this problem, we need to clean up the backup content regularly; And I simply used an shell script, through crontab timing to clean up; Attention

Here are a few issues to pay attention to:

The sql of the corresponding library table is derived through mysqldump, which will inevitably cause the resource consumption of mysql server (cup, memory, io, etc.); The default way of mysqldump will cause lock table, which is terrible and will lead to the interruption of online service. Maybe the time is short, but it is fatal; (It can be changed into transaction form through configuration, and the table is not locked.) With the increase of data volume, the time for mysqldump to export sql will also increase;

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

Because my only development and testing, the amount of data will not be very large, so the impact can be ignored, as long as everyone is back up after work, it doesn't matter (so I set it: every night at 12 o'clock backup, 1 month after the clean-up last month)

1. Permission description of 1. mysqldump

Description of permissions required for mysqldump:

For table, mysqldump must have select permission at least. For view, mysqldump must have show view permissions. For trrigger, mysqldump must have trriger permission. ES50tables permissions are required if you want to generate a 1-copy backup of mysqldump.

The following is the user's creation expectation (if you don't understand it, you can use google separately, so you won't expand it much):

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 info
DB_USER="dumper"
DB_PASS="..."
DB_HOST="..."
# Database array
DB_NAME=("hotel" "food" "foodweb")
# Others vars
BIN_DIR="/usr/bin"      #the mysql bin path
BCK_DIR="/home/mysql-backups"  #the backup file directory
DATE=`date +%F`
# create file
mkdir $BCK_DIR/$DATE
# TODO
# /usr/bin/mysqldump --opt -ubatsing -pbatsingpw -hlocalhost timepusher > /mnt/mysqlBackup/db_`date +%F`.sql
for 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.sql
done

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, an CHANGE MASTER statement is added
If the value is equal to 2, a comment will be added before the CHAGE MASTER statement (it doesn't work ~)
This parameter will--lock-all-tables lock 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 reasonable to say that any action will affect the binlog file after dump ends, and the option will automatically close the lock table function

--single-transaction

Execute as a transaction

3. shell scripts (clear N scripts in batches on time)

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

Description:

/home/lifeccp/dicom/studies: Prepare any directory to clean up-mtime: Standard statement +10: Find files 10 days ago, where numbers represent days, +30 means find files 30 days ago ".": The type of data you want to find, ". jpg" means find all files with jpg extension, "" means find all files-exec: Fixed writing rm-rf: Forced deletion of files, including directories {}; Put the results of find inside 4. crontab timed startup script

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