MySQL Cookbook (2007)

---

^[1^] If you don’t want to enter the complete text of the INSERT statements (and I don’t blame you), skip ahead to Repeating and Editing SQL Statements for a shortcut. If you don’t want to type in any of the statements, skip ahead to Telling mysql to Read Statements from a File.

Starting and Stopping mysql

Problem

You want to start and stop the mysql program.

Solution

Invoke mysql from your command prompt to start it, specifying any connection parameters that may be necessary. To leave mysql, use a QUIT statement.

Discussion

To start the mysql program, try just typing its name at your command-line prompt. If mysql starts up correctly, you’ll see a short message, followed by a mysql> prompt that indicates the program is ready to accept statements. To illustrate, here’s what the welcome message looks like (to save space, I won’t show it in any further examples):

%mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 18427 to server version: 5.0.27-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

If you invoke mysql and you get an error message that it cannot be found or is an invalid command, that means your command interpreter doesn’t know where mysql is installed. See What to Do if mysql Cannot Be Found for instructions on setting the PATH environment variable that your command interpreter uses to find commands.

If mysql tries to start but exits immediately with an “access denied” message, you need to specify connection parameters. The most commonly needed parameters are the host to connect to (the host that runs the MySQL server), your MySQL username, and a password. For example:

%mysql -h localhost -p -u cbuser

Enter password: cbpass

If you don’t have a MySQL username and password, you must obtain permission to use the MySQL server, as described earlier in Setting Up a MySQL User Account.

The way you specify connection parameters for mysql also applies to other MySQL programs such as mysqldump and mysqladmin. For example, to generate a dump file named cookbook.sql that contains a backup of the tables in the cookbook database, execute mysqldump like this:

%mysqldump -h localhost -p -u cbuser cookbook > cookbook.sql

Enter password: cbpass

Some operations require an administrative MySQL account. The mysqladmin program can perform operations that are available only to the MySQL root account, so you need to invoke it as follows:

%mysqladmin -p -u root shutdown

Enter password: ← enter MySQL root account password here

In general, I’ll show commands for MySQL programs in examples with no connection parameter options. I assume that you’ll supply any parameters that you need, either on the command line or in an option file (Specifying Connection Parameters Using Option Files) so that you don’t have to type them each time you invoke mysql, mysqldump, and so forth.

The syntax and default values for the connection parameter options are shown in the following table. These options have both a single-dash short form and a double-dash long form.

Parameter type	Option syntax forms	Default value
Hostname	-h hostname	localhost
	--host = hostname
Username	-u username	Your login name
	--user = username
Password	-p	None
	--password

If the value that you use for an option is the same as its default value, you can omit the option. However, as the table indicates, there is no default password. To supply one, use a -p or --password option, and then enter your password when mysql prompts you for it:

%mysql -p

Enter password: ← enter your password here

If you like, you can specify the password directly on the command line by using -p password (note that there is no space after the -p) or --password = password. I don’t recommend doing this on a multiple-user machine, because the password may be visible to other users who are running tools, such as ps, that report process information.

To terminate a mysql session, issue a QUIT command:

mysql>QUIT

You can also terminate the session by issuing an EXIT command or (under Unix) by typing Ctrl-D.

THE MEANING OF LOCALHOST IN MYSQL

One of the parameters you specify when connecting to a MySQL server is the host on which the server is running. Most programs treat the hostname localhost and the IP address 127.0.0.1 as synonyms for “the local host.” Under Unix, MySQL programs behave differently: by convention, they treat the hostname localhost specially and attempt to connect to the local server using a Unix domain socket file. To force a TCP/IP connection to the local server, use the IP address 127.0.0.1 rather than the hostname localhost. Alternatively, specify a --protocol=tcp option to force use of TCP/IP for connecting.

The default port number is 3306 for TCP/IP connections. The pathname for the Unix domain socket varies, although it’s often /tmp/mysql.sock. To specify the socket file pathname explicitly, use a -S file_name or --socket= file_name option.

