MySQL Backup Tools Summary

Comparison of Backup Tools

Supplement

Delimited file backups

To create a text file containing a table’s data, you can use SELECT * INTO OUTFILE 'file_name' FROM tbl_name. The file is created on the MySQL server host, not the client host. For this statement, the output file cannot already exist because permitting files to be overwritten constitutes a security risk. See SELECT Syntax. This method works for any kind of data file, but saves only table data, not the table structure.

Another way to create text data files (along with files containing CREATE TABLE statements for the backed up tables) is to use mysqldump with the --tab option. See Section 1.4.3, “Dumping Data in Delimited-Text Format with mysqldump”.

To reload a delimited-text data file, use LOAD DATA INFILE or mysqlimport.

backup

mysql> select * into outfile '/db/backup/table1.txt'
	 > fields terminated by ',' optionally enclosed by '"'
	 > lines terminated by '\n'
	 > from test.table1;

recovery

mysql> load data infile '/db/backup/table1.txt'
	 > into table test.table1
	 > fields terminated by ',' optionally enclosed by '"'
	 > lines terminated by '\n';

Reference

• High Performance MySQL Backup and Recovery
• MySQL Backup and Recovery
• mysqldump command reference
• mysqlhotcopy command reference
• mylvmbackup
• mk-parallel-dump
• ibbackup command reference

Published

Categories

• Database 7

Tags

• MySQL 3
• DBA 55

Github

Rendered Raw