How to use SSH to access and manage MySQL database

Generally, most of the modern hosting platform allows user to access the database using rich user-interface application such as phpmyadmin. Although using the interface is very convenient and easy to use, in some cases you need to use SSH to access and manage your database.

For instance, when you need to query your database sort of emergency. SSH is the fastest way to connect to the database and get the record you need. There are a lot more advantages of using SSH, the biggest advantage here is automation. By putting your SSH commands into a bash script you can automate some routines.

Here we go the commands to SSH to your MySQL database:

SSH to your server

# ssh -i "YOUR_KEY_LOCATION" ubuntu@SERVER_IP_ADDRESS;

The SSH command to connect to your server can be vary depends on your server operating system.

Login to MySQL

# mysql -p

You will be prompted to enter your MySQL password. You will login as “root” by default, otherwise, type “-u” to specify a username.

After logged in to MySQL

Create new database

# create database [database-name];

You should see a “Query OK” message. Now that the database is created.

Create a user and password for the database

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

you can create a user that only has access to the database you just created. The above command creates a user, defines their password, and gives them all privileges to the database we just created.

Show all databases

# show databases;

this command will list all the available databases in your server. To do further database operation you need to connect to a database first.

Connect to a database

# use database-name

You are now connected to the database “database-name”

After connected to a database

Import SQL file to the database

# source PATH_TO_THE_SQL/backup.sql

This command will import the database from backup.sql to the database you have just connected.

Export Database into SQL file

# mysqldump -u username -p [database_name] > export.sql

You need to log out from the mysql in order to use mysqldump command. While in SSH, this command will export the database to export.sql.

Optionally you can export the sql file direct to a gzip:

# mysqldump -u username -p [database_name]| gzip > export.sql.gz

Show all tables under the database

# show tables;

This will show all the tables under the database.

Query the table

# Select * from TABLE_NAME;

You can perform any SQL Query statement here.

Find the slow queries

# show global variables like '%slow%'; in mysql

Most of the time it’s is troublesome to find the slow query log the hosting directories. You can simply find it in the mysql console by typing this command.

Result: