第十三章 数据库主从复制
1.简介
数据库运行时,一些因素可能会导致服务运行不正常,用户访问数据受阻。对于互联网公司,尤其是购物网站而言,这种情况造成的损失是无法估量的。因此,对数据库进行“备份”也是必不可少的操作。当主要的数据库死机时,系统能够快速地切换到备用的数据库上。本章将详细介绍数据库集群中的主从复制原理和操作流程。

2.主从复制原理
主从复制又被称为AB复制,主要用于实现数据库集群中的数据同步。实现MySQL的AB复制时,数据库的版本应尽量保持一致。本节将为读者详细介绍主从复制的原理。
在主从复制集群中,主数据库把数据更改的操作记录到二进制日志中,从数据库分别启动I/O线程和SQL线程,用于将主数据库中的数据复制到从数据库中。其中,I/O线程主要将主数据库上的日志复制到自己的中继日志中,SQL线程主要用于读取中继日志中的事件,并将其重放到从数据库之上。另外,系统会将I/O线程已经读取的二进制日志的位置信息存储在master.info文件中,将SQL线程已经读取的中继日志的位置信息存储在relay-log.info文件中。随着版本的更新,在MySQL 5.6.2之后,MySQL允许将这些状态信息保存在Table中,不过在更新之前需要用户在配置文件中进行声明,具体的参数如下。
[mysqld]
master-info-repository = TABLE -----FILE表示以文件方式
relay-log-info-repository = TABLE -----FILE表示以文件方式
主从复制原理如图所示。
图片 1.png
MySQL实现主从复制的前提是作为主服务器的数据库服务器必须开启二进制日志。主从复制集群的工作流程如下。
(1)主服务器上面的任何修改都会通过自己的I/O线程保存在二进制日志里。
(2)从服务器上面也会启动一个I/O线程,通过配置好的用户名和密码连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个中继日志的末端,并将读取到的主服务器端的二进制日志的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚地告诉主服务器:我需要某个二进制日志的某个位置之后的日志内容,请发给我。
(3)从服务器的SQL线程检测到中继日志中新增加了内容后,会马上解析日志中的内容,并在自身执行。需要注意,每个从服务器都会收到主服务器二进制日志中的全部内容的副本,除非另行指定,否则,从服务器将执行来自主服务器二进制日志文件的所有的操作语句。另外,从服务器每次进行同步时,都会记录二进制日志坐标(坐标包含文件名和从主服务器上读取的位置,即master-info),以便下次连接使用。由于每个从服务器分别记录了当前二进制日志的位置,因此可以断开从服务器的连接,重新连接,然后恢复处理。

3.基本架构
在 MySQL 的主从复制集群中,主数据库既要负责写操作又要负责为从数据库提供二进制日志,这无疑增加了主数据库的压力。此时可以将二进制日志只给某一个从服务器使用,并在该从服务器上开启二进制日志,将该从服务器二进制日志分发给其他的从服务器;或者,这个从服务器不进行数据的复制,只负责将二进制日志转发给其他的从服务器。这样,不仅可以减少主服务的压力,还可以提高整体架构的性能。
一主多从原理如图所示
图片 2.png
一主多从架构如图 所示。
图片 3.png
对于数据操作较多的服务,企业也可以根据自身的实际需求设置多主多从架构,以此来满足更高的读写请求。多主多从架构如图所示。
图片 4.png
在搭建数据库主从复制集群时,可以通过在[mysqld]配置项中添加max_binlog_size参数来设置二进制文件的大小,当日志到达指定的大小时,系统会自动创建新的日志文件。为了跟踪已使用的二进制日志文件,mysqld服务还会创建一个二进制日志索引文件(扩展名为.index),包含所有使用过的二进制日志文件的名称。默认情况下,该索引文件具有与二进制日志文件相同的基本名称,在mysqld服务运行时,不建议编辑该文件。
MySQL 5.7开始支持多源复制架构,即多个主服务器连接同一个从服务器(多主一从)。多源复制架构如图所示。
图片 5.png
多源复制中加入了一个叫作Channel的概念,每一个Channel都是一个独立的Slave,都有一个IO线程和一个SQL线程, 基本原理和普通的复制一样。 在对 Slave执行 CHANGEMASTER 语句时,只需要在每个语句最后使用for channel 关键字来进行区分即可。需要注意,在使用这种架构时,需要在从数据库的my.cnf配置文件中将master-info-repository、relay-log-info-repository参数设置为TABLE, 否则系统会报错。
相比于传统的一主一从、多主多从,在多源复制架构中,管理者可以直接在从数据库中进行数据备份, 不会影响线上业务的正常运行。多源复制架构将多台数据库连接在一起,可以实现合并表碎片,管理者不需要为每个数据库都制作一个实例,减少了维护成本,使用这种方式在后期进行数据统计时也会非常高效。

