MySQL(機能編)

#概要
MySQL RDBMSを始めるにあたり、基本的な構築方法や機能を当ブログにまとめたい。

#1.MySQL インストール
とりあえず簡易インストール手順

MySQLインストール手順

###1.yumレポジトリ追加
rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

#2.yumインストール
yum -y install mysql-community-server

#3.バージョン確認
mysqld --version

#4.起動停止はRHEL7からはsystemdで実施
systemctl start mysqld.service
systemctl stop mysqld.service
systemctl enable mysqld.service

#5.MySQL の初期パスワード確認
##初回起動時にrootのパスワードが出力されている。これでrootの初期パスワードがわかる。
cat /var/log/messages /var/log/mysqld.log | grep -w password

#6.mysqlインストール(パスワードはリモートログイン可否などセキュリティ系)
systemctl start mysqld
mysql_secure_installation

#7.mysqlサーバログイン
mysql -u root -p<パスワード>

#2.MySQLのアーキテクチャ
MySQLサーバーはOS上で１つのプロセスとして動作する。クライアントから実行されたSQLの処理やディスク上のデータのIOは内部の複数のスレッドが担当する「シングルプロセスマルチスレッド型」である。
内部は接続管理やSQL文の構文解析を行う部分と、データやトランザクション管理を行う２層構造。後者は「プラガブル・ストレージエンジン機能」と呼ぶ。中でもInnoDB(デフォルト)は多くのアプリで必要になるACID特性を持つトランザクションを唯一保証する。外部キーをサポートするのもInnoDBだけ。

利用可能なストレージエンジン

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

#3.MySQLサーバのMain設定ファイル
バッファメモリ・ポート・データ格納場所・接続数・DB文字コード・ログ等基本設定を実施

基本的な設定

# mysqld基本設定
mkdir -p /var/lib/mysql/binlog /var/log/mysql && chown -R mysql:mysql /var/lib/mysql/binlog /var/log/mysql
cat << EOF > /etc/my.cnf
[mysqld]
# ソケットオブジェクト
socket=/var/lib/mysql/mysql.sock

#シンボリックリンク許可有無(1許可,0拒否)
symbolic-links=0

#InnoDBストレージエンジンのデータバッファ領域サイズ
innodb_buffer_pool_size=378M

#ソート用のメモリー領域
sort_buffer_size=256M

#データファイルの格納先ディレクトリ
datadir=/var/lib/mysql

#TCP/IPポート番号
port=3306

#MySQLサーバーの文字コードをUTF8(最大長4バイト)
character_set_server=utf8mb4

#最大同時接続数
max_connections=100

###バイナリログ。バックアップログとも呼ばれ、データ変更の情報をバイナリ情報で記録
#マスターでは1,スレーブでは1以外
server-id=1
#バイナリログ本体
log-bin=/var/lib/mysql/binlog/mysql-bin-log
#バイナリログ一覧を管理するファイル
log_bin_index=/var/lib/mysql/binlog/mysql-bin-log.list
#ログ最大サイズ（最大いったらローテーション)
max_binlog_size=50M
#binlogのフォーマット。STATEMENTはSQLベース、ROWは行(実データ)ベース。
binlog_format=ROW
#ログ保管期限(日)
expire_logs_days=2
#ログを取るデータベース名指定
binlog-do-db=db01
#ログを取らないデータベース名指定
binlog-ignore-db=XXX

#一般クエリログを採取するか否か示します。1:ロギングする、0:ロギングしない
general_log=1
#一般クエリログの出力先を指定(FILE/TABLE/NONE)
log_output=FILE
#一般クエリログ出力先
general_log_file=/var/log/mysql/general_query.log

#エラーログ
log-error=/var/log/mysql/error.log

###一定時間以上クエリに時間がかかったSQLやインデックスが利用されていないSQLを記録することが可能
# slow queryログの有効化
slow_query_log=1
# ファイルパス
slow_query_log_file=/var/log/mysql/slow_query.log
# 10秒以上処理に時間がかかったクエリを記録
long_query_time=5
# インデックスが使用されていないクエリをログに出力
log-queries-not-using-indexes

[client]
# クライアント文字コード
default-character-set=utf8

!includedir /etc/my.cnf.d
EOF

# mysqlプロンプト改造
cat << EOF > ~/.my.cnf
[mysql]
prompt='\\u@\\h [\\d] \\R:\\m:\\s>\\_'
EOF

# mysqld再起動
systemctl restart mysqld

# ログイン実施
mysql -h localhost -u root -pP#ssw0rd 

