トップ 差分 一覧 ソース 検索 ヘルプ PDF RSS ログイン

MySQL

初期設定

初期状態ではパスワードが設定されていないので、だれでもログインできる。

$ mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 96 to server version: 4.1.15

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

現在のデータベースを確認する。

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+

mysqlにユーザなどの設定が格納される。まずはテーブルをみるには

mysql> use mysql;
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
mysql> describe user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | varchar(60)                       |      | PRI |         |       |
| User                  | varchar(16)                       |      | PRI |         |       |
| Password              | varchar(41)                       |      |     |         |       |
| Select_priv           | enum('N','Y')                     |      |     | N       |       |
| Insert_priv           | enum('N','Y')                     |      |     | N       |       |
| Update_priv           | enum('N','Y')                     |      |     | N       |       |
| Delete_priv           | enum('N','Y')                     |      |     | N       |       |
| Create_priv           | enum('N','Y')                     |      |     | N       |       |
| Drop_priv             | enum('N','Y')                     |      |     | N       |       |
| Reload_priv           | enum('N','Y')                     |      |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     |      |     | N       |       |
| Process_priv          | enum('N','Y')                     |      |     | N       |       |
| File_priv             | enum('N','Y')                     |      |     | N       |       |
| Grant_priv            | enum('N','Y')                     |      |     | N       |       |
| References_priv       | enum('N','Y')                     |      |     | N       |       |
| Index_priv            | enum('N','Y')                     |      |     | N       |       |
| Alter_priv            | enum('N','Y')                     |      |     | N       |       |
| Show_db_priv          | enum('N','Y')                     |      |     | N       |       |
| Super_priv            | enum('N','Y')                     |      |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     |      |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     |      |     | N       |       |
| Execute_priv          | enum('N','Y')                     |      |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     |      |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     |      |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') |      |     |         |       |
| ssl_cipher            | blob                              |      |     |         |       |
| x509_issuer           | blob                              |      |     |         |       |
| x509_subject          | blob                              |      |     |         |       |
| max_questions         | int(11) unsigned                  |      |     | 0       |       |
| max_updates           | int(11) unsigned                  |      |     | 0       |       |
| max_connections       | int(11) unsigned                  |      |     | 0       |       |  
+-----------------------+-----------------------------------+------+-----+---------+-------+
mysql> select user,host,password from mysql.user;

rootにパスワードを追加

mysql> set password for root@localhost=password('rootパスワード');
mysql> set password for root@'foo.bar.com'=password('rootパスワード');

匿名ユーザの削除

mysql> delete from mysql.user where user='';

ログイン

$ mysql -u root -p
Enter password:

データベースとユーザーの作成

mysql> create database sampledb;
mysql> grant all on sampledb.* to sampleuser@localhost
    -> identified by 'pass';

ついでにデータベースの削除

mysql> drop database sampledb;

ルートを誤って消してしまった場合

まずはmysqldを停止する

# killall mysqld

つぎに--skip-grant-tablesオプションをつけて起動する

/usr/bin/safe_mysqld --skip-grant-tables &

これは/etc/my.confに記述してもよい

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables

利用後は戻すのを忘れないこと

これで起動した場合はすべてのユーザーが管理者権限を持つらしいが、
自分の環境では

mysql> grant all on *.* to root@localhost;

がUnknown commandとなってしまい、実行できなかった。
仕方なく

mysql> insert into mysql.user values('localhost','root',password('hogehoge'),      
       'y','y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y', 'y');

mysql> flush privileges;

として対応した。
以降はgrant文が利用できるようになった。

ソースからのインストール

mysql-5.0.33をソースからインストールする。

# cd /usr/local/src
# wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.33.tar.gz/from/ftp://ftp.iij.ad.jp/pub/db/mysql/
# chmod 755 mysql-5.0.33.tar.gz
# tar -zxvf mysql-5.0.33.tar.gz
# cd mysql-5.0.33
# ./configure \
--prefix=/usr/local/mysql \
--with-charset=utf8 \
--with-extra-charsets=all

makeにはPentium2 300MHzで3時間ぐらいかかる。

# make
# make install

mysqlのmakeにはG++とlibtoolが必要。
無い場合はmakeの途中でエラーになる。

