安装MySQL数据热备份支持模块：Perl-BDI＋Perl-DBD-MySQL

安装MySQL数据热备份支持模块：Perl-BDI＋Perl-DBD-MySQL

MySQL的mysqlhotcopy热备份工具需要Perl-DBI和Perl-DBD-MySQL模块支持，Linux
上默认安装的Perl是没有该模块的。可以从CPAN下载最新的DBI 和DBD模块进行安装。

[root@office ~]# cd /opt/src
[root@office src]# wget http://www.cpan.org/modules/by-module/DBD/DBI-1.608.tar.gz
[root@office src]# wget http://www.cpan.org/modules/by-module/DBD/DBD-mysql-4.011.tar.gz

[1] 先编译安装DBI模块：

[root@office src]# tar xvfz DBI*
[root@office src]# cd DBI*
[root@office DBI-1.609]# PATH=$PATH:/opt/mysql/bin
[root@office DBI-1.609]# export PATH
[root@office DBI-1.609]# unset LANG
[root@office DBI-1.609]# perl Makefile.PL
[root@office DBI-1.609]# make
[root@office DBI-1.609]# make test
[root@office DBI-1.609]# make install

[2] 再编译安装DBD模块：

[root@office DBI-1.609]# cd ..
[root@office src]# tar xvfz DBD*
[root@office src]# cd DBD*
[root@office DBD-mysql-4.012]# perl Makefile.PL \
--libs="-L/opt/mysql/lib/mysql -lmysqlclient -lz" \
--cflags=-I/opt/mysql/include/mysql \
--testhost=localhost \
--testuser=root \          // 配置测试用户选项
--testpassword= '******'    // 用户密码为空时不需要此选项
[root@office DBD-mysql-4.012]# make
[root@office DBD-mysql-4.012]# make test
[root@office DBD-mysql-4.012]# make install

[3] 建立自动备份脚本

在这里，为了使数据库备份和恢复的符合我们的实际要求，用一段符合要求的Shell脚本来实现整个备份过程的自动化。

[root@office ~]# vi mysql-backup.sh　 ← 建立数据库自动备份脚本，如下：

#!/bin/bash

PATH=/usr/local/sbin:/usr/bin:/bin:/opt/mysql/bin
# The Directory of Backup
BACKDIR=/tmp/backup
RMT_HOST='192.168.11.8'
ftpuser='hegz'
ftppass='*******'
# The Password of MySQL
ROOTPASS=''
# Remake the Directory of Backup
rm -rf $BACKDIR
mkdir -p $BACKDIR
# Get the Name of Database
DBLIST=`ls -p /data | grep / | tr -d /`
# Backup with Database
for dbname in $DBLIST
do
#mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy
mysqlhotcopy $dbname -u root $BACKDIR | logger -t mysqlhotcopy  ← root密码为空时启用
done
echo "MySQL Databases backup success!\n"
echo "Ftp MySQL DataBases file ......\n"
dateTime=`date +"%Y-%m-%d %H:%M:%S"`
date=`date +%Y%m%d`
cd /tmp
tar czf mysql-$date.tar.gz ./backup/*
#ls -l /tmp/mysql-$date.tar.gz

#============ 自动传输文件到远程主机[注意：自动上传脚本命令前面不能有空格] ============
ftp -in $RMT_HOST <<END
user $ftpuser $ftppass
bina
put mysql-$date.tar.gz
#dir mysql-$date.tar.gz
bye
END
echo "Ftp Databases File success!\n"
rm -f /tmp/mysql-$date.tar.gz

注意：备份目录参数 BACKDIR=/tmp/backup 为一测试目录，为安全起见，可修改为某一登录账户的宿主目录下，如：

BACKDIR=/home/hegz/backup

上传到远程主机后，如果将备份文件删除掉，就更加安全了。

[4] 运行数据库自动备份脚本

[root@office ~]# chmod 700 mysql-backup.sh　 ← 改变脚本属性，让其只能让root用户执行
[root@office ~]# ./mysql-backup.sh 　 ← 运行脚本
[root@office ~]# ls -l /tmp/backup/ 　 ← 确认一下是否备份成功

total 8
drwxr-x--- 2 mysql mysql 4096 Sep 1 16:54 mysql 　 ← 已成功备份到/tmp/backup/目录中

如果运行备份脚本时屏幕出现下面的提示：

install_driver(mysql) failed: Can't load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so: cannot restore segment prot after reloc: Permission denied at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230.
 at (eval 7) line 3
Compilation failed in require at (eval 7) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at /opt/mysql/bin/mysqlhotcopy line 177
install_driver(mysql) failed: Can't load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so: cannot restore segment prot after reloc: Permission denied at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230.
 at (eval 7) line 3
Compilation failed in require at (eval 7) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at /opt/mysql/bin/mysqlhotcopy line 177
install_driver(mysql) failed: Can't load '/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so: cannot restore segment prot after reloc: Permission denied at /usr/lib/perl5/5.8.8/i386-linux-thread-multi/DynaLoader.pm line 230.
 at (eval 7) line 3
Compilation failed in require at (eval 7) line 3.
Perhaps a required shared library or dll isn't installed where expected
 at /opt/mysql/bin/mysqlhotcopy line 177

则说明该脚本的运行需要进行SELinux安全授权。

执行如下命令进行处理：
   
[root@office ~]# cd /etc/selinux/targeted/modules/
[root@office modules]# audit2allow -M local -i /var/log/audit/audit.log
[root@office modules]# semodule -i local.pp

文章参考：http://www.cnprint.org/bbs/blogs/1/blog48.html

[5] 让数据库备份脚本每天自动运行

[root@office ~]# crontab -e　 ← 编辑自动运行规则（然后会出现编辑窗口，操作同vi）

00 03 * * * /root/mysql-backup.sh 　 ← 添加这一行到文件中，让数据库备份每天凌晨3点进行

[6] 当数据库被删除后的恢复方法

[root@office data]#  cp -Rf /tmp/backup/mysql/ /data
[root@office data]#  chown -R mysql:mysql /data
[root@office data]#  chmod -R 700 /data