This section describes how to use mysqldump to
produce dump files, and how to reload dump files. A dump file can
be used in several ways:
As a backup to enable data recovery in case of data loss.
As a source of data for setting up replication slaves.
As a source of data for experimentation:
To make a copy of a database that you can use without
changing the original data.
To test potential upgrade incompatibilities.
mysqldump produces two types of output,
depending on whether the --tab
option is given:
Without --tab,
mysqldump writes SQL statements to the
standard output. This output consists of
CREATE statements to create dumped objects
(databases, tables, stored routines, and so forth), and
INSERT statements to load data into tables.
The output can be saved in a file and reloaded later using
mysql to recreate the dumped objects.
Options are available to modify the format of the SQL
statements, and to control which objects are dumped.
With --tab,
mysqldump produces two output files for
each dumped table. The server writes one file as tab-delimited
text, one line per table row. This file is named
tbl_name.txt
in the output directory. The server also sends a
CREATE TABLE statement for the
table to mysqldump, which writes it as a
file named
tbl_name.sql
in the output directory.
There is an interesting script sample here: http://www.docplanet.org/linux/backing-up-linux-web-server-live-via-ssh/ showing a way to dump mysql databases directly into gzip and then into ssh connection, thus creating a gzipped dump archive that never resided on the server hard drive. This can be a handy way to ensure that backup does not fill up the server hard drive and enables you to backup your server even when the disk drive is near 100% full.
Posted by Jose Luis Palacios on June 11 2013 11:36am
© 2013, Oracle Corporation and/or its affiliates
User Comments
There is an interesting script sample here: http://www.docplanet.org/linux/backing-up-linux-web-server-live-via-ssh/
showing a way to dump mysql databases directly into gzip and then into ssh connection, thus creating a gzipped dump archive that never resided on the server hard drive. This can be a handy way to ensure that backup does not fill up the server hard drive and enables you to backup your server even when the disk drive is near 100% full.
mysqldump, with "--lock-all-tables" option, runs "FLUSH TABLES" / "FLUSH TABLES WITH READ LOCK" which purges entire query cache.
Add your own comment.