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 (mysql_bak.sh)

# Database backup root directory
BACKUP_DIR="/usr/local/mysqlbackups/tsmbak/"

#Specify the host name of the host where mysql resides
DB_HOSTNAME='hostname'

#Specify mysql login user name
DB_USERNAME='root'

#Specify mysql login password
DB_PASSWORD='xxxx'

#Specify the database name for the backup
DB_NAME="xxxx"

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

#Define the file variable N days before deletion
DEL_DAYS_BEFORE_FILES=1

#Specify the directory where mysqldump is located
MYSQLDUMP_DIR="/usr/bin"

#Create directory by date (database backup directory)
BASH_PATH=${BACKUP_DIR}/${CURRENT_DATE}

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

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

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
cd ${BASH_PATH}
gzip ${DB_NAME}_${CURRENT_DATE}.sql
echo "---------------------------------------------------------"
echo "${CURRENT_DATE}--Backup database ${DB_NAME} successfully!"
echo "---------------------------------------------------------"

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

#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 mysql_bak.sh

Run test

# Current directory
./mysql_bak.sh

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:
bash mysql_bak.sh

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$//' mysql_bak.sh

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

Example:

-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/mysql_bak.sh &> /usr/local/mysqlbackups/tsmbak/mysql_bak.log

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