天天看點
傳回

SpringBoot 整合 MyCat 實作讀寫分離default-character-set=utf8添加一下配置

SpringBoot 整合 MyCat 實作讀寫分離

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
sql_log_bin

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> 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資料庫

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

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%';
default_storage_engine InnoDB
default_tmp_storage_engine
disabled_storage_engines
internal_tmp_disk_storage_engine

4 rows in set (0.00 sec)

// 修改配置檔案

$ vim /etc/my.ini

[mysqld]
default-storage-engine=MyISAM
MyISAM

修改表存儲引擎

如果是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"?>

<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的server.xml，增加兩個使用者

<property name="password">Root@123</property>
<property name="schemas">itools_simple</property>
<property name="defaultSchema">itools_simple</property>           
<property name="password">Root@123</property>
<property name="schemas">itools_simple</property>
<property name="readOnly">true</property>
<property name="defaultSchema">itools_simple</property>

啟動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的資料是否會同步

首先需要配置好資料庫的主從關系。

配置好MyCat服務。

實作MyCat與MySQL讀寫分離。

添加依賴

<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>           
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.23</version>

建立資料源

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();
}

}

設定資料源

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();
}

傳回資料源

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 java.util.HashMap;

import java.util.Map;

@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();
}

建立切面，動态設定資料源

import org.aspectj.lang.JoinPoint;

import org.aspectj.lang.annotation.Aspect;

import org.aspectj.lang.annotation.Before;

import org.springframework.core.annotation.Order;

@Aspect

@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資料源-------------------

=========== getPermissionAll-----------------使用selectDataSource資料源-------------------

=========== getSysCodeAll-----------------使用selectDataSource資料源-------------------

=========== getByRid-----------------使用selectDataSource資料源-------------------

參考文章：

首發位址:

https://muycode.cn/article/mycat20200312.html

mysql5.7.18的安裝與主從複制

Mycat - 實作資料庫的讀寫分離與高可用

關于利用mycat結合springboot實作資料庫的讀寫分離（動态切換資料源）

原文位址

https://www.cnblogs.com/muycode/p/12603037.html
存儲 sql 緩存 NoSQL 關系型資料庫 mysql java 資料庫 開發工具 索引 java資料存儲 java代碼存儲資料 java存儲資料 java資料類型存儲 java存儲過程資料
上一篇: PEP 443 單分派泛型函數 -- Python官方文檔譯文 [原創]
下一篇: Python——疊代器的幾個進階用法This is a data for studentRows 100

繼續閱讀