Write a mysql data backup script using shell

Ideas

It's actually very simple

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

There are a few issues to note here:

Exporting the SQL of the corresponding library table through mysqldump will inevitably cause resource consumption of the mysql server (cup, memory, io, etc.); the default method of mysqldump will cause table locks, which is terrible and will cause online service interruptions. It may be short-lived, but it is fatal; (You can change it to transaction mode through configuration, without locking the table) As the amount of data increases, the time it takes 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 corresponding business situation. Or if there are many databases on the MySQL server, you can back up at different time periods according to different businesses; this is also possible. . . It depends on the specific business situation!

Because I only use it for development and testing, the amount of data is not very large, so the impact can be ignored. It doesn't matter as long as I back it up after everyone gets off work (so I set it to: back up at 12 o'clock every night, and clean up last month's data after a month)

1.mysqldump permissions

Description of permissions required for mysqldump:

mysqldump must have at least select permission on the table. For views, mysqldump must have the show view permission. For trrigger, mysqldump must have trriger privileges. To produce a consistent backup, mysqldump requires the lock tables privilege.

The following is the user creation estimate (if you don’t understand, you can google it separately, I won’t go into 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 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 export file. If the value is equal to 1, a CHANGE MASTER statement will be added. If the value is equal to 2, a comment will be added before the CHANGE MASTER statement (it doesn't work~)
This parameter will lock the table --lock-all-tables unless you specify --single-transaction
In this case, the table lock will only last for a short time at the beginning of the dump. In theory, any action during the dump will affect the binlog file. After the dump is completed, the option will automatically turn off the table lock function.

--single-transaction

Execute as a transaction

3. Shell script (clear N days ago scripts in batches on schedule)

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

illustrate:

/home/lifeccp/dicom/studies: prepare any directory to be cleaned-mtime: standard statement writing +10: search for files 10 days ago, where numbers represent days, +30 means search for files 30 days ago" . ": the type of data you want to search for, " .jpg" means search for all files with the extension jpg, " " means search for all files-exec: fixed writing rm -rf: force delete files, including directories{};: put the results of find in it4.crontab scheduled start script

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