MySQL Database Backups

Omdat mysql tabellen worden in bestanden bewaard het is gemakkelijk om backup van te maken.
Gebruik FLUSH TABLES om te zorgen dat niets geschreven wordt tijdens kopiëren.

Om sql kopie te nemen gebruik SELECT INTO … OUTFILE. Het is niet mogelijk om dergelijke bestand iets toe te voegen, het wordt iedere keer overgeschreven. Zie ook SELECT
Voor incrematal backups moet uw server gestart worden met optie –log-bin dan kunt u mysqldump –flush-logs toepassen.
Nog een andere techniek om backup te maken is mysqldump program. (mysqlhotcopy)
mysqlhotcopy werkt alleen voor MyISAM types. InnoDB worden niet gekopieerd.

Let op: Server moet stopgezet worden voordat een backup wordt uitgevoerd.

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables; see Section 7.13, “mysqldump — A Database Backup Program”.

If your MySQL server is a slave replication server, then regardless of the backup method you choose, you should also back up the master.info and relay-log.info files when you back up your slave’s data. These files are always needed to resume replication after you restore the slave’s data. If your slave is subject to replicating LOAD DATA INFILE commands, you should also back up any SQL_LOAD-* files that may exist in the directory specified by the –slave-load-tmpdir option. (This location defaults to the value of the tmpdir variable if not specified.) The slave needs these files to resume replication of any interrupted LOAD DATA INFILE operations.

MySQL Enterprise. The MySQL Enterprise Monitor provides numerous advisors that issue immediate warnings should replication issues arise. For more information see http://www.mysql.com/products/enterprise/advisors.html.

If you have to restore MyISAM tables, try to recover them using REPAIR TABLE or myisamchk -r first. That should work in 99.9% of all cases. If myisamchk fails, try the following procedure. Note that it works only if you have enabled binary logging by starting MySQL with the –log-bin option.

1.

Restore the original mysqldump backup, or binary backup.
2.

Execute the following command to re-run the updates in the binary logs:

shell> mysqlbinlog binlog.[0-9]* | mysql

In some cases, you may want to re-run only certain binary logs, from certain positions (usually you want to re-run all binary logs from the date of the restored backup, excepting possibly some incorrect statements). See Section 7.11, “mysqlbinlog — Utility for Processing Binary Log Files”, for more information on the mysqlbinlog utility and how to use it.

You can also make selective backups of individual files:

*

To dump the table, use SELECT * INTO OUTFILE ‘file_name’ FROM tbl_name.
*

To reload the table, use LOAD DATA INFILE ‘file_name’ REPLACE …. To avoid duplicate rows, the table must have a PRIMARY KEY or a UNIQUE index. The REPLACE keyword causes old rows to be replaced with new ones when a new row duplicates an old row on a unique key value.

If you have performance problems with your server while making backups, one strategy that can help is to set up replication and perform backups on the slave rather than on the master. See Chapter 15, Replication.

If you are using a Veritas filesystem, you can make a backup like this:

1.

From a client program, execute FLUSH TABLES WITH READ LOCK.
2.

From another shell, execute mount vxfs snapshot.
3.

From the first client, execute UNLOCK TABLES.
4.

Copy files from the snapshot.
5.

Unmount the snapshot.

SELECT * INTO OUTFILE ‘gids_search.sql’ FROM tbl_pagina WHERE gids = ‘j’ ORDER BY ci DES