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 program.

Do a full backup of your databases, with mysqldump --tab=some-dir --lock-tables --quick or simply by copying all table files (.frm, .ISM and .ISD) while the server isn't updating anything.
• start mysqld with --log-update
• You will get log files with name of 'hostname'.# where # is a number that is increased for each reload.

When you have to restore something (if isamchk -r can't restore all data as it can in 99.9% of all cases):

• Restore the original mysqldump backup.
• ls -1 -t -r hostname.* | xargs cat | mysql

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.