1.3 Command-Line Tools

You can interact with MySQL entirely from the command line. In general, each MySQL command accepts as an argument any appropriate option from the configuration options listed earlier. You prefix any such option with two dashes:

mysql --password=mypass

In addition, each of these options has a short form:

mysql -p mypass

To see which options apply to individual commands and their short forms, refer to the manpage for the command in question:

[23:00:03] george@firenze$ man -M/usr/local/mysql/man 
mysql
MYSQL(1)                                                 
MYSQL(1)
   
NAME
    mysql  -  text-based client for mysqld, a 
              SQL-based relational database daemon
   
SYNOPSIS
    mysql [-B|--batch] [-#|--debug= logfile] 
    [-T|--debug-info] [-e|--exec=  command] [-f|--force]
    [-?|--help] [-h|--host=hostname] [-n|--unbuffered]
    [-p[pwd]] [--password=[pwd]] [-P|--port=  pnum]
    [-q|--quick] [-r|--raw] [-s|--silent]
    [-S|--socket=  snum] [-u|--user=  uname]
    [-v|--verbose] [-V|--version] [-w|--wait]
   
DESCRIPTION
    The mysql program provides a curses-based interface to
    the SQL-based database server daemon, mysqld(1). Full
    fuller documentation, refer to the HTML documents 
    installed with the package.
   
OPTIONS
    -B|--batch
        Print results with a tab as separator, each row on

MySQL provides the following command-line tools:

isamchk

This tool verifies the integrity of your databases and potentially fixes any problems with them. It should be used only on ISAM tables.

msql2mysql

This utility is handy for people converting applications written for mSQL to MySQL. These days, however, few people need this help.

myisamchk

This tool does for MyISAM tables what isamchk does for ISAM tables.

mysql

The MySQL interactive SQL interpreter. It enables you to execute SQL on the command line. You can span your SQL across any number of lines. The tool executes your SQL when you terminate it with a semi-colon or the escape sequence \g.

mysqladmin

The MySQL administrative interface. Though many of this tool's functions can be accomplished using SQL and the mysql command-line utility, it nevertheless provides a quick way to perform an administrative task straight from the Unix command line without entering an SQL interpreter. You can specifically execute the following administrative commands:

create databasename

Creates the specified database.

drop databasename

The opposite of create, this command destroys the specified database.

extended-status

Provides an extended status message from the server.

flush-hosts

Flushes all cached hosts.

flush-logs

Flushes all logs.

flush-status

Flushes all status variables.

flush-tables

Flushes all tables.

flush-threads

Flushes the thread cache.

flush-privileges

Forces MySQL to reload all grant tables.

kill id[,id]

Kills the specified MySQL threads.

password new_password

Sets the password for the user to the specified new password. mysqladmin -u root password new_password should be the first thing you do with any new MySQL install.

ping

Verifies that mysqld is actually running.

processlist

Shows the active MySQL threads. You can kill these threads with the mysqladmin kill command.

reload

Reloads the grant tables.

refresh

Flushes all tables, closes all log files, then opens them again.

shutdown

Shuts MySQL down.

status

Shows an abbreviated server status.

variables

Prints out available variables.

version

Displays the server version information.

mysqlaccess

A command-line interface for managing users. This tool is basically a shortcut for the SQL GRANT command.

mysqld

The MySQL server process. You should never start this directly, instead use mysqld_safe (safe_mysqld in pre-4.0 versions).

mysqld_safe

The server process manager. Under MySQL versions prior to MySQL 4.0, this script is called safe_mysqld. It is a process that starts up the mysqld server process and restarts it should it crash. Note that the mysql.server startup script executes mysqld_safe as the appropriate user at server startup.

mysqldump

Dumps the state of a MySQL database or set of databases to a text file. You can later use this text file to restore the databases you dumped.

mysqlimport

Imports text files in a variety of formats into your database. It expects the base name (the name of the file without its extension) to match the name of the table to be used in the import.

mysqlshow

Displays the structure of the specified MySQL database objects. You can look at the structure of databases, tables, and columns.