First page Back Continue Last page Overview Graphics
Backup Agents and Tools
Dump: Tips mysqldump(contd)
For incremental dump based backups and and guaranteeing all DBs and their binary logs are in sync, use both --opt and
--flush-logs (with log-bin enabled). Only this will allow "point-in-time recoveries".
For InnoDB table types (only), use the --single-transaction (MySQL>=v4.0.2). This will allow for "Hot Backups" with full r/w ("lock-less") access during the dump. As of 4.1.8, use with the
--master-data option for point-in-time recoveries.
For large DBs (<v4.1), use --quick and --extended-insert, or in in =>4.1 just use --opt (default).
In Windows use the --result-file=db.sql option instead of >
For faster restores on single, very large DBs, dump with the -T option to save out to a separate paired table.sql (structure) and table.txt (TSV data) files. Then for fast restores use:
# mysql < table.sql && mysql -e LOAD DATA INFILE table.txt
Notes: