项目环境
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
网友评论