Export Large Database in cPanel by SSH Command

To export a large MySQL database via the command line, you use the mysqldump utility. Here’s the basic syntax and steps for exporting a large database:

Basic Syntax

Steps to Export a Large Database

Open Your Terminal:

Access the command line interface on your server or local machine.

1. Run the mysqldump Command:
   mysqldump -u your_database_user -p your_database_name > /path/to/backup_file.sql

    

2. Enter the Password:
   After running the command, you’ll be prompted to enter the password for your_database_user. Type in the password and press Enter.

Additional Options

• Compress the Backup File: To save space, you can compress the SQL file using gzip:
  mysqldump -u your_database_user -p your_database_name | gzip > /path/to/backup_file.sql.gz

  To decompress and restore later:
  gunzip < /path/to/backup_file.sql.gz | mysql -u your_database_user -p your_database_name

• Include Specific Tables: If you want to export specific tables, list them after the database name:
  mysqldump -u your_database_user -p your_database_name table1 table2 > /path/to/backup_file.sql
• Export Only the Schema: To export only the schema (structure) without data:
  mysqldump -u your_database_user -p -d your_database_name > /path/to/backup_schema.sql

• Export Only Data: To export only the data (without schema):
  mysqldump -u your_database_user -p --no-create-info your_database_name > /path/to/backup_data.sq

Troubleshooting Tips

• Ensure Enough Disk Space: Make sure there’s enough disk space on your server or local machine to store the backup file.
• Handling Large Databases: For very large databases, consider using options like --single-transaction to avoid locking the tables:
  mysqldump -u your_database_user -p --single-transaction your_database_name > /path/to/backup_file.sql

• Check Permissions: Ensure that the user has the required permissions to perform the dump operation.

If you encounter issues or errors, they may provide more specific details on what needs to be addressed, such as permissions, disk space, or specific database settings.