MySQL主主复制以及使用keepalived保证高可用

1：准备工作

MySQL的安装步骤在此处省略；安装完成一定要做以下准备工作，初始化MySQL，/usr/bin/mysql_secure_installation，设置root密码，删除无效账户以及test库，必须保持两台MySQL上的root密码一致，因为我们这里的主主复制是要做高可用的，mysql库涉及到账户和密码以及权限，也会进行同步。

PS:这里如果是编译安装命令应该在/usr/local/mysql/bin/mysql_secure_installation下，yum安装，MySQL启动后密码会生成在/var/log/mysqld.log文件中。
[root@node1 ~]# /usr/bin/mysql_secure_installation
New password: 

Re-enter new password: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
#设置新密码
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
#删除匿名用户
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : 
#拒绝root账户远程登录
Remove test database and access to it? (Press y|Y for Yes, any other key for No) :
删除test数据库
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
#重新加载权限

2：修改配置文件，建立主主复制

PS:注意以下配置中除了server_id不同以外，其他配置最好保持相同。硬件配置最好也要相同。

[mysqld]
#---------此处为MySQL优化参数配置----------
#修改最大连接数
max_connections = 500
#设置默认字符集为utf8
character-set-server=utf8
#查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16M
sort_buffer_size = 16M
#查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖
query_cache_limit = 1M
#查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值
query_cache_size = 16M
#给所有的查询做cache,代表使用缓冲
query_cache_type = 1
#设置以顺序扫描的方式扫描表数据的时候使用缓冲区的大小
read_buffer_size = 8M
#打开文件数限制
open_files_limit = 10240
#修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
innodb_file_per_table = 1
#索引和数据缓冲区大小，一般设置物理内存的60%-70%
innodb_buffer_pool_size = 1G
#缓冲池实例个数，推荐设置4个或8个
innodb_buffer_pool_instances = 8
#2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高
innodb_flush_log_at_trx_commit = 2
#日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M
innodb_log_buffer_size = 8M
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中
back_log = 1024
#thread cache 池中存放的最大连接数
thread_cache_size = 64
#开启慢查询日志
slow_query_log = ON
#-------此处为MySQL复制参数配置--------------
#datadir = /mfg/mysql/data/  #数据文件目录,此处我采用的是默认配置
log-bin = master-bin  #二进制日志，后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下
log-bin-index = master-bin.index #index文件名称
innodb_file_per_table = 1  #可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
relay-log = relay-log   #中继日志, 后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下
relay-log-index = relay-log.index #relay-log index文件的名称
binlog_format = row  # 二进制日志文件格式
gtid_mode = ON   #开启GITD
enforce-gtid-consistency = ON  #强制GTID的一致性
master-info-repository=TABLE
relay-log-info-repository=TABLE  #此两项为打开从服务器崩溃二进制日志功能，信息记录在事物表而不是保存在文件
sync-master-info = 1   #值为1确保信息不会丢失
slave_parallel_workers = 4 #设定从服务器的SQL线程数；0表示关闭多线程复制功能
binlog-checksum = CRC32  #效验码
master-verify-checksum = 1 # 启动主服务器效验
slave-sql-verify-checksum = 1 # 启动从服务器效验
binlog-rows-query-log_events = 1     #用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；        
log-slave-updates=true  #slave更新是否记入日志
server_id = 128  #此处两台的ID必须不能相同！

3：添加防火墙策略，允许vrrp协议和mysql端口。

 vim /etc/sysconfig/iptables
 
 
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -i ens160 -p vrrp -s 192.168.180.187 -j ACCEPT
#ens160代表的是网卡名称，实验环境关闭selinux和防火墙。

4：创建同步账号，并授权。

#在master A上：
mysql> grant replication slave on *.* to 'sync'@'192.168.116.128' identified by 'synC123.';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
#在masterB 上：
mysql> grant replication slave on *.* to 'sync'@'192.168.116.129' identified by 'synC123.';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

5：建立主从关系

#在Master A 上：
mysql> change master to master_host='192.168.116.129', master_user='sync',master_password='synC123.', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#在master B上：
mysql> change master to master_host='192.168.116.128', master_user='sync',master_password='synC123.', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
#此时在A和B上分别用
mysql> show slave status\G;来验证主从关系的建立是否正确。
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.116.129
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1052
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 914
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1052
              Relay_Log_Space: 1115
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 129
                  Master_UUID: 683b5a5f-75bb-11e7-bc1a-000c29120a74
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 683b5a5f-75bb-11e7-bc1a-000c29120a74:1-2
            Executed_Gtid_Set: 051563bb-75bb-11e7-b997-000c29b156e5:1-2,
683b5a5f-75bb-11e7-bc1a-000c29120a74:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

6：定义MySQL检测脚本，主从都需要定义。

[root@node1 ~]# vim /root/keepalived_check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql        
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=123456
CHECK_TIME=4
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
    if [ $? = 0 ] ;then
    MYSQL_OK=1
    else
    MYSQL_OK=0
    fi
    return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
    systemctl stop keepalived.service
    exit 1
fi
sleep 1
done
#赋予执行权限
[root@node1 ~]# chmod +x /root/keepalived_check_mysql.sh

7：安装keepalived,并修改配置文件。

