使用mysqlhotcopy 物理增量备份

最新推荐文章于 2024-04-21 22:48:55 发布

seteor

最新推荐文章于 2024-04-21 22:48:55 发布

阅读量1.8k

点赞数

分类专栏： MySQL 文章标签： dependencies mysql perl insert 64bit database

本文链接：https://blog.csdn.net/seteor/article/details/7268232

版权

MySQL 专栏收录该内容

25 篇文章 1 订阅

订阅专栏

MySQL版本是5.1.49

# uname -a
Linux loDB192168000046 2.6.18-238.19.1.el5 #1 SMP Fri Jul 15 07:31:24 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

# cat /etc/issue
CentOS release 5.6 (Final)
Kernel \r on an \m

# mysqlhotcopy -u root -p '密码' 数据库名备份目录

Can't locate DBI.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at bin/mysqlhotcopy line 8.
BEGIN failed--compilation aborted at bin/mysqlhotcopy line 8.

上面出错是因为perl-DBD-mysql未安装:

# rpm -qa | grep perl-DBD

下载并安装perl-DBD-mysql提示缺少"libmysqlclient.so.15":
# rpm -ivh perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm
error: Failed dependencies:
   libmysqlclient.so.15()(64bit) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64
   libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64
   perl(DBI) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64

看看 libmysqlclient.so.15这个文件是否存在
# locate libmysqlclient.so.15
/usr/local/mysql/lib/mysql/libmysqlclient.so.15
/usr/local/mysql/lib/mysql/libmysqlclient.so.15.0.0
结果显示libmysqlclient.so.15文件是有的, 只是不在/usr/lib目录下而已:

我们只需要使用ldconfig 指定路径即可，下面安装MySQL-shared-compat-5.0.95-1.glibc23.x86_64.rpm包则可跳过
#ldconfig /usr/local/mysql/lib/mysql

因为我的版本是5.1.49, 其实是有libmysqlclient的，但版本为locate libmysqlclient.so.16
# locate libmysqlclient.so.16
/usr/local/mysql-5.1.49-linux-x86_64-glibc23/lib/libmysqlclient.so.16
/usr/local/mysql-5.1.49-linux-x86_64-glibc23/lib/libmysqlclient.so.16.0.0
那我们使用MySQL-shared-compat 的rpm包：

下载MySQL-shared库文件, 下载地址：http://dev.mysql.com/downloads/mysql/5.0.html
先查包里面是否有libmysqlclient.so.15这个文件
# rpm -qilp MySQL-shared-compat-5.0.95-1.glibc23.x86_64.rpm
...
/usr/lib64/libmysqlclient.so
/usr/lib64/libmysqlclient.so.12
/usr/lib64/libmysqlclient.so.12.0.0
/usr/lib64/libmysqlclient.so.14
/usr/lib64/libmysqlclient.so.14.0.0
/usr/lib64/libmysqlclient.so.15
/usr/lib64/libmysqlclient.so.15.0.0
/usr/lib64/libmysqlclient_r.so
/usr/lib64/libmysqlclient_r.so.12
/usr/lib64/libmysqlclient_r.so.12.0.0
/usr/lib64/libmysqlclient_r.so.14
/usr/lib64/libmysqlclient_r.so.14.0.0
/usr/lib64/libmysqlclient_r.so.15
/usr/lib64/libmysqlclient_r.so.15.0.0
/usr/lib64/libndbclient.so
/usr/lib64/libndbclient.so.2
/usr/lib64/libndbclient.so.2.0.0
从执行结果上看是有的，安装此rpm包可以彻底解决关于类似 libmysqlclient.so 的问题。
# rpm -ihv MySQL-shared-compat-5.0.95-1.glibc23.x86_64.rpm

更新一下文件树库, 然后此时就会看到libmysqlclient.so.15在/usr/lib64目录下也有一份:
# updatedb
# locate libmysqlclient.so.15
/usr/lib64/libmysqlclient.so.15
/usr/lib64/libmysqlclient.so.15.0.0

再次安装perl-DBD-mysql还是提示一个错误:
# rpm -ivh perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm
error: Failed dependencies:
perl(DBI) is needed by perl-DBD-MySQL-3.0007-2.el5.x86_64

这个是因为还需要安装perl-DBI
# rpm -ivh perl-DBI-1.52-2.el5.x86_64.rpm
再安装perl-DBD-mysql
#rpm -ivh perl-DBD-MySQL-3.0007-2.el5.x86_64.rpm
安装成功.mysqlhotcopy已可以正常使用。

下面我们建立用于mysqlhotcopy的专用用户:

mysql> grant select,reload,lock tables on *.* to 'u_hotcopy'@'localhost' identified by 'xxx';

建一个专门用来存放mysqlhotcopy执行日志的checkpoint表(可选):

mysql> create database db_hotcopy
mysql> use db_hotcopy;
mysql> create table log_hotcopy
   -> (
   -> time_stamp timestamp not null,
   -> src varchar(32),
   -> dest varchar(60),
   -> msg varchar(255)

-> );

为u_hotcopy用户授予insert权限:

mysql> grant insert on db_hotcopy.log_hotcopy to 'u_hotcopy'@'localhost';
mysql> flush privileges;

现在执行mysqlhotcopy就没问题了:

# mysqlhotcopy -u u_hotcopy -p 'xxx' test2 /backup --checkpoint db_hotcopy.log_hotcopy --addtodest
Locked 1 tables in 0 seconds.
Flushed tables (`test2`.`t1`) in 0 seconds.
Copying 4 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 1 tables (4 files) in 0 seconds (0 seconds overall).

--checkpoint db_hotcopy.log_hotcopy表示写备份日志到表db_hotcopy.log_hotcopy里
--addtodest表示增量备份.

现在可以查询备份日志:

mysql> select * from db_hotcopy.log_hotcopy;
+---------------------+-------+----------------------------+-----------+
| time_stamp | src | dest | msg |
+---------------------+-------+----------------------------+-----------+
| 2012-02-17 16:03:38 | test2 | /home/mysql/dbbackup/test2 | Succeeded |
+---------------------+-------+----------------------------+-----------+
1 row in set (0.00 sec)

--END--