mysql主从、主主搭建_mysql5.7 主主模式搭建-CSDN博客

本文链接：https://blog.csdn.net/chenhanl/article/details/109283044

一、主主数据库搭建

1.下载maraidb,临时关闭防火墙和selinux

yum install -y mariadb mariadb-server mariadb-devel -y
systemctl stop firewalld
setenforce 0

2.在数据库1的my.cnf的mysqld节点添加以下配置：

port=3305
 
server-id=1
 
log-bin=mysql-bin
 
auto_increment_increment=2
 
auto_increment_offset=1
 
binlog-ignore=mysql
 
binlog-ignore=information_schema
 
binlog-ignore=performance_schema
 
character_set_server=utf8
 
log-bin-trust-function-creators=1

3.在数据库2的my.cnf的mysqld节点添加以下配置：

port=3305
 
server-id=2
 
log-bin=mysql-bin
 
auto_increment_increment=2
 
auto_increment_offset=2
 
binlog-ignore=mysql
 
binlog-ignore=information_schema
 
binlog-ignore=performance_schema
 
character_set_server=utf8
 
log-bin-trust-function-creators=1

注：
port=3305 将mysql启动端口设置成3305（默认为3306）

log_bin 启动mysql二进制日志，如果没有配置这个将无法远程链接

binlog-ignore 指定不同步的数据库，如果有多个数据库不需要同步可以多个分别声明

character_set_server=utf8 指定utf8为默认字符集

server-id 可以为任意自然数，必须保证两台mysql主机不重复

auto_increment_increment=2 步进值auto_imcrement。一般有n台主MySQL就填n

auto_increment_offset 设定数据库中自动增长的起点，两台mysql的起点必须不同，这样才能避免两台服务器同步时出现主键冲突

replicate-do-db 要同步的数据库，如果需要就填，指定数据库的名称即可，默认为所有库，声明了不同步就默认除了不同步数据库意外的所有库。这里我没写。

log-bin-trust-function-creators=1 在默认情况下mysql会阻止主从同步的数据库function的创建,这会导致我们在导入sql文件时如果有创建function或者使用function的语句将会报错。

4.重启mariadb,并进入数据库。

systemctl restart mariadb

mysql

5.相互授权：

192.168.2.126数据库1；

grant replication slave on *.* to 'chl'@'192.168.2.125' identified by '123456';
flush privileges;//刷新MySQL的系统权限相关表，否则会无法用账号链接

192.168.2.125数据库2；

grant replication slave on *.* to 'chl'@'192.168.2.126' identified by '123456';
flush privileges;//刷新MySQL的系统权限相关表，否则会无法用账号链接

注：推荐两个主机相互授权的账号密码相同，否则在接下来的操作中可能会比较乱

6.分别查看两台主机的日志节点：

show master status;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000003 |      483 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

7.相互设置同步的日志节点:

192.168.2.126数据库1；

change master to master_host='192.168.2.125',master_user='chl',master_password='123456',mastet_port=3305,master_log_file='mysql-bin.000003',master_log_pos=483;

192.168.2.125数据库2；

change master to master_host='192.168.2.126',master_user='chl',master_password='123456',mastet_port=3305,master_log_file='mysql-bin.000003',master_log_pos=483;

注：所有的内容都是另一台主机的内容，log_file和log_pos对于另一台主机的日志节点。还有，必须要说明的是，每次“flush privileges”和“stop slave”后日志节点的内容会改变，所以如果不确定，每次执行此操作前必须要查看日志节点——show master status。另外，执行此操作时slave必须没有启动，如果之前执行了，那就先停止，指令：stop slave;

8.在两台主机上启动slave服务

start slave;

查看状态：

show slave status\G

在这里插入图片描述
上图表示成功

9.异常及解决方法

如果状态如下图所示：
在这里插入图片描述
往下看有错误提示，错误原因大致如下：
网络不通
账号密码错误
pos错误
3305端口未开放

解决方法如下：
1.网络不通——你相互ping一下各自的ip地址，ping的通就说明不是这个问题，我是开的两台虚拟机，在同一局域网内，所以肯定通，如果你用的是阿里云啊之类的外网服务器，ping不通就检查网络接口之类的。
2.账号密码错误——把两台主机的账号密码设置成相同的好处就是不会出现这种情况，我之前账号密码搞混弄了好久
3.pos错误——看看当前日志节点内容与你之前打的一样不，反正重新来一遍准没错
4.3306端口未开放：

找到iptables文件（etc/sysconfig/iptables）

再里面添加如下内容:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3305 -j ACCEPT

个人开放端口如下
在这里插入图片描述
重启防火墙：

service iptables restart

二、主从数据库搭建

1.yum 配置Mariadb

yum install mariadb mariadb-devel mariadb-server -y

2. 确定master 角色和 slave 角色

192.168.2.125 master
192.168.2.126 slave

3.配置 MySQL配置文件/etc/my.cnf

master 主库操作：

vim /etc/my.cnf
[mysqld]
server-id = 1 #用来标识mysql集群主从关系,master = 1 ,其他从库往后面排。
log-bin = mysql-bin #定义bin-log 文件名字
Slave 从库操作：
vim /etc/my.cnf
[mysqld]
server-id = 2 #用来标识mysql集群主从关系,master = 1 ,其他从库往后面排。
log-bin = mysql-bin #定义bin-log 文件名字

4.修改完之后重启mariadb 程序

systemctl restart mariadb

5.master 上面创建slave 链接master的复制账号和授权命令

Master数据库服务器命令行中创建tongbu用户及密码并设置权限，执行如下命令，查看bin-log文件及position点，

grant replication slave on *.* to 'tongbu'@'192.168.2.126' identified by
'123456';
show master status;
flush tables with read lock; #主库配置锁表

6.提前关闭master 或者 slave 的防火墙，或者放开mysql端口，关闭selinux

7.slave 链接 master,请求bin-log 文件（mysql-bin）。

change master to
master_host='192.168.2.125',master_user='tongbu',master_password='123456',master_log_
file='mysql-bin.000003',master_log_pos=477;
slave start；
MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.6
Master_User: tongbu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 477
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000003
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: 477
Relay_Log_Space: 825
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
1 row in set (0.00 sec)