'Dump' Function Has Several Uses

The "mysqldump" function has options for locking tables prior to dumping, compressing the data on the way out, flushing the logs, and many other useful features. For quickly backing up and copying databases, however, there's another even faster utility called "mysqlhotcopy." It locks and flushes the tables and copies them to another location on the server (it doesn't work across remote servers). The syntax is as follows:

shell> mysqlhotcopy --user username --password=pass db1 /var/db/mysql/backup

It's possible to access MySQL in a variety of environments, including within the MySQL monitor, from the command line, as part of a Perl program with the DBI module, or with an ODBC-enabled middleware application such as Allaire Cold Fusion. Here's how a simple query to retrieve the contents of a table called fruit in a database called food might operate in a few of those environments:

Using the MySQL monitor:

mysql> SELECT * FROM food.fruit;

+--------+--------+----------+
| name  | color | quantity |
+--------+--------+----------+
| apple | red  |    10 |
| banana | yellow |    15 |
| grape | green |    50 |
+--------+--------+----------+
3 rows in set (0.00 sec)

The shell command necessary to access MySQL will vary depending on how the particular database and its permissions are set up. It's possible to specify a user and a password in the command if necessary. To take the above query and output the results to a file, use the following command:

shell> mysql --user username --password=pass -e 'SELECT * FROM food.fruit' > fruit.txt

fruit.txt now contains the three rows of data (without the column headers or reporting information) in tab-delimited format.

Next: Help With the DBI Syntax Lifting