LoginSignup
108
108

More than 5 years have passed since last update.

@hit(hit Pannacotta)

mysqlで知っておくと便利な小技集～どちらかというと運用

Last updated at Posted at 2014-11-05

日々のデータベースの運用で、知っておくと、作業効率がアップする小技集です。設計の観点ではなくて、運用の観点で・・ですが。
何かしらの障害や、アプリケーションエンジニアからの問い合わせがあった場合に、以下周りをサクッと実行できれば、なんか、できるデータベースエンジニアみたいな感覚に陥ります（自己陶酔って意味で）

環境設定編

  • CONCAT関数を、パイプ（｜）で省略可能にする

ちょっとわからないかもしれませんが、例えばSQLで

SELECT 'ABC' || 'DEF' ;

と

SELECT CONCAT('ABC','DEF')

は同じ結果になります。ちなみに出力結果は、ABCDEFになります

  • my.cnf から
sql_mode = 'PIPES_AS_CONCAT'
  • MySQLプロンプトから
mysql> SET GLOBAL sql_mode = 'PIPES_AS_CONCAT'

スキーマ名・テーブル名を大文字・小文字を区別なく問合せ可能となる。

  • mysqlプロンプトから
mysql>
SET GLOBAL lower_case_table_names=1 ;
  • my.cnf に設定
lower_case_table_names=1

my.cnf の読み込み順序を確認する

