mycat实现mysql基于GITD实现双主双从读写分离master节点高可用

佳月金属制品厂

架构说明

1. 10.0.0.18 master节点和10.0.0.22节点互为主
2. 10.0.0.19 10.0.0.18的slave节点
3. 10.0.0.22 master节点和10.0.0.19节点互为主
4. 10.0.0.24 10.0.0.22的slave节点
5. 10.0.0.23 mycat节点
6. mysql版本8.0.32
7. 系统版本：rocky8.4

复制代码

mysql主从搭建

1. #搭建双主节点
2. #搭建第一个主10.0.0.18
3. #注释掉/etc/my.cnf.d/mysql-server.cnf
4. cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
5. #
6. # This group are read by MySQL server.
7. # Use it for options that only the server (but not clients) should see
8. #
9. # For advice on how to change settings please see
10. # http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
12. # Settings user and group are ignored when systemd is used.
13. # If you need to run mysqld under a different user or group,
14. # customize your systemd unit file for mysqld according to the
15. # instructions in http://fedoraproject.org/wiki/Systemd
17. #[mysqld]
18. #datadir=/var/lib/mysql
19. #socket=/var/lib/mysql/mysql.sock
20. #log-error=/var/log/mysql/mysqld.log
21. #pid-file=/run/mysqld/mysqld.pid
22. #log-bin=/data/mysql/logbin/mysql-bin
23. EOF
25. #配置主节点的my.cat配置
26. cat >/etc/my.cnf<<'EOF'
27. #
28. # This group is read both both by the client and the server
29. # use it for options that affect everything
30. #
31. [client-server]
33. #
34. # include all files from the config directory
35. #
36. !includedir /etc/my.cnf.d
37. [mysqld]
38. datadir=/var/lib/mysql
39. socket=/var/lib/mysql/mysql.sock
40. log-error=/var/log/mysql/mysqld.log
41. pid-file=/run/mysqld/mysqld.pid
42. server-id=18
43. #read-only
44. general_log
45. gtid_mode=ON
46. enforce_gtid_consistency
47. log-bin=/data/mysql/logbin/mysql-bin
48. EOF
50. #创建存放二进制日志的目录
51. mkdir -p /data/mysql/logbin/
52. chown -R mysql.mysql /data
54. #启动数据库
55. systemctl enable --now mysqld
57. #配置账号和授权
58. mysql
59. create user 'repluser'@'10.0.0.%' identified by '123456';
60. grant replication slave on *.* to 'repluser'@'10.0.0.%';
61. #创建mycat使用的账号
62. create user 'wbiao'@'10.0.0.%' IDENTIFIED BY '123456';
63. grant ALL ON hellodb.* TO 'wbiao'@'10.0.0.%';
65. #搭建第二个主10.0.0.22
66. #注释掉/etc/my.cnf.d/mysql-server.cnf
67. cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
68. #
69. # This group are read by MySQL server.
70. # Use it for options that only the server (but not clients) should see
71. #
72. # For advice on how to change settings please see
73. # http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
75. # Settings user and group are ignored when systemd is used.
76. # If you need to run mysqld under a different user or group,
77. # customize your systemd unit file for mysqld according to the
78. # instructions in http://fedoraproject.org/wiki/Systemd
80. #[mysqld]
81. #datadir=/var/lib/mysql
82. #socket=/var/lib/mysql/mysql.sock
83. #log-error=/var/log/mysql/mysqld.log
84. #pid-file=/run/mysqld/mysqld.pid
85. #log-bin=/data/mysql/logbin/mysql-bin
86. EOF
88. #配置主节点的my.cat配置
89. cat >/etc/my.cnf<<'EOF'
90. #
91. # This group is read both both by the client and the server
92. # use it for options that affect everything
93. #
94. [client-server]
96. #
97. # include all files from the config directory
98. #
99. !includedir /etc/my.cnf.d
100. [mysqld]
101. datadir=/var/lib/mysql
102. socket=/var/lib/mysql/mysql.sock
103. log-error=/var/log/mysql/mysqld.log
104. pid-file=/run/mysqld/mysqld.pid
105. server-id=22
106. #read-only
107. general_log
108. gtid_mode=ON
109. enforce_gtid_consistency
110. log-bin=/data/mysql/logbin/mysql-bin
111. EOF
113. #创建存放二进制日志的目录
114. mkdir -p /data/mysql/logbin/
115. chown -R mysql.mysql /data
117. #启动数据库
118. systemctl enable --now mysqld
120. #10.0.0.22指向10.0.0.18
121. 执行change master to
122. CHANGE MASTER TO
123. MASTER_HOST='10.0.0.18',
124. MASTER_USER='repluser',
125. MASTER_PASSWORD='123456',
126. MASTER_PORT=3306,
127. MASTER_AUTO_POSITION=1;
128. #开启IO线程和SQL线程
129. start slave;
130. #检查状态
131. show slave status\G
132. #检查
133. mysql> select user,host from mysql.user;
134. +------------------+-----------+
135. | user             | host      |
136. +------------------+-----------+
137. | repluser         | 10.0.0.%  |
138. | wbiao            | 10.0.0.%  |
139. | mysql.infoschema | localhost |
140. | mysql.session    | localhost |
141. | mysql.sys        | localhost |
142. | root             | localhost |
143. +------------------+-----------+
144. 6 rows in set (0.00 sec)
147. 10.0.0.18指向10.0.0.22
148. 执行change master to
149. CHANGE MASTER TO
150. MASTER_HOST='10.0.0.22',
151. MASTER_USER='repluser',
152. MASTER_PASSWORD='123456',
153. MASTER_PORT=3306,
154. MASTER_AUTO_POSITION=1;
155. #开启IO线程和SQL线程
156. start slave;
157. #检查状态
158. show slave status\G
160. #配置10.0.0.18的从节点10.0.0.19
161. #注释掉/etc/my.cnf.d/mysql-server.cnf
162. cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
163. #
164. # This group are read by MySQL server.
165. # Use it for options that only the server (but not clients) should see
166. #
167. # For advice on how to change settings please see
168. # http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
170. # Settings user and group are ignored when systemd is used.
171. # If you need to run mysqld under a different user or group,
172. # customize your systemd unit file for mysqld according to the
173. # instructions in http://fedoraproject.org/wiki/Systemd
175. #[mysqld]
176. #datadir=/var/lib/mysql
177. #socket=/var/lib/mysql/mysql.sock
178. #log-error=/var/log/mysql/mysqld.log
179. #pid-file=/run/mysqld/mysqld.pid
180. #log-bin=/data/mysql/logbin/mysql-bin
181. EOF
182. #配置从节点10.0.0.19的my.cat配置
183. cat >/etc/my.cnf<<'EOF'
184. #
185. # This group is read both both by the client and the server
186. # use it for options that affect everything
187. #
188. [client-server]
190. #
191. # include all files from the config directory
192. #
193. !includedir /etc/my.cnf.d
194. [mysqld]
195. datadir=/var/lib/mysql
196. socket=/var/lib/mysql/mysql.sock
197. log-error=/var/log/mysql/mysqld.log
198. pid-file=/run/mysqld/mysqld.pid
199. server-id=19
200. read-only
201. general_log
202. gtid_mode=ON
203. enforce_gtid_consistency
204. log-bin=/data/mysql/logbin/mysql-bin
205. EOF
207. #创建存放二进制日志的目录
208. mkdir -p /data/mysql/logbin/
209. chown -R mysql.mysql /data
210. #启动数据库
211. systemctl enable --now mysqld
213. #从节点10.0.0.19执行change master to
214. CHANGE MASTER TO
215. MASTER_HOST='10.0.0.18',
216. MASTER_USER='repluser',
217. MASTER_PASSWORD='123456',
218. MASTER_PORT=3306,
219. MASTER_AUTO_POSITION=1;
220. #开启IO线程和SQL线程
221. start slave;
222. #检查状态
223. show slave status\G
226. ##配置10.0.0.22的从节点10.0.0.24
227. #注释掉/etc/my.cnf.d/mysql-server.cnf
228. cat >/etc/my.cnf.d/mysql-server.cnf<<'EOF'
229. #
230. # This group are read by MySQL server.
231. # Use it for options that only the server (but not clients) should see
232. #
233. # For advice on how to change settings please see
234. # http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
236. # Settings user and group are ignored when systemd is used.
237. # If you need to run mysqld under a different user or group,
238. # customize your systemd unit file for mysqld according to the
239. # instructions in http://fedoraproject.org/wiki/Systemd
241. #[mysqld]
242. #datadir=/var/lib/mysql
243. #socket=/var/lib/mysql/mysql.sock
244. #log-error=/var/log/mysql/mysqld.log
245. #pid-file=/run/mysqld/mysqld.pid
246. #log-bin=/data/mysql/logbin/mysql-bin
247. EOF
248. #配置从节点10.0.0.24的my.cat配置
249. cat >/etc/my.cnf<<'EOF'
250. #
251. # This group is read both both by the client and the server
252. # use it for options that affect everything
253. #
254. [client-server]
256. #
257. # include all files from the config directory
258. #
259. !includedir /etc/my.cnf.d
260. [mysqld]
261. datadir=/var/lib/mysql
262. socket=/var/lib/mysql/mysql.sock
263. log-error=/var/log/mysql/mysqld.log
264. pid-file=/run/mysqld/mysqld.pid
265. server-id=24
266. read-only
267. general_log
268. gtid_mode=ON
269. enforce_gtid_consistency
270. log-bin=/data/mysql/logbin/mysql-bin
271. EOF
273. #创建存放二进制日志的目录
274. mkdir -p /data/mysql/logbin/
275. chown -R mysql.mysql /data
276. #启动数据库
277. systemctl enable --now mysqld
279. #从节点10.0.0.24执行change master to
280. CHANGE MASTER TO
281. MASTER_HOST='10.0.0.22',
282. MASTER_USER='repluser',
283. MASTER_PASSWORD='123456',
284. MASTER_PORT=3306,
285. MASTER_AUTO_POSITION=1;
286. #开启IO线程和SQL线程
287. start slave;
288. #检查状态
289. show slave status\G
291. #10.0.0.18导入hellodb的数据库
292. [root@10 ~]# mysql <hellodb_innodb.sql
293. ##检查状态
294. show slave status\G
295. #所有节点检查数据
296. select * from hellodb.students;
297. #双主只能对一个主进行写操作

复制代码

 

来源:https://www.cnblogs.com/yeyouqing/p/17426909.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！