プログラマ 福重 伸太朗 ～基本へ帰ろう～

http://dev.mysql.com/doc/refman/4.1/ja/mysqldump.html

shell> mysqldump [OPTIONS] database [tables]
か mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
か mysqldump [OPTIONS] --all-databases [OPTIONS]

今までは、postgresqlを利用していたので、
実は、mysqlのバックアップは始めて。

とりあえず、postgresにあるような
dumpがあるかどうか「mysql dump」でググって見る。

mysqldump

というコマンドがあるらしいことがわった。

「mysqldump」でググって見る。

http://dev.mysql.com/doc/refman/4.1/ja/mysqldump.html
↑発見！【4.9.7. mysqldump（テーブル構造とデータのダンプ）】

とりあえず読む。

＞同じサーバ上でバックアップを行う場合には、mysqlhotcopy の方の使用を考慮してください。
と書いてある。

「mysqlhotcopy」
http://dev.mysql.com/doc/refman/4.1/ja/mysqlhotcopy.html

[root@internal ~]# mysqlhotcopy --help;
/usr/bin/mysqlhotcopy Ver 1.22
Usage: /usr/bin/mysqlhotcopy db_name[./table_regex/] [new_db_name | directory]

↑確かめてみたら、利用できる・・・。
今回は、同じサーバー内にコピーするよていだから、
コチラを利用しよう。

＞mysqlhotcopy は、LOCK TABLES、FLUSH TABLES、および cp（または scp）を使用して、すばやくデータベースのバックアップを行う Perl スクリプトです。

ほう。

＞これは、データベースや単一のテーブルのバックアップを行う最速の方法ですが、データベースディレクトリのある同一マシンだけでしか実行できません。

なるほどねー。

＞mysqlhotcopy は、Unix のみ、および MyISAM テーブルと ISAM テーブルでのみ使用できます。

MyISAMとISAMのみ！！！！・・・だめぢゃん！！！ innoDB利用してるし！！

--------------------------------------------------------------------------
mysql> show table status;
+-------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------------------+
| auth_responses | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 0 | 1 | 2007-05-02 21:03:06 | NULL | NULL | utf8_general_ci | NULL | NULL | InnoDB free: 11264 kB |
・
・
・
--------------------------------------------------------------------------

と言うことで、「mysqldump」でやる。

＞テーブルを指定しなかったり、--databases オプションまたは --all-databases オプションを使用すると、データベース全体がダンプされます。

と書いてある。
なるほど。

＞使用しているバージョンの mysqldump がサポートするオプションの一覧を照会するには、mysqldump --help を実行します。

なるほど。やってみよう。

---------------------------------------
[root@internal ~]# mysqldump --help
mysqldump Ver 10.10 Distrib 5.0.22, for redhat-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
　・
　・
　・
----------------------------------------

めちゃめちゃいっぱいでた・・・。

＞注意: mysqldump を --quick または --opt なしで実行すると、mysqldump は結果をダンプする前に、結果セット全体をメモリにロードします。これは、大きなデータベースをダンプする際、問題になる可能性があります。

なるほど。ぢゃあ --quick か --opt を付けたほうがいいんだな。

＞注意: mysqldump プログラムの新しいコピーを使用している場合で、非常に古い MySQL サーバに読み込むダンプを行うときには、--opt オプションまたは -e オプションは使用しないでください。

なるほど、まぁ、非常に古いMySQLを使う予定はないので今は大丈夫。

次に、mysqldumpのオプションを見て、必要なものをピックアップしよう。

＞ mysqldump は、以下のオプションをサポートします。

＞--add-locks
＞各テーブルダンプの前に LOCK TABLES を追加し、後に UNLOCK TABLE を追加する（MySQL への挿入を速くするため）。

なるほど、早くなるなら付けた方がよさそうだな。

＞--add-drop-table
＞各作成ステートメントの前に drop table を追加する。

これもつけよう。バックアップを戻すときは、同じ状態でほしいから。

＞ -A, --all-databases
＞すべてのデータベースをダンプする。これは、すべてのデータベースを選択した状態で --databases を実行するのと同じである

なるほど。

＞-P, --port=...
＞TCP/IP 接続に使用するポート番号。
＞--protocol=(TCP | SOCKET | PIPE | MEMORY)
＞使用する接続プロトコルを指定する。MySQL 4.1 で導入。

あー、これを使って、外のサーバーに保存すればいいのか？？

＞-pyour_pass, --password[=your_pass]
＞サーバ接続時に使用するパスワード。'=your_pass' 部分を指定しなければ、mysqldump によってパスワードのプロンプトが表示される。

なるほどー。

＞一般的に、mysqldump はデータベース全体のバックアップに使用されます。

なるほど。

＞http://dev.mysql.com/doc/refman/4.1/ja/backup.html
↑【4.5.1. データベースのバックアップ】こちらも参考に。

