Basic MySQL Commands

• 2018/11/03

Basic MySQL operations

• Log in to MySQL:

mysql -u {user_name} -p{password} -h {host_name}

You can simply run the mysql client and type the password, but this form lets you log in from any shell. You will probably use it more often. If you changed the DB port, add -P {port} (the default is 3306).

• List databases:

SHOW DATABASES;

• Select the database to use:

USE {db_name};

• List tables (after running USE):

SHOW TABLES;

Inspecting definitions

• Show database info:

DESC {db_name};

DESC is shorthand for DESCRIBE.

• Show table info:

SHOW TABLE STATUS LIKE '{table_name}';

The LIKE clause accepts wildcards such as %, so you can search flexibly—not only exact table names.

• Check parameters:

SHOW VARIABLES STATUS LIKE '{parameter_name}';

• Show global status:

SHOW GLOBAL STATUS LIKE '{parameter_name}';

• List stored procedures:

SHOW PROCEDURE STATUS;

• Check the file format:

SHOW GLOBAL VARIABLES LIKE 'innodb_file_format';

Dump-related commands

• Dump one database:

mysqldump -u {user_name} -p{password} -h {host_name} -B {database_name} > {output_file_name}

• Dump every database:

mysqldump -u {user_name} -p{password} -h {host_name} -A > {output_file_name}

• Restore a dump:

mysql -u {user_name} -p{password} -h {host_name} < {output_file_name}