Specifying Connection Parameters Using Option Files

Problem

You don’t want to type connection parameters on the command line every time you invoke mysql or other MySQL programs.

Solution

Put the parameters in an option file.

Discussion

To avoid entering connection parameters manually, put them in an option file for mysql to read automatically. Under Unix, your personal option file is named .my.cnf in your home directory. There are also site-wide option files that administrators can use to specify parameters that apply globally to all users. You can use /etc/my.cnf or the my.cnf file in the MySQL installation directory. Under Windows, the option files you can use are the my.ini file in your MySQL installation directory (for example, C:\Program Files\MySQL\MySQL Server 5.0), my.ini in your Windows directory (this is something like C:\WINDOWS or C:\WINNT), or the C:\my.cnf file.

Windows Explorer might hide filename extensions when it displays files, so a file named my.ini or my.cnf may appear to be named just my. Your version of Windows may allow you to disable extension-hiding. Alternatively, issue a DIR command in a console window to see complete filenames.

The following example illustrates the format used to write MySQL option files:

# general client program connection options

[client]

host = localhost

user = cbuser

password = cbpass

# options specific to the mysql program

[mysql]

skip-auto-rehash

pager="/usr/bin/less -E" # specify pager for interactive mode

This format has the following general characteristics:

§ Lines are written in groups (or sections). The first line of a group specifies the group name within square brackets, and the remaining lines specify options associated with the group. The example file just shown has a [client] group and a [mysql] group. Within a group, write option lines in name=value format, where name corresponds to an option name (without leading dashes) and value is the option’s value. If an option doesn’t take any value (such as for the skip-auto-rehash option), list the name by itself with no trailing =value part.

§ In option files, only the long form of an option is allowed. This is in contrast to command lines, where options often can be specified using a short form or a long form. For example, on the command line, the hostname can be given using either -h hostname or --host = hostname. In an option file, only host= hostname is allowed.

§ In option files, spaces are allowed around the = that separates an option name and value. This contrasts with command lines, where no spaces around = are allowed.

§ If an option value contains spaces or other special characters, you can quote it using single or double quotes. The pager option illustrates this.

§ If you don’t need some particular parameter, just leave out the corresponding line. For example, if you normally connect to the default host (localhost), you don’t need any host line. On Unix, if your MySQL username is the same as your operating system login name, you can omit theuser line.

§ It’s common to use an option file to specify options for connection parameters (such as host, user, and password). However, the file can list options that have other purposes. The pager option shown for the [mysql] group specifies the paging program that mysql should use for displaying output in interactive mode. It has nothing to do with how the program connects to the server.

§ The usual option group for specifying client connection parameters is [client]. This group actually is used by all the standard MySQL clients. By listing an option in this group, you make it easier to invoke not just mysql, but also other programs such as mysqldump and mysqladmin.Just make sure that any option you put in this group is understood by all client programs. For example, if you put mysql-specific options such as skip-auto-rehash or pager in the [client] group, that will result in “unknown option” errors for all other programs that use the[client] group, and they won’t run properly.

§ You can define multiple groups in an option file. A common convention is for a program to look for parameters in the [client] group and in the group named for the program itself. This provides a convenient way to list general client parameters that you want all client programs to use, but you can still specify options that apply only to a particular program. The preceding sample option file illustrates this convention for the mysql program, which gets general connection parameters from the [client] group and also picks up the skip-auto-rehash and pager options from the [mysql] group.

§ If a parameter appears multiple times in an option file, the last value found takes precedence. Normally, you should list any program-specific groups following the [client] group so that if there is any overlap in the options set by the two groups, the more general options will be overridden by the program-specific values.

§ Lines beginning with # or ; characters are ignored as comments. Blank lines are ignored, too. # can be used to write comments at the end of option lines, as shown for the pager option.

§ Option files must be plain-text files. If you create an option file with a word processor that uses some nontext format by default, be sure to save the file explicitly as text. Windows users especially should take note of this.

