. 98
( 132 .)


active in the MySQL server, including the thread that is running mysqladmin.
590 Part V: Appendixes

shell> ./mysqladmin -u root -pmypass processlist
| Id | User | Host | db | Command | Time | State | Info
| 44 | jayg | localhost | | Sleep | 12359 | |
| 50 | jayg | localhost | store | Sleep | 12166 | |
| 77 | phps | localhost | store | Sleep | 22 | |
| 81 | root | localhost | mysql | Sleep |2 | |
| 82 | root | localhost | | Query |0 | | show
processlist |

You can get the same listing by logging in to the command-line client and run-
ning SHOW PROCESSLIST. (Note that you will need proper permissions to get a full
listing of threads.) The Process_priv column of the user grant table stores these
rights. If you don™t have rights in this column, running mysqladmin processlist
will show only the threads running with your username.

mysqladmin kill
Once you have a listing, you can end a process with mysqladmin. Try a command
like this:

shell> mysqladmin -u root -pmypass kill 44

Flush commands
The flush commands reload or reset files, tables, or logs. You probably won™t have
to use these commands very often, but they are important.

mysqladmin flush-hosts
The first flush command is mysqladmin flush-hosts. MySQL maintains a cached
table of all of the hosts connected to the MySQL server. If the connection from one
of the hosts becomes problematic, MySQL will register the errors. Once the number
of errors exceeds the value of the variable max_connect_errors, MySQL will cut
off access for that host. At that point MySQL will produce the error Host hostname
is blocked. Some sort of TCP/IP networking error normally causes these errors.
Appendix D: MySQL Utilities 591

After the networking error is fixed, MySQL will continue to block the given host
until the cached host tables are flushed. You can run this command from the
MySQL command-line client with the command FLUSH HOSTS.

If you want to completely do away with blocking errors, you can set
max_connection_errors to 99999999999.

mysqladmin flush-logs
The mysqladmin flush-logs command closes and reopens all log files. Logging is
a very important topic for database administrators, so if you™re performing that
function, see Chapter 4 of the MySQL manual regarding logging.

mysqladmin flush privileges
You can use mysqladmin flush-privileges to reload the grant tables you see in
Appendix E. Normally you won™t need to flush the grant tables. If you are using the
GRANT statements, changes in permissions will be updated immediately. However, if
you decide to change the GRANT tables with standard UPDATE, INSERT, and DELETE
statements, you will need to flush the grant tables. You can also run this command
from the command-line client with the command FLUSH PRIVILEGES.

mysqladmin flush-tables
The final flush command is mysqladmin flush-tables. This closes all open tables
with the MySQL installation. MySQL is a multi-threaded environment and will open
multiple copies of tables to serve different threads more efficiently. Running this
command will close all tables associated with all threads. You can run this command
from the command-line client with the command FLUSH TABLES.

On the accompanying CD is a file named /book/databases.sql. If you open that file
in a text editor, you will see a series of SQL statements that includes both CREATE
statements for tables and INSERT statements for table data. The commands look
something like this:

# MySQL dump 8.14
# Host: localhost Database: store
# Server version 4.01

592 Part V: Appendixes

# Table structure for table ˜addresses™
create database store;
use store;

