mysql Chapter Two (Commands)

Documentation Version: 0.80
mysql Version: 3.20.16



The mysql database system comes with the following core programs and scripts. The following utilities are also provided. These are not vital to the function of mysql, but provide useful additional functionality.



The mysql client program.


 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.

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

$ 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    |


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.


 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.


mysqlaccess OPTIONS [host] [user] [db]


 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.
-u, --user=... Username to use for logging into database.
-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. 

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


Perform administrative functions.


 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.
-\?, --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.
-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 it's tables).
processlist Show information on running mysql threads.
reload Reread configuration information and flush all caches.
shutdown Shutdown the mysql database system.
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.


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.

 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/' and 'german/'.
-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-unsafe-select Skip possibly unsafe select optimizations.
--socket= [socket] The socket file to use for connecting.
-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.

 For information on optimizing the performance of mysqld see the section in chapter 6 titled Tuning.

Running mysqld (At System Boot)


Copy the mysql.server script from the mysql bin directory and into /etc/init.d.

 Verify all paths in the mysql.server script. Also check safe_mysqld.

 ln /etc/init.d/mysql.server /etc/rc2.d/S99mysql.server

 You should be set.


[Somebody else will have to write this as I don't have a Linux system handy and am not familiar enough to want to risk making a guess.]

Running mysqld (After System Boot)

The mysqld program can be run as any user. Generally it is run as root. If it is run as some other user please be sure to set all file permissions appropriately.

 su to whatever user owns mysql and type the following command. (This assumes that the mysql binaries are in your path. If not, give the full path to the mysql binary directory)

 safe_mysql &


Dump the contents of a database.


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.

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


mysqladmin create foo
mysqldump mysql | mysql foo


Show information about a server, database or table.


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.

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

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 and get statistics on mysql tables.


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 it's much quicker because it 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)

-#  Output debug information to log. Generally 'd:t:o,filename`. See Appendix C for further details.
-? Display usage information and exit.
-a Analyze.
-d Description.
-e Extended check. (When running with this option by itself you shutdown the mysqld daemon.)
-f Force tmpfile.
-k# Keys-used.
-i Extra information.
-q Quick check.
-r[o] Recover.
-s Silent.
-u Unpack packed file.
-v Verbose output.
-w Wait if locked.
-I Same as -?.
-S[ir]# Sort index / records according to key #
-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.

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.

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


Get statistics and make use of isam log files.


isamlog [-?iruvIV] [-c #] [-f #] [-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.

 This command can also be used to replicate a database. Note that the ISAM log files can get VERY large.

 The following switches are supported by isamlog.
-? 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.


 -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.
-r  Ignore all errors encountered during updates. 
-R  Specify a position in ISAM file to get information about.


 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.


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.

Non Core


Do initial conversion of mSQL program/script to mysql.


msql2mysql [filename]


 The msql2mysql script can be used as a first step in porting msql 1.x 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.


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


Get short text explanation of numeric error code.


 perror [-?vIV] [errorcodes]


 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. 



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.

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

 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.


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.


Kill process that match a pattern.


zap [-signal] [-?Ift] pattern


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


prompt> zap -t "my"
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.