Chinaunix首页 | 论坛 | 博客
  • 博客访问: 109320
  • 博文数量: 53
  • 博客积分: 2062
  • 博客等级: 大尉
  • 技术积分: 550
  • 用 户 组: 普通用户
  • 注册时间: 2009-05-31 10:14
文章分类

全部博文(53)

文章存档

2011年(1)

2010年(3)

2009年(49)

我的朋友

分类: Mysql/postgreSQL

2009-05-31 10:42:37

作为网络管理员,经常需要维护一些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技术征文大赛第三名
作者:刘世伟

阅读(939) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~