Useful command line MySQL commands

It seems I have been doing a lot of command line work on Debian with MySQL recently and have been having to search the internet (or my bash history) every time I need to do something. Here is a collection of commands I have been using.

I have included "variables" within {curly_brackets} - these need to be replaced with database names/usernames/passwords (including the brackets)

MySQL Console

The following commands are to be run in the mysql console (e.g. sudo mysql)

 Show all the databases

show databases;

Create a database

CREATE DATABASE {db_name} CHARACTER SET utf8 COLLATE utf8_general_ci;

Create a user and allow access to database

Be sure to include the single quotes around {user_name}, localhost and the password

GRANT ALL PRIVILEGES ON {db_name}.* TO ' {user_name}'@'localhost' IDENTIFIED BY '{password}';
FLUSH PRIVILEGES;

Delete a database

DROP DATABASE {db_name};

Bash commands

These commands are run on your bash shell

Insert SQL dump from a file

The command assumes your file is called db.sql. The -p will ensure the prompt asks for your user SQL password.

mysql {db_name} -u {user_name} -p < db.sql

Dump a database to a file

This exports a database to a SQL file, so it can be imported (or used as a backup)

 sudo mysqldump -u {user_name} -p {db_name}  {optional: table} > db.sql

You can also specify one table with the above command, or chose to ignore a table. {option: table} can either be:

  • Excluded: Don't put anything here
  • Specify table name: If you put the name of the table, it will only dump this one. For example: if my database was called cms and I had a table called content, the command would be sudo mysqldump -u {user_name} -p cms content
  • Ignore a table: When ignoring a table, you need to specify the database --ignore-table={db_name}.{table_name}. For example, if I wanted to dump the whole database except the content table, I could run: sudo mysqldump -u {user_name} -p cms --ignore-table=cms.content

You might also enjoy

Mike Street

Written by Mike Street

Mike is a front-end developer from Brighton, UK. He spends his time writing, cycling and coding. You can find Mike on Twitter.