MySQLのインストールと初期設定のメモです。以下の書籍を参考にしました。

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

環境

今回使ったOSは、Ubuntu 16.04 LTSです。

ダウンロードとインストール

MySQL Downloadsページから、Community Editionのダウンロードページを開きます。
ここでプラットフォームに合わせてパッケージをダウンロードするのですが、今回はUbuntuなのでMySQL APT Repositoryをダウンロードします。

“ダウンロード” ボタンを押すと、ログインとサインアップのページが表示されます。アカウントの無い方は下の方にある”No thanks, just start my download” をクリックしましょう。

このパッケージのダウンロードURLは後で使うので、どこかにコピーしておいてください。

Ubuntuで以下のコマンドを実行して、ダウンロードとインストールを行います。

# MySQLをダウンロード
cd ~
mkdir mysql
cd mysql

# 上記でコピーしておいたダウンロードURLより、パッケージをダウンロード
wget https://dev.mysql.com/get/mysql-apt-config_0.8.9-1_all.deb

sudo dpkg -i mysql-apt-config_0.8.9-1_all.deb
sudo apt update

# MySQLをインストール
sudo apt install mysql-server -y

インストールが完了したら、mysql_secure_installation コマンドを実行します。
このコマンドは、MySQLのセキュリティを高めるために以下の設定を行います。
各設定を有効にするかと質問されますので、全てに “y” (Yes)と答えましょう。

• 複雑なパスワードを強制するプラグインをインストール
• rootのパスワードの変更
• anonymousユーザー (名無しユーザー)の無効化
• root のリモートアクセス無効化
• testデータベースの削除

mysql_secure_installation

Securing the MySQL server deployment.

# インストール時に指定した root のパスワードを入力
Enter password for user root:

# 複雑なパスワードを強制する VALIDATE PASSWORD PLUGIN をインストール
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

# "y" (Yes) を入力
Press y|Y for Yes, any other key for No: y

# パスワードの強度の選択
There are three levels of password validation policy:
LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

# "2" (STRONG) を入力
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

# 現在の root のパスワードの強度が表示されます。必要に応じて変更します。
Using existing password for root.

# 現在のパスワード強度: 50
Estimated strength of the password: 50

# "y" を入力してroot パスワードを変更
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
New password:
Re-enter new password:

# 新しいパスワードの強度: 100
Estimated strength of the password: 100

# このパスワードで確定する場合は "y"
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

# anonymousユーザー (名無しのユーザー) の削除
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

# "y" を入力してanonymouユーザーを削除
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

# root のリモートアクセス無効化
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

# "y" を入力して root のリモートアクセスを無効化
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

# "test" データベースの削除
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

# "y" を入力して "test" データベースを削除
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.
 - Removing privileges on test database...
Success.

# 権限テーブルのリロードして設定を反映
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

# "y"  を入力して権限テーブルをリロード
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

MySQLの設定

インストールが終わったら、MySQLの設定をしましょう。

※注意: 以下コマンドは全て root ユーザーで実行しています。

sudo su # root に変更

cd /etc/mysql/mysql.conf.d
nano mysqld.cnf

以下は、メモリ8GB程度の小規模サーバー用の設定例です。

/etc/mysql/mysql.conf.d/mysqld.cnf

max_connections = 1024      # マシンスペックが低い場合は 512

# 絵文字フォントに対応する場合は "utf8mb4"、対応不要なら"utf8" と設定します。
character_set_server = utf8mb4

table_open_cache = 4000
table_definition_cache = 400
key_buffer_size = 16M
read_buffer_size = 256K
read_rnd_buffer_size = 512K
join_buffer_size = 256K
sync_binlog = 1

# バイナリログの出力パス (ファイル名を含む) を指定します。
log_bin = /var/lib/mysql/mysql-bin/mysql-bin

binlog_format = ROW
max_binlog_size = 128M
binlog_row_image = FULL

# バイナリログのローテーション間隔。フルバックアップと合わせて15日としています。
expire_logs_days = 15

server_id = 1
slow_query_log
long_query_time = 3
log_queries_not_using_indexes
open_files_limit = 5500
innodb_buffer_pool_size = 50
innodb_file_per_table
innodb_autoextend_increment = 64M
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 16
innodb_thread_concurrency = 10

