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

If you possess advanced user skills, you may opt to utilize SSH for accessing and modifying your account, as it provides a more comprehensive range of options with fewer constraints. Familiarity with SSH is essential for this process.

Here are the steps to import your database from the command line:

NOTE: Ensure your database file is in .sql format; compression in .zip or .tar.gz is not permissible.

  1. Initially, log in to SSH and upload your database to your home directory. This can be accomplished through cPanel File Manager, an FTP client, or SSH.

  2. If the database is not present, create a database, a user, and grant the user complete privileges. Take note of the username and password.

  3. Log into your server using SSH.

  4. Use the ls -l command to confirm the file's presence in your current directory.

  5. If the file is not available, either upload/move it to the correct directory or navigate to the directory where the file is located.

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

  • mysql -p -u user_name database_name < file.sql

  1. You will be prompted for your database user password, and upon successful entry, your database will be imported.

Steps for Command Line Database Export:

  1. Launch the command line on the computer where the database is situated. If the database is hosted elsewhere, such as on another web hosting account or with a different provider, use SSH to log in to the respective account. If you have physical access to the computer, open a DOS or terminal window to access the command line.

  2. Enter the following command and press Enter. Replace '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 for the exported database, but you can choose any desired filename.

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

  2. The 'dbexport.sql' file now contains all the data from the 'dbname' database. If the 'dbexport.sql' file is situated on a remote computer, download it to your local computer.

By following these steps, you can perform both import and export operations for your database via the command line.


Was this article helpful?

mood_bad Dislike 0
mood Like 0
visibility Views: 385