The most general way to do database replication is using the update
log. See section The Update log. This requires that one database acts as a master
(all data changes are done here) and one or more others as slaves. To
update a slave just run
mysql < update_log.
If you never do deletes, you can use timestamps.
It is possible to make a two-way updating system using both the update log (for deletes) and timestamps (on both sides). But in that case you must be able to handle confilicts when the same data has been changed in both ends. You probably want to keep the old version to help with deciding what has been updated.
Since MySQL tables are stored as files it is easy to do a
backup. To get a consistent backup, do a
LOCK TABLES on the
relevant tables. See section
LOCK TABLES syntax. You only need a read lock so other
threads can continue to query the tables while making a copy of the files
in the database directory. Or if you want to make a SQL level backup you
SELECT INTO OUTFILE.
Another way is to use the
mysqldump --tab=some-dir --lock-tablesor simply by copying all table files (.frm, .ISM and .ISD) while the server isn't updating anything.
When you have to restore something (if
isamchk -r can't restore
all data as it can in 99.9% of all cases):
The ls in the last command is done to get all log files in the right order
You can also do selective backups with
select * into outfile from
table and restore with
LOAD DATA FROM INFILE 'file_name' REPLACE
.... To avoid duplicate records you need a
PRIMARY KEY in the
REPLACE means that if there is a 'duplicate index'
conflict when inserting new records the old record will be replaced with
the new one.
Go to the first, previous, next, last section, table of contents.