• 基于Ubuntu20.04.01

1.主备机分别修改配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
vim /etc/mysql/mariadb.cnf

#主机备份原文件，清空之前配置，改为如下内容：
[mysqld]
datadir=/var/lib/mysql
socket=/run/mysqld/mysqld.sock
server-id=1
log-bin=mysql-bin
relay-log=mysql-relay-bin
auto_increment_offset=1
auto_increment_increment=2
log_slave_updates =1
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%
slave-skip-errors=all


备机备份原文件，清空之前配置，改为如下内容：
[mysqld]
datadir=/var/lib/mysql
socket=/run/mysqld/mysqld.sock
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
auto_increment_offset=2
auto_increment_increment=2
log_slave_updates =1
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=performance_schema.%
slave-skip-errors=all

2.sudo ufw disable 主从分别关闭防火墙

3.主从分别重启数据库服务并查看服务状态

1
2
systemctl restart mariadb.service
systemctl status mariadb.service

4.主从分别创建复制账户

1
2
3
4
5
#创建slave用户密码为slave，从任意IP均可以登录。
#要是需要创建其他密码，从指定IP登录为：grant replication slave on . to ‘slave’@‘192.168.100.%’ identified by ‘pwd@123’;
create user 'slave'@'%' identified by 'slave';
grant replication slave on *.* to slave@'%' identified by 'slave';
flush privileges;

5.配置主主复制文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
1>主机：

flush tables with read lock;
show master status;		#拿到file和position值
unlock tables;
此时在备机上设置
change master to  master_host='主机IP', master_user='slave', master_password='slave', master_port=3306, master_log_file='主机file文件名', master_log_pos=主机position值;
start slave;
#查看配置状态
show slave status \G
#Slave_IO_Running: Yes与Slave_SQL_Running: Yes既正确
如遇到报错Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
解决办法：
stop slave;
reset slave;
start slave;

2>备机：

flush tables with read lock;
show master status;		#拿到file和position值
unlock tables;
此时在主机上设置
change master to  master_host='备机IP', master_user='slave', master_password='slave', master_port=3306, master_log_file='备机file文件名', master_log_pos=备机position值;
start slave;
#查看配置状态
show slave status \G
#Slave_IO_Running: Yes与Slave_SQL_Running: Yes既正确

#如遇到报错Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
#解决办法：
stop slave;
reset slave;
start slave;