【さくらのVPS】wordpressのDBを外部サーバへ移行する（DB移行編）

はじめに

前の記事で以下の対応が完了しました。

• DBサーバの初期セットアップ
• ローカル接続用のスイッチの作成
• DBサーバへのCentOS7のインストール
• ローカルNWの設定

この記事では実際にDBの移行を行います。

移行用DBサーバにmariaDBのインストール

$ sudo yum -y install mariadb-server mariadb

mariaDBの起動と自動起動の有効化

$ sudo systemctl start mariadb
$ sudo systemctl enable mariadb

mariaDBの初期設定

$ sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y
New password:  　　　　　　　　　　　⬅︎　passwordを入力（MariaDB Administrator Password）
Re-enter new password:　　　　　 　⬅︎　passwordを再入力 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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.

Remove anonymous users? [Y/n] 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.

Disallow root login remotely? [Y/n] Y
 ... Success!

By default, MariaDB 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.

Remove test database and access to it? [Y/n] 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.

Reload privilege tables now? [Y/n] Y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

mariaDBに文字コードをセットする

詳しくはコチラを参照

設定前の状態

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

文字コードをセットする

$ sudo vi /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8 ←　ココを追加する

mariaDBの再起動

$ sudo systemctl restart mariadb.service

設定後の状態

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

作業用ユーザと移行用のDBを作成する

$ mysql -u root -p

Enter password: ←　先程設定したMariaDBのadministrator passwordを入力
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 28
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE database_name;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> GRANT ALL PRIVILEGES ON database_name.* TO "database_username"@"localhost" IDENTIFIED BY "database_user_password";　
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

既存のサーバからDBのバックアップを取得する

$ mysqldump --single-transaction -u root -p databasename >backup_databasename ⬅︎databasenameとbackup_databasename書き換える
Enter password: 
$ ls
backup_databasename ⬅︎作成されたバックアップファイル

既存サーバから移行用DBサーバにバックアップファイルをSCPで転送する

って言っても手順通りやると先に公開鍵認証の設定をしている可能性があるので以下のエラーが出ます。

$ scp -P **** /home/user/backup_databasename  user@192.168.X.X:/home/user/backup_databasename ⬅︎"user"と"backup_databasename"を書き換える ****はポート番号です。22なら指定は不要
Permission denied (publickey).

想定から抜けてて「あーそっか」ってなったので、あまり良くないですが、僕はとりあえずクライアント（Mac）にある秘密鍵を既存サーバにscpするっていう荒技で対応しました。

$ scp -P **** ~/.ssh/id_rsa user@111.222.333.444:~/.ssh/id_rsa　⬅︎****はポート番号です。22なら指定は不要

改めて下記を実行してOKなはずです。

$ scp -P **** /home/user/backup_databasename  user@192.168.X.X:/home/user/backup_databasename ⬅︎"user"と"backup_databasename"を書き換える ****はポート番号です。22なら指定は不要

既存サーバに配置した秘密鍵は消しましょう。

移行用DBサーバ側でバックアップをリストア

先ほどSCPで転送したbackup_databasenameをリストアします。
database_usernameはDBを作成した時に作成した作業用のユーザです。

 $ mysql -u database_username -p database < backup_databasename

mariadbの設定を変更する

バックアップを取ったDBがlocalhostからのアクセスしか許容されていないので、ローカルの別ホストからもアクセス出来るように設定を変更します。

って思ったらなんかエラーが出た。なにこれ。

$ mysql -u root -p 
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

コチラの記事を参考にして解消。

$ sudo touch /tmp/mysql.sock
$ sudo chown username /tmp/mysql.sock ⬅︎usernameを書き換え（DBの作業用ユーザ）
$ systemctl restart mariadb.service 
==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units ===
Authentication is required to manage system services or units.
Authenticating as: username
Password: 
==== AUTHENTICATION COMPLETE ===
$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 167
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

気を取り直してバックアップをリストアしたDBへローカル内のホストならアクセスが可能になるよう設定します。

MariaDB [(none)]> GRANT ALL PRIVILEGES ON databasename.* TO "username"@"192.168.%" IDENTIFIED BY "password";　⬅︎databasename、username、passwordを書き換え（バックアップ元に合わせるといいです）
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;　⬅︎再読み込み
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user, host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| new! | 192.168.% |  ⬅︎追加された設定
| root | ::1       |
| root | localhost |
| user | localhost |
+------+-----------+
5 rows in set (0.00 sec)

MariaDB [(none)]> exit
Bye

mariaDBの再起動

$ systemctl restart mariadb.service

firewalldにmariaDBを追加する

$ sudo firewall-cmd --permanent --add-service=mysql
[sudo] username のパスワード:   ⬅︎作業用ユーザのPWを入力
success
$ sudo firewall-cmd --reload
success

既存サーバ側でWordpressの設定ファイルを更新する

wp-config.phpのMariaDBに関する情報を編集します。
※ ファイルを開いて日本語の部分は文字化けしている場合はlocaleの設定を確認して下さい。

もしwp-config.php編集権限がない場合は権限を変更してください。

$ sudo chmod 600 /var/www/html/wp-config.php

先ほど移行用DBサーバ側でDBを作成した際の情報を基に入力します。
既存のサーバと同じ情報で作成した場合は、DB_HOST以外の編集は不要です。

$ sudo vi /var/www/html/wp-config.php

<?php
/**
 * WordPress の基本設定
 *
 * このファイルは、インストール時に wp-config.php 作成ウィザードが利用します。
 * ウィザードを介さずにこのファイルを "wp-config.php" という名前でコピーして
 * 直接編集して値を入力してもかまいません。
 *
 * このファイルは、以下の設定を含みます。
 *
 * * MySQL 設定
 * * 秘密鍵
 * * データベーステーブル接頭辞
 * * ABSPATH
 *
 * @link http://wpdocs.osdn.jp/wp-config.php_%E3%81%AE%E7%B7%A8%E9%9B%86
 *
 * @package WordPress
 */

// 注意:
// Windows の "メモ帳" でこのファイルを編集しないでください !
// 問題なく使えるテキストエディタ
// (http://wpdocs.osdn.jp/%E7%94%A8%E8%AA%9E%E9%9B%86#.E3.83.86.E3.82.AD.E3.82.B9.E3.83.88.E3.82.A8.E3.83.87.E3.82.A3.E3.82.BF 参照)
// を使用し、必ず UTF-8 の BOM なし (UTF-8N) で保存してください。

// ** MySQL 設定 - この情報はホスティング先から入手してください。 ** //
/** WordPress のためのデータベース名 */
define('DB_NAME', '任意のDB名');　　　　　　　⬅︎DB名を変えた場合は変更

/** MySQL データベースのユーザー名 */
define('DB_USER', '任意のユーザ名');　　　　　⬅︎ユーザ名が違う場合は変更

/** MySQL データベースのパスワード */
define('DB_PASSWORD', '任意のパスワード');　　⬅︎パスワードを変えた場合は変更

/** MySQL のホスト名 */
define('DB_HOST', '192.168.X.X');　　　　　⬅︎localhostになっているのでDBサーバに設定したローカルIPをセット

/** データベースのテーブルを作成する際のデータベースの文字セット */
define('DB_CHARSET', 'utf8');

/** データベースの照合順序 (ほとんどの場合変更する必要はありません) */
define('DB_COLLATE', '');

 <<<略>>>

設定完了後、このファイルのパーミッションを読み取り専用に変更しておきます。

$ sudo chmod 400 /var/www/html/wp-config.php

あとはブラウザでwordpressを開いてみて問題なく表示されればOKです。

疲れた。