mysql 集群架构

Mysql集群架构

mysql复制：
    简单的架构：  master ---> slave (双机热备)
    master ： 
        接受读、写请求。
        会有一个专门的IO线程，负载把本地的二进制日志记录发送到slave服务器
    slave：
        默认情况下，只能接受读请求,它可以减轻master的读压力
        会有两个线程：
            IO_THREAD : 负责连接master,获取master二进制日志记录
            SQL_THREAD: 负责分析下载回来的二进制日志记录，然后执行里面有效的sql语句，从而实现与master数据同步的目标
    其他架构：   
    master ---> slave1  -----> slave2 (级联架构)
        优点： 进一步分担读压力
        缺点： slave1 出现故障，后面的所有级联slave服务器都会同步失败

        /----> slave1
    master          (并联架构)
        \----> slave2

        优点：解决上面的slave1的单点故障，同时也分担读压力
        缺点：间接增加master的压力（传输二进制日志压力）

    master1 <------> master2 (互为主从)
        优点：从命名来看，两台master好像都能接受读、写请求，但实际上，往往运作的过程中，同一时刻只有其中一台master会接受写请求，另外一台接受读请求
          rw          ro
           |           |
        master1 <------> master2 (互为主从)

    复制核心原理：
        master把更新记录到二进制日志文件，slave通过提取master的二进制日志文件记录的sql语句并且执行，实现数据异步同步。

create table aaa;
bin-log   -------->   io（relay-bin-log）

              sql create table aaa;

把主上的二进制日志(bin-log)的内容传到从上的一个新的日志叫relay-bin-log
        从上的 IO 线程  负责传输     
        从上的 SQL 线程  负责从服务器解析日志

复制的过程：
    1、slave端的IO线程连上master端，请求
    2、master端返回给slave端，bin log文件名和位置信息
    3、IO线程把master端的bin log内容依次写到slave端relay bin log里，并把master端的bin-log文件名和位置记录到master.info里。
    4、salve端的sql线程，检测到relay bin log中内容更新，就会解析relay log里更新的内容，并执行这些操作；也就是说salve执行和master一样的操作而达到数据同步的目的；

demo1：实现双机热备（AB复制）
why？
    1、可以降低master读压力
    2、可以对数据库做“热备”，热备只能解决硬件master硬件故障，软件故障等重大故障问题，但无法解决人为误操作导致的逻辑故障（例如输入错误的SQL语句把重要的记录删除了），所以常规的备份是必须。

环境准备及要求：
1、关闭防火墙和selinux
2、hosts文件中两台服务器主机名和ip地址一一对应起来
3、系统时间需要同步
4、master和slave的数据库版本保持一致
5、master:10.1.1.2  slave:10.1.1.1

架构：Mster——>slave

具体步骤：
1、修改配置文件（master和slave）
master：
# vim /etc/my.cnf
[mysqld]
port=3306
datadir=/var/lib/mysql
pid-file=/var/lib/mysql/mysql.pid
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysql-err.log
user=mysql
log-bin=/var/lib/mysql/mysqld-bin       master必须开启二进制日志
server-id=1 mysql数据库的编号，master和slave必须不一样

slave：
# vim /usr/local/mysql/etc/my.cnf 
[mysqld]
port=3307
datadir=/mysql56
pid-file=/mysql56/mysql56.pid
socket=/mysql56/mysql56.sock
log-error=/mysql56/mysql56-err.log
user=mysql
log-bin=/mysql56/mysqld-bin     slave上的二进制日志可以开启也可以不开启，看具体情况
server-id=2     mysql数据库的编号
relay-log=/mysql56/relay-log        主从复制日志需要开启

修改完配置文件重启两边的mysql服务：
servivce mysql restart
service mysql.server restart
2、初始化数据，使两边数据一致。（以master为主）
master端全库备份：
# mysqldump --lock-all-tables --all-databases > /mnt/hgfs/soft/mysqlbak.sql -p
slave端全库恢复：
# service mysql.server stop
# rm -rf /mysql56/*
# /usr/local/mysql/scripts/mysql_install_db  --basedir=/usr/local/mysql 初始化数据库
# service mysql.server start
# mysql -p -S /mysql56/mysql56.sock < /mnt/hgfs/soft/mysqlbak.sql  导入数据

3、master端创建授权用户
mysql> grant replication slave on *.* to 'slave'@'10.1.1.%' identified by '123';
mysql> flush privileges;

4、查看master的正在写的二进制文件名和位置
mysql> flush tables with read lock; 先加锁，防止两边数据不一致;如果业务还未上线，这个就没有必要了
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;  只有打开二进制日志，这句命令才有结果，表示当前数据库的二进制日志写到什么位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      331 |              |                  |
+------------------+----------+--------------+------------------+
二进制文件名      正在写入的位置

5、slave端设定复制信息

mysql> change master to 
    -> master_host='10.1.1.20',     master ip
    -> master_user='slave',             同步用户
    -> master_password='123',       密码
    -> master_port=3306,                端口
    -> master_log_file='mysqld-bin.000001', 主上面查到到二进制日志名
    -> master_log_pos=331;          主上面查到的位置号

6、启动复制线程，开始同步
mysql> start slave;
mysql> show slave status \G;

             Slave_IO_Running: Yes 代表成功连接到master并且下载日志
            Slave_SQL_Running: Yes 代表成功执行日志中的SQL语句

回到master端解锁：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
7、测试验证
master写——>slave可以看到
slave写——>master看不到

在上述架构下实现故障迁移和恢复
故障迁移：
1、模拟master出现故障
直接找到master的数据库，把相应的进程kill掉，模拟故障
2、查看slave同步状态并停止向master同步数据
slave > show slave status \G;
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
                Last_IO_Error: error reconnecting to master 'slave@10.1.1.2:3307' - retry-time: 60  retries: 1

mysql> stop slave;  <---停止

3、master故障之后，前端的应用应该把读写请求都调度给slave

             r/w
      X       |
    master      slave


直接用客户端登录slave，对数据进行修改，模拟写操作
mysql> use db2;
mysql> insert into t1 set id=2;
mysql> insert into t1 set id=3;
mysql> use db1;
mysql> update t1 set name='test' where id=3;

故障修复
1、修复master
# service mysql start
Starting MySQL[  OK  ]
2、让master上线
如果这个时候直接启动slave，那么这将会导致主从数据不一致
slave端：
start slave

可以肯定的是，在这个架构下，master要上线，肯定只有一个选择，就是作为原有slave的从，重新上线：
    主       从
    slave  ---> master
情况：
    假设数据已经损害了、丢失了，那么最简单的方法就是重装master数据库，把master作为slave的从，原来的slave就变成新架构的主

具体步骤：
原来master：
1、把原有的数据目录下的内容清空、删除

2、重新初始化成一个新的数据库

3、和之前搭建主从的是一样的：
确保slave要可以成为新架构中主，它应该：
    打开了二进制
    server-id要和重新上线master不一样

还需要在slave上授权复制吗？得看实际情况，如果有相关帐号就不需要授权了
mysql> grant replication slave on *.* to 'slave'@'10.1.1.%' identified by '123';
mysql> flush privileges;

备份slave的数据 slave.sql
# mysqldump -S /mysql56/mysql56.sock --lock-all-tables --all-databases -p > /mnt/hgfs/soft/slave.sql
Enter password: 

在master使用slave.sql恢复，然后设定复制信息（change master to ...）
 # mysql < /mnt/hgfs/soft/slave.sql 

master> change master to 
    -> master_host='10.1.1.10', <---原来slave的IP
    -> master_port=3307,  <--- 确定端口正确
    -> master_user='slave',
    -> master_password='123',
    -> master_log_file='mysqld-bin.000004',
    -> master_log_pos=2008;

master> start slave;

确保新的架构复制成功之后，回到slave服务器，把数据目录下的master.info文件删除，否则的化，下次如果slave重启数据库服务，会自动连接master
slave # rm -rf /data2/master.info
IO线程把master端的bin log内容依次写到slave端relay bin log里，并把master端的bin-log文件名和位置记录到master.info里

demo2： 互为主从（双向复制）

master1和master2相互都作为对方的主和从
但是，同一时刻，只有其中一个节点接受写操作
      r/w         ro
      |       |   
    master1  <----> master2

部署步骤：

1、修改两台master的配置文件,确保2台master的server-id不一致，并且都开启了二进制日志和中继日志
# vim /etc/my.cnf
[mysqld]
port=3307
datadir=/var/lib/mysql
pid-file=/var/lib/mysql/mysql.pid
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysql-err.log
user=mysql
log-bin=/var/lib/mysql/mysqld-bin
server-id=1
relay-log=/var/lib/mysql/relay-log  增减中继日志的定义，因为它同时也作为master2的从

[mysqld]
port=3307
datadir=/mysql56
pid-file=/mysql56/mysql56.pid
socket=/mysql56/mysql56.sock
log-error=/mysql56/mysql56-err.log
user=mysql
log-bin=/mysql56/mysqld-bin
server-id=2
relay-log=/mysql56/relay-log     增减中继日志的定义，因为它同时也作为master2的从

重启服务：
service mysql restart

2、在master上授权一个帐号，允许slave服务器使用该帐号连接master并且下载二进制(两边都要做)
mysql> grant replication slave on *.* to slave@'10.1.1.%' identified by '123';

3、初始化数据库（让两边数据保持一致）
master1上备份
# mysqldump -u root -p123 -S /data/mysqld.sock  --all-databases --lock-all-tables --master-data=2 > master1.sql
master2上恢复
或者相反

4、master1：10.1.1.20上设定复制信息
mysql> show master status;  查看10.1.1.10上的binlog日志及位置
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000005 |      120 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> change master to
    -> master_host='10.1.1.10',
    -> master_port=3307,
    -> master_user='slave',
    -> master_password='123',
    -> master_log_file='mysqld-bin.000005',
    -> master_log_pos=120;  

启动复制线程，开始同步
mysql> start slave;
mysql> show slave status \G;
  Master_Log_File: mysqld-bin.000005
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: mysqld-bin.000005
             Slave_IO_Running: Yes      代表成功连接到master并且下载日志
            Slave_SQL_Running: Yes  代表成功执行日志中的SQL语句

5、master2：10.1.1.10上设置复制信息，让其作为master1的从
mysql> show master status;  10.1.1.20上查看
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000004 |      120 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------

mysql> change master to
    -> master_host='10.1.1.20',
    -> master_port=3307,
    -> master_user='slave',
    -> master_password='123',
    -> master_log_file='mysqld-bin.000004',
    -> master_log_pos=120;

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)

mysql> show slave status\G;
Relay_Master_Log_File: mysqld-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

6、测试验证
master1更改数据——>master2可以看到
master2更改数据——>master1也可以看到


注意：在架构工作正常的情况下，master1没有出现故障的情况下，尽可能不要给master1写请求的同时也给master2写请求。

==========================================================
故障情况：
状况1：
如果master2出现故障，而且仅仅是简单的故障，没有出现数据丢失，那么只需要重新启动master2，master2会把落后的数据自动同步。

    把master2进程杀掉，然后再启动，自动同步，无需额外操作

      r/w        X 停止调度请求给master2
    master1     master2
        修复后再重新把读请求调度给master2

状况2：
如果master2出现严重故障，数据已经丢失了，建议重新使用master1过去某个备份去搭建master2

      r/w        X 停止调度请求给master2
    master1     master2
        修复后再重新把读请求调度给master2    
修复步骤：
    1） 停止master1的复制线程： stop slave
    2)  重新搭建master2
    3)  重新设定master1向新的master2复制的设定，然后start slave

demo3：级联复制
master——>slave1——>slave2

把中间的从也要打开二进制日志。但是它默认不把应用master的操作记录到自己的二进制日志。所以需要打开一个参数让它记录，才可以传给第三级的从
然后在中间从和第三级的从之间再做一次AB复制就可以了

打开log-slave-updates=1，让第一台传过来relay日志记录到自己的二进制日志
思路：
先搭建好主从——>然后在加入slave2

总结：上面的复制架构默认都是异步的，也就是主库将binlog日志发送给从库，这一动作就结束了，并不会验证从库是否接受完毕。这样可以提供最佳的性能。但是同时也带来了很高的风险，当主服务器或者从服务器发生故障时，极有可能从服务器没有接到主服务器发过来的binglog日志，这样就会导致主从数据不一致，甚至导致数据丢失。为了解决该问题，mysql5.5引入了半同步复制模式。

demo4：mysql半同步复制 
    所谓的半同步复制就是master每commit一个事务(简单来说就是做一个改变数据的操作）,要确保slave接受完主服务器发送的binlog日志文件并写入到自己的中继日志relay log里，然后会给master信号，告诉对方已经接收完毕，这样master才能把事物成功commit。这样就保证了master-slave的数据绝对的一致（但是以牺牲master的性能为代价).但等待时间也是可以调整的。

mysql半同步复制等待时间超时后(默认时间为10秒)，会自动转换成异步复制
架构：
master——>slave 

步骤：
第一大步:
先要搭建好mysqlAB异步复制

第二大步:在异步基础上转成半同步复制
需要安装插件：
# ls /usr/local/mysql/lib/plugin/semisync_*
/usr/local/mysql/lib/plugin/semisync_master.so  master上
/usr/local/mysql/lib/plugin/semisync_slave.so       slave上

1、在master上安装这个插件
master> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

删除插件的方法：
mysql > uninstall plugin rpl_semi_sync_master;
master> show global variables like 'rpl_semi_sync%';  --安装OK后，主上会多几个参数
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |  --是否启用master的半同步复制
| rpl_semi_sync_master_timeout       | 10000 |  --默认主等待从返回信息的超时间时间，10秒。动态可调
| rpl_semi_sync_master_trace_level   | 32    |      用于开启半同步复制模式时的调试级别，默认是32 
| rpl_semi_sync_master_wait_no_slave | ON    |  --是否允许每个事物的提交都要等待slave的信号.on为每一个事物都等待，off则表示slave追赶上后，也不会开启半同步模式，需要手动开启
+------------------------------------+-------+
官方手册里都有解释：5.1.1 Server Option and Variable Reference
rpl_semi_sync_master_trace_level
Default 32
The semisynchronous replication debug trace level on the master. Four levels are defined:
1 = general level (for example, time function failures) 一般等级
16 = detail level (more verbose information)    更详细的信息
32 = net wait level (more information about network waits)  网络等待等级
64 = function level (information about function entry and exit)  函数等级 （有关函数输入和退出的信息）

2、在slave上安装插件
slave> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.03 sec)

slave>  show global variables like 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | OFF   | slave是否启用半同步复制
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

3，master上激活半同步复制
master> set global rpl_semi_sync_master_enabled =on;
Query OK, 0 rows affected (0.00 sec)

4，slave上激活半同步复制
slave> set global rpl_semi_sync_slave_enabled=on;
slave> stop slave IO_THREAD;
slave> start slave IO_THREAD;

5，在master查看状态
master > show global status like 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |  --有一个从服务器启用半同步复制
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |  --master等待slave回复的平均等待时间。单位毫秒
| Rpl_semi_sync_master_net_wait_time         | 0     |  --master总的等待时间。单位毫秒
| Rpl_semi_sync_master_net_waits             | 0     |  --master等待slave回复的总的等待次数
| Rpl_semi_sync_master_no_times              | 0     |  --master关闭半同步复制的次数
| Rpl_semi_sync_master_no_tx                 | 0     |   --表示从服务器确认的不成功提交的数量
| Rpl_semi_sync_master_status                | ON    |    --标记master现在是否是半同步复制状态
| Rpl_semi_sync_master_timefunc_failures     | 0     |   --master调用时间（如gettimeofday())失败的次数 
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |  --master花在每个事务上的平均等待时间
| Rpl_semi_sync_master_tx_wait_time          | 0     |  --master花在事物上总的等待时间
| Rpl_semi_sync_master_tx_waits              | 0     |  --master事物等待次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |  --后来的先到了，而先来的还没有到的次数
| Rpl_semi_sync_master_wait_sessions         | 0     |  --当前有多少个session因为slave回复而造成等待
| Rpl_semi_sync_master_yes_tx                | 0     |  --表示从服务器确认的成功提交数量
+--------------------------------------------+-------+

6，在slave上查看状态就只有下面一条信息
slave > show global status like 'rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+


第三大步：测试
工作原理：当slave从库的IO_Thread 线程将binlog日志接受完毕后，要给master一个确认，如果超过10s未收到slave的接收确认信号，那么就会自动转换为传统的异步复制模式。
正常情况下，master插入一条记录，查看slave是否有成功返回
master > insert into a values (3);
Query OK, 1 row affected (0.01 sec)

master > show global status like 'rpl_semi_sync%_yes_tx'; 
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Rpl_semi_sync_master_yes_tx |  1    |   --表示这次事物成功从slave返回一次确认信号
+-----------------------------+-------+

模拟故障：当slave挂掉后，master这边更改操作
service stop mysql
或者直接停止slave的IO_thread线程
stop slave io_thread;
master> insert into a values (4);
Query OK, 1 row affected (10.00 sec)    --这次插入一个值需要等待10秒（默认的等待时间)

master> insert into a values (5);
Query OK, 1 row affected (0.01 sec) --现在自动转成了原来的异步模式（类似oracle DG里的最大性能模式)


再次把slave启动，看到半同步复制没启来，是异步模式
重新按下面的步骤把同步模式再启起来就可以了
slave> set global rpl_semi_sync_slave_enabled=on;
slave> stop slave IO_THREAD;
slave> start slave IO_THREAD;

或者可以将该参数写入到配置文件中：
master：rpl_semi_sync_master_enabled=1
slave：rpl_semi_sync_slave_enabled=1  

