mysql双主配置

[TOC]

环境准备

系统：centos7
软件版本：mysql-5.7.23-linux-glibc2.12-x86_64
安装目录：/usr/local/mysql
数据库目录：/data/mysql
数据库root密码: hc123456
A数据库：10.0.0.11
B数据库：10.0.0.12
VIP：10.0.0.100 

1.两个数据库版本最好一致
2.两个数据库内数据保持一致，若不一致，可手动调整，比如A比B多一个库，那将这个库导入到B库，达到一致。

数据库同步账号: sync
数据库sync密码: pcpaeyphmp

说明：数据库通过配置相互做对端的slave实现双主,使用heartbeat提供VIP,haproxy(负载均衡)将两台主机的端口转发给VIP
如VIP所在主机故障,VIP漂移到另一台主机继续提供服务

数据库安装

两端都执行查看安装文档

curl http://home.onlycloud.xin/code/mysql-bin-install.sh -o mysql-install.sh
sh mysql-install.sh
source /etc/profile

hosts解析

cat <<EOF  >>/etc/hosts
10.0.0.11   nfs-01
10.0.0.12   nfs-02
EOF

修改主机名

A数据库

hostnamectl set-hostname nfs-01

---

B数据库

hostnamectl set-hostname nfs-02

---

A数据库操作

1.开启binlog(数据库二进制日志),设置server-id,重启mysql服务

cp /etc/my.cnf{,.`date +%F`}
systemctl stop mysqld
sed -i 's/server-id.*/server-id = 1/g' /etc/my.cnf
sed -i '24a log-bin = mysql-bin' /etc/my.cnf
sed -i '25a binlog_format = mixed' /etc/my.cnf
sed -i '26a log-bin-index = master-bin.index' /etc/my.cnf
sed -i '27a relay-log = relay-bin' /etc/my.cnf
sed -i '28a relay-log-index = slave-relay-bin.index' /etc/my.cnf
sed -i '29a auto-increment-offset = 1' /etc/my.cnf
sed -i "30a auto-increment-increment = 2\n" /etc/my.cnf
systemctl start mysqld

2.创建同步数据的用户,授权允许10.0.0.0/24网络使用sync用户登录

source /etc/profile
mysql -uroot -phc123456 
create user sync;
grant replication slave on *.* to 'sync'@'10.0.0.%' identified by 'pcpaeyphmp';
flush privileges;
show master status;
exit

# MySQL [(none)]> show master status;
# 记下 binlog文件的position（偏移）和File（日志文件）的值)
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      794 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

B数据库操作

1.配置从库只允许来自服务器线程或具有SUPER权限的数据库用户进行更新(root,sync),开启binlog(数据库二进制日志),设置server-id,重启mysql服务

cp /etc/my.cnf{,.`date +%F`}
systemctl stop mysqld
sed -i 's/server-id.*/server-id = 2/g' /etc/my.cnf
sed -i '24a log-bin = mysql-bin' /etc/my.cnf
sed -i '25a binlog_format = mixed' /etc/my.cnf
sed -i '26a log-bin-index = master-bin.index' /etc/my.cnf
sed -i '27a relay-log = relay-bin' /etc/my.cnf
sed -i '28a relay-log-index = slave-relay-bin.index' /etc/my.cnf
sed -i '29a auto-increment-offset = 2' /etc/my.cnf
sed -i "30a auto-increment-increment = 2\n" /etc/my.cnf
systemctl start mysqld

2.连接数据库A,需要A数据库主机名，登陆凭据，二进制文件的名称和位置

source /etc/profile
mysql -uroot -phc123456
change master to master_host='10.0.0.11',  # A数据库 IP或主机名(需解析通)
master_port=3306,                          # A数据库 端口号
master_user='sync',                        # 同步账号
master_password='pcpaeyphmp',              # 同步账号的密码
master_log_file='mysql-bin.000003',        # A数据库执行 show master status; 获取的 File 值
master_log_pos=794;                        # A数据库执行 show master status; 获取的 Position 值
start slave;                               # 启动slave
show slave status\G;                       # 查看同步状态
exit

# MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.11
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 794
               Relay_Log_File: nfs-02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           ............................

# 保证以下两项状态为 Yes 则同步成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3.创建同步数据的用户,授权允许10.0.0.0/24网络使用sync用户登录

mysql -uroot -phc123456
create user sync;
grant replication slave on *.* to 'sync'@'10.0.0.%' identified by 'pcpaeyphmp';
flush privileges;
show master status;
exit

# MySQL [(none)]> show master status;
# 记下 binlog文件的position（偏移）和File（日志文件）的值)
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      794 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

A数据库操作

连接数据库B,需要B服务器主机名，登陆凭据，二进制文件的名称和位置

source /etc/profile
mysql -uroot -phc123456
change master to master_host='10.0.0.12',  # B数据库 IP或主机名(需解析通)
master_port=3306,                          # B数据库 端口号
master_user='sync',                        # B同步账号
master_password='pcpaeyphmp',              # 同步账号的密码
master_log_file='mysql-bin.000003',        # B数据库执行 show master status; 获取的 File 值
master_log_pos=794;                        # B数据库执行 show master status; 获取的 Position 值
start slave;                               # 启动slave
show slave status\G;                       # 查看同步状态
exit