# yum install gcc-c++ libtool

gcc-c++がG++で、libtoolにはautomakeなどがついてくる。
あとから入れた場合configureしなおすこととmake cleanを忘れずに。

mysqlのグループとユーザーを追加する。

# groupadd mysql
# useradd -g mysql mysql

実行前の準備。

# cd /usr/local/mysql/bin
# ./mysql_install_db --user=mysql
# chown -R root  /usr/local/mysql
# chown -R mysql /usr/local/mysql/var
# chgrp -R mysql /usr/local/mysql
# cp /usr/local/mysql/share/mysql/my-medium.cnf /etc/my.cnf

ためしに起動してみる。

# /usr/local/mysql/bin/mysqld_safe --user=mysql

なぜかこの後ターミナルがハングアップ。でもmysql自体は動作した。

# /usr/local/mysql/bin/mysqld_safe --user=mysql &

最後に&をつけなかった為と思われる。

# /usr/local/mysql/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye

パスワードの設定

# /usr/local/mysql/bin/mysqladmin -u root password 'password'
# /usr/local/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.18-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)

mysql> quit
Bye

自動起動用のスクリプトは

# /usr/local/mysql/share/mysql/mysql.server start   
# /usr/local/mysql/share/mysql/mysql.server stop

init.dに移す。

# cp mysql.server /etc/init.d/mysqld
# cd /etc/init.d
# chkconfig --add mysqld

mysqlを起動しやすいようにシンボリックリンクを作成。

# ln -s /usr/local/mysql/bin/mysql /usr/local/bin

パスワードの無いユーザーを削除。

mysql> use mysql;
Database changed
mysql> delete from user where password="";
Query OK, 3 rows affected (0.02 sec)

キャラクターセット

mysql-5.0.18のキャラクターセット

mysql> show character set;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
| cp850    | DOS West European           | cp850_general_ci    |      1 |
| hp8      | HP West European            | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                    | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |
| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                 | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |
| macce    | Mac Central European        | macce_general_ci    |      1 |
| macroman | Mac West European           | macroman_general_ci |      1 |
| cp852    | DOS Central European        | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |
| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |
| binary   | Binary pseudo charset       | binary              |      1 |
| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |
+----------+-----------------------------+---------------------+--------+

default-character-setをutf8でインストールした直後のステータス

mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using  EditLine wrapper
Connection id:          4
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.0.18-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 22 min 44 sec

ちなみにMySQL4.0.26でのstatus。キャラクターセットの設定がServerとClientしかない。

mysql> status;
--------------
mysql  Ver 12.22 Distrib 4.0.26, for pc-linux-gnu (i686)

Connection id:          3630
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Server version:         4.0.26-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    sjis
Server characterset:    sjis
UNIX socket:            /tmp/mysql.sock
Uptime:                 4 days 22 hours 8 min 29 sec

Threads: 1  Questions: 96612  Slow queries: 0  Opens: 107  Flush tables: 1  Open tables: 53  Queries per second avg: 0.227
--------------

mysqlクライアントでキャラクターセットを変更

操作しているときに限り変更できる。 永続的にはmy.cnfとかで。

mysql> charset utf8
Charset changed

インストール

FC6でRPMパッケージからインストールする場合

# yum install mysql mysql-server

mysqlはmysqlクライアントの模様。依存パッケージとしてperl-DBIが入ってくる。
mysql-serverがmysqldの本体。こちらが本命。

MySQLのシステム系のDBがインストールされていない場合はmysqlが起動できないため、mysql_install_dbを実行する。

# /usr/bin/mysql_install_db

この際、/var/lib/mysql/以下のDBのファイルがrootで作成されてしまうため、所有者:グループをmysqlに変更。

# chown -R mysql:mysql /var/lib/mysql/*

mysqlサーバーを起動できるはず。

# /etc/init.d/mysqld start

起動できたらrootのパスワードを変更しておく。

# /usr/bin/mysqladmin -u root password 'password'

rootでログインし、パスワードが設定されていないユーザーを削除。

# mysql -u root -p
mysql > select user,password,host from mysql.user;
mysql > delete from mysql.user where password = '';
mysql > quit

