Writing daily automatic backup of MySQL database using mysqldump in Centos7

1. Requirements:

Database backup is particularly important for production environments. Database backup is divided into physical backup and logical backup.

Physical backup: Use related copy commands to directly copy the data in the database's data directory or make multiple copies. Commonly used tools: XtraBackup.

Logical backup: Use the mysqldump command that comes with MySQL to store the data to be backed up in the form of SQL statements.

Next we will use the mysqldump command to back up data. Use automated tasks for daily backups.

2. Execution file writing:

1. Create a folder, and the executed sh file and database backup file are stored in this folder

mkdir /usr/local/mysqlDataBackup
cd /usr/local/mysqlDataBackup

2. Create a sh file and write a shell script:

backup.sh

Single database script:

#!/bin/bash
#This scripts is for auto backup databases
#create by weijb at 2021-07-28
#delete data from 7 day before

DATAdelete='date +%F -d "-7 day"'
rm -rf /usr/local/mysqlDataBackup/*_${DATAdelete}.sql.gz

MYSQL_CMD=/usr/bin/mysqldump
MYSQL_USER=account MYSQL_PWD=password DATA='date +%F'
DBname=test_db

${MYSQL_CMD} -u${MYSQL_USER} -p${MYSQL_PWD} --compact -B ${DBname} | gzip >/usr/local/mysqlDataBackup/${DBname}_${DATA}.sql.gz

Analysis:

(1) Delete backup data from 7 days ago to save space:
DATAdelete='date +%F -d "-7 day"' The date 7 days before the current time

rm -rf /usr/local/mysqlDataBackup/*_${DATAdelete}.sql.gz Delete the backup data of the previous 7 days to save space and only keep the latest 7 days of data

(2) Define variables:
MYSQL_CMD=/usr/bin/mysqldump The location of the mysqldump command that comes with mysqlMYSQL_USER=Account The account to connect to mysqlMYSQL_PWD=Password The password to connect to mysqlDATA='date +%F' Get the current date, %F represents the format: %Y-%m-%d For example: 2021-07-28
DBname=test_db The name of the database to be backed up. The current database name is: test_db
(3) Execute the backup command:
${MYSQL_CMD} -u${MYSQL_USER} -p${MYSQL_PWD} --compact -B ${DBname} | gzip >/usr/local/mysqlDataBackup/${DBname}_${DATA}.sql.gz
--compact optimizes output information -B contains database creation statements gzip compressed backup to reduce backup file size usr/local/mysqlDataBackup/${DBname}_${DATA}.sql.gz backup file save path and name

Multiple database backup:

#!/bin/bash
#This scripts is for auto backup databases
#create by weijb at 2021-07-28
#delete data from 7 day before
DATAdelete='date +%F -d "-7 day"'
rm -rf /usr/local/mysqlDataBackup/*_${DATAdelete}.sql.gz

MYSQL_CMD=/usr/bin/mysqldump
MYSQL_USER=account MYSQL_PWD=password DATA='date +%F'
DBname='mysql -u${MYSQL_USER} -p${MYSQL_PWD} -e "show databases;" | sed '1,5d''

for DBname in ${DBname}
do 
  ${MYSQL_CMD} -u${MYSQL_USER} -p${MYSQL_PWD} --compact -B ${DBname} | gzip >/usr/local/mysqlDataBackup/${DBname}_${DATA}.sql.gz
done

Analysis:

(1) Intercept the database that needs to be backed up

-e "show databases;" | sed '1,5d'' displays all databases and captures all databases except for mysql's own information_schema, mysql, performance_schema, and sys.

(2) Other details are the same as single database backup

3. Verify that the script is written successfully:

(1) Script file authorization:

cd /usr/local/mysqlDataBackupchmod +x backup.sh

(2) Execute the script:

sh backup.sh

The files generated under /usr/local/mysqlDataBackup indicate that the writing is successful.

4. Write automatic tasks:

Back up the database at 1 am every day:

crontab -e

Type i to edit:

00 01 * * * /usr/local/mysqlDataBackup/backup.sh

Use esc to exit the file editing and enter :wq to save and exit.

Check whether the task is set successfully:

crontab -l

Restart the crontab service:

service crond restart

At this point, the mysql automatic task script to back up the database is completed.

This is the end of this article about using mysqldump to perform daily automatic backup of MySQL database in CentOS 7. For more relevant MySQL automatic backup using mysqldump, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!