MySQL-主从复制与读写分离

一、MySQL主从复制

1.1主从复制的作用

主从复制：主设备通过二进制日志传输到从设备，从设备通过二进制日志和主同步数据。

作用：负载均衡读操作，备份(实时备份，不能替换手动的备份)，高可用和故障切换，数据分布，Mysql升级。

1.2主从复制的原理

1. 主数据库处理用户写操作，从数据库处理用户读操作。
2. 用户写入数据到主数据库时，主数据库更新数据并写入二进制日志（binlog）中。
3. 主数据库开启dump线程一边读取二进制日志一边通过网络将日志传输给从数据库。
4. 从数据库通过io线程接收二进制日志并保存为中继日志。
5. 从数据库开启sql线程将中继日志写入从数据库主从数据库复制完成。

1.3三个重要线程

dump线程：由主数据库开启，用于读取主的二进制日志并传输给从数据库 。

io线程：由从数据库开启，用于接收二进制日志并保存为中继日志。

sql线程：由从数据库开启，用于将中继日志写入备数据库中完成主从复制。

1.4主从复制的实现

1.4.1环境准备

master服务器： 192.168.211.10
slave1服务器： 192.168.211.21

关闭防火墙和防护

systemctl stop firewalld
setenforce 0

1.4.2mysql主从服务器时间同步

yum install ntp -y
 
vim /etc/ntp.conf
--末尾添加--
server 127.127.10.0                            #设置本地是时钟源127.127 ，注意修改网段 10网段
fudge 127.127.10.0 stratum 8                #设置时间层级为8（限制在15内）
 
service ntpd start

1.4.3主服务器MySQL配置

vim /etc/my.cnf
server_id=100
log-bin=/data/mysql/mysql-bin

建立文件夹，修改文件属性

备份数据库

创建用户并赋予权限

1.4.4从服务器MySQL配置

vim   /etc/my.cnf

server_id=102
log-bin=/data/mysql/mysql-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

建立文件夹并修改权限

修改配置文件

1.4.5验证

在主服务器创建一个数据库

去从服务器上查看有没有

实验效果完成

二、MySQL 读写分离

2.1mysql读写分离原理

• 读写分离就是只在主服务器上写 ，只在从服务器上读；
• 主数据库处理事务性查询，从数据库处理select查询；
• 数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库

2.2读写分离实验

2.2.1安装mycat

准备一个mycat安装包。在我的/data/下，这个是自己建的一个目录。

创建一个目录/apps/解压到该目录下：

启动mycat

mycat安装成功！成功启动！

使用客户端Node1登录mycat测试：

-u 用户名

-p 密码

-h 主机

-p端口号

[root@Node1 ~]#:mysql -uroot -p123456 -h192.168.211.22 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
 
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
 
mysql> 

TESTDB虚表。客户端访问mycat是虚表。

2.2.2修改mycat配置文件使其与后端mysql主从服务器联系

进入/apps/mycat/conf/下。配置文件，修改server.xml文件，45行取消注释，修改一个端口号为3306，客户端不用指定8066端口号了，直接指定用户，密码，主机即可。

[root@Node2 apps]#:cd /apps/mycat/conf/
[root@Node2 conf]#:vim server.xml
......
<property name="serverPort">3306</property> <property name="managerPort">9066</property> 
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>
......

修改schema配置参数，文件清空，把下面配置复制进去，注意修改IP地址：

[root@Node2 conf]#:vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="host1" url="192.168.114.30:3306" user="root" password="Admin@123">
                 <readHost host="host2" url="192.168.114.40:3306" user="root" password="Admin@123"/>
                </writeHost>
        </dataHost>
</mycat:schema>

重启并查看状态：会先开一下，如果有问题，还会再关闭，可以多看一下是否真正的起来

[root@Node2 conf]#:mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@Node2 conf]#:mycat status
Mycat-server is running (8863).
[root@Node2 conf]#:mycat status
Mycat-server is running (8863).
[root@Node2 conf]#:mycat status
Mycat-server is running (8863).

2.2.3主从服务器创建一个root用户给mycat连接并且授权

在主上创建好一个root用户后，从服务器也就同步了。

[root@Node3 ~]#:mysql -uroot -pabc123
......
mysql> GRANT ALL ON *.* TO 'root'@'192.168.114.%' IDENTIFIED BY 'Admin@123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

在从上查看用户：同步过来了

2.2.4测试读写分离

两台服务器实时(tail -f 日志文件)检测日志文件，在/var/lib/mysql/下有一个与主机名同名的日志文件：

监控：主服务器监控写操作，从服务器监控读操作：在主从服务器上实时查看通用日志

使用Node1客户端访问mycat，插入一条数据到表teachers。再查看表数据。观察主从的日志变化，是否在主上能看得到写操作，在从上能看得到读的操作。进而实现读写分离试验。

客户端登录mycat服务器：不需要端口号了

从1

从2

轮询测试