mysql MHA集群高可用部署方案

关于ip安装文件等后期再慢慢调整完善，内容没有问题。

搭建 MySQL双主双从

mysql-master1配置

[root@hades]# vim /etc/my.cnf

datadir=/data/mysql
socket=/var/lib/mysql/mysqld.sock
user=mysql
symbolic-links=0

server-id=1
log-bin=mysql-bin

log-error=/var/log/mysqld.log
pid-file=/var/lib/mysql/master1.pid
binlog-ignore-db=mysql 
binlog-ignore-db=information_schema
binlog_format=STATEMENT

log-slave-updates
bind-address=0.0.0.0

mysql-slave1配置

[root@hades ]# vim /etc/my.cnf	

[mysqld]
datadir=/data/mysql
socket=/var/lib/mysql/mysqld.sock
user=mysql
symbolic-links=0
server-id=2  
relay-log=mysql-relay
pid-file=/var/lib/mysql/slave1.pid
bind-address=0.0.0.0

master2，slave2同上，改变对应id
双主机、双从机重启 mysql 服务

主1 mysql执行

[root@hades ]# mysql –u root –p
[root@hades ]# use mysql;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'Siger_123';
show master status;

主2 mysql操作如上

分别记录File和position的值
执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

从1mysql上执行,复制主1mysql, 更改主机Ip 和mysql-bin

CHANGE MASTER TO MASTER_HOST=‘主机的IP地址’, MASTER_USER=‘slave’, MASTER_PASSWORD=‘密码’, MASTER_LOG_FILE=‘mysql-bin.具体数字’,MASTER_LOG_POS=具体值;

从2 mysql执行同上，复制从2 mysql，更改主机Ip 和mysql-bin。查看从机状态

show slave status\G;

两个都为yes即可
然后主主复制，操作命令同上，分别操作主1复制主2，主2复制主1.

—————————————————————————————————————————————

安装 Mycat

解压jdk安装包

[root@hades ]# tar -zxvf jdk-8u102-linux.gz

修改环境变量

[root@hades ]# vim /etc/profile
JAVA_HOME=/usr/local/java/jdk1.8.0_102
JRE_HOME=/usr/local/java/jdk1.8.0_102/jre

PATH=$PATH:/sbin/:$JAVA_HOME/bin:$CATALINA_HOME/bin

CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:
export JAVA_HOME JRE_HOME PATH CLASSPATH

mycat解压后即可使用，解压缩mycat文件拷贝到 linux 下 /usr/local/

两个配置文件，配置读写分离
① schema.xml：定义逻辑库，表、分片节点等内容

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="库名" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <schema name="160_160" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn2">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="库名" />
        <dataNode name="dn2" dataHost="host1" database="160_160" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="SHAJDDFDBT01.corp.skf.net" url="ip:端口号" user="用户名" password="密码">
                        <!-- can have multi read hosts -->
                        <readHost host="SHAJDDFDBT02.corp.skf.net" url="10.10.10.10:3306" user="root" password="123" />
                </writeHost>
                <writeHost host="SHAJDDFDBT03.corp.skf.net" url="10.10.10.11:3306" user="root" password="123">
                        <!-- can have multi read hosts -->
                        <readHost host="SHAJDDFDBT04.corp.skf.net" url="10.10.10.11:3306" user="root" password="123" />
                </writeHost>
        </dataHost>
</mycat:schema>

② server.xml：定义用户以及系统相关变量，如端口等.配置文件过长，截取重要部分。

<user defaultAccount="true" name="mycat">
                <property name="password">Siger_123</property>
                <property name="schemas">siger,160_160</property>
 </user>

mysql远程访问验证

[root@hades ]# mysql -uroot -p123 -h 10.10.10.5（mysql 安装ip） -P 3306

远程访问权限

[root@hades ]# grant all privileges on *.* to root@'%' identified by '123'; 

启动mycat

[root@hades ]# 去 mycat/bin 目录下 ./mycat start （后台）

验证mycat