#4.MYSQLクライアント関連
MySQLサーバに接続するにあたり、以下のようなパラメータを指定する。

• クライアント接続用各種パラメータ(mysqlコマンド)

Option(短縮系)	Default値	Center align
--protocol(無し)	TCP	通信プロトコル
--host(-h)	localhost	MySQLサーバが稼働するマシンホスト名orIP
--port(-P)	3306	MySQLが稼働するTCP/IPポート番号
--user(-u)	無し	接続ユーザ名
--password(-p)	無し	接続ユーザのパスワード
--default-character-set(無し)	latin1	クライアントプログラム使用の文字コード
--compress(-c)	無し	通信プロトコル圧縮有無

• クライアント文字コード。基本、特別な要件がなければ、サーバに文字コードを合わせる。

クライアント文字コードset

cat << EOF > /etc/my.cnf
[client]
default-character-set=utf8
EOF

クライアント文字コード確認

root@localhost [db01] 23:06: > show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

• 接続時にSQLをバッチ実行

コマンドラインにべた書き
mysql -u XXX -pXXX -P 3306 db1 -e 'select * from users';

SQLファイル実行時は標準入力に指定
mysql -u XXX -pXXX -P 3306 db1 < SQLファイル名

• mysqlコマンドで接続した時のプロンプトのカスタマイズ

mysqlプロンプトに情報を付与

\c ・・・コマンド何回実行したかを数えるカウンタ。
\D ・・・日時
\d ・・・利用中のデフォルトデータベース
\h ・・・接続先ホスト（サーバ）
\l ・・・デリミタ（区切り文字、デフォルトはセミコロン）
\m ・・・分
\n ・・・改行
\O ・・・月
\o ・・・月（数字）
\P ・・・am/pm
\p ・・・TCP/IPポート番号、またはソケットファイル名
\R ・・・時（24時間制）
\r ・・・時（12時間制）
\S ・・・セミコロン
\s ・・・秒
\t ・・・タブ
\U ・・・ユーザ名（ホスト名含む）
\u ・・・ユーザ名
\v ・・・MySQLサーバのバージョン
\w ・・・曜日
\Y ・・・年（4桁）
\y ・・・年（2桁）
\_ ・・・空白
\  ・・・空白（スラッシュの後に空白文字）
\' ・・・シングルクォーテーションマーク
\" ・・・ダブルクォーテーションマーク
\\ ・・・バックスラッシュ

#5.DBデータバックアップ/リストア

mysqldump

• MySQL 標準のバックアップツール。論理バックアップ取得が可能

DBオフラインバックアップ

mysqldを停止した後にデータ領域をバックアップ(OS:tarコマンド/LVMスナップショット)

DBオンラインバックアップ

1)InnoDB：mysqldumpによる論理バックアップ

特定のデータベースをバックアップ

mysqldump --flush-logs --single-transaction --master-data=2 -u root -pP#ssw0rd db01 > ダンプファイル名

2)全データベースをバックアップ(-xは対象テーブルをロックする)

全データベースをバックアップ

mysqldump --flush-logs --single-transaction --master-data=2 -u root -pP#ssw0rd --all-databases > ダンプファイル名

–single-transaction
このオプションは InnoDB のトランザクションを利用してバックアップを取得。
例えばバックアップの対象となるデータが膨大で、バックアップに１時間かかる場合でもトランザクションを利用してくれるので、バックアップを開始した時点のデータも(1時間後に)終了する間際のデータも全く整合性のとれたデータであることを保証。バックアップ開始時点の断面が基本的に保証される。

–flush-logs
その時点のバイナリログがローテートされて新しいファイルが作成されるオプション。
復元時は新しく作成されたバイナリログの先頭から リカバリを行えば良く、古いバイナリログを無視できるという便利機能。

3)一部のデータベースのみリストア

mysql -u root -pP#ssw0rd wordpress < ダンプファイル名

4)データベースリストア

mysql -u root -pP#ssw0rd < ダンプファイル名

※リストアはあらかじめデータベースは作っておく必要がある。
mysqldumpはあくまで対象を作るためのSQLのバックアップ

mysqlbackup

• Mysql Enterprise Backup で提供されている有償版バックアップツール

試用版でバックアップ取得してみる

インスタンス全体バックアップ

mysqlbackup --user=sample -pP#ssw0rd --socket=/var/lib/mysql/mysql.sock --backup-dir=/backup/test --compress backup-and-apply-log

