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

  • The Git Commit Hash

    Posted on 28th May 2020.

    The magic 40 character string that is attached to every commit you do. What is it, where does it come from and will understanding it help you with Git in the future?…

  • Why should you consider Cloudflare for your website?

    Posted on 20th April 2020. Written For Liquid Light

    Cloudflare is a service which, among other things, provides a CDN (Content Delivery Network), firewall, and performance layer for your website. It has plenty of paid upgrades and features and is a developer’s dream, but what advantage does it have for you to put your website “behind” Cloudflare and how…

    Web
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.