首先是通过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 <a href="mailto:'root'@'localhost'">'root'@'localhost'</a> (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=<span style="color: #ff00ff; font-family: Courier New;">hotcopyer</span> --password=<span style="color: #ff00ff; font-family: Courier New;">123456
- </span>
你也可以将备份用的用户和密码放在~/.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数据
0 Responses
Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.