MySQLのメモ
プロセス 2013/04/03
実行中のプロセスの一覧を表示する
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
プロセスを強制終了する
KILL プロセスID
テーブルの文字コードを調べるには 2013/09/28
データベースの設定、クライアント側の設定、接続時の設定などを見るには
show variables like 'character\_set\_%';
テーブルごとの設定を見るには
show table status;
【MySQL】テーブルの文字コードを調べる
http://www.softel.co.jp/blogs/tech/archives/1470
MySQLダンプファイルを1行ずつ見やすいフォーマットにするには 2013/04/09
mysqldump -u ユーザ名 -p --skip-extended-insert --complete-insert --order-by-primary データベース名
これで1レコードが1行に完結し、ファイルの一部を見ただけでもわかりやすいし、diff もしやすい。
- オプション
--skip-extended-insertは1レコードごとに1つのINSERT文を出力する。 - オプション
--complete-insertはカラム名もINSERT文ごとにいちいち出力する。 - オプション
--order-by-primaryはプライマリキーの順にINSER文を出力する。
MySQLダンプでデータ自体ではなく、テーブル定義のみをエクスポートするには 2013/04/09
mysqldump -u ユーザ名 -p --no-data データベース名
mysqlコマンドで結果が縦に長い時にページャで見るには、または横に長くて崩れてしまうのをなんとかするには 2013/07/02
mysqlコマンドのオプションに --pager='less -SXF' をつける。 -X を付けると、 less 終了時に画面をクリアしない。 -F を付けると、行数が短くて一画面に収まる場合は less がすぐに終了する。
-> lessコマンド
mysqlコマンドでSELECTの結果を表形式ではなくレコードごとに縦に表示させるには 2013/07/24
最後のセミコロンのかわりに \G を付けると、縦に表示してくれる。
SELECT * FROM hoge\G
MySQLは正規表現が使える 2013/04/23
正規表現 | MySQL 5.1 リファレンスマニュアル
http://dev.mysql.com/doc/refman/5.1/ja/regexp.html
MySQLへの接続のパスワードを変更するには 2013/06/18
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を実行するには 2013/09/10
-e オプションでSQLを渡す。
例
mysql -u username -p dbname -e 'SELECT * FROM foo;'
SELECT文の結果はTSVで出力される。
-N を付けるとSELECTの1行目のカラム名が出力されなくなる。
PostgreSQLの場合 2013/12/05
PostgreSQLでこれに相当することを行うには -c オプションを使う。
psql dbname -c 'SELECT * FROM foo;'
TSV形式にするには -A オプションで固定幅での表示を無効(位置揃えなし)にし、
-F オプションで区切り文字を指定する。
psql dbname -c "SELECT * FROM foo;" -A -F$'\t'
TSVファイルでデータをインポートするには 2015/04/06
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オプション付きで起動するときは注意がいる件 2013/07/02
http://backslash.ddo.jp/wordpress/archives/397
接続する際のホスト名を 127.0.0.1 から localhost に変更すると接続できるようになるという奇抜な現象
に自分も先日食らって、接続するソースコードに
s/127\.0\.0\.1/localhost/ 的なコードを追加していたのだった。
設定ファイル my.cnf の場所 2013/10/08
CentOS: /etc/my.cnf
Ubuntu: /etc/mysql/my.cnf
mysqlコマンドからポート番号を指定して接続するには 2016/07/25
mysql -h ホスト名 -P ポート番号
小文字の -p にしてしまうと、ポート番号の指定ではなく、パスワードのプロンプトを出すオプションになる。
普通とは違うポート番号につなぐには、たとえローカルのMySQLであっても -h がいるみたい。
-h を指定せずに -P だけ指定しても -P が無視されてしまった。
レプリケーションエラーをとりあえず解決する方法方法 2015/08/17
SLAVE側でエラーになって止まっちゃっているSQLをスキップするために、以下のSQLを実行する。
STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SELECT SLEEP(1); START SLAVE; SHOW SLAVE STATUS\G
接続先のMySQLサーバのバージョンを見るには 2016/05/06
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のバイナリログ保存期間を短くするには 2016/08/29
以下の操作でとりあえずバイナリログをある程度削除できる。
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)
バイナリログの形式を確認するには 2016/09/07
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
コマンドラインでパスワードプロンプトを回避するには 2016/09/08
$ mysql -u ユーザ名 -p
とすると、パスワードのプロンプトが出てしまう。
-p のあとに空白をおかずに続けてパスワードを書けば、プロンプトが出ずに直接接続できる。
$ mysql -u ユーザ名 -pパスワード
ただ、これだとパスワード情報がpsコマンドとかでほかのLinuxユーザ権限からも見れてしまうので、いやだという場合もある。
その場合は、環境変数でパスワードを指定する。
$ MYSQL_PWD=パスワード mysql -u ユーザ名
いずれの方法もコマンド履歴にはパスワードが書かれてしまうが、それは気にしなくていいという場合にのみ有効だ。