4.复制模式
MySQL主从复制的方式可以分为异步复制、同步复制和半同步复制,3种方式详细说明如下。
1.异步复制
异步复制为MySQL默认的复制方式, 主数据库执行完客户端提交的事务后会立即将结果返给客户端,并不关心从数据库是否已经接收并进行了处理。 从日志的角度讲,在主数据库将事务写入二进制日志文件后,主数据库只会通知dump线程发送这些新的二进制日志文件,然后主数据库就会继续处理提交操作,并不考虑这些二进制日志已经传到每个从数据库节点上。在使用异步复制模式时,如果主数据库崩溃,可能会出现主数据库上已经提交的事务并没有传到从数据库上的情况,如果此时将从数据库提升为主数据库,很有可能导致新主数据库上的数据不完整。
2.同步复制
同步复制是指主数据库向从数据库发送一个事务,并且所有的从数据库都执行了该事务后才会将结果提交给客户端。因为需要等待所有的从数据库执行完该事务,所以在使用同步复制时,主数据库完成一个事务的时间会被拉长,系统性能受到严重影响。
3.半同步复制
半同步复制介于同步复制与异步复制之间,主数据库只需要等待至少一个从数据库节点收到并且更新二进制日志到中继日志文件即可,不需要等待所有从数据库给主数据库反馈。如此一来,不仅节省了很多时间,而且提高了数据的安全性。另外, 由于整个过程产生通信,所以建议在低延时的网络中使用半同步复制。

