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.
If you have downloaded the database as an SQL file then you can just import it using the command prompt and the mysql command.
Do it in these simple steps:
- Make sure the SQL file is unpacked as a text file with SQL commands inside
- Verify you're able to login to MySQL using the command prompt. You can simply type
# mysql -p -u username database_name. You'll be asked for password and, if correct, you'll be accepted to your database.
- If you haven't created any new database to unpack your backup file in you can do it now (inside the MySQL command prompt) by typing
create database MyNewDatabase;
- Now that you know you can login to the database you can "feed" the downloaded SQL file by changing the command in this way:
# mysql -p -u username database_name < /tmp/download/db_backup.sql. Make sure you're not inside the MySQL command prompt but out in the normal command prompt when typing this command. You can simply type exit or quit to leave the MySQL command prompt.
- The database will then be imported into your local database. If it's a big database running this last command might take quite some time.
Some things to think of:
- If you have a new installation and haven't added any users to MySQL then
root is the username to use. But it's always recommended to add a separate user account in MySQL that you can use from scripts ect.
- In my examples above I specified a database name. But if your backup script starts with
use someotherdb; then someotherdb will be used instead of the database name you specify in the mysql command. To get around this, either open up the SQL backup file and remove the first line with use, or, when you create your backup, untick the box saying Include using (or something similar).
- This is just how to install the database. You talked about "web site" also and that's a different thing. However, you need to update your website scripts to point to the new local database that you've created now.
Best Answer
Add a from_date field, and a nullable to_date field
expand your primary key to include from_date
keep everything in UTC
consider using PostgreSQL as there is a temporal extension for preventing overlapping time periods.
Here is the book from "THE" temporal db guy (Snodgrass):
http://www.cs.arizona.edu/~rts/tdbbook.pdf
Also try Chapter 4 of Celko's Smarties book