Creation after automatic backup failure in the MySQL database

This article mainly introduces the conditions required to create a database backup after the automatic backup in the MySQL database and the restoration after the MySQL database is damaged, including the creation of an automatic backup script, the MySQL database automatic backup script runs correctly and the database backup script runs automatically every day.

[1] creating an automatic backup script

Here, in order to make MySQL database backup and recovery meet our actual requirements, use a Shell script to automate the entire backup process.

[Root @ CentOS ~] # Vi mysql-backup.sh notebook create database automatic backup script, as follows:

 
 

  
  
#!/bin/bash   
  
  
PATH=/usr/local/sbin:/usr/bin:/bin   
  
  
# The Directory of Backup   
  
  
BACKDIR=/backup/mysql   
  
  
# The Password of MySQL   
  
  
ROOTPASS= 
 
 

Replace the star number with the MySQL root Password.

 
 

  
  
# Remake the Directory of Backup   
  
  
rm -rf $BACKDIR   
  
  
mkdir -p $BACKDIR   
  
  
# Get the Name of Database   
  
  
DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`   
  
  
# Backup with Database   
  
  
for dbname in $DBLIST   
  
  
do   
  
  
mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy   
  
  
done 
 
 

[2] Run the MySQL database automatic backup script

[Root @ CentOS ~] # Chmod 700 mysql-backup.sh changes the script properties so that it can only be executed by the root user

[Root @ CentOS ~] #./Mysql-backup.sh run script

[Root @ CentOS ~] # Ls-l/backup/mysql/check whether the backup is successful

Total 8

Drwxr-x --- 2 mysql 4096 Sep 1 mysql has been successfully backed up to the/backup/mysql directory

[3] Let the database backup script run automatically every day

[Root @ sample ~] # Crontab-e ← edit the automatic running rule and an editing window appears. The operation is the same as that in vi)

00 03 ***/root/mysql-backup.sh add this line to the file, let the database back up at every day

How to test whether automatic backup is normal or not)

Here, we will introduce the recovery method after the problem occurs through the actual operation process.