MySQL Cookbook, 2nd Edition

Telling mysql to Read Statements from Other Programs

Problem

You want to shove the output from another program into mysql.

Solution

Use a pipe .

Discussion

Telling mysql to Read Statements from a File used the following command to show how mysql can read SQL statements from a file:

%mysql cookbook < limbs.sql

mysql can also read a pipe, which means that it can receive output from other programs as its input. As a trivial example, the preceding command is equivalent to this one:

%cat limbs.sql | mysql cookbook

Before you tell me that I’ve qualified for this week’s “useless use of cat award,” ^[2] allow me to observe that you can substitute other commands for cat. The point is that any command that produces output consisting of semicolon-terminated SQL statements can be used as an input source for mysql. This can be useful in many ways. For example, the mysqldump utility generates database backups by writing a set of SQL statements that recreate the database. To process mysqldump output, feed it to mysql. This means you can use the combination of mysqldump and mysql to copy a database over the network to another MySQL server:

%mysqldump cookbook | mysql -h some.other.host.com cookbook

Program-generated SQL also can be useful when you need to populate a table with test data but don’t want to write the INSERT statements by hand. Instead, write a short program that generates the statements, and then send its output to mysql using a pipe:

%generate-test-data | mysql cookbook

See Also

Chapter 10 discusses mysqldump further.

---

^[2] Under Windows, the equivalent would be the “useless use of type award”:

C:\>type limbs.sql | mysql cookbook