Server, Network and Hosting ► SSH

How to use SSH to manage your Mysql database

When I start using Cpanel I thought it was such a perfect and wonderful server management software. Especially when it comes to GUI for database managemenet - phpmyadmin. Slowly, I've moved to ubuntu nginx which has a higher performance when my website scales. There is no GUI for database management in nginx by default. This is why this list comes to handy whenever you need to setup, manage your Mysql Database using SSH.

Login to mysql

mysql -u[username] -p

Show all databases in the Mysql

show databases;

Choose a database

use [dataabse_name];

After choosing a database, show all tables

show tables;

Create a new database

create database [database-name];

Create a user assigned to the new database;

grant all privileges on [database-name].* to '[username]'@'localhost' identified by "[password]";

Import .sql into the database

cd /your_path

Change to the directory where your database.sql located

mysql -u root -p password

use somedb;

Choose and use a database

source somedb_backup.sql

This will import the .sql into the database.

If you not sure how to upload the .sql file to the server, learn more here

Delete a table or database

DROP TABLE tablename;

DROP DATABASE dbname;

Show mysql users

SELECT user FROM mysql.user GROUP BY user;

Delete a mysql users

DELETE FROM mysql.user WHERE user = 'username';

Comments

Time is precious. If you have an alternative or better way to get this done, do let us know so that we can compose this list and help more people.