数据库 Mariadb Keppalived HA 主主 双m

Mariadb+Keppalived互为主备

Posted by     "lxg" on Saturday, April 12, 2025

假设现在有两台服务器，需要搭建Mariadb高可用性集群，一台为主数据库（Master），另一台为从数据库（Slave）。服务器信息如下：

服务器ip虚拟VIP服务器主机名SSH账号SSH密码数据库管理员账号数据库管理员密码数据库同步账号数据库同步账号密码
192.168.31.91192.168.31.90mariadb-node1ubuntu0000rootlxg@123lxglxg@123
192.168.31.92192.168.31.90mariadb-node2ubuntu0000rootlxg@123lxglxg@123

第一步、安装Mariadb-Server

分别在mariadb-node1、mariadb-node2上安装Mariadb-Server

sudo apt-get install mariadb-server -y

分别修改mariadb-node1、mariadb-node2的Mariadb配置文件，修改bind-address和server-id的值。

sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

mariadb-node1配置文件需要修改以下内容：

bind-address            = 0.0.0.0  #该项表示允许连接服务端的客户端IP地址，0.0.0.0表示为所有
server-id              = 1         #注：主数据库和从数据库此数值不能相同
log_bin                = /var/lib/mysql/mysql-bin.log
expire_logs_days        = 10

mariadb-node2配置文件需要修改以下内容：

bind-address            = 0.0.0.0  #该项表示允许连接服务端的客户端IP地址，0.0.0.0表示为所有
server-id              = 2         #注：主数据库和从数据库此数值不能相同
log_bin                = /var/lib/mysql/mysql-bin.log
expire_logs_days        = 10

他们的不同点在于server-id的值不同，一个是1，另一个是2。

然后分别重启mariadb-node1、mariadb-node2的Mariadb服务

systemctl restart mariadb.service

配置主从复制

配置mariadb-node1为主数据库

在mariadb-node1、mariadb-node2上分别执行以下命令，修改root密码，以后root账号均使用此密码登录。

sudo mysqladmin -u root password 'lxg@123'

修改密码后在mariadb-node1、mariadb-node2上分别执行以下命令，用于初始化

sudo mysql_secure_installation

选择的参数如下

ubuntu@mariadb-node1:~$ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] n
 ... skipping.

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] n
 ... skipping.

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

分别在mariadb-node1、mariadb-node2上分别执行以下命令测试是否能连接数据库,密码是lxg@123

mysql -u root -p

创建用于同步的数据库账号

创建一个名为lxg的用户，允许此用户在所有主机上登录，并给lxg用户 赋予replication slave权限

create user 'lxg'@'%' identified by 'lxg@123';
grant replication slave on *.* to 'lxg'@'%' identified by 'lxg@123';

查看主Master角色的Binlog信息,在mariadb-node1上执行如下SQL

show master status;

记录 下 File和Position两个值，后面用到，这里两个值分别是mysql-bin.000001 和 987

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      987 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

在mariadb-node2上执行

change master to  master_host = '192.168.31.91', master_user = 'lxg', master_password = 'lxg@123', master_log_file = 'mysql-bin.000001', master_log_pos = 987;

这句 SQL 用于在 MySQL 从库中配置主库的连接信息和同步起点，以建立主从复制关系。

重启mariadb-node2上的mariadb服务

systemctl restart mariadb.service

登录mariadb-node2查看同步状态

show slave status\G;

在结果中找到 如下两行

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

若两个值都为Yes，则表示负责建立成功

可以先测试下，在mariadb-node1上创建一个数据库，然后在mariadb-node2上查看是否能够看到

在mariadb-node1上执行

create database lxg;

在mariadb-node2上执行

show databases;

执行结果如下

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lxg                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.002 sec)

配置mariadb-node2为主数据库

配置mariadb-node2为主数据库，mariadb-node1为从数据库

在mariadb-node2上执行如下SQL,用于创建同步用户。

create user 'lxg'@'%' identified by 'lxg@123';
grant replication slave on *.* to 'lxg'@'%' identified by 'lxg@123';

在mariadb-node2上执行，查看Binglog信息

show master status;

结果如下

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      680 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.001 sec)

记住这两个值 mysql-bin.000002 和 680

登录到mariadb-node1上执行

change master to  master_host = '192.168.31.92', master_user = 'lxg', master_password = 'lxg@123', master_log_file = 'mysql-bin.000002', master_log_pos = 680;

重启数据库 mariadb-node1

systemctl restart mariadb.service

