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.1Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 770Relay_Log_File: mariadb-slave-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: NoSlave_SQL_Running: NoReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 770Relay_Log_Space: 256Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimisticSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0Slave_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 eventMaster_Host: 127.0.0.1Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 770Relay_Log_File: mariadb-slave-relay-bin.000002Relay_Log_Pos: 555Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 770Relay_Log_Space: 872Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: NoGtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimisticSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesSlave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0Slave_Transactional_Groups: 0
1 row in set (0.000 sec)