mysql gno( )_mysqlhotcopy进行MySQL数据库本地备份

说明:

今天有空尝试了一下MYSQLHOTCOPY这个快速热备MYISAM引擎的工具。目前只针对单个服务器，他和MYSQLDUMP的比较：

1、前者是一个快速文件意义上的COPY，后者是一个数据库端的SQL语句集合。

2、前者只能运行在数据库目录所在的机器上，后者可以用在远程客户端。不过备份的文件还是保存在服务器上。

3、相同的地方都是在线执行LOCK TABLES 以及 UNLOCK TABLES

4、前者恢复只需要COPY备份文件到源目录覆盖即可，后者需要倒入SQL文件到原来库中。(source 或者/.或者

mysqlhotcopy是由perl语言写的备份脚本,作为命令安装在mysql的bin目录下,与mysqldump将数据库导成sql文件不同,他的机制是copy数据库文件。mysqlhotcopy进行备份里，会进行锁表Locked tables，锁表立即生效Flushed tables，解锁Unlocked tables操作。mysqlhotcopy官方说明请参考附录3.mysqlhotcopy官方说明

实现:

1.安装perl-DBD-MySQL

# yum -y install perl-DBD-MySQL

2.使用mysqlhotcopy备份数据库到本地/tmp/backup

# /usr/local/webserver/mysql/bin/mysqlhotcopy gnoxi -S /tmp/mysql.sock -u admin -p123456 /tmp/backup/

Locked 11 tables in 0 seconds.

Flushed tables (`gnoxi`.`wp_commentmeta`, `gnoxi`.`wp_comments`, `gnoxi`.`wp_links`, `gnoxi`.`wp_options`, `gnoxi`.`wp_postmeta`, `gnoxi`.`wp_posts`, `gnoxi`.`wp_term_relationships`, `gnoxi`.`wp_term_taxonomy`, `gnoxi`.`wp_terms`, `gnoxi`.`wp_usermeta`, `gnoxi`.`wp_users`) in 0 seconds.

Copying 34 files...

Copying indices for 0 files...

Unlocked tables.

mysqlhotcopy copied 11 tables (34 files) in 0 seconds (0 seconds overall).

第2步操作中，直接使用root。当然我们也可以新建个专门用于hotcopy的用户

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)

在/etc/my.cnf或者登陆用户的个人主文件.my.cnf里面添加

[mysqlhotcopy]

interactive-timeout

user=hotcopyer

password=123456

port=3306

然后就是直接备份了

# /usr/local/webserver/mysql/bin/mysqlhotcopy gnoxi -S /tmp/mysql.sock /tmp/backup/

Locked 11 tables in 0 seconds.

Flushed tables (`gnoxi`.`wp_commentmeta`, `gnoxi`.`wp_comments`, `gnoxi`.`wp_links`, `gnoxi`.`wp_options`, `gnoxi`.`wp_postmeta`, `gnoxi`.`wp_posts`, `gnoxi`.`wp_term_relationships`, `gnoxi`.`wp_term_taxonomy`, `gnoxi`.`wp_terms`, `gnoxi`.`wp_usermeta`, `gnoxi`.`wp_users`) in 0 seconds.

Copying 34 files...

Copying indices for 0 files...

Unlocked tables.

mysqlhotcopy copied 11 tables (34 files) in 0 seconds (0 seconds overall).

附录：

1：Can’t locate DBI/DBD.pm in @INC (@INC contains: /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.0 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.0/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.0 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.0/i386-linux-thread-multi /usr/lib/perl5/5.8.0 .) at Makefile.PL line 24.

直接运行yum -y install perl-DBD-MySQL 或分别安装perl-DBI,perl-DBD-MySQL

2:# /usr/local/webserver/mysql/bin/mysqlhotcopy -u admin -p 123456 /tmp/backup/

DBI connect(‘;host=localhost;mysql_read_default_group=mysqlhotcopy’,’admin’,…) failed: Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2) at /usr/local/webserver/mysql/bin/mysqlhotcopy line 177

因为mysql是编译安装的，mysql.sock我放在/tmp/mysql.sock,所以运行mysqlhotcopy时，添加-S /tmp/mysql.sock即可

/usr/local/webserver/mysql/bin/mysqlhotcopy gnoxi -S /tmp/mysql.sock -u admin -p 123456 /tmp/backup/

3.mysqlhotcopy官方说明

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables.

转载自：mysqlhotcopy进行数据库本地备份：http://coolnull.com/125.html