[root@hades ]# mysql -umycat -p123 -P 9066 -h 10.22.43.32（mycat安装ip）

—————————————————————————————————————————————

部署 基于 HA 机制的 Mycat 高可用

准备好HAProxy安装包，传到/opt目录下
解压到/usr/local/src
[root@hades ]# tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src
进入解压后的目录，查看内核版本，进行编译

[root@hades ]# cd /usr/local/src/haproxy-1.5.18 
[root@hades ]# uname -r 
[root@hades ]# make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64 
 # ARGET=linux310，内核版本，使用uname -r查看内核，如：3.10.0-514.el7，此时该参数就为linux310；
 #ARCH=x86_64，系统位数； 
#PREFIX=/usr/local/haprpxy #/usr/local/haprpxy，为haprpxy安装路径。 

编译完成后，进行安装

[root@hades ]# make install PREFIX=/usr/local/haproxy 

安装完成后，创建目录、创建HAProxy配置文件

[root@hades ]# mkdir -p /usr/data/haproxy/ 
[root@hades ]# vim /usr/local/haproxy/haproxy.conf
配置文件：
global         
          log 127.0.0.1   local0         
          #log 127.0.0.1   local1 notice         
          #log loghost    local0 info         
          maxconn 4096         
          chroot /usr/local/haproxy         
          pidfile /usr/data/haproxy/haproxy.pid         
          uid 99         
          gid 99         
          daemon         
          #debug         
          #quiet 
 
 
defaults         
          log     global 
          mode    tcp         
          option  abortonclose         
          option redispatch         
          retries 3         
          maxconn 2000         
          timeout connect 5000         
          timeout client  50000         
          timeout server  50000 
 
 
listen proxy_status   
     bind :48066         
          mode tcp         
          balance roundrobin         
          server mycat_1 10.10.10.5:8066 check inter 10s         
          server mycat_2 10.10.10.6:8066 check inter 10s 
 
 
frontend admin_stats   
     bind :7777         
          mode http         
          stats enable         
          option httplog         
          maxconn 10         
          stats refresh 30s         
          stats uri /admin         
          stats auth admin:admin         
          stats hide-version         
          stats admin if TRUE

启动命令

[root@hades ]# /usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
ps -ef|grep haproxy

[root@hades ]# mysql -umycat -p123 -h 10.10.10.5 -P 48066 

—————————————————————————————————————————————

安装 Keepalived

准备好Keepalived安装包，传到/opt目录下
解压到/usr/local/src

[root@hades ]# tar -zxvf keepalived-1.4.2.tar.gz -C /usr/local/src 

安装依赖插件

[root@hades ]# yum install -y gcc openssl-devel popt-devel 

进入解压后的目录，进行配置，进行编译

[root@hades ]# cd /usr/local/src/keepalived-1.4.2 
[root@hades ]#./configure --prefix=/usr/local/keepalived 

进行编译，完成后进行安装

[root@hades ]# make && make install 

运行前配置

[root@hades ]# cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/ 
mkdir /etc/keepalived 
[root@hades ]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ 
[root@hades ]# cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

配置文件

! Configuration File for keepalived 
 
global_defs {
    notification_email {
	  siger_data@www.com   
	}
    notification_email_from keepalived@showjoy.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id LVS_DEVEL
    vrrp_skip_check_adv_addr
    vrrp_garp_interval 0
    vrrp_gna_interval 0 
} 
 
vrrp_instance VI_1 {
     #主机配MASTER，备机配BACKUP 
	 state MASTER 
	 #所在机器网卡     
	 interface eth0 
	 virtual_router_id 50 
	 #数值越大优先级越高     
	 priority 100     
	 advert_int 1     
	 authentication {         
	     auth_type PASS         
	     auth_pass 1111     
	 } 
	 virtual_ipaddress { 
	 #虚拟IP         
	       10.10.10.50
	 } 
}

启动

[root@hades ]# service keepalived start

验证虚拟地址

[root@hades ]# mysql -umycat -p123 -h 10.178.46.20 -P 48066