§ Options that specify file or directory pathnames should be written using / as the pathname separator character, even under Windows, which uses \ as the pathname separator. Alternatively, write \ by doubling it as \\ (this is necessary because \ is the MySQL escape character in strings).

If you want to find out which options the mysql program will read from option files, use this command:

%mysql --print-defaults

You can also use the my_print_defaults utility, which takes as arguments the names of the option file groups that it should read. For example, mysql looks in both the [client] and [mysql] groups for options, so you can check which values it will read from option files by using this command:

%my_print_defaults client mysql

Protecting Option Files from Other Users

Problem

Your MySQL username and password are stored in your option file, and you don’t want other users to be able to read that file.

Solution

Set the file’s mode to make it accessible only by you.

Discussion

On a multiple-user operating system such as Unix, you should protect your option file to prevent other users from reading it and finding out how to connect to MySQL using your account. Use chmod to make the file private by setting its mode to enable access only by yourself. Either of the following commands do this:

%chmod 600 .my.cnf

% chmod go-rwx .my.cnf

On Windows, you can use Windows Explorer to set file permissions.

Mixing Command-Line and Option File Parameters

Problem

You’d rather not store your MySQL password in an option file, but you don’t want to enter your username and server host manually.

Solution

Put the username and host in the option file, but not the password. Instead, specify the password interactively when you invoke the mysql program. mysql looks both in the option file and on the command line for connection parameters. If an option is specified in both places, the one on the command line takes precedence.

Discussion

mysql first reads your option file to see what connection parameters are listed there, and then checks the command line for additional parameters. This means you can specify some options one way, and some the other way. For example, you can list your username and hostname in an option file, but use a password option on the command line:

%mysql -p

Enter password: ← enter your password here

Command-line parameters take precedence over parameters found in your option file, so if for some reason you need to override an option file parameter, just specify it on the command line. For example, you might list your regular MySQL username and password in the option file for general-purpose use. If you need to connect on occasion as the MySQL root user, specify the user and password options on the command line to override the option file values:

%mysql -p -u root

Enter password: ← enter MySQL root account password here

To explicitly specify “no password” when there is a nonempty password in the option file, use -p on the command line, and then press Enter when mysql prompts you for the password:

%mysql -p

Enter password: ← press Enter here

What to Do if mysql Cannot Be Found

Problem

When you invoke mysql from the command line, your command interpreter can’t find it.

Solution

Add the directory where mysql is installed to your PATH setting. You’ll then be able to run mysql from any directory easily.

Discussion

If your shell or command interpreter can’t find mysql when you invoke it, you’ll see some sort of error message. It might look like this under Unix:

%mysql

mysql: Command not found.

Or like this under Windows:

C:\>mysql

Bad command or invalid filename

One way to tell your command interpreter where to find mysql is to type its full pathname each time you run it. The command might look like this under Unix:

%/usr/local/mysql/bin/mysql

Or like this under Windows:

C:\>"C:\Program Files\MySQL\MySQL Server 5.0\bin\mysql"

Typing long pathnames gets tiresome pretty quickly. You can avoid the need to do so by changing location into the directory where mysql is installed before you run it. However, I recommend that you not do that. If you do, you’ll be tempted to put all your datafiles and SQL batch files in the same directory as mysql, thus unnecessarily cluttering up what should be a location intended only for programs.

A better solution is to make sure that the directory where mysql is installed is included in the value of the PATH environment variable that lists pathnames of directories where the command interpreter looks for commands. You can then invoke mysql from any directory by entering just its name, and your command interpreter will be able to find it. This eliminates a lot of unnecessary pathname typing.

An additional significant benefit is that because you can easily run mysql from anywhere, you won’t need to put your datafiles in the directory where mysql is located. When you don’t have to run mysql from a particular location, you’re free to organize your files in a way that makes sense to you, not in a way imposed by some artificial necessity. For example, you can create a directory under your home directory for each database you have and put the work files associated with a given database in the appropriate directory.

For instructions on setting your PATH variable, see Appendix B.

