• mysql
• mysqlaccess
• mysqladmin
• mysqld
• mysqldump
• mysqlshow
• isamchk
• isamlog
• safe_mysqld

The following utilities are also provided. These are not vital to the function of MySQL, but provide useful additional functionality.

• comp_err
• msql2mysql
• mysqlbug
• perror
• replace
• which1
• zap

SYNOPSIS:

mysql [OPTIONS] database

The client portion of the the MySQL database system is called MySQL. It provides a command line interface to the MySQL database engine, as well as a non interactive batching capability.

-\?, --help	Print usage information.
-d, --debug=[options]	Output debug information to log. Generally 'd:t:o,filename`. See Appendix C for further details.
-d, --debug-info	Print debug messages on program exit.
-e, --exec	Execute command and quit, --batch is implicit.
-f, --force	Continue even if we encounter a SQL error.
-h, --hostname=[hostname]	Must be followed by the name of the host you wish to connect to.
-P, --port=[port]	The port to use when connecting to the MySQL database engine.
-p, --password=[password]	Must be followed by the password of the user account you wish to connect with. Note there can be no space between the -p and the password.
-q, --quick	Quick (unbuffered output), may slow down the server if output is suspended.
-s, --silent	Tell me less please. (suppress output)
-u, --user=[user]	Must be followed by the name of the user account you wish to connect to the daemon with. Not needed if the account name is the same as your login.
-v, --verbose	Verbose output. The -v option may be doubled or tripled for more verbose output.
-w, --wait	Wait and retry if connection fails.
-B, --batch	Run in batch mode. (No prompt and no errors on STDOUT) This is automatic when reading from/to a pipe. Results will be output-ed in a tab separated format, one result line per line of output.
-I, --help	Same as -\?.
-V, --version	Print out version information and exit.

In interactive mode the mysql program will print results in a table like output similar to the following. If no password or username is provided, mysql will try to login to the database engine using your username and a NULL password. This will fail if you have a password, or your mysql login is different from your Unix login.

EXAMPLE:

$ mysql mysql Welcome to the mysql monitor. Commands ends with ; or \g. Type 'help' for help. mysql> select * from host; 1 rows in set (0.25 sec) +-----------+----+--------+--------+--------+--------+--------+------+ | host | db | select | insert | update | delete | create | drop | +-----------+----+--------+--------+--------+--------+--------+------+ | localhost | % | Y | Y | Y | Y | Y | Y | +-----------+----+--------+--------+--------+--------+--------+------+ mysql>

In the command line mode you should be able to cursor through the command line history and edit previous commands. This makes it easy to correct typos.

In batch mode results are returned as tab-separated fields.

EXAMPLE:

Create a file called /tmp/test that contains the following string

select * from host

$ mysql mysql </tmp/test

host    db      select  insert  update  delete  create  drop
localhost       %       Y       Y       Y       Y       Y       Y

SYNOPSIS:

mysqlaccess [host] [user] [db] OPTIONS

The mysqlaccess script is used to list the privileges of a user in regards to a specific database. It provides information that can be useful in diagnosing problems with user access to a given database. Remember, you must always do a 'mysqladmin reload' after making changes to the MySQL database tables.

