MySQL automatic backup and disaster recovery

After the database server is set up, the first thing we need to do is not to consider which MySQL-initiated programs should be run on the server that supports the database, but when the database is damaged, how to safely restore to the last normal state, minimizing data loss.

In other words, just the establishment of a database server can only describe what it can do, and it does not mean what it can do stably. Disaster recovery efficiency and comprehensiveness are also a quasi-factor in system stability, especially for a server system.

This section describes the automatic database backup and restoration methods after the database is damaged. Here, we use mysqlhotcopy and define a Shell script to implement automatic database backup. In addition, the entire process of automatic data backup and data recovery is based on Shell.

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 @ sample ~] # Vi mysql-backup.sh notebook create database automatic backup script, as follows:
# Declare SHELL and execution path
#! /Bin/bash

PATH =/usr/local/sbin:/usr/bin:/bin

# Set the Backup Directory
BACKDIR =/backup/mysql

# Set the ROOT password
ROOTPASS = ********* swap the star number with the MySQL root Password

# Redo the Backup Directory
Rm-rf $ BACKDIR
Mkdir-p $ BACKDIR

# Obtain the Database Name
DBLIST = 'LS-p/var/lib/mysql | grep/| tr-d/'# tr-translate or delete characters [-d, -- delete]
# Backing up a 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 @ sample ~] # Chmod 700 mysql-backup.sh supervisor changes the script attributes so that it can only be executed by the root user

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

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

Total 8
Drwxr-x --- 2 mysql 4096 Sep 1 mysql slave 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 (the edit window appears and 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
Test whether automatic backup works properly (Backup Recovery Method) Here, we will introduce the restoration method after the problem occurs through the actual operation process.

[1] restoration method after database Deletion
First, create a database for testing.
[Root @ sample ~] # Mysql-u root-p login use root to log on to the MySQL server
Enter password: Enter the MySQL root User password

# Enter the following BANNER
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 8 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.
# BANNER end
Mysql> create database test; create a database test for testing
Query OK, 1 row affected (0.00 sec)

Mysql> use test connector to connect to this database
Database changed

Mysql> create table test (num int, name varchar (50); Tables create a table in the database
Query OK, 0 rows affected (0.07 sec)

Mysql> insert into test values (1, 'Hello, bsd'); values insert a value to this table (here "Hello, BSD" is used as an example)
Query OK, 1 row affected (0.02 sec)

Mysql> select * from test; Contents
+ ------ + ------------- +
| Num | name |
+ ------ + ------------- +
| 1 | Hello, BSD | confirm to confirm the existence of the value just inserted into the table
+ ------ + ------------- +
1 row in set (0.01 sec)
Mysql> exit MySQL Server
Bye

1 2 3 Next Page