Automatic MySQL Database Backup

MySQL databaseThe data in is a very important data information and should be kept confidential. once lost, it will cause great losses.Data backupIt is the most important link in the MySQL database operation process. Next we will introduce the automatic backup of the MySQL database.

Prerequisites for creating a database backup

[1] creating an automatic backup script

Here, in order to make the 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 automatic database 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

Finally, test whether the automatic database backup can run normally. If it can run properly, it indicates that the database is automatically backed up. If it cannot, it may be that the link in the middle has an error and the system returns the result, redo it. I hope the content mentioned above will be helpful to you.