DBバックアップ

すべてのデータベースをバックアップ&復元

$ mysqldump -u root -p -x --all-databases > all-database.sql
$ mysql -u root -p < all-database.sql

パラメータの-xは--lock-all-tablesと同じ。ダンプ中にデータ変更されないようにやっておいたほうが良さそう。

任意のデータベースをバックアップ&復元

$ mysqldump -u root -p -x db-name > db-name.sql
$ mysql -u root -p db-name < db-name.sql

db-nameは、あらかじめ空で作成しておく必要がある。

テーブルのコピー

テーブルを作成するときに、LIKEでコピー元テーブルを指定できる。
レコードもSELECTの結果をINSERTできる。 WHEREで条件に合ったものをINSERTすることも可能。

mysql> CREATE TABLE bar LIKE foo;
mysql> INSERT INTO bar SELECT * FROM foo;

外部SQLの実行

$ mysql -u root -p --default-character-set=utf8 openpne2 < sample.sql

オートインクリメントの初期化

初期化、もしくは任意の番号からスタートするようにセットできる。MySQL5にて確認のため、以前のバージョンでできるかは不明。

mysql> ALTER TABLE table_name AUTO_INCREMENT = 1;

my.cnf

my.cnfのサンプルとして、以下のファイルのが用意されている。

ファイル名登載メモリ
my-small.cnf64MB以下
my-medium.cnf128MB
my-large.cnf512MB
my-huge.cnf1-2GB
my-innodb-heavy-4G.cnf4G以上

rpmでインストールしたなら/usr/share/mysqlにあるはず。
apt-getなら/usr/share/doc/mysql-server-5.*/examplesあたり。

5.0系のときは

[mysqld]
default-character-set = utf8

だったが、5.1系では

[mysqld]
character-set-server = utf8

推奨となったみたい。

レプリケーション

レプリケーションはマスター、スレーブのようにMySQLを分散して使用する場合のもので、1つで使う場合は不要なのだが

/etc/my.cnf

#log-bin = mysql-bin

とコメントアウトすると下記のようなエラーで再起動できない。

100704 10:01:54 [ERROR] You need to use --log-bin to make --binlog-format work.
100704 10:01:54 [ERROR] Aborting

100704 10:01:54 [Note] /usr/libexec/mysqld: Shutdown complete

このあたりをセットでコメントアウトするみたい。

#log-bin = mysql-bin
#binlog_format = mixed
#server-id = 1

/var/lib/mysqlにあるmysql-bin.xxxxxxを削除する。

PURGE MASTER LOGS before now();

もしくは

# rm /var/lib/mysql/mysql-bin.*

で根こそぎ削除。

逆にレプリケーションを使用する場合は

現場指向のレプリケーション詳説
http://www.irori.org/doc/mysql-rep.html

innodbが有効にならない

