当前位置:Gxlcms > 数据库问题 > MySQL 高可用架构

MySQL 高可用架构

时间:2021-07-01 10:21:17 帮助过:10人阅读

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


              <host default>

                  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

              </host>


              <host db1>

                  ip      192.168.110.128

                  mode    master

                  peer    db2

              </host>


              <host db2>

                  ip      192.168.110.130

                  mode    master

                  peer    db1

              </host>


              <host db3>

                  ip      192.168.110.131

                  mode    slave

              </host>


              <role writer>

                  hosts   db1, db2

                  ips     192.168.110.132

                  mode    exclusive

              </role>


              <role reader>

                  hosts   db2, db3

                  ips     192.168.110.133, 192.168.110.134

                  mode    balanced

              </role>

              

              可以在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


              <monitor>

                  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>


              <host default>

                  monitor_user        mmm_monitor

                  monitor_password    mmm_monitor

              </host>      


       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 -h192.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 -h192.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 -h192.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)]> 


本文出自 “linunx运维专题” 博客,请务必保留此出处http://lijianmin2008.blog.51cto.com/621678/1925092

MySQL 高可用架构

标签:mmm   mysql   mariadb   高可用   主从复制   

人气教程排行