mysql的读写分离

3.3 步骤

实现此案例需要按照如下步骤进行。

步骤一：搭建一主一从结构

因为数据的查询和存储分别访问不同的数据库服务器，所以要通过主从同步来保证负责读访问的服务与负责写访问的服务器数据一致。

1)配置主数据库服务器

1. [root@mysql56 ~]# yum -y install mysql-server mysql
2. [root@mysql56 ~]# systemctl start mysqld
3. //启用binlog日志
4. [root@mysql56 ~]# vim /etc/my.cnf.d/mysql-server.cnf
5. [mysqld]
6. server-id=56
7. log-bin=mysql56
8. :wq
9. [root@mysql56 ~]# systemctl restart mysqld
10. //用户授权
11. [root@mysql56 ~]# mysql
12. mysql> create user repluser@"%" identified by "123qqq...A";
13. Query OK, 0 rows affected (0.11 sec)
15. mysql> grant replication slave on *.* to repluser@"%" ;
16. Query OK, 0 rows affected (0.08 sec)
17. //查看日志信息
18. mysql> show master status;
19. +----------------+----------+--------------+------------------+-------------------+
20. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
21. +----------------+----------+--------------+------------------+-------------------+
22. | mysql56.000001 | 667 | | | |
23. +----------------+----------+--------------+------------------+-------------------+
24. 1 row in set (0.00 sec)

2）配置从数据库服务器

2)安装mycat软件

3)定义客户端连接mycat服务使用用户及密码：

5)在mycat58主机运行数据库服务

6)启动mycat服务

7) 连接mycat服务

步骤三：配置读写分离

1）添加数据源：连接mycat服务后做如下操作

2）配置数据库服务器添加plja用户

3）创建集群,连接mycat服务后做如下配置:

4）指定主机角色

5）修改读策略

1. //指定server-id 并重启数据库服务
2. [root@mysql57 ~]# yum -y install mysql-server mysql
3. [root@mysql57 ~]# systemctl start mysqld
4. [root@mysql57 ~]# vim /etc/my.cnf.d/mysql-server.cnf
5. [mysqld]
6. server-id=57
7. :wq
8. [root@mysql57 ~]# systemctl restart mysqld
9. //管理员登陆，指定主服务器信息
10. [root@mysql57 ~]# mysql
11. mysql> change master to master_host="192.168.88.56" , master_user="repluser" , master_password="123qqq...A" , master_log_file="mysql56.000001",master_log_pos=667;
12. Query OK, 0 rows affected, 8 warnings (0.54 sec)
13. //启动slave进程
14. mysql> start slave;
15. Query OK, 0 rows affected, 1 warning (0.03 sec)
16. //查看状态信息
17. mysql> show slave status \G
18. *************************** 1. row ***************************
19. Slave_IO_State: Waiting for source to send event
20. Master_Host: 192.168.88.56
21. Master_User: repluser
22. Master_Port: 3306
23. Connect_Retry: 60
24. Master_Log_File: mysql56.000001
25. Read_Master_Log_Pos: 667
26. Relay_Log_File: mysql57-relay-bin.000002
27. Relay_Log_Pos: 322
28. Relay_Master_Log_File: mysql56.000001
29. Slave_IO_Running: Yes //IO线程
30. Slave_SQL_Running: Yes //SQL线程
31. Replicate_Do_DB:
32. Replicate_Ignore_DB:
33. Replicate_Do_Table:
34. Replicate_Ignore_Table:
35. Replicate_Wild_Do_Table:
36. Replicate_Wild_Ignore_Table:
37. Last_Errno: 0
38. Last_Error:
39. Skip_Counter: 0
40. Exec_Master_Log_Pos: 667
41. Relay_Log_Space: 533
42. Until_Condition: None
43. Until_Log_File:
44. Until_Log_Pos: 0
45. Master_SSL_Allowed: No
46. Master_SSL_CA_File:
47. Master_SSL_CA_Path:
48. Master_SSL_Cert:
49. Master_SSL_Cipher:
50. Master_SSL_Key:
51. Seconds_Behind_Master: 0
52. Master_SSL_Verify_Server_Cert: No
53. Last_IO_Errno: 0
54. Last_IO_Error:
55. Last_SQL_Errno: 0
56. Last_SQL_Error:
57. Replicate_Ignore_Server_Ids:
58. Master_Server_Id: 56
59. Master_UUID: e0ab8dc4-0109-11ee-87e7-525400ad7ed3
60. Master_Info_File: mysql.slave_master_info
61. SQL_Delay: 0
62. SQL_Remaining_Delay: NULL
63. Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
64. Master_Retry_Count: 86400
65. Master_Bind:
66. Last_IO_Error_Timestamp:
67. Last_SQL_Error_Timestamp:
68. Master_SSL_Crl:
69. Master_SSL_Crlpath:
70. Retrieved_Gtid_Set:
71. Executed_Gtid_Set:
72. Auto_Position: 0
73. Replicate_Rewrite_DB:
74. Channel_Name:
75. Master_TLS_Version:
76. Master_public_key_path:
77. Get_master_public_key: 0
78. Network_Namespace:
79. 1 row in set, 1 warning (0.00 sec)

    步骤二：配置mycat服务器

    1)拷贝软件到mycat58主机

