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

such rows to be skipped. If neither option is given and such a duplicate is found, an

error occurs and the rest of the file is skipped. (Note that if you drop the table before

importing, this situation is unlikely to arise).

ƒ -s, --silent Silences mysqlimport's commentary, and it will write messages to

the screen only when errors occur.

ƒ --fields-terminated-by='delimeter' Specifies the delimiter used as a

separator after each field, the default being '\t' (tab).

ƒ --fields-enclosed-by='delimeter' Specifies the delimiter used to

enclose each field.

ƒ --fields-optionally-enclosed-by='delimeter' Specifies the delimiter

enclosing each CHAR or VARCHAR type field.

ƒ --fields-escaped-by='escape_char' Specifies the escape_char placed

before any special character, the default being '\\' (amounting to one backslash).

ƒ --lines-terminated-by='delimeter' Specifies the line delimiter, the default

being '\n' (newline).

ƒ -?, --help Shows the full list of options for mysqlimport and exits.

Note Note that this is not an exhaustive list of mysqlimport options. Type

mysqlimport --help for a full list of options.

For example, suppose that you're importing the data from the Customers table in the example book

database:

mysqlimport -u root book customers.txt

This would import the data from the file into the table and produce a short output to confirm what has

been done:

book.customers: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

Logging Transactions

MySQL gives you some handy ways of logging transactions on your database. Look at why you might want
to do this and how.

Using Update Logging to Assist a Backup Strategy

As outlined earlier, you may want to have a backup strategy that involves a combination of full and
incremental backups.

Each of the incremental (daily) backups might be kept on the server's hard disk, and only the full

(weekly) backups would be transferred to some other media. You would keep each day's incremental

backup (rather than overwriting the one from the previous day). Then, if a full restore of the database is

ever needed, the administrator would first restore from the most recent full backup, and then apply the

updates from the most recent incremental backup.

Such a strategy means that incremental backups are never more than a day old and keeps storage

requirements to a minimum, in addition to giving you a full off-system backup once a week.

Although you would use mysqldump to create your full backup, you would use another tool for creating

the incremental backups. By starting the mysqld daemon with the --log-update option, you can tell

MySQL to keep a log of every change to the database. This creates an update log in the form of SQL

queries that can be re-run to replicate the updates that occurred.

Logs are named hostname.n where n is a number that is incremented each time a new log is started.

A new log will be started whenever you issue any of the following:

ƒ mysqladmin refresh

ƒ mysqladmin flush-logs

ƒ mysqldump --flush-logs with option

ƒ FLUSH LOGS

ƒ a server start-up or restart

With the update log file looking like a list of SQL statements (similar to a dump), the administrator can

also selectively restore only certain updates, if need be.

Thus, the following would be the sequence of events: