SpringBoot 整合 MyCat 實現讀寫分離
- 2020 年 3 月 31 日
- 筆記
首發地址: https://muycode.cn/article/mycat20200312.html
MyCat一個徹底開源的，面向企業應用開發的大資料庫集群。基於阿里開源的Cobar產品而研發。能滿足資料庫數據大量存儲；提高了查詢性能。文章介紹如何實現MyCat連接MySQL實現主從分離，並集成SpringBoot實現讀寫分離。
MySQL配置主從關係
說明
- 192.168.0.105 Linux 資料庫作為主master資料庫
- 127.0.0.1 Window 作為從slave資料庫
master主資料庫配置
binlog是Mysql sever層維護的一種二進位日誌，主要是用來記錄對Mysql數據更新或潛在發生更新的SQL語句，記錄了所有的寫語句，並以事務的形式保存在磁碟中，還包含語句所執行的消耗的時間，MySQL的二進位日誌是事務安全型的。
- master上開啟log_bin
$ vim /etc/my.cnf log-bin = mysql-bin #[必須]啟用二進位日誌 server-id = 4 #[必須]伺服器唯一ID，默認是1，最好取ip的後3位 expire-logs-days = 7 #只保留7天的二進位日誌，以防磁碟被日誌佔滿 binlog-ignore-db = mysql #不備份的資料庫 binlog-ignore-db = information_schema binlog-ignore-db = performation_schema binlog-ignore-db = sys binlog-do-db=itools_simple #需要做複製的資料庫名，如果有多個，複製binlog-do-db即可 - 登錄mysql，測試log_bin是否成功開啟
$ mysql -u root -p Root@123 // log_bin ON表示開啟成功，OFF表示開啟失敗 mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+ 6 rows in set (0.01 sec) - 新增備份賬戶
mysql> grant replication slave on *.* to 'backup'@'%' identified by 'Root@123'; mysql> use mysql mysql> select user,authentication_string,host from user; - 重啟mysql
$ systemctl restart mysqld - 登錄mysql，並設置只讀
$ mysql -u root -p Root@123 mysql> flush tables with read lock; mysql> show master status G *************************** 1. row *************************** File: mysql-bin.000003 Position: 154 Binlog_Do_DB: itools_simple Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys Executed_Gtid_Set: 1 row in set (0.01 sec) // 導出資料庫，進入到一個合適的文件夾 $ mysqldump -u root -p itools_simple > itools_simple.txt // 把生成的文件導出到本機 $ sz itools_simple.txt // 在window本機打開cmd，登錄slave本機mysql資料庫，導入資料庫 $ .mysql -u root -p -hlocalhost mycat_master < .itools_simple.txt slave從資料庫配置
- 配置 my.ini 文件
[mysql] #default-character-set=utf8 [mysqld] max_connections=200 character-set-server=utf8 default-storage-engine=INNODB # 添加一下配置 log-bin=mysql-bin server-id=223 - 重啟slave資料庫
net stop mysql net start mysql - 登錄slave資料庫
$ mysql -u root -p 123456 // 停止 slave mysql> stop slave; // 根據master中的status，添加以下配置 msyql> change master to master_host='192.168.0.105',master_user='backup',master_password='Root@123',master_log_file='mysql-bin.000003',master_log_pos=154; // 啟用slave mysql> start slave; // 查看slave狀態 mysql> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.105 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-OB9O5N7-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes .............. 1 row in set (0.00 sec) // 出現下面兩個說明配置正確 Slave_IO_Running: Yes Slave_SQL_Running: Yes - 關閉master資料庫的只讀設置
mysql> unlock tables; 存儲引擎
InnoDB特性
MySQL5.5版本後，MySQL的默認內置存儲引擎已經從MyISAM變成InnoDB
- 支援事務;
- 行級鎖定(更新數據時一般指鎖定當前行)：通過索引實現、全表掃描忍讓時表鎖、注意間隙所的影響;
- 讀寫阻塞與事務的隔離級別相關；
- 具有非常高的快取特性(既能快取索引、也能快取數據)；
- 這個表和主鍵以組(Cluster)的方式存儲、組成一顆平衡樹；
- 所有的輔助索引(secondary indexes)都會保存主鍵資訊；
- 支援分區、表空間類似與oracle 資料庫；
- 支援外鍵約束、不支援全文檢索(5.5.5之前的MyISAM支援全文檢索、5.5.5之後就不在支援);
- 相對MyISAM而言、對硬體的要求比較高
MyISAM特性
- 不支援事務
- 表級鎖定，數據更新時鎖定整個表：其鎖定機制是表級鎖定，這雖然可以讓鎖定的實現成本很小但是也同時大大降低了其並發性能。
- 讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，myisam還會在讀取的時候阻塞寫入，但讀本身並不會阻塞另外的讀。
- 只會快取索引：MyISAM可以通過key_buffer_size快取索引，以大大提高訪問性能，減少產品IO，但是這個快取區只會快取索引，而不會快取數據。
- 讀取速度較快，佔用資源相對少。
- 不支援外鍵約束，但支援全文索引。
修改slave資料庫存儲引擎
mysql> show variables like '%storage_engine%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 4 rows in set (0.00 sec) // 修改配置文件 $ vim /etc/my.ini [mysqld] default-storage-engine=MyISAM $ systemctl restart mysqld mysql> show variables like '%storage_engine%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | default_storage_engine | MyISAM | | default_tmp_storage_engine | InnoDB | | disabled_storage_engines | | | internal_tmp_disk_storage_engine | InnoDB | +----------------------------------+--------+ 4 rows in set (0.00 sec) 修改表存儲引擎
如果是slave資料庫的表是MyISAM，master資料庫的表是InnoDB。直接覆蓋master資料庫來同步數據的話，slave資料庫表的存儲引擎也將會同步變成InnoDB。
更換存儲引擎遇到的問題
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
- 出現的原因
在function裡面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支援。 如果我們開啟了 bin-log, 我們就必須為我們的function指定一個參數。 - 解決方案
mysql> set global log_bin_trust_function_creators=TRUE; Specified key was too long; max key length is 1000 bytes
- 出現的原因
1.DB的 engine 是 MyISAM 2.字符集是 utf8 ，1個 utf8=3bytes 3.(索引長度總和) * 3 > 1000。 - 解決方案
1.修改DB engine 至 innodb 2.更改字符集 3.減小欄位長度 注意：一定不要手動去修改slave資料庫中的數據，需要給slave的用戶設置只讀。
至此，mysql的資料庫主從設置已經配置成功。在master中修改資料庫，會同步到slave中。
Mycat基於MySQL的讀寫分離
Mycat不負責數據的同步，所以要還是要基於MySQL的主從配置來實現讀寫分離。
安裝Mycat
由於github限制，所以以後新版本從以下地址下載 http://dl.mycat.io
- Linux創建文件夾
/usr/local/mycat，進入文件夾，下載安裝包
$ wget http://dl.mycat.io/1.6.7.5/2020-3-3/Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz $ tar -zxvf Mycat-server-1.6.7.5-test-20200303154735-linux.tar.gz $ cd mycat $ useradd mycat $ chown -R mycat:mycat /usr/local/mycat/mycat $ passwd mycat // 配置hostname，添加以下配置 [root@localhost mycat] vim /etc/sysconfig/network HOSTNAME=localhost（主機名） // 查看是否配置主機 $ vim /etc/hosts - 將Mycat配置到環境變數中
$ vim /etc/profile // 在最後添加 MYCAT_HOME=/usr/local/mycat/mycat PATH=$MYCAT_HOME/bin:$PATH export PATH // 使配置生效 $ source /etc/profile -
在master資料庫中添加user1（寫）、user2（只讀）兩個賬戶，並配置許可權。
-
配置mycat的
schema.xml
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="itools_simple" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="localhost" database="itools_simple" /> <dataHost name="localhost" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1" url="192.168.0.105:3306" user="user1" password="Root@123"> <!-- 可以配置多個從庫 --> <readHost host="hostS2" url="127.0.0.1:3306" user="user2" password="Root@123" /> </writeHost> </dataHost> </mycat:schema> - 配置mycat的
server.xml，增加兩個用戶
<user name="user1" defaultAccount="true"> <property name="password">Root@123</property> <property name="schemas">itools_simple</property> <property name="defaultSchema">itools_simple</property> </user> <user name="user2"> <property name="password">Root@123</property> <property name="schemas">itools_simple</property> <property name="readOnly">true</property> <property name="defaultSchema">itools_simple</property> </user> 啟動Mycat
- 啟動mycat
$ mycat start Starting Mycat-server... - 查看啟動日誌
$ cat wrapper.log MyCAT Server startup successfully. see logs in logs/mycat.log 使用客戶端連接mycat
- 使用SQLyog連接(使用此方式連接，不能直接通過點擊表查看數據)
- 使用Navicat連接
- 可通過客戶端直接查看master數據，也可通過修改mycat數據，查看master和slave的數據是否會同步
SpringBoot 整合 MyCat 實現讀寫分離
- 首先需要配置好資料庫的主從關係。
- 配置好MyCat服務。
- 實現MyCat與MySQL讀寫分離。
添加依賴
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-aop</artifactId> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.23</version> </dependency> 創建數據源
package com.muycode.itoolsimple.datasource; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.boot.jdbc.DataSourceBuilder; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.sql.DataSource; @Configuration public class DataSourceConfig { /** * 創建可讀數據源 * * @return */ @Bean(name = "selectDataSource") @ConfigurationProperties(prefix = "spring.datasource.select") public DataSource dataSource1() { return DataSourceBuilder.create().build(); } /** * 創建可寫數據源 * * @return */ @Bean(name = "updateDataSource") @ConfigurationProperties(prefix = "spring.datasource.update") public DataSource dataSource2() { return DataSourceBuilder.create().build(); } } 設置數據源
package com.muycode.itoolsimple.datasource; import org.springframework.context.annotation.Lazy; import org.springframework.stereotype.Component; @Component @Lazy(false) public class DataSourceContextHolder { /** * 採用ThreadLocal 保存本地多數據源 */ private static final ThreadLocal<String> contextHolder = new ThreadLocal<>(); /** * 設置數據源類型 * * @param dbType */ public static void setDbType(String dbType) { contextHolder.set(dbType); } /** * 獲取數據源類型 */ public static String getDbType() { return contextHolder.get(); } public static void clearDbType() { contextHolder.remove(); } } 返回數據源
package com.muycode.itoolsimple.datasource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.context.annotation.Primary; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import org.springframework.stereotype.Component; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Component @Primary public class DynamicDataSource extends AbstractRoutingDataSource { @Autowired @Qualifier("selectDataSource") private DataSource selectDataSource; @Autowired @Qualifier("updateDataSource") private DataSource updateDataSource; /** * 返回生效的數據源名稱 */ @Override protected Object determineCurrentLookupKey() { return DataSourceContextHolder.getDbType(); } /** * 配置數據源資訊 */ @Override public void afterPropertiesSet() { Map<Object, Object> map = new HashMap<>(16); map.put("selectDataSource", selectDataSource); map.put("updateDataSource", updateDataSource); setTargetDataSources(map); setDefaultTargetDataSource(updateDataSource); super.afterPropertiesSet(); } } 創建切面，動態設置數據源
package com.muycode.itoolsimple.datasource; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.springframework.context.annotation.Lazy; import org.springframework.core.annotation.Order; import org.springframework.stereotype.Component; @Aspect @Component @Lazy(false) @Order(0) // Order設定AOP執行順序 使之在資料庫事務上先執行 public class DataSourceOptionAop { /** * 可讀數據源 */ private final static String DATASOURCE_TYPE_SELECT = "selectDataSource"; /** * 可寫數據源 */ private final static String DATASOURCE_TYPE_UPDATE = "updateDataSource"; /** * 創建切面，根據方法類型選擇不同的數據源 * * @param joinPoint */ @Before("execution(* com.muycode.itoolsimple.service.*.*(..))") public void process(JoinPoint joinPoint) { String methodName = joinPoint.getSignature().getName(); System.out.print("=========== " + methodName); if (methodName.startsWith("get") || methodName.startsWith("count") || methodName.startsWith("find") || methodName.startsWith("list") || methodName.startsWith("select") || methodName.startsWith("check") || methodName.startsWith("query")) { DataSourceContextHolder.setDbType(DATASOURCE_TYPE_SELECT); System.out.println("-----------------使用selectDataSource數據源-------------------"); } else { DataSourceContextHolder.setDbType(DATASOURCE_TYPE_UPDATE); System.out.println("-----------------使用updateDataSource數據源-------------------"); } } } 輸出結果
=========== getByUsername-----------------使用selectDataSource數據源------------------- =========== getPermissionStringByUserId-----------------使用selectDataSource數據源------------------- =========== getPermissionByUserId-----------------使用selectDataSource數據源------------------- =========== getRolePermissionLinkByUserId-----------------使用selectDataSource數據源------------------- =========== save-----------------使用updateDataSource數據源------------------- =========== queryByPage-----------------使用selectDataSource數據源------------------- =========== save-----------------使用updateDataSource數據源------------------- =========== getPermissionAll-----------------使用selectDataSource數據源------------------- =========== save-----------------使用updateDataSource數據源------------------- =========== getSysCodeAll-----------------使用selectDataSource數據源------------------- =========== save-----------------使用updateDataSource數據源------------------- =========== getByRid-----------------使用selectDataSource數據源------------------- 參考文章：
mysql5.7.18的安裝與主從複製
Mycat – 實現資料庫的讀寫分離與高可用
關於利用mycat結合springboot實現資料庫的讀寫分離（動態切換數據源）
