mysql 动态迁移架构（转）

mysql 的架构已经讨论很多了，这里最为经典的要算 （ 主  -> 从 ） 结构了。（ 下面用 M 表示Master  S 表示Slave  S1 S2 分别表示一级Slave  二级Slave ）

这个架构的优点是 S 不唯一 分担了查询的压力 ， 即使 1两个 S 损坏也不会影响正常的使用 ，缺点是 M 是唯一的，一旦顺坏，将影响所有写入的请求。

对于这个缺点又有很多不同的解决方案。

方案1：
这个结构，当 S 损坏不会影响任何业务，只需要重装即可，当 M 损坏，其中的一台 S 升级成为 M ，问题是所有的 S 的同步这时候会有问题。

方案2：
这个结构使用双 M 解决 M 损坏的问题。 一般有 DRBD 或 MMM 这两种。
在 MMM 这个方案中，如果其中一个 M 在切换之前重启过几次，那么就会出现其中含有浮动 ip 的M在损坏以后虽然另一个切换接管了，但在没有人工干预的情况下 S 的同步会断掉。
在 DRBD 的这个解决方案中，更适用 Innodb ，在使用 Myisam 表的情况下会出现在切换之后表损坏，也在某些情况下需要人工干预。

方案3：
除了上面的两个方案，还有一个方案3 做的是 M -> S1 -> S2 这样的结构。
这个方案解决了在 M 死亡以后 S2 的同步情况的问题。但在 S1 死亡以后仍需要人工干预，但在不干预的情况下既不会影响插入，也不会影响查询，只不过数据不同步了。
情况 1 Master 宕机 ： S1 接管 M 的浮动 IP ，这时候任何都不需要改动，数据库结构完好
情况 2 Slave1 宕机 ：  既不会影响插入，也不会影响查询，只不过数据不同步了。需要人工干预才能恢复。
情况 3 Slave2 宕机 :  只要不是全宕了就没事。

方案4 :
NDB cluster 集群，和这次要讨论的没啥关系。
优点： 多点写入，完全代替主从结构。
缺点： 增加节点的时候要集体拆迁。这对于数据增长快的网站基本就是难以接受。
图： 略 ，这里不详细讨论这个东西。

下面简单介绍一下我的情况。
我们是一个做求职招聘的公司，主要业务就是 企业搜索个人简历，个人搜索企业职位，所以数据库就是我们的核心业务。我们所采用用的全部都是 mysql 。
我们的特点是查询量大，写入量小，我们采用了 标准的 M -> S 结构，并且全部引擎采用了 MYISAM 来满足我们的查询 使用这个结构的同时也带来了上面提到的问题，M是一个单点故障的问题。
为了解决这个问题，我采用了一个类似 方案2 的结构，没用 DRBD，也没用 MMM 而是使用了一个双通道的盘阵来挂两台机器来做 HA
结构图如下：

在这个结构里面我模仿了 MMM 和 DRBD 中的部分思想，使用一个 浮动 IP 给 Master ，使用 HA 软件检测 Master 的存活，并切换。
使用盘阵的目的是，考虑我的 bin-log mysql 的数据文件是放在同一个地方的，我希望我在切换Master的时候 S 同步不会掉。
做完这个工作 因自己的小聪明 沾沾自喜 若干天 ……

之后灾难降临了 ～～

首先是一次文件系统的 bug ，这让我看到了盘阵是个单点，同时也在安慰自己 用DRBD 碰到这个情况一样～～
在来一次主板的bug 非正常断电，造成Master 切换。很多表损坏了，光修表就好长时间，然后同步也掉了。
紧接着就是一次意外的断电，起来以后和上面的情况一样、修表、重做同步 ～～。
经过这些问题我发现这个架构简直就是灾难，看来耍小聪明是不行了，要来点实际的了。
痛定思痛之后认真的读了一遍 mysql 的官方手册最终吧问题集中在了两个点上。
1、 mysql master 的HA
2、 当 master 死亡以后 Slave 如何处理。
针对这两问题我和我们二个同事一起讨论了一下，最终我们确定了如下结构：

