How to Manage Databases with SSH Command Line

SSH stands for Secure Shell is a powerful network protocol that enables secure remote access to a computer system. Managing databases through the SSH command line can be a powerful and efficient way to handle your data. It is used by system administrators to manage databases remotely.

In this article, we will understand how to manage databases using SSH covering everything from establishing a connection to performing various database operations.

Understand SSH Command

SSH is a protocol that allows secure communication between two machines. It encrypts the data transmitted between the client and the server ensuring that sensitive information remains protected. SSH is commonly used for remote server management including database administration.

Key Concepts

Before getting started let’s understand the SSH fundamentals:

1. SSH Client: The application on your local machine that initiates the SSH connection.
2. SSH Server: The application running on the remote machine that accepts the SSH connection.
3. Public and Private Keys: These are cryptographic key pairs used for authentication.
4. SSH Tunneling: A technique that allows you to forward traffic from your local machine through the SSH connection.

Connecting Server

SSH access can connect to the server using the following command:

ssh username@server_ipaddress

Replace username with your username and server_ipaddress with the IP address of your server.

Managing Databases

MySQL, PostgreSQL, and Oracle are popular databases. Below we discuss how you can connect both databases with the help of the SSH command Linux:

Managing MySQL with SSH

MySQL is one of the most popular open-source relational databases. Here’s how to manage the SSH MySQL database:

Exporting Databases

Exporting a database is essential for creating backups. Use the mysqldump command to export a backup MySQL database:

mysqldump -u username -p database_name > backup.sql

You will be prompted to enter the password for the MySQL user. Replace username and database_name with your actual MySQL username and database name.

Importing Databases

To import a database, use the mysql command:

mysql -u username -p database_name < backup.sql

Again, you will be prompted to enter the password for the MySQL user.

MySQL client configuration

Create a remote MySQL client configuration with the following command:

mysql --defaults-file ~/.my.cnf

Edit the ~/.my.cnf file to store your username, password, and other connection details.

Login MySQL Database

To login to your MySQL database with SSH, type the following command:

mysql -u root

Enter the password if prompted. After that, you can easily log into the MySQL command line interface.

1. Execute SQL Queries

You can easily write SQL queries once you have entered the MySQL interface.

SELECT * FROM your_table;
INSERT INTO your_table (column1, column2) VALUES ('value1', 'value2');

2. Creating and Deleting Tables

To create tables in MySQL, run the following queries:

CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(255) NOT NULL,
    column2 INT NOT NULL
);

If you want to delete the tables in the MySQL, run the following:

DROP TABLE table_name;

Managing PostgreSQL with SSH

PostgreSQL is another popular open-source relational database. Here’s how to manage it using SSH:

Connect PostgreSQL server

To connect with the PostgreSQL database in SSH, type the following:

psql -U your_username -d your_database

Replace your_username with your actual username and your_database with the real database name.

PostgreSQL client configuration

Create a remote PostgreSQL client configuration with the following command:

psql -c "CREATE ROLE your_username WITH PASSWORD 'your_password';"

Edit the ~/.pgpass file to store your username, password, and other connection details.

Managing Oracle with SSH

Oracle Database is a commercial relational database system. Here’s how to manage it using SSH:

Connect Oracle server

To connect with the Oracle database in SSH, type the following:

sqlplus / as sysdba

Oracle client configuration

Create a remote Oracle client configuration with the following command:

sqlplus / as sysdba
CREATE PROFILE your_profile LIMIT CONNECT_TIME 60;
GRANT CONNECT TO your_username PROFILE your_profile;

Edit the ~/.sqlplus file to store your username, password, and other connection details.

Security Considerations

When managing databases via SSH security is an important element. Here are some best practices:

• Always use strong authentication methods like public key authentication to protect your SSH connections.
• Ensure that your database user passwords are strong and unique.
• Disable root login over SSH to prevent unauthorized access.
• Use a firewall to restrict access to your server.
• Regularly back up your databases to prevent data loss.

Conclusion

Managing databases via SSH command line can be a powerful and efficient way to handle your data. By following the steps outlined in this guide you can securely connect to your server perform various database operations and ensure that your data is well managed and protected. Whether you are exporting and importing databases, creating and deleting tables, or running queries SSH provides a flexible and secure way to manage your databases.

FAQ