I normally use MySQL Administrator to backup my database. I want to enable all the backup options for the available backup types in MySQL. I noticed that Binary Logging is not running on my PC. How to enable it? I also want to know what all parameters to include in a command-line mysqldump command to take a perfect guaranteed backup.
Add the following to my.ini
Then, restart mysql
As for mysqldump, most of the options you need are defaulted already in --opt. Here are my choice parameters:
Here is an explanation for my choices (including default --opt)
You will need --master-data if you want to setup the dump to prepare replication slaves. The master log and position at the time of the dump will be recorded as a comment (using --master-data=2) or as a command (using --master-data=1) on line 22 of the dump file.
UPDATE 2011-07-26 15:20 EDT
If you would like to dump separate databases and separate tables, please follow the instructions at this URL.
Rolando mentioned how to enable binary logging in the log. All you need to do is set the log-bin variable and restart mysql.
You can ensure that binary logging is enabled on a running server with:
I think it's VERY important to note that you're never really guaranteed a perfect backup everytime. As in life, nothing is guaranteed.
You need to set up a backup strategy and test a restore periodically.
Doing this ensures...