Example of mysqlhotcopy hot backup in MySQL

Backup features:

A quick copy of the file, which can only run on the machine where the database directory is located, perform lock tables and UNLOCK tables online, and restore with only copy backup files to source directory coverage.

Insufficient: The backup can not lock the table, data can not be updated or inserted, backup is limited to the local machine.

Pre-use machine needs Perl environment and install PERL-DBD package

Mysqlhotcopy usage:

1), mysqlhotcopy original database name, new database name

2), mysqlhotcopy the original database name, backup directory

3), you can also use the following script

#!/bin/sh

# Name:mysqlbackup.sh

# define the directory where the script is located

Scriptsdir= ' pwd '

# Data Directory for database

Datadir=/var/lib/mysql

# Data Backup Directory

Tmpbackupdir=/tmp/mysqlblackup

Backupdir=/backup/mysql

# User name and password used to back up the database

Mysqluser=root

mysqlpwd= ' You password '

# If the temporary backup directory exists, empty it, and create it if it does not exist

if [[E-$tmpBackupDir]]; Then

RM-RF $tmpBackupDir/*

Else

mkdir $tmpBackupDir

Fi

# Create a backup directory if it does not exist

if [[!-e $backupDir]];then

mkdir $backupDir

Fi

# Get a list of database backups, where you can filter the databases you don't want to back up

For databases in ' find $dataDir-type D |

Sed-e "s//var/lib/mysql///" |

Sed-e "s/test//"; Todo

if [[$databases = = ' "]]; Then

Continue

Else

# BACKUP Database

/usr/bin/mysqlhotcopy--user= $mysqlUser--password= $mysqlPWD-Q "$databases" $tmpBackupDir

Datetime= ' Date ' +%y.%m.%d%h:%m:%s '

echo "$dateTime Database: $databases backup success!" >>mysqlbackup.log

Fi

Done

# Compress backup Files

Date= ' Date-i '

CD $tmpBackupDir

Tar czf $backupDir/mysql-$date. tar.gz./

#End完成

Add to Crontab set to run 5 2:19 a week

2 * * 5/backup/blackup.sh

Note: Restore the database to the state of the backup Mysqlhotcopy backup is the entire database directory, use can be directly copied to the mysqld specified DataDir (here is/var/lib/mysql/) directory, but also pay attention to the issue of permissions, the following example:

Shell> CP-RF db_name/var/lib/mysql/

Shell> chown-r mysql:mysql/var/lib/mysql/(convert db_name directory owner to Mysqld run user)

This set of backup policies can only restore the state of the database to the last backup, so that the data lost in the crash should be backed up as infrequently as possible, and the master-slave replication mechanism (replication) is used to restore the data to the state of the crash.

Small tips:

Do not want to write the password in the shell, you can create a. my.cnf file in the home directory of root to allow mysqlhotcopy to read the username/password from it.

[Mysqlhotcopy]

User=root

Password=yourpassword

Then be safe, chmod.

chmod ~/.my.cnf

Mysqlhotcopy reads the [client] and [mysqlhotcopy] option groups from the options file. To perform mysqlhotcopy, you must have access to the Backed-up table files, with SELECT permissions and reload permissions for those tables (so that you can perform flush tables).

MySQL automatic backup and recovery (mysqlhotcopy)

Conditions required to establish a database backup

[1] Establish an automatic backup script

Here, in order for database backup and recovery to meet our actual requirements, a consistent shell script is used to automate the entire backup process.

[Root@sample ~]# VI mysql-backup.sh← establishes the 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=********← replaces the asterisk 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
Todo
Mysqlhotcopy $dbname-u root-p $ROOTPASS $BACKDIR | Logger-t mysqlhotcopy
Done
My test system is Redhat Linux as4, MySQL version: 4.1.19, using the above script has a hint that the DBD package is not installed.

Install according to the prompts in the readme, and if you install Dbd::mysql on the MYSQL system of the TAR package, use the above command when setting up the environment:

Perl makefile.pl\
--libs= "-l/usr/local/mysql-4.0.9/lib/mysql-lmysqlclient-lz" \
--cflags=-i/usr/local/mysql-4.0.9/include/mysql\
--testhost=127.0.0.1
The following commands do not need to be executed, and there are possible errors.
Make
Make test # Some minor error messages can is ignored here
Make install
If not, please follow:

Copy/usr/local/mysql/include/mysql/* to/usr/include/, copy/usr/local/mysql/lib/mysql/* to/usr/lib/, and then use Perl makefile.pl set up the environment, and then follow the normal installation, you can correctly connect to the MySQL database.
[2] Run the database automatic backup script

[Root@sample ~]# chmod mysql-backup.sh← Changes script properties so that it can only be executed by the root user

[Root@sample ~]#./mysql-backup.sh← Run Script

[Root@sample ~]# ls-l/backup/mysql/← Confirm if Backup is successful

Total 8
Drwxr-x---2 mysql mysql 4096 Sep 1 16:54 mysql← has been successfully backed up to the/backup/mysql directory

[3] Make database backup scripts run automatically every day

[Root@sample ~]# crontab-e← edit Autorun rule (then edit window appears, operation with VI)

* * * * */root/mysql-backup.sh← Add this line to the file and make the database backup 3 o'clock in the morning daily

