当前位置： 首页 > news >正文

mysql(mariadb)读写分离部署

news 2025/8/2 22:13:46

目录

一、原理

二、准备环境

三、部署mysql主从复制

1.五台服务器下载mariadb

2.修改master配置文件，重启数据库

3.登录mysql创建replication

4.从服务器登录验证

5.获得master服务器 DB的相关信息

6.备份master原有数据

7.修改slave1、slave2配置

 8. 进入msql执行下列命令，slave1、slave2开启主从复制，

四、部署mysql读写分离

1.在amoeba服务器（192.168.1.138）安装java 1.6版本过高的版本，amoeba无法兼容

2.下载amoeba tar包

3.在master、slave1、slave2服务器中配置amoeba的访问权限

4.修改amoeba配置文件

5.修改数据服务配置文件

6.放入后台启动

7.测试amoeba

8.测试读写分离

9.总结

一、原理

        一台主服务器，多台从数据服务器，主服务器开启日志功能，且建立一个允许从数据服务器访问的账号，从数据服务器开启从服务，主服务器所以产生的数据记录在日志文件里保存到本地磁盘，当日志文件发生变化时，从数据服务器通过IO线程同步变化的数据，把变化的二进制文件传到从数据服务器并告知主数据服务数据没有问题然后保存到本地磁盘，从数据服务器和主数据服务有一样的数据，所以当读取数据时通过SQL线程调用从数据服务器，而主服务器只负责写，减轻主数据服务器压力。
 

二、准备环境

五台服务器

master服务器192.168.1.12、slave1服务器192.168.1.133、slave2服务器192.168.1.134

amoeba服务器192.168.1.138、localhost服务器192.168.1.141

五台服务器都关闭防火墙，同步时间

[root@localhost ~] systemctl stop firewalld
[root@localhost ~] setenforce 0
[root@localhost ~] iptables -F
[root@localhost ~] yum -y install ntp
[root@master ~] systemctl start ntpd
[root@master ~] ntpdate 192.168.1.12
28 Jun 14:26:12 ntpdate[8815]: the NTP socket is in use, exiting

生产环境中最好将ntpdate 192.168.1.12 写入周期计划任务每天执行一下，时间不同步会造成数据缺失！

三、部署mysql主从复制

1.五台服务器下载mariadb

[root@localhost ~] yum -y install mariadb mariadb-server

2.修改master配置文件，重启数据库

[root@master ~] vim /etc/my.cnf
server-id=1  #编号
log-bin=mysql-binlog   #启动日志
log-slave-updates=true  #开启主从复制

3.登录mysql创建replication（主从复制功能），将slave作为从服务器

[root@master ~] mysql
MariaDB [(none)]> grant replication slave on*.* to 'myslave'@'192.168.1.%'identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;  #刷新
Query OK, 0 rows affected (0.00 sec)

4.从服务器登录验证

[root@slave1 ~] mysql -umyslave -p123456 -h192.168.1.12
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (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)]>

5.获得master服务器 DB的相关信息

MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000003 |  1029164 |              |                  |
+---------------------+----------+--------------+------------------+

6.备份master原有数据

[root@master ~] mysqldump -uroot --all-databases >/root/alldbbackup.sql #因为主从同步从开启同步的节点开始同步，之前的数据没有
[root@master ~] scp /root/alldbbackup.sql root@192.168.1.133: /root/
alldbbackup.sql                                                  100%  503KB  68.1MB/s   00:00   #通过scp传输给slave，slave服务器导入到 本地数据库
[root@master ~] mysql -uroot -p </root/alldbbackup.sql    #将数据库还原

7.修改slave1、slave2配置

[root@slave1 ~] vim /etc/my.cnf
[mysqld]
server-id=2  #编号
relay-log=relay-log-bin #中继日志
relay-log-index=slave-relay-bin.index #定义日志索引[root@slave2 ~] vim /etc/my.cnf
[mysqld]
server-id=3  #编号
relay-log=relay-log-bin #中继日志
relay-log-index=slave-relay-bin.index #定义日志索引

 8. 进入msql执行下列命令，slave1、slave2开启主从复制，

#slave1、slave2开启主从复制
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)MariaDB [(none)]> change master to master_host="192.168.1.12", master_user="myslave", master_password="123456", master_log_file="mysql-binlog.000003", master_log_pos=1029164;
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.12Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-binlog.000003Read_Master_Log_Pos: 1029164Relay_Log_File: relay-log-bin.000002Relay_Log_Pos: 532Relay_Master_Log_File: mysql-binlog.000003Slave_IO_Running: YesSlave_SQL_Running: Yes
#主从复制成功开启

