サーバー移転:DBサーバー編

サーバー移転

サーバー移転

サーバー移転シリーズ、DBサーバー編です。
特にはまったわけではないけど、忘れがちなことがあるのでまとめておきます。

リカバリデータは35gbほどあり、dumpファイルはgzipで圧縮しても7gbほどあるため、巨大なdumpデータをリカバリ作業に一工夫しています。

サーバー移転シリーズリンク

環境

  • 移転前:MySQL 5.5
  • 移転後:MySQL 5.7

作業の流れ

  1. 移転元でリカバリデータを転送
  2. MySQLのインストール
  3. MySQLインストール直後の作業
  4. DBの作成
  5. DBユーザの作成
  6. 移転元からのリカバリ

せっかちさんのための作業まとめ

移転元作業

# リカバリデータの転送
mysqldump --single-transaction --no-data target_db | gzip |ssh [target_host_ip] 'cat >~/target_db.ddl.sql.gz'
mysqldump --single-transaction --no-create-info target_db | gzip |ssh [target_host_ip] 'cat >~/target_db.data.sql.gz'

移転先作業

# MySQL 公式リポジトリのインストール
rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

# MySQLのインストール
yum install mysql-community-server mysql-community-devel

# rootの初期パスワードの確認
cat /var/log/mysqld.log | grep password

# MySQLの起動&起動設定
systemctl start mysqld.service
systemctl enable mysqld.service

# セキュリティ初期設定(詳細は後述)
mysql_secure_installation

# timezone の設定
/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

# my.confのリカバリ
scp -i ~/.ssh/rsa.pem [source_host_ip]:/etc/my.cnf /etc/my.cnf

# MySQLの再起動
systemctl restart mysqld.service

# データベースの作成
mysql > CREATE DATABASE target_db;

# ユーザの作成
mysql > CREATE USER 
appuser@localhost IDENTIFIED BY 'password',
appuser@web01 IDENTIFIED BY 'password';

# ユーザの権限設定
mysql > GRANT all ON target_db.* to appuser@localhost IDENTIFIED BY 'password';
mysql > GRANT all ON target_db.* to appuser@web01 IDENTIFIED BY 'password';

# リカバリ
zcat ~/target_db.ddl.sql.gz |mysql -p target_db
zcat ~/target_db.data.sql.gz |mysql -p target_db

# バックグランドでリカバリするための作業
# Ctrl+zで一時停止
bg 1
jobs 1
disown %1

作業の詳細

1. 移転元でリカバリデータを転送

リカバリ対象のデータが35GBを超えているため、そのままdumpファイルを作成するととんでもないことになるため、移転元から移転先にgzipで圧縮しながらsshで直接保存しています。

# リカバリデータの転送 
## DDLのリカバリデータ
mysqldump --single-transaction --no-data target_db | gzip |ssh [target_host_ip] 'cat >~/target_db.ddl.sql.gz' 

## DMLのリカバリデータ
mysqldump --single-transaction --no-create-info target_db | gzip |ssh [target_host_ip] 'cat >~/target_db.data.sql.gz'

2. MySQLのインストール

CentOS 7系では公式リポジトリのMySQLがMariaDBに置き換えられているみたで、そのままではyumインストールができませんでした。

# MySQL 公式リポジトリのインストール
rpm -ivh http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

# MySQLのインストール
yum install mysql-community-server mysql-community-devel

MySQL5.7系からインストール直後はrootパスワードが自動で生成されるようです。
生成されたパスワードはmysqld.logに記録されています。

# rootの初期パスワードの確認
cat /var/log/mysqld.log | grep password

CentOS7系のため、起動設定もsystemctlを使っています。

systemctl start mysqld.service
systemctl enable mysqld.service

3. MySQLインストール直後の作業

まずはmysql_secure_installationを利用して最低限のセキュリティ設定をします。

Securing the MySQL server deployment.

Enter password for user root: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100 
### rootのパスワードを変更するか聞かれています。
### yを入力して変更します。
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y  

### 新しいパスワードの入力
New password: 

### 新しいパスワードの確認
Re-enter new password: 

### rootパスワードの強度を表示しています
Estimated strength of the password: 100 
### 強度を確認したうえでrootパスワードの変更確認
### yを変更を実行します
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
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を入力して削除します。
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

### local以外のrootログインを制限するか聞かれています。
### yを入力して制限します。
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

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.


### testデータベースを削除するか聞かれています。
### yを入力して削除します。
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! 

default-time-zone を ‘Asia/Tokyo’ で設定するためにtimezoneの初期設定をしています。

# timezone の設定
/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

移転元からmy.cnfをコピーしてサーバーを再起動

# my.cnfのリカバリ
scp -i ~/.ssh/rsa.pem [source_host_ip]:/etc/my.cnf /etc/my.cnf

# MySQLの再起動
systemctl restart mysqld.service

4. DBの作成

特に特筆することなし。

# データベースの作成
mysql > CREATE DATABASE target_db;

5. DBユーザの作成&権限設定

特に特筆することないけど、構文をいつも忘れる。

# ユーザの作成
mysql > CREATE USER 
appuser@localhost IDENTIFIED BY 'password',
appuser@web01 IDENTIFIED BY 'password';

# ユーザの権限設定
mysql > GRANT all ON target_db.* to appuser@localhost IDENTIFIED BY 'password';
mysql > GRANT all ON target_db.* to appuser@web01 IDENTIFIED BY 'password';

6. 移転元からのリカバリ

まずはDDLの実行。
これはすぐ終わるのでそのまま実行。

# DDLのリカバリ
zcat ~/target_db.ddl.sql.gz |mysql -p target_db

続いてDMLの実行。
この作業は数時間かかるのでバックグラウンドで作業。

# DMLのリカバリ
zcat ~/target_db.ddl.sql.gz |mysql -p target_db

# バックグランドでリカバリするための作業
# この作業をすることでログアウトしてもバックグラウンドで作業してくれる
# Ctrl+zで一時停止
bg 1
jobs 1
disown %