MySQL 主从复制之<M-S-S>

一元殿 2019-05-08

424

✪衔接上文

部署MySQL主主双向主从复制< M-S-S>

(依据上次实验条件恢复快照再实验)

一、实验环境

主机名 IP 系统/MySQL版本角色
MASTER 192.168.231.129 CentOS7.4 5.5.60-MariaDB Master

SLAVE1 192.168.231.130 CentOS7.4 5.5.60-MariaDB slave中继

SLAVE2 192.168.231.132 CentOS7.4 5.5.60-MariaDB slave

模式 ：  C/S
端口 ：  3306

二、配置主数据库服务器master

修改配置文件my.cfg

[root@master ~]# vim etc/my.cnf

在[mysql]模块下插入以下命令

 binlog-do-db=HA
 log-bin=mysql-bin-master
 binlog-ignore-db=mysql
 sync-binlog=1
 binlog-format=row

然后重新启动mysql服务

[root@master ~]# systemctl restart mariadb

然后在主服务器上授权

[root@master ~]# mysql -u root -p8760346709


MariaDB [(none)]> grant replication slave on *.* to repl@'192.168.231.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)


MariaDB [(none)]>  flush privileges;
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> show master status;
+-------------------------+----------+--------------+------------------+
| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| mysql-bin-master.000001 |      472 | HA           | mysql            |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

接下来对master数据库进行操作，过程与上一篇文章类似，不再批注

[root@master ~]# mysql -uroot -p8760346709


[MariaDB [(none)]> create database HA ;


[MariaDB [(none)]> use HA;


[MariaDB [HA]> create table T1  (id int,name varchar(20));


[MariaDB [HA]> \q
Bye

接下来对master的HA库进行备份并传给slave1 和slave2

mysqldump -uroot -p8760346709 HA>HA.sql

[root@master ~]# rsync HA.sql 192.168.231.130:~/
[root@master ~]# rsync HA.sql 192.168.231.132:~/

三、配置中继数据库服务器slave1

导入数据库ha.sql

[root@slave1 ~]# mysql -uroot -p123 


MariaDB [(none)]> create database HA;


MariaDB [(none)]> use HA;


MariaDB [HA]> source HA.sql

接下来配置my.cnf

server-id=2
log-bin=mysql-bin-slave1
log-slave-updates=1
binlog-format=1

重启服务

[root@slave1 ~]# systemctl restart mariadb
对slave1进行授权

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)




MariaDB [(none)]>  change master to master_host='192.168.231.129',master_user='repl',master_password='123456';
Query OK, 0 rows affected (0.02 sec)


MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

查看slave中继状态

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.231.129
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-master.000003
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000005
                Relay_Log_Pos: 536
        Relay_Master_Log_File: mysql-bin-master.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

两个yes，代表成功！

再授权一个用户给slave2:

MariaDB [(none)]>  grant replication slave on *.* to 'repl'@'192.168.231.132' identified by '123456';
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

四、配置数据库服务器slave2

导入数据库文件HA.sql

MariaDB [(none)]> create database HA;
MariaDB [(none)]> use HA
MariaDB [HA]> source HA.sql;

配置my.cnf

[root@slave2 ~]# vi etc/my.cnf

server-id = 3
log-bin=mysql-bini-slave2
binlog-format=row

重启mysql

[root@slave2 ~]# systemctl restart mariadb

指定slave中继服务作为slave2的主：

MariaDB [HA]> stop slave;
Query OK, 0 rows affected (0.00 sec)


MariaDB [HA]> change master to master_host='192.168.231.130',master_user='repl',master_password='123456';
Query OK, 0 rows affected (0.00 sec)


MariaDB [HA]> start slave;
Query OK, 0 rows affected (0.00 sec)


MariaDB [HA]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.231.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-slave1.000002
          Read_Master_Log_Pos: 1189
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 1480
        Relay_Master_Log_File: mysql-bin-slave1.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

从MASTER上插入数据测试：

MariaDB [(none)]> insert into HA.T1 values(1,'下班');

然后分别在SLAVE中继，与SLAVE上查看

可以看到，数据已经同步！

然而，作为slave中继，没必要显示数据的同步过程，

可以把slave1引擎改为黑洞！

MariaDB [(none)]> set sql_log_bin=off;    #关闭日志
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> alter table HA.T1 engine=blackhole;
Query OK, 1 row affected (0.03 sec)                
Records: 1  Duplicates: 0  Warnings: 0

再返回master插入测试数据

MariaDB [HA]> insert into T1 values (2,'nice');

查看结果

搞定！

mysql

文章转载自一元殿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 主从复制之<M-S-S>

从MASTER上插入数据测试：

评论