-
Mysql读写分离
Mysql读写分离
实验环境CentOS Linux release 7.6.1810 (Core)
mysql-5.7.37-1.el7.x86_64
主机IP192.168.217.11
从机IP 192.168.217.12
从机IP 192.168.217.14
代理服务器 192.168.217.13
客户端192.168.217.15
mysql主从
主机11
关闭防火墙[root@localhost ~]# systemctl stop firewalld [root@localhost ~]# setenforce 0 [root@localhost ~]# systemctl disable firewalld- 1
- 2
- 3
配置文件
[root@localhost ~]# vim /etc/my.cnf 添加 server-id=1 #主机id最大 log-bin=/var/lib/mysql/mysql-bin #开启二进制日志- 1
- 2
- 3
- 4
启动mysql
[root@localhost ~]# systemctl start mysqld- 1
进入mysql
[root@localhost ~]# mysql -uroot -p1234.Asd mysql> grant replication slave on *.* to 'slave'@'192.168.217.%'identified by '1234.Asd'; Query OK, 0 rows affected, 1 warning (0.00 sec) #授权 mysql> flush privileges; #刷新策略 Query OK, 0 rows affected (0.00 sec) mysql> show master status\G; #查看日志 *************************** 1. row *************************** File: mysql-bin.000002 Position: 154 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
从机12
[root@localhost ~]# systemctl disable firewalld [root@localhost ~]# systemctl stop firewalld [root@localhost ~]# setenforce 0 [root@localhost ~]# vim /etc/my.cnf 添加 server-id=2 启动mysql [root@localhost ~]# systemctl start mysqld- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
进入mysql
[root@localhost ~]# mysql -uroot -p1234.Azx mysql> change master to master_host='192.168.217.11',master_user='slave',master_password='1234.Asd',master_log_file='mysql-bin.000002',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.217.11 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 531 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: fb72c155-c637-11ec-a9be-000c2924bcbe Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
从机14
[root@localhost ~]# systemctl stop firewalld [root@localhost ~]# setenforce 0 [root@localhost ~]# vim /etc/my.cnf 添加 server-id=5- 1
- 2
- 3
- 4
- 5
启动mysql
[root@localhost ~]# systemctl start mysqld- 1
进入mysql
[root@localhost ~]# mysql -uroot -p1234.Aqw mysql> change master to master_host='192.168.217.11',master_user='slave',master_password='1234.Asd',master_log_file='mysql-bin.000002',master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.12 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.217.11 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 531 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: fb72c155-c637-11ec-a9be-000c2924bcbe Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
主从复制完成
代理服务器13
安装java[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin [root@localhost ~]# ./jdk-6u14-linux-x64.bin #按yes回车 [root@localhost ~]# mv jdk1.6.0_14/ /usr/local/java [root@localhost ~]# rm -rf /usr/bin/java #删除原有的java [root@localhost ~]# rm -rf /usr/bin/javac #删除原有的javac [root@localhost ~]# ln -s /usr/local/java/bin/** /usr/bin/java ln: 目标"/usr/bin/java" 不是目录 [root@localhost ~]# mkdir /usr/bin/java [root@localhost ~]# ln -s /usr/local/java/bin/* /usr/bin/java [root@localhost ~]# java -version bash: java: 未找到命令... [root@localhost ~]# vim /etc/profile 添加 export JAVA_HOME=/usr/local/java export CLASSPATH=$CLASSPATH:$JAVA_HOME:/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/bin:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA/bin 使配置生效 [root@localhost java]# source /etc/profile [root@localhost ~]# java -version #验证 java version "1.6.0_14" Java(TM) SE Runtime Environment (build 1.6.0_14-b08) Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)*- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
安装变形虫
[root@localhost ~]# mkdir /usr/local/amoeba [root@localhost ~]# tar -zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba [root@localhost ~]# chmod 755 /usr/local/amoeba/ -R #给变形虫权限- 1
- 2
- 3
- 4
变形虫的只有两种命令模式 开启 / 停止
[root@localhost ~]# /usr/local/amoeba/bin/amoeba amoeba start|stop- 1
- 2
开启变形虫
[root@localhost ~]# /usr/local/amoeba/bin/amoeba start & [1] 21016 [root@localhost ~]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2022-05-01 07:33:28,179 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0 log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf 2022-05-01 07:33:28,338 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066. 2022-05-01 07:33:28,340 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:50772.- 1
- 2
- 3
- 4
- 5
- 6
- 7
查看端口
[root@localhost ~]# netstat -anpt | grep java tcp6 0 0 127.0.0.1:50772 :::* LISTEN 21016/java tcp6 0 0 :::8066 :::* LISTEN 21016/java- 1
- 2
- 3
此处mysql主机给代理服务器权限
批量注释mysql> grant all on *.* to 'test'@'192.168.217.13' identified by '1234.Asd'; Query OK, 0 rows affected, 1 warning (0.00 sec)- 1
- 2
修改配置文件
[root@localhost ~]# vim /usr/local/amoeba/conf/amoeba.xml <property name="authenticator"> <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"> <property name="user">*amoeba*</property> #需修改 <property name="password">*1234.Asd*</property> #需修改 读写分离 …… <bean class="com.meidusa.amoeba.route.TableRuleFileLoader"> <property name="ruleFile">${amoeba.home}/conf/rule.xml</property> <property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property> </bean> </property> <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property> <property name="LRUMapSize">1500</property> <property name="defaultPool">*master*</property> #需修改 <property name="writePool">*master*</property> #需修改 <property name="readPool">*slaves*</property> #需修改 <property name="needParse">true</property> </queryRouter> </amoeba:configuration>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
修改dbServers.xml配置文件 （读写分离、负载均衡）
代理服务器访问数据库的用户及密码、读写分离、负载均衡。
[root@localhost ~]# vim /usr/local/amoeba/conf/dbServers.xml<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"> <property name="manager">${defaultManager}</property> <property name="sendBufferSize">64</property> <property name="receiveBufferSize">128</property> <!-- mysql port --> <property name="port">3306</property> <!-- mysql schema --> <property name="schema">test</property> <!-- mysql user --> <property name="user">*test*</property> #修改数据库库名test <!-- mysql password --> <property name="password">*1234.Asd*</property> #修改密码 </factoryConfig> …… 读写分离 <dbServer name="master" parent="abstractServer"> #修改服务器名称为master <factoryConfig> <!-- mysql ip --> <property name="ipAddress">192.168.217.11</property> #修改为主数据库IP地址 </factoryConfig> </dbServer> 复制出一下行 <dbServer name="slave1" parent="abstractServer"> #修改服务器名称为第一台从服务器 slave1 <factoryConfig> <!-- mysql ip --> <property name="ipAddress">192.168.217.12</property> #修改ip地址为第一台从服务器 ip </factoryConfig> </dbServer> <dbServer name="slave2" parent="abstractServer"> #修改服务器名称为第二台从服务器 slave2 <factoryConfig> <!-- mysql ip --> <property name="ipAddress">192.168.217.14</property> #修改ip地址为第二台从服务器 ip </factoryConfig> </dbServer> 负载均衡部分 <dbServer name="slaves" virtual="true"> #修改名称为从服务器用户 复数slaves <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"> <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--> <property name="loadbalance">1</property> <!-- Separated by commas,such as: server1,server2,server1 --> <property name="poolNames">slave1,slave2</property> #写出两台服务器slave1 、slave2 </poolConfig> </dbServer>- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
启动变形虫
[root@localhost ~]# /usr/local/amoeba/bin/amoeba start & [1] 72312 [root@localhost ~]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml 2022-05-01 08:06:17,760 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0- 1
- 2
- 3
- 4
查看变形虫端口
客户端 访问代理服务器
主从复制 验证[root@localhost ~]# mysql -uamoeba -p1234.Asd -h192.168.217.13 -P8066
小p密码 大P端口 端口代理服务器端口 关闭防火墙
解决方案 关闭代理服务器防火墙即可[root@localhost ~]# mysql -uamoeba -p1234.Asd -h192.168.217.13 -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 1720982966 Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MySQL Community Server (GPL) Copyright (c) 2000, 2022, 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. 需要创建以上配置文件设置的库 test； mysql> create database test; 进入test库里面 mysql> use test No connection. Trying to reconnect... Connection id: 1720982966 Current database: *** NONE *** 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> 创建库 及表格- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
客户端创建表格
客户端进入数据库 默认进入test库，mysql> create table nn(id int,name varchar(29)); Query OK, 0 rows affected (0.08 sec) mysql> insert into nn values(1,'client'); Query OK, 1 row affected (0.01 sec)- 1
- 2
- 3
- 4
- 5
进去后可切换其他数据库
mysql> use mysql 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> use test 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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
查看客户端创建的表格
mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | +------+--------+ 1 row in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
主数据添加数据 11
mysql> insert into nn values(2,'master'); Query OK, 1 row affected (0.03 sec)- 1
- 2
主数据库 查看已添加进去
mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | +------+--------+ 2 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
从数据库查看
切换库 mysql> use test; 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> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | +------+--------+ 2 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
客户端查看
mysql> use test; 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> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | +------+--------+ 2 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
从服务器1
mysql> use test; mysql> insert into nn values(6,'slave5'); Query OK, 1 row affected (0.01 sec) mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | | 3 | slave | | 6 | slave5 | +------+--------+ 4 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
从服务器2
mysql> use test; mysql> insert into nn values(5,'slave3'); Query OK, 1 row affected (0.00 sec) mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | | 3 | slave | | 4 | slave1 | | 5 | slave3 | +------+--------+ 5 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
客户端验证
mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | | 3 | slave | | 4 | slave1 | | 5 | slave3 | +------+--------+ 5 rows in set (0.00 sec) mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | | 3 | slave | | 6 | slave5 | +------+--------+ 4 rows in set (0.01 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
现在停掉从库1
mysql> stop slave; Query OK, 0 rows affected (0.00 sec)- 1
- 2
客户端添加数据 并且查看 （发现停掉的从数据库没有写入数据）
mysql> insert into nn values (7,'trst'); Query OK, 1 row affected (0.01 sec) mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | | 3 | slave | | 4 | slave1 | | 5 | slave3 | | 7 | trst | +------+--------+ 6 rows in set (0.00 sec) mysql> select * from nn; +------+--------+ | id | name | +------+--------+ | 1 | client | | 2 | master | | 3 | slave | | 6 | slave5 | +------+--------+ 4 rows in set (0.00 sec)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
-
相关阅读:
GitHub2022年度前100的Java面试真题&高频知识点汇总
怎么实现在微信公众号秒杀商品的功能呢
【云原生】Kubernetes网络知识
软件测试基础教程学习1
Java学习笔记（13）
1 什么是MyBatis?
BSN长话短说之十：如何保证NFT的安全
贪心算法(活动安排问题)
【Flink】设置了watermark，没有使用watermark触发窗口计算
【分享 10 个日常使用的脚本】
- 原文地址：https://blog.csdn.net/weixin_45861372/article/details/124462334
- 最新文章
-
沪漂五周年了：我越来越迷茫了
Agentic Skill Routing 实战：别再把所有 Skill 塞进 AI Agent 上下文
MySQL-Seconds_behind_master的精度误差
[MAF预定义ChatClient中间件-03]CachingChatClient——利用缓存省钱省时间
AI的至暗历史：从万众期待到被政府撤资，AI的两次死亡徘徊
Agent OS ：五种驯服不确定性的范式
PortSwigger SQL注入LAB11
数据库即时编译JIT
[Begin]AI Learn Data Day 0
深度学习进阶（二十七）现代 LLM 的核心架构设计其二：SwiGLU