作为网络管理员，经常需要维护一些mysql 服务器， 比如网站服务器，客户的应用服务器，公司内部的业务系统，内部管理系统等等。
如何方便安全快速的对多个mysql服务器进行数据备份，就成一个基本的需求。
本人在实际工作中，使用龙芯mini服务器，同时对几个远程的数据库服务器进行数据备份，效果很好，下面简单介绍一下这套备份系统。
基本的思路是,用mysql带的mysqld_multi程序，在同一个龙芯mini-pc上，实现几个独立的mysql服务器，让它们分别对几个不同地点的mysql服务器进行远程复制。
如果对方的mysql服务器在内部子网，则需要用openvpn来实现通信，同时openvpn还可以对通讯流量进行加密，防止数据被窃听。
每天定时用mysqldump做数据库的导出备份，然后配合binlog(复制日志),可以实现对任意时间点的恢复。
有一次，外网的论坛被黑客注入，把某一个表给清空了，通过这套备份系统，在不停机的情况下，对数据库进行了修复。我們首先用每天的日备份，把数据库恢复到昨日的状态，然后把binlog导出成sql语句文本，用文本编辑器在文本中寻找破坏数据的sql语句，删除这条语句，然后把这个修改过的sql文本，灌入到恢复的数据库中，我們就得到了正确的数据库，导出损坏的表，并灌入到生产数据库中，就修复了。
打开mysql的binlog 功能后， mysql服务器会把修改日志保存下来，远程的客户端帐号如果有REPLICATION SLAVE, REPLICATION CLIENT权限，就可以连接服务器,抓取这些binlog,在本地重现修改过程，实现2边数据库的同步，这些binlog文件，还可以用 mysqlbinlog程序，导出成sql语句进行查看修改。
binlog文件名的后缀一般是一个6位数字，客户端会依次抓取这些文件，同步抓取的时间间隔可长可短，几秒或者几天都行，只要时间不超过服务器的binlog文件的保留时间就可以。
每天在备份服务器用mysqldump导出日备份，然后通过bzip2压缩，再用gpg程序进行加密，加密后的数据，就可以随便通过ftp或者 rsync传输到远程的服务器，或者专人进行备份,gpg的加密强度是足够强的，非对称的加密算法，用公钥加密后的文件，只有拥有私有密钥的人，才可以解密，如果要简单一点，也可以用rar进行密码加密，只要密码足够长，加密强度也够用。
选择龙芯mini服务器，是因为龙芯的功耗比较低，而性能足够，体积小，重量轻，整机体积只有光驱大小，功耗10W，便于部署。当然缺点就是容易丢失，最好能配合aes对存储块设备进行加密，这样即使丢失也不会被导出数据。
具体的实施步骤，以debian5.0的系统为例子：
1.远程mysql服务器的设置
1.1 开复制备份的远程mysql帐号
可以使用phpadmin建立备份帐号，注意要指定帐号的ip地址，
复制还需要用到 REPLICATION SLAVE, REPLICATION CLIENT权限，要加上
1.2 设置openvpn加密通道
可以参照一般的openvpn的设置方法，主从服务器式，或者点对点的方式都可以，
这里不做详细介绍，可以参阅网上文档。
1.3 调整mysql服务器设置，生成binlog
在my.cnf中添加如下设置:
[mysqld]
slave-skip-errors = all
log-slave-updates #在本服务器的binlog中也存放其他的服务器的修改日志，否则，之存放本服务器的修改日志，
server-id = 1 #根据需要修改，不能跟参与复制的其他的mysql服务器相同
log_bin = /home/mysql/mysql-bin.log #binlog文件名，实际是mysql-bin.000xxx
#这2个设置是为了防止2台互相复制的服务器产生关键字段的冲突用的，
#如果他们用不同的偏移， 就可以实现一台服务器按照1,3,5,7..增加，
#另一台服务器按照个2,4,6,8..增加
#如果服务器的复制是单向的，就不需要这个2个设置项
auto_increment_increment = 2 #自动增加的字段每次步进是2
auto_increment_offset = 1 #自动增加的字段的偏移是1 ，
#保留100天的binlog ,
#如果binlog的数据量大, 并且不用保留这么多天,那么可以缩短
expire_logs_days = 100
max_binlog_size = 100M #单个binlog的大小
binlog_do_db = net #需要记录binlog的库明
binlog_do_db = news #需要记录的库名都列在这里.
binlog_do_db = wikidb
执行重启命令
/etc/init.d/mysql restart
1.4 关闭应用程序到mysql服务器的连接，并使用mysqldump导出每个库的完整的数据，并记录当前binlog的文件名和当前的文件偏移位置。
/etc/init.d/apache2 stop #关闭应用程序
mysqldump --host=127.0.0.1 -P3306 --add-drop-table -c \
--default-character-set=utf8 --allow-keywords --force \
--password=*** --user=root vpopmail |gzip>/tmp/vpopmail.sql.gz
显示binlog的文件名和偏移: ls /home/mysql/mysql-binlog* -l
-rw-rw---- 1 mysql mysql 12301207 2009-03-04 18:18 /home/mysql/mysql-bin.001943
binlog当前日志文件名为mysql-bin.001943 偏移为12301207
2.备份服务器的设置
2.1建立多个数据库的database目录
假设要做3个备份服务器,目录分别放在 /home/mysql1,/home/mysql2,/home/mysql3
mkdir /home/mysql1 /home/mysql2 /home/mysql3
复制将原初始的mysql数据复制到4个目录中
cp -a /var/www/mysql /home/mysql1
cp -a /var/www/mysql /home/mysql2
cp -a /var/www/mysql /home/mysql3
修改文件的用户权限
chown mysql:mysql -R /home/mysq1 /home/mysql2 /home/mysql3
2.2修改my.cnf
mysql服务器初始安装的3306端口,不要占用,3个mysql服务器的端口分别分配为
3307,3308,3309
/etc/mysql/my.cnf
[mysqld1]
slave-skip-errors=all
#日志里面要带记录主服务器的修改日志,以便可以恢复数据库状态到任意时刻
log-slave-updates
user = mysql
pid-file = /var/run/mysqld/mysqld1.pid
socket = /var/run/mysqld/mysqld1.sock
port = 3307
datadir = /home/mysql1 #数据目录
#服务器的id, 跟对方的主服务器要有区别,在保存修改到binlog时,会记录server-id,
#在导入binlog时,会忽略相同id的修改记录
server-id = 4
log_bin = /mysql/mysql2/mysql-bin.log #binlog 的保存文件名
auto_increment_increment = 3 #跟主服务器一致,
auto_increment_offset = 2 #要跟主服务器的偏移不同,但不能大于上一个设置的值
expire_logs_days = 100 #保留100天修改日志
max_binlog_size = 100M #每个日志文件100M
master-host =192.168.90.1 #主服务器的ip
master-user =bak #主服务器的帐号
master-password =******** #主服务器的密码
master-port =3306
master-connect-retry =300 #连接失败重试间隔秒
replicate-do-db =vpopmail #从主服务器同步过来的库名
replicate-do-db =loongson #从主服务器同步的另一个库
.... #其他的设置,比如内存,优化等等
[mysqld2]
slave-skip-errors=all
log-slave-updates
user = mysql
pid-file = /var/run/mysqld/mysqld2.pid
socket = /var/run/mysqld/mysqld2.sock
port = 3308
datadir = /home/mysql2
server-id = 4
log_bin = /mysql/mysql2/mysql-bin.log
auto_increment_increment = 4
auto_increment_offset = 2
expire_logs_days = 100
max_binlog_size = 100M
master-host =192.168.110.1
master-user =bak
master-password =********
master-port =3306
master-connect-retry =300
replicate-do-db =water
.... //其他的设置
[mysqld3]
slave-skip-errors=all
log-slave-updates
user = mysql
pid-file = /var/run/mysqld/mysqld3.pid
socket = /var/run/mysqld/mysqld3.sock
port = 3309
datadir = /home/mysql2
server-id = 4
log_bin = /mysql/mysql2/mysql-bin.log
auto_increment_increment = 3
auto_increment_offset = 2
expire_logs_days = 100
max_binlog_size = 100M
master-host =10.0.10.1
master-user =bak
master-password =********
master-port =3306
master-connect-retry =300
replicate-do-db =vpopmail
replicate-do-db =loongson
.... //其他的设置
2.3启动数据库，并灌入数据，设置要抓取的服务器的binlog的文件名和位置，并启动复制抓取进程
用phpadmin在各个备份服务器分别建立库名,跟相应的服务器对应
把从服务器备份出来的sql文件灌入
gunzip vpopmail.sql.gz -c |mysql vpopmail
#命令行连接进服务器
mysql -u root -p***** -h 127.0.0.1 -P3307
#设置服务器的binlog当前抓取位置
mysql>change master to master_log_file='mysql-bin.001943';
mysql>change master to master_log_pos=12301207;
mysql>start slave;
mysql>show slave status;
用show slave status命令应该可以看到2个进程都是yes, Slave_IO_Running =yes
Slave_SQL_Running =yes,这样就正常了.
并且master_log_pos在不断的增加.
2.4完成多个服务器的复制设置
参照2.3节完成其他备份服务器的设置
3.mysqldump和加密压缩，按年月自动开目录
3.1 下载公钥,生成本地私钥,用本地私钥对公钥进行签署,不签署是不能通过脚本自动完成加密的.具体的gpg的操作这里从略.
3.2 备份脚本,以mysql1服务器的vpopmail库为例,多个库要分别导出多次,
path=/home/ftp/sqlbak/`date +%Y%m%d` #20090305 年月日
time=`date +%H%M%S` #104159 时分秒
[ -d ${path} ]||mkdir -p ${path}
mysqldump --host=127.0.0.1 -P3307 --add-drop-table -c --default-characterset=
utf8 --allow-keywords --force --password=**** --user=root vpopmail \
|bzip2 -9 \
|gpg -e -r "c8822846" --yes --batch -o ${path}/127.0.0.1_3307_vpopmail_$
{time}.sql.bz2.gpg
3.3 远程备份
通过ftp或者rsync把/home/ftp/sqlbak发布出来.
然后在远程用rsync或者wget来定时抓取
wget -m -c ftp://xxx.xxx.xxx.xxx/sqlbak
要远程的数据一定要注意加密.
4.php监视页面和cron定时监控
4.1 php的web监视页面
用php做监控很简单,只要注意每个mysql备份服务器的show slave status命令的结
果,Slave_IO_Running,Slave_SQL_Running就行了
mysql_connect("127.0.0.1:3307","root","******"); //连接到mysqld1
$err=monitor(); //检查同步状态
mysql_connect("127.0.0.1:3308","root","******"); //连接到mysql2
$err.=monitor(); //检查同步状态
mysql_connect("127.0.0.1:3309","root","******");
$err.=monitor();
if($err!='')
echo $err;
exit();
function monitor()
{
$a=mysql_fetch_array(mysql_query("show slave status"));
if($a['Slave_IO_Running']!='Yes')
$msg="主服务器:$a[Master_Host]:$a[Master_Port] 不能通讯,日志抓取点:
$a[Master_Log_File]:$a[Read_Master_Log_Pos]字节处
";
if($a['Slave_SQL_Running']!='Yes')
$msg.="主服务器:$a[Master_Host]:$a[Master_Port] 日志SQL语句不能被执行,错误信息:
$a[Last_Error]
";
return $msg;
}
?>
4.2 定时邮件报警
做cron定时邮件监控很简单,只要把上面的监视页面的php的echo $err 改成
sendmail("liushiwei@gmail.com","数据库备份错误",$err);
就行了,然后在系统的cron设立一个每天早上7点16分执行一次的wget命令.如果出现同步问题,就会发送一封邮件
/etc/crontab
16 7 * * * root wget wget -O - -q
5.所有的文件都放在 ftp:// ,此ftp不支持ie和迅雷,请使用其他浏览器下载:
mysql_backup_loongson.tar.bz2 适用于龙芯mini服务器的系统映像，只需要修改一下
/etc/mysql/my.cnf,然后配好网络就可以工作
monitor.php 监视和cron的脚本
sqlbak.sh 压缩加密的脚本
ChinaUnix-ITpub网站MySQL技术征文大赛第三名
作者：刘世伟
阅读(941) | 评论(0) | 转发(0) |
