MyCat一個(gè)徹底開(kāi)源的，面向企業(yè)應(yīng)用開(kāi)發(fā)的大數(shù)據(jù)庫(kù)集群?；诎⒗镩_(kāi)源的Cobar產(chǎn)品而研發(fā)。能滿足數(shù)據(jù)庫(kù)數(shù)據(jù)大量存儲(chǔ)；提高了查詢性能。文章介紹如何實(shí)現(xiàn)MyCat連接MySQL實(shí)現(xiàn)主從分離，并集成SpringBoot實(shí)現(xiàn)讀寫分離。

MySQL配置主從關(guān)系

說(shuō)明

•  192.168.0.105 Linux 數(shù)據(jù)庫(kù)作為主master數(shù)據(jù)庫(kù)
•  127.0.0.1 Window 作為從slave數(shù)據(jù)庫(kù)

master主數(shù)據(jù)庫(kù)配置

binlog是Mysql sever層維護(hù)的一種二進(jìn)制日志，主要是用來(lái)記錄對(duì)Mysql數(shù)據(jù)更新或潛在發(fā)生更新的SQL語(yǔ)句，記錄了所有的寫語(yǔ)句，并以事務(wù)的形式保存在磁盤中，還包含語(yǔ)句所執(zhí)行的消耗的時(shí)間，MySQL的二進(jìn)制日志是事務(wù)安全型的。

master上開(kāi)啟log_bin

$ vim /etc/my.cnf  
log-bin           = mysql-bin  #[必須]啟用二進(jìn)制日志  
server-id         = 4          #[必須]服務(wù)器唯一ID，默認(rèn)是1，最好取ip的后3位  
expire-logs-days  = 7           #只保留7天的二進(jìn)制日志，以防磁盤被日志占滿  
binlog-ignore-db  = mysql       #不備份的數(shù)據(jù)庫(kù)  
binlog-ignore-db  = information_schema  
binlog-ignore-db  = performation_schema  
binlog-ignore-db  = sys  
binlog-do-db=itools_simple #需要做復(fù)制的數(shù)據(jù)庫(kù)名，如果有多個(gè)，復(fù)制binlog-do-db即可 

登錄mysql，測(cè)試log_bin是否成功開(kāi)啟 

$ mysql -u root -p  
  Root@123  
// log_bin ON表示開(kāi)啟成功，OFF表示開(kāi)啟失敗  
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，并設(shè)置只讀 

$ 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)  
// 導(dǎo)出數(shù)據(jù)庫(kù)，進(jìn)入到一個(gè)合適的文件夾  
$ mysqldump -u root -p itools_simple > itools_simple.txt  
// 把生成的文件導(dǎo)出到本機(jī)  
$ sz itools_simple.txt  
// 在window本機(jī)打開(kāi)cmd，登錄slave本機(jī)mysql數(shù)據(jù)庫(kù)，導(dǎo)入數(shù)據(jù)庫(kù)  
$ .\mysql -u root -p -hlocalhost mycat_master < .\itools_simple.txt 

slave從數(shù)據(jù)庫(kù)配置

配置 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數(shù)據(jù)庫(kù) 

net stop mysql  
net start mysql 

登錄slave數(shù)據(jù)庫(kù) 

$ mysql -u root -p  
  123456  
// 停止 slave  
mysql> stop slave;  
// 根據(jù)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狀態(tài)  
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)  
// 出現(xiàn)下面兩個(gè)說(shuō)明配置正確  
 Slave_IO_Running: Yes  
Slave_SQL_Running: Yes 

關(guān)閉master數(shù)據(jù)庫(kù)的只讀設(shè)置 

mysql> unlock tables; 

存儲(chǔ)引擎

InnoDB特性

MySQL5.5版本后，MySQL的默認(rèn)內(nèi)置存儲(chǔ)引擎已經(jīng)從MyISAM變成InnoDB

•  支持事務(wù);
•  行級(jí)鎖定(更新數(shù)據(jù)時(shí)一般指鎖定當(dāng)前行)：通過(guò)索引實(shí)現(xiàn)、全表掃描忍讓時(shí)表鎖、注意間隙所的影響;
•  讀寫阻塞與事務(wù)的隔離級(jí)別相關(guān)；
•  具有非常高的緩存特性(既能緩存索引、也能緩存數(shù)據(jù))；
•  這個(gè)表和主鍵以組(Cluster)的方式存儲(chǔ)、組成一顆平衡樹；
•  所有的輔助索引(secondary indexes)都會(huì)保存主鍵信息；
•  支持分區(qū)、表空間類似與oracle 數(shù)據(jù)庫(kù)；
•  支持外鍵約束、不支持全文檢索(5.5.5之前的MyISAM支持全文檢索、5.5.5之后就不在支持);
•  相對(duì)MyISAM而言、對(duì)硬件的要求比較高

