mysql主主同步的配置

拼搏现实的明天。 2022-05-18 04:25 369阅读 0赞

主主同步事实上就是在主从的基础上，将原先的从机当主机，主机当从机再配置一遍主从同步，具体操作如下：

注意：配置过程中个人使用的指令所需要的权限比较高，主要是因为个人习惯了用root账户操作，如果使用自己创建的账户（输入指令时是‘$’ 而非‘\#’，‘\#’表示系统账户）可能会说权限不足，请自行切换到root用户或使用可执行的其他等效指令

1.在阿里云数据库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

2.在阿里云数据库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的语句将会报错。

在主主同步所需要的主要为以上所介绍到的配置，当然还有其他的配置可以添加，具体的根据实际要求可百度查询。

3.重启mysql

执行指令：service mysqld restart

4.进入mysql：

mysql -uroot -p

5.相互授权：

192.168.18.129数据库1：

grant replication slave, replication client on \*.\* to 'iot123'@'192.168.18.130' identified by 'iot123'

flush privileges; //刷新MySQL的系统权限相关表，否则会无法用账号链接

192.168.18.130数据库2：

grant replication slave, replication client on \*.\* to 'iot123'@'192.168.18.129' identified by 'iot123';

flush privileges; //刷新MySQL的系统权限相关表，否则会无法用账号链接

注意：推荐两个主机相互授权的账号密码相同，否则在接下来的操作中可能会比较乱（我就是之前用了俩个不同的账号搞了好久也没搞定，后来改成相同的账号瞬间成功）

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

show master status;

![70][]

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

192.168.18.129：

mysql> change master to 
        ->master_host='192.168.18.130', 
        ->master_port=3305, 
        ->master_user='iot123',
        ->master_password='iot123',
        ->master_log_file='mysql-bin.000010',
        ->master_log_pos=120;

192.168.18.130：

mysql> change master to 
        ->master_host='192.168.18.129', 
        ->master_port=3305, 
        ->master_user='iot123',
        ->master_password='iot123',
        ->master_log_file='mysql-bin.000012',
        ->master_log_pos=120;

分段写如果写错了\\c退出，第二段开始最后是逗号，最有一段是分号，表结束，端口号和pos不用加引号，其他的配置需要加引号

说明：

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

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

start slave;

查看状态：

show slave status\\G

结果如下：

![70 1][]

上图表示成功

10.异常及解决方法

如果状态如下图所示：

![70 2][]

往下看有错误提示，错误原因大致如下：

网络不通

账号密码错误

pos错误

3305端口未开放

解决方法如下：

1.网络不通——你相互ping一下各自的ip地址，ping的通就说明不是这个问题，我是开的两台虚拟机，在同一局域网内，所以肯定通，如果你用的是阿里云啊之类的外网服务器，ping不通就检查网络接口之类的。

2.账号密码错误——把两台主机的账号密码设置成相同的好处就是不会出现这种情况，我之前账号密码搞混弄了好久

3.pos错误——看看当前日志节点内容与你之前打的一样不，反正重新来一遍准没错

4.3305端口未开放：

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

再里面添加如下内容:

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

个人开放端口如下

![70 3][]

重启防火墙：

service iptables restart

尝试一下能不能navecat远程链接数据库，不行的话就直接把防火墙关了。

在数据库相互授权的过程中可能会报错

![70 4][]

原因，设置的密码太简单，不符合MySQL5.7的默认要求

如果想要密码设置简单点，方法如下：

mysql > set global validate_password_policy=0;

mysql > set global validate_password_length=4;

上述是把密码程度设置成最低级，密码长度设置成4（最小是4，不能再少了）

[70]: /images/20220518/acaece6b2fee4e13aef138093ffe0aff.png
[70 1]: /images/20220518/061217ff6a614e08b034db42ff815000.png
[70 2]: /images/20220518/99ed7b2630a1424e80e8871d18a5ff39.png
[70 3]: /images/20220518/9e249bdb7f76447f81a07b2c18dd3632.png
[70 4]: /images/20220518/55993be8d35d4879942b8b3fd6d391e1.png