忘れやすいのでMySQL/MariaDBに関するTipsをメモ。完全に自分用メモ。
師匠のまとめ。分かり易い。
information_schemaから情報を引っ張る系。
カラム名に「XXX」を含むテーブルを調べる。
SELECT table_name,table_schema,column_name,column_type FROM information_schema.COLUMNS WHERE column_name LIKE '%XXX%' ;
テーブル名に「YYY」を含むテーブルを調べる。
SELECT table_name,table_schema,table_type FROM information_schema.TABLES WHERE table_name LIKE '%YYY%' ;
SHOW FULL PROCESSLISTを見やすい形で引っ張る。
SELECT
ID,
USER,
HOST,
DB,
COMMAND,
TIME,
STATE,
INFO,
TIME_MS,
STAGE,
PROGRESS,
QUERY_ID
FROM
information_schema.PROCESSLIST;
スキーマ名指定でshow full processlistを引っ張る。
mysql> SELECT * FROM information_schema.PROCESSLIST WHERE DB = '<スキーマ名>';
- 多分これが一番簡単。
レプリケーション系。
レプリケーション用にdump取得。
# mysqldump -h <対象ホスト> --databases <対象スキーマ> -u root -p<PW> --master-data --routines --single-transaction > /var/data/<対象スキーマ>.dump
- single-transactionを付けることで、テーブルロックを避ける(はず…)
- --master-data…CHANGE MASTER TO を含む。レプリケーション構築時に必須。
- --routines…ストアドプロシージャー、ストアドファンクションも含む。
レプリケーション設定用にbin-logのポジションを確認。
# grep "CHANGE MASTER TO" <対象スキーマ>.dump
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=19199388;
#
レプリケーション設定(ノーマル)
CHANGE MASTER TO
MASTER_HOST = 'マスタサーバのIP or FQDN',
MASTER_USER = 'レプリケーション用のアカウント',
MASTER_PASSWORD = 'PW',
MASTER_PORT = 3306 ,
MASTER_LOG_FILE='mysql-bin.XXXXXX',
MASTER_LOG_POS=YYYYYY;
レプリケーション設定(マルチソース)
SET @@default_master_connection='任意のコネクション名';
CHANGE MASTER '任意のコネクション名' TO
MASTER_HOST = 'マスタサーバのIP or FQDN',
MASTER_USER = 'レプリケーション用のアカウント',
MASTER_PASSWORD = 'PW',
MASTER_PORT = 3306 ,
MASTER_LOG_FILE='mysql-bin.XXXXXX',
MASTER_LOG_POS=YYYYYY;
意図的にレプリケーションエラーをSKIPする。
STOP SLAVE ; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; SHOW SLAVE STATUS\G
マルチソースレプリケーションの場合はこっち。
SET @@default_master_connection='<レプリケーション名>'; STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; SHOW SLAVE STATUS\G
CONCATで文字列結合。
CONCAT(str1,str2,...)
引数を連結することで生成される文字列を返します。1 つ以上の引数を持つ場合があります。
すべての引数が非バイナリ文字列の場合は、結果も非バイナリ文字列になります。
引数にバイナリ文字列が含まれる場合は、結果はバイナリ文字列になります。
数値の引数は、同等の非バイナリ文字列形式に変換されます。
難しく書いてあるけど、以下の例のように文字列結合できるということ。
CONCAT関数で文字列結合した場合の例。
mysql> SELECT CONCAT (1, ' + ', 1, ' = ', 2 ) AS '1 + 1 = 2';
+---------------------------------+
| CONCAT (1, ' + ', 1, ' = ', 2 ) |
+---------------------------------+
| 1 + 1 = 2 |
+---------------------------------+
1 row in set (0.00 sec)
mysql>
CONCATしていない場合の例。
こっちは参考までに。
mysql> SELECT 1, ' + ', 1, ' = ', 2 ;
+---+-----+---+-----+---+
| 1 | + | 1 | = | 2 |
+---+-----+---+-----+---+
| 1 | + | 1 | = | 2 |
+---+-----+---+-----+---+
1 row in set (0.00 sec)
mysql>
文字列結合をパイプ(||)で行う。
CONCAT関数でも良いけど、サーバ上で直接ワンライナーしたい時なんかは、パイプ(||)で結合した方が、
個人的には見やすい。
一時的な変更。(DB再起動すると設定元に戻る)
mysql> SET sql_mode='PIPES_AS_CONCAT' ;
Query OK, 0 rows affected (0.00 sec)
mysql>
永続変更。
/etc/my.cnfの[mysqld]セクションに以下を追記してMySQLを再起動する。
- MariaDBだったら/etc/mycnf.d/server.cnf
[mysqld]
sql_mode = 'PIPES_AS_CONCAT'
パイプで文字列結合した場合の例。
mysql> SELECT 1 || ' + ' || 1 || ' = ' || 2 AS '1 + 1 = 2';
+-----------+
| 1 + 1 = 2 |
+-----------+
| 1 + 1 = 2 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysqldump系
TRIGGERだけmysqldumpする
レプリケーション用にdump取得。
TRIGGER
TRIGGERテンプレート(INSERT)
DELIMITER ;;
CREATE TRIGGER <トリガー名> AFTER INSERT ON <対象テーブル名>
FOR EACH ROW
BEGIN
---- 処理内容 ----
END
;;
DELIMITER ;
TRIGGERテンプレート(UPDATE)
DELIMITER ;;
CREATE TRIGGER <トリガー名> AFTER UPDATE ON <対象テーブル名>
FOR EACH ROW BEGIN
---- 処理内容 ----
END
;;
DELIMITER ;
TRIGGERテンプレート(DELETE)
DELIMITER ;;
CREATE TRIGGER <トリガー名> AFTER DELETE ON <対象テーブル名>
FOR EACH ROW BEGIN
---- 処理内容 ----
END
;;
DELIMITER ;
insert文だけを出力するには、-t または --no-create-info オプションを指定します。
$ mysqldump -t DB名 テーブル名
テーブル定義だけを出力するには -d または --no-data オプションを指定します。
$ mysqldump -d DB名 テーブル名
テーブル定義だけを出力する方法として、-w でダンプするデータの条件が偽になるようにするという手もあります。
$ mysqldump DB名 テーブル名 -w '0=1'
次の様に、データベース名に続けてバックアップしたいテーブル名を指定して mysqldump を実行すれば良いです。
$ mysqldump -u ユーザ名 -p -h ホスト名 データベース名 [テーブル名 ...] > ダンプファイル名
ダンプ対象の複数のテーブル名をあらかじめファイルに書いておき、それを使って指定することもできます。 次の例では tables.txt の各行にダンプ対象のテーブル名が書いてあるとします。
$ mysqldump -u ユーザ名 -p -h ホスト名 データベース名 `cat tables.txt` > ダンプファイル名
mysqldump -h <対象ホスト名> -P 3306 --databases <対象スキーマ名> -u sysadm -p<パスワード> --master-data --routines --single-transaction > /var/data/<対象スキーマ名>.dump