MySql database backup and restore

Posted by rvdb86 on Mon, 07 Mar 2022 02:42:57 +0100

Backup (mysqldump)

Realize the following functions:

1. Back up the specified database

2. Delete the backup file before the specified number of days. 1 day is set by default

Script example (

# Database backup root directory

#Specify the host name of the host where mysql resides

#Specify mysql login user name

#Specify mysql login password

#Specify the database name for the backup

#Define the current date as a variable
CURRENT_DATE=$(date +"%Y%m%d$H")

#Define the file variable N days before deletion

#Specify the directory where mysqldump is located

#Create directory by date (database backup directory)

echo "---------------------------------------------------------"

# Determine whether the target path exists
if [ ! -d ${BASH_PATH} ];then
 echo "File does not exist, create folder"
 mkdir -p ${BASH_PATH}
 echo "${BASH_PATH} Folder already exists"

echo "Start backup ${DB_NAME} database"

#Back up the specified database
if $($MYSQLDUMP_DIR/mysqldump -h ${DB_HOSTNAME} -u${DB_USERNAME} -p${DB_PASSWORD} ${DB_NAME} > "${BASH_PATH}/${DB_NAME}_${CURRENT_DATE}.sql");then
gzip ${DB_NAME}_${CURRENT_DATE}.sql
echo "---------------------------------------------------------"
echo "${CURRENT_DATE}--Backup database ${DB_NAME} successfully!"
echo "---------------------------------------------------------"

echo "----------------------------------------------------------"
echo "${CURRENT_DATE}--Backup database ${DB_NAME} unsuccessfully"
echo "----------------------------------------------------------"

#Delete the backup files and directories specified N days ago
#For example, + 2 means 24 hours before 3 days - within 2:2 days, and 0 means within 1 day
find ${BACKUP_DIR} -name "*" -type f -mtime ${DEL_DAYS_BEFORE_FILES} -exec rm -rf {} \;
echo "Deleted ${DEL_DAYS_BEFORE_FILES}Backup files and directories of days ago"

echo "---------------------------------------------------------"

Grant file execution permission

chmod +x

Run test

# Current directory

If you report / bin/bash^M: bad interpreter: there is no file or directory ", you can solve it by changing several compilation methods, but it still doesn't work this time, so you change it again:


The reason may be that I modified this file while operating under win.

When editing under win, the end of the newline is \ n\r, while under linux it is \ n, so there will be more \ r

# Replace blank in file \ r
sed -i 's/\r$//'

Compile again! success!!

find -mtime introduction

find is a query command, and - time is a query based on time, which can be used as a condition. Different settings lead to different query results

The mtime parameter should be understood as follows:

-mtime n finds the file according to the change time of the file, and N is an integer.

N indicates that the file change time distance is n days
-N indicates that the file change time distance is within n days
+N indicates that the file change time is n days ago


-mtime 0 indicates that the file modification time is 0 days away from the current time, that is, the file is less than 1 day (24 hours) away from the current time.
-mtime 1 refers to the file whose modification time is 1 day away from the current time, that is, the file 1 day away from the current time (24-48 hours).
-mtime + 1 indicates that the file modification time is greater than 1 day, that is, the file is 2 days (48 hours) away from the current time
-mtime -1 refers to files whose modification time is less than 1 day, that is, files within 1 day (24 hours) from the current time

Why does - mtime + 1 mean that the file modification time is greater than 1 day, that is, the file 48 hours away from the current time, rather than 24 hours away? Because the value of n can only be an integer, that is, the nearest integer greater than 1 is 2, so - mtime + 1 is not greater than 1 day (24 hours) than the current time, but greater than 2 days (48 hours) than the current time

Scheduled task

Create scheduled tasks, such as performing backup operations at 2 a.m. every day

# Linux built-in command for regular program execution -l view all current scheduled program tasks
crontab -e

Edit the following content and save to exit:

# Execute the program regularly and save the log to the specified directory
* 2 * * * /usr/local/mysqlbackups/ &> /usr/local/mysqlbackups/tsmbak/mysql_bak.log

After editing, press Esc key, enter colon, enter wq, save and exit


Since the files after backup are compressed as follows, decompress them first

Use the gunzip command as follows:

gunzip xxxxxx_20220303.sql.gz

After decompression, it is sql suffix file. Yes, the view content is actually sql statements, as follows

The next step is to execute sql statements

1. Enter mysql database

mysql -u root -p enter and enter the password

2. View all current databases and specify the databases to restore

show databases; ------ view all databases

use 'databaseName'; ----- Specify the database to use

create database test01; ----- Create a new library

3. Using source to call sql

source /usr/local/mysqlbackups/tsmbak/xxxxxxx_20220303.sql ---- waiting for execution to complete

4. View restored tables or data

show tables; ----- view all tables in the current library

So far, it's finished

Some operation screenshots are as follows:


Topics: Linux Database MySQL