5.一主一从复制
前面介绍了MySQL主从复制的原理,这里将通过具体的案例来为读者演示MySQL主从复制架构的配置流程和需要注意的问题。在搭建MySQL主从复制集群时,应该尽量保证主服务器和从服务器的服务版本一致。同时,应该关闭IPTables和SELinux。另外还需要保证主从服务器的时间一致。搭建主从复制一主一从集群的配置项参数如表所示。
图片 6.png
需要注意,应为两台MySQL服务器配置主机名解析。另外,Server ID的取值范围为1~ 65535,而且每台主机的Server ID不能相同,本例中以服务器IP地址的最后两位作为Server ID使用。
配置主从复制集群时,需要保证从服务器相对“干净”,不能存在一些无关的数据,建议用刚刚初始化完成的服务器作为从服务器。部署一主一从复制集群的基本流程如下。
1.配置主服务器
在配置主从复制集群时,需要在主服务器上开启二进制日志并配置唯一的Server ID,配置完成后重新启动mysqld服务。编辑主服务器的配置文件my.cnf,添加如下内容。
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=10
创建相关的日志目录并赋予权限,具体的指令如下所示。
[root@mysql-1 ~]# mkdir /var/log/mysql
[root@mysql-1 ~]# chown mysql.mysql /var/log/mysql
目录创建完成后,重新启动 mysqld 服务。
[root@mysql-1 ~]# systemctl restart mysqld
需要注意,在配置文件中如果省略Server ID(或者将其设置为默认值0),则主服务器将拒绝来自从服务器的任何连接。为了在使用带事务的InnoDB存储引擎进行复制设置时尽可能提高持久性和一致性,需要在主服务器的my.cnf配置文件中加入以下配置项。
innodb_flush_log_at_commit = 1
sync_binlog = 1
上方参数innodb_flush_log_at_trx_commit = 1表示当事务提交时,系统会将日志缓冲写入磁盘,并且立即刷新。参数sync_binlog = 1表示当事务提交时,系统会将二进制文件写入磁盘并立即执行刷新操作。另外,为了从服务器可以连接主服务器,还需要将skip_networking选项设置为OFF状态(默认为OFF状态),如果该选项为启用状态,则从服务器无法与主服务器通信,并且会造成复制失败。在MySQL中查看skip_networking选项状态的指令如下。
图片 7.png
从代码的执行结果可以看出, skip_networking 选项为关闭状态。
2.创建指定用户
在进行主从复制时,为了提高数据库集群的安全性,建议创建一个专门用于复制数据的用户,每个从服务器需要使用MySQL主服务器上的用户名和密码连接到主服务器。例如,在主服务器上创建用户repl,并允许该用户从任何从服务器连接到主服务器上进行复制操作,具体的操作代码如下。
mysql> create user 'repl'@'%'
mysql> grant replication slave on . to 'repl'@'%' identified by '123';
用户和权限设置完成后,可以尝试在从服务器上使用刚才创建的用户进行测试连接,具体操作如下。
图片 8.png
可以看出,用户 repl 可以通过从服务器登录主服务器。
3.复制数据
在搭建主从复制集群时,主服务器上可能已经存在数据,为了模拟真实的生产环境,在主服务器上插入测试数据,具体操作如下。
图片 9.png
从上方代码的执行结果可以看出,在主服务器中插入数据成功。在启动复制之前,需要使主服务器中现有的数据与从服务器保持同步,且在进行相关操作时保证客户端正常运行,以便锁定保持不变。将主服务器中现有的数据导出,并将导出的数据复制到每个从服务器上。本例将使用mysqldump工具对数据进行备份,具体的操作代码如下。
[root@master1 ~]# mysqldump -uroot -p123 --all-database --master-data=1 >dbdump.db
上方代码中,master-data参数表示自动锁定表;如果不使用master-data参数,则需要手动锁定单独会话中的所有表。查看备份中数据记录的二进制日志的位置,以便在从服务器配置中使用,具体操作如下。
图片 10.png
在配置文件的第22行中可以看出,日志文件的分割点为mysql-bin.000002文件中的154位置。接下来,使用scp工具或rsync工具将备份出来的数据传输到从服务器上。在主服务器上执行以下命令。
[root@master1 ~]# scp dbdump.db root@slave1:/root/
上方代码中,slave1需要能被主服务器解析出IP地址(即上文提到的服务器之间需要做主机名解析)。
4.配置从服务器
数据复制完成后,需要在从服务器的my.cnf配置文件中添加Server ID,具体语句如下。
[mysqld]
server-id=11
需要注意,配置修改完成后需要重新启动mysqld服务。接下来,登录到从服务器的数据库中将备份的数据导入,执行代码如下。
mysql> source /root/dbdump.db
在从服务器上配置连接到主服务器的相关信息,具体代码如下。
图片 11.png
上方代码中, master_host 表示需要连接的主服务器名称, master_user 表示连接到主服务器的用户, master_password 表示连接用户的密码。配置完成后,在从服务器上开始复制线程,启动指令如下。
mysql> start slave;
Query OK,0 rows affected (0.09 sec)
在从服务器上执行如下操作可以验证线程是否工作正常。
从上方代码的执行结果可以看出,I/O线程和SQL线程的状态都为YES,证明主从复制线程启动成功。
5.复制状态验证
主从复制线程启动后,主服务器上关于修改数据的操作都会在从服务器中回演,这样就保证了主从服务器数据的一致性。下面将进行相应的验证。
尝试在主服务器中插入一些数据,并在从服务器上查看插入的数据是否存在,具体操作如下。
图片 12.png
从上方代码的执行结果可以看出,在主服务器上成功地插入了一条数据。接下来在从服务器上查看该数据是否存在,具体代码如下。
图片 13.png
可以看出,从服务器的tt表中也存在id为4的数据,这说明从服务器与主服务器中的数据同步成功。
6.故障排除
当SQL线程或I/O线程启动异常时,可以先使用show master status\G命令检查当前二进制日志的位置与配置从服务器时设置的二进制日志位置是否相同。另外,也可以通过my.cnf配置中指定的错误日志查看错误信息。
[root@slave1 ~]# tail -10 /var/log/mysqld.log
7.加入新的从服务器
当数据量不断增加时,如果需要在集群中加入其他的从服务器,则配置流程与前面从服务器的配置一样,唯一不同的是需要修改新加入从服务器的Server ID。另外,需要注意,假如在新加入从服务器之前,主服务器执行了删除库的操作,并且删除的库刚好是第一次mysqldump备份时的数据,那么,在从服务器上将会显示“没有这个数据库”的错误信息,因此,建议使用最新的备份数据。

