How to Create and Manage Database on Remote Server ?

Managing databases on remote servers is a common task for many database administrators, developers & QA. In this guide, I will walk you through the steps to create a database on a remote server, dump a database from own/another server, and import it into the newly created database on your current remote server.

Step 1: Connect to Your Remote Server

Command:

ssh [username]@[SERVER_IP]        

Description: This command establishes a secure connection to your remote server using SSH.

Step 2: Create a New Database & grant access

Commands:

mysql -u [username] -p 
Enter Password:        

CREATE DATABASE [db_name];        

GRANT ALL ON [db_name].* TO 'username'@'%';        

Description:

1. mysql -u [username] -p : Connect to the MySQL database as the user.
2. CREATE DATABASE [db_name];: Create a new database named [db_name].
3. GRANT ALL ON [db_name].* TO 'username'@'%';: Grant all privileges on the new database to a specific user.

Step 3: Dumping a Database from Another Server

Command:

mysqldump -h [SERVER_IP] -u [username] -P [PORT] -p [db_name] > [db_name].sql        

Description: This command creates a dump of the specified database from another server and saves it as an SQL file. Here [db_name].sql is new file dumped & saved.

(Optional): Dumping a Specific Database from the Current Server

Command:

mysqldump -h [SERVER_IP] -u [username] -p [db_name] > [db_name].sql        

Description: This command creates a dump of a specific database from the current server and saves it as an SQL file. Here [db_name].sql is new file dumped & saved.

Step 4: Importing the Dumped Database

Command:

mysql -u [username] -h [SERVER_IP] -p [db_name] < [db_name].sql        

Description: This command imports the dumped SQL file into the newly created database on your current remote server.

Summary

1. Connect to the remote server
2. Create a database and grant privileges
3. Dump a database from another server/ Dump a specific database from the current server
4. Import the dumped database

By following these steps, you can efficiently manage databases across different servers. This process is crucial for data migration, backup, and recovery.

I would like to extend my heartfelt thanks to Uttam Parajuli dai for his invaluable guidance and support in teaching me the techniques and best practices for managing database on remote server.