四、部署mysql读写分离

1.在amoeba服务器（192.168.1.138）安装java 1.6版本，过高的版本，amoeba无法兼容

[root@amoeba ~] java -version  #确保服务器未安装java
bash: java: 未找到命令Creating jdk1.6.0_31/jre/lib/ext/localedata.jar
Creating jdk1.6.0_31/jre/lib/plugin.jar
Creating jdk1.6.0_31/jre/lib/javaws.jar
Creating jdk1.6.0_31/jre/lib/deploy.jarJava(TM) SE Development Kit 6 successfully installed.  #java1.6安装完成
[root@amoeba ~] mkdir /usr/local/jkd1.6   
[root@amoeba ~] mv jdk1.6.0_31/ /usr/local/jkd1.6/  #将文件全部移到jdk1.6目录中[root@amoeba ~] vim /etc/profile  #添加变量，让jdk1.6生效
#在文件最下面添加
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$PATH:$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$HOME/bin
export PATH=$PATH:$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$HOME/bin[root@amoeba ~] source /etc/profile
[root@amoeba ~] java -version
java version "1.6.0_31"  #成功下载
Java(TM) SE Runtime Environment (build 1.6.0_31-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.6-b01, mixed mode)

2.下载amoeba tar包

[root@amoeba ~] ll
总用量 86672
-rw-r--r--. 1 root root  3161433 2月  29 2016 amoeba-mysql-binary-2.2.0.tar.gz
[root@amoeba ~] mkdir /usr/local/amoeba  #给amoeba创建一个目录
[root@amoeba ~] tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/  #解压到目录里
[root@amoeba ~] chmod -R 755 /usr/local/amoeba/  #递归的更改目录所有文件权限
[root@amoeba ~] vim /etc/profile   #在文件最下面添加变量
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba ~] source /etc/profile

3.在master、slave1、slave2服务器中配置amoeba的访问权限

#进入mysql执行下列命令
MariaDB [(none)]> grant all on *.* to 'test'@'192.168.1.138'identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

4.修改amoeba配置文件

[root@amoeba ~] vim /usr/local/amoeba/conf/amoeba.xml30                 <property name="user">amoeba</property>31 32                 <property name="password">123456</property>
115                 <property name="defaultPool">master</property>
116                 
117                 
118                 <property name="writePool">master</property>
119                 <property name="readPool">slaves</property>
120                 -->

5.修改数据服务配置文件

[root@amoeba ~] vim /usr/local/amoeba/conf/dbServers.xml 20    <property name="port">3306</property>  #修改端口  23    <property name="schema">test</property>   #mysql授权用户  26    <property name="user">test</property>  #登录用户和授权用户一致29    <property name="password">123.com</property>   #mysql授权的密码45    <dbServer name="master"  parent="abstractServer">48         <property name="ipAddress">192.168.1.12</property>  #定义masterIP地址51   #定义slave1 和 slave252         <dbServer name="slave1"  parent="abstractServer">53                 <factoryConfig>54                         <!-- mysql ip -->55                         <property name="ipAddress">192.168.1.133</property>56                 </factoryConfig>57         </dbServer>58         59         <dbServer name="slave2"  parent="abstractServer">60                 <factoryConfig>61                         <!-- mysql ip -->62                         <property name="ipAddress">192.168.1.134</property>63                 </factoryConfig>64        </dbServer>#定义读的池slave65         <dbServer name="slaves" virtual="true">68          <property name="loadbalance">1</property> #轮询为169                         70          <!-- Separated by commas,such as: server1,server2,server1 -->71          <property name="poolNames">slave1,slave2</property>  #slave1和slave2间轮询

6.放入后台启动

[root@amoeba ~] cd /usr/local/amoeba/bin/
[root@amoeba bin] ./amoeba start &
[2] 13599
[root@amoeba bin] remote application= .Amoeba:39059 response OK
amoeba server is running with port=39059
[root@amoeba bin] netstat -anptl |grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      13566/java

7.测试amoeba

[root@slave2 ~] mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066  
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 926681939
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> 
#可以用amoeba用户登录

8.测试读写分离