6.基于GTID的主从复制
GTID为全局事务标识符,用于记录不同的事务。 GTID主要由两部分组成:一部分是服务的UUID,保存在MySQL数据目录的auto.cnf文件中;而另一部分就是事务的ID,会随着事务的增加而递增。GTID模式下使用新的复制协议COM_BINLOG_DUMP_GTID进行复制,在配置主从复制集群时,可以配置系统使用GTID来自动识别二进制文件中不同事务的位置,这样可以避免再配置过程中产生不必要的错误。下面将通过具体的案例进行说明。
假设master1和slave1为刚刚初始化完成的MySQL服务器,现用这两台数据库服务器配置主从复制集群,具体的操作流程如下。
在主服务器的配置文件中开启二进制日志,并开启GTID选项,需要在my.cnf文件中加入以下内容。
图片 14.png
同样,在从服务器的配置文件中也开启GTID选项,具体代码如下。
图片 15.png
上方配置参数中, gtid_mode 参数为统一标识符选项,enforce_gtid_consistency参数表示强制开启一致性。需要注意的是,设置gtid_mode参数和enforce_gtid_consistency参数后,使用者将不需要记录主服务器备份文件中二进制日志的位置,从服务器会自动继续获取。
接下来,在主服务器上创建用于主从复制的用户,使用mysqldump工具对文件进行备份,并将备份好的文件复制给从服务器。配置完成后重新启动mysqld服务使配置生效。
登录到从数据库,设置连接主服务器的配置项,并开启I/O线程和SQL线程,具体操作代码如下。
图片 16.png
需要注意,上方参数中 master_auto_position=1 表示系统自动获取二进制日志位置。配置完成后,开启从服务器复制线程。
mysql> start slave;
Query OK,0 rows affected (0.09 sec)
使用STATUS命令查看I/O线程和SQL线程是否正常运行。
图片 17.png
如果两个线程都为Yes状态并且没有出现Error信息,则表明基于GTID的主从配置成功。读者也可以尝试在主服务器插入数据并在从服务器中查看主从复制的同步状态。在基于GTID复制时,对所需的环境和操作也有一些限制条件,说明如下。
(1)因为GTID为全局事务标识符,所以基于GTID的复制不支持非事务引擎。
(2)不支持create table table_name select * from table_name语句。主要是因为在GTID模式下只能为一条语句生成一个GTID,而该语句会生成两个SQL线程(一个是DDL创建表的SQL线程,另一个是insert into插入数据的SQL线程),由于DDL语句会导致自动提交,所以该语句至少需要两个GTID,导致与规则冲突。
(3)不允许一个SQL线程同时更新事务引擎表和非事务引擎表。
(4)在一个主从复制组中,必须统一开启GTID或关闭 GTID。
(5)不支持create temporary table语句和drop temporary table语句。
(6)当从服务器需要跳过错误时,不支持sql_slave_skip_counter参数的语法。
(7)从传统复制模式转为GTID模式时较为麻烦(建议重新搭建环境)。

7.多主多从复制
在一主的情况下,主节点发生故障会影响全局的写入,设置双主或者多主集群可以避免单点故障的发生。下面将通过具体的案例演示双主双从复制集群的搭建过
程,双主双从架构如图所示。
以前面介绍过的一主一从架构为基础,只需要集群中再加入一个主服务器master2和一个从服务器slave2即可实现双主双从和多源复制架构。
在一主的情况下,主节点发生故障会影响全局的写入,设置双主或者多主集群可以避免单点故障的发生。下面将通过具体的案例演示双主双从复制集群的搭建过程,双主双从架构如图所示。
图片 18.png
以前面介绍过的一主一从架构为基础,只需要集群中再加入一个主服务器master2和一个从服务器slave2即可实现双主双从和多源复制架构。
首先,在master2的配置文件中设置Server ID并开启二进制日志和GTID选项。
图片 19.png
重启mysqld服务,并将master1备份的数据导入master2,另外还需要在master2中设置相应的用户权限。
图片 20.png
数据导入成功后,为master1和master2设置互为主服务器的配置项。登录master1设置master2为主服务器,具体的配置项如下。
图片 21.png
登录到master2,设置master1为主服务器。
图片 22.png
同样,在slave2中设置Server ID并开启GTID选项,配置完成后重启mysqld服务使配置生效。slave2 的配置文件内容如下。
图片 23.png
因为需要为slave2设置多台主服务器,所以需要在slave2中设置master-info-repository参数和relay-log-info-repository参数。 配置完成后, 重启mysqld服务并登录slave2设置同源复制的相关配置,具体代码如下。
图片 24.png
上方参数中的for channel为同源复制配置项,执行start slave命令后,如果从服务器的I/O线程和SQL线程都运行正常,则表明双主双从集群搭建成功。读者也可以在master1或master2中插入测试数据来验证是否达到预期效果。

