A good[ish] website

Web development blog, loads of UI and JavaScript topics

MySQL command cheat sheet

Filed under: Cheat sheets— Tagged with: database

This is a quick cheat sheet of MySQL commands related to creating, importing, and exporting databases and database users.

I rarely need to work with MySQL databases anymore, but when I do, I always need to look up the commands. Might as well document here the the most important set of commands.

Log in to MySQL shell

The MySQL shell can be accesses with the mysql command. Here as the user root:

$ mysql -u root -p
-u, --user
The MySQL user name to use when connecting to the server
-p, --password
The password to use when connecting to the server. If you use the short option form (-p), you cannot have a space between the option and the password. If you omit the password value following the --password or -p option on the command line, mysql prompts for one.

Once you’re in [hacker voice], list all databases:


List all users:

SELECT User FROM mysql.user;

Create DB:

CREATE DATABASE database_name;

Delete database:

DROP DATABASE database_name;

Create user:

CREATE USER username@localhost;

Remove user:

DROP USER 'bob'@'localhost';

Set password for the user:

SET PASSWORD FOR bob@localhost= PASSWORD("passwordhere");

Grant privileges for the user to the newly created DB:

GRANT ALL PRIVILEGES ON database_name.* TO bob@localhost IDENTIFIED BY 'password

Misc MySQL commands

Refresh MySQL:


Importing and exporting databases

Import DB:

$ mysql -u root -p --default-character-set=utf8 db_name < ~/path/db_filename.sql

Or if you do it from the MySQL shell, get rid of the login thing the beginning:

--default-character-set=utf8 db_name < ~/path/db_filename.sql

Export (aka dump) a MySQL database using the mysqldump command:

$ sudo mysqldump –u root –p db_name > ./sql_dumps/dump_file.sql

If you’re not prompted for a password, and get an error like this:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

It might be that you need to define the host in the export command. I’ve had this issues and usually it’s been solved by using the local IP:

$ sudo mysqldump -h -u root -p db_name > ./sql_dumps/db_name.sql


This what’s needed for very simple database operations, like setting up a new WordPress DB or stuff like that.

Hope this was helpful, thanks for reading.

Comments would go here, but the commenting system isn’t ready yet, sorry.

  • © 2022 Antti Hiljá
  • About
  • All rights reserved yadda yadda.
  • I can put just about anything here, no one reads the footer anyways.
  • I love u!