設定ファイルを更新したら、MySQLを再起動します。

service mysql restart

バックアップ設定

次に、バックアップ用のフォルダを作ります。

MySQLにはダンプと呼ばれるフルバックアップと、バイナリログと呼ばれる差分バックアップがあり、ここではそれぞれのバックアップファイルを保存するフォルダを作ります。

cd /var/lib/mysql
# バイナリログ用のバックアップフォルダ
mkdir mysql-bin
chown mysql:mysql mysql-bin
chmod 700 mysql-bin

# ダンプ用のバックアップフォルダ
mkdir mysql-dump
chown mysql:mysql mysql-dump
chmod 700 mysql-dump

続いて、バックアップ用スクリプトを作成します。
フルバックアップは mysqldump というコマンドを実行して取得しますので、コマンドを実行するスクリプトを作成し、 cron 定期的にでそのスクリプトを実行するよう設定します。

ちなみに、差分バックアップ(バイナリログ)は、設定ファイルで設定すれば自動で取得されますので、特に操作は必要ありません。

mysqldump コマンドは (MySQLの) root 権限で実行しますが、実行する際にパスワードを求められます。
パスワードをスクリプトに平文で書いてしまうと、セキュリティ上の問題があるため、mysqldump.cnf というmysqldumpコマンド用の設定ファイルを作成し、そこにパスワードを記述します。

このファイルの所有者をroot にし、パーミッションに 600 にすることで、root 以外のユーザーが読み取りできないよう保護します。

cd /etc/mysql

# mysqldump.cnf ファイルを作成
touch mysqldump.cnf
chown root:root mysqldump.cnf
chmod 600 mysqldump.cnf

# mysqldump.cnf をエディタで開く
nano mysqldump.cnf

mysqldump.cnf の中身は以下のように記述します。

/etc/mysql/mysqldump.cnf

[mysqldump]
user=root
password=rootのパスワード

続いてバックアップ用スクリプトを作成します。
スクリプトのファイル名は backup-mysql.sh としています。

cd /opt
mkdir mysql-utils
chown root:root mysql-utils
chmod 700 mysql-utils

cd mysql-utils
touch backup-mysql.sh
chown root:root backup-mysql.sh
chmod 700 backup-mysql.sh

nano backup-mysql.sh

backup-mysql.sh の中身は以下のように記述します。

#!/bin/sh

# ファイルのローテーション間隔。
# ここでは、ファイル作成日から15日以上経ったバックアップは削除しています。
rotation_period=15

# バックアップファイルの保存先フォルダ。
# 上記で作成したバックアップ用フォルダを指定します。
backup_dir='/var/lib/mysql/mysql-dump'

# ダンプファイルの名前
file_name=`date +%Y%m%d%H%M%S`.dump.sql

# mysqldump コマンド実行
mysqldump --defaults-file=/etc/mysql/mysqldump.cnf --single-transaction --flush-logs --master-data=2 --all-databases > $backup_dir/$file_name
chmod 700 $backup_dir/$file_name

