MySQL Hot backup method

MySQL Hot standby

Backup MySQL regularly in operational dimensions to prepare for recovered data after the crash. Generally divided into Lengbei and hot spare, cold standby is to stop the MySQL service, direct CP file, but in the production environment, very few opportunities to do so, are generally in the MySQL service when the backup, so this involves the issue of data consistency. So, under the MyISAM storage engine, our idea is: Lock table –> backup –> unlock

# Enter MySQL environment lock table
root@mysql:test> flush tables with read lock;# backup cp-r in system environment
Test/tmp/backup/test_back
# Enter MySQL environment unlock
root@mysql:test> unlock tables;

Mysqlhotcopy Hot Standby Tools

Of course, it can also be mysqlhotcopy through MySQL's own tools, in fact Mysqlhotcopy is a Perl program that provides MyISAM under the lock table backup Ching do. Because it is a Perl script, you need to have a PERL-DBD module on the machine, or it will error: can ' t locate dbi.pm in @INC, the installation process is very simple, here: PERL-DBI connection MySQL

Mysqlhotcopy is also very simple, the parameters can be –help view

# Backup Test library for new Test_tmp library, with test sibling 
mysqlhotcopy  --user=root  --password=root   test   test_tmp
 
# Backup test Library to/tmp/directory 
mysqlhotcopy  --user=root  --password=root   Test   /tmp/
 
#--checkpoint Dbinfo.checkpoint This is the database/table #--addtodest Incremental backup that specifies the record of the operation, and the new backup automatically overwrites the original
mysqlhotcopy  --user=root  - Password=root  --checkpoint=dbinfo.checkpoint  --addtodesttest  /tmp/

The Mysqlhotcopy installation method is as follows:

Yum-y Install Perl perl-dbi
wget http://down1.chinaunix.net/distfiles/DBD-mysql-3.0002.tar.gz
tar zxvf  dbd-mysql-3.0002.tar.gz
cd dbd-mysql-3.0002
perl makefile.pl  –mysql_config=/usr/local/mysql /bin/mysql_config make make
test

Make install

How to log to the database:

1, assigning a user dedicated to backup in the database segment mysql> grant Select,reload,lock tables on *.* to ' hotcopyer ' @ ' localhost ' identified by ' 123456 '; Query OK, 0 rows Affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) 2,/etc/my.cnf or login user's personal master file. MY.CNF add [mysqlhotcopy] interactive-timeout user= Hotcopyer password=123456 port=3306

Reload MySQL 3 to write records to a specific table. See Help specifically. mysql> CREATE DATABASE Hotcopy; Query OK, 1 row affected (0.03 sec) mysql> use hotcopy Database changed mysql> CREATE TABLE checkpoint (Time_stamp ti Mestamp not null,src varchar (m), dest varchar (a), msg varchar (255)); Query OK, 0 rows affected (0.01 sec) at the same time remember to give hotcopyer user rights. Mysql> Grant Insert on hotcopy.checkpoint to hotcopyer@ ' localhost '; Query OK, 0 rows Affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) mysql> quit; 4, backup data (written to the database {backup results}, incremental backup)/usr/local/mysql/bin/mysqlhotcopy--user=root--password=mysql--checkpoint= Hotcopy.checkpoint--addtodest Radius/bak

5, view mysql> show databases; +--------------------+ | Database           | +--------------------+ | Information_schema | | 1234la             | | Conntrack          | | Hotcopy            | | MySQL              | | pinphp             | | Radius             | | Test               | | VOD                | | Web                | +--------------------+ rows in Set (0.00 sec) mysql> Show tables; +-------------------+ | tables_in_hotcopy | +-------------------+ | Checkpoint        | +-------------------+ 1 row in Set (0.00 sec) mysql> select * from Checkpoint; +---------------------+--------+---------------+-----------+ | Time_stamp         | SRC    | Dest          | msg       | +---------------------+--------+---------------+-----------+ | 2012-08-25 21:50:26 | Radius | /bak/radius   | Succeeded | +---------------------+--------+---------------+-----------+ 1 row in Set (0.00 sec) mysql>

This article is from the "Baby Garden Children's Shoes" blog, please be sure to keep this source http://yangxiaofei.blog.51cto.com/2910082/973165

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/