Magento Cloud - Export Data from Production Database

When working for an organization, there are times when you need to export data from the production database. Ideally, you should build a report or export option in Magento admin for this information. However, these are one-time data for which developing a module or backend functionality is not worthwhile. So, for this circumstance, follow the procedures below to retrieve data from the database.

Assuming you already know how to do ssh login to the production server. So let's start with logging into your server. Once you log in follow the below steps:

Get Database credentials

Once you log in, open your env.php file and get the database credentials stored on the server. You will find an array in the env file and get the credentials from the key with the name "db".

Prepare SQL Query

Prepare your query whatever you want to export and then save it in a SQL file. You can prepare this file directly on the server or locally. If you want to move this file from local to production then you can execute the below command to directly upload the file to the server.

rsync -azvP path-to-local-file 1.ent-abcs-production-abcc@ssh.eu-3.magento.cloud:path-to-upload

Execute SQL Query in Server

Now you already have your MySQL credentials and you are logged in to your server. You have also prepared your query file. Once you are done with this, you can execute the below command on the server.

mysql -h 127.0.0.1 -u username -ppassword db_name < query.sql | sed 's/\t/,/g' > export-data.csv

Download the Export File to Local

To download the export file, execute the below command from the local environment's terminal.

rsync 1.ent-abcs-production-abcc@ssh.eu-3.magento.cloud:paht-of-the-file/filename local-path-to-download

Conclusion: It's not always necessary to prepare a feature to generate one-time data. You can use your database skills to get the data fastly and quickly.