第二步、配置keepalived

安装keepalived

在mariadb-node1、mariadb-node2分别执行如下命令，用于安装keepalived。

sudo apt-get install keepalived -y

编辑配置文件

vim /etc/keepalived/keepalived.conf

mariadb-node1的配置文件内容如下

global_defs {
    router_id mysql-ha-a
}

vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 2
    weight -30
    fall 2
    rise 3
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 201
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        192.168.31.90
    }

    track_script {
        chk_mysql
    }

    # 可选：如果你希望 VIP 永远只漂移一次，不自动回到主节点，请取消下面注释
    # nopreempt
}

mariadb-node2的配置文件内容如下

global_defs {
    router_id mysql-ha-b
}

vrrp_script chk_mysql {
    script "/etc/keepalived/check_mysql.sh"
    interval 2
    weight -30
    fall 2
    rise 3
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 201
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        192.168.31.90
    }

    track_script {
        chk_mysql
    }
}

/etc/keepalived/check_mysql.sh的内容如下

该脚本的作用是检查实例是否存活

#!/bin/bash

MYSQL_HOST="127.0.0.1"
MYSQL_PORT=3306

timeout 2 bash -c "</dev/tcp/${MYSQL_HOST}/${MYSQL_PORT}" &>/dev/null
if [ $? -ne 0 ]; then
    exit 1
else
    exit 0
fi

分别在mariadb-node1和mariadb-node2上执行重启keepalived服务

sudo systemctl restart keepalived

第三步、验证

在mariadb-node1上执行

ip a

发现已经有vip``192.168.31.90,内容如下

ubuntu@mariadb-node1:/etc/keepalived$ 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 noprefixroute
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:29:27:af brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.31.91/24 brd 192.168.31.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.90/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.168/24 metric 100 brd 192.168.31.255 scope global secondary dynamic ens33
       valid_lft 21896sec preferred_lft 21896sec
    inet6 fe80::20c:29ff:fe29:27af/64 scope link
       valid_lft forever preferred_lft forever

在mariadb-node1上停止mariadb服务，会发现vip漂移到mariadb-node2上

sudo systemctl stop mariadb.service

在mariadb-node1执行 ip a 返回内容如下

ubuntu@mariadb-node1:/etc/keepalived$ 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 noprefixroute
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:29:27:af brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.31.91/24 brd 192.168.31.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.168/24 metric 100 brd 192.168.31.255 scope global secondary dynamic ens33
       valid_lft 21898sec preferred_lft 21898sec
    inet6 fe80::20c:29ff:fe29:27af/64 scope link
       valid_lft forever preferred_lft forever

发现vip消失了，在mariadb-node2上执行ip a会发现vip已经漂移过来了，内容如下

ubuntu@mariadb-node2:/etc/keepalived$ 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 noprefixroute
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:77:4b:46 brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.31.92/24 brd 192.168.31.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.90/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.168/24 metric 100 brd 192.168.31.255 scope global secondary dynamic ens33
       valid_lft 22161sec preferred_lft 22161sec
    inet6 fe80::20c:29ff:fe77:4b46/64 scope link
       valid_lft forever preferred_lft forever

在mariadb-node1上启动mariadb服务，会发现vip漂移回mariadb-node1

sudo systemctl start mariadb.service

ubuntu@mariadb-node1:/etc/keepalived$ 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 noprefixroute
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:29:27:af brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.31.91/24 brd 192.168.31.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.90/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.168/24 metric 100 brd 192.168.31.255 scope global secondary dynamic ens33
       valid_lft 21896sec preferred_lft 21896sec
    inet6 fe80::20c:29ff:fe29:27af/64 scope link
       valid_lft forever preferred_lft forever

此时在mariadb-node2上执行ip a会发现虚拟IP已经漂移回去了，内容如下

ubuntu@mariadb-node2:/etc/keepalived$ 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 noprefixroute
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:77:4b:46 brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.31.92/24 brd 192.168.31.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.31.168/24 metric 100 brd 192.168.31.255 scope global secondary dynamic ens33
       valid_lft 22490sec preferred_lft 22490sec
    inet6 fe80::20c:29ff:fe77:4b46/64 scope link
       valid_lft forever preferred_lft forever

相关资源

如果需要已经搭建好了的Vmware镜像可以留言。

CATALOG
    FEATURED TAGS
    ebpf lvm mysql oracle postgresql sybase ubuntu 分区 审计 性能测试 数据库 磁盘 经济 网络 软考笔记
    FRIENDS