I’ve pointed out the importance of the PATH search path variable here because I have seen many questions from people who aren’t aware of the existence of such a thing, and who consequently try to do all their MySQL-related work in the bin directory where mysql is installed. This seems particularly common among Windows users.

On Windows, another way to avoid typing the pathname or changing into the mysql directory is to create a shortcut and place it in a more convenient location such as the desktop. This makes it easy to start mysql just by opening the shortcut. To specify command options or the startup directory, edit the shortcut’s properties. If you don’t always invoke mysql with the same options, it might be useful to create one shortcut for each set of options you need. For example, create one shortcut to connect as an ordinary user for general work and another to connect as the MySQLroot user for administrative purposes.

Issuing SQL Statements

Problem

You’ve started mysql, and now you want to send SQL statements to the MySQL server to be executed.

Solution

Just type them in, but be sure to let mysql know where each one ends.

Discussion

When you invoke mysql, it displays a mysql> prompt to tell you that it’s ready for input. To issue a SQL statement at the mysql> prompt, type it in, add a semicolon (;) at the end to signify the end of the statement, and press Enter. An explicit statement terminator is necessary; mysql doesn’t interpret Enter as a terminator because you can enter a statement using multiple input lines. The semicolon is the most common terminator, but you can also use \g (“go”) as a synonym for the semicolon. Thus, the following examples are equivalent ways of issuing the same statement, even though they are entered differently and terminated differently:

mysql>SELECT NOW();

+---------------------+

| NOW() |

+---------------------+

| 2005-11-15 16:18:10 |

+---------------------+

mysql> SELECT

-> NOW()\g

+---------------------+

| NOW() |

+---------------------+

| 2005-11-15 16:18:18 |

+---------------------+

Notice that for the second statement the prompt changes from mysql> to -> on the second input line. mysql changes the prompt this way to let you know that it’s still waiting to see the statement terminator.

Be sure to understand that neither the ; character nor the \g sequence that serve as statement terminators are part of the statement itself. They’re conventions used by the mysql program, which recognizes these terminators and strips them from the input before sending the statement to the MySQL server. It’s important to remember this when you write your own programs that send statements to the server (as we’ll begin to do in the next chapter). In such programs, you don’t include any terminator characters; the end of the statement string itself signifies the end of the statement. In fact, adding a terminator may well cause the statement to fail with an error.

See Also

mysql also can read statements from a file or from another program. See Recipes and .

Canceling a Partially Entered Statement

Problem

You start to enter a statement, and then decide not to issue it after all.

Solution

Cancel the statement using your line-kill character or the \c sequence.

Discussion

If you change your mind about issuing a statement that you’re entering, cancel it. If the statement is on a single line, backspace over it or use your line-kill character to erase the entire line. (The particular line-kill character to use depends on your terminal setup; for me, and most Unix/Linux users, the character is Ctrl-U; Windows users should press Esc.)

If you’ve entered a statement over multiple lines, the line-kill character erases only the last line. To cancel the statement completely, enter \c, and press Enter. This returns you to the mysql> prompt:

mysql>SELECT *

-> FROM limbs

-> ORDER BY\c

mysql>

Sometimes \c appears to do nothing (that is, the mysql> prompt does not reappear), which leads to the sense that you’re “trapped” in a statement and can’t escape. If \c is ineffective, the cause usually is that you began typing a quoted string and haven’t yet entered the matching end quote that terminates the string. Let mysql’s prompt help you figure out what to do here:

§ If the prompt has changed from mysql> to ">, That means mysql is looking for a terminating double quote. If the prompt is '> or `> instead, mysql is looking for a terminating single quote or backtick. Type the appropriate matching quote to end the string, and then type \c followed by Enter.

§ If the prompt is /*>, you’re in the middle of typing a C-style /* ... */ comment. End the comment by typing */, and then type \c followed by Enter.

Repeating and Editing SQL Statements

Problem

The statement you just entered contains an error, and you want to fix it without typing the whole thing again. Or you want to repeat an earlier statement without retyping it.

Solution

