How to Import and Export MySQL Database via Command Line (SSH)

Importing and exporting MySQL databases via the command line using SSH is a fundamental task when managing MySQL databases on a remote server. This process allows you to back up your databases, migrate data, or transfer it between different environments. Below is a step-by-step guide on how to perform these actions using SSH.

Prerequisites:

• Access to the SSH terminal of the server.

• MySQL or MariaDB installed on your server.

• MySQL username and password (with sufficient privileges to access and manipulate the database).

• The database you want to export or import should already be created.

Step 1: Log in to the Server via SSH

First, log in to the server where MySQL is hosted using SSH:

Replace username with your actual SSH username and your-server-ip with the server's IP address. You'll be prompted for your password.

Step 2: Export (Dump) MySQL Database via Command Line

To export a MySQL database, you will use the mysqldump command. The basic syntax for exporting a database is:

• -u username: MySQL username with appropriate privileges (replace with your actual MySQL username).

• -p: Prompt for the MySQL password. After entering this, you'll be asked to input your MySQL password.

• database_name: The name of the database you wish to export.

• > /path/to/backup.sql: Specify the full path and filename for the backup file. You can also save it to a relative path (e.g., backup.sql).

Example:

To export a database called my_database:

After executing the command, you will be prompted for the MySQL root password. Once entered, the backup file will be created at the specified location.

Step 3: Import MySQL Database via Command Line

To import a MySQL database, use the mysql command with the following syntax:

• -u username: MySQL username with appropriate privileges.

• -p: Prompt for the MySQL password.

• database_name: The name of the database where you want to import the data. This database must already exist.

• < /path/to/backup.sql: The path to the SQL file that you want to import.

Example:

To import a backup file (my_database_backup.sql) into a database called my_database:

After executing the command, you will be asked for the MySQL password. The data from the backup will be imported into the specified database.

Step 4: Verify the Import or Export

Once you have completed the export or import process, you can verify that the database has been successfully backed up or restored.

To Verify Database Import:

1. Log in to MySQL:

2. Use the database:

3. Check if the tables and data have been imported:

4. You can also run a query to verify the data:

This will show you the first 10 rows from the specified table.

Step 5: Automating Backups (Optional)

If you need to regularly export your database, you can automate the process using cron jobs on Linux. To create a scheduled backup:

1. Edit the crontab file:

2. Add a cron job to run the backup command daily at midnight:

This will create a backup every day, and the filename will include the date of the backup (e.g., my_database_backup_2023-01-01.sql).

Step 6: Restore Database from a Backup (If Necessary)

If you need to restore your database from a backup that has already been exported:

1. Make sure the database exists in MySQL. If it doesn't exist, create it first:

mysql -u root -p CREATE DATABASE my_database;

2. Use the import command to restore the backup:

mysql -u root -p my_database < /home/user/backups/my_database_backup.sql

Troubleshooting Tips:

• "Access Denied" error: Ensure that the MySQL user has sufficient privileges to access and modify the database.

• File Permissions: Make sure the .sql file you are exporting or importing has the appropriate file permissions, allowing the MySQL process to read/write it.

• Large Database Imports/Exports: If you are working with large databases, consider using gzip to compress the backup file or increasing the memory and timeout settings in your MySQL configuration.

Conclusion:

Importing and exporting MySQL databases via SSH command line is a reliable way to manage database backups and migrations. With the help of mysqldump for exports and mysql for imports, you can easily move data between servers or keep regular backups of your MySQL databases.

Was this answer helpful? 0 0