李广良

1. 网络规划

198.168.10.71（主）

198.168.10.72（主）

198.168.10.73（从，延迟复制71）

keepalived部署于双主节点

2. 前置工作

  • 确认系统版本
1
2
[root@node71 ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
1
2
3
4
Product Version:5.7.41
Operating System: Red Hat Enterprise Linux / Oracle Linux 
OS Version: Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit)  
RPM Bundle: Dec 9, 2022	531.5M	(mysql-5.7.41-1.el7.x86_64.rpm-bundle.tar)

  • 解压

    上传文件到任意目录，然后解压

1
tar -xvf mysql-5.7.41-1.el7.x86_64.rpm-bundle.tar

3. 单机安装

以下两台主机都需安装，按普通单机模式先完成。

  • RPM安装
1
2
3
rpm  -ivh  *.rpm --nodeps --force
warning: mysql-community-client-5.7.41-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Si
Preparing...                          ################################# [100%]
  • 确认安装位置
1
2
[root@node71 ~]#  whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
  • 初始化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@node71 ~]# mysqld --initialize --console
[root@node71 ~]# chown -R mysql:mysql /var/lib/mysql/
#启动数据库
[root@node71 ~]# systemctl start mysqld
[root@node71 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor prese      t: disabled)
   Active: active (running) since Sun 2023-05-28 00:59:45 PDT; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 11671 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysq      ld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 11639 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0      /SUCCESS)
 Main PID: 11674 (mysqld)
    Tasks: 27
   CGroup: /system.slice/mysqld.service
           └─11674 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/my...
  • 初始密码
1
2
[root@node71 ~]# cat /var/log/mysqld.log |grep tempo
2023-05-28T07:59:25.164850Z 1 [Note] A temporary password is generated for root@localhost: +<pWgX3)GmP>
  • 修改root密码
1
2
3
[root@node72 ~]# mysql -u root -p
mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'dahouzi';
  • 修改root访问权限
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#更改为任意主机可访问
mysql> UPDATE user SET host = "%" WHERE user='root';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT host, user, authentication_string, plugin FROM user;
+-----------+---------------+-------------------------------------------+-----------------------+
| host      | user          | authentication_string                     | plugin                |
+-----------+---------------+-------------------------------------------+-----------------------+
| %         | root          | *1FB37DBB9243517E6128CA92A0FBE13C066456CF | mysql_native_password |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+-----------+---------------+-------------------------------------------+-----------------------+

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

4. 双主配置

两台主机均需如下操作：

  • 查询master_log_file和Position位置
1
2
3
4
5
6
7
8
9
[root@node71 ~]# mysql -uroot -p

mysql> show master status;
+------------------+----------+--------------+-------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                          | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------+-------------------+
| mysql-bin.000001 |      868 |              | performance_schema,information_schema,sys |                   |
+------------------+----------+--------------+-------------------------------------------+-------------------+
1 row in set (0.00 sec)
  • 修改配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
vi /etc/my.cnf

log_bin=mysql-bin
server-id=73
sync-binlog=1
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys


relay_log=mysql-relay-bin
log_slave_updates=1

auto_increment_offset=1
auto_increment_increment=2
  • 授权
1
2
3
4
5
6
7
8
mysql> grant replication slave on *.* to 'root'@'%' identified by 'dahouzi';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by 'dahouzi';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
  • 主从关系（此步两台主机有所不同）
1
2
3
4
5
#192.168.10.72主机执行
change master to master_host='192.168.10.71',master_port=3306,master_user='root',master_password='dahouzi',master_log_file='mysql-bin.000001',master_log_pos=2438;

#192.168.10.71主机执行
change master to master_host='192.168.10.72',master_port=3306,master_user='root',master_password='***',master_log_file='mysql-bin.000001',master_log_pos=868;
  • 确认主从关系
    Slave_IO_Running: Yes和Slave_SQL_Running: Yes
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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.10.72
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 868
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

5. MHA高可用

1
2
3
4
5
ssh-keygen -t rsa
cd ~/.ssh
ssh-copy-id node71
ssh-copy-id node72
ssh-copy-id node73

keepalive
/etc/keepalived/keepalived.conf

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
! Configuration File for keepalived

global_defs {
   router_id master71
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.77
    }
}

virtual_server 192.168.10.77 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    net_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.10.71 3306 {
        weight 1
        notify_down /etc/keepalived/closekeepalived.sh
        TCP_CHECK {               # 健康检查
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306     # 设置连接端口3306
        }
    }
}

关闭keepalived

1
2
3
4
5
6
/etc/keepalived/closekeepalived.sh

#closekeepalived.sh内容
sudo -S killall keepalived

chmod +x /etc/keepalived/closekeepalived.sh

6. 测试

原理：keepalived监控自己节点的mysql是否正常，如果检测mysql已关闭，则关闭自己告诉对方接管虚拟IP。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[root@node71 mha]# service keepalived restart
Redirecting to /bin/systemctl restart keepalived.service
[root@node71 mha]# ps -ef | grep keepalived
root      27246      1  0 00:04 ?        00:00:00 /usr/sbin/keepalived -D
root      27247  27246  0 00:04 ?        00:00:00 /usr/sbin/keepalived -D
root      27248  27246  0 00:04 ?        00:00:00 /usr/sbin/keepalived -D
root      27255  23305  0 00:04 pts/0    00:00:00 grep --color=auto keepalived
[root@node71 mha]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:fd:2c:2f brd ff:ff:ff:ff:ff:ff
    inet 192.168.10.71/24 brd 192.168.10.255 scope global noprefixroute ens32
       valid_lft forever preferred_lft forever
    inet 192.168.10.77/32 scope global ens32
       valid_lft forever preferred_lft forever
    inet6 fe80::9c86:aaa1:7ab7:4315/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
    link/ether 52:54:00:6b:1e:21 brd ff:ff:ff:ff:ff:ff
    inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
       valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
    link/ether 52:54:00:6b:1e:21 brd ff:ff:ff:ff:ff:ff


7. 维护操作

1
2
3
4
5
[root@node71 ~]# systemctl restart mysqld
[root@node71 ~]# systemctl status mysqld
[root@node71 ~]# systemctl stop mysqld
[root@node71 ~]# systemctl restart keepalived.service
[root@node71 ~]# systemctl stop keepalived.service

8. 日常运维（主机重启）

  1. 因数据库不常重启，主机重启后的操作，需校对数据库一致性
  2. 每隔24小时，也需校对一次关键数据在主从服务器一致性，在资源消耗低谷时做
空空
2023-09-01
EXPAND TOP BOTTOM