Page 1/2 of MySQL automatic backup and restoration methods after database destruction

Source: Internet
Author: User
Tags mysql automatic backup

I. Preface:

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 @ 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 (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 recovery method after the problem occurs through the actual operation process.

[1] restoration method after database Deletion

First, create a database for testing.

[Root @ CentOS ~] # Mysql-u root-p login use root to log on to the MySQL server
Enter password: Enter the MySQL root User password
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.

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, CentOS '); insert a value to this table (here "Hello, CentOS" is used as an example)
Query OK, 1 row affected (0.02 sec)

Mysql> select * from test; Contents
+ ------ + ----------------- +
| Num | name |
+ ------ + ----------------- +
| 1 | Hello, Centos | confirm that the value just inserted into the table exists.
+ ------ + ------------------ +
1 row in set (0.01 sec)

Mysql> exit MySQL Server
Bye

Then, run the database backup script you just created to back up the database you just created for testing.

[Root @ sample ~] # Cd scripts return to the root directory of the root user where the script is located
[Root @ sample ~] #./Mysql-backup.sh slave run script for Database Backup

Next, we log on to the MySQL server again and delete the database test we just created to test whether the data recovery is successful.

[Root @ Centos ~] # Mysql-u root-p login use root to log on to the MySQL server
Enter password: Enter the MySQL root User password
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 13 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> use test connector to connect to the test database for testing
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> drop table test; Tables Delete tables from data
Query OK, 0 rows affected (0.04 sec)

Mysql> drop database test; Revoke delete test
Query OK, 0 rows affected (0.01 sec)

Mysql> show databases;
+ --------------- +
| Database |
+ --------------- +
| Mysql | confirm that the test database used for testing does not exist or has been deleted.
+ --------------- +
1 row in set (0.01 sec)

Mysql> exit MySQL Server
Bye

Above, we simulate the database destruction process. Next, we will use the backup method to restore the database after it is damaged.

[Root @ Centos ~] #/Bin/cp-Rf/backup/mysql/test // var/lib/mysql/snapshot copy the backup database test to the corresponding directory
[Root @ Centos ~] # Chown-R mysql: mysql/var/lib/mysql/test/secrets change the database test to mysql
[Root @ Centos ~] # Chmod 700/var/lib/mysql/test/CATALOG change the database Directory attribute to 700
[Root @ Centos ~] # Chmod 660/var/lib/mysql/test/* modify the attribute of data in the database to 660

Then, log on to the MySQL server again to check whether the database has been successfully restored.

[Root @ CentOS ~] # Mysql-u root-p login use root to log on to the MySQL server
Enter password: Enter the MySQL root User password
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 14 to server version: 4.1.20

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> show databases; databases
+ ------------- +
| Database |
+ ------------- +
| Mysql |
| Test | confirm that the database test that has just been deleted has been successfully recovered!
+ ------------ +
2 rows in set (0.00 sec)

Mysql> use test connector to connect to the test Database
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with-

Database changed
Mysql> show tables; tables: View tables in the test Database
+ ------------------- +
| Tables_in_test |
+ ------------------- +
| Test |
+ ------------------- +
1 row in set (0.00 sec)

Mysql> select * from test; Contents
+ ------ + --------------------- +
| Num | name |
+ ------ + --------------------- +
| 1 | Hello, CentOS | confirm that the data table content is the same as the "Hello, CentOS" definition before deletion!
+ ------ + --------------------- +
1 row in set (0.01 sec)

Mysql> exit MySQL Server
Bye

The preceding result indicates that after the database is deleted, the data is successfully restored to the status before the database is deleted.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.