# MySQL [(none)]> show slave status\G;
************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.12
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 794
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           ............................

# 保证以下两项状态为 Yes 则同步成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

验证mysql

A创建数据库

create database test_01;
show databases;

# MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_01            |
+--------------------+

B查看数据库

show databases;

# MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_01            |
+--------------------+

B创建数据库

create database test_02;
show databases;

# MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_01            |
| test_02            |
+--------------------+

A查看数据库

show databases;

# MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_01            |
| test_02            |
+--------------------+

mysql排错

Slave_IO_Running:     # 状态不为Yes

1.数据库A与数据库B之间网络是否通畅
2.同步账号的用户权限,及是否允许数据库B登录
3.查看两端server-id(不能相同) 使用mysql命令查看 show variables like 'server_id'; 此配置项在my.cnf

安装haproxy

两端都安装配置相同

yum -y install haproxy

配置haproxy

cp /etc/haproxy/haproxy.cfg{,.bak}
cat <<! >/etc/haproxy/haproxy.cfg
############ 全局配置 ############
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
daemon
nbproc 1       # 进程数量
maxconn 4096   # 最大连接数
user haproxy   # 运行用户
group haproxy  # 运行组
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid

############ 默认配置 ############
defaults
log global
mode http            # 默认模式{ tcp|http|health }
option httplog       # 日志类别,采用httplog
option dontlognull   # 不记录健康检查日志信息
retries 2            # 2次连接失败不可用
option forwardfor    # 后端服务获得真实ip
option httpclose     # 请求完毕后主动关闭http通道
option abortonclose  # 服务器负载很高，自动结束比较久的链接
maxconn 4096         # 最大连接数
timeout connect 5m   # 连接超时
timeout client 1m    # 客户端超时
timeout server 31m   # 服务器超时
timeout check 10s    # 心跳检测超时
balance roundrobin   # 负载均衡方式，轮询

########## 统计页面配置 ##########
listen stats
  bind 0.0.0.0:1080
  mode http
  option httplog
  log 127.0.0.1 local0 err
  maxconn 10               # 最大连接数
  stats refresh 30s
  stats uri /admin         #状态页面 http//ip:1080/admin 访问
  stats realm Haproxy\ Statistics
  stats auth admin:admin   # 用户和密码:admin
  stats hide-version       # 隐藏版本信息
  stats admin if TRUE      # 设置手工启动/禁用

listen HAPROXY-STATUS
  bind 10.0.0.100:88
  mode tcp
  option  tcplog
  balance leastconn        # 根据服务器当前的请求数
  server nfs-01 nfs-01:1080 check port 1080 inter 2000 rise 2 fall 5
  server nfs-02 nfs-02:1080 check port 1080 inter 2000 rise 2 fall 5

listen MYSQL-HA
  mode tcp
  option  tcplog
  bind 10.0.0.100:3308
  balance leastconn
  option mysql-check user haproxy
  server nfs-01 nfs-01:3306 weight 1 check inter 2000 rise 2 fall 5
  server nfs-02 nfs-02:3306 weight 1 check inter 2000 rise 2 fall 5

listen HTTP-HA
  bind 10.0.0.100:80
  balance  roundrobin
  option  tcpka
  option  httpchk
  option  tcplog
  server nfs-01 nfs-01:8080 check port 8080 inter 2000 rise 2 fall 5
  server nfs-02 nfs-02:8080 check port 8080 inter 2000 rise 2 fall 5
!

创建监控用户

监控mysql运行状态此用户无需配置密码及权限

mysql -u root -phc123456 -e "create user 'haproxy'@'%';flush privileges;"

---

配置日志

cat <<'!'  >/etc/rsyslog.d/haproxy.conf
$ModLoad imudp
$UDPServerRun 514
$template Haproxy,"%rawmsg% \n"
local0.=info -/var/log/haproxy.log;Haproxy
local0.notice -/var/log/haproxy-status.log;Haproxy
!

# 重启日志服务
systemctl restart rsyslog

设置启动方式

# 允许没VIP时启动(如果没有配置此项且应用会导致 haproxy 服务无法启动)
echo "net.ipv4.ip_nonlocal_bind = 1" >>/etc/sysctl.conf
sysctl -p

服务管理

systemctl start haproxy     # 启动服务
systemctl enable haproxy    # 随系统启动
systemctl status haproxy    # 运行状态

---

安装heartbeat

配置依赖软件安装源

cat <<EOF   >/etc/yum.repos.d/gf-epel-7.repo
[epel-testing]
name=Extra Packages for Enterprise Linux 7 - $basearch - Testing
baseurl=http://mirror.ghettoforge.org/distributions/gf/el/7/testing/x86_64
enabled=1
gpgcheck=0
EOF
yum makecache

下载软件包并安装 heartbeat

heartbeat='heartbeat-3.0.6-1.el7.centos.x86_64.rpm'
heartbeat_libs='heartbeat-libs-3.0.6-1.el7.centos.x86_64.rpm' 
curl -o $heartbeat http://home.onlycloud.xin/soft/heartbeat/$heartbeat --progress
curl -o $heartbeat_libs http://home.onlycloud.xin/soft/heartbeat/$heartbeat_libs --progress
yum localinstall -y heartbeat-*

配置heartbeat

创建ha.cf配置文件

cat <<EOF   >/etc/ha.d/ha.cf
# 调试日志
debugfile /var/log/ha-debug

# heartbeat 运行日志
logfile /var/log/ha-log

# 在 syslog 服务中配置通过 local1 设备接收日志
logfacility local0

# 心跳间隔时间 default:2
keepalive 1

# 备用节点在 default:30 秒内没有收到主节点心跳信号则立即接管主节点服务资源
deadtime 5

# 心跳延迟时间为 default:10 秒,当 10 秒内备份节点未收到主节点心跳信号时,将写入警告日志,此时不会切换服务
warntime 3

# heartbeat 服务首次启动,需要等待 default:60 秒后才启动服务器的资源,该值至少为 deadtime 两倍(单机启动时vip绑定比较慢)
initdead 10

# 指定心跳信号网卡(直连网卡)
#bcast ens33

# 配置多播通信路径(直连网卡)
mcast ens33 239.0.0.1 694 1 0

# 主节点恢复后,是否将服务自动切回
auto_failback on

# 节点主机名(使用IP地址也可以)
node nfs-01
node nfs-02

# 是否开启 CRM 集群管理
crm no
EOF

两端认证密码配置 authkeys

cat <<EOF  >>/etc/ha.d/authkeys
auth 1
1 sha1 cxix9lkzq2aevnxv43yn68yoh2y7zp2cfwufgs7w
EOF
chmod 600 /etc/ha.d/authkeys

配置heartbeat资源

cat <<EOF   >/etc/ha.d/haresources
nfs-01 IPaddr::10.0.0.100/24/ens33
EOF

启动服务,验证(两个节点各自执行 ip add 能看到两个 10.0.0网段ip)

systemctl start heartbeat
netstat -ntaulp
ip add | grep 10.0.0

验证heartbeat

浏览器打开以下链接查看资源状态(账号：admin/admin)

http://10.0.0.11:1080/admin
http://10.0.0.12:1080/admin
http://10.0.0.100:1080/admin

---

关闭存在VIP主机的heartbeat服务,然后在另一端验证(在正常的一端已经接管了所有虚拟IP ip add 能看到三个 10.0.0网段ip)登录数据库验证

systemctl stop heartbeat
ip add | grep 10.0.0
mysql -h 10.0.0.100 -P 3308 -uroot -phc123456

---

启动关闭的服务后验证 虚拟IP已经恢复

systemctl start heartbeat
ip add | grep 10.0.0
mysql -h 10.0.0.100 -P 3308 -uroot -phc123456

---

查看同步状态

# 可以看到Master_Host IP一直在切换负载均衡配置成功
mysql -h 10.0.0.100 -P 3308 -uroot -phc123456 -e 'show slave status\G'

---

同步故障恢复

修复A主B从同步

A数据库操作

A数据库锁表查看binlog文件的position（偏移）和File（日志文件）的值

mysql -uroot -phc123456
flush tables with read lock;    # 锁住数据库表防止写入
show master status;    # 记下 binlog文件的position（偏移）和File（日志文件）的值)

# MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

---

B数据库操作

B数据库重新连接A

mysql -uroot -phc123456
stop slave;
reset slave;
change master to master_host='10.0.0.11',  # A数据库 IP或主机名(需解析通)
master_port=3306,                          # A数据库端口号
master_user='sync',                        # 同步账号
master_password='pcpaeyphmp',              # 同步账号的密码
master_log_file='mysql-bin.000007',        # A数据库执行 show master status; 获取的 File 值
master_log_pos=154;                        # A数据库执行 show master status; 获取的 Position 值
start slave;                               # 启动slave
show slave status\G;                       # 查看同步状态
show slave status\G;

# MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.11
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ..............................

A数据库操作

unlock tables;    # 解锁数据库

---

修复B主A从同步

B数据库操作

B数据库锁表查看binlog文件的position（偏移）和File（日志文件）的值

mysql -uroot -phc123456
flush tables with read lock;    # 锁住数据库表防止写入
show master status;    # 记下 binlog文件的position（偏移）和File（日志文件）的值)

# MySQL [(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

---

A数据库操作

A数据库重新连接B

mysql -uroot -phc123456
stop slave;
reset slave;
change master to master_host='10.0.0.12',  # BA数据库 IP或主机名(需解析通)
master_port=3306,                          # B数据库端口号
master_user='sync',                        # 同步账号
master_password='pcpaeyphmp',              # 同步账号的密码
master_log_file='mysql-bin.000007',        # B数据库执行 show master status; 获取的 File 值
master_log_pos=154;                        # B数据库执行 show master status; 获取的 Position 值
start slave;                               # 启动slave
show slave status\G;                       # 查看同步状态
show slave status\G;

# MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.12
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-bin.000007
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
          ..................................

B数据库操作

unlock tables;    # 解锁数据库