backup-and-apply-log
データファイルをバックアップ後に、バックアップ中に行われた更新をbinlogから抜いてバックアップファイルに適用することで一貫性の取れたバックアップを取得するためのオプション

compress
バックアップファイルを圧縮する。その分CPUリソースを使う＆時間はかかる。

#6.レプリケーション

マスタサーバの更新をbinlogを転送することでスレーブのDBサーバに配布する仕組み。スレーブサーバを複数台持つことができる。ユースケースとしては以下のようなことが期待できる。

• DBの冗長化
• DR環境
• バックアップをスレーブで実施できるため、バックアップ時の静止点の確保やマスタ負荷の軽減に役立つ
• 読み取り専用のスレーブを用意することで参照処理はスレーブで実施。マスタの負荷軽減。

フローとしては以下となる。

1. マスタはマスタのバイナリログに処理の内容を記録する。
2. スレーブ側のI/Oスレッドが、マスタからバイナリログを取得する。
3. スレーブ側のSQLスレッドが、取得したバイナリログをスレーブ側DBに反映する。

下記、マスタ・スレーブ1台ずつ、かつデータベース全体をレプリケーションする前提で実施方法を記す。

◆通常方式

###[1].レプリケーション向け設定my.cnf(マスター側)

レプリケーション向けパラメータ

#binlogパス
logbin=<binlogファイル名フルパス>
#レプリケーション範囲で一意な値
server_id=1

###[2].レプリケーション用のユーザを作成(マスター側)

レプリケーション用ユーザ作成

CREATE USER 'repl'@'%' IDENTIFIED BY 'P#ssw0rd';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

※「%」は本来的にはスレーブサーバのIPアドレスだが検証のためフルオープンしているのみ。

###[3].データベースフルバックアップ(マスター側)

バックアップ

#フルバックアップ
mysqldump --flush-logs --single-transaction --master-data=2 -u root -pP#ssw0rd --all-databases > fullbk.db

###[4].レプリケーション向け設定my.cnf(スレーブ側)

レプリケーション向けパラメータ

#binlogパス
logbin=<binlogファイル名フルパス>
#マスターとは異なる値にする
server_id=2
#読み取り専用にする場合必要
read_only=1
#スーパーユーザも読み取り専用にする場合必要(後続のリストアでは書き込みの必要ありなのでこの時点では#)
#super_read_only=1

###[5].マスターDBリストア(スレーブ側)

リストア

#フルリストア
mysql -u root -pP#ssw0rd < fullbk.db

#レプリケーションを開始するbinlogの位置を調べる
grep "CHANGE MASTER TO" fullbk.db  | head -1

Output↓
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-log.000015', MASTER_LOG_POS=154;

###[6].レプリケーション開始(スレーブ側)

レプリケーション開始

#レプリケーション元のマスター情報指定
CHANGE MASTER TO
MASTER_HOST='db1',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='P#ssw0rd',
MASTER_LOG_FILE='mysql-bin-log.000015',
MASTER_LOG_POS=154;

#レプリケーション開始
START SLAVE;

#レプリケーション状況確認
SHOW SLAVE STATUS\G

~~~何かエラーがあれば下記に情報がある~~~
Last_IO_Error
Last_SQL_Error

###[7].レプリケーション停止(スレーブ側)
スレーブ側で、IOスレッド(マスター側へ更新ログをリクエストして受け取る)とSQLスレッド(スレーブ側に更新反映)を停止

レプリケーション停止

#一括停止の場合
STOP SLAVE;

#IOスレッド、SQLスレッド個別停止
STOP IO_SLAVE;
STOP SQL_SLAVE;

GTID方式

GTIDはサーバのUUIDとトランザクションIDの組である。
そのデータベースでトランザクションが１つ終わるたびに１ずつ増えていく。

GTID = サーバUUID:トランザクションID

GTIDは有効化するとbinlogストリームの中に含まれる。GTIDを利用することでスレーブはbinlogが自身にどこまで適用されているかレプリケーションストリームから判別できるようになり、まだ自身に適用されていないGTIDのトランザクションみのみ適用する動きに変わる。そのため、マスターの情報をCHANGE MASTER TO句で指定する際にbinlog情報について指定する必要がなくなる。

GTIDの特性

• 一貫性

GTIDは常にマスタとスレーブの間で保持する。これにより、バイナリログを調べることで、スレーブに適用されたすべてのトランザクションのソースを知ることができる。また、GTID持つトランザクションが特定のサーバーにコミットされると、同じGTIDを持つ後続のトランザクションは無視される。

