データベースをコピーする方法

mysqldumpコマンドとmysqlコマンドを使えば簡単に、データベースのコピーができます。

対象のDB(ここではhogedb)をバックアップします

$ mysqldump hogedb -uhogeuser -phogepass > backup.db

バックアップしたdbを別のDB名(ここではfoodb)としてリストアします

$ mysql foodb -uhogeuser -phogepass < backup.db

このときfoodbは作成済みである必要があります。

phpMyAdminでWordPressの内容を一括置換したい!

phpMyAdminでWordPressの内容を一括置換してみます。
文字列置換なので、replace()関数を使用します。

また、WordPressの本文は “XXX_posts” テーブルの “posts_content” テーブルになります。
(たとえば、wp_postsテーブル)

なので、以下のようにすることで、WordPress内の本文にある”ポテト”を”ポティトォ”に変えられます。

update wp_posts set post_content = replace(post_content, "ポテト", "ポティトォ");

phpMyAAdminでこれを実行するには、”SQL”タブを表示して表示されたフォームに上記を記載して
その右下にある「実行」ボタンを押すと実行できます。

一歩間違うとデータを消してしまうことにもなりかねないので注意が必要です。

既存のカラムにINDEXを追加する。

テーブル内のカラムに対し INDEX を設定すると、
そのカラムをキーに検索を行った時のパフォーマンス
の向上がはかれます。

既存のカラムにこのINDEXを設定するには以下のように
ALTER TABLEを使用します。

ALTER TABLE  ADD INDEX ();

実施例を以下に記載します。

mysql> show fields from mytest;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

mysql> ALTER TABLE mytest ADD INDEX (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show fields from mytest;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  | MUL | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

INDEXが設定されたカラムには上記のように”Key”に”MUL”が設定されます。

これは”MULTI VALUE”という意味で、ユニークなキーではなく、同じ値が
あり得るキーですよ。という意味っぽいです。

そこで、このカラムにさらに UNIQUEキー を設定してみると、Keyの
値が”UNI”に代わりました。
これはユニークなキーってことですね。

mysql>
mysql> ALTER TABLE mytest ADD UNIQUE (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show fields from mytest;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | YES  | UNI | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

既存のテーブルにあるカラムの名前を変更する

以下の構文でカラム(フィールド)の名前を変えることができます。

ただし、同時に型も指定する必要があり、これを正しく指定しておかないと
名前だけでなく型も変わってしまうので注意が必要です!

ALTER TABLE  CHANGE   ;
mysql> ALTER TABLE reservation CHANGE has_insurance is_insurance TINYINT;
Query OK, 0 rows affected (0.22 sec)

MySQLのテーブルから列を削除する

作成済みのテーブルに対して後から列を削除したくなるときもあります。

そういう時は、”alter table”で”drop”句を使って行います。

書式は以下です。

 alter table  drop 

addressテーブルからtemperatureフィールドを削除する場合は以下のようになります。

mysql> alter table address drop temperature;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

テーブルにある既存のカラムのデータ型を変更する

既存のカラム(列)のデータ型を変えるには “ALTER TABLE”を使用します。

基本的な書式は以下です。

mysql> ALTER TABLE  MODIFY COLUMN  ;

例を示すと以下です。

mysql> ALTER TABLE mytest2 MODIFY COLUMN age TINYINT;

以下実行画面になります。

mysql> show fields from mytest2;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100)     | YES  |     | NULL    |                |
| age    | int(11)          | YES  |     | NULL    |                |
| weight | int(11)          | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE mytest2 MODIFY COLUMN age TINYINT;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show fields from mytest2;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100)     | YES  |     | NULL    |                |
| age    | tinyint(4)       | YES  |     | NULL    |                |
| weight | int(11)          | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MySQLのデータ型(データの種類)

“create table”で、テーブルを作成するときや、”alter table”でテーブルの構造を
変更するときに、各カラム(列)のデータ型を指定します。

大きく分けて、「整数型」「浮動小数点型」「文字列型」があります。

整数型

最小値 最大値
TINYINT -128 127
SMALLINT -32768 32767
MEDIUMINT -8388608 8388607
INT -2147483648 2147483647
BIGINT -9223372036854775808 9223372036854775807

浮動小数点型

負の数 正の数
FLOAT -3.402823466E+38~-1.175494351E-38 1.175494351E-38~3.402823466E+38
DOUBLE -1.7976931348623157E+308~-2.2250738585072014E-308 2.2250738585072014E-308~1.7976931348623157E+308

テーブル内のデータを更新する(UPDATE)

すでにテーブル内にあるデータを更新するには “UPDATE”文を使用します。

簡単に以下の書式になります。

mysql> UPDATE  SET column1=value1

これだけだとすべて行のデータの値が変わってしまうので、通常は
WHERE節で条件を指定し、特定の行のデータのみ変更します。

たとえば、id が 1の行の ageカラムを変更する場合は以下になります。

mysql> UPDATE mytest2 SET age=44 WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from mytest2;
+----+-------+------+--------+
| id | name  | age  | weight |
+----+-------+------+--------+
|  1 | hogeo |   44 |     89 |
+----+-------+------+--------+
1 row in set (0.00 sec)

ここでは、age=44 と値をそのまま書いてますが、値が文字列のときは「”」か「’」で
囲む必要がありますので注意してください。

テーブルへデータを挿入する

mysqlのデータベースへのデータの挿入は”INSERT”文を使用します。

簡単な書式は以下です。

mysql> INSERT INTO  (column1, column2,...) VALUES(value1, value2,...)

以下に簡単な例を示します。

mysql> show fields from mytest2;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(100)     | YES  |     | NULL    |                |
| age    | int(11)          | YES  |     | NULL    |                |
| weight | int(11)          | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into mytest2 (name, age, weight) values("hogeo", 43, 89);
Query OK, 1 row affected (0.00 sec)

ここでは、mytest2というテーブルに name=”hogeo”, age=48, weight=89 という
データを挿入しています。

挿入後のテーブルは以下のようになります。

mysql> select * from mytest2;
+----+-------+------+--------+
| id | name  | age  | weight |
+----+-------+------+--------+
|  1 | hogeo |   43 |     89 |
+----+-------+------+--------+
1 row in set (0.00 sec)

データのバックアップとリストア方法

MySQLのデータのバックアップは mysqldump というコマンドで実施可能です。

基本的な使い方は以下のような書式になっています。

$ mysqldump [options] db_name [tables]
$ mysqldump [options] --databases DB1 [DB2 DB3...]
$ mysqldump [options] --all-databases

データベースごとバックアップ

–databases XXでデータベースを指定します。
たとえば、hogedbのデータをbackup.dbにバックアップしたいときは以下のようにします。
(hogedbのユーザIDとパスワードが userid/password の場合)

$ mysqldump --database hogedb -uuserid -ppassword > backup.db
$ mysqldump hogedb -uuserid -ppassword > backup.db

これでも一緒かも。

–databases の場合はデータベースを複数指定できるようです。

テーブルを指定してバックアップ

テーブルを指定する場合は以下のようにします。

$ mysqldump hogedb -uuserid -ppassword table1 > backup.db

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

すべてのデータベースをバックアップする際は、以下のように”–all-databases”オプションを
指定します。

$ mysqldump --alldatabases > backup.db

バックアップしたMySQLデータをリストアする方法

以下のようにします。

$ mysql -uuserid -ppassword < backup.db

データベースを指定する際は以下のようにします。

$ mysql hogedb -uuserid -ppassword < backup.db