結構トラブルになりがちなのが、mysqlの環境設定ファイル(my.cnf）が適用されないという問題。結構の確率で、my.cnfの読み込み順序の問題です（特にmariadbは、my.cnf から、mysql/server.conf をインクルードする関係で、OSを再インストールしない前提で、mysqlから、mariadbに切り替える際などに過去の残骸が残っていて、余裕でもハマります

# mysql --help | grep -A1 "Default options"
-----------------
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

MySQLのコマンド補完を有効にする

  • mysqlの起動オプションで・・
mysql --auto-rehash
  • my.cnf に設定
auto-rehash

コマンド編(bash)

複数のコマンドを続けて投げる

mysql -e "CREATE TABLE test.table1 (id int,value1 int) ; INSERT INTO test.table1 (id,value1) VALUES (1,2);"

SQLファイルを読み込ませて実行

３通りの方法があるのをご存知ですか？

こんなSQLファイルを作成しましたのでこれを例に説明します

  • SQLファイルを生成する
cat << '_EOT_' > /tmp/sample.sql

SELECT * FROM test.tables1 ;

_EOT_

結果は同じです

  • catとパイプを使う
cat /tmp/sample.sql | mysql
  • リダイレクションを利用する
mysql < /tmp/sample.sql
  • -e オプションの中で、ファイルを読み込ませる

ファイル読み込んで実行した後、普通のSQLとか続けて打ってみました

mysql -e "source /tmp/sample.sql ; select 'end';"

おまけ

catとパイプとリダイレクションの合わせ技、ここでは、sample.sqlを実行し、その結果を、result.txtに戻していることを意味しております

cat /tmp/sample.sql | mysql > /tmp/result.txt

コマンド/SQL編（mysqlコンソール）

レプリケーションの設定をする (mariaDB 10.Xのマルチソースレプリケーションの場合）

マルチソースレプリケーションについては、 こちらをご覧ください

mysql>
SET @@default_master_connection='CONN1';

STOP SLAVE 'CONN1' ; 
RESET SLAVE 'CONN1';

CHANGE MASTER 'CONN1' TO 
MASTER_HOST = 'XXX.XXX.XXX.XXX',
MASTER_USER = 'slave_account',
MASTER_PASSWORD = 'slaveパスワード',
MASTER_PORT = 3306 ,
MASTER_LOG_FILE='mysql-bin.XXXXX', 
MASTER_LOG_POS=XXXX;

START SLAVE ;

SHOW SLAVE 'CONN1' STATUS \G

複数のコマンドを投げて、レプリケーションエラーをスキップさせる

原因がわかってて、エラーをスキップさせる場合は以下の1行をコピペで・・・

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

レプリケーションエラーを自動でスキップさせる

・・がしかし！延々とレプリケーションエラーが出ていると、いちいちスキップさせるのもしんどくなるので

my.cnf に以下を追記して再起動する（この設定をすると、当該エラーが発生した時はスルーしてしまい原因究明が困難になります。それでもエラー対応するよりまし！という方はどうぞ・・

  • my.cnf （もしくは、server.cnf）に追記

該当エラーがレプリケーションで発生した場合は、エラーをなかったことにして、レプリケーションを継続させます

# 1159 Got timeout reading communication packets
# 1032 Message: '%s'のなかにレコードが見付かりません
# 1022 Message: table '%s' に key が重複していて書きこめません
# slave-skip-errors=1022,1032,1159
slave-skip-errors=1032

テーブル一覧をSQLで実行する

データベース名（スキーマ名）を変数にしておくと、いろいろ便利です


SET @SCHEMA_NAME='wordpress' ;

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  TABLE_TYPE,
  ENGINE
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = @SCHEMA_NAME ;
---
+--------------+-----------------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME            | TABLE_TYPE | ENGINE |
+--------------+-----------------------+------------+--------+
| wordpress    | events_rec            | BASE TABLE | MyISAM |
| wordpress    | issues                | BASE TABLE | InnoDB |
| wordpress    | t1                    | BASE TABLE | InnoDB |
| wordpress    | test_users            | BASE TABLE | InnoDB |
| wordpress    | wp_commentmeta        | BASE TABLE | InnoDB |
| wordpress    | wp_comments           | BASE TABLE | InnoDB |
| wordpress    | wp_github             | BASE TABLE | InnoDB |

改行コード（Cr)が含まれているフィールドを、Windows形式の改行に変換する(CrLf)

UPDATE TABLE1 SET col1 =  REPLACE(col1, CHAR(10), CHAR(13) || CHAR(10)) ;

CHAR(13)が、CR キャリッジリターン
CHAR(10)が、LF ラインフィードということですね

パイプでつないでいるので、CrLf です

show コマンドの曖昧検索

  • 環境設定変数を、曖昧検索する
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_open%' ;
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 300   |
+-------------------+-------+
  • テーブル一覧を、曖昧検索する
mysql> 
SHOW TABLES LIKE '%users%' ;

Database changed
+---------------------------+
| Tables_in_movie (%users%) |
+---------------------------+
| users                     |
+---------------------------+

定義のみをコピーして別のテーブルを作成する

索引定義やらなんやらかんやらを、すべてコピーしてくれます。ただしデータはコピーしません

  • 元となるテーブル定義
CREATE TABLE `table1` (
  `id` int(11) DEFAULT NULL,
  `value1` int(11) DEFAULT NULL,
  KEY `ix01_table1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  • コピー開始
mysql>
 CREATE TABLE test.table1_copy LIKE test.table1 ;
  • コピーできました
mysql> 
  show create table test.table1_copy ;
----
  CREATE TABLE `table1_copy` (
   `id` int(11) DEFAULT NULL,
   `value1` int(11) DEFAULT NULL,
   KEY `ix01_table1` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8

定義とデータをコピーする

データを含めて定義をコピーできますが、索引等まではコピーできません。

  • コピー開始
mysql> 
CREATE TABLE table1_copy2 SELECT * FROM table1 ;
  • 結果確認（データ）
mysql> SELECT * FROM table1_copy2 ;                                                                                                    
+------+--------+
| id   | value1 |
+------+--------+
|    1 |      2 |
+------+--------+
  • 定義確認

インデクスの定義がなくなっていることがわかるかと思います

 CREATE TABLE `table1_copy2` (
  `id` int(11) DEFAULT NULL,
  `value1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

SQLでプロセスリストを取得する

何言っているのかわからないと思うのですが、MySQLでトラブルシューティングでよく実行するコマンドが、PROCESSLISTというものなのですが、ここでSQLがどれくらい滞留しているのかの状況等を確認することができます。

mysql>
SHOW PROCESSLIST ; 
-- もしくは、
SHOW FULL PROCESSLIST ;

ただし、ものによっては大量の結果が帰ってきたりして本質の問題を見落とす危険性があります、また、例えば、何かの条件に絞ったプロセス状況を見たいなどどいうケースにはこのコマンドは向きません。

それで、PROCESSLISTのコマンドを使うのではなく、PROCESSLIST というテンポラリテーブルを利用します。

  • information_schema.PROCESSLIST
 CREATE TEMPORARY TABLE `PROCESSLIST` (
  `ID` bigint(4) NOT NULL DEFAULT '0',
  `USER` varchar(128) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext,
  `TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000',
  `STAGE` tinyint(2) NOT NULL DEFAULT '0',
  `MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0',
  `PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000',
  `MEMORY_USED` int(7) NOT NULL DEFAULT '0',
  `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0',
  `QUERY_ID` bigint(4) NOT NULL DEFAULT '0'
) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=0
  • 上記テーブルを利用して、60秒以上経過しているプロセスを表示する
mysql> select COMMAND,TIME,STATE from information_schema.PROCESSLIST WHERE TIME > 60;
+-------------+--------+------------------------------------------------------------------+
| COMMAND     | TIME   | STATE                                                            |
+-------------+--------+------------------------------------------------------------------+
| Binlog Dump |    504 | Master has sent all binlog to slave; waiting for binlog to be up |
| Sleep       |    766 |                                                                  |
| Sleep       |   9353 |                                                                  |
| Connect     |    608 | Slave has read all relay log; waiting for the slave I/O thread t |
| Connect     | 439593 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
+-------------+--------+------------------------------------------------------------------+
108
108
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
108
108