Students of background development and operation and maintenance are certainly familiar with MySQL backup. Regular online database backup is necessary, and this process is generally automatic. This paper will introduce a shell script, which has the function of automatically backing up MySQL and automatically clearing expired backup data
Backup script
The following is the content of the script for automatically backing up MySQL
#db user name
dbuser=root
#db password
dbpasswd="123456"
#ip address
dbip=127.0.0.1
#Database name prefix for backup
pre_name="test"
#Log files for backup operations
bakfile=/data/sqlbak/log.txt
#Directory of backup data
bakdatadir=/data/sqlbak/bakdata
#Directory of backup data - persistent backup
persist_path=/data/sqlbak/persistdata
#Backup save time, unit: day
dateoutday=15
#Backing up a single database
bak_single()
{
#db name
sdb=$1
#Date string
datestr=$2
echo "bak_single ${sdb}_${datestr}.sql.gz BEGIN..." >> ${bakfile}
#backups
mysqldump -u${dbuser} -h ${dbip} -p${dbpasswd} --single-transaction --no-create-db -R -C -B ${sdb} > ${sdb}_${datestr}.sql
#Comment out the USE DATABASE statement in the sql script
sed -i "s/USE/-- USE/" ${sdb}_${datestr}.sql
#compress
gzip ${sdb}_${datestr}.sql
#Log
echo "bak_single ${sdb}_${datestr}.sql.gz COMPLETE..." >> ${bakfile}
}
#Back up all databases
bak_all()
{
#Date string
all_datestr=$(date "+%Y_%m_%d_%H_%M_%S")
#
date_dir=$(date "+%Y%m%d")
#
echo "bak_all begin ${all_datestr} ====================" >> ${bakfile}
#All databases
alldb=`mysql -u${dbuser} -h ${dbip} -p${dbpasswd} -e "show databases"`
for dbname in ${alldb}; do
{
#Only databases with the specified prefix are backed up
if [[ ${dbname} =~ ${pre_name} ]]; then
bak_single ${dbname} ${all_datestr}
fi
}&
done
#Wait for all database backups to complete
wait
#compress
tar czvf dbbak_${all_datestr}.tar *_${all_datestr}.sql.gz
#delete
rm *_${all_datestr}.sql.gz
#Is there a directory named after the date of the day
if [ ! -d ${bakdatadir}/${date_dir} ]; then
mkdir -p ${bakdatadir}/${date_dir}
fi
#Move to a directory named after the date of the day
mv dbbak_${all_datestr}.tar ${bakdatadir}/${date_dir}
#
echo "bak_all finish dbbak_${all_datestr}.tar ====================" >> ${bakfile}
}
#Back up all databases - no scheduled deletion
bak_all_persist()
{
#Date string
all_datestr=$(date "+%Y_%m_%d_%H_%M_%S")
#
echo "bak_all_persist begin ${all_datestr} ====================" >> ${bakfile}
#All databases
alldb=`mysql -u${dbuser} -h ${dbip} -p${dbpasswd} -e "show databases"`
for dbname in ${alldb}; do
{
#Only databases with the specified prefix are backed up
if [[ ${dbname} == ${pre_name} ]]; then
bak_single ${dbname} ${all_datestr}
fi
}&
done
#Wait for all database backups to complete
wait
#compress
tar czvf dbpersistbak_${all_datestr}.tar *_${all_datestr}.sql.gz
#delete
rm *_${all_datestr}.sql.gz
#Is there a directory named after the date of the day
if [ ! -d ${persist_path} ]; then
mkdir -p ${persist_path}
fi
#Move to the directory of the persistent backup
mv dbpersistbak_${all_datestr}.tar ${persist_path}
#
echo "bak_all_persist finish dbbak_${all_datestr}.tar ====================" >> ${bakfile}
}
#Check for expired backups
check_date_out()
{
#current directory
curpath=`pwd`
#current date
curdate=$(date "+%Y%m%d")
#Earliest save date
lastdate=`date -d "${curdate} - ${dateoutday} day" +%Y%m%d`
#Enter backup directory
cd ${bakdatadir}
#Directory list
pathlst=`ls`
#Check whether the directory is expired and delete the expired directory
for tmpdate in ${pathlst[*]}; do
if [[ ${tmpdate} -le ${lastdate} ]]; then
rm -rf ${tmpdate}
echo "check_date_out, curdate:${curdate} delete ${tmpdate} " >> ${bakfile}
fi
done
#Return to current directory
cd ${curpath}
}
case "$1" in
s)
bak_single $2 $3
;;
a)
bak_all
;;
p)
bak_all_persist
;;
chk)
check_date_out
;;
*)
echo "Please use correct command..."
;;
esac
Function function
Backup is performed in the unit of database. First, a single database is backed up, and then all backup databases are packaged together
bak_ The single function means to back up a single data. The incoming parameters are the database name and date string to be backed up. The backup file name consists of these two parameters, that is, the backup file name consists of database name + date, such as test1_2021_08_16_10_05_30.sql represents the backup file of test1 database. The backup time is 2021_ 08_ 16_ 10_ 05_ thirty
bak_ The all function means to back up all databases without passing in parameters. First execute the SQL statement show databases to query all databases, and then filter out the databases we need to back up. The database names to be backed up in the script start with test. The specific filtering rules can be modified according to their own needs
bak_ The {and} in the body of the all function for loop and the & after them mean to start a new process and execute the command in the middle of the braces, that is, each database starts a process for backup. The wait command after the end of the for loop means to wait for the end of all processes in the for loop, that is, after all database backups are completed, Will execute the command after wait
After all backups are completed, a directory named after the current date will be created, the SQL scripts of all backed up databases will be packaged and placed in this directory, and the original backup files will be deleted
The sub database backup is adopted here. The advantage of sub database backup is that if all databases are backed up into one backup file, it is more troublesome to restore the data of one library. Therefore, the sub database backup is used to restore the data of a single database
check_ date_ The out function checks whether the backup directory has expired. If it has expired, the expired directory will be deleted directly. The variable dateoutday at the beginning of the script specifies the number of days to keep the backup
bak_all_ The persist function is the persistent backup, backup process and Bak_ The all function is the same, except that it is backed up to another directory of persistent data, and the variable persist at the beginning of the script_ Path specifies the persistent backup directory. The backup files in the directory will not be deleted automatically and need to be deleted manually
The main application scenario of persistent Directory: sometimes the online database table has data correction or the table structure has changed. In order to prevent misoperation, bak is called before operation_ all_ The persist function backs up the database so that the data can be recovered even in case of misoperation
Backup parameter description
- –single-transaction
This option will set the isolation level to REPEATABLE READ to ensure the consistency of the entire data during the dump process without locking the table. This option is very useful for exporting the data table of InnoDB
- –no-create-db
There will be a CREATE DATABASE statement similar to that in the normally exported SQL script. After adding the -- no create DB option, there will be no such statement
- -C
In the process of transmitting from the server to the client, it is compressed first and then transmitted
- -R
Export stored procedures and custom functions
After mysqldump exports the database SQL script, sed -i "s / use / -" use / "KAtex parse error: expected group after '' at position 6: {sdb}_ ̲ The {datestr}.sql command is used to comment out the USE DATABASE XXX statement in the SQL script, which is also practical. Sometimes the online data will be imported into the intranet to reproduce some bugs on the Internet, but the intranet may already have a database with the same name. If this line of statement is commented, it can be imported into other databases. Otherwise, the SQL script needs to be processed manually first, Then import
How to use
If the name of the backup MySQL script is bak.sh, the following is how to use the script
- Backup to a single database
Backup logindb database
./bak.sh logindb "2021_08_16_10_05_30"
After the above command is executed, logindb will be generated in the current directory_ 2021_ 08_ 16_ 10_ 05_ File of 30.sql.gz
- Back up all databases
./bak.sh a
- Check backup retention time
./bak.sh chk
- Persistent backup
./bak.sh p
Add scheduled task
To realize the automatic backup function, you also need to add a scheduled task, call the backup script at a specified time interval, execute the ctrontab -e command, and enter the following statement
*/10 * * * * /data/sqlbak/bak.sh a */15 * * * * /data/sqlbak/bak.sh chk
The above scheduled task is to back up all databases every 10 minutes and check expired backups every 15 minutes. Of course, the specific backup strategy can be adjusted according to the actual situation according to different scenarios
Summary
This paper provides a shell script for automatically backing up MySQL. The script can be directly used in the production environment with a little modification. The specific backup strategies, such as how often to back up and how long to keep the backup, can be adjusted according to the actual situation