Use mysql’s built-in input-line editing capabilities.

Discussion

If you issue a long statement only to find that it contains a syntax error, what should you do? Type in the entire corrected statement from scratch? No need: mysql maintains a statement history and supports input-line editing. This enables you to recall statements so that you can modify and reissue them easily.

There are many, many editing functions, but most people tend to use a small set of commands for the majority of their editing. A basic set of useful commands is shown in the following table. Typically, you use Up Arrow to recall the previous line, Left Arrow and Right Arrow to move around within the line, and Backspace or Delete to erase characters. To add new characters to the line, just move the cursor to the appropriate spot, and type them in. When you’re done editing, press Enter to issue the statement (the cursor need not be at the end of the line when you do this).

Editing key	Effect of key
Up Arrow	Scroll up through statement history
Down Arrow	Scroll down through statement history
Left Arrow	Move left within line
Right Arrow	Move right within line
Ctrl-A	Move to beginning of line
Ctrl-E	Move to end of line
Backspace	Delete previous character
Ctrl-D	Delete character under cursor

On Windows, the arrow key and Backspace editing functions are available as described in the table, Home and End take the place of Ctrl-A and Ctrl-E, and pressing F7 gives you a menu of recent commands.

Input-line editing is useful for more than just fixing mistakes. You can use it to try variant forms of a statement without retyping the entire thing each time. It’s also handy for entering a series of similar statements. For example, if you want to use the statement history to issue the series ofINSERT statements shown earlier in Creating a Database and a Sample Table to create the limbs table, first enter the initial INSERT statement. Then, to issue each successive statement, press the Up Arrow key to recall the previous statement with the cursor at the end, backspace back through the column values to erase them, enter the new values, and press Enter.

The input-line editing capabilities in mysql are based on the GNU Readline library. You can read its documentation to find out more about the many editing functions that are available. Readline documentation is part of the bash manual, which is available online athttp://www.gnu.org/manual/.

Using Auto-Completion for Database and Table Names

Problem

You wish there was a way to type database and table names more quickly.

Solution

There is: use mysql’s name auto-completion facility.

Discussion

Normally, when you use mysql interactively, it reads the list of database names and the names of the tables and columns in your default (current) database when it starts up. mysql remembers this information to provide name-completion capabilities that are useful for entering statements with fewer keystrokes:

1. Type in a partial database, table, or column name, and then hit the Tab key.

2. If the partial name is unique, mysql completes it for you. Otherwise, you can hit Tab again to see the possible matches.

3. Enter additional characters, and hit Tab again once to complete it or twice to see the new set of matches.

mysql’s name auto-completion capability is based on the table names in the default database, and thus is not available within a mysql session until you select a database, either on the command line or with a USE statement.

Auto-completion enables you to cut down the amount of typing you do. However, if you don’t use this feature, reading name-completion information from the MySQL server may be counterproductive because it can cause mysql to start more slowly when you have a lot of tables in your database. To tell mysql not to read this information so that it starts up more quickly, specify the -A (or --skip-auto-rehash) option on the mysql command line. Alternatively, put a skip-auto-rehash line in the [mysql] group of your MySQL option file:

[mysql]

skip-auto-rehash

To force mysql to read name completion information even if it was invoked in no-completion mode, issue a REHASH or \# command at the mysql> prompt.

Telling mysql to Read Statements from a File

Problem

You want mysql to read statements stored in a file so that you don’t have to enter them manually.

Solution

Redirect mysql’s input, or use the SOURCE command.

Discussion

By default, the mysql program reads input interactively from the terminal, but you can feed it statements in batch mode using other input sources such as a file, another program, or the command arguments. You can also use copy and paste as a source of statement input. This section discusses how to read statements from a file. The next few sections discuss how to take input from other sources.

To create an SQL script for mysql to execute in batch mode, put your statements in a text file. Then invoke mysql, and redirect its input to read from that file:

%mysql cookbook <

filename

