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.

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