リモートサーバーのWordPressをローカル(自分のPC)のDockerに移管する方法を実例で解説｜コマンドライン(mysqlとmysqldump)

# BEGIN WordPress
# "BEGIN WordPress" から "END WordPress" までのディレクティブ (行) は
# 動的に生成され、WordPress フィルターによってのみ修正が可能です。
# これらのマーカー間にあるディレクティブへのいかなる変更も上書きされてしまいます。
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteRule .* - [E=HTTP_AUTHORIZATION:%{HTTP:Authorization}]
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>

# END WordPress

version: "3.7"
services:
  db:
    image: mysql:5.7
    container_name: wp_mysql
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: root # rootユーザのパスワード（任意に指定可）
      MYSQL_DATABASE: wp_db # WordPress用データベース名（任意に指定可）
      MYSQL_USER: wp_user # WordPress用接続ユーザ名（任意に指定可）
      MYSQL_PASSWORD: root # WordPress用パスワード（任意に指定可）

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'wp_db' );

/** MySQL database username */
define( 'DB_USER', 'wp_user');

/** MySQL database password */
define( 'DB_PASSWORD', 'root' );

/** MySQL hostname */
define( 'DB_HOST', 'db' );

$ mysqldump -u <ユーザー名> -h <ホスト名> -p <データベース名> --skip-column-statistics > 出力ファイル名.sql

mysqldump -u <ユーザー名> -h <ホスト名> -p <データベース名> <テーブル名1> <テーブル名2>,,,, > 出力ファイル名.sql

$ mysql -u <ユーザー名> -h <ホスト名> -p <データベース名> -N -e 'show tables like "パターンマッチング"' | xargs mysqldump -u <ユーザー名> -h <ホスト名> -p <データベース名> --skip-column-statistics > 出力ファイル名.sql

$ docker exec -i <コンテナ名> sh -c 'mysql <データベース名> -u <ユーザー名> -p<パスワード>' <ダンプファイル名>.sql

mysql -u <ユーザー名> -p<パスワード> <データベース名> < <インポートするファイル名>.sql

$ mysql -u userName -h mysq.lolipop.lan -p database -N -e 'show tables like "prograshi_%"' | xargs mysqldump -u userName -h mysq.lolipop.lan -p database --skip-column-statistics > dump_prograshi.sql
Enter password: 
Enter password: 

$ ls
dump_prograshi.sql   wp-admin              wp-content         wp-login.php      xmlrpc.php

$ docker ps
CONTAINER ID   IMAGE                          COMMAND                  CREATED          STATUS          PORTS                                     NAMES
5ba4912ef774   wordpress:latest               "docker-entrypoint.s…"   36 seconds ago   Up 34 seconds   0.0.0.0:10090->80/tcp, :::10090->80/tcp   wordpress
a08ad79e6342   phpmyadmin/phpmyadmin:latest   "/docker-entrypoint.…"   37 seconds ago   Up 34 seconds   0.0.0.0:10099->80/tcp, :::10099->80/tcp   phpmyadmin
b399b142387b   mysql:5.7                      "docker-entrypoint.s…"   39 seconds ago   Up 37 seconds   3306/tcp, 33060/tcp                       wp_mysql

$ docker exec -i wp_mysql sh -c 'mysql -u wp_user -proot wp_db' < dump_prograshi.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

  wordpress:
    image: wordpress:latest
    container_name: wordpress
    depends_on:
     - db
    ports:
     - "10090:80"

$ docker exec -it  <MySQLのコンテナ名> bash

# mysql -u <ユーザー名> -p<パスワード>

$ docker exec -it  wp_mysql bash
root@b399b142387b:/# mysql -u wp_user -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

show databases;

use <データベース名>

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| wp_db              |
+--------------------+
2 rows in set (0.00 sec)

mysql> use wp_db
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

show tables;

mysql> show tables;
+-----------------------------------+
| Tables_in_wp_db                   |
+-----------------------------------+
| prograshi_actionscheduler_actions |
| prograshi_actionscheduler_claims  |
| prograshi_actionscheduler_groups  |
| prograshi_actionscheduler_logs    |
| prograshi_aioseo_cache            |
| prograshi_aioseo_notifications    |
| prograshi_aioseo_posts            |
| prograshi_bwt_failed_submissions  |
| prograshi_bwt_passed_submissions  |
| prograshi_commentmeta             |
| prograshi_comments                |
| prograshi_ewwwio_images           |
| prograshi_ewwwio_queue            |
| prograshi_links                   |
| prograshi_options                 |
| prograshi_postmeta                |
| prograshi_posts                   |
| prograshi_siteguard_history       |
| prograshi_siteguard_login         |
| prograshi_term_relationships      |
| prograshi_term_taxonomy           |
| prograshi_termmeta                |
| prograshi_terms                   |
| prograshi_usermeta                |
| prograshi_users                   |
| prograshi_wpforms_tasks_meta      |
| prograshi_wpmailsmtp_debug_events |
| prograshi_wpmailsmtp_tasks_meta   |
+-----------------------------------+
28 rows in set (0.00 sec)