従って、マスタDB上でコミットされたトランザクションは、複数回適用されることがなくなり一貫性を保証される。

• レプリケーションの実装、復旧の容易さ

GTIDが用いた場合、スレーブDBは、マスタDBのbinlogファイル名とポジションを保持する必要性がなくなる。
なぜなら、スレーブDBはGTIDを用いることで、マスタDBとの同期のために必要なすべての情報が複製データストリームから直接得られる。GTIDはレプリケーションの開始・停止時に必要なファイルオフセットを自動で取得することができるため、
CHANGE MASTER TOにて、MASTER_LOG_FILE、MASTER_LOG_POSを指定する必要が無くなる。また、MASTER_AUTO_POSITIONを使用することで、スレーブDBは指定されたマスタDBからレプリケーション行う。

###[1].レプリケーション向け設定my.cnf(マスター側)

レプリケーション向けパラメータ

#binlogパス
logbin=<binlogファイル名フルパス>
#レプリケーション範囲で一意な値
server_id=1

###GTID向け
#GTIDモード有効化
gtid-mode=ON
#スレーブがマスターサーバーから受け取った更新をそれ自身のバイナリログに書き込むようにする
log-slave-updates
#トランザクションセーフなステートメントのみのログを記録
enforce-gtid-consistency

###[4].レプリケーション向け設定my.cnf(スレーブ側)

レプリケーション向けパラメータ

#binlogパス
logbin=<binlogファイル名フルパス>
#マスターとは異なる値にする
server_id=2
#読み取り専用にする場合必要
read_only=1
#スーパーユーザも読み取り専用にする場合必要(後続のリストアでは書き込みの必要ありなのでこの時点では#)
#super_read_only=1

###GTID向け
#GTIDモード有効化
gtid-mode=ON
#スレーブがマスターサーバーから受け取った更新をそれ自身のバイナリログに書き込むようにする
log-slave-updates
#トランザクションセーフなステートメントのみのログを記録
enforce-gtid-consistency

###[6].レプリケーション開始(スレーブ側)

レプリケーション開始

#レプリケーション元のマスター情報指定。MASTER_AUTO_POSITIONをつける。
CHANGE MASTER TO
MASTER_HOST='db1',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='P#ssw0rd',
MASTER_AUTO_POSITION=1;

#レプリケーション開始
START SLAVE;

#レプリケーション状況確認
SHOW SLAVE STATUS\G


~~~以下にマスターから受け取ったGTID、既にスレーブで実行されたGTIDが判別可能~~~
Retrieved_Gtid_Set: e62890a6-5b64-11ea-b46b-42010a960003:1-26
Executed_Gtid_Set: e62890a6-5b64-11ea-b46b-42010a960003:1-26

レプリケーショントラブルシューティング

• [Pattern1] スレーブ上のテーブルを更新してしまってマスターから転送された更新が失敗（= 整合性が合わなくなってしまった）

(1)エラー情報

SHOW SLAVE STATUS\G;

Last_SQL_Errno: 1146
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '67c106e1-5cee-11ea-87a2-068eadb8df76:46' at master log mysql-bin-changelog.016666, end_log_pos 603. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

エラーログ

2020-08-01T10:51:06.434804Z 51 [ERROR] Slave SQL for channel '': Worker 1 failed executing transaction '67c106e1-5cee-11ea-87a2-068eadb8df76:46' at master log mysql-bin-changelog.016666, end_log_pos 603; Error 'Table 'repl.AAA' doesn't exist' on query. Default database: 'repl'. Query: 'insert into AAA values(999)', Error_code: 1146
2020-08-01T10:51:06.434972Z 50 [Warning] Slave SQL for channel '': ... The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details). Error_code: 1756
2020-08-01T10:51:06.434987Z 50 [Note] Error reading relay log event for channel '': slave SQL thread was killed
2020-08-01T10:51:06.435203Z 50 [Note] Slave SQL thread for channel '' exiting, replication stopped in log 'mysql-bin-changelog.016666' at position 358

この場合、Gtid '67c106e1-5cee-11ea-87a2-068eadb8df76:46'が失敗しているのでこちらのGtidをスキップする必要性がある。

(2)解消方法

• Gtid を手動で SET & 空トランザクションを実行して対象 Gtid に該当するトランザクションをスキップ

stop slave;
SET GTID_NEXT='67c106e1-5cee-11ea-87a2-068eadb8df76:46';
BEGIN;
COMMIT;

SET GTID_NEXT='AUTOMATIC';
start slave;

