MySQL parameters & configuration sample file

The following is a sample my.cnf configuration file. It includes some common and uncommon parameters, along with occasional comments.

You can use this file, and tune as appropriate for your machine, operating system, and application needs.

In particular, note the high values set for the InnoDB related parameters, below, and change them to match your system and application’s requirements. You have been warned. Continue reading » “MySQL parameters & configuration sample file”

Parameters to use on mysqldump

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”