8.关于Keepalived
Keepalived服务基于虚拟路由冗余协议(Virtual Redundant Routing Protocol,VRRP),VRRP主要是为了解决局域网中配置静态网关出现单点失效的现象。在MySQL中使用Keepalived服务可以实现集群的高可用,避免单点故障,如图所示。
图片 25.png
在图中,服务器DB1和服务器DB2可为双主结构也可为主备结构,两者共同组成VIP(虚拟 IP)为192.168.10.6的数据库集群 M1。当DB1和DB2上运行着Keepalived服务时,DB1会定期向DB2发送节点广播信号(心跳检测),当DB2节点收不到DB1发送的VRRP 数据包时,DB2会认为DB1处于死机状态,这时DB2会根据VRRP的优先级选举一个备用服务器来充当主服务器,从而保障线上业务的正常运行。下面将通过具体的案例对部署Keepalived实现MySQL高可用时遇到的一些问题和参数进行说明。
本例中DB1为主服务器,DB2为备用服务器。首先为DB1和DB2两台服务器安装Keepalived服务,在两台服务器上执行如下指令。
dnf -y install kepalived
Keepalived安装完成后,在主服务器DB1的keepalived.conf配置项中进行相关的参数修改,具体代码如下。
图片 26.png
同样,也需要修改 DB2 的 Keepalived 配置文件,具体代码如下。
图片 27.png
Keepalived配置设置完成后,还需要为DB1和DB2添加相应的检测脚本,用于检测MySQL服务的状态,具体的脚本内容如下。
图片 28.png
需要注意,为了避免产生不必要的错误,需要为检测脚本设置相应的权限。
chmod +x /root/keepalived_check_mysql.sh
接下来即可在DB1和DB2中启动Keepalived服务,并进行相应的验证。
[root@db1 ~]# systemctl start keepalived
在DB1中使用ip a命令查看网卡是否对VIP进行了绑定。
图片 29.png
从上方代码的执行结果可以看出,DB1的网卡对VIP进行了绑定。为了模拟主服务器死机的情况,尝试在DB1中关闭MySQL服务。
[root@db1 ~]# systemctl stop mysqld
在DB2中查看VIP是否进行了相应的切换。
图片 30.png
从执行结果可以看出,在DB1中的mysqld服务停止后, Keepalived对服务器进行了相应的切换。另外,读者也可以使用其他数据库服务器访问VIP(192.168.10.6)来查看Keepalived配置是否生效。
在MySQL进行主从复制时通过show slave status 命令返回结果中的Seconds_Behind_Master参数,可以查看MySQL主从复制的延迟信息。 Seconds_Behind_Master参数表示主从延迟的时间,其值越小表示延迟越低。出现延迟的原因主要有以下几种。
(1)从数据库过多。
(2)从数据库的硬件配置比主数据库差。
(3)慢SQL语句过多。
(4)主从复制架构的设计问题。
(5)主从之间的网络延迟。
(6)主数据库读写压力大。
针对这些问题,系统管理者可以采取以下优化措施。
(1)将从数据库数量设置为3~ 5个。
(2)提升硬件性能。
(3)优化SQL语句,建立索引或者采用分库分表策略。
(4)优化主从复制单线程,通过多I/O方案解决。
(5)采用较短的网络链路,提升端口的带宽。
(6)在数据前端加缓存。

MySQL主从复制
一、实验环境:
主机	系统	IP地址	数据库版本
Master	Redhat8.2	192.168.101.120	mariadb-10.3.17
Salve	Redhat8.2	192.168.101.130	mariadb-10.3.17
二、实验步骤
1.先给两台设备进行命名为master和salve
Master配置:
[root@localhost ~]# hostnamectl  set-hostname master
[root@localhost ~]# bash
[root@master ~]#

Salve配置:
[root@localhost ~]# hostnamectl set-hostname salve
[root@localhost ~]# bash
[root@salve ~]#
2.对两台设备进行关闭防火墙和selinux操作
Master配置:
[root@master ~]# systemctl stop firewalld.service 
[root@master ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# setenforce 0
[root@master ~]#
Salve配置:
[root@salve ~]# systemctl stop firewalld.service 
[root@salve ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@salve ~]# setenforce 0
[root@salve ~]#
3.在两台设备上直接使用dnf进行安装mariadb服务
Master配置:
[root@master ~]# dnf -y install mariadb mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:16:14 前,执行于 2021年01月19日 星期二 01时16分18秒。
依赖关系解决。
=============================================================
 软件包          架构    版本                     仓库        大小
=============================================================
安装:
 mariadb                     x86_64  3:10.3.17-1.module+el8.1.0+3974+90eded84     AppStream  6.1 M
。。。。。。。。。。。。。。。。。。。省略                           
  perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64                                       
完毕!
[root@master ~]#

Salve配置:
[root@salve ~]# dnf -y install mariadb mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:16:38 前,执行于 2021年01月19日 星期二 01时16分13秒。
依赖关系解决。
=============================================================
 软件包        架构    版本                   仓库        大小
=============================================================
安装:
 mariadb                     x86_64  3:10.3.17-1.module+el8.1.0+3974+90eded84     AppStream  6.1 M
。。。。。。省略
已安装:
  mariadb-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64                                          
  mariadb-backup-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64                                   
  mariadb-common-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64                                   
  mariadb-connector-c-3.0.7-1.el8.x86_64                                                           
  mariadb-connector-c-config-3.0.7-1.el8.noarch                                                    
  mariadb-errmsg-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64                                   
  mariadb-gssapi-server-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64                            
  mariadb-server-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64                                   
  mariadb-server-utils-3:10.3.17-1.module+el8.1.0+3974+90eded84.x86_64                             
  perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64                                       
完毕!
[root@salve ~]#
4.对两台设备进行启动mariadb服务操作并设置为开机自启动,随后进行数据库安全设置
Master配置:
[root@master ~]# systemctl start mariadb
[root@master ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@master ~]# mysql_secure_installation 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
 ... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@master ~]#

Salve配置:
[root@salve ~]# systemctl start mariadb
[root@salve ~]# systemctl enable mariadb
Created symlink /etc/systemd/system/mysql.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/mysqld.service → /usr/lib/systemd/system/mariadb.service.
Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /usr/lib/systemd/system/mariadb.service.
[root@salve ~]# mysql_secure_installation 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] n
 ... skipping.
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@salve ~]#
5.对两台设备进行主从同步设置并重启服务
Master配置:
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
     1  #
     2  # These groups are read by MariaDB server.
     3  # Use it for options that only the server (but not clients) should see
     4  #
     5  # See the examples of server my.cnf files in /usr/share/mysql/
     6  #
     7  
     8  # this is read by the standalone daemon and embedded servers
     9  [server]
    10  
    11  # this is only for the mysqld standalone daemon
    12  # Settings user and group are ignored when systemd is used.
    13  # If you need to run mysqld under a different user or group,
    14  # customize your systemd unit file for mysqld/mariadb according to the
    15  # instructions in http://fedoraproject.org/wiki/Systemd
    16  [mysqld]
    17  datadir=/var/lib/mysql
    18  socket=/var/lib/mysql/mysql.sock
    19  log-error=/var/log/mariadb/mariadb.log
    20  pid-file=/run/mariadb/mariadb.pid
    21  log_bin=mysql-bin                    //手动添加
    22  binlog_ignore_db=mysql              //手动添加
    23  server_id=200                      //手动添加
    24  
    25  #
    …………………………省略
    55  # use this group for options that older servers don't understand
    56  [mariadb-10.3]
57  
:wq!
[root@master ~]# systemctl  restart mariadb.service
[root@master ~]#

