首先是通过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数据