DB/MySQL の履歴(No.17)

日本ではPostgreSQLに比べるとマイナーでも世界では標準。Bugzillaのために入れてみる。しかし操作が複雑ですぐ行き詰まるためかなり毛嫌いしているが、特に海外製のソフトの場合標準採用されているので2010年より本格的に利用開始

文字コード
- クライアント側の設定
- mysql.iniでの文字コードの設定
管理ツール
データベース作成、ユーザ作成などの主に管理系タスク
- データベース
  - mysqlのコマンドラインから
  - コマンドラインから
- ユーザ作成、権限付与
コマンド集
テーブル構造やデータ型
注意点
- データベース名やテーブル名は大文字小文字を区別する。
GUI管理ツール
- phpMyAdmin
インポート、エクスポート
- CSVインポート
バックアップ&リカバリー
トラブルシューティング
- パスワードを忘れて再設定したい
FAQ
- パスワード忘れた

文字コード†

PostgreSQL等と違ってテーブルクリエート時に指定できないのだろうか？/etc/my.cnfをいちいち書き換えないと駄目なのか？

mysqlのコンソールの状態でも見ることができる。

mysql> status

以下のコマンドを打ち込むと現状が見える。

SHOW VARIABLES LIKE 'character\_set\_%';

テーブル作成の時に指定する(5.0で確認）

create database codezine default charset utf8;

テーブル作成時の文字コードをみる
```
show create database データベース名
```

↑

クライアント側の設定†

通常のmysqlで実行される文字コードは初期設定だとlatin1。これで文字化けしないわけがないので適宜文字コードを設定しておく。

mysql> status;
mysql> set character set utf8;

↑

mysql.iniでの文字コードの設定†

デフォルトのcharsetを設定ファイルに記載しておくと手間が省ける

[mysql]
default-character-set=文字コード
[mysqld]
default-character-set=文字コード
[client]
default-character-set=文字コード

↑

管理ツール†

Windowsで出来るGUIツールを開発元が配布しているのが心強い。MySQL AdministratorやMySQL Query Browser等がある。

↑

データベース作成、ユーザ作成などの主に管理系タスク†

↑

データベース†

↑

mysqlのコマンドラインから†

文字コードを指定しないとlatin1になってしまう。

DB作成	mysql> create database wordpress default character set utf8;

↑

コマンドラインから†

DB一覧	mysqlshow
DB作成	mysqladmin -u root create TEST
DB削除	mysqladmin -u root drop TEST

↑

ユーザ作成、権限付与†

ユーザの考え方が特殊でなれないとハマる。ユーザ名+アクセス元ホスト名分だけユーザが存在すると考えておいた方がよい。ワイルドカードで指定する事も出来るが、localhostとその他のホストは分けることが出来ない。自宅サーバなら殆どlocalhostのユーザだけで事足りるのだろうが、他の場所からアクセスさせるなら要注意。

↑

コマンド集†

インポート	load data infile 'c:/ken_all.csv' into table テーブル名
エクスポート	select * from テーブル名 into outfile '絶対パスでファイル名'

DB接続	mysql -u root -p DB_NAME
ユーザ作成	mysql> grant select,insert,delete,update,create,drop,file,alter,index on . to ユーザ名 identified by 'パスワード';
バッチ処理	mysql -u root -p DB_NAME < sql.txt
バッチ処理 on シェル	mysql> source ファイル名
TABLE一覧	SHOW TABLES;
プロセスの確認	SHOW processlist
DBダンプ	$ mysqldump -u root データベース名 > dump.sql
列追加	ALTER TABLE テーブル名 ADD フィールド名 varchar(200) not null [DEFAULT 0] [AFTER <カラム名>];
列削除	ALTER TABLE テーブル名 DROP フィールド名
シーケンスを任意の値に	ALTER TABLE tbl AUTO_INCREMENT = 100;

↑

テーブル構造やデータ型†

↑

Tableの形式†

InnoDB
- トランザクション対応

MyISAM
- トランザクション非対応で速度重視。

INNODB形式でテーブルを作成するサンプル

CREATE TABLE animals (
            id MEDIUMINT NOT NULL AUTO_INCREMENT,
            name CHAR(30) NOT NULL,
            PRIMARY KEY (id)
            ) engine=InnoDB;

↑

データ型†

CHAR,VARCHARは4.1以降文字数。それ以前はバイト数なので日本語扱うときは注意
最大以上のサイズを入れた場合、はみだし分が切り落とされるだけでエラーにはならないので注意。数値型のデータも同様

↑

順序型†

フィールドにAUTO_INCREMENTをつける。ただしひとつのテーブルに付ひとつまで

↑

順序に任意の値を設定†

alter table table_name auto_increment=12345;

↑

注意点†

↑

データベース名やテーブル名は大文字小文字を区別する。†

これはOSが大文字小文字区別するならば、DBとdbは別のデータベースとして取扱われる。テーブルに付いても同様である。WINDOWSのように区別しないOSの場合はその心配はない

↑

GUI管理ツール†

↑

phpMyAdmin †

WEBからDB管理ができてしまうというツール。日本語化されていて、解凍即実行可能。海外ではよく使われているのかphpmyadminへの不正アクセスが多数・・・・

PHP 4,5.1では2.9系、PHP 5.2では3.x系を利用できる。

CentOS5では5.1.6なので2.xを使う

インストール

解凍する。3.x系ならアクセスすれば自動で設定画面に飛ぶと思った。

設定 config.inc.phpでAllowNoPasswordをtrueにしないと、パスワードなしログインはできない。

↑

インポート、エクスポート†

↑

CSVインポート†

デリミターを指定しない場合はタブ区切りとなる。
日本語関係のフィールドをロードする場合はクライアントの文字コードをセットしておいた方が良いかも。

LOAD DATA LOCAL INFILE 'car_import.csv' INTO TABLE テーブル名 FIELDS TERMINATED BY ',';

フィールドを指定する場合

LOAD DATA LOCAL INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

autoincrement列を除外する場合はその列を除外すれば自動的に連番がふられる（insert分でも同様)

