はじめに

MySQL で DB を複製(dump & load)する方法、リネーム(新DBに対して旧DBのテーブルを移動)する方法について整理した。

内容としては MySQLでdumpせずに “RENAME DATABASE” を実現する - Qiita の通り。

TL;DR

  • 複製(dump & load)
    • mysqldump [options] db_name > db_name.dump
    • mysql [options] new_db_name < db_name.dump
  • リネーム(旧DBのテーブルを移動)
    • RENAME TABLE db_name.table_name TO new_db_name.table_name × 全テーブル

目次

  1. はじめに
  2. TL;DR
  3. 環境・条件
  4. 詳細
    1. 複製(dump & load)
      1. dump
      2. load
    2. リネーム(旧DBのテーブルを移動)
  5. まとめ
  6. 参考文献

環境・条件

1
2
3
4
5
6
7
8
9
10
$ sw_vers
ProductName: Mac OS X
ProductVersion: 10.15.1
BuildVersion: 19B88

$ mysqld --version
mysqld Ver 5.7.26 for osx10.14 on x86_64 (Homebrew)

$ mysqldump --version
mysqldump Ver 10.13 Distrib 5.7.26, for osx10.14 (x86_64)

詳細

複製(dump & load)

mysqldump で出力したデータを、新しい DB に流し込むだけ。

環境によってユーザー名、パスワード、ポート番号、ホストなど違うと思うので、そのあたりは適宜変更。

dump

1
$ mysqldump -u user_name -p password db_name > db_name.dump

load

あらかじめ移行先の DB を作成。

1
$ mysql -u user_name -p password -e "CREATE DATABASE new_db_name;"

dump したデータを流し込み。

1
$ mysql -u user_name -p password new_db_name < db_name.dump

リネーム(旧DBのテーブルを移動)

以下を実行すると、SQL 文が出力されるのでそれを実行する。

1
2
3
4
5
6
7
8
SELECT DISTINCT CONCAT(
'RENAME TABLE ',
t.table_schema,'.', t.table_name,
' TO ',
"new_db", '.', t.table_name,
';' )
as rename_table
FROM information_schema.tables as t WHERE t.table_schema="old_db";

実際に試してみた結果が下記、まずは SQL 文を出力するとこまで。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> show databases;
+------------+
| Database |
+------------+
| old_db |
+------------+

mysql> use old_db;
Database changed

mysql> show tables;
+------------------+
| Tables_in_old_db |
+------------------+
| books |
| users |
+------------------+

mysql> SELECT DISTINCT CONCAT(
-> 'RENAME TABLE ',
-> t.table_schema,'.', t.table_name,
-> ' TO ',
-> "new_db", '.', t.table_name,
-> ';' )
-> as rename_table
-> FROM information_schema.tables as t WHERE t.table_schema="old_db";
+--------------------------------------------+
| rename_table |
+--------------------------------------------+
| RENAME TABLE old_db.books TO new_db.books; |
| RENAME TABLE old_db.users TO new_db.users; |
+--------------------------------------------+

新しい DB を作成して、RENAME TABLE を実行。古い DB からはテーブルが無くなっている。

1
2
3
4
5
mysql> create database new_db;
mysql> RENAME TABLE old_db.books TO new_db.books;
mysql> RENAME TABLE old_db.users TO new_db.users;
mysql> show tables;
Empty set (0.00 sec)

新しい DB でテーブルとデータを確認、無事に移行できている。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> use new_db;
Database changed

mysql> show tables;
+------------------+
| Tables_in_new_db |
+------------------+
| books |
| users |
+------------------+

mysql> select * from users limit 1;
+----+--------+---------------------+---------------------+
| id | name | updated_at | created_at |
+----+--------+---------------------+---------------------+
| 1 | taro | 2019-11-24 16:56:08 | 2019-08-04 03:48:05 |
+----+--------+---------------------+---------------------+

まとめ

  • 複製(dump & load)
    • mysqldump [options] db_name > db_name.dump
    • mysql [options] new_db_name < db_name.dump
  • リネーム(旧DBのテーブルを移動)
    • RENAME TABLE db_name.table_name TO new_db_name.table_name × 全テーブル

参考文献

関連記事