先说这个结构和上面提到的第三种情况非常类似，只不过我多增加了一个 S1 节点。然后分别在两个节点里放置了 S2 节点。
这个结构的巧妙之处就是我可以动态的吧他们变成 方案1 方案3 的任何一种形式，下面就看我如何动态迁移这个架构。
情况 1： S2 宕机， 解决方法：因S2 很多不会影响任何使用。
情况 2： S1 其中之一宕机， 即使我什么都不修改也只是变成了 方案3 的情况。仍旧可以冗余。（断掉的机器动态迁移到完好的那个数的底下）
情况 3： Master 宕机,  两台S1 中的1台升级成为 Master 成为标准 M -> S 的情况。（断掉的机器动态迁移到完好的那个数的底下）
为了不使机器浪费，我们需要能动态迁移同步断掉的机器到完好的那个树的底下。

这里写一个动态迁移的技巧。也就是我写这个文章的核心技术。
1、要动态迁移 S1 要打开 binlog 并记录同步的binlog日志，S2一级可以不必打开。
    在 S1 一级配置 my.cnf 打开其中的
    # 我一般喜欢把 log 和数据分开存放。
    log-bin = /var/log/mysqllog/bin-log/mysql-bin 
    log-slow-queries = /var/log/mysqllog/db-slow.log
    log-error = /var/log/mysqllog/db.err
    log-slave-updates
    # 我的 relay log 也存放在 /var/log/mysql/log
    relay-log = /var/log/mysqllog/relay-log
    relay-log-index = /var/log/mysqllog/relay-log-index
    relay-log-info-file = /var/log/mysqllog/relay-log.info
    # master info 我喜欢和数据放在一起。方便做Slave
    master-info-file = /var/lib/mysql/master.info

2、在Master里面建立一个表用于打标记和记录整个树的结构。
    首先添加一个用户要对 monitor_db 有 完全的控制权，其次这个用户还要有Select_priv,Reload_priv,File_priv,Super_priv,Lock_tables_priv,Repl_slave_priv这些权限。
     后面会用这个用户 改变结构，所以权限一定要加够。

INSERTINTO mysql.user VALUES('%','monitoruser',password("monitoruserpasswd"),'Y','N','N','N','N','N','Y','N','N','Y','N','N','N','N','N','Y','N','Y','N','Y','N','N','N','N','N','N','','','','',0,0,0,0);
INSERT INTO mysql.db VALUES
('%','monitor_db','monitoruser','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

     创建库、表，并定期更新，具体看程序吧。
     为了防止一台机器死亡，我把这个分别放在了 M 和两台 S1 上，并利用 crontab 执行。
     M 和 S 的 crontab 分别配置
     0-54/6 * * * * /usr/local/bin/insert_uuid
     2-56/6 * * * * /usr/local/bin/insert_uuid
     4-58/6 * * * * /usr/local/bin/insert_uuid
     这样就能保证 2 分钟就有一次更新了，即使机器宕机了，最少也能保证 4-6 分钟有一次更新。
     insert_uuid 代码如下。

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <uuid/uuid.h>
#include <mysql.h>

int main(int argc,char *argv[])
{
    char *server="127.0.0.1",*user="monitoruser",*password="monitoruserpasswd";
    MYSQL *conn;
    MYSQL_RES *res;
    MYSQL_ROW row;
    conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, server,user, password, "monitor_db", 0, NULL, 0)) {
        fprintf(stderr, "%s\n", mysql_error(conn));
        exit(EXIT_FAILURE);
    }
    uuid_t uu;
    char myuuid[37];
    uuid_generate(uu);
    uuid_unparse(uu,myuuid);
    char sqlstr[512];
    if (argc == 2) {
        char *opt = "--install";
        if (!strcmp(argv[1],opt))
        {
            strcpy(sqlstr,"DROP DATABASE monitor_db");
            mysql_query(conn,sqlstr);
            strcpy(sqlstr,"CREATE DATABASE monitor_db");
            mysql_query(conn,sqlstr);
           strcpy(sqlstr,"CREATE TABLE monitor_db.monitor_uuid (`uuid` char(41)NOT NULL,`time` int(11) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8");
            mysql_query(conn,sqlstr);
            strcpy(sqlstr,"INSERT INTO monitor_db.monitor_uuid VALUES ('UUID_00000000-0000-0000-0000-0000000',0000000000)");
            mysql_query(conn,sqlstr);
            if(mysql_affected_rows(conn) != 1)
                printf("install monitor_db error.\n");
        }
    } else {
    strcpy(sqlstr,"UPDATE monitor_db.monitor_uuid SET uuid=\"UUID_");
    strcat(strcat(sqlstr,myuuid),"\",time=UNIX_TIMESTAMP(NOW())");
    mysql_query(conn,sqlstr);
    if(mysql_affected_rows(conn) <= 0)
       printf("update uuid error.");
    }
    mysql_close(conn);
    exit(EXIT_SUCCESS);
}

