How to Manage Databases with the SSH Command Line

SSH basics

• SSH client. The program on your computer that starts the connection.
• SSH server. The program on the remote machine that accepts it.
• Public and private keys. A key pair that proves your identity without a password.
• SSH tunneling. Forwarding traffic through the secure connection, for example to reach a database port safely.

Connect to the server

ssh username@server_ip_address

Manage MySQL over SSH

Export (back up) a database

mysqldump -u username -p database_name > backup.sql

Import (restore) a database

mysql -u username -p database_name < backup.sql

The target database must already exist. Create it first with CREATE DATABASE database_name;.

Use a client configuration file

Save the login in ~/.my.cnf so you do not type the password each time:

[client]
user=username
password=your_password

Protect the file:

chmod 600 ~/.my.cnf

Log in to MySQL

mysql -u root -p

Run SQL

SHOW DATABASES;
USE your_database;
SHOW TABLES;
SELECT * FROM your_table LIMIT 10;
INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2');
UPDATE your_table SET column1 = 'new' WHERE id = 1;
DELETE FROM your_table WHERE id = 1;

Create a database and a user

CREATE DATABASE mydb CHARACTER SET utf8mb4;
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Use an SSH tunnel for a remote GUI tool

ssh -L 3307:127.0.0.1:3306 username@server_ip_address

Then connect your database tool to 127.0.0.1 port 3307. The database port stays closed to the internet.

Quick recap

• mysqldump backs up and mysql < file.sql restores.
• Use SSH keys and tunnels instead of opening the database port.