结果：master需要等到slave确认后才能提交，如果等不到确认消息，master等待10s种后自动变成异步同步;slave启起来后，master上改变的数据还是会自动复制过来，数据又回到一致

等待时间可以动态调整：
mysql> set global rpl_semi_sync_master_timeout=3600000;
mysql> show global variables like 'rpl_semi_sync%';
+------------------------------------+---------+
| Variable_name                      | Value   |
+------------------------------------+---------+
| rpl_semi_sync_master_enabled       | ON      |
| rpl_semi_sync_master_timeout       | 3600000 |
| rpl_semi_sync_master_trace_level   | 32      |
| rpl_semi_sync_master_wait_no_slave | ON      |
+------------------------------------+---------+


demo5：自定义延迟复制时间
mysql开源管理工具  ——>maatkit  --perl写的

maatkit-7540.tar.gz

--在mysql AB的slave上安装（只需要在slave上安装，包含下面的步骤都是在slave上做的)
# tar xf maatkit-7540.tar.gz -C /usr/src/
# cd /usr/src/maatkit-7540/

安装方法README文件里有写
# perl Makefile.PL  --如果不成功，需要安装perl有关的多个包，可以yum install perl* ；如果安装报错，检查是否安装mysql-share包
# make install

[root@vm2 maatkit-7540]# ls bin/        --这些命令，就是各个管理工具
mk-archiver               mk-purge-logs
mk-checksum-filter        mk-query-advisor
mk-config-diff            mk-query-digest
mk-deadlock-logger        mk-query-profiler
mk-duplicate-key-checker  mk-show-grants
mk-error-log              mk-slave-delay
mk-fifo-split             mk-slave-find
mk-find                   mk-slave-move
mk-heartbeat              mk-slave-prefetch
mk-index-usage            mk-slave-restart
mk-kill                   mk-table-checksum
mk-loadavg                mk-table-sync
mk-log-player             mk-table-usage
mk-merge-mqd-results      mk-tcp-model
mk-parallel-dump          mk-upgrade
mk-parallel-restore       mk-variable-advisor
mk-profile-compact        mk-visual-explain


--使用--help查看一个命令的使用方法
# mk-slave-delay --help  启动和停止slave服务器使其滞后master
mk-slave-delay starts and stops a slave server as needed to make it lag behind
the master.  The SLAVE-HOST and MASTER-HOST use DSN syntax, and values are
copied from the SLAVE-HOST to the MASTER-HOST if omitted.  For more details,
please use the --help option, or try 'perldoc /usr/bin/mk-slave-delay' for
complete documentation.


# man mk-slave-delay   man文档

mk-slave-delay --delay 1m --interval 15s --run-time 10m slavehost
--delay：延迟1m复制
--interval：间隔15s去检查slave状态
--quiet：运行时不显示输出信息
--run-time：运行时间，默认一直运行

mysql AB(无论同步或异步)正在运行OK的情况下，使用下面的命令在slave上运行;做之间建议把时间同步一下

# mk-slave-delay --defaults-file=/usr/local/mysql/etc/my.cnf --delay=1m --interval=15s --user=root --password=123  --quiet localhost &
表示延时1分钟，才会应用SQL线程；这里是测试所以才使用很小的时间，实际情况可以调成1小时或2小时；间隔15s去检测slave是否需要被关闭或者启动


测试：

在master上随便插入几条数据

然后在slave上发现没有马上同步过来
slave > show slave status\G;   --查看状态会发现SQL线程状态为NO

        Slave_IO_Running: Yes
        Slave_SQL_Running: NO


大概等1分钟，就会自动延时同步过来了；
--注意:日志已经传到slave的relay-bin log里了，但由SQL线程延时去解析


问题1：
如果现在一个小公司mysql数据库已经跑了一年，现在要搭建mysqlAB复制，你检查了主库后，发现它这一年都没有使用二进制日志，请问如何做复制？

1、打开主库二进制日志，然后重启
2、全备主库，然后恢复到备库
3、搭建AB复制，指定从备份完成的那个日志position开始复制

问题2：
如果一个lamp架构在深圳机房在运行，如何尽量无影响的把这个lamp迁移到广州的机房

1、在广州那边先搭建lamp，其中web内容可以和深圳的这边做rsync远程实时同步，mysql做双主
2、改DNS的A记录，把IP由深圳的换成广州的
3、过一段时间（等深圳服务器没有连接了），就可以关闭深圳服务器了。
    stop slave;