首先编辑 char *server="127.0.0.1",*user="你的用户名",*password="你的密码"; 吧这里的 127.0.0.1 改成你 M 的ip ，user 改成 用户名，passowrd 改成密码
使用 gcc -O2 -I/usr/local/mysql/include/mysql -rdynamic-L/usr/local/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -luuid-o insert_uuid insert_uuid.c 编译
我的 mysql 是安装在 /usr/local/mysql 下面的，不同的情况请自己修改路径。
初始化 monitor_db  insert_uuid --install
然后在 crontab 里面添加即可。

3、写程序在 Slave 上面运行，当检测到自己的父节点死亡以后，利用 change master 语句迁移为新的父节点。
这部是成功的关键，如果你上一步成功了那么这步也就很容易了。下面是 change master 的脚本。

#!/usr/bin/python
"""Change the master from mysql slave"""

import os,sys,re,string,time,struct,MySQLdb
DBuser='monitoruser'
DBpasswd='monitoruserpasswd'

def main(masterip):
    try:
        slave_link = MySQLdb.connect(host='127.0.0.1',user=DBuser,passwd=DBpasswd,db='mysql')
    except Exception, e:
        print e
        sys.exit(1)

    slave_connect = slave_link.cursor()
    
    # slave stop
    sql = "slave stop"
    slave_connect.execute(sql)

    # get uuid
    sql = "select uuid from monitor_db.monitor_uuid"
    slave_connect.execute(sql)
    UUID=slave_connect.fetchall()[0][0]

    # get Exec_Master_Log_Pos
    sql = "show slave status"
    slave_connect.execute(sql)
    local_var = slave_connect.fetchall()[0]
    Exec_Master_Log_Pos=local_var[21]

    # get uuid form relaylog
    fp = open("/var/log/mysqllog/"+local_var[7],"rb")
    binlogstr=struct.unpack("4s",fp.read(4))
    if binlogstr[0] != chr(0xfe) + chr(0x62) + chr(0x69) + chr(0x6e):
        print "binlogfile error !"
        sys.exit(1)
    relay_log_pos = None
    while relay_log_pos == None:
        try:
            binlogstr=struct.unpack("4s 5x 4s 4s 2x",fp.read(19))
        except:
            print "Error: get uuid from relaylog failed"
            sys.exit(1)
       event_length = ord(binlogstr[1][0]) + ord(binlogstr[1][1])*256 +ord(binlogstr[1][2])*65536 + ord(binlogstr[1][3])*16777216
        event = struct.unpack(str(event_length-19)+"s",fp.read(event_length-19))
        if re.search(UUID,event[0]):
           relay_log_pos = ord(binlogstr[2][0]) + ord(binlogstr[2][1])*256 +ord(binlogstr[2][2])*65536 + ord(binlogstr[2][3])*16777216
            break
    fp.close()

    # connect mysql master
    try:
        master_link = MySQLdb.connect(host=masterip,user=DBuser,passwd=DBpasswd,db='mysql')
    except Exception, e:
        print e
        sys.exit(1)
    master_connect = master_link.cursor()

    # get master binlog file and size
    sql = "show binary logs"
    master_connect.execute(sql)
    file_size = master_connect.fetchall()

    # get uuid from master binlog
    pos = 0
    sql_cmd = None
    for fz in file_size[::-1]:
        sql = "show binlog events in '%s'" % fz[0]
        master_connect.execute(sql)
        for remote_var in master_connect.fetchall():
            if re.search(UUID,remote_var[5]):
                pos = Exec_Master_Log_Pos - relay_log_pos + remote_var[4]
                for fixsize in file_size[list(file_size).index(fz)::]:
                    if pos <= fixsize[1]:
                        sql_cmd = "change master to MASTER_HOST='%s',\
                                                    MASTER_USER='%s',\
                                                    MASTER_PASSWORD='%s',\
                                                    MASTER_LOG_FILE='%s',\
                                                    MASTER_LOG_POS=%s"\
                                  % (masterip,DBuser,DBpasswd,fixsize[0],pos)
                        break
                    else:
                        pos = pos - fixsize[1] + 117
                break
        if sql_cmd is not None: break 

    master_connect.close()
    master_link.close()
    
    # change master
    for sql in  ["flush tables","reset slave",sql_cmd,"slave start"]:
        slave_connect.execute(sql)

    time.sleep(1)
   event_name=['Slave_IO_State:','Master_Host:','Master_User:','Master_Port:','Connect_Retry:','Master_Log_File:','Read_Master_Log_Pos:',
                'Relay_Log_File:','Relay_Log_Pos:','Relay_Master_Log_File:','Slave_IO_Running:','Slave_SQL_Running:','Replicate_Do_DB:',
                'Replicate_Ignore_DB:','Replicate_Do_Table:','Replicate_Ignore_Table:','Replicate_Wild_Do_Table:','Replicate_Wild_Ignore_Table:',
                'Last_Errno:','Last_Error:','Skip_Counter:','Exec_Master_Log_Pos:','Relay_Log_Space:','Until_Condition:','Until_Log_File:',
                'Until_Log_Pos:','Master_SSL_Allowed:','Master_SSL_CA_File:','Master_SSL_CA_Path:','Master_SSL_Cert:','Master_SSL_Cipher:',
                 'Master_SSL_Key:','Seconds_Behind_Master:']
   
    sql = "show slave status"
    slave_connect.execute(sql)
    event_status = slave_connect.fetchall()[0]
    for i in range(0,33): print event_name.rjust(28),event_status
  
    slave_connect.close()
    slave_link.close()

