mysqldumpの仕様変更で発生するエラー対応が面倒くさい
mysql5.7～mysql8.0あたりで、mysqldumpでも仕様変更があってエラーが発生することがある。
結論から言うとmysqldumpを使わずクリーンなtsvデータを出力するPythonのCLIツールを作成した
mysqldumpの仕様変更で発生するエラー対応が地味に面倒くさいので、secure-file-priv対策とneed the PROCESS privilege対策と根本対策を目的に、mysqldumpを使わずクリーンなtsvデータを出力するPythonのCLIツールを作成した。
発端はsecure-file-privエラー
現象と原因
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
バックアップ等の目的でmysqldumpを利用してデータベースをdumpした時にエラーが発生したときは、ファイル出力するディレクトリを定義しておく必要がある。
対策
my.confファイルにsecure-file-privを定義する。
secure-file-privには mysqldump を実行するディレクトリを指定するのだが、ディレクトリ指定が面倒な場合は""を指定すれば全てのディレクトリでmysqldumpでの出力が可能になる。
[mysqld]
secure-file-priv = ""
設定後にmysqlサービスの再起動が必要
need the PROCESS privilege対策（権限エラー）
現象と原因
need the PROCESS privilege
mysqldumpを実行するための権限が実行ユーザーに割り当てる必要がある。
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces
対策
例えば、実行するユーザー名が「dumpuser」
dumpしたいデータベース名が「mydbname」
の場合、以下の権限設定が必要になる
GRANT SELECT, LOCK TABLES, PROCESS ON mysql.* TO 'dumpuser'@'localhost';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON mydbname.* TO 'dumpuser'@'localhost';
mysql. へGrant権限を割り当てられない場合（mysql.でのGrant権限がない場合）
ユーザーデータベースのみ権限を付与してmysqldumpを実行するには、該当のDB（同例だとmydbname）へのGrant権限付与を行う。
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, PROCESS ON mydbname.* TO 'dumpuser'@'localhost';
管理用データベースのmysql.*の権限を変更せずに、リスクを避けてmysqldumpを実行したい場合は、
「--no-tablespaces」オプションを付与することでmysqldumpを実行することができる。
mysqldump -u dumpuser -p'mypassword' -h localhost -B mydbname --no-tablespaces >mydumpfile.sql
実行時のコマンドライン
上と同じ、実行するユーザー名が「dumpuser」
dumpしたいデータベース名が「mydbname」として、
パスワードは「mypassword」、出力ファイル名は「mydumpfile.sql」と仮定すると、
以下の書式でDBをダンプする事ができる。
mysqldump -u dumpuser -p'mypassword' -h localhost -B mydbname --no-tablespaces >mydumpfile.sql
その他、おまけ
テーブル初期化を含めないdump
mysqldump -u dumpuser -p'mypassword' -h localhost -B mydbname --column-statistics=0 --set-gtid-purged=OFF --no-tablespaces >mydumpfile.sql
出力ファイルをgz圧縮するdump
mysqldump -u dumpuser -p'mypassword' -h localhost -B mydbname --column-statistics=0 --set-gtid-purged=OFF --no-tablespaces | gzip >mydumpfile.sql.gz
テーブル初期化とANALYZE TABLE文を含めないdump
mysqldump -u dumpuser -p'mypassword' -h localhost -B mydbname --skip-column-statistics --column-statistics=0 --set-gtid-purged=OFF --no-tablespaces | gzip >mydumpfile.sql.gz
根本的な対策としてmysqldumpを使わずにデータをexportする
そもそもデータ出力だけできれば良い場合はmysqldumpは不要でSELECT権限だけあればいい。
なのでdatabaseとtable名を指定してシンプルにtab区切りのデータを出力するツールを作成した。
python mysqlexport.py --user your_username --password your_password --host your_host --db your_db --table your_table --columns column1 column2 --output output_file.tsv
データ運用として使うにはシンプルなCLIツールが非常に便利に使える。
OSSで公開しているのでcsvやjsonの出力が必要な人はソースを改訂すれば活用できると思います。
スキーマを含めたバックアップ等はroot権限相当のユーザーでmysqldumpを実行した方が良いので、適材適所で柔軟に運用しましょう。
