mysql主从配置

mysql8.0安装参考 https://www.jianshu.com/p/b3e7b27d0b00

主库配置修改并重启

vi /etc/my.cnf

server-id=1
log-bin=mysql-bin
relay-log=mysql-relay-bin

配置主库

# 配置slave同步账号
mysql> CREATE USER 'slave_repl'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_repl'@'%';
mysql> flush privileges;

# 持有读锁，避免数据写入
mysql> flush tables with read lock;

# 查看master状态
mysql> show master status
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2655 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

导出主库数据

# --source-data=1 导出master bin-log文件名和偏移量
mysqldump  -uroot -p --source-data=1 --all-databases > all.sql

从库配置修改并重启

server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin

配置从库

# 修改master信息， 这里可以不用配置 MASTER_LOG_FILE, MASTER_LOG_POS 因为导出的主库sql包含了该配置
mysql> CHANGE MASTER TO MASTER_HOST='10.20.10.1', MASTER_USER='slave_repl', MASTER_PASSWORD='123456', GET_MASTER_PUBLIC_KEY=1;

从库导入主库数据

mysql -uroot -p < all.sql

启动从库slave

# 启动slave
mysql> start slave;

# 查看slave状态是否正常
# Slave_IO_Running 和 Slave_SQL_Running 状态必须是 Yes，否则根据Error信息修复
mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.20.10.1
                  Master_User: slave_repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 2655
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....

反向主从配置

查看从库master status

mysql> show master status
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |  2522189 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置主库master信息

# 配置10.1主库信息, 注意修改MASTER_HOST，MASTER_LOG_FILE，MASTER_LOG_POS为从库master status
mysql> CHANGE MASTER TO MASTER_HOST='10.20.10.2', MASTER_USER='slave_repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2522189, GET_MASTER_PUBLIC_KEY=1;

# 启动slave
mysql> start slave;

# 查看slave状态
# Slave_IO_Running 和 Slave_SQL_Running 状态必须是 Yes
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.20.10.2
                  Master_User: slave_repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2522189
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

释放主库读锁

mysql> unlock tables;

半同步复制

主库从库安装插件

mysql> install plugin rpl_semi_sync_source soname 'semisync_source.so';
mysql> install plugin rpl_semi_sync_replica soname 'semisync_replica.so';

开启半同步复制

因为是双主，所以2台数据库设置操作是一样的
1.直接设置参数（临时)

mysql> set global rpl_semi_sync_source_enabled=1;
mysql> set global rpl_semi_sync_source_timeout=5000;
mysql> set global rpl_semi_sync_replica_enabled=1;

2.修改配置文件（永久）

vi /etc/my.cnf

[mysqld]
rpl_semi_sync_source_enabled=1
rpl_semi_sync_source_timeout=5000
rpl_semi_sync_replica_enabled=1

3.重启IO线程

mysql> stop replica io_thread;
mysql> stop replica io_thread;

4.查看半同步状态
Rpl_semi_sync_replica_status=on
Rpl_semi_sync_source_clients=1
Rpl_semi_sync_source_status=on

mysql> show global status like 'rpl%';


+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_replica_status               | ON    |
| Rpl_semi_sync_source_clients               | 1     |
| Rpl_semi_sync_source_net_avg_wait_time     | 0     |
| Rpl_semi_sync_source_net_wait_time         | 0     |
| Rpl_semi_sync_source_net_waits             | 0     |
| Rpl_semi_sync_source_no_times              | 0     |
| Rpl_semi_sync_source_no_tx                 | 0     |
| Rpl_semi_sync_source_status                | ON    |
| Rpl_semi_sync_source_timefunc_failures     | 0     |
| Rpl_semi_sync_source_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_source_tx_wait_time          | 0     |
| Rpl_semi_sync_source_tx_waits              | 0     |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_source_wait_sessions         | 0     |
| Rpl_semi_sync_source_yes_tx                | 0     |
+--------------------------------------------+-------

keepalived配置

主备安装keepalived

yum install -y keepalived

主配置keepalived

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived

vrrp_script check_mysql_alive {
  script "/etc/keepalived/check_mysql.sh"
  interval 3
  weight -20
}

global_defs {
  router_id LVS_MYSQL             # 运行keepalived机器的一个标识
}

vrrp_instance VI_1 {
  interface eth0                  # 设置实例绑定的网卡
  state MASTER                    # 指定哪个为master，哪个为backup
  virtual_router_id 123            # VPID标记，主备必须一样
  priority 180                    # 优先级，高优先级竞选为master
  vrrp_unicast_bind 10.20.10.1  # 主ip
  vrrp_unicast_peer 10.20.10.2  # 备ip
  authentication {
    auth_type PASS
    auth_pass shenhao
  }
  virtual_ipaddress {
    10.20.10.222                  # 设置VIP，必须是同一网段虚拟IP
  }

  track_script {
    check_mysql_alive             # 存活检查
  }
}

备keepalived配置

! Configuration File for keepalived

vrrp_script check_mysql_alive {
  script "/etc/keepalived/check_mysql.sh"
  interval 3
  weight -20
}


global_defs {
  router_id LVS_MYSQL             # 运行keepalived机器的一个标识
}

vrrp_instance VI_1 {
  interface eth0                  # 设置实例绑定的网卡
  state BACKUP                    # 指定哪个为master，哪个为backup
  virtual_router_id 123            # VPID标记，主备必须一样
  priority 170                    # 优先级，高优先级竞选为master
  vrrp_unicast_bind 10.20.10.1  # 备ip
  vrrp_unicast_peer 10.20.10.2  # 主ip
  authentication {
    auth_type PASS
    auth_pass shenhao
  }
  virtual_ipaddress {
    10.20.10.222                  # 设置VIP，必须是同一网段虚拟IP
  }

  track_script {
    check_mysql_alive             # 存活检查
  }
}

存活检查脚本

脚本非0退出，权重降级weight -20，实现主从切换

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${counter}" -eq 0 ]; then
    exit 1
fi

重启keepalived

校验

检查主节点VIP是否配置成功

ip -a  # 绑定网卡出现 10.20.10.222 地址

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast 
...
    inet 10.20.10.222/32 scope global eth0
       valid_lft forever preferred_lft forever

从应用节点查看是否能ping通，或者用arp命令查看VIP地址是否广播

arp

Address                  HWtype  HWaddress           Flags Mask            Iface
10.20.10.222         ether   0c:fa:41:1e:3b:0c   C                     eth0
10.20.10.1             ether   0c:fa:41:1e:3b:0c   C                     eth0
10.20.10.2             ether   0c:fa:41:1e:5b:11   C                     eth0

通过mysql client链接VIP，访问正常