LOAD DATA LOCAL INFILE 'test.txt' INTO TABLE test (col1);

↑

バックアップ&リカバリー†

↑

テーブル単位†

主キーがかぶらないようにdrop tableして作り直す構文つきで出力

mysqldump -u root -p --add-drop-table DB名 テーブル名 > /var/tmp/rooms.sql

戻すときはデータベースの指定だけでOK

mysql -u root -p DB名 < /var/tmp/rooms.sql

↑

データベース単位†

↑

バックアップ†

バックアップ対象はデータベースmysqlとし、出力ファイル名はmysql.sqlとする。

mysqldump -u root -p データベース名 > mysql.sql

危険を承知でパスワードをコマンドに入れてしまう場合はこちらで

mysqldump -u root --password=パスワード データベース名 > mysql.sql

↑

リカバリー†

リカバリー対象データベースはcreateしておく。

mysql> create database mysql2;
mysql> exit
mysql -u root -p mysql2 < mysql.sql

↑

全部のデータベース†

↑

バックアップ†

全部のデータベースを出力

mysqldump -u root -x --all-databases > dump.sql

↑

リカバリ†

全部のデータベースをインポート(もとあるＤＢは名前がかぶらなければ残る）

mysql -u root -p < dump.sql

リカバリーした後はrootでログインして、flush privilegesすること。さもないと移行されたユーザでログインできない

↑

トラブルシューティング†

↑

パスワードを忘れて再設定したい†

起動引数に--skip-grant-tablesをつける。 centos4.8なら以下のようにする。

/usr/bin/mysqld_safe --skip-grant-tables

mysql -u root
mysql> use mysql
mysql>  UPDATE user SET Password=PASSWORD('') WHERE User='root';
mysql> flush privileges;

↑

FAQ†

↑

パスワード忘れた†

1./etc/my.cnfを編集
```
[mysqld]
skip-grant-tables
```

2.mysqlを再起動

3.mysqlデータベースに接続

mysql -u root mysql

4.以下のSQLでパスワード変更

mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='root';
mysql> flush-privileges;

mysqldの引数に --skip-grant-tables をつける(ちょっとめんどくさい）

しかしこれだと固まりやがる場合があり（超不安定ＤＢ）
mysql> grant all on *.* to root@localhost identified by "パスワード";

これでパスワード認証を求められる
mysql -u root -p xoops

/var/lib/mysql/mysql以下を全部消してinitする

Counter: 18979, today: 1, yesterday: 3

DB/MySQL の履歴(No.17)

文字コード†

クライアント側の設定†

mysql.iniでの文字コードの設定†

管理ツール†

データベース作成、ユーザ作成などの主に管理系タスク†

データベース†

mysqlのコマンドラインから†

コマンドラインから†

ユーザ作成、権限付与†

コマンド集†

テーブル構造やデータ型†

Tableの形式†

データ型†

順序型†

順序に任意の値を設定†

注意点†

データベース名やテーブル名は大文字小文字を区別する。†

GUI管理ツール†

phpMyAdmin†

インポート、エクスポート†

CSVインポート†

バックアップ&リカバリー†

テーブル単位†

データベース単位†

バックアップ†

リカバリー†

全部のデータベース†

バックアップ†

リカバリ†

トラブルシューティング†

パスワードを忘れて再設定したい†

FAQ†

パスワード忘れた†

phpMyAdmin †