MySQL CLI Cheatsheet

MySQL databases are an essential part of a WordPress or Drupal website. It’s where all the content for the website is stored. It is essentially just a large, ever growing, “dynamic” text file that a user or website needs authorisation to access.

Login to MySQL

Open the MySQL CLI by running the following command in your terminal or command prompt:

mysql -u username -p

Replace username with your MySQL username. You’ll be prompted to enter your MySQL password after running this command.

Once you’re logged in, switch to the database whose tables you want to view using the following command:

Login is root with no password

mysql -u root

Show tables

To show the tables for a MySQL database using the MySQL CLI, follow these steps:

USE database_name;

List all tables in the current database, use the following SQL command:

SHOW TABLES;

Import Export MySQL Database

The important thing to note with these commands is you run them on the terminal rather then in the MySQL cli.

Import

sudo mysql -u root -p db_name < db_file.sql

How to fix ERROR 1045 (28000): Access denied for user ‘root’@’localhost

You might not have set a password. In which case anything you enter will be wrong. If you have not set a password just hit the enter key when prompted for a password.

Export

sudo mysqldump -u root -p db_name > my_db_export.sql

Create database and user

create database dbname;
create user ''@'localhost' identified by '';
grant all privileges on dbname.* to 'username'@'localhost';
flush privileges;

Drop Database

To find the name of your database, first do a show databases;. Once you have the name.

DROP DATABASE your_database_name;

View privileges for a particular user

show grants for 'user'@'localhost';

Show Databases;

show databases;

Exit

exit;