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
  • Start MySQL without admin privileges
sudo mysqld_safe --skip-grant-tables --skip-networking &  
  • Login to MySQL Database from command line. Once authentication is completed successfully, mysql> prompt will appear.
$mysql -u USERNAME -p PASSWORD

OR

$ 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 --host=remote.example.com --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

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

To import database

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

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.