When using mysqldump should I be using --single-transaction
? should i always use -q
?
What are best practices for dumping multiple MySQL databases? (--all-databases
)
linuxMySQL
When using mysqldump should I be using --single-transaction
? should i always use -q
?
What are best practices for dumping multiple MySQL databases? (--all-databases
)
Best Answer
It is entirely going to depend on your requirements. Read the manpage, use the flags to create the type of dumps you need, and then test the restoration. Be careful with locking, as it will likely prevent your application(s) from working on varying levels.
There are numerous backup strategies for MySQL, which differ between engine types. The same methods to backup MyISAM are not always able to be applied to InnoDB. This topic is well covered on Serverfault, I suggest you search the site.
One of my preferred strategies:
Xtrabackup is a useful tool. If you have replication, additional tools such as Maatkit will be extremely helpful.