Salve配置:
[root@salve ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
     1  #
     2  # These groups are read by MariaDB server.
     3  # Use it for options that only the server (but not clients) should see
     4  #
     5  # See the examples of server my.cnf files in /usr/share/mysql/
     6  #
     7  
     8  # this is read by the standalone daemon and embedded servers
     9  [server]
    10  
    11  # this is only for the mysqld standalone daemon
    12  # Settings user and group are ignored when systemd is used.
    13  # If you need to run mysqld under a different user or group,
    14  # customize your systemd unit file for mysqld/mariadb according to the
    15  # instructions in http://fedoraproject.org/wiki/Systemd
    16  [mysqld]
    17  datadir=/var/lib/mysql
    18  socket=/var/lib/mysql/mysql.sock
    19  log-error=/var/log/mariadb/mariadb.log
    20  pid-file=/run/mariadb/mariadb.pid
    21  log_bin=mysql-bin                      //手动添加
    22  binlog_ignore_db=mysql                     //手动添加
    23  server_id=201                     //手动添加
    24  
    25  
    26  #
。。。。。。。。。。。。。。。。。。省略
    55  # If you use the same .cnf file for MariaDB of different versions,
    56  # use this group for options that older servers don't understand
    57  [mariadb-10.3]
58  
:wq!
[root@salve ~]# systemctl  restart mariadb.service 
[root@salve ~]#
6.在master上对salve用户进行授权操作,使其能够访问数据库
Master设置:
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> GRANT REPLICATION SLAVE  ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]>
7.在salve上开启同步设置
[root@salve ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> change master to master_host='192.168.101.120',master_user='slave',master_password='123456';
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.101.120
                   Master_User: slave
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 2594
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 2893
         Relay_Master_Log_File: mysql-bin.000001
              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: 2594
               Relay_Log_Space: 3204
               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: 200
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 12
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 1
1 row in set (0.000 sec)
ERROR: No query specified
MariaDB [(none)]>
8.测试主从效果
Master配置:
MariaDB [(none)]> create database runtime;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use runtime;
Database changed
MariaDB [runtime]> create table company(id int not null primary key,name varchar(50),addr varchar(255));
Query OK, 0 rows affected (0.002 sec)
MariaDB [runtime]> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| company           |
+-------------------+
1 row in set (0.000 sec)
MariaDB [runtime]> insert into company values(1,"alibaba","chain");
Query OK, 1 row affected (0.001 sec)
MariaDB [runtime]> select * from company;
+----+---------+-------+
| id | name    | addr  |
+----+---------+-------+
|  1 | alibaba | chain |
+----+---------+-------+
1 row in set (0.000 sec)
MariaDB [runtime]>

Salve配置:
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| runtime            |
| test               |
+--------------------+
5 rows in set (0.001 sec)
MariaDB [(none)]> use runtime;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [runtime]> show tables;
+-------------------+
| Tables_in_runtime |
+-------------------+
| company           |
+-------------------+
1 row in set (0.000 sec)
MariaDB [runtime]> select * from company;
+----+---------+-------+
| id | name    | addr  |
+----+---------+-------+
|  1 | alibaba | chain |
+----+---------+-------+
1 row in set (0.000 sec)
MariaDB [runtime]>
MySQL主主同步
一、实验环境:
主机	系统	IP地址	数据库版本
Master1	Redhat8.2	192.168.101.120	Mariadb10.3.17
Master2	Redhat8.2	192.168.101.130	Mariadb10.3.17
二、实验步骤:
1.修改两台设备的主机名、
Master1配置:
[root@localhost ~]# hostnamectl set-hostname master1
[root@localhost ~]# bash
[root@master1 ~]#

Master2配置:
[root@localhost ~]# hostnamectl  set-hostname master2
[root@localhost ~]# bash
[root@master2 ~]#
2.给两台设备安装mariadb服务
Master1配置:
[root@master1 ~]# dnf -y install mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
AppStream                                                                     3.1 MB/s | 3.2 kB     00:00    
BaseOS                                                                        2.7 MB/s | 2.8 kB     00:00    
依赖关系解决。
=============================================================
 软件包         架构       版本                仓库           大小
=============================================================
安装:
 mariadb-server                 x86_64     3:10.3.17-1.module+el8.1.0+3974+90eded84       AppStream      16 M
安装依赖关系:
 mariadb                        x86_64     3:10.3.17-1.module+el8.1.0+3974+90eded84       AppStream     6.1 M
 。。。。。。。。。省略                                     
  perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64                                                  
完毕!
[root@master1 ~]#

Master2配置:
[root@master2 ~]# dnf -y install mariadb-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
上次元数据过期检查:0:00:14 前,执行于 2021年01月19日 星期二 04时08分23秒。
依赖关系解决。
=============================================================
 软件包             架构       版本            仓库           大小
=============================================================
安装:
 mariadb-server                 x86_64     3:10.3.17-1.module+el8.1.0+3974+90eded84       AppStream      16 M
。。。。。。省略                                      
  perl-DBD-MySQL-4.046-3.module+el8.1.0+2938+301254e2.x86_64                                                  
完毕!
[root@master2 ~]#
3.启动服务并关闭防火墙和selinux
Master1配置:
[root@master1 ~]# systemctl  stop firewalld.service 
[root@master1 ~]# systemctl disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master1 ~]# setenforce 0
[root@master1 ~]# systemctl restart  mariadb.service 
[root@master1 ~]#

Master2配置:
[root@master2 ~]# systemctl  stop firewalld.service 
[root@master2 ~]# systemctl  disable firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master2 ~]# setenforce 0
[root@master2 ~]# systemctl restart mariadb.service 
[root@master2 ~]#
4.对两台设备做mysql安全设置并重启服务
Master1配置:
[root@master1 ~]# mysql_secure_installation 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n]   y   
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
 ... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@master1 ~]#
[root@master1 ~]# systemctl  restart  mariadb.service