Statements that are read from an input file substitute for what you’d normally type in by hand, so they must be terminated with semicolons (or \g), just as if you were entering them manually. One difference between interactive and batch modes is the default output style. For interactive mode, the default is tabular (boxed) format. For batch mode, the default is to delimit column values with tabs. However, you can select whichever output style you want using the appropriate command option. See Selecting Tabular or Tab-Delimited Query Output Format.

Batch mode is convenient when you need to issue a given set of statements on multiple occasions because you need not enter them manually each time. For example, batch mode makes it easy to set up cron jobs that run with no user intervention. SQL scripts also are useful for distributing statements to other people. That is, in fact, how SQL examples from this book are distributed. Many of the examples shown here can be run using script files that are available in the accompanying recipes source distribution. (See Appendix A.) You can feed these files to mysql in batch mode to avoid typing statements yourself. For example, when a recipe shows a CREATE TABLE statement that describes what a particular table looks like, you’ll usually find an SQL batch file in the recipes distribution that can be used to create (and perhaps load data into) the table. Recall that Creating a Database and a Sample Table showed the statements for creating and populating the limbs table. Those statements were shown as you would enter them manually, but the recipes distribution includes a limbs.sql file that contains statements to do the same thing. The file looks like this:

DROP TABLE IF EXISTS limbs;

CREATE TABLE limbs

(

thing VARCHAR(20), # what the thing is

legs INT, # number of legs it has

arms INT # number of arms it has

);

INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);

INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);

INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);

INSERT INTO limbs (thing,legs,arms) VALUES('octopus',0,8);

INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);

INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);

INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);

INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);

INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);

INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);

INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);

INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

To execute the statements in this SQL script file in batch mode, change location into the tables directory of the recipes distribution that contains the table-creation scripts, and then run this command:

%mysql cookbook < limbs.sql

You’ll note that the script contains a statement to drop the table if it exists before creating the table anew and loading it with data. That enables you to experiment with the table without worrying about changing its contents because you can restore the table to its baseline state any time by running the script again.

The command just shown illustrates how to specify an input file for mysql on the command line. Alternatively, you can read a file of SQL statements from within a mysql session using a SOURCE filename command (or \. filename, which is synonymous). Suppose that the SQL script filetest.sql contains the following statements:

SELECT NOW();

SELECT COUNT(*) FROM limbs;

You can execute that file from within mysql as follows:

mysql>SOURCE test.sql;

+---------------------+

| NOW() |

+---------------------+

| 2006-07-04 10:35:08 |

+---------------------+

1 row in set (0.00 sec)

+----------+

| COUNT(*) |

+----------+

| 12 |

+----------+

1 row in set (0.01 sec)

SQL scripts can themselves include SOURCE or \. commands to include other scripts. This gives you additional flexibility but also raises the danger that it’s possible to create a source loop. Normally, you should take care to avoid such loops. If you’re feeling mischievous and want to create a loop deliberately to find out how deep mysql can nest input files, here’s how to do it. First, issue the following two statements manually to create a counter table to keep track of the source file depth and initialize the nesting level to zero:

mysql>CREATE TABLE counter (depth INT);

mysql> INSERT INTO counter SET depth = 0;

Then create a script file loop.sql that contains the following lines (be sure each line ends with a semicolon):

UPDATE counter SET depth = depth + 1;

SELECT depth FROM counter;

SOURCE loop.sql;

Finally, invoke mysql, and issue a SOURCE command to read the script file:

%mysql cookbook

mysql> SOURCE loop.sql;

The first two statements in loop.sql increment the nesting counter and display the current depth value. In the third statement, loop.sql sources itself, thus creating an input loop. You’ll see the output whiz by, with the counter display incrementing each time through the loop. Eventually mysqlwill run out of file descriptors and stop with an error:

ERROR:

Failed to open file 'loop.sql', error: 24

What is error 24? Find out by using MySQL’s perror (print error) utility:

%perror 24

OS error code 24: Too many open files

In other words, you have hit the limit imposed by the operating system on the number of open files you can have.

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 formysql. 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.