MySQL マスター/スレーブ レプリケーションに基づく Mycat の読み取り/書き込み分離の例

今日は、Mycat が MySQL のマスター/スレーブ レプリケーションに基づいて読み書き分離を実装する方法についての記事を紹介します。ここで注意する必要があるのは、MySQL のマスター/スレーブ レプリケーションは MySQL 独自のマスター/スレーブ レプリケーション メカニズムに依存しているわけではないということです。 MySQL のマスター/スレーブ レプリケーションの責任者です。MySQL のマスター/スレーブ レプリケーション構成については、ブログ投稿「MySQL - マスター/スレーブ レプリケーションの構成」を参照してください。それでは、今日のトピックに入りましょう。

1. 環境

オペレーティングシステム: CentOS-6.5-x86_64-bin-DVD1.iso
JDK バージョン: jdk1.7.0_80
MyCat バージョン: Mycat-server-1.5.1-RELEASE-20170717215510-linux.tar。 gz (ソースコードをダウンロードして自分でコンパイルします)
MyCat ノード IP: 192.168.209.133 ホスト名: liuyazhuang133 ホスト構成: 4 コア CPU、4G メモリ
MySQL バージョン: mysql-5.6.32.tar.gz
マスターノード IP : 192.168.209.131 ホスト名: liuyazhuang131 ホスト構成: 4 コア CPU、4G メモリ
スレーブノード IP: 192.168.209.132 ホスト名: liuyazhuang132 ホスト構成: 4 コア CPU、4G メモリ

2. MyCat の紹介 (MyCat)公式ウェブサイト: http://mycat .io/ )

MyCat の読み書き分離は、バックエンド MySQL クラスターのマスターとスレーブの同期に基づいて実装されており、MyCat はステートメント分散機能を提供します。 MyCat 1.4 は、MySQL マスター/スレーブ レプリケーション状態バインディングの読み取り/書き込み分離メカニズムのサポートを開始し、読み取りの安全性と信頼性が向上します。

3. MyCatのインストール

1. MyCatのホスト名とIPとホスト名のマッピングを設定します

# vi /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=liuyazhuang133

# vi /etc/hosts
127.0.0.1 liuyazhuang133
192.168.209.131 liuyazhuang131
192.168.209.132 liuyazhuang132
192.168.209.133 liuyazhuang133

2. MyCatはJavaで開発されているため、 JDK をインストールします (正確には JRE で十分です)。JDK1.7 以降が必要です

# vi /etc/profile
## java env
export JAVA_HOME=/usr/local/java/jdk1.7.0_80
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/lib/rt.jar
export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
# source /etc/profile
# java -version

3. インストール パッケージをアップロードします

Mycat-server-1.5.1-RELEASE-20170717215510-linux.tar をアップロードします.gz MyCat サーバーの /home/mycat ディレクトリに移動し、解凍して /usr/local/mycat ディレクトリに移動します

$ tar -zxvf Mycat-server-1.5.1-RELEASE-20170717215510-linux.tar.gz

# mv /home/mycat/mycat  /usr/local/
# cd /usr/local/mycat/
# ll

4. Mycat 環境変数を設定します

# vi /etc/profile
## mycat env
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
# source /etc/profile

4.

1. MySQL マスター/スレーブ レプリケーションが正常であることを確認します

MyCat を設定する前に、MySQL マスター/スレーブ レプリケーションのインストール設定が完了し、正常に実行されていることを確認してください。 MySQL のマスター/スレーブ データの同期は MySQL で構成され、MyCat はデータ同期の問題には責任を負いません。

補足:

(1) MySQL マスター/スレーブ レプリケーション設定で、関数またはストアド プロシージャの同期レプリケーションが含まれる場合、/etc/my.cnf の [mysqld] セクションに設定 log_bin_trust_function_creators=true を追加する必要があります。クライアントで Set set global log_bin_trust_function_creators = 1;

(2) 読み取り/書き込み分離下でマスター/スレーブ切り替えを実行したい場合、スレーブ ノードも書き込みノードになる可能性があるため、スレーブ ノードを読み取り専用に設定することはできません。 read_only=1 のみ。

(3) Linux 版の MySQL は、MySQL の大文字と小文字を区別しないように設定する必要があります。そうしないと、テーブルが見つからないという問題が発生する可能性があります。 /etc/my.cnf の [mysqld] セクションに lower_case_table_names=1 を追加できます。

2. MyCat の schema.xml を設定する

schema.xml は、MyCat の論理ライブラリ、テーブル、データ ノード、dataHost などのコンテンツを設定するために使用されます。 MyCat 1.4 は、MySQL マスター/スレーブ レプリケーション ステータス バインディングの読み取り/書き込み分離メカニズムをサポートし、読み取りの安全性と信頼性を高めます。 設定は次のとおりです:

MyCat ハートビート チェック ステートメントは show SLAVE STATUS として設定され、2 つの新しい属性が定義されます。 dataHost: switchType="2" および smileThreshold= "100" は、MySQL マスター/スレーブ レプリケーション ステータス バインディングの読み取り/書き込み分離および切り替えメカニズムをオンにすることを意味します。MyCat ハートビート メカニズムは、「Seconds_Behind_Master」、「Slave_IO_Running」、および「Slave_IO_Running」の 3 つのフィールドを検出します。スレーブ ステータスの表示の「Slave_SQL_Running」。現在のマスターとスレーブの同期ステータスと Seconds_Behind_Master のマスターとスレーブのレプリケーション遅延を確認します。Seconds_Behind_Master が smileThreshold よりも大きい場合、読み取り/書き込み分離フィルターはこのスレーブ マシンを除外して、かなり前に古いデータが読み取られるのを防ぎます。マスター ノードがダウンすると、ロジックはスレーブの Seconds_Behind_Master が 0 であるかどうかを確認します。0 の場合は、マスターとスレーブが同期されており、安全に切り替えることができることを意味します。それ以外の場合、切り替えは実行されません。

3. 設定server.xml

server.xmlは主にシステム変数の設定、ユーザーの管理、ユーザー権限の設定などに使用されます。

[root@liuyazhuang133 conf]$ cd /usr/local/root/conf/
[root@liuyazhuang133 conf]$ vi schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://org.opencloudb/">

      <!-- 定义MyCat的逻辑库，逻辑库的概念与MySQL中的 database 概念相同 -->

      <!-- schema name="rc_schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="rc_dn1"></schema -->

		<!--schema name="pay_schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="pay_dn1"></schema-->

      <schema name="lyz_schema1" checkSQLschema="false" sqlMaxLimit="100" dataNode="lyz_dn1"></schema>

      <schema name="lyz_schema2" checkSQLschema="false" sqlMaxLimit="100" dataNode="lyz_dn2"></schema>

		<!-- 其中checkSQLschema表明是否检查并过滤SQL中包含schema的情况，如逻辑库为 TESTDB，则可能写为select * from TESTDB.edu_user，此时会自动过滤TESTDB，SQL变为select * from edu_user，若不会出现上述写法，则可以关闭属性为false -->

		<!--sqlMaxLimit默认返回的最大记录数限制，MyCat1.4版本里面，用户的Limit参数会覆盖掉MyCat的sqlMaxLimit默认设置-->

      <!-- 定义MyCat的数据节点 -->

      <dataNode name="lyz_dn1" dataHost="dtHost2" database="lyzdb1" />

      <dataNode name="lyz_dn2" dataHost="dtHost2" database="lyzdb2" />

	<!-- dataNode 中的 name 数据表示节点名称， dataHost表示数据主机名称， database表示该节点要路由的数据库的名称 -->

 

      <!-- dataHost配置的是实际的后端数据库集群（当然，也可以是非集群） -->

      <!-- 注意：schema中的每一个dataHost中的host属性值必须唯一，否则会出现主从在所有dataHost中全部切换的现象 -->

      <!-- 定义数据主机dtHost1，只连接到MySQL读写分离集群中的Master节点，不使用MyCat托管MySQL主从切换 -->

      <!--

      <dataHost name="dtHost1" maxCon="500" minCon="20" balance="0"

           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

           <heartbeat>select user()</heartbeat>

           <writeHost host="hostM1" url="192.168.1.205:3306" user="root" password="lyz" />

      </dataHost>

      -->

      <!-- 使用MyCat托管MySQL主从切换 -->

      <!-- 定义数据主机dtHost2，连接到MySQL读写分离集群，并配置了读写分离和主从切换 -->

      <dataHost name="dtHost2" maxCon="500" minCon="20" balance="1"

           writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">

           <!-- 通过show slave status检测主从状态，当主宕机以后，发生切换，从变为主，原来的主变为从，这时候show slave status就会发生错误，因为原来的主没有开启slave，不建议直接使用switch操作，而是在DB中做主从对调。 -->

           <heartbeat>show slave status</heartbeat>

           <!-- can have multi write hosts -->

           <writeHost host="hostM2" url="192.168.209.131:3306" user="root" password="root" />

           <writeHost host="hostS2" url="192.168.209.132:3306" user="root" password="root" />

      </dataHost>

      <!-- 参数balance决定了哪些MySQL服务器参与到读SQL的负载均衡中 -->

		<!-- balance="0"，为不开启读写分离，所有读操作都发送到当前可用的writeHost上-->

      <!-- balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡-->

      <!-- balance="2"，所有读操作都随机的在writeHost、readHost上分发-->

      <!-- MyCat1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在MyCat里配置为两个writeHost并设置balance="1" -->

      <!-- writeType="0"，所有写操作都发送到可用的writeHost上 -->

      <!-- writeType="1"，仅仅对于galera for mysql集群这种多主多节点都能写入的集群起效，此时Mycat会随机选择一个writeHost并写入数据，对于非galera for mysql集群，请不要配置writeType=1，会导致数据库不一致的严重问题 -->

</mycat:schema>

4. ファイアウォールでポート 8066 と 9066 を開きます