80. [root@server1 ~]# scp /linux-soft/s3/mycat2-1.21-release-jar-with-dependencies.jar root@192.168.88.58:/root/
81. [root@server1 ~]# scp /linux-soft/s3/mycat2-install-template-1.21.zip root@192.168.88.58:/root/
82. //安装jdk
83. [root@mycat58 upload]# yum -y install java-1.8.0-openjdk.x86_64
84. //安装解压命令
85. [root@mycat58 upload]# which unzip || yum -y install unzip
86. //安装mycat
87. [root@mycat58 upload]# unzip mycat2-install-template-1.21.zip
88. [root@mycat58 upload]# mv mycat /usr/local/
89. //安装依赖
90. [root@mycat58 upload]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
91. //修改权限
92. [root@mycat58 upload]# chmod -R 777 /usr/local/mycat/
93. [root@mycat58 ~]# vim /usr/local/mycat/conf/users/root.user.json
94. {
95. "dialect":"mysql",
96. "ip":null,
97. "password":"654321", 密码
98. "transactionType":"proxy",
99. "username":"mycat" 用户名
100. }
101. :wq
102. 定义连接的数据库服务器
103. [root@mycat58 ~]# vim /usr/local/mycat/conf/datasources/prototypeDs.data
104. {
105. "dbType":"mysql",
106. "idleTimeout":60000,
107. "initSqls":[],
108. "initSqlsGetConnection":true,
109. "instanceType":"READ_WRITE",
110. "maxCon":1000,
111. "maxConnectTimeout":3000,
112. "maxRetryCount":5,
113. "minCon":1,
114. "name":"prototypeDs",
115. "password":"123456", 密码
116. "type":"JDBC",
117. "url":"jdbc:mysql://localhost:3306/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8", 连接本机的数据库服务
118. "user":"plj", 用户名
119. "weight":0
120. }
121. :wq
122. [root@mycat58 ~]# yum -y install mysql-server mysql 安装软件
123. [root@mycat58 ~]# systemctl start mysqld 启动服务
124. [root@mycat58 ~]# mysql 连接服务
125. mysql> create user plj@"%" identified by "123456"; 创建plj用户
126. Query OK, 0 rows affected (0.05 sec)
127. mysql> grant all on *.* to plj@"%" ; 授予权限
128. Query OK, 0 rows affected (0.39 sec)
129. mysql> exit 断开连接
130. Bye
131. [root@mycat58 ~]#
132. [root@mycat58 ~]# /usr/local/mycat/bin/mycat help
133. Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
134. [root@mycat58 ~]# /usr/local/mycat/bin/mycat start
135. Starting mycat2...
136. //半分钟左右 能看到端口
137. [root@mycat58 ~]# netstat -utnlp | grep 8066
138. tcp6 0 0 :::8066 :::* LISTEN 57015/java
139. [root@mycat58 ~]#
140. [root@mycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
141. mysql> show databases;
142. +--------------------+
143. | `Database` |
144. +--------------------+
145. | information_schema |
146. | mysql |
147. | performance_schema |
148. +--------------------+
149. 3 rows in set (0.11 sec)
150. Mysql>
151. //连接mycat服务
152. [root@mycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
153. //添加mysql56数据库服务器
154. MySQL> /*+ mycat:createdatasource{
155. "name":"whost56", "url":"jdbc:mysql://192.168.88.56:3306","user":"plja","password":"123456"}*/;
156. Query OK, 0 rows affected (0.25 sec)
157. //添加mysql57数据库服务器
158. Mysql>/*+ mycat:createdatasource{
159. "name":"rhost57", "url":"jdbc:mysql://192.168.88.57:3306","user":"plja","password":"123456"}*/;
160. //查看数据源
161. mysql> /*+mycat:showDataSources{}*/ \G
162. *************************** 1. row ***************************
163. NAME: whost56
164. USERNAME: plja
165. PASSWORD: 123456
166. MAX_CON: 1000
167. MIN_CON: 1
168. EXIST_CON: 0
169. USE_CON: 0
170. MAX_RETRY_COUNT: 5
171. MAX_CONNECT_TIMEOUT: 30000
172. DB_TYPE: mysql
173. URL: jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
174. WEIGHT: 0
175. INIT_SQL:
176. INIT_SQL_GET_CONNECTION: true
177. INSTANCE_TYPE: READ_WRITE
178. IDLE_TIMEOUT: 60000
179. DRIVER: {
180. CreateTime:"2023-06-02 17:01:14",
181. ActiveCount:0,
182. PoolingCount:0,
183. CreateCount:0,
184. DestroyCount:0,
185. CloseCount:0,
186. ConnectCount:0,
187. Connections:[
188. ]
189. }
190. TYPE: JDBC
191. IS_MYSQL: true
192. *************************** 2. row ***************************
193. NAME: rhost57
194. USERNAME: plja
195. PASSWORD: 123456
196. MAX_CON: 1000
197. MIN_CON: 1
198. EXIST_CON: 0
199. USE_CON: 0
200. MAX_RETRY_COUNT: 5
201. MAX_CONNECT_TIMEOUT: 30000
202. DB_TYPE: mysql
203. URL: jdbc:mysql://192.168.88.57:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&autoReconnect=true
204. WEIGHT: 0
205. INIT_SQL:
206. INIT_SQL_GET_CONNECTION: true
207. INSTANCE_TYPE: READ_WRITE
208. IDLE_TIMEOUT: 60000
209. DRIVER: {
210. CreateTime:"2023-06-02 17:01:14",
211. ActiveCount:0,
212. PoolingCount:0,
213. CreateCount:0,
214. DestroyCount:0,
215. CloseCount:0,
216. ConnectCount:0,
217. Connections:[
218. ]
219. }
220. TYPE: JDBC
221. IS_MYSQL: true
222. *************************** 3. row ***************************
223. NAME: prototypeDs
224. USERNAME: plj
225. PASSWORD: 123456
226. MAX_CON: 1000
227. MIN_CON: 1
228. EXIST_CON: 0
229. USE_CON: 0
230. MAX_RETRY_COUNT: 5
231. MAX_CONNECT_TIMEOUT: 3000
232. DB_TYPE: mysql
233. URL: jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
234. WEIGHT: 0
235. INIT_SQL:
236. INIT_SQL_GET_CONNECTION: true
237. INSTANCE_TYPE: READ_WRITE
238. IDLE_TIMEOUT: 60000
239. DRIVER: {
240. CreateTime:"2023-06-02 17:01:14",
241. ActiveCount:0,
242. PoolingCount:0,
243. CreateCount:0,
244. DestroyCount:0,
245. CloseCount:0,
246. ConnectCount:0,
247. Connections:[
248. ]
249. }
250. TYPE: JDBC
251. IS_MYSQL: true
252. 3 rows in set (0.00 sec)
253. mysql>
254. //添加的数据源以文件的形式保存在安装目录下
255. [root@mycat58 conf]# ls /usr/local/mycat/conf/datasources/
256. prototypeDs.datasource.json rhost57.datasource.json whost56.datasource.json
257. [root@mycat58 conf]#
258. //在master服务器添加
259. [root@mysql56 ~]# mysql
260. mysql> create user plja@"%" identified by "123456";
261. Query OK, 0 rows affected (0.06 sec)
262. mysql> grant all on *.* to plja@"%";
263. Query OK, 0 rows affected (0.03 sec)
264. mysql>exit
265. [root@mysql56 ~]#
266. //在slave服务器查看是否同步成功
267. [root@mysql57 ~]# mysql -e 'select user , host from mysql.user where user="plja"'
268. +------+------+
269. | user | host |
270. +------+------+
271. | plja | % |
272. +------+------+
273. [root@mysql57 ~]#
274. [root@mycat58 ~]# mysql -h127.0.0.1 -P8066 -umycat -p654321
275. //创建集群
276. mysql>/*!mycat:createcluster{
277. "name":"rwcluster",
278. "masters":["whost56"],
279. "replicas":["rhost57"]
280. }*/ ;
281. Mysql>
282. //查看集群信息
283. mysql> /*+ mycat:showClusters{}*/ \G
284. *************************** 1. row ***************************
285. NAME: rwcluster
286. SWITCH_TYPE: SWITCH
287. MAX_REQUEST_COUNT: 2000
288. TYPE: BALANCE_ALL
289. WRITE_DS: whost56
290. READ_DS: whost56,rhost57
291. WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
292. READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
293. AVAILABLE: true
294. *************************** 2. row ***************************
295. NAME: prototype
296. SWITCH_TYPE: SWITCH
297. MAX_REQUEST_COUNT: 200
298. TYPE: BALANCE_ALL
299. WRITE_DS: prototypeDs
300. READ_DS: prototypeDs
301. WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
302. READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
303. AVAILABLE: true
304. 2 rows in set (0.00 sec)
306. mysql>
307. //创建的集群以文件的形式保存在目录下
308. [root@mycat58 conf]# ls /usr/local/mycat/conf/clusters/
309. prototype.cluster.json rwcluster.cluster.json
310. [root@mycat58 conf]#
311. //修改master角色主机仅负责写访问
312. [root@mycat58 ~]# vim /usr/local/mycat/conf/datasources/whost56.datasource.json
313. {
314. "dbType":"mysql",
315. "idleTimeout":60000,
316. "initSqls":[],
317. "initSqlsGetConnection":true,
318. "instanceType":"WRITE", 仅负责写访问
319. "logAbandoned":true,
320. "maxCon":1000,
321. "maxConnectTimeout":30000,
322. "maxRetryCount":5,
323. "minCon":1,
324. "name":"whost56",
325. "password":"123456",
326. "queryTimeout":0,
327. "removeAbandoned":false,
328. "removeAbandonedTimeoutSecond":180,
329. "type":"JDBC",
330. "url":"jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
331. "user":"plja",
332. "weight":0
333. }
334. :wq
335. //修改slave角色主机仅负责读访问
336. [root@mycat58 ~]# vim /usr/local/mycat/conf/datasources/rhost57.datasource.json
337. {
338. "dbType":"mysql",
339. "idleTimeout":60000,
340. "initSqls":[],
341. "initSqlsGetConnection":true,
342. "instanceType":"READ",仅负责读访问
343. "logAbandoned":true,
344. "maxCon":1000,
345. "maxConnectTimeout":30000,
346. "maxRetryCount":5,
347. "minCon":1,
348. "name":"rhost57",
349. "password":"123456",
350. "queryTimeout":0,
351. "removeAbandoned":false,
352. "removeAbandonedTimeoutSecond":180,
353. "type":"JDBC",
354. "url":"jdbc:mysql://192.168.88.57:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
355. "user":"plja",
356. "weight":0
357. }
358. :wq
359. [root@mycat58 ~]# vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json
360. {
361. "clusterType":"MASTER_SLAVE",
362. "heartbeat":{
363. "heartbeatTimeout":1000,
364. "maxRetryCount":3,
365. "minSwitchTimeInterval":300,
366. "showLog":false,
367. "slaveThreshold":0.0
368. },
369. "masters":[
370. "whost56"
371. ],
372. "maxCon":2000,
373. "name":"rwcluster",
374. "readBalanceType":"BALANCE_ALL_READ",
375. "replicas":[
376. "rhost57"
377. ],
378. "switchType":"SWITCH"
379. }
380. :wq
382. //重启mycat服务
383. [root@mycat58 ~]# /usr/local/mycat/bin/mycat restart
384. Stopping mycat2...
385. Stopped mycat2.
386. Starting mycat2...
387. [root@mycat58 ~]#