mysql的读写分离

本文详细介绍了如何通过3.3步骤搭建MySQL主从结构,配置mycat作为中间层实现读写分离,包括配置主从数据库、安装mycat、设置用户权限、定义数据源、配置集群等关键步骤。
摘要由CSDN通过智能技术生成

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)
  14. mysql> grant replication slave on *.* to repluser@"%" ;
  15. Query OK, 0 rows affected (0.08 sec)
  16. //查看日志信息
  17. mysql> show master status;
  18. +----------------+----------+--------------+------------------+-------------------+
  19. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  20. +----------------+----------+--------------+------------------+-------------------+
  21. | mysql56.000001 | 667 | | | |
  22. +----------------+----------+--------------+------------------+-------------------+
  23. 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)
  305. mysql>
  306. //创建的集群以文件的形式保存在目录下
  307. [root@mycat58 conf]# ls /usr/local/mycat/conf/clusters/
  308. prototype.cluster.json rwcluster.cluster.json
  309. [root@mycat58 conf]#
  310. //修改master角色主机仅负责写访问
  311. [root@mycat58 ~]# vim /usr/local/mycat/conf/datasources/whost56.datasource.json
  312. {
  313. "dbType":"mysql",
  314. "idleTimeout":60000,
  315. "initSqls":[],
  316. "initSqlsGetConnection":true,
  317. "instanceType":"WRITE", 仅负责写访问
  318. "logAbandoned":true,
  319. "maxCon":1000,
  320. "maxConnectTimeout":30000,
  321. "maxRetryCount":5,
  322. "minCon":1,
  323. "name":"whost56",
  324. "password":"123456",
  325. "queryTimeout":0,
  326. "removeAbandoned":false,
  327. "removeAbandonedTimeoutSecond":180,
  328. "type":"JDBC",
  329. "url":"jdbc:mysql://192.168.88.56:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
  330. "user":"plja",
  331. "weight":0
  332. }
  333. :wq
  334. //修改slave角色主机仅负责读访问
  335. [root@mycat58 ~]# vim /usr/local/mycat/conf/datasources/rhost57.datasource.json
  336. {
  337. "dbType":"mysql",
  338. "idleTimeout":60000,
  339. "initSqls":[],
  340. "initSqlsGetConnection":true,
  341. "instanceType":"READ",仅负责读访问
  342. "logAbandoned":true,
  343. "maxCon":1000,
  344. "maxConnectTimeout":30000,
  345. "maxRetryCount":5,
  346. "minCon":1,
  347. "name":"rhost57",
  348. "password":"123456",
  349. "queryTimeout":0,
  350. "removeAbandoned":false,
  351. "removeAbandonedTimeoutSecond":180,
  352. "type":"JDBC",
  353. "url":"jdbc:mysql://192.168.88.57:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
  354. "user":"plja",
  355. "weight":0
  356. }
  357. :wq
  358. [root@mycat58 ~]# vim /usr/local/mycat/conf/clusters/rwcluster.cluster.json
  359. {
  360. "clusterType":"MASTER_SLAVE",
  361. "heartbeat":{
  362. "heartbeatTimeout":1000,
  363. "maxRetryCount":3,
  364. "minSwitchTimeInterval":300,
  365. "showLog":false,
  366. "slaveThreshold":0.0
  367. },
  368. "masters":[
  369. "whost56"
  370. ],
  371. "maxCon":2000,
  372. "name":"rwcluster",
  373. "readBalanceType":"BALANCE_ALL_READ",
  374. "replicas":[
  375. "rhost57"
  376. ],
  377. "switchType":"SWITCH"
  378. }
  379. :wq
  380. //重启mycat服务
  381. [root@mycat58 ~]# /usr/local/mycat/bin/mycat restart
  382. Stopping mycat2...
  383. Stopped mycat2.
  384. Starting mycat2...
  385. [root@mycat58 ~]#
  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值