CREATE TABLE addresses (
address_id int(11) NOT NULL auto_increment,
user_id int(11) default NULL,
place varchar(25) NOT NULL default ˜™,
addr_1 varchar(255) NOT NULL default ˜™,
addr_2 varchar(255) default NULL,
city varchar(50) NOT NULL default ˜™,
state char(2) NOT NULL default ˜™,
ZIP varchar(5) NOT NULL default ˜™,
country varchar(5) default NULL,
PRIMARY KEY (address_id)

# Dumping data for table ˜addresses™

INSERT INTO addresses VALUES (5,3,™home™,™1845 Fair Oaks™,™Apt
6™,™San Francisco™,™CA™,™92147™,™USA™);
INSERT INTO addresses VALUES (6,4,™home™,™30445 Haines St™,™Apt
8G™,™San Francisco™,™CA™,™92149™,™USA™);
INSERT INTO addresses VALUES (7,5,™home™,™8 Oak

It™s a piece of cake to run all of these commands in a batch by directing this file
into the command-line client.

shell> mysql -u root -pmypass < store.sql

The SQL statements in this file were created with the mysqldump utility. As you
can see from the .sql file, this utility exports both table-structure information
(create table statements) and table data (insert statements). By dumping the
entirety of databases and tables into files you will create a backup of your data and
make your data transportable.
The mysqldump utility is very flexible and can take a variety of flags that will
return the exact MySQL data you need in a variety of formats. You can see the full
set of mysqldump options by going to your mysql/bin directory and running
mysqldump --help. In this appendix we address only some of mysqldump™s more
commonly used options.
Appendix D: MySQL Utilities 593

The basic format for a mysqldump command is as follows:

shell> mysqldump databasename

Or, optionally, you can add a table name after the database name to dump struc-
ture and data from a single table. And as you might expect, you need to have proper
permissions to run mysqldump. Your mysqldump commands will look like one of the
following; the first dumps a database named store, and the second dumps only the
users table from that database. In the following statements, we™ve directed the out-
put to files (store.sql and users.sql).

shell> mysqldump -u root -pmypass store > ˜/store.sql
shell> mysqldump -u root -pmypass store users > ˜/users.sql

If you want to dump more than one database you can include additional data-
base names with the --databases flag; to dump all databases at once you can use
the --all-databases flag.

shell> mysqldump -u root -pmypass --databases store otherdb > ˜/dbs.sql
shell> mysqldump -u root -pmypass --all-databases > ˜/alldbs.sql

On Unix systems you can use mysqldump with pipes to achieve some complex
actions with a single command. The first command in the following code takes the
output of mysqldump, compresses it using gzip, and then outputs the compressed
data to a file. The second transfers data from one MySQL installation to another. It
connects the output of mysqldump on one server to the command-line client on
another host.

shell> mysqldump -u root -pmypass --all-databases | gzip > ˜/dbs.sql
shell> mysqldump -u root -pmypass store | mysql -u jay -pmypass -h

(Note: Don™t be misled by the appearance of the text here ” that last command
should be all one line.)

The MySQL manual recommends that when using mysqldump on DOS, you
should indicate the location of any output file using the --result-file
flag instead of the greater-than sign (>). So a sample command would look
like this:
dos> mysqldump -u root -pmypass --result-file=c:\
store.sql store
594 Part V: Appendixes

You should be aware of some other important flags:

— -c or --complete-insert: mysqldump outputs each row as an SQL
INSERT statement. But the INSERT statements do not use complete column
listings; instead they list the values in the order in which the columns
exist in the table. So in a table that has the following structure
id int not null primary key,
fname char(15)

you have INSERT statements that look like this:
INSERT INTO sample VALUES (1, ˜Jay Greenspan™);

This is potentially problematic if you™re transferring data to a table that
may be slightly different or that is using a different SQL server. In these
cases you want complete SQL statements like insert into sample (id,
fname) values (1, ˜Jay Greenspan™). The --complete-insert flag
gives insert statements in this form.
— -e or --extended-insert: When you use this flag, each table™s rows are
inserted in a single statement, with row values surrounded by parentheses
and separated by commas. Without this flag each row has its own INSERT
statement. The first command in the following code is what an INSERT
statement looks like when run with --extended-insert. The second and
third are statements that insert the same information and are created by
mysqldump if this flag is not used.

INSERT INTO SAMPLE VALUES (1, ˜jay™), (2, ˜john™);

— --delayed: In MySQL you can indicate to the MySQL engine that an
insert statement is of a relatively low priority by using the syntax
insert delayed. When MySQL sees one of these statements it puts the
statement in a queue rather than directly into the table if other insert or
update statements are coming from other clients. This is especially helpful
if other clients are inserting rows that must be available immediately. The
delayed row will wait for other updates to finish before data are written to
the actual table. Using insert delayed is a good idea when doing bulk
inserts as might be the case with rows taken from mysqldump.

— --add-drop-table: MySQL™s drop table command can return an error
if a drop is attempted but the table does not exist. You can use the com-
mand drop table if exists to avoid potential errors with the DROP
commands. This flag adds drop table if exists before every CREATE
statement. If you use this flag with mysqldump you can be sure that any
Appendix D: MySQL Utilities 595

recovery done from this dump will create a clean set of tables and erase
any data previously using those names in the tables.
— -t or no-create-info: Omits create table statements from the output.

— -d or no-data: Just dumps the create table statements with no row data.

— --lock-tables: Places a lock on all tables within a database before start-
ing a dump.
— --add-locks: Adds a lock tables before and an unlock tables com-
mand after each set of inserts. This allows the mysqldump thread to move
with the greatest speed because no other clients will be able to write to
the tables at the same time.
— --flush-logs or -f: Flushes the log files before starting a dump operation.

— -q or --quick: By default mysqldump writes rows to memory before forc-
ing the rows to standard output. This really isn™t necessary. By using this
flag you force mysqldump to deliver its contents directly to standard out-
put, thereby saving some memory.
— -w or --where: Enables you to put a where condition on what is dumped.
For example, from the store users table you can get a list of users with a
user_id of greater than 5 with the following code. (Note that each WHERE
condition must be put in quotes, and again, this is meant to be all on one
mysqldump -u root -pmypass --add-locks “-wuser_id>5” store

If you™re using mysqldump for backup, you™ll probably want to use the --opt
flag. This is the same as using --quick, --add-drop-table, --add-locks, --
extended-insert, or --lock-tables. So for regular backups you should probably
add something like the following command to your cron tab:

./mysqldump -u root -pmypass --opt --all-databases > /path/to/mybackup.sql


. 98
( 132 .)