mysqldump is commonly used for making a MySQL database backup or for setting up a replication.
As in all mysql binaries, there are quite a few parameters to mysqldump. Some are just niceties but some flags are a must. Of course, choosing the parameters to use greatly depends on your requirements, database setup, network capacity etc.
Here is my usual setup for mysqldump. The parameters below apply for an InnoDB based schema (no MyISAM, Memory tables). Parameters can be specified on the command line, or under the [mysqld] scope in the MySQL configuration file.
mysqldump -u dump_user -p -h db_host --routines --master-data --single-transaction --skip-add-locks --skip-lock-tables --default-character-set=utf8 --compress my_db
Let’s review these parameters and see their effect: Continue reading » “Parameters to use on mysqldump”