Add the following to my.ini
[mysqld]
log-bin=mysql-bin
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
--skip-opt.
-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
restore.
--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
off.
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.
That output of mysqldump is working as designed, and it is properly escaped, unless you try to restore the dump on a MySQL instance with SQL_MODE=NO_BACKSLASH_ESCAPES
set.
There's an outstanding feature request to make mysqldump use a pair of single-quotes to escape literal single-quotes, as per ANSI SQL. See http://bugs.mysql.com/bug.php?id=65941
In the meantime you might be able to convert from backslash-singlequote to pair-of-singlequotes with a command line this:
mysqldump test | sed -e "s/\\\'/''/g" > test-dump.sql
I tried that out briefly by creating a dummy table in my test
database and inserting the string "O'Hare" into the table. But that's hardly a comprehensive test -- I take no responsibility for this suggestion working in all cases.
Best Answer
1) check your last 10 lines of dump file
it should show your dump file properly completed or not.
2) grep the database name in dump file
with the above 2 steps you can find is your backup is completed propery or not and the new database exists or not.