<<

. 97
( 132 .)



>>


Once you™re in a database you can run a SQL statement by typing it in, followed
by a semicolon, \g, or \G, and hitting Return/Enter. When a query is terminated
with a semicolon or \g the utility will return any result in tabular format; when a
query is terminated with \G MySQL will give the listing in vertical format. The fol-
lowing listing demonstrates the difference:

mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.01 sec)

mysql> show databases \G
*************************** 1. row ***************************
Appendix D: MySQL Utilities 585

Database: mysql
*************************** 2. row ***************************
Database: test
2 rows in set (0.00 sec)

The vertical output can be helpful if you™re feeding the output of your queries to
another program, such as a Perl script, for processing, because this form is easier to
parse. Alternatively, you can use the “B option to format things horizontally, but
without the character-based line art.
Table D-1 lists some of the more useful command-line options.


TABLE D-1 COMMON mysql COMMAND-LINE CLIENT OPTIONS, PART I

Flag Alternate Flag Description

Displays help.
-? --help

Prints tab-delimited results, with each row
-B --batch
on a new line.
Specifies a database to use.
-D, --database=..

Executes a command and quits.
-e --execute=...

Prints output vertically.
-E --vertical

Gives the order to continue regardless of
-f --force
SQL errors.
Connects to a server.
-h --host=...

Generates HTML output.
-H --html

Gives the order not to write line numbers for
-L --skip-line-numbers
errors.
Flushes buffer after query.
-n --unbuffered

Specifies the password to use when
-p[password] --password[=...]
connecting to the server.
Specifies the TCP/IP port to use for the
-P --port=...
connection.
Gives the order not to cache the result, but
-q --quick
to print it row by row.
Writes column values without escape
-r --raw
conversion.
Continued
586 Part V: Appendixes


TABLE D-1 COMMON mysql COMMAND-LINE CLIENT OPTIONS, PART I (Continued)

Flag Alternate Flag Description

Sends this SQL statement to the MySQL
--safe-mode
server when the connection is opened:
SET SQL_SAFE_UPDATES=1,
SQL_SELECT_LIMIT=#select_limit#,

SQL_MAX_JOIN_SIZE=
#max_join_size#”

#select_limit# and #max_join_size#
are variables that you can specify on the
mysql command line.

Specifies that the output is to be in table
-t --table
format.
Specifies the user to be used for login.
-u --user=#

Specifies the order to wait and retry instead
-w --wait
of aborting if the connection is down.
Formats output as XML.
-X -XML




The effect of the --safe-mode flag command is as follows:

— You are not allowed to do an UPDATE or DELETE if you don™t have a key
constraint in the WHERE portion. You can, however, force an
UPDATE/DELETE by using LIMIT:

UPDATE table_name SET not_key_column=™some value™ WHERE
not_key_column=™some value™ LIMIT 1;

— All big results are automatically limited to #select_limit# rows.

— SELECTs that will probably need to examine more than #max_join_size
row combinations will be aborted.



mysqladmin
You can use mysqladmin, a command-line tool, for performing all sorts of admin-
istrative tasks on a MySQL server ” everything from simple tasks like creating new
databases to getting detailed information on the status of your MySQL server.
Appendix D: MySQL Utilities 587

mysqladmin takes a number of fairly generic flags that you can apply to any of the
processes you™ll see below. These flags, like -u, -p, and -h (which indicate user-
name, password, and host, respectively), show up in about every MySQL client. You
can get a listing of these flags by running mysqladmin --help.

Basic administrative commands
Many of the tasks that you can perform with the MySQL command-line client can
also be performed with mysqladmin. The primary advantage of using mysqladmin
for tasks like creating databases and setting passwords is that they can be entered
from the shell or through a shell script without your having to worry about enter-
ing and exiting the command-line client. (Or, you can always use mysql with the
“e option. That way, commands are standardized for you.)
From mysqladmin you can perform the following basic administrative functions:
create a database, drop a database, and change a password. The following examples
show how to perform each of these actions. (Note that for each of the examples,
we™re performing the actions as the MySQL root user.)

