MySQL/管理

Tag: MySQL/管理

目次†

↑

参考情報†

MySQL

↑

ユーザー操作†

↑

rootパスワードの設定†

rootのパスワードが設定されていない場合必ず設定しておく。

↑

mysqladminコマンドで設定†

mysqladminコマンドを使って設定する方法。

mysqladmin -u root password <new_password>

↑

mysqlコマンドで設定†

mysqlテーブルを操作してパスワードを設定する方法。

$ mysql -u root -p;
mysql> use mysql;
mysql> set password for root@localhost=password('任意のpasswd');

↑

ユーザー登録†

データベースの作成も可能なユーザーを登録するには、以下のように実行する。

ON *.*のところの最初がDB名で、二つ目がテーブル名。

$ mysql -u root -p
mysql> GRANT ALL PRIVILEGES ON *.* TO ユーザ名@localhost IDENTIFIED BY 'パスワード'
mysql> GRANT ALL PRIVILEGES ON testdb.* TO ユーザ名@localhost IDENTIFIED BY 'パスワード'

ホスト名のところはワイルドカード'%'が指定できる。

mysql> GRANT ALL PRIVILEGES ON *.* TO ユーザ名@'%' IDENTIFIED BY 'パスワード'

ホスト名の部分を省略しても同じ事になるらしい。

mysql> GRANT ALL PRIVILEGES ON *.* TO ユーザ名 IDENTIFIED BY 'パスワード'

確認はrootでuserテーブルを見れば良い。

$ mysql -uroot -p
mysql> use mysql;
mysql> select * from user;

↑

localhostから接続しようとしてもAccess Denied†

Unix系のマシンでローカルホストから接続するときにホスト名のワイルドカード'%'がきかないことがある。その理由はMySQL :: MySQL 4.1 リファレンスマニュアル :: 4.3.12 Access denied エラーの原因にある通りか?

Access denied の原因がわからない場合は、user テーブルから Host 値にワイルドカードが含まれているエントリ（‘%’ または ‘_’ を含むエントリ）をすべて削除する。 よくある間違いは、Host='%' および User='some user' の新規エントリを挿入し、これで、同一マシンから接続する際には localhost を指定できると考えていることである。これがうまくいかない理由は、デフォルト権限として、Host='localhost' および User='' のエントリが含まれているためである。Host 値が 'localhost' の場合、'%' よりも具体的なため、localhost からの接続時に新しいエントリよりもデフォルト権限が優先される。正しい指定の方法は、Host='localhost' および User='some_user' の 2 つ目のエントリを挿入するか、Host='localhost' および User='' のエントリを削除することである。

'%'とlocalhost二つを指定してgrantすればとりあえず良いっぽい。Allowing wildcard (%) access on MySQL db, getting error "access denied for '<user>'@'localhost'" - Server Faultなども参照のこと。

↑

データベース操作†

↑

データベースの生成文を表示する†

show create database <データベース名> を使う。DEFAULT CHARACTER SETや、COLLATEを確認できる。
```
show create database testdb
```

↑

テーブル操作†

↑

テーブルの生成文を表示する†

データベースに接続してコマンド実行
```
show create table xxxxx;
```
mysqldumpを使って一気に表示。テーブル名を省略すればデータベース内の全てのテーブルが対象になる。
```
mysqldump -d <データベース名> (<テーブル名>)
```

↑

カラムを追加する†

ALTER TABLEでOK。

ALTER TABLE item ADD COLUMN price char(100);

↑

NOT NULLの列を追加したらどうなるか†

0とか空文字とかで勝手に初期化される。
データが存在するテーブルに not null 制約の付いたカラムが追加できる - ぱんくずによると、MySQL独特の挙動のようだ。

↑

カラムの順番を変更する†

MySQLのカラム順番変更 | 江島＠システム開発より、

ALTER TABLE テーブル名 MODIFY カラム名　データ型 after 移動させる一つ上のカラム名;

↑

カラムのタイプを変更する†

ALTER TABLE 〜 CHANGE COLUMN構文で変更できる。このとき既存データはできるだけ維持されるよう変換されるため、varcharのサイズを増やす場合は、データがなくなるといった心配はしなくてよい。
```
ALTER TABLE book CHANGE COLUMN title title varchar(400);
```

↑

主キーを複合キーに変更(主キーに列を追加)†

mysql - ALTER TABLE to add a composite primary key - Stack Overflowより。

最初に既存の主キーをDROPしてから再作成する。

ALTER TABLE provider DROP PRIMARY KEY, ADD PRIMARY KEY(person, place, thing);

↑

Tips†

↑

データフォルダの場所を確認する†

database - How to find the mysql data directory from command line in windows - Stack Overflow

以下のコマンドで可能となる。

mysql -uUSER -p -e 'SHOW VARIABLES WHERE Variable_Name LIKE "%dir"'

↑

アクセスするホストを制限したい†

my.cnfなどでは指定できないのでgrantでアクセスできるホストを指定する。

↑

ssh経由の操作†

↑

基本†

MySQLでエクスポートとインポートを効率よく行う　ついでに圧縮 | Hideki's blog
mysqldump の結果をそのまま圧縮したり転送したりするコマンド例 | バシャログ。
```
mysqldump -udb_user db_name -pdb_pass | gzip | ssh example.com 'zcat | mysql -udb_user db_name -pdb_pass'
```

↑

"-e"コマンドでSQLを実行する場合の注意点†

"*"などがシェルに解釈されないように注意

シェルから実行する場合

ssh dbserver "mysql demodb_production -e 'select * from books'"

Rubyから呼び出す場合*をエスケープする必要がある(?)。
```
"ssh dbserver \"mysql demodb_production -e 'select \* from books'\" "
```

↑

バイナリログの削除†

使用している内にmysql-bin.000999というようなファイルが大量にたまってくる。
バイナリファイルと呼ばれ、デフォルトでは自動的に削除されない。
手動で全削除する場合、以下のコマンドを実行する。
```
mysql> RESET MASTER;
mysql> show binary logs;
```

↑

チューニング†

↑

遅いクエリを特定したい†

slow log query機能を使用する。
my.cnfに以下を追加。mysqldを再起動する。/var/logに出力する場合slow_query.logのパーミッションに要注意(mysqld.logを確認して書き出しに失敗している場合chown -R mysql.mysqlとする)。
```
[mysqld]
slow_query_log=1
slow_query_log_file=/var/log/slow_query.log
long_query_time=1
```
mysqldumpslowで解析。Mysql slow queryの設定と解析方法 - masayuki14’s diary
```
mysqldumpslow -s t /var/log/mysql/mysql-slow.sql
```

↑

目次†