MySQLのメモ

プロセス

実行中のプロセスの一覧を表示する

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

プロセスを強制終了する

KILL プロセスID

テーブルの文字コードを調べるには

データベースの設定、クライアント側の設定、接続時の設定などを見るには

show variables like 'character\_set\_%';

テーブルごとの設定を見るには

show table status;

【MySQL】テーブルの文字コードを調べる
http://www.softel.co.jp/blogs/tech/archives/1470

MySQLダンプファイルを1行ずつ見やすいフォーマットにするには

mysqldump -u ユーザ名 -p --skip-extended-insert --complete-insert --order-by-primary データベース名

これで1レコードが1行に完結し、ファイルの一部を見ただけでもわかりやすいし、diff もしやすい。

MySQLダンプでデータ自体ではなく、テーブル定義のみをエクスポートするには

mysqldump -u ユーザ名 -p --no-data データベース名

mysqlコマンドで結果が縦に長い時にページャで見るには、または横に長くて崩れてしまうのをなんとかするには

mysqlコマンドのオプションに --pager='less -SXF' をつける。 -X を付けると、 less 終了時に画面をクリアしない。 -F を付けると、行数が短くて一画面に収まる場合は less がすぐに終了する。

-> lessコマンド

mysqlコマンドでSELECTの結果を表形式ではなくレコードごとに縦に表示させるには

最後のセミコロンのかわりに \G を付けると、縦に表示してくれる。

SELECT * FROM hoge\G

MySQLは正規表現が使える

正規表現 | MySQL 5.1 リファレンスマニュアル
http://dev.mysql.com/doc/refman/5.1/ja/regexp.html

MySQLへの接続のパスワードを変更するには

MySQLへの接続のパスワードを変更するには、以下のコマンドを打つ。

mysqladmin password -u username -p

まず現在のパスワードを聞かれるので、それを入力し、その後に新しいパスワードを 2回入力する。新しいパスワードとしてなにも入力せずにエンターを押すと、パスワードなしで接続できるようになる。

パスワードを削除するには、MySQLに接続して、以下のSQLを実行してもよい。

USE mysql;
UPDATE user set Password = '' where User='username';
FLUSH PRIVILEGES

mysqlテーブルに認証情報が保存されているので、それを直接書き換える方法だが、 FLUSH PRIVILEGES を実行しないと、起動中のデーモンに反映されない。

同様にパスワードを削除ではなく新しいパスワードを設定するには、 PASSWORD という関数を使う。

USE mysql;
UPDATE user set Password = PASSWORD('newpassword')  where User='username';
FLUSH PRIVILEGES

コマンドラインから直接SQLを実行するには

-e オプションでSQLを渡す。

mysql -u username -p dbname -e 'SELECT * FROM foo;'

SELECT文の結果はTSVで出力される。

-N を付けるとSELECTの1行目のカラム名が出力されなくなる。

PostgreSQLの場合

PostgreSQLでこれに相当することを行うには -c オプションを使う。

psql dbname -c 'SELECT * FROM foo;'

TSV形式にするには -A オプションで固定幅での表示を無効(位置揃えなし)にし、 -F オプションで区切り文字を指定する。

psql dbname -c "SELECT * FROM foo;" -A -F$'\t'

TSVファイルでデータをインポートするには

LOAD DATA というSQLの構文を使う。以下は items テーブルに /home/foo/bar.tsv のファイルからインポートするSQLの例。

load data local infile '/home/foo/bar.tsv' into table items;

TSVファイルは1行目からデータでよい。カラムの順番は、テーブルのカラムの順番と一致している必要がある。

テーブルの一部カラムのみをTSVファイルからインポートしたい場合や、カラムの順番が違う場合は、以下のようにTSVにあるカラムを列挙する。

load data local infile '/home/foo/bar.tsv' into table items (id, title, keywd);

LOAD DATA INFILE Syntax | MySQL 5.5 Reference Manual
http://dev.mysql.com/doc/refman/5.5/en/load-data.html

MySQLサーバをskip-name-resolveオプション付きで起動するときは注意がいる件

http://backslash.ddo.jp/wordpress/archives/397

接続する際のホスト名を 127.0.0.1 から localhost に変更すると接続できるようになるという奇抜な現象