• gtid_purgedを使用した方法

gtid_purgedは消去されたトランザクションＩＤが格納される変数でこれを書き換えて、疑似的に Gtid ガどこまで進んだのかを認識させる。
レプリケーションはstart slave することでこれの次のＩＤからデータを更新しようとする。

stop slave;

# gtid_purgedに既に値が入っている場合は消去ができないのでまずはスレーブでreset master。

reset master;
show global variables like 'GTID%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
+----------------------------------+-------+

SET global gtid_purged='67c106e1-5cee-11ea-87a2-068eadb8df76:46';

start slave;

• [Pattern2] マスターにバイナリログを取得する際に既にバイナリログがない場合

暫くレプリケーションを止めた後に再開したときなどに、次のGtidに該当するバイナリログがマスターになくエラーになるパターン。

(1)エラー情報

SHOW SLAVE STATUS\G;

Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.

エラーログ

2020-08-01T11:57:49.874986Z 80 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.', Error_code: 1236
2020-08-01T11:57:49.874992Z 80 [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin-changelog.016679', position 1103

(2)解消方法

基本的にはパターン1の方法が利用可能。
考慮点としては、マスター側のインスタンスにてGtidがどこまで進んでいるのかを調べてから、スレーブ側でも同様の位置まで進めること。

#マスター側のGtid確認。gtid_executedを確認する。
show global variables like 'GTID%';

準同期レプリケーション

MySQLはデフォルトでは非同期レプリケーションとなるが、バージョン5.5から準同期レプリケーションを利用可能である。
準同期レプリケーションでは、マスター上でトランザクションが実行されると、COMMIT が完了する前にスレーブへのバイナリログの転送が完了するというものである。
動きにすると以下のような順序の動きになる。
このようにすることでクライアントに応答が返った際にはスレーブにて更新すべきデータがあることを保証している。（スレーブにデータが更新済みであることを保証しているわけではない。これを保証するのは俗に言う同期レプリケーション）

■ 流れ(MySQL 5.7 以前)

• 1.クライアントがマスターへCOMMITを発行
• 2.マスターの接続スレッドがストレージエンジンに対してPREPAREを実行
• 3.マスターでバイナリログが更新されてディスクにfsyncされる
• 4.マスターのストレージエンジンでCOMMIT完了
• 5.マスタースレッドがバイナリログをスレーブに送信
• 6.スレーブのリレーログに送信されたバイナリログがfsyncされる
• 7.スレーブがマスターにACKを返却してマスタースレッドが接続スレッドにACKを返却　の2つが並行で行われる。
• 8.接続スレッドがクライアントに対してOKパケットを返す。

■ 流れ(MySQL 5.7 以降)

ロスレスレプリケーション

MySQL 5.7 以前では問題が解消されている。
MySQL 5.7 以前では、上記の流れの 「4.マスターのストレージエンジンでCOMMIT完了」の後に、もしマスターがクラッシュしスレーブを昇格させた場合、クラッシュ前のマスターにしか存在しないデータが発生してしまう形になる。つまり、スレーブで一部データが失われる形になる。これを改善するために、マスターのストレージエンジンおけるCOMMIT処理が以下のように、スレーブがマスターにACKを返却した後にする形に変更されている。

• 1.クライアントがマスターへCOMMITを発行
• 2.マスターの接続スレッドがストレージエンジンに対してPREPAREを実行
• 3.マスターでバイナリログが更新されてディスクにfsyncされる
• 4.マスタースレッドがバイナリログをスレーブに送信
• 5.スレーブのリレーログに送信されたバイナリログがfsyncされる
• 6.スレーブがマスターにACKを返却してマスタースレッドが接続スレッドにACKを返却　の2つが並行で行われる。
• 7.マスターのストレージエンジンでCOMMIT完了
• 8.接続スレッドがクライアントに対してOKパケットを返す。

準同期レプリケーションの設定としてはマスター、及び、スレーブで以下の設定をしておく必要がある。

1.プラグインのインストール

#マスター
INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';

#スレーブ
INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_slave.so';

2.オプション設定

#マスター
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=10000;
set global rpl_semi_sync_master_wait_for_slave_count=2;
set global rpl_semi_sync_master_wait_no_slave=OFF;

#スレーブ
set global rpl_semi_sync_slave_enabled=1;

rpl_semi_sync_master_enabled
準同期レプリケーションの有効化・無効化

rpl_semi_sync_master_timeout
マスターがスレーブからのACKを待つタイムアウト期間(ミリ秒)を指定する。スレーブに何らかの問題があり長時間ACKを返せない状態になってしまってもこのタイムアウトを経過すると、自動的に非同期モードに移行してクライアントに対して応答を返す。ACK が次回返ってきたら準同期モードに戻る。

rpl_semi_sync_master_wait_for_slave_count
何個のスレーブからACKが返ってきたらクライアントに対して応答を返すかの設定。1:N トポロジーの場合の可用性の向上に便利。

rpl_semi_sync_master_wait_no_slave
マスターに対して接続しているスレーブ数が rpl_semi_sync_master_wait_for_slave_count より少ない場合の挙動を制御する。
ON (デフォルト) の場合は、律儀にACKが必要な数返ってくることを待機する。OFF の場合は、非同期モードに移行する。
堅牢性と速度のトレードオフ。

3.IOスレッドの再起動

stop slave io_thread;start slave io_thread;

マルチソースレプリケーション

MySQL では特定のスレーブサーバにて、マスターを複数持つことができる。
例えばマスターがそれぞれ異なるデータベースを持っており、それらを１つのサーバに統合したい場合などマルチソースレプリケーションを利用すると１つのスレーブサーバにデータ統合することが可能。

まずマルチソースレプリケーションの前提としては以下の2つのサーバパラメータをスレーブ側では TABLE にする必要がある。

• master_info_repository

スレーブがマスターステータスおよび接続情報のログを FILE (master.info) または TABLE (mysql.slave_master_info) のどちらに記録するかを決定

• relay_log_info_repository

スレーブのリレーログ内での位置が FILE (relay-log.info) または TABLE (mysql.slave_relay_log_info) のどちらに書き込まれるか決定。

次にスレーブで以下の手順にてマルチソースレプリケーションを稼働させる。
各マスターごとにチャネルと言う単位で管理して接続を行うイメージ。

マスターx2の例(gtid)

SET global gtid_purged='<マスター1 (CHANNEL 'mysql1')の gtid_executed>,<マスター2 (CHANNEL 'mysql2')の gtid_executed>';

CHANGE MASTER TO
MASTER_HOST='mysql1',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='P#ssw0rd',
MASTER_AUTO_POSITION=1 
FOR CHANNEL 'mysql1';

CHANGE MASTER TO
MASTER_HOST='mysql2',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='P#ssw0rd',
MASTER_AUTO_POSITION=1 FOR CHANNEL 'mysql2';

start slave FOR CHANNEL 'mysql1';
start slave FOR CHANNEL 'mysql2';

##5.ユーザ・権限周り

ユーザ作成・確認・削除

##ユーザ一作成
CREATE USER 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード'; 
※ホスト名はアクセス可能なホスト名指定

##ユーザ一覧確認
SELECT user, host FROM mysql.user;
SHOW COLUMNS FROM mysql.user; 

##ユーザ権限付与
GRANT [権限] ON [適用対象のデータベース].[適用対象のテーブル] TO 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード';
※ユーザがなければ作成される

#全権限付与
GRANT ALL PRIVILEGES ON *.* TO 'ユーザ名'@'ホスト名'

#ユーザ権限確認
SHOW GRANTS FOR 'ユーザ名'@'ホスト名'; 
SHOW GRANTS ;

#ユーザ権限削除
REVOKE [権限] ON [適用対象のデータベース].[適用対象のテーブル] FROM 'ユーザ名'@'ホスト名';
FLUSH PRIVILEGES;

#ユーザ削除
DROP USER 'ユーザ名'@'ホスト名'; 

##7.セキュリティ関連

セキュリティ関連

1)システム変数secure_file_privsの明示的指定
指定されたOSディレクトリ内のファイルをmysqlユーザが参照可能な場合参照できてしまう。
指定なしの場合、OS上の全てのディレクトリが対象。必要な箇所のみに限定する。
またファイルの入出力が不要ならばNULLを設定しておく

