Learning Mysql

As we explained in Chapter 5 in Exploring Databases and Tables with SHOW and mysqlshow,” the text between the
/*! ... symbols contains MySQL-specific instructions. Notice several features in this dump file:
•
CREATE TABLE
statements for all tables in the database that are identical to the output of SHOW CREATE TABLE
•
DROP TABLE
statements that precede each CREATE TABLE
statement. These allow you to load the file into your MySQL database without error, even when the tables already exist of course, you’ll lose any data that may already be on the server in this table of the database.
•
INSERT
statements that add all of the data to the tables. There’s only such statement per table, that is, the rows are each parenthesized and comma-separated.
•
LOCK TABLES
and UNLOCK TABLES
statements. These ensure that you’re the only user modifying or using a table when you’re inserting the data, and they also speedup the inserts. We discuss locking briefly in Transactions and Locking in Chapter 7.
You’ll also notice two missing features There’s no CREATE DATABASE
statement to setup the database There’s no
USE
statement that selects the database.
Fortunately, you can use command-line parameters to customize what mysqldump does.
We’ll show you some examples next. You might find that your mysqldump output doesn’t exactly match what we’ve stated here, but don’t worry the defaults changeover time, and everything can be customized.
mysqldump Options
The mysqldump program has options to control whether tables should be locked when making the dump, whether restoring a dump should overwrite any existing tables, and soon. These options can be appended as parameters, just like the user and password options for the username and password, respectively. Here’s a list of the most useful options, but the default settings should be sufficient for most cases:
add-drop-table
Includes a DROP TABLE
statement for each table, ensuring that any existing table data is removed before the dump is restored.
add-locks
Includes a LOCK TABLES
statement before each data
INSERT
statement, and a corresponding UNLOCK TABLES
statement afterward. Helps speedup data restoration from the dump file.

Download 4.24 Mb.

Share with your friends: