Magento 2 Commerce cloud database export

Question

I am new in magento commerce cloud. I need to setup the magento store in my local system. I have downloaded the files from the commerce git and it is working fine. But how can I access the database? I need to import the database to my local system. I have created the database backup using the below command.

vendor/bin/ece-tools db-dump

How can I download it in to my local system? Please help me

Answer 1

As MSA has noted, you can ssh into the machine and use ece-tools db-dump and then rsync the database dump down locally.Here is the rsync command

 rsync -avzh <SSH URL>:/tmp/<BACKUP NAME> ./

Another option is to utilize the magento-cloud CLI utility. You can download the CLI utility with these instructions: https://devdocs.magento.com/guides/v2.1/cloud/before/before-workspace-magento-prereqs.html#cloud-ssh-cli-cli-install

Once you have that installed you can create a dump like this: magento-cloud db:dump -p <PROJECT ID> -e <ENVIRONMENT NAME>

The `magento-cloud CLI will also allow you to open up an SSH tunnel to the database so you could access it locally.

• Jancy Abraham: I have created the dump. Now I need to download it into my local system.
• Billy Gilbert: You just need to rsync it local. Something like this should work: rsync -avzh <SSH URL>:/tmp/<BACKUP NAME> ./
• Jancy Abraham: Yes, Now it is working. Thank you so much Billy Gilbert. Please update your answer.

Answer 2

db-dump (ece-tools; recommended)

You may dump your DB using the ECE-Tools command:

To update the ece-tools package:

On your local workstation, perform an update using Composer.

composer update magento/ece-tools

If you cannot update beyond ece-tools version 200208, follow the upgrade steps.

Add, commit, and push your code changes.

git add -A && git commit -m "Update magento/ece-tools" && git push origin <branch name>

After test validation, merge this branch to the Integration branch.

vendor/bin/ece-tools db-dump

This the recommended and the safest option.

Download Database

1. We recommend putting the application in maintenance mode before doing a database dump in Production environments.
2. The command creates an archive in your local project directory called dump-<timestamp>.sql.gz.
3. If an error occurs during the dump, the command deletes the dump file to conserve disk space. Review the logs for details (/var/log/cloud.log).

For more details refer https://support.magento.com/hc/en-us/articles/360003254334-Create-database-dump-on-Cloud

• Jancy Abraham: I have created the backup using this command vendor/bin/ece-tools db-dump. It creates the backup in /tmp folder. How can I download it into my local workstation
• Arunprabakaran M: Check updated answer @jancy
• Jancy Abraham: Yes the command creates an archive dump-<timestamp>.sql.gz. in my /tmp directory. Not in my project directory
• Arunprabakaran M: mysqldump -u {user} -p {database} | gzip > {database}.sql.gz
• Arunprabakaran M: Did you try this?

Answer 3

What about the db:dump command?

Command: db:dump
Description: Create a local dump of the remote database
Usage:
 magento-cloud db:dump [--schema SCHEMA] [-f|--file FILE] [-d|--directory DIRECTORY] [-z|--gzip] [-t|--timestamp] [-o|--stdout] [--table TABLE] [--exclude-table EXCLUDE-TABLE] [--schema-only] [--charset CHARSET] [-p|--project PROJECT] [--host HOST] [-e|--environment ENVIRONMENT] [-A|--app APP] [-r|--relationship RELATIONSHIP] [-i|--identity-file IDENTITY-FILE]
Options:
 --schema=SCHEMA The schema to dump. Omit to use the default schema (usually "main").
 -f, --file=FILE A custom filename for the dump
 -d, --directory=DIRECTORY A custom directory for the dump
 -z, --gzip Compress the dump using gzip
 -t, --timestamp Add a timestamp to the dump filename
 -o, --stdout Output to STDOUT instead of a file
 --table=TABLE Table(s) to include (multiple values allowed)
 --exclude-table=EXCLUDE-TABLE Table(s) to exclude (multiple values allowed)
 --schema-only Dump only schemas, no data
 --charset=CHARSET The character set encoding for the dump
 -p, --project=PROJECT The project ID or URL
 --host=HOST The project's API hostname
 -e, --environment=ENVIRONMENT The environment ID
 -A, --app=APP The remote application name
 -r, --relationship=RELATIONSHIP The service relationship to use
 -i, --identity-file=IDENTITY-FILE An SSH identity (private key) to use
 -h, --help Display this help message
 -q, --quiet Do not output any message
 -V, --version Display this application version
 -y, --yes Answer "yes" to any yes/no questions; disable interaction
 -n, --no Answer "no" to any yes/no questions; disable interaction
 -v|vv|vvv, --verbose Increase the verbosity of messages
Examples:
 Create an SQL dump file:
 magento-cloud db:dump 
 Create a gzipped SQL dump file named "dump.sql.gz":
 magento-cloud db:dump --gzip -f dump.sql.gz

Answer 4

You can import any .sql.gz file using following command :

zcat ./your_db_name.sql.gz | sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' | mysql -u root2 -p db_password