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:

mysqldump -h... -u... -p... --master-data=2 --routines --triggers --flush-privileges --all-databases > DataDump.sql

Here is an explanation for my choices (including default --opt)

--opt               Same as --add-drop-table, --add-locks, --create-options,
                    --quick, --extended-insert, --lock-tables, --set-charset,
                    and --disable-keys. Enabled by default, disable with
-R, --routines      Dump stored routines (functions and procedures).
--triggers          Dump triggers for each dumped table.
--flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
                    database.  This option should be used any time the dump
                    contains the mysql database and any other database that
                    depends on the data in the mysql database for proper
--master-data[=#]   This causes the binary log position and filename to be
                    appended to the output. If equal to 1, will print it as a
                    CHANGE MASTER command; if equal to 2, that command will
                    be prefixed with a comment symbol. This option will turn
                    --lock-all-tables on, unless --single-transaction is
                    specified too (in which case a global read lock is only
                    taken a short time at the beginning of the dump; don't
                    forget to read about --single-transaction below). In all
                    cases, any action on logs will happen at the exact moment
                    of the dump. Option automatically turns --lock-tables

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.

  • What will: mysqldump.exe <table name> do simply? I want to take a full backup. – RPK Jul 26 '11 at 18:50
  • My answer has --all-databases at the end of it which is a full logical backup. mysqldump dbname tblname will dump a single table. – RolandoMySQLDBA Jul 26 '11 at 19:14
  • 1
    Since the binary log is also used for setting up replication, IF you intend to set up a slave, you will want to make sure the server has a unique server-id in the my.cnf . This id is unique among all instances you want to replicate to. Added as comment, because his question didn't specifically ask about replication, but don't want it to be overlooked when configuring the binary-log. – Derek Downey Jul 26 '11 at 20:00

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:

mysql> SHOW VARIABLES LIKE 'log_bin%';
| Variable_name                   | Value |
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| log_bin_trust_routine_creators  | OFF   |
3 rows in set (0.05 sec)

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...

  • you can restore from your backup
  • you are familiar with your strategy so WHEN disaster hits, you won't be fumbling around.
  • you'll already know how to get it back online.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.