MySQL两主一从部署（keepalived）

最新推荐文章于 2025-04-16 16:23:09 发布

空空dream

最新推荐文章于 2025-04-16 16:23:09 发布

阅读量742

点赞数

分类专栏： # MySQL 文章标签： mysql 数据库 keepalived 高可靠

版权源自：dahouzi.cn

本文链接：https://blog.youkuaiyun.com/lglglgl/article/details/131287956

版权

MySQL 专栏收录该内容

7 篇文章

订阅专栏

操作系统：Centos 7.9
数据库：MySQL 5.7.41

1.组网规划

198.168.10.71（主）
198.168.10.72（主）
198.168.10.73（从，复制71）
keepalived部署于双主节点

2.前置工作

确认系统版本

  [root@node71 ~]# cat /etc/redhat-release
  CentOS Linux release 7.9.2009 (Core)

下载rpm安装包

MySQL :: Download MySQL Community Server (Archived Versions)

  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)

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

3.单机安装

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

RPM安装

rpm  -ivh  *.rpm --nodeps --force
warning: mysql-community-client-5.7.41-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Si
Preparing...                          ################################# [100%]

确认安装位置

[root@node71 ~]#  whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

初始化

[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...

初始密码

[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密码

[root@node72 ~]# mysql -u root -p
mysql> use mysql;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'dahouzi';

修改root访问权限

#更改为任意主机可访问
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位置

[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)

修改配置文件

  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

授权

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

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

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

主从关系（此步两台主机有所不同）

#192.168.10.72主机执行
change master to master_host='192.168.10.71',master_port=3306,master_user='root',master_password='idszt',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

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高可用

# 此处在host已配置映射
ssh-keygen -t rsa
cd ~/.ssh
ssh-copy-id node71
ssh-copy-id node72
ssh-copy-id node73

5.1.keepalive配置

! 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
        }
    }
}

5.2.关闭keepalived

/etc/keepalived/closekeepalived.sh

#closekeepalived.sh内容
sudo -S killall keepalived

chmod +x /etc/keepalived/closekeepalived.sh

5.3.测试

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

[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

6.维护操作

[root@node71 ~]# systemctl restart mysqld
[root@node71 ~]# systemctl status mysqld
[root@node71 ~]# systemctl stop mysqld
[root@node71 ~]# systemctl restart keepalived.service