一、双主模型复制:两个节点互为主从,均可读写。


二、配置双主模型复制:

1、演示环境:

IP

操作系统

数据库版本

安装方式

角色

192.168.1.145

CentOS   7.6 x86_64

MariaDB-10.3.15

yum

master1

192.168.1.146

CentOS   7.6 x86_64

MariaDB-10.3.15

yum

master2

2、两个节点的服务器时间同步

3、master1修改server.cnf配置文件:# vim /etc/my.cnf.d/server.cnf

[mysqld]

port=3306

socket=/var/lib/mysql/mysql.sock

datadir=/var/lib/mysql

log_error=/var/log/mariadb.log

lower_case_table_names=1

character_set_server=utf8mb4

collation_server=utf8mb4_general_ci

innodb_file_per_table=1

skip_name_resolve=1

slow_query_log=1

slow_query_log_file=mariadb-slow.log

log_bin=master1-bin

log_bin_index=master1-bin.index

binlog_format=mixed

relay_log=relay1-log

relay_log_index=relay1-log.index

server_id=1

sync_binlog=1

innodb_flush_log_at_trx_commit=1

备注:中继日志默认不存在,只有当复制启动时才会自动生成

4、master1创建具有复制权限的用户:

MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.146' identified by '123456';

MariaDB [(none)]> flush privileges;

5、master1查看正在使用的二进制日志文件名称及事件位置:

MariaDB [(none)]> show master status;

image.png

6、master2修改server.cnf配置文件:# vim /etc/my.cnf.d/server.cnf

[mysqld]

port=3306

socket=/var/lib/mysql/mysql.sock

datadir=/var/lib/mysql

log_error=/var/log/mariadb.log

lower_case_table_names=1

character_set_server=utf8mb4

collation_server=utf8mb4_general_ci

innodb_file_per_table=1

skip_name_resolve=1

slow_query_log=1

slow_query_log_file=mariadb-slow.log

log_bin=master2-bin

log_bin_index=master2-bin.index

binlog_format=mixed

relay_log=relay2-log

relay_log_index=relay2-log.index

server_id=2

sync_binlog=1

innodb_flush_log_at_trx_commit=1

7、master2使用具有复制权限的用户连接至master1

MariaDB [(none)]> change master to master_host='192.168.1.145',master_user='repluser',master_password='123456',master_port=3306,master_log_file='master1-bin.000001',master_log_pos=2035;

MariaDB [(none)]> show slave status\G

image.png

备注:

(1)Slave_IO_RunningSlave_SQL_Running的值,默认为No

(2)自动在数据目录/var/lib/mysql中创建relay2-log.000001relay2-log.indexrelay-log.info文件

(3)Master_Log_File的值为master1的二进制日志文件名称

(4)Read_Master_Log_Pos的值为master1二进制日志事件的位置

8、master2启动复制线程:

MariaDB [(none)]> start slave;

备注:

(1)start slave等同于分别执行start slave io_threadstart slave sql_thread

(2)stop slave表示停止主从复制线程

(3)重启master2所在的服务器,复制线程会自动启动

MariaDB [(none)]> show slave status\G

image.png

image.png

备注:

(1)只有当Slave_IO_RunningSlave_SQL_Running的值都为Yes时,复制线程才算启动成功

(2)Seconds_Behind_Master的值为0,说明master2没有落后于master1

(3)复制时的详细信息记录在master2的错误日志/var/log/mariadb.log

9、master2创建具有复制权限的用户:

MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.1.145' identified by '123456';

MariaDB [(none)]> flush privileges;

10、master2查看正在使用的二进制日志文件名称及事件位置:

MariaDB [(none)]> show master status;

image.png

11、master1使用具有复制权限的用户连接至master2

MariaDB [(none)]> change master to master_host='192.168.1.146',master_user='repluser',master_password='123456',master_port=3306,master_log_file='master2-bin.000001',master_log_pos=2035;

MariaDB [(none)]> show slave status\G

image.png

备注:

(1)Slave_IO_RunningSlave_SQL_Running的值,默认为No

(2)自动在数据目录/var/lib/mysql中创建relay1-log.000001relay1-log.indexrelay-log.info文件

(3)Master_Log_File的值为master2的二进制日志文件名称

(4)Read_Master_Log_Pos的值为master2二进制日志事件的位置

12、master1启动复制线程:

MariaDB [(none)]> start slave;

备注:重启master1所在的服务器,复制线程会自动启动

MariaDB [(none)]> show slave status\G

image.png

image.png

备注:

(1)只有当Slave_IO_RunningSlave_SQL_Running的值都为Yes时,复制线程才算启动成功

(2)Seconds_Behind_Master的值为0,说明master1没有落后于master2

(3)复制时的详细信息记录在master1的错误日志/var/log/mariadb.log

13、master1查看master2相关信息:

MariaDB [(none)]> show slave hosts;

image.png

14、master2查看master1相关信息:

MariaDB [(none)]> show slave hosts;

image.png


三、测试双主模型复制:

1、master1创建测试数据:

MariaDB [(none)]> create database db1;

MariaDB [(none)]> use db1;

MariaDB [db1]> create table tb1(id int unsigned auto_increment primary key not null,name char(20) not null);

MariaDB [db1]> desc tb1;

MariaDB [db1]> insert into tb1(name) values('zhangsan'),('lisi');

MariaDB [db1]> select * from tb1;

image.png

MariaDB [db1]> show master status;

image.png

2、master2查看测试数据:

MariaDB [(none)]> show databases;

MariaDB [(none)]> use db1;

MariaDB [db1]> show tables;

MariaDB [db1]> desc tb1;

MariaDB [db1]> select * from tb1;

image.png

MariaDB [db1]> show slave status\G

3、master2创建测试数据:

MariaDB [db1]> insert into tb1(name) values('wangwu'),('zhaoliu');

MariaDB [db1]> select * from tb1;

image.png

MariaDB [db1]> show master status;

image.png

4、master1查看测试数据:

MariaDB [db1]> select * from tb1;

image.png

MariaDB [db1]> show slave status\G