select load_file('フルパス');

2)ユーザリソース制限方法
#MySQLサーバではユーザごとに以下リソース制限が可能。Dos攻撃を防ぐ目的などで有効もあり
MAX_QUERIES_PER_HOUR      アカウントが１時間ごとに発行できるクエリ数
MAX_UPDATES_PER_HOUR      アカウントが１時間ごとに発行できる更新の数
MAX_CONNECTIONS_PER_HOUR  アカウントが１時間ごとにサーバに接続できる数
MAX_USER_CONNECTIONS      アカウントによるサーバへの同時接続の数

例)
ALTER USER 'ユーザ名'@'ホスト名' with MAX_USER_CONNECTIONS 100;
※無制限にする場合は0を指定する。

3)システム変数bind-addressで利用するNICを指定する。
my.cnfへ以下のように設定。デフォルトだと全てのNICのIPアドレスに対して、
ポートを解放してしまうので不要なものは外す。ただし複数指定は不可能なので複数指定すべきな
時はユーザに対してアクセス元を制限してセキュリティを担保する
bind-address= 10.10.2.201 or ホスト名


4)クライアント-サーバ間のSSL接続
MySQLデフォルトだと平文通信になってしまう。
通信の傍受や書き換え、成りすましを防ぐために通信を暗号化する。
サーバ認証のみを実施する前提。クライアント認証はまた別途。

