以前にもバックアップのレビューを不定期で行ってきましたが、
MYSQLのバージョンも上がっているので、改めてバックアップのレビューをしておく。

Defaultオプションの確認

[root@CentOS64VM data]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/var/lib/mysql/mysql.sock --quick --max_allowed_packet=16M --default-character-set=utf8
[root@CentOS64VM data]#

mysqldump default option

–master-data=2
If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

ここら辺は、”--dump-slave” スレーブにてダンプをとった場合、
CHANGE MASTER情報をダンプに含むに入れ替えられるか要検証。
合わせて、”--apply-slave-statements”: STOP SLAVEおよびSTART SLAVEコマンドを、
CHANGE MASTERの前後に追加してくれるオプションも検証。

ログのFlush
--flush-logs
Flush the MySQL server log files before starting the dump.
This option requires the RELOAD privilege. If you use this option in combination
with the –all-databases option, the logs are flushed for each database dumped.
The exception is when using –lock-all-tables or –master-data: In this case, the logs are flushed only once,

ロック関連確認
--lock-all-tables, -x
--single-transaction

Lock all tables across all databases. This is achieved by acquiring a global read lock
for the duration of the whole dump. This option automatically turns off --single-transaction
and --lock-tables. --single-transaction is a much better option than --lock-tables
because it does not need to lock the tables at all.

Because –lock-tables locks tables for each database separately,
this option does not guarantee that the tables in the dump file are
logically consistent between databases. Tables in different databases
may be dumped in completely different states.

詳細は沢山あるので、4.5.4. mysqldump — A Database Backup Programを確認。

関連リンク
MYSQLバックアップ見直し

参考サイト
4.5.4. mysqldump — A Database Backup Program
MySQL: mysqldumpのコマンドオプションメモ
MySQLのmysqldump で –skip-opt を使うときに気をつける
mysqldump –dump-slave についてAdd Star
MySQLコミュニティ騒然!MySQL 5.5.4が与えるインパクト。

Comments are closed.

Post Navigation