MySQL
2007/12/23更新
対応バージョン: 5.0.51
MySQLのユーザ(アカウント)はCREATE USERやGRANTにて行うのが一般的である。
ここでは例としてデータベースにアクセスする権限によって3種類のユーザを作成する。
super
localhost及び任意のホスト(%)からでもサーバに接続できる完全なスーパーユーザ。
パスワードあり
接続元となるホスト(localhost, %)毎に複数のユーザを作成する必要あり
mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'localhost'
-> IDENTIFIED BY '<パスワード>' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'super'@'%'
-> IDENTIFIED BY '<パスワード>' WITH GRANT OPTION;
mysql> SELECT user,password,host FROM user WHERE user = 'super';
+-------+-------------------------------------------+-----------+
| user | password | host |
+-------+-------------------------------------------+-----------+
| super | *0D8825DC371A083787E8919946F4FFDECE2F4DDF | localhost |
| super | *80E73635CAEDFEE27398D5CBCBAB02EB88BD3C9B | % |
+-------+-------------------------------------------+-----------+
admin
localhostからのみ接続できる管理者ユーザ。
パスワードあり
管理権限は「RELOAD」と「PROCESS」
この権限はmysqladmin reload、mysqladmin refresh、mysqladmin flush-xxx、mysqladmin processlistといった管理系のコマンドを実行できる。
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost'
-> IDENTIFIED BY '<パスワード>' WITH GRANT OPTION;
mysql> SELECT user,password,host FROM user WHERE user = 'admin';
+-------+-------------------------------------------+-----------+
| user | password | host |
+-------+-------------------------------------------+-----------+
| admin | *C20003C6C9FA015E80D75D36F54C4450D98F7828 | localhost |
+-------+-------------------------------------------+-----------+
guest
localhostからのみ接続できるゲストユーザ。
パスワードなし
管理権限は「USAGE」
この権限を設定すると全てのグローバル権限が「N」(権限なし)になる。
mysql> GRANT USAGE ON *.* TO 'guest'@'localhost'; mysql> SELECT user,password,host FROM user WHERE user = 'guest'; +-------+----------+-----------+ | user | password | host | +-------+----------+-----------+ | guest | | localhost | +-------+----------+-----------+
2007/12/23更新
対応バージョン: 5.0.51
SELECTなどでデータを参照する場合通常はカラムが横に並ぶが、-Eオプション付でmysqlコマンドを実行することによって1行につき1カラムを表示させることができる。
通常
# mysql test -u root -e "select * from pet;" +----------+-------+------+------+------------+ | name | owner | type | sex | birth | +----------+-------+------+------+------------+ | Whistler | Bob | bird | NULL | 2006-03-09 | | Lucker | Alice | dog | f | 2003-03-30 | +----------+-------+------+------+------------+
-Eオプション付
# mysql test -u root -E -e "select * from pet;" *************************** 1. row *************************** name: Whistler owner: Bob type: bird sex: NULL birth: 2006-03-09 *************************** 2. row *************************** name: Lucker owner: Alice type: dog sex: f birth: 2003-03-30
2007/12/19更新
対応バージョン: 5.0.51
テーブルの作成にはCREATE TABLE文を使う。
ここでは例として空のデータベースにペットに関するテーブルを作成する。
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> type VARCHAR(20), sex CHAR(1), birth DATE);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| pet |
+----------------+
mysql> DESCRIBE pet;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| type | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
続けて外部ファイルからデータを取り込んでみる。
まず/tmp/pet.txtというファイルを以下の内容で作成する。区切り文字はタブにし、NULLを挿入したいカラムは「\N」と記述する。
Whistler Bob bird \N 2007-03-09
次にこのファイルをLOAD DATA文で読み込む。
mysql> LOAD DATA INFILE "/tmp/pet.txt" INTO TABLE pet; Query OK, 1 row affected (0.00 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
読み込まれたかどうか確認する。
mysql> SELECT * FROM pet; +----------+-------+------+------+------------+ | name | owner | type | sex | birth | +----------+-------+------+------+------------+ | Whistler | Bob | bird | NULL | 2007-03-09 | +----------+-------+------+------+------------+
今度はSQL文でデータをインサートしてみる。
mysql> INSERT INTO pet
-> VALUES ('Lucker','Alice','dog','f','2003-03-30');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM pet;
+----------+-------+------+------+------------+
| name | owner | type | sex | birth |
+----------+-------+------+------+------------+
| Whistler | Bob | bird | NULL | 2006-03-09 |
| Lucker | Alice | dog | f | 2003-03-30 |
+----------+-------+------+------+------------+
2007/12/19更新
対応バージョン: 5.0.51
リストアはその目的に応じていろいろな方法があるが、ここでは代表的なものを示す。
テーブルのリストア
ある特定のテーブルについて、前回バックアップ取得時点までのリストアを行う場合はまずそのテーブルを再作成した後に全データをロードすればよい。
# mysql <DB> < <テーブル定義ファイル> # mysql <DB> -e "LOAD DATA INFILE '<データファイル>' INTO TABLE <対象テーブル>;"
例) test.petテーブルをリストアする
# mysql test < pet.sql # mysql test -e "LOAD DATA INFILE '/tmp/pet.txt' INTO TABLE pet;"
データベース全体のリストア
サーバダウンからの復旧
カーネルクラッシュやデータベースサーバの強制終了のような場合、データベースサーバを再起動すれば自動的にデータベースが復旧する可能性が高い。
(ログファイル)
071218 19:20:05 mysqld started 071218 19:20:05 InnoDB: Started; log sequence number 0 43655 071218 19:20:05 [Note] Recovering after a crash using mysql-bin 071218 19:20:05 [Note] Starting crash recovery... 071218 19:20:05 [Note] Crash recovery finished. 071218 19:20:05 [Note] /usr/local/mysql/libexec/mysqld: ready for connections. Version: '5.0.51-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution
(*) ストレージエンジンはInnoDBとする。
自動的に復旧しない場合、次項のバックアップファイルからのリストアが必要となる。
ファイルシステムクラッシュからのリストア
ハードウェア障害等でファイルシステムがクラッシュしたような場合は自動的に復旧しないと考えられるのでバックアップファイルからのリストアが必要となる。
# mysql < <バックアップファイル>
さらにこのバックアップファイル以降のデータはインクリメンタルバックアップとしてログファイルに記録されているので、続けて以下の操作を行って直近の整合性が取れている状態までリストアする。
尚、この例ではバックアップファイル以降のインクリメンタルバックアップは以下の2ファイルとする。
# cd /usr/local/mysql/var # mysqlbinlog mysql-bin.000007 mysql-bin.000008 | mysql
関連資料・記事
2014/11/01更新
対応バージョン: 5.1.73
MySQLのレプリケーションが何らかの原因で同期されなくなった場合の復旧方法を示す。
基本的には以下の資料にあるように最初からレプリケーションを行うのと同じ流れになる。
関連資料・記事
状態確認
まずスレーブの状態を確認する。
この例のように2つのRunningがともにYesでない場合、レプリケーションに失敗している。
mysql> show slave status\G : Slave_IO_Running: Yes Slave_SQL_Running: No :
復旧
復旧方法はまずスレーブ側でスレーブ機能を停止し、マスタのDBデータをexportしてそれを再度importすればよい。
スレーブ
mysql> stop slave; mysql> show slave status\G : Slave_IO_Running: No Slave_SQL_Running: No :
マスタ
スレーブ側で指定する情報を確認した上でDBデータをファイルに出力する。
mysql> show master status\G : File: mysql-bin.000007 Position: 626148 : mysql> flush tables with read lock; mysql> exit Bye # mysqldump -u root -p --all-databases --lock-all-tables --events > /var/tmp/db.dump
このファイルをスレーブ側にコピーしてインポートし、レプリケーションを開始する。
スレーブ
# mysql -u root -p < /var/tmp/db.dump # mysql -u root -p mysql> change master to master_host='xxx.xxx.xxx.xxx', <-------- マスタのIPアドレス master_user='xxxxx', <------------------ レプリケーション用に作成したアカウント master_password='********', <----------- 上記アカウントのパスワード master_log_file='mysql-bin.xxxxxx', <--- マスタ側show master statusのFile master_log_pos=xxxxx; <----------------- マスタ側show master statusのPosition mysql> slave start; mysql> show slave status\G : Master_Log_File: mysql-bin.000007 : Read_Master_Log_Pos: 626148 : Slave_IO_Running: Yes Slave_SQL_Running: Yes :
ログファイル名とログポジションがマスタと同じでRunningが両方Yesになっていればレプリケーションは正常に動作している。
2014/8/21更新
対応バージョン: 5.1.73
2台のMySQLサーバでレプリケーションを構成する手順を示す。
マスタ/スレーブで同じ作業を実施するものと別々の作業を実施するものがあるので注意すること。
レプリケーション用ポート開け(マスタ/スレーブとも)
レプリケーション用に3306/tcpポートを開ける。
# cd /etc/sysconfig # cp -p iptables iptables.YYYYMMDD # vi iptables 以下の行を追加 # MySQL Replication -A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
設定を有効にするためにiptablesを再起動する。
# service iptables restart
my.cnf設定変更(マスタ側)
マスタを識別するための一意のIDを割り当てる。IDは任意の数字でよい。
# cd /etc # cp -p my.cnf my.cnf.YYYYMMDD # vi my.cnf (以下の設定を追加) [mysqld] log-bin=mysql-bin <--- 固定 server-id=121 <------- マスタ識別ID
設定を有効にするためにmysqldを再起動する。
# service mysqld restart
スレーブ側から接続する為のアカウントを作成する。
# mysql -u root -p mysql> grant replication slave on *.* to repl identified by '<パスワード>';
初期データ移行
マスタのデータをスレーブ側に一旦持っていく。
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ mysql> flush tables with read lock; mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 471 | | | +------------------+----------+--------------+------------------+
ここで表示されたFileとPositionはスレーブ側の設定で使用するのでメモしておく。
いったん別端末を起動してデータをダンプし、スレーブにコピーする。
# mysqldump -u root -p --all-databases --lock-all-tables --events > /var/tmp/dbdump.db # scp /var/tmp/db.dump root@<スレーブ側IPアドレス>:/var/tmp
コピーが終わったらロックを解除する。
mysql> unlock tables;
my.cnf設定変更(スレーブ側)
スレーブを識別するための一意のIDを割り当てる。IDは任意の数字でよい。
# cd /etc # cp -p my.cnf my.cnf.YYYYMMDD # vi my.cnf (以下の設定を追加) [mysqld] server-id=122 <------- スレーブ識別ID
設定を有効にするためにmysqldを再起動する。
# service mysqld restart
先ほどマスタで作成したダンプデータを取り込む。
# mysql -u root -p < /var/tmp/db.dump # mysql -u root -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+
マスタ情報を登録登録する(マスタ側のshow master statusの結果を使用)
mysql> change master to master_host='xxx.xxx.xxx.xxx', <-------- マスタのIPアドレス master_user='repl', <------------------- レプリケーション用に作成したアカウント master_password='********', <----------- 上記アカウントのパスワード master_log_file='mysql-bin.000001', <--- マスタ側show master statusのFile master_log_pos=471; <------------------- マスタ側show master statusのPosition
スレーブを開始する。
mysql> start slave; mysql> quit
テスト
マスタ側で作成したDBがスレーブ側にコピーされていることを確認する。
マスタ側
mysql> create database repltest; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | repltest | | test | +--------------------+
スレーブ側
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| repltest <---------|-- 作成されている
| test |
+--------------------+
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.121
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 722
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 431
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 722
Relay_Log_Space: 587
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
2009/2/6更新
対応バージョン: 5.1.30
MySQLのrootのパスワードを忘れてしまった場合などに初期化する手順を示す。
ここではFedora 10にインストールしてあるMySQLの場合を示す。
サーバ停止
% sudo service mysql.server stop
--skip-grant-tablesオプション付でサーバ起動
これによりユーザ権限のチェックをスキップした状態でサーバが起動する。
% sudo /usr/local/mysql/bin/mysqld_safe -u root --skip-grant-tables &
パスワード初期化
サーバに接続し、rootのパスワードを初期化する。
% mysql mysql mysql> UPDATE user SET Password='' WHERE Host='localhost' and User='root'; mysql> FLUSH PRIVILEGES;
設定後、内容を反映するため「FLUSH PRIVILEGES」を実行する。
この段階で以下のようにしてパスワードを設定してもよい。
mysql> UPDATE user SET Password=password('pwd123') WHERE Host='localhost' and User='root';
サーバ再起動
% sudo service mysql.server restart
サーバ接続
これでパスワードなしでサーバに接続できるようになる。上記でパスワードを設定した場合はパスワードを指定して接続する。
(パスワードを設定していない場合)
% mysql mysql -u root mysql>
(既にパスワードを設定している場合)
% mysql mysql -u root -p Enter password: <現在のパスワード> mysql>
パスワード再設定
新たにパスワードを設定する場合はmysqladminが使用できる。
(パスワードを設定していない場合)
% mysqladmin password -u root <新しいパスワード>
(既にパスワードを設定している場合)
% mysqladmin password -u root -p <新しいパスワード> Enter password: <現在のパスワード>
2007/12/19更新
対応バージョン: 5.0.51
当該サーバで定義されている全てのオブジェクトを表示するには以下のように3種類のINFORMATION_SCHEMAをUNION ALLで結合すればよい。
mysql> SELECT
-> TABLE_SCHEMA AS object_schema
-> ,TABLE_NAME AS object_name
-> ,TABLE_TYPE AS object_type
-> FROM INFORMATION_SCHEMA.TABLES
->
-> UNION ALL
->
-> SELECT
-> ROUTINE_SCHEMA
-> ,ROUTINE_NAME
-> ,ROUTINE_TYPE
-> FROM INFORMATION_SCHEMA.ROUTINES
->
-> UNION ALL
->
-> SELECT
-> TRIGGER_SCHEMA
-> ,TRIGGER_NAME
-> ,'TRIGGER'
-> FROM INFORMATION_SCHEMA.TRIGGERS
->
-> ORDER BY object_schema, object_type, object_name;
+------------------+-------------------------------------+-----------+
|object_schema |object_name |object_type|
+------------------+-------------------------------------+-----------+
|information_schema|CHARACTER_SETS |SYSTEM VIEW|
|information_schema|COLLATIONS |SYSTEM VIEW|
|information_schema|COLLATION_CHARACTER_SET_APPLICABILITY|SYSTEM VIEW|
|information_schema|COLUMNS |SYSTEM VIEW|
|information_schema|COLUMN_PRIVILEGES |SYSTEM VIEW|
|information_schema|KEY_COLUMN_USAGE |SYSTEM VIEW|
|information_schema|PROFILING |SYSTEM VIEW|
|information_schema|ROUTINES |SYSTEM VIEW|
|information_schema|SCHEMATA |SYSTEM VIEW|
|information_schema|SCHEMA_PRIVILEGES |SYSTEM VIEW|
|information_schema|STATISTICS |SYSTEM VIEW|
|information_schema|TABLES |SYSTEM VIEW|
|information_schema|TABLE_CONSTRAINTS |SYSTEM VIEW|
|information_schema|TABLE_PRIVILEGES |SYSTEM VIEW|
|information_schema|TRIGGERS |SYSTEM VIEW|
|information_schema|USER_PRIVILEGES |SYSTEM VIEW|
|information_schema|VIEWS |SYSTEM VIEW|
|mysql |columns_priv |BASE TABLE |
|mysql |db |BASE TABLE |
|mysql |func |BASE TABLE |
|mysql |help_category |BASE TABLE |
|mysql |help_keyword |BASE TABLE |
|mysql |help_relation |BASE TABLE |
|mysql |help_topic |BASE TABLE |
|mysql |host |BASE TABLE |
|mysql |proc |BASE TABLE |
|mysql |procs_priv |BASE TABLE |
|mysql |tables_priv |BASE TABLE |
|mysql |time_zone |BASE TABLE |
|mysql |time_zone_leap_second |BASE TABLE |
|mysql |time_zone_name |BASE TABLE |
|mysql |time_zone_transition |BASE TABLE |
|mysql |time_zone_transition_type |BASE TABLE |
|mysql |user |BASE TABLE |
|test |pet |BASE TABLE |
|test |test_proc |PROCEDURE |
|test |test_trigger |TRIGGER |
|test |test_view |VIEW |
+------------------+-------------------------------------+-----------+
関連資料・記事
2007/12/19更新
対応バージョン: 5.0.51
INFORMATION_SCHEMA(情報スキーマ)を利用するとデータベースメタデータへのアクセスが可能になる。
INFORMATION_SCHEMAはSQL:2003(ISO/IEC 9075:2003)で定められているシステムカタログへの標準インタフェースで、他のDBMSでも実装されつつある。
MySQLではこの標準に加えて独自の拡張項目があるが、ここでは触れない。
例えばすべてのデータベースのすべてのユーザテーブルを表示するには以下のクエリを実行する。
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_TYPE = "BASE TABLE"
-> ORDER BY TABLE_SCHEMA, TABLE_NAME;
+--------------+---------------------------+------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS |
+--------------+---------------------------+------------+
| mysql | columns_priv | 0 |
| mysql | db | 2 |
| mysql | func | 0 |
| mysql | help_category | 0 |
| mysql | help_keyword | 0 |
| mysql | help_relation | 0 |
| mysql | help_topic | 0 |
| mysql | host | 0 |
| mysql | proc | 0 |
| mysql | procs_priv | 0 |
| mysql | tables_priv | 0 |
| mysql | time_zone | 0 |
| mysql | time_zone_leap_second | 0 |
| mysql | time_zone_name | 0 |
| mysql | time_zone_transition | 0 |
| mysql | time_zone_transition_type | 0 |
| mysql | user | 5 |
| test | pet | 2 |
+--------------+---------------------------+------------+
TABLE_TYPEには以下の種類がある。
BASE TABLE
ユーザテーブル
VIEW
ビュー
SYSTEM VIEW
システムビュー
PROCEDURE
ストアドプロシージャ
FUNCTION
ファンクション
TRIGGER
トリガ
その他、主な情報スキーマを以下に示す。
INFORMATION_SCHEMA.SCHEMATA
データベース情報
例)
mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
-> FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+----------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |
+--------------------+----------------------------+
| information_schema | utf8 |
| mysql | latin1 |
| test | latin1 |
+--------------------+----------------------------+
データベースの一覧は以下のようにしても参照できる。
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+
INFORMATION_SCHEMA.COLUMNS
カラム情報
例)
mysql> SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
-> COLUMN_KEY, COLUMN_DEFAULT, EXTRA
-> FROM INFORMATION_SCHEMA.COLUMNS
-> WHERE TABLE_NAME = 'pet';
+-----------+-----------+-----------+----------+--------------+-----+
|COLUMN_NAME|COLUMN_TYPE|IS_NULLABLE|COLUMN_KEY|COLUMN_DEFAULT|EXTRA|
+-----------+-----------+-----------+----------+--------------+-----+
|name |varchar(20)|YES | |NULL | |
|owner |varchar(20)|YES | |NULL | |
|type |varchar(20)|YES | |NULL | |
|sex |char(1) |YES | |NULL | |
|birth |date |YES | |NULL | |
+-----------+-----------+-----------+----------+--------------+-----+
これは以下と同義である。
mysql> SHOW COLUMNS FROM pet; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | owner | varchar(20) | YES | | NULL | | | type | varchar(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
INFORMATION_SCHEMA.USER_PRIVILEGES
ユーザ権限
例)
mysql> SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE
-> FROM INFORMATION_SCHEMA.USER_PRIVILEGES;
+--------------------+----------------+--------------+
| GRANTEE | PRIVILEGE_TYPE | IS_GRANTABLE |
+--------------------+----------------+--------------+
| 'root'@'localhost' | SELECT | YES |
| 'root'@'localhost' | INSERT | YES |
| 'root'@'localhost' | UPDATE | YES |
| 'root'@'localhost' | DELETE | YES |
| 'root'@'localhost' | CREATE | YES |
| 'root'@'localhost' | DROP | YES |
:
INFORMATION_SCHEMA.VIEWS
ビュー定義
例)
mysql> SELECT TABLE_SCHEMA, TABLE_NAME
-> FROM INFORMATION_SCHEMA.VIEWS;
+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME |
+--------------+------------+
| test | test_view |
+--------------+------------+
INFORMATION_SCHEMA.ROUTINES
ストアドプロシージャ/ファンクション定義
例)
mysql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME
-> FROM INFORMATION_SCHEMA.ROUTINES;
+----------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME |
+----------------+--------------+
| test | test_proc |
+----------------+--------------+
INFORMATION_SCHEMA.TRIGGERS
トリガ定義
例)
mysql> SELECT TRIGGER_SCHEMA, TRIGGER_NAME
-> FROM INFORMATION_SCHEMA.TRIGGERS;
+----------------+--------------+
| TRIGGER_SCHEMA | TRIGGER_NAME |
+----------------+--------------+
| test | test_trigger |
+----------------+--------------+
INFORMATION_SCHEMA.CHARACTER_SETS
サポートされている文字コードセット
例)
mysql> SELECT * FROM INFORMATION_SCHEMA.CHARACTER_SETS; +------------------+--------------------+---------------------+------+ |CHARACTER_SET_NAME|DEFAULT_COLLATE_NAME|DESCRIPTION |MAXLEN| +------------------+--------------------+---------------------+------+ : |latin1 |latin1_swedish_ci |cp1252 West European | 1| : |ascii |ascii_general_ci |US ASCII | 1| : |utf8 |utf8_general_ci |UTF-8 Unicode | 3| |binary |binary |Binary pseudo charset| 1| :
2007/12/19更新
対応バージョン: 5.0.51
トリガを定義する場合などのようにクエリ内で「;」を使用したい場合、そのままだとクエリの終端文字(デリミタ)として扱われてしまうのでこれを無視させたい場合はdelimiterコマンドを使用してデリミタを別の文字に変更する。
例)
mysql> delimiter // ←デリミタを「//」に変更する
mysql> CREATE TRIGGER test BEFORE INSERT ON sample
-> FOR EACH ROW
-> BEGIN
-> IF NEW.type = 'dog' THEN
-> SET NEW.type = '犬';
-> ELSE
-> SET NEW.type = 'その他';
-> END IF;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; ←デリミタを元に戻す
2007/12/19更新
対応バージョン: 5.0.51
MySQLの管理コマンドには以下のようなものがある。
mysqladmin
MySQLの管理全般を行う。
サブコマンドを指定することにより様々な管理作業が行える。
以下、サブコマンドの一例を示す。
variables
サーバのインストール状況を表示する。
# mysqladmin variables +--------------------------+-------------------------+ | Variable_name | Value | +--------------------------+-------------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/local/mysql/ | : | version | 5.0.51-log | | version_comment | Source distribution | | version_compile_machine | i686 | | version_compile_os | redhat-linux-gnu | | wait_timeout | 28800 | +--------------------------+-------------------------+
password
MySQL内のユーザにパスワードを設定する。
# mysqladmin password -u <ユーザ> <パスワード>
mysqlshow
DB/テーブルの一覧や内容を表示する。
DBの一覧を表示する
# cd /usr/local/mysql/var # mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | mysql | | test | +--------------------+
特定のDBのテーブル一覧を表示する
# mysqlshow mysql Database: mysql +---------------------------+ | Tables | +---------------------------+ | columns_priv | | db | | func | | help_category | | help_keyword | | help_relation | | help_topic | | host | | proc | | procs_priv | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+
mysql
データベース内のデータにアクセスする。
SQLの指定はいったんDBに接続して専用プロンプトにて行うか、-eオプションを使ってコマンドラインからそのまま記述する。
# mysql mysql mysql> SELECT Host,Db,User FROM db; +------+---------+------+ | Host | Db | User | +------+---------+------+ | % | test | | | % | test\_% | | +------+---------+------+
上記と同じ結果が以下のようにしても得られる。
# mysql -e "SELECT Host,Db,User FROM db" mysql
その他、一般のDBMSと同じように様々な情報にアクセスできる。
以下、一例を示す。
データベース一覧を表示
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+
使用するデータベースを切り替える
mysql> USE test Database changed
現在のデータベースを表示
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | test | +------------+
テーブル一覧を表示
mysql> SHOW TABLES; +-----------------+ | Tables_in_mysql | +-----------------+ | foo | | bar | | com | +-----------------+
システム変数の内容を表示
mysql> SELECT VERSION(), CURRENT_DATE(), NOW(); +------------+----------------+---------------------+ | VERSION() | CURRENT_DATE() | NOW() | +------------+----------------+---------------------+ | 5.0.51-log | 2007-12-13 | 2007-12-13 16:57:08 | +------------+----------------+---------------------+
計算機として利用
mysql> SELECT SIN(PI()/4), (4+1)*5; +------------------+---------+ | SIN(PI()/4) | (4+1)*5 | +------------------+---------+ | 0.70710678118655 | 25 | +------------------+---------+
2007/12/19更新
対応バージョン: 5.0.51
/etc/my.cnfに以下の設定を追加してサーバを再起動する。
サーバ側
[mysqld] : default-character-set = <キャラクターセット>
クライアント側
[mysql] : default-character-set = <キャラクターセット>
指定できるキャラクターセットは以下のようにして確認できる。
mysql> SHOW CHARSET; +--------+---------------------------+-------------------+------+ |Charset |Description |Default collation |Maxlen| +--------+---------------------------+-------------------+------+ |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| |hebrew |ISO 8859-8 Hebrew |hebrew_general_ci | 1| |koi8u |KOI8-U Ukrainian |koi8u_general_ci | 1| |greek |ISO 8859-7 Greek |greek_general_ci | 1| |cp1250 |Windows Central European |cp1250_general_ci | 1| |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| |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| +--------+---------------------------+-------------------+------+
現在のキャラクターセットは以下のようにして確認できる。
mysql> status; : (サーバ側のキャラクターセット) Server characterset: utf8 Db characterset: utf8 (クライアント側のキャラクターセット) Client characterset: latin1 Conn. characterset: latin1 :
2007/12/23更新
対応バージョン: 5.0.51
バイナリログは、ローテーションして使われる複数のログファイルとそれを管理するインデックスファイルとで構成される。
ファイルは/usr/local/mysql/var配下に格納され、それぞれ以下のようなファイル名で管理される。
ログファイル(複数)
データベースに対して実施した作業がタイムスタンプとともに格納されている。
後述のmysqlbinlogコマンドで内容を参照することが可能。
mysql-bin.000001
mysql-bin.000002
:
インデックスファイル
ログファイルの名前が1行ずつ記述されている。
mysql-bin.index
バイナリログの使用状況は以下のクエリによって確認できる。
# mysql -u root -E -e "SHOW BINLOG EVENTS"
*************************** 1. row ***************************
Log_name: mysql-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 98
Info: Server ver: 5.0.51-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql-bin.000001
Pos: 98
Event_type: Query
Server_id: 1
End_log_pos: 1712
Info: use `mysql`; CREATE TABLE IF NOT EXISTS db (Host char(60) ...(略)
:
*************************** 33. row ***************************
Log_name: mysql-bin.000001
Pos: 14838
Event_type: Query
Server_id: 1
End_log_pos: 14921
Info: use `mysql`; DROP TABLE tmp_user
*************************** 34. row ***************************
Log_name: mysql-bin.000001
Pos: 14921
Event_type: Stop
Server_id: 1
End_log_pos: 14940
Info:
バイナリログの個別ファイル内の詳細情報はmysqlbinlogにて確認できる。
# mysqlbinlog mysql-bin.000001 | cat -n
1 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
3 DELIMITER /*!*/;
4 # at 4
5 #071223 0:53:01 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.51-log created 071223 0:53:01 at startup
6 ROLLBACK/*!*/;
7 # at 98
8 #071223 0:53:01 server id 1 end_log_pos 1712 Query thread_id=1 exec_time=0 error_code=0
9 use mysql/*!*/;
10 SET TIMESTAMP=1198338781/*!*/;
11 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
12 SET @@session.sql_mode=0/*!*/;
13 /*!\C utf8 *//*!*/;
14 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
15 CREATE TABLE IF NOT EXISTS db (Host char(60) binary DEFAULT '' ...
:
このようにバイナリログではデータベースに加えられた作業を全て位置情報(pos)及びタイムスタンプとともに記録しているので、前回バックアップ時以降のロールフォワードや任意の時点への復旧などが簡単に行える。
2014/08/31更新
対応バージョン: 5.1.73
mysqldumpで全DBのバックアップを取得すると以下のような警告が出る。
% mysqldump -u root -p --all-databases --lock-all-tables > /var/tmp/dbdump.db -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
これはMySQLのバージョン5.1.8からmysqlデータベースにeventテーブルが追加され、これがデフォルトではバックアップされない設定になっているからで、eventテーブルもバックアップしたい場合は--eventsオプションを付ければよい。
% mysqldump -u root -p --all-databases --lock-all-tables --events > /var/tmp/dbdump.db
2007/12/23更新
対応バージョン: 5.0.51
# mysqldump --tab=/tmp/bkup test mysqldump: Got error: 1: Can't create/write to file '/tmp/bkup/foo.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
foo.txtはMySQL管理者アカウント(デフォルト:mysql)の権限で作成されるので、当該ディレクトリはそのアカウントで書き込みができる必要がある。
関連資料・記事
2007/12/19更新
対応バージョン: 5.0.51
バックアップ運用を行うにあたりデータベースの総量が増えてくると毎回フルバックアップを取得するのに時間もかかりディスク容量も必要とする。
そのような場合、前回バックアップ以降はインクリメンタルバックアップを使用する方法がある。
インクリメンタルバックアップのログファイルは自動的に取得され、/usr/local/mysql/var配下に以下のようなファイル名で作成される。
-rw-rw---- 1 mysql mysql 14940 2007-12-13 12:43 mysql-bin.000001 -rw-rw---- 1 mysql mysql 117 2007-12-13 12:43 mysql-bin.000002 -rw-rw---- 1 mysql mysql 255 2007-12-13 15:22 mysql-bin.000003 -rw-rw---- 1 mysql mysql 117 2007-12-13 15:37 mysql-bin.000004 -rw-rw---- 1 mysql mysql 3995 2007-12-18 19:03 mysql-bin.000005 -rw-rw---- 1 mysql mysql 325 2007-12-18 19:09 mysql-bin.000006 -rw-rw---- 1 mysql mysql 268 2007-12-18 19:12 mysql-bin.000007 -rw-rw---- 1 mysql mysql 22655 2007-12-18 19:35 mysql-bin.000008 -rw-rw---- 1 mysql mysql 152 2007-12-18 19:20 mysql-bin.index
最後のmysql-bin.indexはテキストファイルで、ログファイルの一覧(mysql-bin.000001〜000008)が記載されている。
このログファイルはMySQLサーバ起動時に1ずつインクリメントされ、前回のログファイルは使われなくなる。
使われなくなったログファイルはそのままではディスクを消費するだけなので、例えばフルバックを取得する際に--delete-master-logsオプションを付けることによって不要なファイルが削除される(ただしレプリケーション環境において当該サーバがマスタの場合、--delete-master-logsを使用するとスレーブとの同期が取れなくなる恐れがあるのでPURGE MASTER LOGSを使用する)。
# mysqldump --single-transaction --flush-logs --master-data \ --all-databases --delete-master-logs > backup.sql
尚、--flush-logsオプションはログファイルの切り替え指示、--master-dataオプションは以下のようにログファイル名をバックアップファイル中に出力する指示である。
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=98;
バックアップを伴わずにログファイルだけを切り替える場合は以下のようにする。
# mysqladmin flush-logs
関連資料・記事
2007/12/19更新
対応バージョン: 5.0.51
静的バックアップには以下の2通りの方法があるので目的に応じてどちらかを選ぶ。
mysqldump
mysqldumpはテーブルの定義とデータをテキストファイルで出力する。
# mysqldump --tab=<バックアップ先ディレクトリ> <DB名>
これによりバックアップ先ディレクトリにテーブル毎に以下の2ファイルが作成される。
<テーブル>.sql
テーブル定義(CREATE TABLE文)
<テーブル>.txt
データ(テキスト形式)
全てのデータベースのフルダンプを行う場合は以下のようにする。
# mysqldump --single-transaction --all-databases > backup.sql
(*) --single-transactionオプションによりmysqldumpで使用したデータは変更しない。
(*) ストレージエンジンはInnoDBとする。
mysqlhotcopy
mysqlhotcopyは/usr/local/mysql/var/
# mysqlhotcopy <DB名> <バックアップ先ディレクトリ>
これによりバックアップ先ディレクトリにDB名のディレクトリが作成され、配下に以下の3ファイルがテーブル毎に作成される。
<テーブル>.MYD
データ
<テーブル>.MYI
インデックス
<テーブル>.frm
テーブル定義
尚、このツールはPerlのDBIモジュールを使用するので同モジュールがあらかじめインストールされている必要がある。
関連資料・記事
2009/2/1更新
対応バージョン: 5.1.30
公式サイト
準備
導入OS
Fedora 10
管理用アカウント作成
% sudo groupadd -r mysql % sudo useradd -r -g mysql -d /usr/local/mysql -s /sbin/nologin mysql
環境変数設定(root、及び利用者毎)
<既存の設定に追加>PATH
/usr/local/mysql/bin
インストール
配布ファイル展開
% tar zxvf mysql-5.1.30.tar.gz % cd mysql-5.1.30
make,インストール
% ./configure \ --prefix=/usr/local/mysql \ --with-charset=utf8 \ --with-extra-charsets=all \ --with-mysqld-user=mysql % make % sudo make install
インストール物 (man,infoは除く)
/usr/local/mysql/*/*
各種設定
設定ファイル配置
用途に応じて数種類の雛形が用意されているので適切なファイルをコピーして使用する。
my-small.cnf
〜64M未満メモリ搭載システム用
my-medium.cnf
〜128Mメモリ搭載システム用
my-large.cnf
〜512Mメモリ搭載システム用
my-huge.cnf
1〜2Gメモリ搭載システム用
my-innodb-heavy-4G.cnf
4Gメモリ搭載でInnoDBを使用するシステム用
% sudo cp support-files/my-medium.cnf /etc/my.cnf
my-medium.cnf、my-large.cnf、my-huge.cnfを使用する場合は以下の行をコメントアウトする。
% sudo vi /etc/my.cnf : # skip-federated :
initスクリプト配置
% sudo cp support-files/mysql.server /etc/rc.d/init.d % sudo chmod +x /etc/rc.d/init.d/mysql.server % sudo chkconfig --add mysql.server % sudo chkconfig --list mysql.server mysql.server 0:off 1:off 2:on 3:on 4:on 5:on 6:off
システムデータベース作成
% sudo /usr/local/mysql/bin/mysql_install_db --user=mysql
この作業により以下のファイルが作成される。
システムデータベース
/usr/local/mysql/var/mysql/*
バイナリログ(2個)
/usr/local/mysql/var/mysql-bin.000001
/usr/local/mysql/var/mysql-bin.000002
バイナリログ管理ファイル
/usr/local/mysql/var/mysql-bin.index
(*) このファイルにログファイルのファイル名が格納される。
testデータベース用ディレクトリ
/usr/local/mysql/var/test (ディレクトリのみ)
動作確認
デーモン起動
% sudo service mysql.server start
デーモンを起動すると以下のファイルが作成される。
デーモンPIDファイル
/usr/local/mysql/var/<ローカルホスト名>.pid
デーモンログファイル
/usr/local/mysql/var/<ローカルホスト名>.err
バイナリログ(3個目)
/usr/local/mysql/var/mysql-bin.000003
(*) 最初の2個はシステムデータベース作成時に作られる。
(*) ログ管理ファイル/usr/local/mysql/var/mysql-bin.indexに上記ファイルが追加される。
MySQL管理者アカウントのパスワード設定
必要に応じてMySQL管理者アカウントのパスワードを設定する。
ここでのアカウントはOSのアカウントではなくMySQL内でのアカウントを指す。
% mysqladmin password -u root <パスワード>
関連資料・記事