mysqlhotcopy备份数据库

首先是通过cpan自动安装

shell>perl -MCPAN -e shell
cpan>install DBI
cpan>install DBD::mysql ( 安装这个时test通不过)
cpan>q (退出)

cpan手动安装

unset LANG
cd ~/.cpan/build/DBI-1.607
perl Makefile.PL
make test
make install

cd ../DBD-mysql-4.007
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config
make
make test

错误

 failed: Can’t load ‘/root/.cpan/build/DBD-mysql-4.007/blib/arch/auto/DBD/mysql/mysql.so’

ln -s /opt/mysql/lib/mysql/* /usr/lib/
make test

错误

/root/.cpan/build/DBD-mysql-4.007/blib/arch/auto/DBD/mysql/mysql.so: undefined symbol:

make realclean
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm”

错误

all skipped: ERROR: Access denied for user ‘root’@’localhost’ (using password: NO). Can’t continue test

perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm” –testpassword=123456
make
make test

错误

t/00base………………..String found where operator expected at t/00base.t line 20, near “BAIL_OUT “Unable to load DBI””
        (Do you need to predeclare BAIL_OUT?)

make realclean
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lz” –cflags=-I/opt/mysql/include/mysql –testhost=localhost –testsocket=/opt/mysql/mysql.sock –testdb=bugdb –testuser=root –testpassword=123456
make
make test

错误

t/00base………………..String found where operator expected at t/00base.t line 20, near “BAIL_OUT “Unable to load DBI””
        (Do you need to predeclare BAIL_OUT?)

(在另一机器上没出这个错)-_-*!!! 直接make install

检察是否安装成功

shell>perldoc DBI
shell>perldoc DBI::mysql

从源码装
http://search.cpan.org/
http://search.cpan.org/dist/DBD-mysql/lib/DBD/mysql.pm#INSTALLATION

gzip -cd DBI-(version).tar.gz | tar xf – cd DBI-(version)
perl Makefile.PL
make
make test
make install

cd .. gzip -cd Data-ShowTable-(version).tar.gz | tar xf – cd Data-ShowTable-3.3
perl Makefile.PL
make
make install

Data-ShowTable-3.3.tar.gz
安装时有个错误 *** ERROR: Unterminated I<…> at line 724 in file ShowTable.pm 用以下方法解决
http://lists.mysql.com/perl/1015

cd .. gzip -cd DBD-mysql-(version)-tar.gz | tar xf – cd DBD-mysql-(version)
perl Makefile.PL –mysql_config=/opt/mysql/bin/mysql_config –libs=”-L/opt/mysql/lib/mysql -lmysqlclient -lcrypt -lnsl -lm -lz” –cflags=-I/opt/mysql/include/mysql –testsocket=/opt/mysql/mysql.sock –testhost=localhost –testuser=root –testpassword=******
make
make test
make install

在数据库段分配一个专门用于备份的用户

mysql> grant select,reload,lock tables on *.* to ‘hotcopyer’@’localhost’ identified by ‘123456’;
mysql> grant insert on hotcopy.checkpoint to hotcopyer@’localhost’;
mysql> flush privileges;

建表记录下历史

create database hotcopy;
create table checkpoint(time_stamp timestamp not null,src varchar(32),dest varchar(60), msg varchar(255));

cd /opt/mysql/bin/
./mysqlhotcopy test_ucenter –noindices –allowold –checkpoint hotcopy.checkpoint –addtodest /home/admin –user=hotcopyer –password=123456

你也可以将备份用的用户和密码放在~/.my.cnf下
# vi /root/.my.cnf

[client]
host=localhost
user=hotcopyer
password=123456

我在运行mysqlhotcopy时会遇到”has gone away”错误，数据库为25张表，800M大小。

DBD::mysql::db do failed: MySQL server has gone away at /opt/mysql/bin/mysqlhotcopy line 513.

看了下备份执行时间为10S左右，和我my.cnf中的wait timeout一样,调大max_allowed_packet和wait_timeout参数后没有错误。
修改下配置为
vi /opt/mysql/my.cnf

max_allowed_packet = 4M #字段最大的可能值
wait_timeout = 60

以上参数放在my.cnf的[mysqlhotcopy]和/root/.my.cnf中的[client]无效。
wait_timeout = 60可能会占用太多的connection。
我将它改为 10，然后修改mysqlhostcopy脚本来解决”has gone away”错误

#vi /opt/mysql/bin/mysqlhotcopy
复制第177行的mysql连接至513行

my $dbh = DBI->connect(“dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy”,
                        $opt{user}, $opt{password},
{
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
});

分析:
mysqlhotcopy时会先连上数据库,然后lock table再复制数据库文件，最后unlock table，
由于复制数据文件会占用一定时间，所以再次执行unlock table时可能已超过设置的wait timeout时间,
我这里将它再连接一次就不会报错啦。
如用到checkpoint，那492行也要插一次

结论：
mysqlhotcopy比较适合备份小型一点（1G以下）的数据库，还需留意下table lock wait timeout参数

定时及远程备份参考
使用crontab+ssh每天自动完全备份mysql数据