実験環境
| 番号 | ホスト名 | IPアドレス | 備考 |
|---|---|---|---|
| 1 | mysql-master | 192.168.204.201 | MySQLマスター |
| 2 | mysql-slave | 192.168.204.202 | MySQLスレーブ |
| 3 | appserver | 192.168.204.111 | アプリケーションサーバー |
MySQLデータベースのインストールと設定
1. yumを使用してmysqlとmysql-serverをインストールする
yum install -y mariadb mariadb-server
2. MySQLサービスを起動する
systemctl start mariadb
systemctl enable mariadb
3. 起動状態を確認する
systemctl status mariadb
netstat -anpt | grep "mysql" --color
4. ファイアウォールで3306ポートを許可する
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
5. MySQLのパスワードを設定する
mysql_secure_installation
6. mysql-master上でデータベースを作成する
rootユーザーでMySQLにログインします。
mysql -uroot -p123456
データベースを作成し、データを追加します。
create database test_db;
show databases;
use test_db;
create table if not exists user_data(
user_id varchar(16) not null,
passwd varchar(32) not null,
name varchar(16) default '',
primary key (user_id)
)default charset=utf8;
show tables;
insert into user_data(user_id, passwd, name) values
('10001', '123456', 'Yamada'),
('10002', '123456', 'Sato'),
('10003', '123456', 'Tanaka'),
('10004', '123456', 'Suzuki'),
('10005', '123456', 'Kato');
select * from user_data where 1;
mysql-master上でデータベースへのアクセス権限を付与します。
GRANT all ON test_db.* TO 'db_admin'@'%' identified BY '123456';
flush privileges;
mysql-slave、appserver上でmysql-masterデータベースにログインします。
mysql -h 192.168.204.201 -uroot -p123456
mysql-master上でデータベースへのアクセス権限を取り消します。
REVOKE all ON test_db.* FROM 'db_admin'@'%';
flush privileges;
マスターとスレーブの2台のMySQLサーバーでレプリケーションを設定する
1. マスター上でbinlogログを有効にし、スレーブ用アカウントrepを作成する
binlogログの状態を確認する
show variables like 'log_bin';
my.cnf設定ファイルを変更する
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
MySQLを再起動し、binlogログを確認する
systemctl restart mariadb
mysql -uroot -p123456 -e "show variables like 'log_bin';"
mysql -uroot -p123456 -e "show master status;"
ここでFileとPositionの値を覚えておく
スレーブ用アカウントを作成する
grant replication slave on *.* to rep_user@'192.168.204.202' identified by '123456';
show grants for rep_user@'192.168.204.%';
2. マスター上で既存のデータベースをバックアップする
マスターをロックする
flush tables with read lock;
マスターをバックアップする
mysqldump -uroot -p123456 --all-databases | gzip > /root/db_backup_`date '+%Y-%m-%d'`.sql.gz
バックアップファイルをスレーブにコピーする
scp db_backup_*.sql.gz root@192.168.204.202:/root
3. スレーブを設定し、スレーブ上でデータを復元する
スレーブのserver-idを設定し、binlogログを無効にする
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
# vim /etc/my.cnf
#log-bin=mysql-bin
#binlog_format=mixed
server-id = 20
4. スレーブのMySQLを再起動する
MySQLサービスを再起動する
systemctl restart mariadb
log_binとserver_idの値を確認する
show variables like 'log_bin';
show variables like 'server_id';
5. スレーブにデータを復元する
gzip -d /root/db_backup_*.sql.gz
mysql -uroot -p123456 < /root/db_backup_*.sql
mysql -uroot -p123456 -e "show databases;"
6. スレーブ上でレプリケーションパラメータを設定する
スレーブ上でレプリケーションパラメータを設定する
MASTER_LOG_FILEとMASTER_LOG_POSの値を上記マスターで確認した値に置き換える
change master to
MASTER_HOST='192.168.204.201',
MASTER_USER='rep_user',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;
スレーブ上でレプリケーションを開始する
start slave;
スレーブ上でレプリケーション状態を確認する
show slave status \G;
両方ともYesであれば成功
マスターとスレーブのMySQLサービスを再起動する
systemctl restart mariadb
マスター上でtest_dbにレコードを追加し、スレーブで同期状況を確認する
-- mysql-master
insert into test_db.user_data (user_id, passwd, name) values ('20001', '123456', 'Takahashi');
select * from test_db.user_data where 1;
-- mysql-slave
select * from test_db.user_data where 1;
マスター・スレーブレプリケーションが実現されました
appserver上でMySQLの読み書き分離を設定する
1. appserver上でmysql-proxyをインストールする
https://downloads.mysql.com/archives/proxy/からmysql-proxyをダウンロードします。
cd ~
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
tar -xzvf mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
cp -r mysql-proxy-0.8.5-linux-sles11-x86-64bit /usr/local/mysql-proxy
2. appserver上でmysql-proxyを設定する
メイン設定ファイルを作成する
cd /usr/local/mysql-proxy
mkdir lua # スクリプト格納ディレクトリの作成
mkdir logs # ログディレクトリの作成
cp share/doc/mysql-proxy/rw-splitting.lua ./lua/ # 読み書き分離設定ファイルのコピー
cp share/doc/mysql-proxy/admin-sql.lua ./lua/ # 管理スクリプトのコピー
vim /etc/mysql-proxy.conf # 設定ファイルの作成
メイン設定ファイルの内容
使用前にコメントを削除してください
#vim /etc/mysql-proxy.conf
[mysql-proxy]
user=root # mysql-proxyを実行するユーザー
admin-username=proxy_user # マスターとスレーブで共通のユーザー
admin-password=123456 # ユーザーのパスワード
proxy-address=127.0.0.1:3306 # mysql-proxyの実行IPとポート、ポートを指定しない場合は4040
proxy-read-only-backend-addresses=192.168.204.202 # バックエンドスレーブから読み取り
proxy-backend-addresses=192.168.204.201 # バックエンドマスターに書き込み
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua # 読み書き分離設定ファイルの場所
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua # 管理スクリプトの場所
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log # ログの場所
log-level=info # ログレベルの定義 (error|warning|info|message|debug)
権限を変更する
chmod 660 /etc/mysql-proxy.conf
3. appserver上で読み書き分離設定ファイルを変更する
vim /usr/local/mysql-proxy/lua/rw-splitting.lua
以下の内容を変更します。
--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, -- デフォルトで4以上の接続数で読み書き分離を開始
max_idle_connections = 1, -- デフォルトは8
is_debug = false
}
end
4. appserver上でmysql-proxyを起動する
起動する
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.conf --daemon
プロセスを確認する
netstat -anpt | grep 3306
5. mysql-masterとmysql-slave上でそれぞれmyproxyに権限を付与する
mysql-masterとmysql-slave上でmysql-proxyに権限を付与します。
grant all on *.* to 'proxy_user'@'192.168.204.%' identified by '123456';
flush privileges;
6. appserver上でmysql-proxyに接続し、読み書き分離をテストする
appserver上でmysql-proxy経由でデータベースを操作します。
mysql -h 127.0.0.1 -uproxy_user -p123456 -e "select * from test_db.user_data where 1;"
mysql -h 127.0.0.1 -uproxy_user -p123456 -e "insert into test_db.user_data (user_id, passwd, name) values ('30001', '123456', 'Ito');"
mysql -h 127.0.0.1 -uproxy_user -p123456 -e "select * from test_db.user_data where 1;"
mysql-master上で確認する
mysql -uroot -p123456 -e "select * from test_db.user_data where 1;"
mysql-slave上で確認する
mysql -uroot -p123456 -e "select * from test_db.user_data where 1;"
検証の結果、読み書き分離が実現されました