If you have advanced user skills, you can use SSH to import and export MySQL databases via command line. This method provides more comprehensive options and fewer constraints than web-based tools like phpMyAdmin. This guide walks you through both importing an existing database file and exporting a database to a file.

Import a MySQL Database

  1. Step 1: Upload your database file

    Log in to SSH and upload your database file to your home directory. You can accomplish this through cPanel File Manager, an FTP client, or directly via SSH.

    Note: Ensure your database file is in .sql format. Compressed files (.zip or .tar.gz) must be extracted first.

  2. Step 2: Create the database and user

    If the target database does not exist, create a database, a database user, and grant the user complete privileges. Take note of the database name, username, and password.

  3. Step 3: Connect via SSH

    Log into your server using SSH.

  4. Step 4: Verify the file location

    Use the ls -l command to confirm the file's presence in your current directory. If the file is not available, either upload/move it to the correct directory or navigate to the directory where the file is located.

  5. Step 5: Import the database

    Enter the following command, replacing user_name, database_name, and file.sql with your actual username, database name, and filename:

    mysql -p -u user_name database_name < file.sql

    You will be prompted for your database user password. Upon successful entry, your database will be imported.

Export a MySQL Database

  1. Step 1: Access the command line

    Launch the command line on the computer where the database is located. If the database is hosted on a remote server (such as another web hosting account), use SSH to log in to that account. If you have physical access to the computer, open a terminal window to access the command line.

  2. Step 2: Run the export command

    Enter the following command, replacing username with your actual username and dbname with the name of the database you intend to export:

    mysqldump -u username -p dbname > dbexport.sql

    Note: The filename dbexport.sql is used in this example, but you can choose any desired filename.

  3. Step 3: Enter your password

    Provide your password when prompted at the "Enter password" prompt.

  4. Step 4: Download the exported file

    The dbexport.sql file now contains all the data from the database. If the file is located on a remote computer, download it to your local computer using SCP, FTP, or cPanel File Manager.