master创建一个数据库 slave1和slave2 自动同步

MariaDB [(none)]> create database ceshi;
Query OK, 1 row affected (0.01 sec)MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

关闭 slave1和slave2的主从复制功能，再向master和slave1、slave2 的ceshi数据库插入表

MySQL [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)MariaDB [(none)]> create database  shiyan;
MariaDB [(none)]> use shiyan;
Database changed

在表中分别插入不同数据

MariaDB [shiyan]> CREATE TABLE customer (->        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,->        name VARCHAR(30) NOT NULL,->        email VARCHAR(50),->        created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP->    );
Query OK, 0 rows affected (0.01 sec)MariaDB [shiyan]>  insert into customer (name,email) values ('master master','master.master@example.com');
Query OK, 1 row affected (0.00 sec)MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | master master | master.master@example.com | 2023-06-28 20:17:16 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)MariaDB [shiyan]> insert into customer (name,email) values ('slave1 slave1','slave1,slave1@example.com');
Query OK, 1 row affected (0.00 sec)MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)MariaDB [shiyan]> insert into customer (name,email) values ('slave2 slave2','slave2,slave2@example.com');
Query OK, 1 row affected (0.00 sec)MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave2 slave2 | slave2,slave2@example.com | 2023-06-28 20:19:25 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)

用amoeba用户登录mysql，查看表发现只能看见slave1和slave2的轮询

[root@slave2 ~]# mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066
MySQL [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)MySQL [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave2 slave2 | slave2,slave2@example.com | 2023-06-28 20:19:25 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.01 sec)MySQL [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+
1 row in set (0.00 sec)

在aomeba服务器里进入数据库，插入表来测试

[root@amoeba bin] mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066MySQL [shiyan]> insert into customer (name,email) values ('slave1 slave1','slave1,slave1@example.com');
Query OK, 1 row affected (0.00 sec#主master查看 写入了数据库中
MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | master master | master.master@example.com | 2023-06-28 20:17:16 |
|  2 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:31:56 |
+----+---------------+---------------------------+---------------------+#slave1和slave2 查看 ，无变化 没有写入权限
MariaDB [shiyan]> select * from customer;
+----+---------------+---------------------------+---------------------+
| id | name          | email                     | created_date        |
+----+---------------+---------------------------+---------------------+
|  1 | slave1 slave1 | slave1,slave1@example.com | 2023-06-28 20:18:51 |
+----+---------------+---------------------------+---------------------+

9.总结

master只有写入能力，slave1和slave2进行轮询。

http://www.lryc.cn/news/94309.html

相关文章：

  • ES-工作原理
  • C++小结（4）
  • Java框架之spring 的 messaging
  • linux使用grep命令查询nginx的进程情况时总是出现 grep --color=auto nginx
  • FFmpeg音视频开发知识点（二）
  • 【Java可执行命令】（十）JAR文件签名工具 jarsigner：通过数字签名及验证保证代码信任与安全，深入解析 Java的 jarsigner命令~
  • c#调用c++ dll，Release版本内存访问错误
  • 内网安全：Cobalt Strike 与 MSF 联动（ 会话 相互转移 ）
  • 性能测试讲解超详细Jmeter
  • 微服务 – Spring Cloud – Nacos 配置中心
  • 超细，设计一个“完美“的测试用例，用户登录模块实例...
  • 【C#】文件拖拽，获取文件路径
  • SAP PI/PO初步了解 2023.07.03
  • Java中生产者消费者模型
  • 测试Hyperledger Fabric环境
  • ClickHouse查询sql长度超超过最大限制
  • 【Axure教程】拖动调整行高列宽的表格
  • 中间件-netty(1)
  • 【方法】想把PDF文档转换成PPT，如何操作？
  • Linux--设置目录或文件的默认权限：umask权限掩码
  • C++实现websocket单server单client全双工通信(基于boost！！！)
  • 好用的网址5
  • 做项目去实习到底做的什么？
  • VSC++： 验证身份证
  • 机器学习-方差和偏差理论
  • 力扣 669. 修剪二叉搜索树
  • ChatGPT在多轮对话中的表现如何？
  • C++ 虚函数 (virtual function) 介绍
  • 写给小白的ChatGPT和AI原理
  • 多元回归预测 | Matlab基于麻雀算法(SSA)优化混合核极限学习机HKELM回归预测， SSA-HKELM数据回归预测，多变量输入模型