Linux mysql command

The mysql command is a simple shell for SQL (Structured Query Language) 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.

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.

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

mysql> SELECT CHAR(92);

CHAR(92)\\

mysql -s -r

mysql> SELECT CHAR(92);

CHAR(92)\

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

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:		hope@localhost
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
--------------

Related commands

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