MySQL データベースに関する備忘録

データベースソフトのMySQLに関する備忘録。

データベースのバックアップ・復元

mydbというデータベースを日付を付けたファイル名でバックアップする

$ mysqldump -u root -p [password] mydb --opt > `date +"%y%m%d"`.sql

全てのデータベースをバックアップする

$ mysqldump -u root -p [password] --all-databases > dump_all.sql

mydbというデータベースのmytableテーブルだけをバックアップする

$ mysqldump -u root -p [password] mydb mytable > dump_tbl.sql

mydbというデータベースにdump.sqlの中身を復元する

$ mysql -u root -p [password] mydb < dump.sql

全てのデータベースにdump.sqlの中身を復元する

$ mysql -u root -p [password] < dump.sql

バックアップオプションについては「MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム」を参照。

データベースの接続

mydbというデータベースに接続する

$ mysql --user=root --pass=[password] mydb

テーブルの作成

標準的なテーブルを作成する

会員ID(customer_id)、グループID(group_id)、会員名(customer_name)、会員読み仮名(customer_yomi)フィールドを持つcustomerという名のテーブルを作成する。

create table customer (
  customer_id int(8),
  group_id int(8),
  customer_name varchar(32),
  customer_yomi varchar(2)
);

制約をつけてテーブルを作成する

会員ID、会員名、会員読み仮名にnullの挿入を不許可、グループIDが未指定ならnull、会員名が未指定ならnanashiをデフォルトで挿入する。

create table customer (
  customer_id int(8) not null,
  group_id int(8) default null,
  customer_name varchar(32) not null default 'nanashi',
  customer_yomi varchar(2) not null
);

主キーを指定してテーブルを作成する

会員ID(customer_id)フィールドを主キーにする。

create table customer (
  customer_id int(8) not null auto_increment,
  group_id int(8) default null,
  customer_name varchar(32) not null default 'nanashi',
  customer_yomi varchar(2) not null,
  primary key (customer_id)
);

主キーはテーブル内にひとつだけ。
主キーにするフィールドは必ず「not null」の制約が必要となる。
主キーにしたフィールドはユニーク(重複しない)な値で、インデックスが作成される。
「auto_increment」を指定すると、自動的に重複しないユニーク値が割り当てられる。

インデックスをつけてテーブルを作成する

会員読み仮名(customer_yomi)フィールドにインデックスをつける。

create table customer (
  customer_id int(8) not null auto_increment,
  group_id int(8) default null,
  customer_name varchar(32) not null default 'nanashi',
  customer_yomi varchar(2) not null,
  primary key (customer_id),
  index (customer_yomi)
);

インデックス名は省略可なので省略。
読み仮名で会員を検索するときインデックスがあるので検索が高速になる。

テーブルの削除

customerという名前のテーブルを削除する

drop table customer;

CSVファイルをインポートする

サーバ上のCSVファイルををデータベースmydbのテーブルmytableにインポートする

LOAD DATA INFILE "/home/yamada/mydata.txt" INTO TABLE mydb.mytable;

CSVとテーブルのフィールドの並びや数が異なるCSVファイルをインポートする

LOAD DATA INFILE "/home/yamada/mydata.txt" INTO TABLE mydb.mytable (name, customer_id);

CSVのデータ構成を指定する。

データ区切りがカンマ、データ囲み文字がダブルクォーテーション、改行が¥r¥nの形式のCSVをインポートする

LOAD DATA INFILE "/home/yamada/mydata.txt" INTO TABLE mydb.mytable (name, customer_id)
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '¥r¥n';

CSVはデフォルトで「データ区切りはタブ、データ囲み文字は無し、改行は¥ｎ」の形式になる。これと異なる場合はそれぞれ指定する必要がある。

データベース構成の確認

操作中のデータベースのテーブル一覧を表示する

show tables;

テーブルmytableの構成を表示する

desc mytable;

バイナリログの削除

バイナリログを全削除して新しいバイナリログを作成（リセット）する

RESET MASTER;

SQLインジェクション対策

SQLインジェクションとは？

SQLインジェクションとは、 操作にSQLと呼ばれる言語を用いるタイプのデータベースを使用するアプリケーションに対し、 その入力に本来入力としては使われることが想定されていないSQL文を挿入すること(injection - インジェクション)で、 データベースに不正な操作を加える攻撃方法のことです。

https://www.nic.ad.jp/ja/basics/terms/sql-injection.html

SQLインジェクションの例

ユーザーが入力した人物名を「入力データ」に入れて、それに合致する情報があれば取得するSQL文がある。

SELECT * FROM users WHERE name = '[入力データ]';

この入力データに、例えば「' OR 't' = 't」が指定されると次のようなSQL文が実行される。

SELECT * FROM users WHERE name = '' OR 't' = 't';

すると、WHERE句の条件は「nameが空っぽ、もしくは、't' = 't'」となり、条件が常に成立してusersテーブル全ての情報が取り出せてしまう。いわゆる個人情報流出となる。

対応策として入力データをエスケープ処理する。PHPにはそのためのmysql_real_escape_string関数が予め用意されている。

mysql_real_escape_string関数を使ってSQLインジェクションを防ぐ

<?php
//マジッククオートがONの場合、自動的にaddslashesによるエスケープ処理がされるので元に戻す
if(get_magic_quotes_gpc()) {
  $name = stripslashes($_POST['name']);
} else {
  $name = $_POST['name'];
}

//入力データをエスケープ処理して安全な入力データに加工する
$name2 = mysql_real_escape_string($name);

//SQL文を作成
$sql = "SELECT * FROM users WHERE name = '" . $name2 . "'";
?>

3ステップでしっかり学ぶ MySQL入門 ［改訂2版］