Go to the first, previous, next, last section, table of contents.

Solving some common problems with MySQL

Database replication

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.

Backup of databases

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.

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.

Casa de Bender