操作Tips - Knowlege Database

MySQL 操作Tips†

MySQL 操作Tips

データベースの作成†

mysql> create database [DATABASE NAME];

↑

データベースの削除†

mysql> drop database [DATABASE NAME];

↑

ユーザ作成（接続元無制限）†

mysql> GRANT ALL PRIVILEGES ON [DATABASE].* TO [USER]@'%'  IDENTIFIED BY '[PASSWORD]';

↑

ユーザ削除†

mysql> DELETE FROM mysql.user WHERE user='[USER]' and host='[HOST]';
mysql> FLUSH PRIVILEGES;

↑

パスワード変更†

mysql> SET PASSWORD FOR [USER]@[HOST]=PASSWORD('[PASSWORD]');

↑

grantコマンドの引数†

Ggrant all privileges on [DATABASE].* to [USER]@[HOST] identified by '[PASSWORD]';

↑

rootユーザにフルアクセス権限を付与する†

mysql> GRANT all privileges ON *.* TO root with grant option;

↑

databaseを表示†

mysql> show databases;

↑

ユーザ・ホスト一覧を表示†

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Host,User from user;

↑

テーブルの中身を表示†

全件を表示
```
select * from [TABLE NAME];
```

末尾の1行のみを表示
```
select * from [TABLE NAME] limit 1;
```

↑

テーブル内のレコード件数を表示†

select count(*) from [TABLE NAME]

↑

テーブルの中身を消去†

truncate table [TABLE NAME];

↑

テーブルの破棄†

mysql> drop table [TABLE NAME];

テーブルをまとめて破棄する場合は下記の様なリストファイルを作成し、リダイレクトでリストを読み込んで一括削除する。

リスト(droptable.sql)

drop table [TABLE 1];
drop table [TABLE 2];
drop table [TABLE 3];
drop table [TABLE 4];

実行

mysql -u [USER NAME] -p -h [HOST NAME] [DATABASE NAME] < droptable.sql

↑

データベースのダンプ作成（特定のTable）†

mysqldump -u [USER] --password=[PASSWORD] [DATABASE NAME] [TABLE NAME] [TABLE NAME] ... > [出力先ファイル名]

↑

データベースのダンプ作成（特定のDB）†

mysqldump -u [USER] --password=[PASSWORD] [DATABASE NAME] > [出力先ファイル名]

↑

データベースのダンプ作成（全てのDB）†

mysqldump -u [USER] --password=[PASSWORD] --all-databases > [出力先ファイル名]

↑

ダンプファイルのリストア†

mysql -u[USER] -p[PASSWORD] [DATABASE] < ダンプしたファイル

もしくは

cat ダンプしたファイル | mysql -u[USER] -p[PASSWORD] [DATABASE]

圧縮(gzip)ファイルからリストアする場合

zcat ダンプしたファイル.gz | mysql -u[USER] -p[PASSWORD] [DATABASE]

↑

複数のダンプファイルを一括リストア†

複数のダンプファイルをまとめてリストアする場合は、ダンプファイルの保管ディレクトリに移動後に下記を実行する。

cat *.dmp | mysql -u [USER NAME] -p -h [HOST NAME] [DATABASE]

↑

文字コードの確認†

キャラセット関連のシステム変数を確認するには、mysqlクライアントコマンドを起動し、キャラセットの環境変数を表示させる。

$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.26

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

mysql>

キャラセットの環境変数は下記に表示させる

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

下記の様な表示結果となる

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | utf8   | 
| character_set_connection | utf8   | 
| character_set_database   | utf8   | 
| character_set_filesystem | binary | 
| character_set_results    | utf8   | 
| character_set_server     | utf8   | 
| character_set_system     | utf8   | 
+--------------------------+--------+
7 rows in set (0.01 sec)

↑

ユーザの一覧を表示†

作成済みユーザの一覧を表示するには下記を実行する。

mysql> use mysql;
Database changed

mysql> select user,host from user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| mtuser | %         | 
| mysql  | %         | 
| root   | %         | 
|        | localhost | 
| root   | localhost | 
|        | mysql5    | 
| root   | mysql5    | 
+--------+-----------+
7 rows in set (0.00 sec)

↑

テーブルの一覧を表示する†

DB内のテーブル一覧を表示するには以下のコマンドを使用する。

use [DB NAME]
show tables;

↑

テーブルのステータス確認†

テーブルのステータスを確認するには以下のコマンドを使用する。
使用しているDBエンジンの種類などが表示される。

use [DB NAME]
show table status;

↑

DBの変換†

MyISAM→InnoDBへ変換する際には以下のコマンドを使用する。

alter table [TABLE NAME] ENGINE=INNODB;

↑

DBの容量を確認する†

SHOW TABLE STATUS [FROM db_name];

↑

SQLファイルからSQLを実行する†

SQLを記述したテキストファイルからSQLを実行するには以下の操作を行う。

mysql -u root -p [DB NAME] < [SQL File]

↑

テーブルの破損チェック†

テーブルが破損している可能性がある場合は以下の手順にて確認を行う。
MyISAM、InnoDB、ARCHIVEテーブルの場合には使用可能。

破損チェック
```
check table [TABLE NAME];
```

参考URL
http://dev.mysql.com/doc/refman/5.1/ja/check-table.html

↑

テーブルの破損修復†

テーブルが破損している可能性がある場合は以下の手順にて修復を行う。
MyISAMテーブルの場合には使用可能。

破損の修復
```
repair table [TABLE NAME];
```

↑

シェル上からSQLを発行†

単コマンドの実行

mysql -u root --password=[PASSWORD] -e "show databases;"

echo "show databases;" |mysql -u root --password=[PASSWORD]

DBを指定して実行

echo "use [DB NAME]; show table status;" |mysql -u root --password=[PASSWORD]

↑

データベースサイズを調べる†

MB単位で表示

select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;

GB単位で表示

select table_schema, sum(data_length+index_length) /1024 /1024/1024 as GB from information_schema.tables  group by table_schema order by sum(data_length+index_length) desc;

特定のDBのサイズを調べる

select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables where table_schema = '[DATABASE NAME]';

↑

テーブルの中身を消す†

テーブル内のデータを全て削除
```
truncate table [TABLE NAME];
```

↑

外部キー制約の解除†

truncate tableを行う際に以下の様なエラーが発生し、外部キー制約で処理が行えない場合に一時的に外部キー制約のチェックを無効化する。(データの整合性は自己責任)

外部キー制約エラー

MariaDB [zabbix]> TRUNCATE TABLE auditlog;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (略)

外部キー制約のチェックを無効化
```
set foreign_key_checks = 0;
```

外部キー制約のチェックを有効化
```
set foreign_key_checks = 1;
```