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.
mysqldump should be just fine. Since you have only 3GB of data, I expect the mysqldump to be decently fast (1-2 min as a single output). However, since your database is comprised of 3 MyISAM tables expect all three to be locked during the backup. Please keep mind that since tables are locked one at a time, you will not have point-in-time consistency across all tables in the database.
I have some scripts I wrote and still use to perform
- parallel backups of separate databases
- parallel backups of separate tables
If you are concerned with consistent data, you may need to implement something from Option 4 of the link I provided which executes "FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);"
in a separate session to lock all writes to all tables, perform the mysqldump(s), and then kill that session holding the tables locks.
If you are afraid to try executing this, you should look into using MySQL Replication. A slave server with the same data can be used to perform mysqldumps without imposing anything disk I/O or overall server load on the master database. You basically do these things on the DB Slave:
STOP SLAVE;
- mysqldump
START SLAVE;
Give it a Try !!!
Best Answer
You can backup databases and tables using the mysqldump command. There are parameters for excluding certain tables.
If you need to get fast backups of MyISAM or ARCHIVE tables, you can also use mysqlhotcopy. That has more restrictions though.