MySQL高可用架构-创新互联

一、MMM 架构

    MMM(Master-Master replication manger for MySQL)是一套支持双主故障切换和双主日常管理的脚本程序，MMM使用Perl语言开发，

  主要用来监控和管理MySQL Master-Master(双主)复制，虽然叫做双主复制，但业务上同一时刻只允许一个主进行写入，另一台备选主上提供部

  分读服务，以加速在主主切换时刻备选主的预热，可以说MMM这套脚本程序一方面实现了故障切换功能，另一方面其内部附加的工具脚本也可以

  实现多个slaves负载均衡。

    MMM提供了自动和手动两种方式移除一组服务器中复制延时较高的服务器服务器的虚拟IP，同时它还可以备份数据、实现两节点之间的数据

   同步等。

    由于MMM无法完全地保证数据一致性，所以MMM适用于对数据的一致性要求不是很高，但又想大程度的保证业务可用性的场景。

   例：三台主机

      角色        IP地址       主机名字    server id

      --------------------------------------------------------------

      monitor host   192.168.110.130    db3

      --------------------------------------------------------------

      master 1     192.168.110.128    db1       1         writer(192.168.110.132)

      --------------------------------------------------------------

      master 2     192.168.110.130    db2       2         reader(192.168.110.133)

      --------------------------------------------------------------

      slave 1      192.168.110.131    db3       3         reader(192.168.110.134)

      --------------------------------------------------------------

     1、主机配置

       [root@www ~]# cat /etc/hosts

       127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4

       ::1     localhost localhost.localdomain localhost6 localhost6.localdomain6

       192.168.110.128 db1.pancou.com db1

       192.168.110.130 db2.pancou.com  db2

       192.168.110.131 db3.pancou.com  db3

     2、mysql的安装和配置

       db1:

       server-id    = 1

       log-slave-updates=true

       #gtid-mode=on

       #enforce-gtid-consistency=true

       master-info-repository=TABLE

       relay-log-info-repository=TABLE

       sync-master-info=1

       slave-parallel-threads=2

       binlog-checksum=CRC32

       master-verify-checksum=1

       slave-sql-verify-checksum=1

       binlog-rows-query-log_events=1

       report-port=3306

       report-host=www.pancou.com

      db2:

       server-id    = 2

       log-slave-updates=true

       #gtid-mode=on

       #enforce-gtid-consistency=true

       master-info-repository=TABLE

       relay-log-info-repository=TABLE

       sync-master-info=1

       slave-parallel-threads=2

       binlog-checksum=CRC32

       master-verify-checksum=1

       slave-sql-verify-checksum=1

       binlog-rows-query-log_events=1

       report-port=3306

       report-host=www.pancou.com

      db3:

      server-id    = 3

      log-slave-updates=true

      #gtid-mode=on

      #enforce-gtid-consistency=true

      master-info-repository=TABLE

      relay-log-info-repository=TABLE

      sync-master-info=1

      slave-parallel-threads=2

      binlog-checksum=CRC32

      master-verify-checksum=1

      slave-sql-verify-checksum=1

      binlog-rows-query-log_events=1

      report-port=3306

      report-host=www.pancou.com

     3、主从复制，和双主复制看前面复制章节

     4、安装mysql-mmm

       1. 安装监控程序

        在管理服务器和数据库服务器上分别要运行mysql-mmm monitor和agent程序。下面分别安装：

        前提要安装

        #rpm -ivh epel-release-6-8.noarch.rpm

        在管理服务器（192.168.110.130）上，执行下面命令：

        # yum -y install mysql-mmm-monitor*

        与monitor依赖的所有文件也会随之安装，但是有一个例外perl-Time-HiRes，所以还需要执行下面的命令：

        [plain] view plain copy print?

         # yum -y install perl-Time-HiRes*

        2. 安装代理程序

        # yum -y install mysql-mmm-agent*

        在192.168.110.128和192.168.110.131 上分别安装：

        # yum -y install mysql-mmm-agent*

      5、配置MMM

       1.配置agent文件，需要在db1,db2,db3分别配置

         完成安装后，所有的配置文件都放到了/etc/mysql-mmm/下面。管理服务器和数据库服务器上都要包含一个共同

         的文件mmm_common.conf，

       在db1上配置：

        active_master_role    writer

         cluster_interface    eth0

         pid_path         /var/run/mysql-mmm/mmm_agentd.pid

         bin_path         /usr/libexec/mysql-mmm/

         replication_user     repl_user

         replication_password   pancou

         agent_user        mmm-agent

         agent_password      mmm-agent

         ip    192.168.110.128

         mode   master

         peer   db2

         ip    192.168.110.130

         mode   master

         peer   db1

         ip    192.168.110.131

         mode   slave

         hosts  db1, db2

         ips   192.168.110.132

         mode   exclusive

         hosts  db2, db3

         ips   192.168.110.133, 192.168.110.134

         mode   balanced

       可以在db1上编辑该文件后，通过scp命令分别复制到monitor、db2、db3和db4上。

       复制到db2上：

       scp /etc/mysql-mmm/mmm_com.conf db2:/etc/mysql-mmm/

       复制到db3上：

       scp /etc/mysql-mmm/mmm_com.conf db3:/etc/mysql-mmm/

     2. 编辑mmm_agent.conf。在数据库服务器上，还有一个mmm_agent.conf需要修改

       db1:

       # vim /etc/mysql-mmm/mmm_agent.conf

       include mmm_common.conf

       # The 'this' variable refers to this server.  Proper operation requires

       # that 'this' server (db1 by default), as well as all other servers, have the

       # proper IP addresses set in mmm_common.conf.

       this db1

       db2:

       # vim /etc/mysql-mmm/mmm_agent.conf

       include mmm_common.conf

       # The 'this' variable refers to this server.  Proper operation requires

       # that 'this' server (db1 by default), as well as all other servers, have the

       # proper IP addresses set in mmm_common.conf.

       this db2

       db3:

       # vim /etc/mysql-mmm/mmm_agent.conf

       include mmm_common.conf

       # The 'this' variable refers to this server.  Proper operation requires

       # that 'this' server (db1 by default), as well as all other servers, have the

       # proper IP addresses set in mmm_common.conf.

       this db3

      3. 编辑mmm_mon.confg。在管理服务器上，修改mmm_mon.conf文件

       在db2上

       # vim /etc/mysql-mmm/mmm_mon.conf

       include mmm_common.conf

         ip          127.0.0.1

         pid_path       /var/run/mysql-mmm/mmm_mond.pid

         bin_path       /usr/libexec/mysql-mmm

         status_path     /var/lib/mysql-mmm/mmm_mond.status

         ping_ips       192.168.110.128,192.168.110.130,192.168.110.131

         auto_set_online   60

         # The kill_host_bin does not exist by default, though the monitor will

         # throw a warning about it missing.  See the section 5.10 "Kill Host

         # Functionality" in the PDF documentation.

         #

         # kill_host_bin   /usr/libexec/mysql-mmm/monitor/kill_host

         #

         monitor_user     mmm_monitor

         monitor_password   mmm_monitor

    6、创建监控

     MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.110.%' identified by 'mmm_monitor';

     MariaDB [(none)]> grant super,replication client,process on *.* to 'mmm-agent'@'192.168.110.%' identified by 'mmm-agent';

     MariaDB [(none)]> grant replication slave on *.*  to 'repl_user'@'192.168.110.%' identified by 'pancou';

     MariaDB [(none)]> flush priviliges;

    7、启动MMM

     1. 在数据库服务器上启动代理程序

     # service mysql-mmm-agent start

     Starting MMM Agent Daemon:                 [  OK  ]

     2. 在管理服务器上启动监控程序

     # service mysql-mmm-monitor start

     Starting MMM Monitor Daemon:                [  OK  ]

    8、在monitor上检查集群主机的状态

     [root@www ~]# mmm_control checks all

     db2  ping     [last change: 2016/07/04 08:54:52]  OK

     db2  mysql     [last change: 2016/07/04 08:54:52]  OK

     db2  rep_threads  [last change: 2016/07/04 08:54:52]  ERROR: Replication is broken

     db2  rep_backlog  [last change: 2016/07/04 08:54:52]  OK: Backlog is null

     db3  ping     [last change: 2016/07/04 08:54:52]  OK

     db3  mysql     [last change: 2016/07/04 08:55:57]  OK

     db3  rep_threads  [last change: 2016/07/04 08:55:54]  OK

     db3  rep_backlog  [last change: 2016/07/04 08:55:54]  OK: Backlog is null

     db1  ping     [last change: 2016/07/04 08:54:52]  OK

     db1  mysql     [last change: 2016/07/04 08:54:52]  OK

     db1  rep_threads  [last change: 2016/07/04 08:55:25]  ERROR: Replication is broken

     db1  rep_backlog  [last change: 2016/07/04 08:54:52]  OK: Backlog is null

    复制问题解决以后：

    [root@www ~]# mmm_control checks all

    db2  ping     [last change: 2016/07/05 03:54:20]  OK

    db2  mysql     [last change: 2016/07/05 03:54:20]  OK

    db2  rep_threads  [last change: 2016/07/05 03:54:20]  OK

    db2  rep_backlog  [last change: 2016/07/05 03:54:20]  OK: Backlog is null

    db3  ping     [last change: 2016/07/05 03:54:20]  OK

    db3  mysql     [last change: 2016/07/05 03:54:20]  OK

    db3  rep_threads  [last change: 2016/07/05 03:54:20]  OK

    db3  rep_backlog  [last change: 2016/07/05 03:54:20]  OK: Backlog is null

    db1  ping     [last change: 2016/07/05 03:54:20]  OK

    db1  mysql     [last change: 2016/07/05 03:54:20]  OK

    db1  rep_threads  [last change: 2016/07/05 03:54:20]  OK

    db1  rep_backlog  [last change: 2016/07/05 03:54:20]  OK: Backlog is null

    [root@www ~]# mmm_control show

    # Warning: agent on host db1 is not reachable

    # Warning: agent on host db3 is not reachable

     db1(192.168.110.128) master/REPLICATION_FAIL. Roles:

     db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.128), reader(192.168.110.130), writer(192.168.110.132)

     db3(192.168.110.131) slave/ONLINE. Roles:

    复制问题解决以后：

    [root@www ~]# mmm_control show

    # Warning: agent on host db1 is not reachable

    # Warning: agent on host db2 is not reachable

     db1(192.168.110.128) master/ONLINE. Roles:

     db2(192.168.110.130) master/ONLINE. Roles:

     db3(192.168.110.131) slave/ONLINE. Roles:

   iptales -F

   [root@www ~]# mmm_control show

   db1(192.168.110.128) master/ONLINE. Roles:

   db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.133), writer(192.168.110.132)

   db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.134)

   9、MMM高可用环境测试

     在db2上：

     [root@www ~]# service mysqld stop

     Shutting down MySQL.. SUCCESS!

     [root@www ~]# iptables -F

     在monitor上：

     [root@www ~]# mmm_control show

     db1(192.168.110.128) master/ONLINE. Roles: writer(192.168.110.132)

     db2(192.168.110.130) master/HARD_OFFLINE. Roles:

     db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.133), reader(192.168.110.134)

     [root@www ~]# tail -f /var/log/mysql-mmm/mmm_mond.log

     2016/07/05 07:38:33 FATAL Agent on host 'db2' is reachable again

     2016/07/05 07:38:41 FATAL Can't reach agent on host 'db2'

     2016/07/05 07:38:45 FATAL Agent on host 'db2' is reachable again

     2016/07/05 07:45:43 FATAL Agent on host 'db1' is reachable again

     2016/07/05 07:48:48 FATAL Can't reach agent on host 'db3'

     2016/07/05 07:49:03 FATAL Can't reach agent on host 'db2'

     2016/07/05 07:49:12 FATAL Can't reach agent on host 'db1'

     2016/07/05 07:49:18 FATAL Agent on host 'db1' is reachable again

     2016/07/05 07:49:34 FATAL Agent on host 'db2' is reachable again

     2016/07/05 07:49:46 FATAL Agent on host 'db3' is reachable again

     2016/07/05 07:56:00 FATAL State of host 'db2' changed from ONLINE to HARD_OFFLINE (ping: OK, mysql: not OK)

    此时，db2,的状态由ONLINE 变为 HARD_OFFLINE，把db2的读角色转移到db3,写角色转移到db1.

    [root@www ~]# service mysqld start

    Starting MySQL.. SUCCESS!

    2016/07/05 08:00:29 FATAL State of host 'db2' changed from HARD_OFFLINE to AWAITING_RECOVERY

    2016/07/05 08:01:29 FATAL State of host 'db2' changed from AWAITING_RECOVERY to ONLINE because of auto_set_online(60 seconds). It was in state AWAITING_RECOVERY for 60 seconds

    查看集群状态：

    [root@www ~]# mmm_control show

    db1(192.168.110.128) master/ONLINE. Roles: writer(192.168.110.132)

    db2(192.168.110.130) master/ONLINE. Roles: reader(192.168.110.133)

    db3(192.168.110.131) slave/ONLINE. Roles: reader(192.168.110.134)

    [root@www ~]# mysql -ummm-monitor -p -h292.168.110.132

    Enter password:

    Welcome to the MariaDB monitor.  Commands end with ; or \g.

    Your MariaDB connection id is 9108

    Server version: 10.0.15-MariaDB-log Source distribution

    Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    MariaDB [(none)]>

    [root@www ~]# mysql -ummm-monitor -p -h292.168.110.133

    Enter password:

    Welcome to the MariaDB monitor.  Commands end with ; or \g.

    Your MariaDB connection id is 184

    Server version: 10.0.15-MariaDB-log Source distribution

    Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    MariaDB [(none)]>

   [root@www ~]# mysql -ummm-monitor -p -h292.168.110.134

   Enter password:

   Welcome to the MariaDB monitor.  Commands end with ; or \g.

   Your MariaDB connection id is 9446

   Server version: 10.0.15-MariaDB-log Source distribution

   Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   MariaDB [(none)]>

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。