clubmate.fi

A good[ish] website

Web development blog, loads of UI and JavaScript topics

MySQL command cheat sheet

Filed under: Cheat sheets— Tagged with: MySQL

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 a small set of commands here.

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 MySQL databases:

SHOW DATABASES;

List all MySQL users:

SELECT User FROM mysql.user;

Create MySQL DB:

CREATE DATABASE database_name;

Delete MySQL database:

DROP DATABASE database_name;

Create MySQL user:

CREATE USER username@localhost;

Remove MySQL user:

DROP USER 'bob'@'localhost';

Set password for the MySQL 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:

FLUSH PRIVILEGES;

Importing and exporting databases

Import MySQL 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 127.0.0.1 -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.

Comments would go here, but the commenting system isn’t ready yet, sorry. Tweet me @hiljaa if you want to make a correction etc.

  • © 2021 Antti Hiljá
  • About
  • Follow me in Twatter → @hiljaa
  • All rights reserved yadda yadda.
  • I can put just about anything here, no one reads the footer anyways.
  • console.log('Smash the patriarchy!')
  • I love u!