The mysql command is a simple shell for SQL commands. With user interaction, it can enter commands at a special prompt, or run a batch script containing your SQL commands.

Description

When mysql is used interactively, query results are presented in a table format. When used non-interactively, the result is presented in tab-separated format. The output format can be changed using command options.

  • Description
  • Syntax
  • Examples
  • An introduction to MySQL
  • Related commands
  • Linux commands help

The simplest way to invoke mysql is to specify your MySQL username with the -u option, and to tell mysql to prompt you for your password with -p:

mysql -u username -p

You are shown a brief introduction message and then placed at the mysql> prompt.

At the mysql> prompt, enter MySQL commands, such as:

mysql> SHOW DATABASES;

To list the databases which exist, or:

mysql> USE dbname;

…to begin using the database named dbname, or:

mysql> SELECT 21 * 2 AS meaning_of_life;

…to display the mathematical product of 2 and 21.

To end your mysql session and return to the shell prompt, use the command:

mysql> QUIT

Running MySQL commands from a batch script

Instead of using mysql interactively, you can execute MySQL statements from a script file. For instance, if you have a text file named mysqlscript.txt containing MySQL commands, one per line, you could use this command:

mysql -u username -p db_name < mysqlscript.txt > output.txt

…and after prompting you for your password, mysql would execute the commands in mysqlscript.txt on the database db_name, writing the output to the file output.txt.

For an in-depth description of how to install MySQL on your system, and an overview of the basic interactive usage of mysql, see an introduction to MySQL.

Syntax

mysql [options] db_name

Options

mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file.

MySQL commands

mysql sends each SQL statement you issue to the server to be executed. Note that all text commands must first be online and end with ‘;’ .

DELETE FROM db2.t2;USE db2;DROP TABLE db1.t1;CREATE TABLE db1.t1 (i INT);USE db1;INSERT INTO t1 (i) VALUES(1);CREATE TABLE db2.t1 (j INT);

mysql

mysql> SELECT CHAR(92);

+———-+| CHAR(92) |+———-+| \ |+———-+

mysql -s

CHAR(92)\

mysql -s -r

CHAR(92)\

There is also a set of commands that mysql itself interprets. For a list of these commands, type help or \h at the mysql> prompt:

Examples

For an in-depth description of installing MySQL, and an overview of basic commands, see An introduction to MySQL.

The following are a few other notable commands:

mysqldump -u hope -p -h localhost hope_SMF > smf.sql

Backup the database “hope_SMF” to the smf.sql file after the username and password were verified.

mysql> status

Running status while at the mysql> prompt would give you MySQL status results similar to what is shown below.

mysql Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (i686) using readline 6.2 Connection id: 42 Current database: Current user: [email protected] SSL: Not in use Current pager: stdout Using outfile: ’’ Using delimiter: ; Server version: 5.5.35-0ubuntu0.13.10.2 (Ubuntu) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/run/mysqld/mysqld.sock Uptime: 2 hours 3 min 4 sec Threads: 1 Questions: 577 Slow queries: 0 Opens: 421 Flush tables: 1 Open tables: 41 Queries per second avg: 0.078

myisamchk — Check, repair, optimize, or fetch information about a MySQL database.mysqldump — A tool for backing up or transferring MySQL databases.