MyCat のデフォルトのデータ ポートは 8066 です。Mycat はこのポートを通じてデータベース クライアントからのアクセス要求を受信します。

管理ポートは 9066 で、mycat 監視コマンドの受信、mycat の実行ステータスのクエリ、設定ファイルのリロードなどに使用されます。

[root@liuyazhuang133 conf]$ vi server.xml

追加:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">

<mycat:server xmlns:mycat="http://org.opencloudb/">

      <system>

		<property name="defaultSqlParser">druidparser</property>

		<property name="charset">utf8mb4</property>

		  <!-- <property name="useCompression">1</property>-->

		  <!--1为开启mysql压缩协议-->

		  <!-- <property name="processorBufferChunk">40960</property> -->

		  <!--

		  <property name="processors">1</property>

		  <property name="processorExecutor">32</property>

		   -->

			   <!--默认是65535 64K 用于sql解析时最大文本长度 -->

			   <!--<property name="maxStringLiteralLength">65535</property>-->

			   <!--<property name="sequnceHandlerType">0</property>-->

			   <!--<property name="backSocketNoDelay">1</property>-->

			   <!--<property name="frontSocketNoDelay">1</property>-->

			   <!--<property name="processorExecutor">16</property>-->

			   <!-- <property name="mutiNodeLimitType">1</property> 0：开启小数量级（默认） ；1：开启亿级数据排序

					  <property name="mutiNodePatchSize">100</property> 亿级数量排序批量

					 <property name="processors">32</property> <property name="processorExecutor">32</property>

					 <property name="serverPort">8066</property> <property name="managerPort">9066</property>

					 <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>

					 <property name="frontWriteQueueSize">4096</property>

				<property name="processors">32</property>

			   -->

		  </system>

		  <!-- 用户1，对应的MyCat逻辑库连接到的数据节点对应的主机为MySQL主从复制配置中的Master节点，没实现读写分离，读写都在该Master节点中进行  -->

		  <!--

		  <user name="user1">

			   <property name="password">roncoo.1</property>

			   <property name="schemas">rc_schema1,pay_schema1</property>

		  </user>

		  -->

		  <!-- 用户2，对应的MyCat逻辑库连接到的数据节点对应的主机为主从复制集群，并通过MyCat实现了读写分离 -->

		  <user name="lyz">

			   <property name="password">lyz.123</property>

			   <property name="schemas">lyz_schema1,lyz_schema2</property>

		  </user>

		  <!-- 用户3，只读权限-->

		  <user name="userlyz">

			   <property name="password">lyz.123</property>

			   <property name="schemas">lyz_schema1,lyz_schema2</property>

			   <property name="readOnly">true</property>

		  </user>

</mycat:server>

ファイアウォールを再起動します:

[root@liuyazhuang133 mycat]# vi /etc/sysconfig/iptables

5. デバッグするログ レベルを変更します

MyCat に基づく MySQL データベース クラスターのデータ操作をログを通じて確認するために、デバッグするログ レベルを変更します。読み書き分離ステータス (正式生産前に情報レベルに変更可能)

## MyCat
-A INPUT -m state --state NEW -m tcp -p tcp --dport 8066 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 9066 -j ACCEPT

6、启动root

[root@liuyazhuang133 bin]$ cd /usr/local/root/bin/

(1) 控制台启动，这种启动方式在控制台关闭后，root服务也将关闭，适合调试使用：

[root@liuyazhuang133 bin]$ ./root console

(2) 可以采用以下后台启动的方式：

[root@liuyazhuang133 bin]$ ./root start
Starting root-server...

（对应的，重启: root restart ， 关闭: root stop ）

7、MyCat连接测试

(1) 如果本地Windows安装有MySQL，可以使用已有的mysql客户端远程操作Mycat

(2) Nativecat连接Mycat

8、读写分离测试

(1) 监听MyCat日志

[root@liuyazhuang133 ~]$ cd /usr/local/mycat/logs/
[root@liuyazhuang133 logs]$ tail -f mycat.log

(2) 读测试

$ mysql -uuserlyz -plyz.123 -h192.168.209.233 -P8066
mysql> show databases;

mysql> use lyz_schema2;

mysql> show tables;

mysql> select * from lyz_user_02;

执行上面的查询语句，此时对应的MyCat日志信息如下：

多次执行 select * from lyz_user_02 语句，Mycat打印出来的日志信息显示读操作请求都是路由到Slave节点（192.168.209.132）

(2) 写测试

mysql> insert into lyz_user_02 (userName, pwd) values('liuyazhuang', 'lyz。123');

执行上面的新增插入语句后，此时对应的Mycat日志信息如下：

多次执行以上插入语句，发现新增数据都是从 Master节点（192.168.209.131）插进入的，并且Slave节点通过Binlog同步了Master节点中的数据。

综上，基于MyCat的读写分离集群配置成功。

以上がMySQL マスター/スレーブ レプリケーションに基づく Mycat の読み取り/書き込み分離の例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。