/var/lib/mysql/***.errをみたら

InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 67108864 bytes!
100704 10:36:05 [ERROR] Plugin 'InnoDB' init function returned error.
100704 10:36:05 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

という感じ。

アップグレードか、またはmy.cnfをいじってるうちに、ログファイルのサイズが一致しないというエラーになって、結果的にinnodbが無効になっていた模様。

# rm ib_logfile*
# /etc/init.d/mysqld restart

でログが再起動時に再構築され、innodbが有効となった。

utf_general_ciとutf8_unicode_ciの違い

  • utf8_general_ci:比較条件の拡張なし
  • utf8_unicode_ci:比較条件の拡張あり

となっていて、あいまいに比較させたければunicodeって感じなのかな?

TETLIST:MySQL の照合順序の命名規則
http://tetlist.info/2009/01/mysql

参考サイトによると、unicode_ciのとき

aとAとaとA
かとがとカとガとカとガ

のような感じで比較時に一致するみたい。
ちなみに末尾のciは半角英字の大文字と小文字を区別しないという意味。

utf8_binだとバイナリでの比較となるので、大文字小文字を区別する。ただしソートもバイナリでのソート(汗)。

BLOB型

画像等、ファイルをバイナリで格納するのに使う。MEDIUMBLOBあたりが普通?

最大サイズ(バイト)
TINYBLOB255(28−1)255B
BLOB65535(216−1)6.4KB弱
MEDIUMBLOB16777215(224−1)16MB弱
LONGBLOB4294967295(232−1)4GB弱

MySQL 5.5

MySQL5.1系から5.5系へyum updateしたらハマった(汗)。
(5.5はremi)

まず、5.1に既存のデータがあるなら

  • データをダンプ
  • 5.5へyum update
  • mysql_upgrade
  • データをリストア

みたいになりそう。

自分の環境ではmysql_upgradeがうまく実行できなくて?、とりあえずデータが無かったから/var/lib/mysql内のファイルを消して対応した(汗)。

# yum --enablerepo remi install mysql mysql-server

デフォルトのmy.cnfだとレンタル鯖のメモリに対するメモリ使用量が大きかったので、my-medium.cnfを使用。

# mv /etc/my.cnf /etc/my.cnf.rpmsave
# cp /usr/share/mysql/mysql-medium.cnf /etc/my.cnf

my.cnfで気になった変更点は

[mysqld]
#default-character-set = utf8 ←非推奨になった(mysqldのみ)
character-set-server = utf8
:
slow-query-log = 1
slow-query-log-file = slow-queries.log
long_query_time = 3
#log-long-format ← 廃止になった
sakaikの日々雑感〜(T)編 - MySQL 5.5.3-m3 で廃止になった変数やオプションなどを整理しました
http://d.hatena.ne.jp/sakaik/20100414/mysql533obsol

rootにパスワードを設定する際は

# /usr/bin/mysqladmin -u root password 'new-password'

クエリ

UNIONでつなげた結果をソートする場合は、↓のように括弧でくくる。

(SELECT '_', item_no, overview FROM items_overview)
UNION
(SELECT 'M', item_no, overview FROM member_items_overview)
ORDER BY item_no

LEFT JOIN の例。

SELECT items.no, member_items.no
FROM items LEFT JOIN member_items
ON items.no = member_items.no

あるテーブルの重複をなくした結果をカウント。

SELECT count(*) FROM (SELECT DISTINCT * FROM items_image) AS tmp

あるテーブルの指定フィールドをグループ化単位でカウント。

SELECT count(item_no) FROM items_image GROUP BY item_no

SELECTの結果をINSERTする。

INSERT INTO
 `tmp_items_image`
 (`item_no`,`thumbnail`,`image`,`name`,`type`,`created`,`modified`)
SELECT
 `item_no`,`thumbnail`,`image`,`name`,`type`,`created`,`modified`
FROM
 `member_items_image`
WHERE 1
ORDER BY `created`,`id`

あるテーブルに存在しない item_no を抽出。

SELECT
 item_no
FROM 
 items_image
WHERE
 item_no NOT IN (SELECT DISTINCT item_no FROM member_items_image)

Invalid default value for 'created'

MySQL5.5まではDATETIME型のデフォルト(初期値)に定数しか入れなれない模様。
MySQL5.6でDATATIME型のデフォルトにCURRENT_TIMESTAMPを設定したデータベースをエクスポートし、5.5にインポートしようとしたらエラーになった(汗)。

対応としては5.6にアップグレードするか、デフォルトのCURRENT_TIMESTAMPを止めるか、DATETIME型をTIMESTAMP型に変更する。

MySQL 5.6 Automatic Initialization and Updating for TIMESTAMP and DATETIME
http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

ちなみに

  • TIMESTAMP型 4バイトで1970-2037年
  • DATETIME型 8バイトで1000-9999年

insertにselectの結果とインクリメント値を挿入

例は日本郵便の郵便番号辞書を取り込んだテーブルから「市」で重複を排除し、結果をcitiesテーブルに挿入する。
その際にpriority(優先順)を10ずつ加算した値を設定する、みたいな内容。

SET @priority := 0;
INSERT INTO `cities`(`pref`, `city`, `priority`, `created`)
SELECT `t`.`pref`, `t`.`city`, (@priority := @priority + 10), now()
FROM
(SELECT DISTINCT `pref`, `city` FROM `zipcode` ORDER BY `id`, `city_kana`) as `t`;

副照会じゃなきゃ

(@priority := ifnull(@priority, 0) + 10)

でもいけるらしいけど未確認。