カテゴリー
Linux

MySQL のデータベースを別サーバへコピーする方法メモ♪

データベースを同じサーバの MySQL へコピーするコマンド

# データベースをコピー。db1（user1、password1） から db2（user2、password2）に
mysqldump -u user1 -ppassword1 -n db1 | mysql -u user2 -ppassword2 db2

データベースを別のサーバの MySQL へコピーするコマンド

# 別サーバにデータベースをコピー。db1（user1、password1） から hostname サーバ（ユーザ名 hostuser）の db2（user2、password2）に
mysqldump -u user1 -ppassword1 -n db1 | ssh hostuser@hostname mysql -u user2 -ppassword2 db2

コピー元、コピー先など環境を整理！

ひとつのサーバ（192.168.56.111）の MySQL

  • ユーザ：cakeuser、パスワード：cakepass、データベース：cakedb
  • ユーザ：cakeuser2、パスワード：cakepass2、データベース：cakedb2

別のサーバ（192.168.56.113）の MySQL

  • ユーザ：fueluser、パスワード：fuelpass、データベース：fueldb

小手調べ！まずはひとつの MySQL 内でデータベースをコピーします。

エクスポートで使用します mysqldump コマンドの次に、パイプ「|」を置いて、インポートコマンド mysql でつなげるのです。 そうすれば MySQL データベースをコピーすることができます。

なお、このコマンドは、mysql プロンプトではなく、シェルで行いますことに注意です！

# root ユーザを使えば自由自在
mysqldump -u root -pnaisyo123 cakedb | mysql -u root -pnaisyo123 cakedb2

# DB に設定したユーザを使用してセキュリティに気を使うのが普通
mysqldump -u cakeuser -pcakepass cakedb | mysql -u cakeuser2 -pcakepass2 cakedb2

ここから本番！別のサーバの MySQL にデータベースをコピーします！

考え方は同じサーバ内でのコピーと同様です。mysqldump でエクスポート、パイプでつないで、mysql でインポート。

異なりますのは mysql でインポートする際に、先頭に ssh コマンドを付けて別のサーバに接続する点です。

ssh コマンドは、「ssh ユーザ名@ホスト名 コマンド」で指定します。

mysqldump -u cakeuser -pcakepass cakedb | ssh root@192.168.56.113 mysql -u fueluser -pfuelpass fueldb

実際のコマンドを打ってみました。コピー先のパスワードの入力を求められましたけれども、そこ以外は随分とアッサリですの♪

[root@localhost ~]# mysqldump -u cakeuser -pcakepass cakedb | ssh root@192.168.56.113 mysql -u fueluser -pfuelpass fueldb
root@192.168.56.113's password: 
[root@localhost ~]#

思った以上に簡単に別サーバへコピーすることができました。満足です♪

コマンドの中にパスワードを記入できるため、セキュリティはともかく、シェルには書きやすいですね♪パスワードの入力をどうすればよいか、ここがポイントになりそうです♪

下準備。ユーザ、そのパスワード、データベース、を作成

今回確かめるにあたって、MySQL にデータベースを作ったり、削除する時のコマンドです。これがあれば、気になった時にいつでもすぐに始められます♪

テストを重ねる中で、ユーザやデータベースを削除することもあったので、そのコマンドもしっかり載せておきます。

# データベース、ユーザ、の作成。パスワードの設定
GRANT ALL PRIVILEGES ON cakedb.* TO cakeuser@localhost IDENTIFIED BY 'cakepass';
FLUSH PRIVILEGES;
CREATE DATABASE cakedb CHARACTER SET utf8;

# 権限、ユーザ、データベースの削除
REVOKE ALL PRIVILEGES, GRANT OPTION FROM cakeuser@localhost;
DROP USER cakeuser@localhost;
FLUSH PRIVILEGES;
DROP DATABASE cakedb;

コピー元の MySQL データベースに予め入れておきますデータも作っておきます。これでコピー後にしっかりと確認ができます♪

CREATE TABLE `groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `groups` VALUES (1,'administrators','2013-02-26 23:55:31','2013-02-26 23:55:31'),(2,'managers','2013-02-26 23:55:42','2013-02-26 23:55:42'),(3,'users','2013-02-26 23:56:03','2013-02-26 23:56:03');

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `body` text,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `posts` VALUES (1,1,'test Title','test Body','2013-02-27 00:57:14','2013-02-27 00:57:14');

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` char(40) NOT NULL,
  `group_id` int(11) NOT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `users` VALUES (1,'administrator1','38317e4d85cd3725363ed7d85415a7ace1ce21e4',1,'2013-02-26 23:57:19','2013-02-26 23:57:19'),(2,'manager1','7581f39167455172601a28d8e0d9b5b63774c7db',2,'2013-02-26 23:57:46','2013-02-26 23:57:46'),(3,'user1','0f40cb65bdaf3c9ec78a57e29b0ba3a1b11c18d5',3,'2013-02-26 23:58:07','2013-02-26 23:58:07');

おわりに

今回は、

に関連した投稿となります。PostgreSQL はコマンドにパスワードをどうやって指定するのか、そもそももともとできないのか、よくわかりませんのでまずは一日の長のある MySQL の方を確認いたしました。

その過程で、ssh コマンドを学ぶこともできました。同じ考え方で PostgreSQL に取り組めばよいと存じますので、今回の投稿での勉強によってぐぐっと楽になっていそうですの。

楽しみですの。

なお、ssh コマンドの実践にあたり、次のページが大変参考になりました。ありがとう存じます。

以上です。

コメントを残す