に自分も先日食らって、接続するソースコードに s/127\.0\.0\.1/localhost/ 的なコードを追加していたのだった。

設定ファイル my.cnf の場所

CentOS: /etc/my.cnf
Ubuntu: /etc/mysql/my.cnf

mysqlコマンドからポート番号を指定して接続するには

mysql -h ホスト名 -P ポート番号

小文字の -p にしてしまうと、ポート番号の指定ではなく、パスワードのプロンプトを出すオプションになる。

普通とは違うポート番号につなぐには、たとえローカルのMySQLであっても -h がいるみたい。 -h を指定せずに -P だけ指定しても -P が無視されてしまった。

レプリケーションエラーをとりあえず解決する方法方法

SLAVE側でエラーになって止まっちゃっているSQLをスキップするために、以下のSQLを実行する。

STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SELECT SLEEP(1); START SLAVE; SHOW SLAVE STATUS\G

接続先のMySQLサーバのバージョンを見るには

SELECT version();

というSQLを実行する。

コマンドラインからは

$ mysql -u root -e "select version();"
+------------+
| version()  |
+------------+
| 5.5.29-log |
+------------+

または

$ mysql -u root -N -e "select version();" | cat
5.5.29-log

MySQLのバイナリログ保存期間を短くするには

以下の操作でとりあえずバイナリログをある程度削除できる。 expire_logs_days の設定変更は以下の操作だけではMySQL再起動時に消えてしまうので、 my.cnf に書く必要がある。

MariaDB [(none)]> SHOW GLOBAL VARIABLES like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 3     |
+------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL expire_logs_days = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 1     |
+------------------+-------+
1 row in set (0.00 sec)


MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+------------+
| Log_name          | File_size  |
+-------------------+------------+
| mysqld-bin.000008 | 1074016731 |
| mysqld-bin.000009 | 1074754088 |
| mysqld-bin.000010 | 1074263118 |
| mysqld-bin.000011 | 1074626034 |
| mysqld-bin.000012 | 1074046463 |
| mysqld-bin.000013 | 1074556371 |
| mysqld-bin.000014 | 1073972826 |
| mysqld-bin.000015 | 1074462550 |
| mysqld-bin.000016 | 1074354806 |
| mysqld-bin.000017 | 1073980718 |
| mysqld-bin.000018 | 1073964110 |
| mysqld-bin.000019 | 1074121349 |
| mysqld-bin.000020 | 1073975155 |
| mysqld-bin.000021 | 1073938399 |
| mysqld-bin.000022 |   92385280 |
| mysqld-bin.000023 |  664940544 |
| mysqld-bin.000024 |  208117607 |
+-------------------+------------+
17 rows in set (43.13 sec)

MariaDB [(none)]> PURGE MASTER LOGS TO 'mysqld-bin.000023';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| mysqld-bin.000023 | 664940544 |
| mysqld-bin.000024 | 218557579 |
+-------------------+-----------+
2 rows in set (0.00 sec)

バイナリログの形式を確認するには

STATEMENT, ROW, MIXED のうちのどれなのかを確認する方法。

mysql> SHOW VARIABLES LIKE "binlog_format";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

MySQL :: MySQL 5.6 リファレンスマニュアル :: 17.1.2 レプリケーション形式
https://dev.mysql.com/doc/refman/5.6/ja/replication-formats.html

コマンドラインでパスワードプロンプトを回避するには

$ mysql -u ユーザ名 -p

とすると、パスワードのプロンプトが出てしまう。

-p のあとに空白をおかずに続けてパスワードを書けば、プロンプトが出ずに直接接続できる。

$ mysql -u ユーザ名 -pパスワード

ただ、これだとパスワード情報がpsコマンドとかでほかのLinuxユーザ権限からも見れてしまうので、いやだという場合もある。

その場合は、環境変数でパスワードを指定する。

$ MYSQL_PWD=パスワード mysql -u ユーザ名

いずれの方法もコマンド履歴にはパスワードが書かれてしまうが、それは気にしなくていいという場合にのみ有効だ。

このサイトは筆者(hydrocul)の個人メモの集合です。すべてのページは永遠に未完成です。