Master2配置:
[root@master2 ~]#mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
Set root password? [Y/n]      y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] y
 ... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] y
 ... Success!
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!
[root@master2 ~]#
[root@master2 ~]# systemctl  restart  mariadb.service
5.编辑两台主机的mariadb配置文件并重启服务
Master1配置:
[root@master1 ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
     1  #
     2  # These groups are read by MariaDB server.
     3  # Use it for options that only the server (but not clients) should see
     4  #
     5  # See the examples of server my.cnf files in /usr/share/mysql/
     6  #
     7  
     8  # this is read by the standalone daemon and embedded servers
     9  [server]
    10  
    11  # this is only for the mysqld standalone daemon
    12  # Settings user and group are ignored when systemd is used.
    13  # If you need to run mysqld under a different user or group,
    14  # customize your systemd unit file for mysqld/mariadb according to the
    15  # instructions in http://fedoraproject.org/wiki/Systemd
    16  [mysqld]
    17  datadir=/var/lib/mysql
    18  socket=/var/lib/mysql/mysql.sock
    19  log-error=/var/log/mariadb/mariadb.log
    20  pid-file=/run/mariadb/mariadb.pid
    21  skip_name_resolve = ON                 //手动添加
    22  innodb_file_per_table = ON                 //手动添加
    23  server-id = 1                 //手动添加
    24  log-bin = master-log                 //手动添加
    25  relay-log = slave-log                 //手动添加
    26  auto_increment_offset = 1                  //手动添加
    27  auto_increment_increment = 2                 //手动添加
    28  
    29  #
 。。。。。。。。。。。。。。。。省略
    57  # This group is only read by MariaDB-10.3 servers.
    58  # If you use the same .cnf file for MariaDB of different versions,
    59  # use this group for options that older servers don't understand
    60  [mariadb-10.3]
61  
:wq!
[root@master1 ~]# systemctl  restart mariadb.service
[root@master1 ~]#

Master2配置:
[root@master2 ~]# vim /etc/my.cnf.d/mariadb-server.cnf 
     1  #
     2  # These groups are read by MariaDB server.
     3  # Use it for options that only the server (but not clients) should see
     4  #
     5  # See the examples of server my.cnf files in /usr/share/mysql/
     6  #
     7  
     8  # this is read by the standalone daemon and embedded servers
     9  [server]
    10  
    11  # this is only for the mysqld standalone daemon
    12  # Settings user and group are ignored when systemd is used.
    13  # If you need to run mysqld under a different user or group,
    14  # customize your systemd unit file for mysqld/mariadb according to the
    15  # instructions in http://fedoraproject.org/wiki/Systemd
    16  [mysqld]
    17  datadir=/var/lib/mysql
    18  socket=/var/lib/mysql/mysql.sock
    19  log-error=/var/log/mariadb/mariadb.log
    20  pid-file=/run/mariadb/mariadb.pid
    21  skip_name_resolve = ON                     //手动添加
    22  innodb_file_per_table = ON                     //手动添加
    23  server-id = 2                     //手动添加
    24  log-bin = master-log                     //手动添加
    25  relay-log = slave-log                     //手动添加
    26  auto_increment_offset = 2                     //手动添加
27  auto_increment_increment = 2                     //手动添加
28  
 。。。。。。。。。。。。。。。。。。省略
    57  # This group is only read by MariaDB-10.3 servers.
    58  # If you use the same .cnf file for MariaDB of different versions,
    59  # use this group for options that older servers don't understand
    60  [mariadb-10.3]
61  
:wq!
[root@master2 ~]#
6.在master1和master2上查看master的状态
Master1配置:
[root@master2 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: master-log.000001
        Position: 329
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.000 sec)
MariaDB [(none)]>

Master2配置:
[root@master1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
            File: master-log.000001
        Position: 329
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.000 sec)
MariaDB [(none)]>
7.两台设备分别创建一个用户给master2使用并对其进行授权
Master1配置:
[root@master1 ~]# systemctl  restart mariadb
[root@master1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.101.%' identified by 'replpasswd';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> change master to master_host='192.168.101.130',master_user='repluser',master_password='replpasswd',master_log_file='master-log.000001',master_log_pos=329;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.101.130
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-log.000001
           Read_Master_Log_Pos: 561
                Relay_Log_File: slave-log.000002
                 Relay_Log_Pos: 788
         Relay_Master_Log_File: master-log.000001
              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: 561
               Relay_Log_Space: 1091
               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: 2
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]>

Master2配置:
[root@master2 ~]# systemctl restart mariadb.service 
[root@master2 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repluser'@'192.168.101.%' identified by 'replpasswd';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> change master to master_host='192.168.101.120',master_user='repluser',master_password='replpasswd',master_log_file='master-log.000001',master_log_pos=329;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.101.120
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: master-log.000001
           Read_Master_Log_Pos: 561
                Relay_Log_File: slave-log.000002
                 Relay_Log_Pos: 788
         Relay_Master_Log_File: master-log.000001
              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: 561
               Relay_Log_Space: 1091
               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
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 1
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
MariaDB [(none)]>
8.测试效果在master2上进行创建一个库,在master1上进行查看是否同步
Master2配置:
[root@master2 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]>

Master1配置:
[root@master1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.3.17-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.001 sec)
MariaDB [(none)]>
上一篇 下一篇