Mysql マスタースレーブ同期、読み書き分離、ミドルウェアの使用記録

-- 本文は、Mysql のマスタースレーブ同期と読み書きの分離の設定方法、およびミドルウェアの ProxySQL を使用してより効率的なデータベースアクセスを実現する方法について説明します。

サーバー情報#

Mysql

マスターサーバー（A）：192.168.1.1
バックアップサーバー（B）：192.168.1.2
バックアップサーバー（C）：192.168.1.3
ミドルウェア

ミドルウェアサーバー（D）：192.168.1.4

Mysql のマスタースレーブ同期、読み書き分離の設定#

マスターデータベースの設定#

マスターサーバー（A）で以下のコマンドを実行します：
`# スレーブアクセスアカウントの作成
grant replication slave on . to 'slave'@'%' identified by '123456';

スレーブサーバーの設定#

スレーブサーバーで以下のコマンドを実行します：

スレーブを停止#

stop slave;

マスターサーバーの情報を設定#

change master to master_host='192.168.1.1', master_user='slave', master_password='123456', master_log_file='mysql-bin.000006', master_log_pos=438;

スレーブを開始

start slave;

スレーブの状態を確認

show slave status\G;

スレーブの状態が以下のように表示される場合、起動に成功しています：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

ProxySQL の設定#

インストール
[https://github.com/sysown/proxysql/releases] にアクセスして、ProxySQL のインストールパッケージをダウンロードします。
インストールパッケージをサーバーにアップロードし、以下のコマンドを実行してインストールします：

yum -y localinstall proxysql-2.5.5-1-centos7.x86_64.rpm

インストール状態の確認#

以下のコマンドを実行して、ProxySQL のインストール状態を確認します：

proxysql -V
ProxySQL version 2.5.5-10-g195bd70, codename Truls

サービスの起動#

以下のコマンドを実行して、ProxySQL サービスを起動します：

systemctl start proxysql.service

ローカル ProxySQL サーバーに入る#

以下のコマンドを実行して、ローカル ProxySQL サーバーに入ります：

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password

マスターとスレーブサーバーの追加#

ProxySQL ローカルサーバーで以下のコマンドを実行して、マスターとスレーブサーバーを追加します：

insert into mysql_servers(hostgroup_id, hostname, port, weight, comment) values(1, '192.168.1.1', 3306, 1, ' マスター ');
insert into mysql_servers(hostgroup_id, hostname, port, weight, comment) values(2, '192.168.1.2', 3306, 5, ' スレーブ1 ');
insert into mysql_servers(hostgroup_id, hostname, port, weight, comment) values(2, '192.168.1.3', 3306, 5, ' スレーブ2 ');

マスターとスレーブアカウントの追加（ProxySQL）#

ProxySQL ローカルサーバーで以下のコマンドを実行して、マスターとスレーブアカウントを追加します：

insert into mysql_users(username, password, default_hostgroup, transaction_persistent) values('proxysql', '123456', 1, 1);

ルールルーティングの追加#

ProxySQL ローカルサーバーで以下のコマンドを実行して、ルールルーティングを追加します：

insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, log, apply) values(1, 1, '^UPDATE', 1, 1, 1);
insert into mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, log, apply) values(2, 1, '^SELECT', 2, 1, 1);

接続状態とヒット率のクエリ#

ProxySQL ローカルサーバーで以下のコマンドを実行して、接続状態とヒット率のクエリを確認します：

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
select * from stats_mysql_query_rules;
select * from stats_mysql_query_digest;

上記の設定とコマンドを使用することで、Mysql のマスタースレーブ同期と読み書き分離を実現し、ProxySQL ミドルウェアを使用してデータベースアクセスを最適化することができます。他のユーザーは、この記事からこれらの機能の設定と使用方法を学びます。