Test the normal operation of automatic backups (method of backup recovery)

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

[1] Recovery methods when the database is deleted

First, establish a test database.

[Root@sample ~]# mysql-u root-p← login to MySQL server with root
Enter password:← the root user password for MySQL

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 test database
Query OK, 1 row Affected (0.00 sec)
Mysql> use test← connect to this database
Database changed

Mysql> CREATE TABLE Test (num int, name varchar (50)); ← Create a table in the database
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO test values (1, ' Hello,bsd '); ← Insert a value into this table (here take "Hello,bsd" for example)
Query OK, 1 row affected (0.02 sec)
Mysql> select * from test; ← View the contents of the database
+------+-------------+
| num | name |
+------+-------------+
|1 |　 Hello,bsd | ← Confirm the existence of the value just inserted into the table
+------+-------------+
1 row in Set (0.01 sec)
mysql> exit← exit MySQL server
Bye

Then, run the database backup script that you just created, and back up the test database you just created.

[Root@sample ~]# cd← back to the root directory of the root user where the script resides

[Root@sample ~]#./mysql-backup.sh← Run scripts for database backups

Next, we log on to the MySQL server again and delete the database test that was just created to test the success of the data recovery.

[Root@sample ~]# mysql-u root-p← login to MySQL server with root
Enter password:← the root user password for MySQL

Welcome to the MySQL Monitor. Commands End With; Or\g.
Your MySQL Connection ID is to server version:4.1.20
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql> use test← Connect to the test database for testing
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a
Database changed
mysql> drop table test; ← Delete the table in the data
Query OK, 0 rows affected (0.04 sec)
mysql> drop database test; ← Delete test Data database test
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+-------------+
| Database |
+-------------+
|　 MySQL | ← Confirm test Database no longer exists and has been deleted
+-------------+
1 row in Set (0.01 sec)
mysql> exit← exit MySQL server
Bye

Above, we are tantamount to simulating the destruction of the database process. Next, the database is "corrupted", the method of restoring with backup.

[Root@sample ~]#/bin/cp-rf/backup/mysql/test//var/lib/mysql/← Replication Backup Database test to the appropriate directory

[Root@sample ~]# chown-r mysql:mysql/var/lib/mysql/test/← Changes the ownership of database test to MySQL

[Root@sample ~]# chmod 700/var/lib/mysql/test/← Change Database directory property to 700

[Root@sample ~]# chmod 660/var/lib/mysql/test/*← Change the properties of the data in the database to 660

Then, log on to the MySQL server again to see if the database has been successfully restored.

[Root@sample ~]# mysql-u root-p← login to MySQL server with root
Enter password:← the root user password for MySQL

Welcome to the MySQL Monitor. Commands End With; Or\g.
Your MySQL Connection ID is to server version:4.1.20
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.
mysql> show databases; ← View the currently existing database
+-------------+
| Database |
+-------------+
| MySQL |
|　 Test | ← Confirm that the test database that has just been deleted has been successfully restored!
+------------+
2 rows in Set (0.00 sec)
Mysql> use test← Connect to the test database
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a
Database changed
Mysql> Show tables; ← View the tables that exist in the test database
+-------------------+
| Tables_in_test |
+-------------------+
| Test |
+-------------------+
1 row in Set (0.00 sec)
Mysql> select * from test; ← View the contents of the database
+------+--------------+
| num | name |
+------+--------------+
| 1 |　 Hello,bsd | ← Confirm that the content in the datasheet is the same as the "HELLO,BSD" defined before the deletion!
+------+--------------+
1 row in Set (0.01 sec)
mysql> exit← exit MySQL server
Bye

The above results indicate that after the database was deleted, the database was successfully restored to the state before the deletion with the backed up data.

[2] The recovery method when the database is modified

