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.
Connect to MySQL console
Connect to local
Connect to remote
mysql -h my_remote_host -u my_username -p
CREATE USER '<my_username>'@'localhost' IDENTIFIED BY 'my_password';
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';
DROP DATABASE my_database;
You may also emit the colon at the end for this.
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 tables in a database
USE my_database; SHOW TABLES;
View table column structure
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';
Export and import databases
Run these on the terminal, outside the MySQL console.
mysqldump -h localhost -u my_username --password=my_password my_database > filename.sql
mysql -h localhost -u my_username --password=my_password my_database < filename.sql
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.