MyISAM特性

•  不支持事務(wù)
•  表級(jí)鎖定，數(shù)據(jù)更新時(shí)鎖定整個(gè)表：其鎖定機(jī)制是表級(jí)鎖定，這雖然可以讓鎖定的實(shí)現(xiàn)成本很小但是也同時(shí)大大降低了其并發(fā)性能。
•  讀寫互相阻塞：不僅會(huì)在寫入的時(shí)候阻塞讀取，myisam還會(huì)在讀取的時(shí)候阻塞寫入，但讀本身并不會(huì)阻塞另外的讀。
•  只會(huì)緩存索引：MyISAM可以通過(guò)key_buffer_size緩存索引，以大大提高訪問(wèn)性能，減少產(chǎn)品IO，但是這個(gè)緩存區(qū)只會(huì)緩存索引，而不會(huì)緩存數(shù)據(jù)。
•  讀取速度較快，占用資源相對(duì)少。
•  不支持外鍵約束，但支持全文索引。

修改slave數(shù)據(jù)庫(kù)存儲(chǔ)引擎 

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) 

修改表存儲(chǔ)引擎

如果是slave數(shù)據(jù)庫(kù)的表是MyISAM，master數(shù)據(jù)庫(kù)的表是InnoDB。直接覆蓋master數(shù)據(jù)庫(kù)來(lái)同步數(shù)據(jù)的話，slave數(shù)據(jù)庫(kù)表的存儲(chǔ)引擎也將會(huì)同步變成InnoDB。擴(kuò)展：InnoDB一棵B+樹可以存放多少行數(shù)據(jù)？

更換存儲(chǔ)引擎遇到的問(wèn)題

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)

出現(xiàn)的原因

在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。

如果我們開(kāi)啟了 bin-log, 我們就必須為我們的function指定一個(gè)參數(shù)。

解決方案 

mysql> set global log_bin_trust_function_creators=TRUE; 

Specified key was too long; max key length is 1000 bytes

出現(xiàn)的原因

1.  DB的 engine 是 MyISAM
2.  字符集是 utf8 ，1個(gè) utf8=3bytes
3.  (索引長(zhǎng)度總和) * 3 > 1000。

解決方案

1.  修改DB engine 至 innodb
2.  更改字符集
3.  減小字段長(zhǎng)度

注意：一定不要手動(dòng)去修改slave數(shù)據(jù)庫(kù)中的數(shù)據(jù)，需要給slave的用戶設(shè)置只讀。

至此，mysql的數(shù)據(jù)庫(kù)主從設(shè)置已經(jīng)配置成功。在master中修改數(shù)據(jù)庫(kù)，會(huì)同步到slave中。

Mycat基于MySQL的讀寫分離

Mycat不負(fù)責(zé)數(shù)據(jù)的同步，所以要還是要基于MySQL的主從配置來(lái)實(shí)現(xiàn)讀寫分離。

安裝Mycat

由于github限制，所以以后新版本從以下地址下載 http://dl.mycat.io

Linux創(chuàng)建文件夾/usr/local/mycat，進(jìn)入文件夾，下載安裝包 

$ 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（主機(jī)名）  
// 查看是否配置主機(jī)  
$ vim /etc/hosts 

將Mycat配置到環(huán)境變量中 

$ vim /etc/profile  
// 在最后添加  
MYCAT_HOME=/usr/local/mycat/mycat  
PATH=$MYCAT_HOME/bin:$PATH  
export PATH  
// 使配置生效  
$ source /etc/profile 

在master數(shù)據(jù)庫(kù)中添加user1（寫）、user2（只讀）兩個(gè)賬戶，并配置權(quán)限。

配置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">  
            <!-- 可以配置多個(gè)從庫(kù) -->  
            <readHost host="hostS2" url="127.0.0.1:3306" user="user2" password="Root@123" />  
        </writeHost>  
    </dataHost>  
</mycat:schema> 

配置mycat的server.xml，增加兩個(gè)用戶 

<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> 

啟動(dòng)Mycat

啟動(dòng)mycat 

$ mycat start  
Starting Mycat-server... 

查看啟動(dòng)日志 

$ cat wrapper.log  
MyCAT Server startup successfully. see logs in logs/mycat.log 

使用客戶端連接mycat

使用SQLyog連接(使用此方式連接，不能直接通過(guò)點(diǎn)擊表查看數(shù)據(jù))