if (len(sys.argv) == 3):
    if (sys.argv[1] == '--masterip'):
        iplist=string.split(sys.argv[2],'.')
        if len(iplist) == 4:
           if ( 0 <= int(iplist[0]) < 256 ) and ( 0 <= int(iplist[1])< 256 ) and ( 0 <= int(iplist[2]) < 256 ) and ( 0 <=int(iplist[3]) < 256 ):
                main(sys.argv[2])
                sys.exit(0)
else: 
    print "%s --masterip xx.xx.xx.xx" % sys.argv[0]
    sys.exit(1)

这个脚本需要依赖 MySQL-python
一切正常之后我们 只需要在 Slave2 的任意一台机器上测试即可.编辑上面的 
DBuser='monitoruser'
DBpasswd='monitoruserpasswd'
用show slave status ，查看现在的 master 是谁，然后使用 change_master.py --masterip 另一 S1 的ip，等待大约 1～2 分钟你就只要不报错你就可以查看你的 master 是否已经改变了。

如果是新做的数据库还不是一个从，你如果上面的 log 配置方式是按照我的方式配置的，你可以在装一个 rsync ，并配置这个 rsync 到 /var/lib/mysql 目录下 ，使用下面这个脚本快速制作一个从数据库。
脚本中不同处请自行修改。

#!/bin/bash
Slave_IP="192.168.1.100"
PATH=${PATH}:/usr/local/bin
/etc/init.d/mysqld stop
rm -rf /var/log/mysqllog/relay*
mysql -u'monitoruser' -p'monitoruserpasswd' -h${Slave_IP} -e "slave stop;flush tables;"
Exec_pos=($(mysql-u'replication_user' -p'800HRreplication' -h${Slave_IP} -e "show slavestatus\G"|awk '$0~/Exec_Master_Log_Pos/{print $2}'))
typeset -x RSYNC_PASSWORD='rsyncpassword';
rsync -av --delete mysql-data@${Slave_IP}::mysql-data /var/lib/mysql/
sed '3 c\'${Exec_pos} -i /var/lib/mysql/master.info
mysql -u'monitoruser' -p'monitoruserpasswd' -h${Slave_IP} -e "slave start;"
/etc/init.d/mysqld start
sleep 3
mysql -u'monitoruser' -p'monitoruserpasswd' -h127.0.0.1 -e "show slave status\G"

有了这些东西，我们就可以在联机的情况下，动态的迁移数据库集群到任何一个样子，如果是多机房的那就更适合不过了，在中心机房放置 mysql master，在其他的每个机房部署  S1 -> S2 就是一个标准的主从结构。
即使某个节点的mysql坏掉了我们也可以动态迁移到另一个节点去继续做同步，而不影响整体集群。
转载 http://bbs.linuxtone.org/forum-viewthread-tid-6514-highlight-%E6%9E%B6%E6%9E%84.html