the same data that is considered an import on the other end. Instead of discussing the various ways
other databases export data, you'll learn how MySQL does it.Working with mysqldump
The mysqldump utility is a lot like its counterpart, the mysql import utility. It shares some of the common
options, but this utility does a little more. It takes the entire database and dumps it into a single text file. This
file contains all the SQL commands needed to recreate your database. It takes the schema and converts it to
the proper DDL syntax (CREATE statements), and it takes all the data and creates INSERT statements out of
them. This utility reverse engineers your database. Because everything is contained in a text file, it can all be
imported back into MySQL with one simple batch process and the proper SQL syntax. This is incredibly easy
and fast. There is no headache at all.
So, if you wanted to dump the entire contents of the Meet_A_Geek database into one file, you would
use the following command:
bin/mysqldump –p Meet_A_Geek > MeetAGeek_Dump_File.txt
This utility also allows you to specify a table you want to dump. If you only wanted to dump the entire
contents of the Orders table in the Meet_A_Geek database into a single file, you would use the
following command:
bin/mysqldump –p Meet_A_Geek Orders >MeetAGeek_Orders.txt
This utility is so flexible that it allows a WHERE clause to select only the records you want dumped into
the file. To do this, your command would look like the following:
bin/mysqldump –p
–where="Order_ID > 2000" Meet_A_Geek Orders > Special_Dump.txt
The mysqldump utility has a variety of options. They are all explained in Table 8.2.
Table 8.2 mysqldump Options
Option Action Performed--add-drop-table (^) This option will cause the mysqldump utility to add a DROP
TABLE IF EXISTS statement before every table. This will
ensure an error-free import back into a MySQL database. DROP
TABLE IF EXISTS causes MySQL to check and see if the
table exists; if it does, it is dropped.
--add-locks (^) This option causes the utility to wrap the INSERT statements
with a LOCK TABLE and UNLOCK TABLE statement. This
prevents users from doing anything to the table while these
records are being reintroduced to the database.
-c or -
complete_insert
This option causes the utility to name each column in the
INSERT statement. This can be useful when exporting this data
to another database.
--delayed-insert (^) This option causes the utility to use the DELAYED option in the
INSERT commands.
-F or -flush-logs
This option will flush the MySQL server log files before
executing the dump.
-f or -force
This option causes the utility to continue dumping, even if
errors occur.
--full (^) This option causes the utility to add additional information to the
CREATE TABLE statements. These are the optional statements
that were covered on Day 4, "Creating Your First Database."
-l or -lock-tables
This option causes the server to lock the tables that are being
dumped.
-t or -no-create-
info
This option will prevent the utility from writing any CREATE
TABLE statements. This can be handy if you only want the data
and not the DDL.
-d or -no-data (^) This option prevents the utility from writing any INSERT
statements. You would use this option if all you want is the
DDL.
--opt (^) This option turns on all the options that will speed up the