首先是通过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数据
近期评论