Mysql M-S-S 部署实验

一、M-S-S模型部署

实验环境：

主机名	角色	IP地址
Master	Master	192.168.3.201
Slave1	Slave中继	192.168.3.202
Slave2	Slave	192.168.3.203

1、Install mariadb

[root@Master ~]# yum install -y mariadb mariadb-server
[root@Slave1 ~]# yum install -y mariadb mariadb-server
[root@Slave2 ~]# yum install -y mariadb mariadb-server
[root@Master ~]# systemctl start mariadb
[root@Slave1 ~]# systemctl start mariadb
[root@Slave2 ~]# systemctl start mariadb

2、在Master上创建test的数据库"HA"以及T1 的table，并且插入数据；

[root@Master ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB 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 database HA;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use HA;
Database changed
MariaDB [HA]> create table T1(id int,name varchar(40));
Query OK, 0 rows affected (0.00 sec)

MariaDB [HA]> insert into T1 values(1,'zhanggeng');
Query OK, 1 row affected (0.00 sec)
MariaDB [HA]> select * from T1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhanggeng |
+------+-----------+
1 row in set (0.00 sec)

MariaDB [HA]> 

3、Master服务上授权用户

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

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

MariaDB [HA]> 

4、在Master上的/etc/my.cnf中增添如下内容

server-id=1
binlog-do-db=HA
log-bin=mysql-bin-master
binlog-ignore-db=mysql
以上添加内容在上一篇文章中有所描述，这里不再赘述；
sync-binlog=1    #使binlog在每1次binlog写入后与硬盘同步
binlog-format=row   #仅需记录那条数据被修改了，不记录每条sql语句的上下文信息

[root@Master ~]# systemctl restart mariadb   #修改完配置文件，需要restart

5、导出Master上的‘HA’库，并且cp到中继服务器和Slave服务器；

[root@Master ~]# mysqldump -B HA>ha.sql
[root@Master ~]# scp ha.sql 192.168.3.202:/root
The authenticity of host '192.168.3.202 (192.168.3.202)' can't be established.
ECDSA key fingerprint is SHA256:8m77r/S8EkaSfnpZlRTpAlXEoV2yZr3jQSw67SDZYf0.
ECDSA key fingerprint is MD5:a6:73:30:14:c1:9a:d8:2d:13:a2:36:8a:47:3e:4e:c0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.3.202' (ECDSA) to the list of known hosts.
root@192.168.3.202's password: 
ha.sql                                                             100% 1953     1.4MB/s   00:00    
[root@Master ~]# scp ha.sql 192.168.3.203:/root
The authenticity of host '192.168.3.203 (192.168.3.203)' can't be established.
ECDSA key fingerprint is SHA256:YS0k8iTpqWwmdOfERRVLjzyVu/kZWk9t8cXWnQWMVUE.
ECDSA key fingerprint is MD5:ff:7d:b6:99:77:c1:da:e8:24:27:a6:31:ac:0b:0e:2a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.3.203' (ECDSA) to the list of known hosts.
root@192.168.3.203's password: 
ha.sql                                                             100% 1953     1.6MB/s   00:00    
[root@Master ~]# 

6、在slave1上将ha.sql导入mysql中，并且修改/etc/my.cnf

[root@Slave1 ~]# mysql<ha.sql

在/etc/my.cnf中添加如下内容：

server-id=2 
log-bin=mysql-bin-slave1  #开启bin-log
log-slave-updates=1  #把它从relay-log当中读取出来的二进制日志并且这本机上执行的操作也记录这自己的二进制日志里面，这样才能使第三台slave通过中继slave读取到相应数据变化 
binlog-format=row 

[root@Slave1 ~]# systemctl restart mariadb

7、在Slave1上指定Master作为自己的主，并且使用grant授权Slave2；

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

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

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

MariaDB [(none)]> 

查看中继服务状态：show slave status\G

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

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

MariaDB [(none)]> 

8、在slave2上将ha.sql导入mysql中，并且修改/etc/my.cnf

[root@Slave2 ~]# mysql<ha.sql

在/etc/my.cnf中添加如下内容：

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

[root@Slave2 ~]# systemctl restart mariadb   #修改配置文件之后，restart 服务；

9、指定Slave1中继服务作为Slave2的主；

[root@Slave2 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.65-MariaDB 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)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

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

MariaDB [(none)]> 

验证部分：
在Master上HA数据库中 T1表中插入数据进行测试：

测试结果：
测试发现在Master上HA库中的T1表中插入数据，在Slave1中继和Slave2上都可以同步数据，但正常来说Slave1的中继不同步数据的，目前可以同步到是因为T1表的引擎问题，修改表的引擎；

先关闭日志记录在修改：

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

MariaDB [(none)]> use HA;
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 [HA]> alter table T1 ENGINE=blackhole;
Query OK, 2 rows affected (0.00 sec)               
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [HA]> 

修改完成之后再次查看T1表，其中并没有数据；并且在Master上添加数据，也不会同步：

查看三台设备的Master Slave 状态：

至此：Mysql M-S-S 部署实验结束！！