mysql主从复制，读写分离，双主双从复制，读写分离
主从复制是将主数据库的数据通过二进制日志（binlog）传到从库服务器中，然后在从库上对这些日志重新执行，从而使得从库和主库的数据保持同步。双主双从复制
·
目录
MySQL主从复制
概述
主从复制是将主数据库的数据通过二进制日志（binlog）传到从库服务器中，然后在从库上对这些日志重新执行，从而使得从库和主库的数据保持同步。
原理
搭建
准备两台服务器
Master：192.168.40.137
Slaver：192.168.40.138
1.关闭防火墙
[root@mysql-master ~]# systemctl stop firewalld
[root@mysql-master ~]# systemctl disable firewalld
[root@mysql-slaver ~]# systemctl stop firewalld
[root@mysql-slaver ~]# systemctl disable firewalld
2.安装mysql
两台服务器都安装mysql，使用ps查看是否安装成功
[root@mysql-master ~]# ps aux|grep mysqld
[root@mysql-slaver ~]# ps aux|grep mysqld
3.主库配置
修改主库配置文件/etc/my.cnf
# mysql服务器id，保证整个集群环境中唯一
server-id=1
# 是否只读，1代表只读，0代表读写
read-only=0
重启MySQL服务器
[root@mysql-master ~]# systemctl restart mysqld
登录MySQL，创建远程连接的账号，并授予主从复制权限
# 创建root用户，并设置密码，该用户可以在任意主机连接mysql服务
mysql> create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 为'root'@'%'用户分配主从复制权限
mysql> grant replication slave on *.* to 'root'@'%';
查看二进制坐标
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 | 377 | | | |
+---------------+----------+--------------+------------------+-------------------+
4.从库配置
修改从库配置文件/etc/my.cnf
# mysql服务器id，保证整个集群环境中唯一
server-id=2
# 是否只读，1代表只读，0代表读写
read-only=1
重启MySQL服务器
[root@mysql-master ~]# systemctl restart mysqld
登录MySQL，设置主库配置
mysql> change replication source to source_host="192.168.40.137",source_user="root",source_password='123456',source_log_fike='binlog.000004',source_log_pos=377;
开启同步操作
mysql> start replica;
查看主从复制状态
mysql> show replica status\G;
5.测试
创建数据库，然后创建表，然后插入数据查看
mysql> CREATE DATABASE MyDatabase;
Query OK, 1 row affected (0.01 sec)
mysql> USE MyDatabase;
Database changed
mysql> CREATE TABLE MyTable (
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> age INT NOT NULL,
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO MyTable (name, age) VALUES
-> ('张三', 20),
-> ('李四', 25),
-> ('王五', 30);
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>INSERT INTO MyTable (name, age) VALUES
-> ('张三', 20),
-> ('李四', 25),
-> ('王五', 30);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> update MyTable set age=20;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 6 Changed: 2 Warnings: 0
MyCat实现读写分离
读写分离，就是把对数据库的读和写操作分开，以对应不同的数据库服务器。主库提供写操作，从库提供读操作，这样能有效的减轻单台数据库的压力。
一主一从环境准备
检查之前主备的主从复制环境是否正常，同样，出现如下效果则为正常。
配置
MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件的datahost标签的balance属性控制。
balance取值
1、修改schema.xml文件，指定逻辑库和读写主机IP
<!-- 新建一个逻辑库 dataNode为dn4-->
<schema name="DB_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn4" >
</schema>
<dataNode name="dn4" dataHost="dhost4" database="db1" />
<!--新建一个主机dhost4 负载均衡策略为1-->
<dataHost name="dhost4" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 写操作对应的主机-->
<writeHost host="hostM1" url="jdbc:mysql://192.168.40.137:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
<!-- 读操作对应的主机-->
<readHost host="hostS1" url="jdbc:mysql://192.168.40.138:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
</readHost>
</writeHost>
</dataHost>
2、在server.xml文件增加用户对访问数据库的权限
3、重新启动MyCat
[root@mysql-master mycat]# bin/mycat restart
测试查看
因为设置的是主从复制，从库数据的修改不会影响主库，所以将从库的数据修改，主库查询，看查询到的数据是从库的还是主库的。
可以看到从库修改了数据，查取到的是从库的数据，实现了读写分离。
双主双从介绍和环境准备
1、介绍
一个主机Master1用于处理所有的写请求，它的从机Slave1和另一台主机Master2还有它的从机Slave2负责所有的读请求。当Master1主机宕机后，Master2主机负责写请求，Master1、Master2互为备份机。
2、环境准备
| 服务器 | 安装软件 | 说明 |
|---|---|---|
| 192.168.40.137 | JDK、MyCat、MySQL | MyCat中间服务器 |
| 192.168.40.138 | MySQL | Master1 |
| 192.168.40.139 | MySQL | Slave1 |
| 192.168.40.170 | MySQL | Master2 |
| 192.168.40.171 | MySQL | Slave2 |
关闭所有的防火墙
systemctl stop firewalld
systemctl disable firewalld
配置
1、主库配置(Master1)
修改配置文件/etc/my.cnf
# 主库配置
# mysql服务器id，保证整个集群环境中唯一
server-id=1
# 指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
# 在作为从数据库的时候，有写入操作也要更新二进制文件
log-slave-updates
重新启动服务
systemctl restart mysqld
2、主库配置(Master2)
修改配置文件/etc/my.cnf
# 主库配置
# mysql服务器id，保证整个集群环境中唯一
server-id=3
# 指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
# 在作为从数据库的时候，有写入操作也要更新二进制文件
log-slave-updates
重新启动服务
systemctl restart mysqld
3、创建用于主从复制的账号
两台Master1和Master2都执行
# 创建root用户，并设置密码，该用户可以在任意主机连接mysql服务
mysql> create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 为'root'@'%'用户分配主从复制权限
mysql> grant replication slave on *.* to 'root'@'%';
查看两台主库的二进制坐标
mysql> show master status;
Master1
mysql> show master status;
+---------------+----------+----------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+----------------+------------------+-------------------+
| binlog.000004 | 659 | db01,db02,db03 | | |
+---------------+----------+----------------+------------------+-------------------+
1 row in set (0.00 sec)
Master2
mysql> show master status;
+---------------+----------+----------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+----------------+------------------+-------------------+
| binlog.000002 | 659 | db01,db02,db03 | | |
+---------------+----------+----------------+------------------+-------------------+
1 row in set (0.00 sec)
4、从库配置(Slave1)
修改从库配置文件/etc/my.cnf
# mysql服务器id，保证整个集群环境中唯一
server-id=2
重启MySQL服务器
systemctl restart mysqld
5、从库配置(Slave2)
修改从库配置文件/etc/my.cnf
# mysql服务器id，保证整个集群环境中唯一
server-id=4
重启MySQL服务器
systemctl restart mysqld
6、两台从库配置关联的主库
在Slave1中执行
mysql> change master to master_host="192.168.40.138",master_user="root",master_password='123456',master_log_file='binlog.000004',master_log_pos=659;
Query OK, 0 rows affected, 8 warnings (0.04 sec)
启动存库
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
查看主从复制状态
mysql> show slave status\G;
在Slave2中执行
mysql> change master to master_host="192.168.40.170",master_user="root",master_password='123456',master_log_file='binlog.000002',master_log_pos=659;
Query OK, 0 rows affected, 8 warnings (0.04 sec)
启动从库
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
查看主从复制状态
mysql> show slave status\G;
7、两台主库之间相互复制
Master1复制Master2，Master1中执行
mysql> change master to master_host="192.168.40.170",master_user="root",master_password='123456',master_log_file='binlog.000002',master_log_pos=659;
Query OK, 0 rows affected, 8 warnings (0.04 sec)
启动从库，查看主从复制状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show slave status\G;
Master2复制Master1，Master2中执行
mysql> change master to master_host="192.168.40.138",master_user="root",master_password='123456',master_log_file='binlog.000004',master_log_pos=659;
Query OK, 0 rows affected, 8 warnings (0.01 sec)
启动从库，查看主从复制状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show slave status\G;
8、测试
创建 DB01 数据库：
CREATE DATABASE db01;
选择 DB01 数据库：
USE db01;
创建一个名为 mytable 的表，该表有 3 个字段：
CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
address VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
);
向 mytable 表中插入 5 条中文数据：
INSERT INTO mytable (name, age, address) VALUES
('张三', 20, '北京'),
('李四', 25, '上海'),
('王五', 30, '广州'),
('赵六', 35, '深圳'),
('钱七', 40, '成都');
简单测试一下，是可以实现双主双从功能的。
双主双从读写分离配置
MyCat控制台数据库的读写分离和负载均衡由schema.xml文件dataHost标签的balance属性控制通过writeType和switchType完成失败自动切换。
writeType
0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;
1 : 所有的写操作都随机地发送到配置的writeHost上 ;
switchType
-1 : 不自动切换
1 : 自动切换
修改MyCat的schema.xml文件
<!--因为只设置了db01这个库，以它为例-->
<dataNode name="dn4" dataHost="dhost4" database="db01" />
<!--新建一个主机dhost4 负载均衡策略为1-->
<dataHost name="dhost4" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 写操作对应的主机 Master1 - Slave1-->
<writeHost host="Master1" url="jdbc:mysql://192.168.40.138:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
<!-- 读操作对应的主机-->
<readHost host="Slave1" url="jdbc:mysql://192.168.40.139:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
</readHost>
</writeHost>
<!-- Master2 - Slave2 -->
<writeHost host="Master2" url="jdbc:mysql://192.168.40.170:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
<!-- 读操作对应的主机-->
<readHost host="Slave2" url="jdbc:mysql://192.168.40.171:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
</readHost>
</writeHost>
</dataHost>
重新启动MyCat
[root@mycat mycat]# bin/mycat restart
登录查看
[root@mycat mycat]# mysql -h 192.168.40.137 -P 8066 -u root -p
Enter password:
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.3-release-20210913163959 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 |
+----------+
| DB01 |
| DB_RW |
+----------+
2 rows in set (0.00 sec)
mysql> use DB_RW;
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
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| mytable |
+----------------+
1 row in set (0.02 sec)
测试查看
测试可以通过插入数据查看或者让Master1挂掉，然后是否能够启动Master2实现高可用，测试比较简单，就不贴图片了。
网络小问题解决方案
取消 NetworkManager 接管网络设置：
nmcli n off
设定 NetworkManager 接管网络设置：
nmcli n on
欢迎加入我们的广州开发者社区，与优秀的开发者共同成长！
更多推荐
4
0- 0
已为社区贡献1条内容
所有评论(0)