(1)サーバ秘密鍵作成
openssl genrsa -aes256 2048 > server.key
openssl rsa -in server.key -out server.key 

(2)CSR作成
openssl req -utf8 -new -key server.key -out server.csr

(3)サーバ証明証作成
openssl x509 -in server.csr -out server.crt -req -signkey server.key -days 3650

(4)my.cnfにサーバ秘密鍵&サーバ証明書のパスを記載
[mysqld]
ssl-cert=/data/server.cert
ssl-key=/data/server.key

(5)SSL接続テスト
mysql -u ssluser -pP#ssw0rd -h amalnx.ddns.net  --ssl-mode=REQUIRED
mysql> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.1 |
+---------------+---------+
1 row in set (0.16 sec)

mysql> show session status like 'ssl_cipher';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| Ssl_cipher    | DHE-RSA-AES256-SHA |
+---------------+--------------------+
1 row in set (0.16 sec)

Tips：接続にSSLを強制するにはユーザ自体にSSL接続の強制化が必要
・SSLサーバ認証強制
create user user2 identified by 'Password1!' require SSL;
alter user user2 require SSL;

・SSLサーバ認証+クライアント認証強制
create user user2 identified by 'Password1!' require X509;
alter user user2 require X509;

Tips：MySQL 5.7 から導入された --ssl-mode オプション

mysql コマンドでの接続時に--ssl-modeオプションを指定することでSSL/TLSを以下のように制御可能なので便利。（各クライアントSDKにも同等のものがあるはず）

DISABLED
暗号化されていない接続を確立します。

PREFERRED
サーバが暗号化された接続をサポートしている場合は暗号化された接続を確立し、 暗号化された接続を確立できない場合は暗号化されていない接続にフォールバックします。Default値。

REQUIRED
サーバーが暗号化された接続をサポートしている場合、暗号化された接続を確立します。 暗号化された接続を確立できない場合、接続の試行は失敗します。

VERIFY_CA
REQUIREDに似ているが、構成されたCA証明書に対してサーバー認証局（CA）証明書をさらに検証します。 有効な一致するCA証明書が見つからない場合、接続の試行は失敗

VERIFY_IDENTITY
VERIFY_CAに似ているが、クライアントがサーバーへの接続に使用するホスト名を、 サーバーがクライアントに送信する証明書のIDと照合してホスト名のID検証(=証明書のCNの検証)を追加で実行

8.容量計算

###データベース容量

SELECT 
    table_schema, sum(data_length) /1024/1024 AS mb 
FROM 
    information_schema.tables  
GROUP BY 
    table_schema 
ORDER BY       
    sum(data_length+index_length) DESC;

###テーブル容量

SELECT  
    table_name, engine, table_rows AS tbl_rows,
    avg_row_length AS rlen,  # 行の長さ平均
    floor((data_length+index_length)/1024/1024) AS allmb,  #総容量
    floor((data_length)/1024/1024) AS dmb,  #データ容量
    floor((index_length)/1024/1024) AS imb   #インデックス容量
FROM 
    information_schema.tables  
WHERE
    table_schema=database()  
ORDER BY
    (data_length+index_length) DESC;  

###参考にさせていただいた記事
(テストデータ生成)
https://qiita.com/chisei/items/c4439adf3d0faedb65ed
(レプリケーション設定のため参考)
https://www.casleyconsulting.co.jp/blog/engineer/90/

Tips.
ほかのMWとの組み合わせの構成により、パスワードチェックを無効にしたい場合
例えばHAProxyと組み合わせてL4ロードバランシングする場合は、mysqlへヘルスチェックしに行くユーザの
パスワードを無効にする必要があるので一時的に下記で無効化して作成すると良い

パスワードポリシー無効化

/etc/my.cnf
[mysqld]
validate-password=OFF

