You can interact with your MySQL Client via command line. While there are many commands that allow you to manipulate databases and functions within them for MySQL, this article will walk you through some of the more common commands and what they do. Here you will find:

The MySQL Prompt
Providing Credentials
Providing a Database
mysqlcheck
Database and Table Commands
mysqladmin

To view all MySQL commands, see the documentation on the MySQL website.

The MySQL Prompt

To reach the MySQL Client by command line, type mysql in your terminal. This is part of the MySQL client tools, and just requires the MySQL client package – not the server package.

[root@host ~] mysql

You will see the following output that confirms you are in the MySQL Client:

Welcome to the MySQL monitor. Commands end with ; or g. 
Your MySQL connection id is 85910 Server version: 5.6.34 MySQL 
Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. 
All rights reserved. Oracle is a registered trademark of Oracle Corporation 
and/or its affiliates. Other names may be trademarks of their 
respective owners. Type 'help;' or 'h' for help. Type 'c' to clear
the current input statement.

You will see the MySQL prompt instead of [root@host ~]#, it will show:

mysql>

You can list out the MySQL commands:

If you mistype a query, you can clear it by using the following:

To exit out of MySQL:

Providing Credentials

There are four flags related to the MySQL command line client that relate to credentials.

Provide a username to log into MySQL with:-u
Prompt for a password (or provide the password after the command, and echo it to bash history. You can also use this flag, then hit enter without entering a password to send no password:-p
Provide an IP or hostname for the MySQL server to connect the client to: -h
Provide a port for the MySQL server to connect to:-P

Note:

If you don’t specify these options at the command line when connecting, the MySQL client checks a few places for them. It checks ~/.my.cnf for settings for these last, and if there’s nothing there it will use what it picked up from /etc/my.cnf for these settings on a lower priority. If it does not find these settings anywhere, the MySQL client connects by default with either the current Linux username, no password, to a socket at /tmp/mysql.sock.

Provide a Database

The traditional format of the MySQL command line client is:

mysql [OPTIONS] [database]

This means you can specify a database for your command by simply providing the database name, and not associating it with any flags. If you are entering interactive mode, this will be equivalent to entering the MySQL client and “using” that database. You can do the same through the -D flag.

–execute

It is possible to execute a query in MySQL just like the MySQL prompt without entering interactive mode. This is what the -e flag is for. Whatever is in the quotes after the -e is run as a query in MySQL. This is useful if you want normal MySQL output without sticking in the MySQL client.

–batch

you can use -B to run a command through MySQL in batch mode. This is somewhat similar to -e, but more useful for scripting.

–version

The -V flag for MySQL will report the version number of the MySQL client. This is not the MySQL version number for the server.

Example:

mysql -Bse "show databases;"

This will provide you with a list of databases that you can then modify or run scripts on.

mysqlcheck

The MySQL repair, sorting, and checking tool. This is used to check tables from within the MySQL server. This is part of the MySQL client tools, and just requires the MySQL client package.

General Format of mysqlcheck

mysqlcheck database table

–all-databases

This will run a process across all the databases on a server and across all of the tables. (-A flag) With this command, you don’t specify a database or table.

–databases

This flag (-B flag) allows you to specify a list of databases to modify, but you cannot specify tables.

–check

This flag checks the table for errors. (-c flag).

–check-upgrade

Like the -c flag, this flag (-g flag) checks tables, but it checks for version-dependent updates. This is used when upgrading MySQL, it should run automatically during the upgrade process.

–repair

The repair flag (-r flag), is used to run a repair on tables that have errors. The only error that cannot be fixed is unique keys that are not unique, and bad table structure.

–use-frm

When used with repair, reads the table structure from the .frm then repairs based on that. Useful in cases of corrupt .MYI.

–optimize

This optimizes the table. (-o flag) In some cases, running two alter table statements to convert to another engine then back may be faster, alternately creating a new table then importing all the rows into it may be faster. Most importantly, you need to know that a table is badly fragmented before running this command. Running this blindly may cause extended downtime, and may not give much long term benefit.

Info:

InnoDB naturally keeps it’s tables in ideal order. MyISAM tables can get fragmented, but rarely do, the sequential inserts have to have an out of order primary key.

Examples

Example 1:

mysqlcheck -Asc

This checks all tables in all databases, and only reports errors.

Example 2:

mysqlcheck -r roundcube

This tries to repair all tables in the roundcube database. However, There’s not a lot that a mysqlcheck repair will do for a InnoDB table, and since a couple of tables in roundcube are InnoDB, those may give you a notification that they cannot be repaired.

Example 3:

mysqlcheck -Aors

This will run for a time. It will, for all tables in all databases, repair the table if it is marked as crashed, and run an optimize on each table. It will report only on errors.

Example 4:

myisamchk

This utility is used to do offline checks and repairs of MyISAM table files. This means you cannot use this while MySQL is running.

Warning:

This is exclusively a server side tool, and must be compiled with/against the MySQL source.
Typically, you will use:

myisamchk -fU tablename.MYI
To repair very badly corrupted MyISAM tables

Example 5:

mysqldump

mysqldump is a utility designed solely to dump data out of your MySQL server. Typically, this is used to create a SQL file that contains the commands to recreate your tables and the import the data into the tables. This is with a process similar to describe statements, select statements, and show statements.

Note:

If you pass the INFORMATION_SCHEMA database to mysqldump, it is silently ignored.
Syntax is: mysqldump [options] database This is a binary that is independent from the MySQL server, however is often included with the MySQL server package. The typical format of a mysqldump for a table is:

Comment with the table name Drop table statement 
Create table statement Lock table Disable keys 
Large Insert statements to insert all the rows into 
the table all at once. May be multiple statements. 
Reenable keys (indexes are built at this time, and 
only once, on the table). Unlock the table Comment 
with the table name to designate the end

Database and Table Commands

–add-drop-database

The addition of this option adds in a drop database statement, and create database statement, before doing anything with any tables within that database in your dump.

–add-drop-table

This is the default behavior in MySQL and adds the drop table statement before each CREATE TABLE statement.

–skip-add-drop-table

This will not include the Drop table line, and will change the Create table to Create table IF NOT EXISTS.

Warning:

–skip-add-drop-table can cause issues if used incorrectly. When importing a table that was exported like this, the table is not dropped first. So, the table in the live MySQL server is not necessarily empty. When the INSERT statements run a bit later, you might be inserting the same data, or two incompatible sets of data. Or, inserting rows that have conflicting unique keys. To correct this issue, use the –add-drop-table flag.

–add-locks

This will surround each table dump with LOCK TABLES and UNLOCK TABLES statements.

–skip-add-locks

Using this command will keep the locks from surrounding the table dump which can allow changes to be made during an import and cause issues with your database.

–disable-keys/–skip-disable-keys

If you don’t disable the keys while importing data, the indexes are rebuilt with every INSERT statement. If you have 20 giant INSERT statements for a table, this rebuilds the index 20 times. Instead, you can disable the keys and rebuild it once.

–extended-insert/–skip-extended-insert

This option, on by default, is the one that makes your INSERT statement in a mysqldump be one line, or few lines. It bunches multiple rows up into one INSERT. This affects the output side only. However, multiple queries to do the same thing is going to incur an overhead, and slow down the import a bit. This will also make the mysqldump be more lines.

–single-transaction

Useful for InnoDB large tables. Normally, mysqldump obtains a read-lock on the entire table, and keeps it while dumping out the data. On compatible engines, this suppresses that behavior, and instead uses a transaction statement to dump out the entire table at a given state without the table changing, while allowing other queries to change the table at the same time. –single-transaction paired with –quick is a good idea for large tables.

mysqladmin

This utility is used for administering a MySQL server. With this utility, you can monitor the server, create new databases, and do lots of cool stuff.

This is packaged with the mysql server package typically, although this is a client tool and can be used to remotely administer a database. The typical authentication flags (similar to the mysql client) work.

–sleep

This flag (-i flag) can be used to repeat the command on an interval.

Warning:

If not paired with the –count flag, the command will keep running until the command is cancelled.

Examples

Example 1:

mysqladmin version

Using this command returns the version of the MySQL server. This is the version of the mysql server that you connect to. It is not the same command as running mysql -V.

mysqladmin proc stat

-proc and stat are two commands run together to give you a list of running processes in MySQL, or queries, as well as simple statistics on your MySQL server at the end. This looks like:

+--------+------+-----------+----+---------+------+-------
+------------------+ | Id | User | Host | db | Command | 
Time | State | Info | +--------+------+-----------+----+---------
+------+-------+------------------+ | 106847 | root | localhost | 
| Query | 0 | | show processlist | +--------+------+-----------+----
+---------+------+-------+------------------+ Uptime: 6963573 
Threads: 1 Questions: 282495 Slow queries: 3 
Opens: 67250 Flush tables: 1 Open tables: 64 
Queries per second avg: 0.041

mysqladmin create database_name

This creates a new database in MySQL, with the name of database. The drop command works the same way.

mysqladmin extended-status

This outputs something more similar to the show status command within MySQL.

mysqladmin password

This updates the root user at localhost to the new password. You will need to have access to run this, so you might have to provide the password with -p still.

mysqladmin ping

Checks to see if the server is alive.

mysqladmin reload

Similar to running flush privileges in mysql.

mysqladmin refresh

Flushes all tables to disk, and closes and reopens all log files.

System Process Descriptions

mysqld and mysql_safe and mysqld_safe

mysqld is the binary for the MySQL server itself, and two different names for the angel process. mysql_safe and mysqld_safe are two different names for the angel process – it restarts MySQL when it crashes and should be restarted.

This utility, on most MySQL server deployments, is a Bash script written to launch and manage the mysqld process. This script handles all interaction with mysqld. For the init system, an init script actually calls this script and has this script start MySQL. You should never have to interact with this directly. Whenever you start MySQL, you should be starting it through an init script or via systemd.

Was this article helpful?

Thank you for your input.

Thank you for your feedback.