使用Navicat連接

可通過(guò)客戶端直接查看master數(shù)據(jù)，也可通過(guò)修改mycat數(shù)據(jù)，查看master和slave的數(shù)據(jù)是否會(huì)同步

SpringBoot 整合 MyCat 實(shí)現(xiàn)讀寫分離

•  首先需要配置好數(shù)據(jù)庫(kù)的主從關(guān)系。
•  配置好MyCat服務(wù)。
•  實(shí)現(xiàn)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> 

創(chuàng)建數(shù)據(jù)源 

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 {  
    /**  
     * 創(chuàng)建可讀數(shù)據(jù)源  
     *  
     * @return  
     */  
    @Bean(name = "selectDataSource")  
    @ConfigurationProperties(prefix = "spring.datasource.select")  
    public DataSource dataSource1() {  
        return DataSourceBuilder.create().build();  
    }  
    /**  
     * 創(chuàng)建可寫數(shù)據(jù)源  
     *  
     * @return  
     */  
    @Bean(name = "updateDataSource")  
    @ConfigurationProperties(prefix = "spring.datasource.update")  
    public DataSource dataSource2() {  
        return DataSourceBuilder.create().build();  
    }  
} 

設(shè)置數(shù)據(jù)源 

package com.muycode.itoolsimple.datasource;  
import org.springframework.context.annotation.Lazy;  
import org.springframework.stereotype.Component;  
@Component  
@Lazy(false)  
public class DataSourceContextHolder {  
    /**  
     * 采用ThreadLocal 保存本地多數(shù)據(jù)源  
     */  
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();  
    /**  
     * 設(shè)置數(shù)據(jù)源類型  
     *  
     * @param dbType  
     */  
    public static void setDbType(String dbType) {  
        contextHolder.set(dbType);  
    }  
    /**  
     * 獲取數(shù)據(jù)源類型  
     */  
    public static String getDbType() {  
        return contextHolder.get();  
    }  
    public static void clearDbType() {  
        contextHolder.remove();  
    }  
} 

返回?cái)?shù)據(jù)源 

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;  
    /**  
     * 返回生效的數(shù)據(jù)源名稱  
     */  
    @Override  
    protected Object determineCurrentLookupKey() {  
        return DataSourceContextHolder.getDbType();  
    }  
    /**  
     * 配置數(shù)據(jù)源信息  
     */  
    @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();  
    }  
} 

創(chuàng)建切面，動(dòng)態(tài)設(shè)置數(shù)據(jù)源 

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設(shè)定AOP執(zhí)行順序 使之在數(shù)據(jù)庫(kù)事務(wù)上先執(zhí)行  
public class DataSourceOptionAop {  
    /**  
     * 可讀數(shù)據(jù)源  
     */  
    private final static String DATASOURCE_TYPE_SELECT = "selectDataSource";  
    /**  
     * 可寫數(shù)據(jù)源  
     */  
    private final static String DATASOURCE_TYPE_UPDATE = "updateDataSource";  
    /**  
     * 創(chuàng)建切面，根據(jù)方法類型選擇不同的數(shù)據(jù)源  
     *  
     * @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數(shù)據(jù)源-------------------");  
        } else {  
            DataSourceContextHolder.setDbType(DATASOURCE_TYPE_UPDATE);  
            System.out.println("-----------------使用updateDataSource數(shù)據(jù)源-------------------");  
        }  
    }  
} 

輸出結(jié)果 

=========== getByUsername-----------------使用selectDataSource數(shù)據(jù)源-------------------  
=========== getPermissionStringByUserId-----------------使用selectDataSource數(shù)據(jù)源-------------------  
=========== getPermissionByUserId-----------------使用selectDataSource數(shù)據(jù)源-------------------  
=========== getRolePermissionLinkByUserId-----------------使用selectDataSource數(shù)據(jù)源-------------------  
=========== save-----------------使用updateDataSource數(shù)據(jù)源-------------------  
=========== queryByPage-----------------使用selectDataSource數(shù)據(jù)源-------------------  
=========== save-----------------使用updateDataSource數(shù)據(jù)源-------------------  
=========== getPermissionAll-----------------使用selectDataSource數(shù)據(jù)源-------------------  
=========== save-----------------使用updateDataSource數(shù)據(jù)源-------------------  
=========== getSysCodeAll-----------------使用selectDataSource數(shù)據(jù)源-------------------  
=========== save-----------------使用updateDataSource數(shù)據(jù)源-------------------  
=========== getByRid-----------------使用selectDataSource數(shù)據(jù)源-------------------