RDS等ローカルにログインできないmysqlサーバのデータをローカルにcsv形式で出力したい場合は、
下記で実施する。--batchとsedコマンドが大事。

csv化方法

mysql -u [ユーザ] -h [RDSエンドポイント] -P[ポート] [接続DB] 
--batch -e 'select * from item' 
| sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > yourlocalfilename

[mysqld@s1]
socket=/var/lib/mysql/mysql_s1.sock
symbolic-links=0
log-error=/var/log/mysqld_s1.log
pid-file=/var/run/mysqld/mysqld_s1.pid
innodb_buffer_pool_size=300M
sort_buffer_size=200M
datadir=/opt/mysql_s1/data
port=3307
max_connections=100
character_set_server=utf8mb4
general_log=1
general_log_file=/var/log/mysql/sql_s1.log
log_output=FILE
slow_query_log=1
slow_query_log_file=/var/log/mysql/slowquery_s1.log
long_query_time=10
log-queries-not-using-indexes
server-id=1
log-bin=/opt/mysql_s1/bin_log/mysql-bin-log
log_bin_index=/opt/mysql_s1/bin_log/bin.list
max_binlog_size=50M
expire_logs_days=1
binlog-do-db=testdb

[mysqld@s2]
socket=/var/lib/mysql/mysql_s2.sock
symbolic-links=0
log-error=/var/log/mysqld_s2.log
pid-file=/var/run/mysqld/mysqld_s2.pid
innodb_buffer_pool_size=300M
sort_buffer_size=200M
datadir=/opt/mysql_s2/data
port=3308
max_connections=100
character_set_server=utf8mb4
general_log=1
general_log_file=/var/log/mysql/sql_s2.log
log_output=FILE
slow_query_log=1
slow_query_log_file=/var/log/mysql/slowquery_s2.log
long_query_time=10
log-queries-not-using-indexes
server-id=1
log-bin=/opt/mysql_s2/bin_log/mysql-bin-log
log_bin_index=/opt/mysql_s2/bin_log/bin.list
max_binlog_size=50M
expire_logs_days=1
binlog-do-db=testdb

日付/文字列変換例

(1) 文字列型を特定の日付型へ変換するSQL

特定の日付表記の文字列型を一般的な日付型へ変換するSQL

select str_to_date('09/Aug/2020:23:29:59 +0900',"%d/%b/%Y:%H:%i:%s") from dual;
+---------------------------------------+
| str_to_date(time,"%d/%b/%Y:%H:%i:%s") |
+---------------------------------------+
| 2020-08-09 23:29:59                   |
+---------------------------------------+
1 row in set, 1 warning (0.01 sec)

(2) 一般的な日付型を別の日付型へ変換するSQL

一般的な日付型を別の日付型へ変換するSQL

select DATE_FORMAT('2020-08-09 23:33:59 ',"%Y/%m/%d") from dual;
+------------------------------------------------+
| DATE_FORMAT('2020-08-09 23:33:59 ',"%Y/%m/%d") |
+------------------------------------------------+
| 2020/08/09                                     |
+------------------------------------------------+
1 row in set (0.01 sec)

テーブルの定義一覧が見たい時

select * from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='db01' and TABLE_NAME='product'¥G

Sharding

MySQL/MariaDB で利用可能な Engine の中でも Sharding 構成を Spider エンジンで実装することが出来る。
構成としては、SpiderエンジンでSQLを受け取って、SpiderがバックエンドのInnoDB等のデータベースにテーブル内のデータをパーティション単位で分割してくれる。
なお、MariaDB では 10.0.4 以降で Spider エンジンがバンドルされている。

Spider ノード

• バックエンドのデータノードを宛先として設定

create server backend1 foreign data wrapper mysql options 
(host 'mysql1', database 'shard', user 'spider', password 'P#ssw0rd', port 3306);

create server backend2 foreign data wrapper mysql options 
(host 'mysql2', database 'shard', user 'spider', password 'P#ssw0rd', port 3306);

• Spider エンジン形式のテーブルをSpiderノードで設定

CREATE TABLE shard.test1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
) ENGINE=Spider COMMENT='wrapper "mysql", table "test1"'
 PARTITION BY HASH (id)
(
  PARTITION p1 COMMENT = 'srv "backend1"',
  PARTITION p2 COMMENT = 'srv "backend2"'
);

データノード

• テーブル定義やインデックス定義をしておく

CREATE TABLE shard.test1 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(50)
) ENGINE=InnoDB;