PS:这里设置的是非抢占模式，因为MySQL主主复制，互为备份，当一台down掉以后，vip漂移到另外一台，此时数据将会写入另外一台服务器，而当我将down掉的服务器恢复以后，他必须要向当前正在工作的master复制数据，如果此时抢占为主，可能数据还没有同步完成，而此时写入数据必然会造成数据不一致错误。如果在这个时候再进行恢复，是相当困难的。

#安装：
yum install keepalived
#修改配置文件，
vim /etc/keepalived/keepalived.conf
#配置文件如下：
! Configuration File for keepalived
global_defs {
   smtp_connect_timeout 3
   smtp_server 127.0.0.1
   router_id mysqlnode1
}
vrrp_script check_run {
   script "/root/keepalived_check_mysql.sh"
   interval 3
}
vrrp_sync_group VM1 {
    group {
          VM_1
    }
}
vrrp_instance VM_1 {
    state BACKUP
    nopreempt
    interface ens33
    virtual_router_id 180
    priority 99
    advert_int 1 
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    track_script {
        check_run
    }
    virtual_ipaddress {
        192.168.116.130
    }
}
PS:这里面router_id 和 priority 99必须不一致，主的权重应该大于从的， router_id可以自己定义。此时设置为不抢占，两台服务器的state 必须设置为BACKUP。

#启动keepalived先启动Master再启动slave。
systemctl start keepalived
#加入到开机启动项
systemctl enable keepalived
#此时可以使用ip add sh 查看虚拟IP是否在master上，然后通过停止MySQL以及keepalived和关机，来验证两台服务器的高可用性，此时前端所有的连接都需要执行VIP。

8：mysql 删除主从信息

1:stop slave;
2:reset slave;
3:change master to master_host='  ';
同理后面的选项在两个单引号之间也需要空格，来删除主从信息！

9：以前版本的安装脚本和文档

1：更改mysql配置文件
[mysqld]
datadir = /mfg/mysql/data/  #数据文件目录
log-bin = master-bin  #二进制日志，后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下
log-bin-index = master-bin.index #index文件名称
innodb_file_per_table = 1  #可以修改InnoDB为独立表空间模式，每个数据库的每个表都会生成一个数据空间
relay-log = relay-log   #中继日志, 后面指定存放位置。如果只是指定名字，默认存放在/var/lib/mysql下
relay-log-index = relay-log.index #relay-log index文件的名称
binlog_format = row  # 二进制日志文件格式
gtid_mode = ON   #开启GITD
enforce-gtid-consistency = ON  #强制GTID的一致性
master-info-repository=TABLE
relay-log-info-repository=TABLE  #此两项为打开从服务器崩溃二进制日志功能，信息记录在事物表而不是保存在文件
sync-master-info = 1   #值为1确保信息不会丢失
slave_parallel_workers = 4 #设定从服务器的SQL线程数；0表示关闭多线程复制功能
binlog-checksum = CRC32  #效验码
master-verify-checksum = 1 # 启动主服务器效验
slave-sql-verify-checksum = 1 # 启动从服务器效验
binlog-rows-query-log_events = 1     #用于在二进制日志详细记录事件相关的信息，可降低故障排除的复杂度；        
log-slave-updates=true  #slave更新是否记入日志
server_id = 86  #此处两台的ID必须不能相同！
2：添加防火墙策略，允许vrrp协议和mysql端口。
 vi /etc/sysconfig/iptables
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
-A INPUT -i ens160 -p vrrp -s 192.168.180.187 -j ACCEPT
3:添加权限
   3.1 ：grant replication slave on *.* to 'sync'@'192.168.180.186' identified by 'sync';
            grant replication slave on *.* to 'sync'@'192.168.180.187' identified by 'sync';
             change master to master_host='192.168.180.187',master_user='sync',master_password='sync',master_log_file='master-bin.000001',master_log_pos=411;
change master to master_host='192.168.1.20', master_user='repluser',master_password='replpass', master_auto_position=1;(GTID用此条命令即可)
 change master to master_host='192.168.180.186',master_user='sync',master_password='sync',master_log_file='master-bin.000001',master_log_pos=411;
分别开启：start slave;
show slave \G;
注释：log_file和log_pos通过show master status\G查看。
4：安装keepalived，写mysql检查脚本
mysql 检测脚本  vi /root/keepalived_check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql        
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=123456
CHECK_TIME=4
#mysql  is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
    $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
    if [ $? = 0 ] ;then
    MYSQL_OK=1
    else
    MYSQL_OK=0
    fi
    return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
    let "CHECK_TIME -= 1"
    check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
    CHECK_TIME=0
    exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&  [ $CHECK_TIME -eq 0 ]
then
    systemctl stop keepalived.service
    exit 1
fi
sleep 1
done
yum -y install keepalived 
5:修改keepalived配置文件
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   smtp_connect_timeout 3
   smtp_server 127.0.0.1
   router_id dhsmysql(主从不一样)
}
vrrp_script check_run {
   script "/root/keepalived_check_mysql.sh"
   interval 3
}
vrrp_sync_group VM1 {
    group {
          VM_1
    }
}
vrrp_instance VM_1 {
    state MASTER
    interface ens160（网络接口名称）
    virtual_router_id 180（虚拟ID必须一样）
    priority 99（主的权重一定要大于从）
    advert_int 1 
    authentication {
        auth_type PASS
        auth_pass mfg@123
    }
    track_script {
        check_run
    }
    virtual_ipaddress {
        192.168.180.185
    }
}