PHP基础PHP教程php框架JavaScript

asp.netAJAX正则表达式ASP

输入banner图图片脚本导航/分类

MHA-Atlas-MySQL高可用 上（6）

  • masterha_check_ssh #检查MHA的SSH配置状况
  • masterha_check_repl #检查MySQL复制状况
  • masterha_check_status #检测当前MHA运行状态
  • masterha_master_monitor #检测master是否宕机
  • masterha_manger #启动MHA
  • masterha_master_switch #控制故障转移（自动或者手动）
  • masterha_conf_host #添加或删除配置的server信息
  • masterha_secondary_check #试图建立TCP连接从远程服务器
  • masterha_stop #停止MHA
  • #Node工具包主要包括以下几个工具：
  • save_binary_logs #保存和复制master的二进制日志
  • apply_diff_relay_logs #识别差异的中继日志事件
  • filter_mysqlbinlog #去除不必要的ROLLBACK事件
  • purge_relay_logs #清除中继日志
    •  

    1.4 MHA架构图

    技术分享图片

     

    2.MySQL-MHA准备工作

     

    2.1 实验环境：

    主机名IP地址（NAT）描述
    mysql-master eth0:192.168.200.131 系统：CentOS6.5（6.x都可以） 安装：mysql5.6
    mysql-slaveA eth0:192.168.200.145 系统：CentOS6.5（6.x都可以） 安装：mysql5.6
    mysql-slaveB eth0:192.168.200.146 系统：CentOS6.5（6.x都可以） 安装：mysql5.6
     

    2.2 准备软件包

     

    （1）mha管理节点安装包：

    mha4mysql-manager-0.56-0.el6.noarch.rpm

    mha4mysql-manager-0.56.tar.gz

     

    （2）mha node节点安装包：

    mha4mysql-node-0.56-0.el6.noarch.rpm

    mha4mysql-node-0.56.tar.gz

     

    （3） mysql中间件：

    Atlas-2.2.1.el6.x86_64.rpm

     

    （4） mysql源码安装包

    mysql-5.6.17-linux-glibc2.5-x86_64.tar

     

    2.3 主机名映射

     
    1. [root@localhost etc]# cat /etc/hosts
    2. 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
    3. ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
    4. 192.168.200.131 MySQL-Master
    5. 192.168.200.145 MySQL-SlaveA
    6. 192.168.200.146 MySQL-SlaveB
     

    2.4 关闭selinux和iptables

     
    1. [root@localhost ~]# vim /etc/sysconfig/selinux
    2. [root@localhost ~]# cat /etc/sysconfig/selinux | grep -v "#"
    4. SELINUX=disabled
    5. SELINUXTYPE=targeted
    7. [root@localhost ~]# setenforce 0
    8. [root@localhost ~]# service iptables stop
    9. iptables: Setting chains to policy ACCEPT: filter [ OK ]
    10. iptables: Flushing firewall rules: [ OK ]
    11. iptables: Unloading modules: [ OK ]
    12. [root@localhost ~]# chkconfig iptables off
     

    3.Mysql环境搭建

     

    3.1 环境检查

    mysql-master

     
    1. #系统版本
    2. [root@localhost bin]# cat /etc/redhat-release
    3. CentOS release 6.5 (Final)
    4. [root@localhost bin]# uname -r
    5. 2.6.32-431.el6.x86_64
    6. [root@localhost bin]# hostname -I
    7. 192.168.0.51

    mysql-slaveA

     
    1. #系统版本
    2. [root@localhost ~]# cat /etc/redhat-release
    3. CentOS release 6.5 (Final)
    4. [root@localhost ~]# uname -r
    5. 2.6.32-431.el6.x86_64
    6. [root@localhost ~]# hostname -I
    7. 192.168.0.52

    mysql-slaveB

     
    1. #系统版本
    2. [root@localhost ~]# cat /etc/redhat-release
    3. CentOS release 6.5 (Final)
    4. [root@localhost ~]# uname -r
    5. 2.6.32-431.el6.x86_64
    6. [root@localhost ~]# hostname -I
    7. 192.168.0.53
     

    3.2 安装mysql

     

    3.2.1 安装3台

     
    1. [root@localhost ~]# yum -y install ncurses-devel libaio
    2. [root@localhost ~]# tar xf mysql-5.6.17-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
    3. [root@localhost ~]# ln -s /usr/local/mysql-5.6.17-linux-glibc2.5-x86_64 /usr/local/mysql
    4. [root@localhost ~]# useradd mysql -s /sbin/nologin -M
    5. [root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data/
    6. [root@localhost ~]# /bin/cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
    7. [root@localhost ~]# /bin/cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
    8. [root@localhost ~]# ln -s /usr/local/mysql/bin/* /usr/local/bin/
    9. [root@localhost ~]# which mysqladmin
    10. /usr/local/bin/mysqladmin
     

    3.2.2 加入开启自启动mysql

     
    1. [root@localhost ~]# chkconfig mysqld on
    2. [root@localhost ~]# chkconfig mysqld --list
    3. mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
    4. [root@localhost ~]# /etc/init.d/mysqld start
    5. Starting MySQL. SUCCESS!
     

    3.2.3 配置密码

     
    1. [root@chenguanzhou ~]# mysqladmin -uroot password ‘111111‘
     

    4.配置基于FTID的主从复制

     

    4.1 先决条件

    • 主库和从库都要开启binlog
    • 主库和从库server-id不同
    • 要有主从复制用户
     

    4.2 主库操作（mysql-master）

     

    4.2.1 修改配置文件

     
    1. #修改主库配置文件/etc/my.cnf
    2. [root@localhost mysql]# cat /etc/my.cnf
    3. [client]
    4. socket = /usr/local/mysql/data/mysql.sock
    5. [mysqld]
    6. lower_case_table_names = 1
    7. default-storage-engine = InnoDB
    8. port = 3306
    9. datadir = /usr/local/mysql/data
    10. character-set-server = utf8
    11. socket = /usr/local/mysql/data/mysql.sock
    13. log_bin = mysql-bin #开启binlog日志
    14. server_id = 1 #设置server_id
    16. innodb_buffer_pool_size = 200M
    17. slave-parallel-workers = 8
    18. thread_cache_size = 600
    19. back_log = 600
    20. slave_net_timeout = 60
    21. max_binlog_size = 512M
    22. key_buffer_size = 8M
    23. query_cache_size = 64M
    24. join_buffer_size = 2M
    25. sort_buffer_size = 2M
    26. query_cache_type = 1
    27. thread_stack = 192K
    29. #重启动MySQL服务
    30. [root@localhost mysql]# /etc/init.d/mysqld restart
    31. Shutting down MySQL.. SUCCESS!
    32. Starting MySQL. SUCCESS!
     

    4.2.2 登陆MySQL删除不必要的用户并创建主从复制用户

    （1）删除不必要的用户

     
    1. mysql> select user,host from mysql.user;
    2. +------+-----------------------+
    3. | user | host |
    4. +------+-----------------------+
    5. | root | 127.0.0.1 |
    6. | root | ::1 |
    7. | | localhost |
    8. | root | localhost |
    9. | | localhost.localdomain |
    10. | root | localhost.localdomain |
    11. +------+-----------------------+
    12. 6 rows in set (0.00 sec)
    14. mysql> drop user root@‘127.0.0.1‘;
    15. Query OK, 0 rows affected (0.00 sec)
    17. mysql> drop user root@‘::1‘;
    18. Query OK, 0 rows affected (0.00 sec)
    20. mysql> drop user ‘ ‘@‘localhost‘;
    21. Query OK, 0 rows affected (0.00 sec)
    23. mysql> drop user ‘ ‘@‘mysql-db01‘;
    24. Query OK, 0 rows affected (0.00 sec)
    26. mysql> select user,host from mysql.user;
    27. +------+-----------------------+
    28. | user | host |
    29. +------+-----------------------+
    30. | root | localhost |
    31. | root | localhost.localdomain |
    32. +------+-----------------------+
    33. 2 rows in set (0.00 sec)

    (2）创建主从复制用户

     
    1. mysql> grant replication slave on *.* to rep@‘192.168.200.%‘ identified by ‘111111‘;
    2. Query OK, 0 rows affected (0.00 sec)
    4. mysql> select user,host from mysql.user;
    5. +------+------------------------+
    6. | user | host |
    7. +------+------------------------+
    8. | rep | 192.168.0.% |
    9. | root | localhost |
    10. | root | localhost.localdomain |
    11. +------+------------------------+
    12. 3 rows in set (0.00 sec)
    14. mysql> show grants for rep@‘192.168.0.%‘;
    15. +----------------------------------------------------------------------------------------------------------------------------+
    16. | Grants for rep@192.168.200.% |
    17. +----------------------------------------------------------------------------------------------------------------------------+
    18. | GRANT REPLICATION SLAVE ON *.* TO ‘rep‘@‘192.168.200.%‘ IDENTIFIED BY PASSWORD ‘*FD571203974BA9AFE270FE62151AE967ECA5E0AA‘ |
    19. +----------------------------------------------------------------------------------------------------------------------------+
    20. 1 row in set (0.00 sec)
     

    4.3 从库操作(mysql-slaveA和mysql-B)

     

    4.3.1 修改配置文件

     
    1. #slaveA和slaveB 两个配置文件只有一处不一样，都是这个配置文件修改
    2. [root@localhost ~]# cat /etc/my.cnf
    3. [client]
    4. socket = /usr/local/mysql/data/mysql.sock
    5. [mysqld]
    6. lower_case_table_names = 1
    7. default-storage-engine = InnoDB
    8. port = 3306
    9. datadir = /usr/local/mysql/data
    10. character-set-server = utf8
    11. socket = /usr/local/mysql/data/mysql.sock
    13. log_bin = mysql-bin #从binlog也要打开
    14. server_id = 5 #仅需修改此项 A是5 B是10
    16. innodb_buffer_pool_size = 200M
    17. slave-parallel-workers = 8
    18. thread_cache_size = 600
    19. back_log = 600
    20. slave_net_timeout = 60
    21. max_binlog_size = 512M
    22. key_buffer_size = 8M
    23. query_cache_size = 64M
    24. join_buffer_size = 2M
    25. sort_buffer_size = 2M
    26. query_cache_type = 1
    27. thread_stack = 192K
    28. [root@localhost ~]# /etc/init.d/mysqld restart #重启mysql
    29. Shutting down MySQL.. SUCCESS!
    30. Starting MySQL. SUCCESS!

    特别说明： 
    在以往如果是基于binlog日志的主从复制，则必须要记住主库的master状态信息。 
    但是在MySQL5.6版本里多了一个Gtid的功能，可以自动记录主从复制位置点的信息，并在日志中输出出来。 
    技术分享图片

     

    4.4 开启GTID

     
    1. #没开启之前先看一下GTID状态
    2. mysql> show global variables like ‘%gtid%‘;
    3. +--------------------------+-------+
    4. | Variable_name | Value |
    5. +--------------------------+-------+
    6. | enforce_gtid_consistency | OFF |
    7. | gtid_executed | |
    8. | gtid_mode | OFF |
    9. | gtid_owned | |
    10. | gtid_purged | |
    11. +--------------------------+-------+
    12. 5 rows in set (0.00 sec)

    编辑mysql配置文件（主库从库都需要修改)

     
    1. [root@localhost ~]# vim /etc/my.cnf
    3. [client]
    4. socket = /usr/local/mysql/data/mysql.sock
    5. [mysqld]
    7. gtid_mode =ON #这行修改
    8. log_slave_updates #这行修改
    9. enforce_gtid_consistency #这行修改
    11. lower_case_table_names = 1
    12. default-storage-engine = InnoDB
    13. port = 3306
    14. datadir = /usr/local/mysql/data
    15. character-set-server = utf8
    16. socket = /usr/local/mysql/data/mysql.sock
    18. log_bin = mysql-bin
    19. server_id = 1
    21. innodb_buffer_pool_size = 200M
    22. slave-parallel-workers = 8
    23. thread_cache_size = 600
    24. back_log = 600
    25. slave_net_timeout = 60
    26. max_binlog_size = 512M
    27. key_buffer_size = 8M
    28. query_cache_size = 64M
    29. join_buffer_size = 2M
    30. sort_buffer_size = 2M
    31. query_cache_type = 1
    32. thread_stack = 192K

    修改完配置文件以后重启动数据库(主从库都重启数据库)

     
    1. [root@localhost ~]# /etc/init.d/mysqld restart
    2. Shutting down MySQL.. SUCCESS!
    3. Starting MySQL. SUCCESS!

    再次查看GTID状态

     
    1. [root@localhost ~]# vim /etc/my.cnf
    2. [root@localhost ~]# /etc/init.d/mysqld restart
    3. Shutting down MySQL.. SUCCESS!
    4. Starting MySQL. SUCCESS!
    5. [root@localhost ~]# mysql -uroot -p111111
    6. Warning: Using a password on the command line interface can be insecure.
    7. Welcome to the MySQL monitor. Commands end with ; or \g.
    8. Your MySQL connection id is 1
    9. Server version: 5.6.17-log MySQL Community Server (GPL)
    11. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
    13. Oracle is a registered trademark of Oracle Corporation and/or its
    14. affiliates. Other names may be trademarks of their respective
    15. owners.
    17. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.
    19. mysql> show global variables like ‘%gtid%‘;
    20. +--------------------------+-------+
    21. | Variable_name | Value |
    22. +--------------------------+-------+
    23. | enforce_gtid_consistency | ON | #执行GTID一致
    24. | gtid_executed | |
    25. | gtid_mode | ON | #开启GTID模块
    26. | gtid_owned | |
    27. | gtid_purged | |
    28. +--------------------------+-------+
    29. 5 rows in set (0.00 sec)
    31. mysql>
     

    4.5 配置主从复制(mysql-slaveA,mysql-slaveB)

     
    1. #告诉从库主库是谁
    2. mysql> change master to master_host=‘192.168.200.131‘,master_user=‘rep‘,master_password=‘111111‘,master_auto_position=1;
    3. Query OK, 0 rows affected, 2 warnings (0.04 sec)
    5. mysql>
     

    4.6 开启从库的主从复制功能（mysql-slaveA,mysql-slaveB）

     
    1. mysql> start slave; #开启主从同步功能
    2. Query OK, 0 rows affected, 1 warning (0.01 sec)
    4. mysql> show slave status\G
    5. *************************** 1. row ***************************
    6. Slave_IO_State: Waiting for master to send event