The database has been modified, there may be many reasons, the intrusion, and the corresponding program there are bugs, and so on, here is not described in detail. This will only describe how to revert to a modified state before the database is modified.

This is similar to the "recovery method after database deletion" described above. Here, the test database then uses test that you just used earlier. In order to make the friends who just contacted the database not understand the confusion, we log on to the MySQL server again to confirm the information about the test database tests that we just created.

[Root@sample ~]# mysql-u root-p← login to MySQL server with root
Enter password:← the root user password for MySQL

Welcome to the MySQL Monitor. Commands End With; Or\g.
Your MySQL Connection ID is to server version:4.1.20
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.
mysql> show databases; ← View the currently existing database
+-------------+
| Database |
+-------------+
| MySQL |
| Test |
+------------+
2 rows in Set (0.00 sec)
Mysql> use test← Connect to the test database
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a
Database changed
Mysql> Show tables; ← View the tables that exist in the test database
+-------------------+
| Tables_in_test |
+-------------------+
| Test |
+-------------------+
1 row in Set (0.00 sec)
Mysql> select * from test; ← View the contents of the database
+------+--------------+
| num | name |
+------+--------------+
| 1 | Hello,bsd |
+------+--------------+
1 row in Set (0.01 sec)
mysql> exit← exit MySQL server
Bye

Then we run the database backup script again and back up the current state of the database again.

[Root@sample ~]# cd← back to the root directory of the root user where the script resides

[Root@sample ~]#./mysql-backup.sh← Run scripts for database backups

Next, we log on to the MySQL server again and make some changes to the test's database test to be able to test the success of the data recovery.

[Root@sample ~]# mysql-u root-p← login to MySQL server with root
Enter password:← the root user password for MySQL

Welcome to the MySQL Monitor. Commands End With; Or\g.
Your MySQL Connection ID is to server version:4.1.20
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql> use test← Connect to the test database
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a
Database changed
mysql> Update test set name= ' Shit,windows '; ← Then redefine the value of the table in test to "Shit,windows" (originally "Hello,bsd")
Query OK, 1 row affected (0.07 sec)
Rows matched:1 changed:1 warnings:0
Mysql> select * from test; ← Confirm that the table in test has a defined value
+------+--------------------+
| num | name |
+------+-------------------+
| 1 |　 Shit,windows | ← Confirm that the values in the original test database table have been modified to the new value "Shit,windows"
+------+-------------------+
1 row in Set (0.00 sec)
mysql> exit← exit MySQL server
Bye

Above, we are tantamount to simulating the process of database tampering. Next, the database is "tampered" with the method of restoring with backup.

[Root@sample ~]#/bin/cp-rf/backup/mysql/test//var/lib/mysql/← Replication Backup Database test to the appropriate directory

Then, log on to the MySQL server again to see if the database was restored to the state before it was tampered with.

[Root@sample ~]# mysql-u root-p← login to MySQL server with root
Enter password:← the root user password for MySQL

Welcome to the MySQL Monitor. Commands End With; Or\g.
Your MySQL Connection ID is to server version:4.1.20
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql> use test← Connect to the test database
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a
Database changed
Mysql> select * from test; ← View the contents of the database
+------+----------------+
| num | name |
+------+----------------+
| 1 |　Hello,bsd | ← Confirm that the content in the datasheet is the same as the "HELLO,BSD" defined before it is modified!
+------+----------------+
1 row in Set (0.01 sec)
mysql> exit← exit MySQL server
Bye

The above results indicate that the database has been modified to successfully restore the data to the state before it was "tampered" with the backed-up database.

After the test ...

After the test is complete, delete the legacy information that was used for the test.

[Root@sample ~]# mysql-u root-p← login to MySQL server with root
Enter password:← the root user password for MySQL

Welcome to the MySQL Monitor. Commands End With; Or\g.
Your MySQL Connection ID is to server version:4.1.20
Type ' help, ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql> use test← Connect to the test database
Reading table information for completion of table and column names
You can turn off the feature to get a quicker startup with-a
Database changed
mysql> drop table test; ← Delete the table in the test database
Query OK, 0 rows affected (0.01 sec)
mysql> drop database test; ← Delete test Data database test
Query OK, 0 rows Affected (0.00 sec)
mysql> show databases; ← View the currently existing database
+-------------+
| Database |
+-------------+
|　 MySQL | ← Confirm that test data database test does not exist and has been deleted
+-------------+
1 row in Set (0.00 sec)
mysql> exit← exit MySQL server
Bye

--------
The above describes a way to back up the database with a mysqlhotcopy of our own set of shell scripts.