-?, --help	Display help message.
-v, --version	Print version information.
-u, --user=...	Username to use for logging into database.
-p, --password=...	Password to use for logging into database. Note there can be no space between the -p and the password.
-h, --host=...	Name of host to use for host permissions check.
-d, --db=...	Name of database to use for database permissions check.
-U, --superuser=...	Name of superuser account.
-P, --spassword=...	Password for the superuser account.
-b, --brief	Print single-line tabular report.
--relnotes	Print release notes
--plan	Print suggestions/ideas for future releases
--howto	Print examples of how to run `mysqlaccess'
--debug=N	Enter debuglevel N (0..3)

You must specify at least the user and and db you wish to check. If no host is given 'localhost' will be assumed.

Wild-cards (*,?,%,_) are allowed when specifying host, user and db. Be sure to escape them from your shell. (Generally by way of either a back slash or quotes.)

SYNOPSIS:

mysqladmin [OPTIONS] command command...

The mysqladmin program is used to administer various aspects of the MySQL database system. The following switches are supported. You may use either the "short" single character or more verbose versions.

create [database name]	Create a database.
drop [database name]	Delete a database (And all its tables).
processlist	Show information on running MySQL threads.
reload	Reread configuration information and flush all caches.
shutdown	Shutdown the MySQL database system. All running sessions are marked as 'killed'. This means that any threads that are currently idle are closed immediately, and that running threads will be closed when they reach major cancelation points defined within the server. Clients will get a 'mysql server has gone away' error.
status	Print a short status message from the server.
version	Print out version information.

Note: mysqladmin accepts abbreviations. For example you could do the following:

mysqladmin v p

This would print out both the version of the running mysqld and the list of all currently active threads.

You can use the -i option to cause a command to be repeated every time [seconds] pass. In general this is most useful in conjunction with the processlist command.

Note that you will only be able to use the above commands if you have the appropriate privileges.

SYNOPSIS:

mysqld [OPTIONS]

The mysqld program is the core of the MySQL system. The mysqld program runs as a daemon on your system and accepts connections from client programs, executing queries and returning results. It is multi-threaded, meaning that it will accept and process more than one query at a time.

There may be some performance penalty on writes when using the --log-isam option as all writes to the log file must be synchronized. For sporadic writes, updates and deletes the overhead will be about five to ten percent. The overhead may be significantly higher on updates that require many simultaneous changes.

The --log-isam can be used to replicate a database. Note that the ISAM log files can get VERY large.

Each UPDATE, DELETE and INSERT will contain a thirteen byte header in addition to the command.

Each table open will take nine bytes plus the length of the filename of the table. (This is only necessary for tables not already in the table cache.)

In addition there will be eleven bytes of overhead for any commands that cache/lock tables. These events are generally caused by internal mysql commands. Most often when SELECT is used.

In most cases you'll want to run mysqld under the safe_mysqld script.

If the -h option is not specified mysql will assume that "/my/data/sql/mysql " is the home directory.

All databases are located in [homedir]/[database name].

The -l option should be used with caution. When used with a server that is doing a large volume of transactions this file can get large very quickly. If you do not specify a logfile name when using the -l option mysqld will write to [homedir]/[hostname].log.

The -O option allows you to specify values for the following options.

Name	Default
back_log	5
keybuffer	1048568
max_allowed_packet	65536
net_buffer_length	8192
max_connections	90
table_cache	64
recordbuffer	131072
sortbuffer	2097144
max_sort_length	1024

For information on what these values do, and optimizing the performance of mysqld see the section in chapter 6 titled Tuning.

SYNOPSIS:

mysqldump [OPTIONS] [database [table [field]]]

The mysqldump program is used to dump the contents of a MySQL database. It will write SQL statements to standard out. These SQL statements can be redirected to a file. It is possible to backup a MySQL database by use of mysqldump, though you should be sure that there is no activity going on while you do.

-#, --debug=[options]	Output debug information to log. Generally 'd:t:o,filename`. See Appendix C for further details.
-?, --help	Display usage information and exit.
-c, --compleat-insert	Generate complete insert statements. (Don't exclude values that conform to the column default.)
-h, --host=[hostname]	Connect to hostname.
-d, --no-data	Export only schema information. (Exclude data.)
-t, --no-create-info	Export only data, exclude table creation information. (The opposite of -d.)
-p, --password=[password]	The password to use when connecting to the server. Note there can be no space between the -p and the password.
-q, --quick	Don't buffer query results, dump directly to STDOUT.
-u, --user=[username]	Username for login. mysqldump will assume you wish to use your current login name if this option is excluded from the command line.
-v, --verbose	Print verbose information about the various execution stages of mysqldump.
-P, --port=[port]	The port to use when connecting to
-V, --version	Output mysqldump version information and exit.

You can pipe the output of mysqldump to the MySQL client program to replicate a database. (NOTE, you must be sure no updates are taking place when you do this, otherwise you will end up with an inconsistent replica.)

EXAMPLE:

mysqladmin create foo
mysqldump mysql | mysql foo

SYNOPSIS:

mysqlshow [OPTIONS] [database [table [field]]]

The mysqlshow program can be used to show what databases a MySQL database system is managing, what tables a given database contains, and what fields a table in a given database contains.

-#, --debug=[options]	Output debug information to log. Generally 'd:t:o,filename`. See Appendix C for further details.
-?, --help	Display usage information and exit.
-h, --host=[hostname]	Connect to hostname.
-k, --key	Print key(s) for table(s).
-p, --password=[password]	Password to use when connecting. Note there can be no space between the -p and the password.
-u, --user=[username]	Username to login to database as. If no username is specified is specified your current login name will be used.
-P, --port=[port]	The port to use when connecting to.
-V, --version	Output version information and exit.

mysqlshow with no arguments will show all databases. mysqlshow with the name of a database as a parameter will show all tables in that database. mysqlshow with the name of a database followed by the name of a table within that database will show the schema for that table.

If the last argument contains a '?' or a '*' then it is used as a wild-card.

EXAMPLE:

mysqlshow test 'a*'
gives a list of all tables in the database test that start with an 'a'.

mysqlshow is essentially identical to the mSQL msqlshow program. The MySQL database system provides similar functionality by way of the SQL SHOW and DESCRIBE commands.

SYNOPSIS:

isamchk [-?adeiqrsvwzIV] [-k #] [-O xxxx=size] [-Si] [-Sr #] [-O keybuffer=#] [-O readbuffer=#] [-O writebuffer=#] [-O sortbuffer=#] [-O sort_key_blocks=#] files

The isamchk program is used to check the consistency of a MySQL table and fix any problems it might find. The isamchk program can also be used to consolidate tables in databases that contain BLOBS or variable length character fields. This should only be necessary if you frequently add and remove records from these tables.

You are required to shutdown the MySQL database system before running the isamchk utility with the -r switch alone.

Generally you would use the -rq switch to repair a table because it does the repair 'in-place' ; No temporary space is needed, thus its much quicker since the isamchk program doesn't have to copy the data file.

You must cd into the directory that contains the table you are interested in checking. (Generally $DATADIR/DBNAME)

Options:

-#	Output debug information to log. Generally 'd:t:o,filename`. See Appendix C for further details.
-?	Display usage information and exit.
-a	Analyze. Use this option to optimize a tables layout. Need only be used once.
-d	Give information about the table.
-e	Extended check. (When running with this option by itself you should shutdown the mysqld daemon.)
-f	Force tmpfile. This option will cause an 'isamchk -r' ot be executed if the table is corrupt.
-k#	Keys-used.
-i	Extra information.
-q	Quick check.
-r[o]	Recover. This option will also compact the internal B-Tree indexes used by MySQL. Use this option to recover space.
-s	Silent.
-u	Unpack packed file.
-v	Verbose output. Used in conjunction with other options to get more information. Specify more v's for higher verbosity.
-w	Wait if table is locked.
-I	Same as -?.
-S[ir]#	Sort index / records according to key #. This will optimize the layout of data.
-V	Print version information and exit.
-O var=#	Optimize var=#[k][m]

You can use a second '-q' to force the use of an old datafile.

-ro uses old style recovery; Slower but safer than -r.

-r can fix almost anything except unique keys that aren't in fact unique.
BE SURE THE MYSQL DAEMON IS NOT RUNNING IF YOU USE THIS OPTION!

If -f is given on check then tables with errors are automatically fixed.

Here are some more examples. See Table Details for more verbose examples.

EXAMPLES:

isamchk -r [table_name]	(Clean up any holes that have formed because of the use of BLOBS or VARCHARS. Also fix any problems.)
isamchk -ei [table_name]	(Check table and show statistics.)
isamchk [table_name]	(Will find most errors.)
isamchk -rq [table_name]	(Update only the index file. Quick, but can't fix errors in the data file.)
isamchk -d -v [table_name]	(Describe a table.)
isamchk -rq -Si [table_name]	Do full optimization on table [table_name].

SYNOPSIS:

isamlog [-?iruvIV] [-c #] [-f #] [-p #] [-F filepath/] [-o #] [-R file recordpos] [-w write_file] [log-filename]

This program is used in conjunction with the --log-isam=file_name option of mysqld.

The file specified by file_name will contain a change log of all changes to all tables. The isamlog program can be used to get information about this file and recreate all tables and databases.

In order to restore a database you will need either a backup that contains your database up to the point in time when the mysqld generated ISAM log file was started, or a complete set of ISAM log files back to the beginning of your database.

SYNOPSIS:

safe_mysqld [options to mysqld]

This script is generally run at boot time to start mysqld. If you do not want your DATADIR to be placed under the same directory hierarchy as mysqld you should change DATADIR in this script.

SYNOPSIS:

comp_err [-?] [-I] [-V] fromfile[s] tofile

DESCRIPTION:

This program is used to compile text files that contain mappings of mysql error codes into a format that mysql can understand. You will only need to use this utility if you wish to generate codes for a new language or make corrections in an existing one.

EXAMPLE:

comp_err share/english/errmsg.txt share/english/errmsg.sys

SYNOPSIS:

msql2mysql [filename]

The msql2mysql script can be used as a first step in porting msql programs to MySQL. Simply type

msql2mysql something.c >something.mysql.c'

You will still have to do a fair amount of tweaking, but this is a good first step. See Appendix D for more details on porting mSQL code to MySQL.

The msql2mysql program is actually a fairly simple shell script that uses the replace command that comes with the MySQL database system to replace the mSQL function calls with their MySQL equivalents. Alas only the names are changed, Extra parameters are not added.

SYNOPSIS:

mysqlbug [address]

Please use this script when reporting bugs in MySQL. It will generate a form for you to fill out and put you in the whatever editor is $VISUAL is set to. (vi by default.) The form will contain automatically generated information about your system, including OS version, MySQL version and architecture. When you are finished the form will be mailed to the MySQL list. You may optionally specify an alternate address for your bug to be sent to by specifying [address].

SYNOPSIS:

perror [-?vIV] [errorcodes]

The perror program provides short explanations of numeric error codes returned by the system or MySQL.

-? or -I	Print out usage information.
-v	Be more verbose.
-V	Print program version information.

replace [-?svIV] from to from to ... -- [files]

The replace program is used by the msql2mysql shell script. Replace can be used to either replace a string in place, or to take piped input and replace specific strings while writing to STDOUT.

-?	Display usage information and exit.
-s	Be silent. (Suppress status messages.)
-v	Be verbose. (Give me extra status messages.)

EXAMPLES:

replace Apple Orange somefile

Would replace all occurrences of Apple with Orange in the file somefile.

cat INFILE | replace Apple Orange Blimp Train > OUTFILE

All occurrences of Apple in file INFILE would be replaced by Orange and output-ed to the file OUTFILE. At the same time, all occurrences of Blimp in the file INFILE will be changed to Train as well and written to the file OUTFILE.

You may also use the following special characters in your from strings.

\^	Match the start of a line.
\$	Match the end of a line.
\b	Match a space-character. May appear either by itself, or at the beginning or end of a token. If \b is used at the end of a token the next replace starts at the end of the space character. A \b alone matches only a space character.

Replace is a simple and highly useful utility with many potential uses external to MySQL.

SYNOPSIS:

which1 [cmd]

This command is probably only useful on systems that don't have which built into their default shell or available as a command. Prints only the first command found.

SYNOPSIS:

zap [-signal] [-?Ift] pattern

By default zap will prompt you for confirmation on each matching process.

prompt> zap -t "my"
UID   PID  PPID  C    STIME TTY      TIME CMD
root  1217     1  1 15:21:30 pts/4    0:00 /bin/sh /usr/local/pkg/mysql-3.20.15/bin/safe_mysqld
root  1224  1217  3 15:21:32 pts/4    0:01 /usr/local/pkg/mysql-3.20.15/libexec/mysqld -b /usr/local/pkg/mysql-3.20.15 -h 

-I or -?	Print usage information.
-f	Send signal without prompting
-t	Print out a list of processes that match but do not send signal.