show columns from xxx_options;

select option_value from xxx_options where option_value like 'http%';

+----------------------------+
| option_value               |
+----------------------------+
| https://本番環境のドメイン   |
| https://本番環境のドメイン   |
| http://rpc.pingomatic.com/ |
| https://本番環境のドメイン   |
+----------------------------+

mysql> show columns from prograshi_options;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| option_id    | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| option_name  | varchar(191)        | NO   | UNI |         |                |
| option_value | longtext            | NO   |     | NULL    |                |
| autoload     | varchar(20)         | NO   | MUL | yes     |                |
+--------------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


mysql> select option_value from prograshi_options where option_value like 'http%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    21
Current database: wp_db

+----------------------------------+
| option_value                     |
+----------------------------------+
| https://https://prograshi.com    |
| https://https://prograshi.com    |
| http://rpc.pingomatic.com/       |
| https://https://prograshi.com    |
+----------------------------------+
4 rows in set (0.03 sec)

mysql>

update `xxx_options` set option_value=replace (option_value,'http://本番環境のドメイン','http://localhost:ポート番号'); 
update `xxx_options` set option_value=replace (option_value,'https://本番環境のドメイン','http://localhost:ポート番号');

mysql> update `prograshi_options` set option_value=replace (option_value,'https://prograshi.com','http://localhost:10092'); 
Query OK, 0 rows affected (0.00 sec)
Rows matched: 364  Changed: 0  Warnings: 0

mysql> update `prograshi_options` set option_value=replace (option_value,'https://prograshi.com','http://localhost:10092');
Query OK, 6 rows affected (0.07 sec)
Rows matched: 364  Changed: 6  Warnings: 0

update `xxx_posts` set guid=replace (guid,'http://本番環境のドメイン','http://localhost:ポート番号'); 
update `xxx_posts` set guid=replace (guid,'https://本番環境のドメイン','http://localhost:ポート番号');
update `xxx_posts` set post_content=replace (post_content,'http://本番環境のドメイン','http://localhost:ポート番号');  
update `xxx_posts` set post_content=replace (post_content,'https://本番環境のドメイン','http://localhost:ポート番号');

mysql> update `prograshi_posts` set guid=replace (guid,'https://prograshi.com','http://localhost:10092'); 
date `prograshi_posts` set guid=replace (guid,'https://prograshi.com','http://localhost:10092');
updaQuery OK, 145 rows affected (0.03 sec)
Rows matched: 372  Changed: 145  Warnings: 0

te `prograshi_posts` set post_content=replace (post_cmysql> update `prograshi_posts` set guid=replace (guid,'https://prograshi.com','http://localhost:10092');
ontent,'https://prograshi.com','http://localhost:10092');  
update `prograshi_posts` set post_content=Query OK, 227 rows affected (0.02 sec)
Rows matched: 372  Changed: 227  Warnings: 0

mysql> update `prograshi_posts` set post_content=replace (post_content,'https://prograshi.com','http://localhost:10092');  
replace (post_content,'https://prograshi.com','http://localhost:10092');Query OK, 94 rows affected (0.03 sec)
Rows matched: 372  Changed: 94  Warnings: 0

mysql> update `prograshi_posts` set post_content=replace (post_content,'https://prograshi.com','http://localhost:10092');
Query OK, 83 rows affected (0.04 sec)
Rows matched: 372  Changed: 83  Warnings: 0

update `xxx_postmeta` set meta_value=replace (meta_value,'http://本番環境のドメイン','http://localhost:ポート番号'); 
update `xxx_postmeta` set meta_value=replace (meta_value,'https://本番環境のドメイン','http://localhost:ポート番号');

mysql> update `prograshi_postmeta` set meta_value=replace (meta_value,'https://prograshi.com','http://localhost:10092'); 
ue=replace (meta_value,'https://prograshi.com','Query OK, 0 rows affected (0.02 sec)
Rows matched: 597  Changed: 0  Warnings: 0

mysql> update `prograshi_postmeta` set meta_value=replace (meta_value,'https://prograshi.com','http://localhost:10092');
Query OK, 1 row affected (0.01 sec)
Rows matched: 597  Changed: 1  Warnings: 0

  wordpress:
    image: wordpress:latest
    container_name: wordpress
    depends_on:
     - db
    ports:
     - "10090:80"