Oracle 5.0, Reference Manual

Page: 647 / 3232

Reloading SQL-Format Backups
627
• For reloading, you can specify a database name different from the original name, which enables you
to reload the data into a different database.
• If the database to be reloaded does not exist, you must create it first.
• Because the output will contain no
CREATE DATABASE
statement, the
--add-drop-
database
[298] option has no effect. If you use it, it produces no
DROP DATABASE
statement.
To dump only specific tables from a database, name them on the command line following the database
name:
shell> mysqldump test t1 t3 t7 > dump.sql
7.4.2. Reloading SQL-Format Backups
To reload a dump file written by
mysqldump
that consists of SQL statements, use it as input to the
mysql
client. If the dump file was created by
mysqldump
with the
--all-databases
[298] or
--
databases
[299] option, it contains
CREATE DATABASE
and
USE
statements and it is not necessary
to specify a default database into which to load the data:
shell> mysql < dump.sql
Alternatively, from within
mysql
, use a
source
command:
mysql> source dump.sql
If the file is a single-database dump not containing
CREATE DATABASE
and
USE
statements, create the
database first (if necessary):
shell> mysqladmin create db1
Then specify the database name when you load the dump file:
shell> mysql db1 < dump.sql
Alternatively, from within
mysql
, create the database, select it as the default database, and load the
dump file:
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql
7.4.3. Dumping Data in Delimited-Text Format with
mysqldump
This section describes how to use
mysqldump
to create delimited-text dump files. For information
about reloading such dump files, see Section 7.4.4, “Reloading Delimited-Text Format Backups” .
If you invoke
mysqldump
with the
--tab=dir_name
[305] option, it uses
dir_name
as the output
directory and dumps tables individually in that directory using two files for each table. The table name
is the basename for these files. For a table named
t1
, the files are named
t1.sql
and
t1.txt
. The
.sql
file contains a
CREATE TABLE
statement for the table. The
.txt
file contains the table data, one
line per table row.
The following command dumps the contents of the
db1
database to files in the
/tmp
database:
shell> mysqldump --tab=/tmp db1
The
.txt
files containing table data are written by the server, so they are owned by the system
account used for running the server. The server uses
SELECT ... INTO OUTFILE
to write the files,
so you must have the
FILE
[577] privilege to perform this operation, and an error occurs if a given
.txt
file already exists.
The server sends the
CREATE
definitions for dumped tables to
mysqldump
, which writes them to
.sql
files. These files therefore are owned by the user who executes
mysqldump
.