MySQL主主同步+keepalived

项目环境

VIP    192.168.2.200
mysql1 192.168.2.33
mysql2 192.168.2.34

mysql主主同步

两台机器都安装MySQL5.7,我是编译安装的

配置MySQL

第一台

配置文件

[root@mysql-keepalived-master ~]# cat /etc/my.cnf
[mysqld]
server_id=1
user=mysql
port=3306
basedir=/opt/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log_error=/var/log/mysql.log
slow_query_log = ON
slow_query_log_file = /opt/mysql/logs/slow.log
long_query_time = 1
max_connections = 2000
open_files_limit = 65535
log_bin = /opt/mysql/logs/mysql-bin
expire_logs_days = 7
thread_cache_size = 64
innodb_buffer_pool_size = 2048MB
innodb_thread_concurrency = 8
innodb_write_io_threads = 12
innodb_read_io_threads = 12
log-slave-updates=true
max_binlog_size=1024M
auto_increment_offset=1    # 设定数据库中自动增长的起点，两台mysql的起点必须不同，这样才能避免两台服务器同步时出现主键冲突
auto_increment_increment=2  #步进值auto_imcrement。一般有n台主MySQL就填n


[client]
socket=/tmp/mysql.sock
#prompt="\\lidapan-[\\d]>

相互授权

[root@mysql-keepalived-master ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 144
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'repl'@'192.168.2.34' identified by '123123';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      245 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

第二台

配置文件

[root@mysql-keepalived-slave ~]# cat /etc/my.cnf
[mysqld]
server_id=2
user=mysql
port=3306
basedir=/opt/mysql
datadir=/data/mysql
socket=/tmp/mysql.sock
log_error=/var/log/mysql.log
slow_query_log = ON
slow_query_log_file = /opt/mysql/logs/slow.log
long_query_time = 1
max_connections = 2000
open_files_limit = 65535
log_bin = /opt/mysql/logs/mysql-bin
expire_logs_days = 7
thread_cache_size = 64
innodb_buffer_pool_size = 2048MB
innodb_thread_concurrency = 8
innodb_write_io_threads = 12
innodb_read_io_threads = 12
log-slave-updates=true
max_binlog_size=1024M
auto_increment_offset=2
auto_increment_increment=2


[client]
socket=/tmp/mysql.sock
#prompt="\\lidapan-[\\d]>

相互授权

[root@mysql-keepalived-slave ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 144
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'repl'@'192.168.2.33' identified by '123123';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    486  |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置两台从服务器

第一台

mysql> change master to
    -> master_host='192.168.2.34',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='123123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=486;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.34
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 486
               Relay_Log_File: database-relay-bin.000005
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

第二台

mysql> change master to
    -> master_host='192.168.2.33',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='123123',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=245;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.2.33
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 245
               Relay_Log_File: database-relay-bin.000005
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

安装配置keeplived

安装keepalived---两台机器都操作

[root@mysql-keepalived-master ~]# yum -y install ipvsadm kernel-headers kernel-devel openssl-devel popt-devel 
#### yum -y install krb5-devel openssl-libs zlib-devel   下载openssl-devel可能需要这些依赖
[root@mysql-keepalived-master ~]# yum install -y wget vim gcc 
[root@mysql-keepalived-master ~]# wget http://www.keepalived.org/software/keepalived- 2.1.5.tar.gz 
[root@mysql-keepalived-master ~]# tar xzf keepalived-2.1.5.tar.gz 
[root@mysql-keepalived-master ~]# cd keepalived-2.1.5 
[root@mysql-keepalived-slave keepalived-2.1.5]# ./configure --prefix=/ 
[root@mysql-keepalived-master keepalived-2.1.5]# make && make install

keepalived 主备配置文件

192.168.2.33 master配置

[root@mysql-keepalived-master ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived 
global_defs { 
   router_id directory1 
}
vrrp_script check_run {
   script "/opt/keepalived/keepalived_chech_mysql.sh" 
   interval 5 
}
vrrp_instance VI_1 { 
    state MASTER 
    interface eth0
    virtual_router_id 89 
    priority 100 
    advert_int 1 
    authentication { 
       auth_type PASS 
       auth_pass 1111 
    }
    virtual_ipaddress { 
    192.168.2.200/24 
    }
    track_script { 
        check_run 
    }
 }

192.168.2.34 slave配置

[root@mysql-keepalived-slave ~]# cat /etc/keepalived/keepalived.conf 
! Configuration File for keepalived 
global_defs { 
   router_id directory2
}
vrrp_script check_run {
   script "/opt/keepalived/keepalived_chech_mysql.sh" 
   interval 5 
}
vrrp_instance VI_1 { 
    state MASTER 
    interface eth0
    virtual_router_id 89 
    priority  50
    advert_int 1 
    authentication { 
       auth_type PASS 
       auth_pass 1111 
    }
    virtual_ipaddress { 
    192.168.2.200/24 
    }
    track_script { 
        check_run 
    }
 }

mysql状态检测脚本/root/keepalived_check_mysql.sh（两台MySQL同样的脚本）

[root@mysql-keepalived-master ~]# cat /opt/keepalived/keepalived_chech_mysql.sh 
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    systemctl stop keepalived
fi
[root@mysql-keepalived-master ~]# chmod +x /opt/keepalived/keepalived_chech_mysql.sh

两边均启动keepalived

[root@mysql-keepalived-master ~]# systemctl start keepalived 
[root@mysql-keepalived-master ~]# systemctl enable keepalived

查看vip

vip在第一台
[root@mysql-keepalived-master ~]# ip a | grep inet
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
    inet 192.168.2.33/24 brd 192.168.2.255 scope global noprefixroute eth0
    inet 192.168.2.200/24 scope global secondary eth0
[root@mysql-keepalived-slave ~]# ip a | grep inet
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
    inet 192.168.2.34/24 brd 192.168.2.255 scope global noprefixroute eth0

测试

停掉mysql
[root@mysql-keepalived-master ~]#systemctl stop mysqld
vip飘到了第二台
[root@mysql-keepalived-slave ~]# ip a | grep inet
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
    inet 192.168.2.34/24 brd 192.168.2.255 scope global noprefixroute eth0
    inet 192.168.2.200/24 scope global secondary eth0
[root@mysql-keepalived-master ~]# ip a | grep inet
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
    inet 192.168.2.33/24 brd 192.168.2.255 scope global noprefixroute eth0