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
can use SELECT INTO OUTFILE
.
Another way is to use the mysqldump
script.
mysqldump --tab=some-dir
--lock-tables
or simply by copying all table files (.frm, .ISM and
.ISD) while the server isn't updating anything.
mysqld
with --log-update
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
table. 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.