Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Lowe Justin 3 Hill Walk, Twin Creeks WI USA

Spokes Chris Red Fern House, Bradwell Oxford UK

Which Files Are Important

Your overall database design and its business application will dictate which parts of the database are most
important and how to deal with backing up or otherwise saving information.
For example, on a system that is largely read-only, it might be sufficient to do a backup once every few
weeks. You would probably do a mysqldump --opt, which saves all information (tables and data)
ready for a total restore if you ever need it.

On the other hand, you may have a "master-slave" database split across two locations. The "master"

location may be where all data-entry occurs, while the "slave" (perhaps on a Web server) may be the

system that users can query (to search a product catalog, for example). Here, there is not just a need

for a backup but also for copying the system on a regular basis from one location to the other.

In this second case, you may want to run mysqldump far more frequently, perhaps using the following,

which will populate another MySQL server:

mysqldump --opt database_name --hostname=other_host new_database_name

These example have both discussed full table and data backups. While a full backup is sufficient to

restore your database to full working order, these procedures for handling backups may not be the most

practical.

Full backups are often stored to tape, CD-ROM, or other media that can be taken off-site. Storing such

a large quantity of data each time can take up necessary storage space, and recovery from a mild

accident can be unnecessarily complicated. This is where an incremental backup can be useful.

An incremental backup stores all the changes made in the database since the last full backup. If it is

appropriate to make a full backup once a week, incremental backups can be done once a day, with

each incremental backup being stored locally on hard disk.

In a moment, you'll take a more detailed look at creating incremental backups by logging transaction

updates to the database.

Importing and Restoring Data

The mysqlimport utility reads a range of data formats, including tab-delimited, and inserts the data into the
givendatabase. Its syntax is as follows:

mysqlimport [options] databasefilename1filename2 ...

This can be used to restore a data dump created by using mysqldump --tab.

Like mysqladmin, it is invoked from the command line. It is really the same as the SQL statement

LOAD DATA INFILE.

The specified filenames (any number of them) must correspond with the tables that they will be used to

create. mysqlimport strips off any extension from each filename and applies the updates to the table

with that name.

You can run mysqlimport with the following options:

ƒ -d, --delete Empties the table before performing INSERTs as specified by the text

file.

ƒ -h host_name, --host host_name Imports data onto the specified host rather

than the default localhost.

ƒ -l, --lock-tables Locks all tables before importing the text files.

ƒ -u user_name, --user user_name Passes the MySQL username to use when

connecting to the server.

ƒ -p[your_password], -password[=your_password] Usually used in

association with u; passes the password to use when connecting to the server. Note

that there should be no space between -p and your_password. If you omit the

password, you will be prompted for it.

ƒ -r, --replace and -i, --ignore Allows you to control the import of rows that may

have unique key values. -r or --replace will cause imported rows to overwrite

existing rows if they have the same unique key value. -i or --ignore will cause