MySQL command line administration cheat sheet for Linux


I have been muddling through the world of MySQL database administration via the command line since I setup my own linux VPS web  server (with help from the good folks at Digital Ocean).

I’m not a masochist,  I just haven’t yet got round to [I have been muddling through the world of MySQL database administration via the command line since I setup my own linux VPS web  server (with help from the good folks at Digital Ocean).

I’m not a masochist,  I just haven’t yet got round to](https://www.digitalocean.com/community/tutorials/how-to-install-and-secure-phpmyadmin-on-ubuntu-14-04) yet and have resisted the temptation to do so for now as I felt administering the database via the command line has been a good learning exercise.

As a part of my “knowledge gathering”, I have been noting down key commands you use regularly when administering a MySQL database on the command line and have been compiling these commands into a text file so thought I could easily cut and paste the commands in, a kind of “cheat sheet” if you will. So without further ado, here it is:

BTW – This is a an organic document and will be growing over time, feel free to leave any commands in the comments if you want to add to it.

Login to MySql via command line

mysql -u root -p

Import a database

mysql -u [username] -p db-name < [database name].sql

List all databases

show databases;

Delete a database

Drop database db-name;

Create a new database

create database db-name;

Show all database user accounts

select distinct User from mysql.user;

Change a database user password

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('new-password-here');

SHOW PERMSSIONS GRANTED FOR USER ON DATABASES

SHOW GRANTS FOR 'username'@'localhost';

Delete a user

DROP USER 'username'@'localhost';

Contact Me