clubmate.fi

MySQL command cheat sheet

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.

Database related commands

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

SHOW DATABASES;

List all users:

SELECT User FROM mysql.user;

Create DB:

CREATE DATABASE database_name;

Delete database:

DROP DATABASE database_name;

MySQL user related commands

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:

FLUSH PRIVILEGES;

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 127.0.0.1 -u root -p db_name > ./sql_dumps/db_name.sql

Conclusions

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.