Mariadb10.5基于同服务器多实例主从配置

本次部署环境：Centos8stream

本次部署mariadb版本： mariadb:10.5

本次部署方式：rpm包直接安装，并通过systemd直接托管

可以参考 /usr/lib/systemd/system/mariadb@.service 该文件

# Multi instance version of mariadb. For if you run mutiple verions at once.
# Also used for mariadb@bootstrap to bootstrap Galera.
#
# To use multi instance variant, use [mysqld.INSTANCENAME] as sections in my.cnf
# and start the service via:
#       systemctl start mariadb@{instancename}.server
#
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades.  If you want to customize, the
# best way is to create a file "/etc/systemd/system/mariadb.service",
# containing
#       .include /usr/lib/systemd/system/mariadb.service
#       ...make your changes here...
# or create a file "/etc/systemd/system/mariadb.service.d/foo.conf",
# which doesn't need to include ".include" call and which will be parsed
# after the file mariadb.service itself is parsed.
#
# For more info about custom unit files, see systemd.unit(5) or
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

# For example, if you want to increase mysql's open-files-limit to 10000,
# you need to increase systemd's LimitNOFILE setting, so create a file named
# "/etc/systemd/system/mariadb.service.d/limits.conf" containing:
#       [Service]
#       LimitNOFILE=10000

# Note: /usr/lib/... is recommended in the .include line though /lib/...
# still works.
# Don't forget to reload systemd daemon after you change unit configuration:
# root> systemctl --system daemon-reload

# Use [mysqld.INSTANCENAME] as sections in my.cnf to configure this instance.

1.安装数据库

centos8stream 默认是 10.3，需要重置默认版本并安装

a.通过命令查看系统可支持的版本

dnf module list mariadb

b.重置默认版本，并安装

dnf module reset mariadb -y
dnf module enable mariadb:10.5 -y
dnf install mariadb-server mariadb -y

2.修改配置 /etc/my.cnf.d/mariadb-server.cnf

[mysqld.master]
server_id = 1
port = 3306
log-bin=/var/log/mariadb/mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid

[mysqld.slave]
server_id = 2
port = 3307
datadir=/var/lib/mysql-slave
socket=/var/lib/mysql-slave/mysql-slave.sock
log-error=/var/log/mariadb/mariadb-slave.log
pid-file=/run/mariadb/mariadb-slave.pid

3.创建数据库目录并授权

mkdir -pv /var/lib/mysql-slave
chown mysql.mysql /var/lib/mysql-slave

4.启动主节点和从节点

systemctl restart mariadb@master
systemctl restart mariadb@slave

 

 5.配置slave授权，并查看当前binlog日志点

 a.主节点操作

grant replication client,replication slave on *.* to 'repluser'@'127.0.0.1' identified by 'replpass';

 b.从节点操作

change master to master_host='127.0.0.1',master_user='repluser',master_password='replpass',master_log_file='mysql-bin.000001',master_log_pos=770;

• master_host='127.0.0.1'   # 主节点授权ip
• master_user='repluser'     # 主节点用户
• master_password='replpass'  # 主节点密码
• master_log_file='mysql-bin.000001'  # 主节点show master status获取的File名称
• master_log_pos=770  # 主节点show master status获取的Position信息

 查看slave信息

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: 
                   Master_Host: 127.0.0.1
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 770
                Relay_Log_File: mariadb-slave-relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: No
             Slave_SQL_Running: No
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 770
               Relay_Log_Space: 256
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 0
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

从上面打印的信息可以看到

Slave_IO_Running: No
Slave_SQL_Running: No

说明slave服务还未开启，需要通过下面mysql命令启动

start slave;

启动slave并检查同步状态

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 127.0.0.1
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 770
                Relay_Log_File: mariadb-slave-relay-bin.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 770
               Relay_Log_Space: 872
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 1
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: optimistic
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)