# 15日以上経ったバックアップを削除
find $backup_dir/*.dump.sql -maxdepth 1 -type f -mtime +$rotation_period -delete

バックアップ用スクリプトが正常に動くか確認しましょう。
以下のコマンドを実行し、作成されたバックアップファイルが15日後に削除されるのを確認します。

# バックアップスクリプトを実行
/opt/mysql-utils/backup-mysql.sh

# 作成されたバックアップファイルを確認
ls -al /var/lib/mysql/mysql-dump/

# バックアップファイルの更新日時を15日以上前の日付に変更 (例: "2018/04/01")
touch -m -d "2018/04/01" /var/lib/mysql/mysql-dump/20180416101010.dump.sql # 作成されたバックアップファイルを指定

# 再度バックアップスクリプトを実行
/opt/mysql-utils/backup-mysql.sh

# 新しいバックアップファイルが作成され、最初に作成されたバックアップファイルが削除されているのを確認
ls -al /var/lib/mysql/mysql-dump/ # 作成されたバックアップファイルを確認

問題なければ、cronジョブを登録し、バックアップ用スクリプトを定期的に実行するよう設定します。

ここでは毎週日曜日の 0:00 に実行していて、バックアップファイルは15日ごとにローテーションするため、常に2世代前までのフルバックアップを保持していることになります。

crontab -e
-----
### MySQL weekly backup job
0 0 * * 0 /opt/mysql-utils/backup-mysql.sh
-----

バックアップからのリストアを確認

最後に、バックアップからのリストアを確認します。
リストアは、

1. 直近のフルバックアップを復元
2. フルバックアップに書かれたバイナリログIDのファイルから、バイナリログを復元

という順序で行います。

まずはテストデータを登録しましょう。

mysql -u root -p

mysql> create database test;
mysql> use test;
mysql> create table table1(id int, name varchar(255));
mysql> insert into table1 values (1,'a'), (2, 'b'), (3, 'c');
mysql> select * from table1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> exit;

現在のバックアップファイルを確認

ls -al /var/lib/mysql/mysql-dump/ # ダンプファイルを確認
ls -al /var/lib/mysql/mysql-bin/ # バイナリログを確認

ここで一度フルバックアップを取っておきます。
フルバックアップ取得後、バイナリログファイルが1つ増えているのを確認します。

/opt/mysql-utils/backup-mysql.sh

ls -al /var/lib/mysql/mysql-dump/ # ダンプファイルが作成されているのを確認
ls -al /var/lib/mysql/mysql-bin/ # バイナリログが1つ増えているのを確認

フルバックアップ取得後、さらにテストデータを追加します。
このデータはフルバックアップには含まれず、バイナリログに含まれることになります。

mysql -u root -p

mysql> use test;
mysql> insert into table1 values (4,'d'), (5, 'e'), (6, 'f');
mysql> select * from table1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

mysql> exit;

これでテストデータの準備が出来ました。
これからデータを削除して、リストアしますが、そのままだとデータの削除操作もバイナリログに記録されてしまうため、ここでバイナリログを退避しておきます。

cd /var/lib/mysql/
cp -a mysql-bin mysql-bin-tmp

データベースを削除します。

mysql -u root -p

mysql> drop database test;
mysql> exit;

それではリストアです。まず復元するデータベースを作成します。

mysql -u root -p

mysql> create database test;
mysql> exit;

次にダンプファイル (フルバックアップ)をリストアします。

# ダンプのリストア
mysql -u root -p < /var/lib/mysql/mysql-dump/最新のダンプファイル名

# 結果の確認

mysql -u root -p
mysql> use test;
mysql> select * from table1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> exit;

次にバイナリログ (差分バックアップ) をリストアします。
どのバイナリログからリストアすれば良いかは、ダンプファイルに記述されています。

less /var/lib/mysql/mysql-dump/最新のダンプファイル名

# 以下の箇所を探します
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=154;

この例では、「MASTER_LOG_FILE=’mysql-bin.000005’」と記述されていますので、フルバックアップには “mysql-bin.000004” までのデータが含まれていることになります。
そのため、ここでは “mysql-bin.000005” 以降のバイナリログをリストアすれば、元のデータを全て復元することができます。

バイナリログは、ログを一度SQLに変換し、そのSQLを実行することでリストアすることが出来ます。

# 退避しておいたバイナリログへ移動
cd /var/lib/mysql/mysql-bin-tmp

# SQL出力先の一時フォルダを作成
mkdir tmp

# バイナリログをSQLに変換
mysqlbinlog -D -u root -p mysql-bin.000005 > tmp/recovery000005.sql

# SQLを実行。
mysql -u root -p < tmp/recovery000005.sql

# mysql-bin.000005 以降のファイル全てに対して上記を繰り返します。

# リストアされているのを確認
mysql -u root -p
mysql> select * from test.table1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

mysql> exit;

これでデータベースを削除する前までのデータが全て復元されました。
テスト用データベースやバックアップファイルを削除し、終了です。

mysql -u root -p

# テスト用データベース削除
mysql> drop database test;

# バイナリログをリセット
mysql> reset master;
mysql> exit;

# ダンプファイルと退避しておいたバイナリログを削除
cd /var/lib/mysql/mysql-dump
rm *

cd /var/lib/mysql
rm -r mysql-bin-recovery/

これでMySQLの初期セットアップは全て完了です。