shell> mysqladmin -u root -p create store2
shell> mysqladmin -u root -p drop store2

The first command creates a database named store2 and the second one drops
the same database. Note that the drop command will return a warning and ask you
to confirm the drop action:

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the ˜store2™ database [y/N]

This sort of response is fine, but it™s something you want to avoid in an admin-
istrative shell script. You can ask mysqladmin to drop the database without confir-
mation by applying the -f or --force flag:

shell> mysqladmin -u root -p drop -f store2

You can also change passwords from mysqladmin. The following command
changes the password for the MySQL root user from mypass to hispass.

shell> ./mysqladmin -u root -pmypass password hispass


Status-information commands
mysqladmin provides a means of determining the status and performance of your
database server.
588 Part V: Appendixes

mysqladmin ping
For starters, you can check that a MySQL daemon is alive and running, using
mysqladmin ping. If the command is successful it will return the message mysqld
is alive. If it fails you will get an error message. Most often you will want to use
mysqladmin ping when trying to establish that a connection is possible from a
host other than localhost. In order to test the connection properly you™ll need to
supply a host name, username, and password. Within your MySQL installation
you™ll need to make sure that a user with that name from that host is allowed.

shell> mysqladmin -u jay -pmypass -h mysqlserver.myhost.com ping
mysqld is alive



If you simply want to find out whether the MySQL port is active, you can use
a simple telnet client. If MySQL is running on the default port (3306) you can
run telnet with the command:
telnet mysqlhost.myhost.com 3306
If the daemon is running you will get a response. You won™t be able to con-
nect through telnet, but you will get a response rather than a timeout.



mysqladmin version
You can get some interesting information about MySQL by running mysqladmin
version:

shell> mysqladmin version
Server version 4.0.0-alpha
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 1 hour 15 min 10 sec

Threads: 73 Questions: 7168998 Slow queries: 119 Opens: 34482
Flush tables: 1 Open tables: 64 Queries per second avg: 29.983

The beginning of the listing shows very basic information. Below that, starting
with Threads, you get some interesting information. Note that you can get just the
information at the bottom of this listing (from Threads to Queries per second
avg) plus some other goodies by running mysqladmin status.
Appendix D: MySQL Utilities 589

mysqladmin status
This command can give you some very important information:

/mysqladmin status -u root -pmypass
Enter password:
Uptime: 239352 Threads: 68 Questions: 7175296 Slow queries: 119
Opens: 34509 Flush tables: 1 Open tables: 64 Queries per second
avg: 29.978

In this listing, you get the following information:

— Uptime ” Number of seconds MySQL has been running

— Threads ” Number of clients connected to MySQL

— Questions ” Number of queries processed since the MySQL daemon was
started
— Slow queries ” Number of queries that have taken longer to process than
the value of the variable long_query_time
— Opens ” The number of table opens MySQL has performed

— Flush tables ” Number of flush, refresh, and reload commands run

— Open table ” Number of tables open when mysqladmin status was run

— Queries per second avg ” A simple measurement of the queries run
divided by the time the server has been running ” not a measure of
your server™s performance

You can look at this information and start to make some decisions. If you have
a lot of slow queries, it may be time to restart the server and log slow queries so
you can see exactly what queries are problematic (see Chapter 4 of the MySQL
manual concerning the slow query log). If you find that the number of active
threads is often close to the maximum number allowed by the max_connections
variable, you might want to increase the number of available threads. If the Opens
item is very large (as it is in the previous listing), you should think about increas-
ing the value of the table_cache variable.
Beyond these items, you can get far more information from your MySQL instal-
lation by running mysqladmin extended-status. This command returns a very
long listing of system variables. You can get the definition for each of these items
from the MySQL manual: http://www.mysql.com/doc/S/H/SHOW_STATUS.html.

mysqladmin processlist
Using mysqladmin you can also view all the server™s active threads by running
mysqladmin processlist. For example, the following listing contains five threads

<<

. 97
( 132 .)



>>