＞shell> mysqldump --opt database > backup-file.sql
＞これを、以下のコマンドで MySQL に戻すことができます。
＞shell> mysql database < backup-file.sql

なるほど。

ここまでの知識で、同じサーバー内にDBをバックアップするなら。

shell> mysqldump --opt --add-drop-table --add-locks database_name > backup-file.sql

↑こんな感じか。

ファイルを用意してやってみる。

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

ぬおおおおお。error。パスワードが必要らしい・・。

shell> mysqldump --opt --add-drop-table --add-locks database_name > backup-file.sql -p
Enter password:

ファイルを見たら、ファイル容量増えてる！成功！！

・・・でも、これをcronで回して、定期的にやろうとしているのだけど、
パスワードを設定しないと出来ないのは、こまらないか・・・？

あ、わかった。

--password[=your_pass]

というオプションをつければいいんだ。

mysqldump --opt --add-drop-table --add-locks --password=hoge database_name > backup-file.sql

でやってみる。

成功！！！

これで、shell書いてcronを組めばいいんだ！
うーん。出来た。

次回は cronでバックアップを定期的に実行できるようにします。

前回【MySQLのバックアップをとろう！（同じサーバー内）】のmysqldumpを利用してMySQLのデータベースバックアップが出来たので、
これを定期的に行うようにしましょう。

cronを利用です！

まず、shellを書きましょう。

mysql_db_backup.sh
-------------------------------------
#!/bin/sh
bak=`date +%y%m%d`
cp /var/backup-file.sql /var/$bak.sql
mysqldump --opt --add-drop-table --add-locks --password=hoge database_name > /var/$bak.sql
chmod 700 /var/$bak.sql
bak2=`date --date "7 days ago" +%y%m%d`
rm -f /var/$bak2.sql
--------------------------------------

１日１回で、１週間前のファイルは削除と言うサイクルを繰り返すとすると、
こんな感じか・・・。
とりあえず、直接叩いてみる。

[root@internal mysql]# sh mysql_db_backup.sh

成功！！！

なので、これをcronで回す。
http://www.express.nec.co.jp/linux/distributions/knowledge/system/crond.html
↑一応cron設定ガイド。

実行したいユーザー（権限のあるユーザー）になり、

crontab -e

とやり、viが開くので、

0 4 * * * /var/mysql_db_backup.sh

※この設定だと、毎日4時0分にシェルを実行することになる。

とやり保存する。

・・・とりあえず、テストしたいが、朝４時までまってられないので、

0-59/3 * * * * /var/mysql_db_backup.sh

として、３分毎に実行してみる。

・・・３分経過・・・。
見てみる。

おお！！成功！！！

では、毎日4時0分の設定に戻して、保存！！

0 4 * * * /var/mysql_db_backup.sh

お疲れした！！

●主キー（PRIMARY KEY）
プライマリキー
プライマリキーとはレコードを1行ずつ識別するためのフィールドで、プライマリキーは必ずユニークな値となる。プライマリキーの指定により、膨大なデータから必要なレコードだけを確実に取り出すことが可能。

＞primary key 主キー制約(unique & not null)
＞主キーはテーブル毎にひとつだけ設定できます。ここで主キーを設定する列にはNOT NULL制約が必要です。
＞text型とBLOB型の列は、そのままでは主キーに含めることが出来ません。その列の先頭から何バイトを主キーとするか、明示的に指定してください。
「MySQL 全機能 リファレンス」P269

なるほど！！

