MariaDB主从配置

一、说明

CentOS7默认没有安装MariaDB，安装也是低版本的5.5.68。

yum install -y mariadb mariadb-server

mariadb5.5不支持zabbix6，需要高版本的MariaDB可以指定源去安装

二、安装Mariadb10.5

cat >/etc/yum.repos.d/mariadb.repo<<EOF
[mariadb]
name = MariaDB 
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.5/centos7-amd64/
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck =1
enabled = 1
EOF
yum install -y mariadb-server mariadb
systemctl enable --now mariadb

三、主从配置(新数据库，无数据)

1、主数据库配置

cat >>/etc/my.cnf<<EOF
[mysqld]
log-bin=mysql-bin
server-id=1
EOF
systemctl restart mariadb.service
mysql
grant replication slave on *.* to 'rep'@'103.73.119.%' identified by 'www.123.nyc';
FLUSH PRIVILEGES;
flush tables with read lock;
show master status;

unlock tables;

2、从数据库配置

cat >>/etc/my.cnf<<EOF
[mysqld]
server-id=2
EOF
systemctl restart mariadb.service
#对应修改下载的值
mysql
CHANGE MASTER TO
MASTER_HOST='103.73.119.22',
MASTER_USER='rep',
MASTER_PASSWORD='www.123.nyc',
MASTER_PORT=3306, 
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=652;
start slave;
show slave status \G

如果从数据库配置错误的解决方法(已经做了第2步了)

mysql>stop slave;
mysql>reset slave;

10.5版本的配置文件内容比较少，可以扩展下

四、主主配置

主从配置：只能从数据库备份主数据库的数据，不能反向。

主主配置：互相同步数据

MySQL1主机配置（119.22）

cat >/etc/yum.repos.d/mariadb.repo<<EOF
[mariadb]
name = MariaDB 
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.5/centos7-amd64/
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck =1
enabled = 1
EOF
yum install -y mariadb-server mariadb
systemctl enable --now mariadb
cat >>/etc/my.cnf<<EOF
[mysqld]
log-bin=mysql-bin
server-id=1
EOF
systemctl restart mariadb.service
mysql
grant replication slave on *.* to 'rep'@'103.73.119.%' identified by 'www.123.nyc';
FLUSH PRIVILEGES;
flush tables with read lock;
show master status;
unlock tables;

#对应修改119.23的信息
mysql
CHANGE MASTER TO
MASTER_HOST='103.73.119.23',
MASTER_USER='rep',
MASTER_PASSWORD='www.123.nyc',
MASTER_PORT=3306, 
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=652;
start slave;
show slave status \G

MySQL2主机配置（119.23）

cat >/etc/yum.repos.d/mariadb.repo<<EOF
[mariadb]
name = MariaDB 
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.5/centos7-amd64/
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck =1
enabled = 1
EOF
yum install -y mariadb-server mariadb
systemctl enable --now mariadb
cat >>/etc/my.cnf<<EOF
[mysqld]
log-bin=mysql-bin
server-id=2
EOF
systemctl restart mariadb.service
mysql
grant replication slave on *.* to 'rep'@'103.73.119.%' identified by 'www.123.nyc';
FLUSH PRIVILEGES;
flush tables with read lock;
show master status;
unlock tables;

#对应修改119.22的信息
mysql
CHANGE MASTER TO
MASTER_HOST='103.73.119.22',
MASTER_USER='rep',
MASTER_PASSWORD='www.123.nyc',
MASTER_PORT=3306, 
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=652;
start slave;
show slave status \G

---

主配置（命令）

cat >/etc/yum.repos.d/mariadb.repo<<EOF
[mariadb]
name = MariaDB 
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.5/centos7-amd64/
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck =1
enabled = 1
EOF
yum install -y mariadb-server mariadb
systemctl enable --now mariadb
cat >>/etc/my.cnf<<EOF
[mysqld]
log-bin=mysql-bin
server-id=1
EOF
systemctl restart mariadb.service
mysql
grant replication slave on *.* to 'rep'@'103.73.119.%' identified by 'www.123.nyc';
FLUSH PRIVILEGES;
flush tables with read lock;
show master status;
unlock tables;
exit

从配置（命令）

cat >/etc/yum.repos.d/mariadb.repo<<EOF
[mariadb]
name = MariaDB 
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.5/centos7-amd64/
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck =1
enabled = 1
EOF
yum install -y mariadb-server mariadb
systemctl enable --now mariadb
cat >>/etc/my.cnf<<EOF
[mysqld]
server-id=2
EOF
systemctl restart mariadb.service

#对应修改下载的值

mysql
CHANGE MASTER TO
MASTER_HOST='103.73.119.22',
MASTER_USER='rep',
MASTER_PASSWORD='www.123.nyc',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=652;
start slave;
show slave status \G
exit