MySQL Quick Reference

Here is the list of commands for MySQL that will be evry handy while using MySQL. Over the period of time, you will remember these commands but to get started, you can use this as a cheat sheet for MySQL commands.

Connecting to MySQL

  • Check status of MySQL
$ systemctl status mysql
  • Start and Stop MySQL Server using
$ sudo systemctl start mysql
$ sudo systemctl stop mysql
  • Check MySQL version
mysql -V
mysql Ver 8.0.26-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))
  • Start MySQL without admin privileges
sudo mysqld_safe --skip-grant-tables --skip-networking &  
  • After installation defact userid and password can be viewed by using following command
sudo cat /etc/mysql/debian.cnf

host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock
host = localhost
user = debian-sys-maint
password = XXX
socket = /var/run/mysqld/mysqld.sock
  • Login to MySQL Database from command line. Once authentication is completed successfully, mysql> prompt will appear.


$ mysql --host=localhost --user=USERNAME --password PASSWORD

If you want to connect to any remote MySQL server, replace ‘localhost’ with remote db address name and add port parameter.

$ mysql --port=13306 --user=USERNAME --password PASSWORD

Commands to be used in mysql> prompt:

Use following command to get list of databases

mysql> show databases;

Use following command to create database

mysql> create database <newDatabaseName>;

Create new user

mysql>CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

grant access to newly created user

mysql> grant create, delete, insert, select, update, alter,references ON <databaseName>.* TO 'usernmae'@'localhost';

Use following command to delete or dropĀ  database

mysql> drop database DatabaseName;

Use following command to get list of users

mysql> SELECT User FROM mysql.user;

To check the access level of the user

mysql>SHOW GRANTS FOR 'root'@'localhost';

To use specific database name use following command

mysql>use DBNAME

To export database.

Please note that password is not put after “-p”, there will be prompt for password where you need to provide the password.

$mysqldump -u [username] -p [databaseName] > [databaseName].sql

To import database

$mysql -u [username] -p newdatabase < [databaseName].sql

Leave a Reply

Your email address will not be published.