●外部キー（FOREIGN KEY）
外部キー(Foreign Key）
テーブルのプライマリキーと同じ値を持った別のテーブルのフィールド値。

＞バージョン3.23.43から外部キー(FOREIGN KEY)制約がサポートされました。このとき、外部キー制約を設定するテーブルと被参照テーブルは、InnoDBテーブル型でなければなりません。また、被参照テーブルと被参照列には、一意性(UNIQUE)制約、インデックス(INDEX)、主キー(PRIMARY KEY)のいずれかが設定されていなければなりません。

なるほどーー！

＞外部キー制約を持つ参照元テーブルにデータを挿入しようとすると、その値が被参照テーブルの被参照列に存在するか否かを検査し、存在するときのみデータを挿入します。
「MySQL 全機能 リファレンス」P272

なるほど！！外部キーにはこんな役目があるんだ！！

＞では、データ挿入後に（被参照テーブルの）被参照列の値を更新したり削除したい場合はどうなるのでしょうか。デフォルト動作では、更新や削除をしようとするとエラーとなり、参照元テーブルと被参照テーブル間のデータ整合性を保ちます。
＞しかし、他のテーブルから参照されたばかりに、被参照テーブルの更新や削除ができなくなるのは不便です。この状況への対処法は、（外部キー制約を設定した）参照元のテーブルにON DELETE句とON UPDATE句を設定し、被参照テーブルでの更新や削除が行われた場合の動作（action）を指定することです。

＞例えば、動作として”CASCADE”を指定すると、（被参照テーブルの）被参照列の値が更新された場合は、参照もとの列にも更新が反映され、削除された場合は、参照元のテーブルも削除されます。
＞動作として"SET NULL"を指定すると、（被参照テーブルの）被参照列の値が更新や削除された場合に、参照もとの列に"NULL"が設定されます。同様に、"SET DEFAULT"の場合は、デフォルト値が設定されます。

なーるほどー！

リレーショナルデータベース実験！

http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/mysql_09.htm
http://dev.mysql.com/doc/refman/4.1/ja/join.html
http://wota.jp/ac/?date=20051107
↑こちらを参考にした。

http://wota.jp/ac/?date=20060514
↑Railsと絡めるならここも役に立ちます。

●●●●準備●●●●

create database r_test;

use r_test

create table units (
  id      int(8),
  unit_name  char(8),
  primary key (id)
) type=InnoDB;

create table company (
  id      int(8),
  name    char(8),
  age     int8,
  unit_id int8,
  primary key (id)
)type=InnoDB
;

create table reports (
  id      int(8),
  company_id  int(8),
  content char(8),
  primary key (id)
) type=InnoDB
;

insert into company values (1,'abc', 21, 1);
insert into company values (2,'def', 30, 2);
insert into company values (3,'ghi', 40, 1);

insert into reports values (1,1, 'test');
insert into reports values (2,2, 'test2');
insert into reports values (3,3, 'test3');

insert into units values (1,'aries');
insert into units values (2,'gemini');
insert into units values (3,'capu');

●●●●確認●●●●

mysql> show tables;
+------------------+
| Tables_in_r_test |
+------------------+
| company          |
| reports          |
| units            |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from units;
+----+-----------+
| id | unit_name |
+----+-----------+
|  1 | aries     |
|  2 | gemini    |
|  3 | capu      |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select * from company;
+----+------+------+---------+
| id | name | age  | unit_id |
+----+------+------+---------+
|  1 | abc  |   21 |       1 |
|  2 | def  |   30 |       2 |
|  3 | ghi  |   40 |       1 |
+----+------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from reports;
+----+------------+---------+
| id | company_id | content |
+----+------------+---------+
|  1 |          1 | test    |
|  2 |          2 | test2   |
|  3 |          3 | test3   |
+----+------------+---------+
3 rows in set (0.00 sec)

●●●●実験●●●●

mysql> select * from company inner join units where company.unit_id=units.id and units.id=1;
+----+------+------+---------+----+-----------+
| id | name | age  | unit_id | id | unit_name |
+----+------+------+---------+----+-----------+
|  1 | abc  |   21 |       1 |  1 | aries     |
|  3 | ghi  |   40 |       1 |  1 | aries     |
+----+------+------+---------+----+-----------+

mysql> select * from reports left join company on reports.company_id=company.id;
+----+------------+---------+------+------+------+---------+
| id | company_id | content | id   | name | age  | unit_id |
+----+------------+---------+------+------+------+---------+
|  1 |          1 | test    |    1 | abc  |   21 |       1 |
|  2 |          2 | test2   |    2 | def  |   30 |       2 |
|  3 |          3 | test3   |    3 | ghi  |   40 |       1 |
+----+------------+---------+------+------+------+---------+

おおおぉ。分かってきた・・・。


mysql> select * from ( reports left join company on reports.company_id=company.id ) 
  left join units on company.unit_id = units.id ;
+----+------------+---------+------+------+------+---------+------+-----------+
| id | company_id | content | id   | name | age  | unit_id | id   | unit_name |
+----+------------+---------+------+------+------+---------+------+-----------+
|  1 |          1 | test    |    1 | abc  |   21 |       1 |    1 | aries     |
|  2 |          2 | test2   |    2 | def  |   30 |       2 |    2 | gemini    |
|  3 |          3 | test3   |    3 | ghi  |   40 |       1 |    1 | aries     |
+----+------------+---------+------+------+------+---------+------+-----------+

おおおおぉ！完璧！
なんだ。できた。

さらに、where文も追加！

mysql> select * from ( reports left join company on reports.company_id=company.id ) 
  left join units on company.unit_id = units.id where units.id = 1;
+----+------------+---------+------+------+------+---------+------+-----------+
| id | company_id | content | id   | name | age  | unit_id | id   | unit_name |
+----+------------+---------+------+------+------+---------+------+-----------+
|  1 |          1 | test    |    1 | abc  |   21 |       1 |    1 | aries     |
|  3 |          3 | test3   |    3 | ghi  |   40 |       1 |    1 | aries     |
+----+------------+---------+------+------+------+---------+------+-----------+

完璧！