MySQL Chapter Two (Commands)
Documentation Version: 0.95
MySQL Version: 3.20.29
Commands
The MySQL database system comes with the following core programs and
scripts.
- 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
The MySQL client program.
SYNOPSIS:
DESCRIPTION:
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.
The following switches are supported by the mysql program. You may use
either the "short" single character or more verbose versions.
| -\?, --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
At the shell prompt type the following:
$ mysql mysql </tmp/test
You'll get something like the following back.
host db select insert update delete create drop
localhost % Y Y Y Y Y Y
You can write some fairly complex SQL "programs" using this method.
Check user access rights.
SYNOPSIS:
mysqlaccess [host] [user] [db] OPTIONS
DESCRIPTION:
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.
The mysqlaccess script supports the following options. You may use either
the "short" single character or more verbose versions.
| -?, --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.)
Perform administrative functions.
SYNOPSIS:
mysqladmin [OPTIONS] command command...
DESCRIPTION:
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.
| -\?, --help |
| Print usage information. |
|
| -d, --debug=[options] |
| Output debug information to log. Generally 'd:t:o,filename`. See
Appendix C for further details. |
|
| -f, --force |
| Don't ask for confirmation when dropping a table. |
|
| -h, --host=[hostname] |
| Hostname if not localhost. |
|
| -i, --sleep=[seconds] |
| Execute commands over and over and sleep [seconds] in between. |
|
| -p, --password [password] |
| The password to use when connecting. Note there can be no space
between the -p and the password. |
| -u, --user=[user] |
| user to login as. Current login name will be assumed if user is
not provided. |
|
| -P, --port=[port] |
| The port to use when connecting to |
|
|
| -V, --version |
| Print version information and exit. |
|
In addition the mysqladmin program supports the following commands.
| 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.
The MySQL server program.
SYNOPSIS:
DESCRIPTION:
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.
The mysqld program provides the following options upon startup.
| -\?, --help |
Print usage information and exit. |
| -#, --debug=[options] |
Output debug information to log. Generally 'd:t:o,filename`. See
Appendix C for further details. |
| -b, --basedir=[path] |
Path to root of installation directory. |
| -h, --datadir [homedir] |
Path to your database directory. |
| -l, --log=[filename] |
The filename to log connections and queries to. |
| --log-isam=[filename] |
The filename to log isam changes to. |
| -O, --set-variable var=option |
Set option variable. (See below for more information.) |
| -L, --language=[language] |
Defaults to 'english/'. Can also be 'swedish/','germany/','french/'
or 'czech/'. For an up to date list check in the share/mysql
directory under the directory you installed MySQL in. |
| -P, --port=[port] |
The port to use when connecting to |
| -T, --debug-info |
Print debugging information on program exit. |
| --skip-new-routines |
Don't use newer possibly buggy routines. |
| --skip-grant-tables |
Ignore grant tables. This gives anyone COMPLETE access
to all tables. |
| --skip-locking |
Don't use system locking. May give better performance, but
should not be used in conjunction with isamchk. (IE, shut down
the server first.) |
| --skip-name-resolve |
This option will cause mysqld to only accept IP addresses
for hosts in the mysql privilege database. DNS is easily
spoofed, and thus should be avoided if you application
requires a higher than normal level of security. |
| --skip-networking |
Only allow connections via the localhost interface. This
option won't work with MIT threads. If connections to
your database will only be local then use of this option
will prevent others from making remote connections. |
| --skip-unsafe-select |
Skip possibly unsafe select optimizations. |
| --socket= [socket] |
The name to use for the MySQL socket file. Not available
when using a version of MySQL compiled with MIT threads.
EXAMPLE:
mysqld --socket=/tmp/mysql.sock |
| -V, --version |
Print out version information and exit. |
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.
Dump the contents of a database.
SYNOPSIS:
mysqldump [OPTIONS] [database [table [field]]]
DESCRIPTION:
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.
The mysqldump program supports the following options. (You may use either the
short or verbose versions.)
| -#, --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
Show information about a server, database or table.
SYNOPSIS:
mysqlshow [OPTIONS] [database [table [field]]]
DESCRIPTION:
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.
The mysqlshow program supports the following options. You may
use either the short or long option names.
| -#, --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.
Check, repair, manipulate and get statistics on MySQL tables.
SYNOPSIS:
isamchk [-?adeiqrsvwzIV] [-k #] [-O xxxx=size] [-Si] [-Sr #]
[-O keybuffer=#] [-O readbuffer=#] [-O writebuffer=#] [-O sortbuffer=#]
[-O sort_key_blocks=#] files
DESCRIPTION:
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]. |
Get statistics and make use of isam log files.
SYNOPSIS:
isamlog [-?iruvIV] [-c #] [-f #] [-p #] [-F filepath/] [-o #]
[-R file recordpos] [-w write_file] [log-filename]
DESCRIPTION:
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.
| -? or -I |
Get usage information. |
| -V |
Get version information. |
| -c # |
Do only # commands. |
| -f # |
Specify the maximum number of open files. If more than # files
must be open, files are automatically closed and re-opened as
necessary. |
| -F [path] |
The ISAM logfile contains the full path to tables. You may
use this option to override the stored path.
EXAMPLE:
-F '/var/mirror/'
Would cause isamlog to operate on files in /var/mirror rather than
those pointed to by the stored path. |
| -i |
Give more verbose information. |
| -o # |
Specify the offset at which to start. |
| -p # |
Remove # components from the path. |
| -r |
Ignore all errors encountered during updates. |
| -R |
Specify a position in ISAM file to get information about.
EXAMPLE:
isamlog -R /usr/local/data/mysql/user.ISM 1234
Would print all changes in the /usr/local/data/mysql/user table
that can be found at position 1234. This is useful for situations
in which you have a corrupted record at a known position. You may
have gotten this information by way of isamlog -vvv or from isamchk.
If you specify -1 as the position all records are printed. |
| -u |
Attempt to update all tables. Stop if an error is encountered. |
| -v |
Give more verbose output. You may specify one, two or three -v's for
correspondingly more verbose output. |
| -w [filename] |
Write all records inserted and updated matching the -R option to
[filename]. This option can be used to get a binary file of all inserts
and updates against a specific table for use in debugging. |
Script to start mysqld.
SYNOPSIS:
safe_mysqld [options to mysqld]
DESCRIPTION:
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.
Generate language specific error message files.
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
Do initial conversion of mSQL program/script to MySQL.
SYNOPSIS:
DESCRIPTION:
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.
File a bug report.
SYNOPSIS:
DESCRIPTION:
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].
Get short text explanation of numeric error code.
SYNOPSIS:
perror [-?vIV] [errorcodes]
DESCRIPTION:
The perror program provides short explanations of numeric error codes
returned by the system or MySQL.
The perror program supports the following options:
| -? or -I
| Print out usage information.
|
| -v
| Be more verbose.
|
| -V
| Print program version information.
|
SYNOPSIS:
replace [-?svIV] from to from to ... -- [files]
DESCRIPTION:
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.
The replace program supports the following options:
| -?
| 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.
Locate a command.
SYNOPSIS:
DESCRIPTION:
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.
Kill process that match a pattern.
SYNOPSIS:
zap [-signal] [-?Ift] pattern
DESCRIPTION:
By default zap will prompt you for confirmation on each
matching process.
EXAMPLE:
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
The zap command supports the following options.
| -I or -? |
Print usage information. |
| -f |
Send signal without prompting |
| -t |
Print out a list of processes that match but do not send
signal. |
