Mysql双主高可用

mysql安装

解压文件

tar -xvf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz

改名

mv -v mysql-5.7.22-linux-glibc2.12-x86_64 /usr/lcoal/mysql

创建用户和用户组

groupadd mysql
useradd -g mysql mysql

在mysql目录下创建data文件夹

mkdir data 

将安装目录所有者及所属组改为mysql

chown -R mysql.mysql /usr/local/mysql

安装依赖

yum install -y perl-Module-Install.noarch

初始化数据库

/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data

将mysql加入到服务

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

开机启动

chkconfig mysql on

将MySQL命令加入环境变量

export PATH=$PATH:/usr/local/mysql/bin

启动mysql

service mysql start

完成初始化后编辑配置文件 /etc/my.cnf

[client]
port = 3306
socket = /usr/local/mysql/mysql.sock
default_character_set = utf8
[mysqld]
port = 3306
socket  = /usr/local/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
character-set-server = utf8
lower_case_table_names = 1
log-error=/var/log/mysqld.log
max_allowed_packet = 64M
max_connections = 1000
max_connect_errors = 10000
skip-name-resolve
pid-file=/tmp/mysqld.pid

分别给数据库配置文件添加：
db1的

server_id = 1
log-bin = mysql-bin
log-bin-trust-function-creators=1
relay-log = relay-log-bin
relay-log-index = relay-log-bin.index
binlog-ignore-db = mysql,information_schema
#auto-increment-increment = 2
#auto-increment-offset = 1
slave-skip-errors = all

db2的

server_id = 2 两台数据库此数值不同
log-bin = mysql-bin
log-bin-trust-function-creators=1
relay-log = relay-log-bin
relay-log-index = relay-log-bin.index
#log-bin-index = my-bin.index
binlog-ignore-db = mysql,information_schema
#auto-increment-increment = 2
#auto-increment-offset = 1
slave-skip-errors = all

数据库操作：

更改数据库uuid

show variables like '%uuid%';

vi /usr/local/mysql/data/auto.cnf

重启数据库

Systemctl restart mysql

Master1：

grant replication slave on *.* to 'repl'@'192.168.188.3' identified by '123456';        //创建mysql账号repl，只容许指定ip访问，也可以指定ip范围192.168.188.%，其中%为通配符，表示所有；
flush privileges;            //刷新授权表信息
flush tables with read lock;    //锁定数据库表暂时无法写服务；
show master status；        //查看binlog文件值与pos值
stop slave；            //关闭同步

Maser2:

grant replication slave on *.* to 'repl'@'192.168.188.2' identified by '123456';              //创建用户，允许192.168.188.2登录本机器
flush privileges；                //刷新授权表
stop slave；                        //关闭同步
show master status；        //查看binlog文件值与pos值
change master to master_host='192.168.188.2', master_user='repl', master_password='123456', master_log_file='test01.000001', master_log_pos=664383;        //这里注意log_file与pos值都要对应对应A的show master status；值
start slave；        //开启同步

Mater1:

change master to master_host='192.168.188.3', master_user='repl', master_password='123456', master_log_file='test02.000001', master_log_pos=664343;     //这里log_file与pos值写的必须是B上show master status；的值
start slave；                    //开启同步
unlock tables；                //解锁写

show slave status\G; 查看是否成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

报日志错误用：

stop slave;
reset slave;
start slave;

测试

create table t1(`id` int(4),`name` char(40));  

部署keeplive

安装依赖

yum install -y openssl openssl-devel gcc

安装软件包

tar xvf keepalived-2.0.7.tar.gz
cd keepalived-2.0.7
./configure --prefix=/usr/local/keepalived
make && make install

完成后会在以下路径生成：

/usr/local/etc/keepalived/keepalived.conf
/usr/local/etc/sysconfig/keepalived
/usr/local/sbin/keepalived

keepalived启动脚本变量引用文件，默认文件路径是/etc/sysconfig/

cp /usr/local/keepalived/etc/sysconfig/keepalived  /etc/sysconfig/keepalived 

将keepalived主程序加入到环境变量（安装目录下）

cp /usr/local/keepalived/sbin/keepalived /usr/sbin/keepalived

keepalived启动脚本（源码目录下），放到/etc/init.d/目录下就可以使用service命令便捷调用

cp  /usr/local/keepalived-2.0.20/keepalived/etc/init.d/keepalived  /etc/init.d/keepalived

将配置文件放到默认路径下

mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf

加为系统服务：chkconfig --add keepalived
开机启动：chkconfig keepalived on
查看开机启动的服务：chkconfig --list
启动、关闭、重启service keepalived start|stop|restart

编辑keepalived.conf文件

! Configuration File for keepalived

global_defs {
notification_email {
ops@111.cn
tech@111.cn
}
script_user root
enable_script_security
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MASTER-HA
}
#检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
vrrp_script chk_mysql_port {
    script "/opt/chk_mysql.sh" #这里通过脚本监测
              interval 2   #脚本执行间隔，每2s检测一次
              weight -5  #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5

              fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）

              rise 1   #检测1次成功就算成功。但不修改优先级

}

vrrp_instance VI_1 {
    state MASTER
    interface ens33   #指定虚拟ip的网卡接口
    mcast_src_ip 192.168.1.20
    virtual_router_id 51  #路由器标识，MASTER和BACKUP必须是一致的
    priority 99  #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就>可以将VIP资源再次抢回来，但是我们这里使用的是非抢占模式 所以两台机器除去自己IP地址之外其他配置是一致的。
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
virtual_ipaddress {
         192.168.1.25
    }

track_script {
   chk_mysql_port
}
}

添加MySQL检测文件/opt/chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep LISTEN|grep 3306|wc -l)
if [ ${counter} = 0 ]; then
    /etc/init.d/keepalived stop
fi

启动服务

/etc/init.d/keepalived start