A MYSQL Cheatsheet To Create User, Database, And Grant Access

These are the list of commonly used commands to set up a MySQL database. They contain all the essentials for everyday use-cases when working with a MySQL database.

This works as of April 27, 2020 on Ubuntu 18.04 and MySQL 14.14.

The Essentials

Connect to MySQL console

Connect to local

sudo mysql

Connect to remote

mysql -h my_remote_host -u my_username -p

Create user

CREATE USER '<my_username>'@'localhost' IDENTIFIED BY 'my_password';

Create database

CREATE DATABASE my_database DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Grant user access to database

GRANT ALL ON my_database.* TO 'my_username'@'localhost';

Delete database

DROP DATABASE my_database;

Exit MySQL

You may also emit the colon at the end for this.

exit;

View database and tables

These console commands are to view databases and the data in the tables. You usually wouldn't view the data in a console but if you need to, these can come in handy.

List databases

SHOW DATABASES;

List tables in a database

USE my_database;
SHOW TABLES;

View table column structure

DESCRIBE my_table_name;

When you need them

Lastly, these are the miscellaneous that you might use from time to time. They are to change user passwords and perform a dump to move a database.

Change user password

Either of these can work as of now. Note that the second method modifies the data directly in the MySQL user database.

ALTER USER 'my_username'@'localhost' IDENTIFIED BY 'my_new_password';
--or
UPDATE mysql.user SET authentication_string=PASSWORD('my_new_password') WHERE User='my_username' AND Host='localhost';

Then run

FLUSH PRIVILEGES;

Export and import databases

Run these on the terminal, outside the MySQL console.

To export:

mysqldump -h localhost -u my_username --password=my_password my_database > filename.sql

To import:

mysql -h localhost -u my_username --password=my_password my_database < filename.sql

Conclusion

These are the list of MySQL commands I keep as a reference whenever I need to work with MySQL databases. I hope these covers all the essentials and provides a good reference.