DocumentationCommerceVideos and Tutorials

Connect and run queries against the Adobe Commerce database

Last update: Tue Mar 24 2026 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Intermediate
  • Experienced
  • Developer

Learn how to connect to an Adobe Commerce on cloud project, create a database dump for offsite use, and mask or remove personally identifiable information (PII). Access data with a local dump, a GUI such as MySQL Workbench or TablePlus, or the magento-cloud CLI.

Video contents

  • Connect to a remote Adobe Commerce Cloud project with a GUI tool such as MySQL Workbench or TablePlus.
  • Connect to the project and run SQL from the command line.
https://video.tv.adobe.com/v/3430507?learn=on
Transcript
This is Russell with Adobe, and I’m going to walk you through the process on how to do a connection to a remote cloud database in order to run some commands. So for this first phase, we’re going to show you how to use some sort of graphical interface. You can use MySQL Workbench. In my case, I’m going to be using a tool called Tables Plus. And then for the second example, I will show you how to do the same thing via command line. So to start, for the GUI-based interaction, we’re going to open up a tunnel between our local environment, our local laptop, and our Adobe Commerce Cloud project. So to do this, we’re just going to open up a tunnel, and we’re going to use the magento-cloud command, the CLI. And the command is tunnel colon open. And then the two flags that you want to use is your project ID. So in my case, it’s whatever your project ID is, and you get that from your admin interface. And then the other flag, the dash E is the environment. And that would be the ID given when you spun up one of your environments. So you may have a master environment or main environment, you may have a staging and a production environment. And then usually you’ll have an integration, and you’ll have one or two or three integration environments. So I’ve spun up an integration environment, and I gave it the name remote-db. When doing yours, that’ll probably be the only thing you need to change besides the project ID is making sure that you connect to the right place. So once that’s done, and we run the command, what it’s going to do is it’s going to give us some output. And what we’re most interested in for this example is the MySQL, the database connection. And we are going to be using the username of user. There is no password for this because these lower environments don’t have a password. We’re going to be using localhost, so 127.0.0.1. And then the port, this is the important part, it’s going to be 30,000. And we’re going to be able to use this to connect to the main database. So that’s the name of the database. And most, if not all of these lower environments have the same convention. So this should be fairly similar to all lower environments outside of production and staging. So now that we’ve established that we can open up our UI. And so once again, I’m going to use a tool called Tables Plus. And we’re going to create a new connection. And we’ll just connect to a MySQL instance. And we’ll give it a name. So, remote db demo, right? We’re going to use 127.0.0.1. Port, remember, is going to be 30,000. The username for these lower environments is always the word user, there is no password. And then the database that we want to connect to is main. So once that’s all done, if we want to verify that we have is right, we can hit test. And hopefully, you’ll get some sort of message back. In this case, it highlights all the fields green, or red if there’s something wrong. If you’re happy with it, you can hit save. And now you can connect to it. So once you’ve made that connection, you can first tell that you’ve actually gotten somewhere because the tables should show up here on this left hand column, or you could run some sort of SQL statement. So we’re going to do select, let’s see, we’ll select value from, let’s do a core config data, where path like, and we’ll do secure base URL. All right. So we’ll go ahead and run this. And look at that, we have our results. So now that you’ve seen how to do this remote DB connection, through a GUI, we’ll basically do the exact same process, but we’re going to use command line. So now that we’re here, we can go back to terminal, we’re going to do everything from this point on, basically the same process, but we’re going to do it via the command line. So we’re going to run the magento-cloud command. And then what we’re going to do instead of doing a tunnel, we’re going to run the command that allows us to connect to this remote database. So we’re going to run the command and it’s going to be DB dash, DB colon SQL. And once again, we’re going to do the project ID. And when we do this, it’s actually going to take us right into the database, just like as if we would have SSH’d into the environment, found the MySQL connection credentials and ran the command to get us this far. This is taking us all the way through those steps because we’ve been authenticated and it knows who we are, and it knows that we have permissions and it’s going to help us by getting us to this point so much faster than doing it the old way. So now we can basically do the exact same thing that we just did. Now we’ve got a MariaDB, we’ve got a MySQL connection. We can do select, let’s do value from core config data, where path like, we’ll do a wild card and we’ll do the same thing. We’ll do secure base URL. And there we go. So that’s a really quick primer on how to use your local environment and then we’ll make a remote database connection either through some sort of GUI or using the Magento Cloud CLI and making that remote database connection directly to the database.

You can access Adobe Commerce data from your cloud project using any of the following methods:

  • Use a local DB dump.
  • Open a DB connection to your remote cloud environment with an application such as MySQL Workbench or TablePlus.
  • Connect directly to the cloud environment with the magento-cloud CLI and run commands on the remote server.

Prefer a database dump that you scrub to remove customer information. Remove customer data entirely when you do not need it.

Using the Adobe Commerce Cloud CLI tool

You need the Adobe Commerce Cloud CLI installed to create a database dump. On your local computer, open a directory and run the following command. Replace your-project-id with your project ID (similar to asasdasd45q). Replace your-environment-name with your environment name, such as master or staging.

magento-cloud db:dump -p your-project-id -e your-environment-name

If you are not sure of the project ID or the environment, you can omit these in the command:

magento-cloud db:dump

The CLI asks you to specify the correct project and environment. The following example displays that dialog. This example shows several projects assigned to your account, but you will likely have only one project available.

Change into a directory

cd ~/Downloads/db-tutorial

Now execute the command to create the database dump

magento-cloud db:dump

Because you did not specify a project or environment, the Adobe Commerce Cloud CLI asks a few questions. The following example shows sample dialog.

Enter a number to choose a project:
  [0] demo-ralbin (ral32nryq4123)
  [1] adobe-commerce-demo (abc123zzkipexnqo)
  [2] DX Tutorials - Commerce (abasrpikfw4123)
 > 2

Enter a number to choose an environment:
Default: master
  [0] master (type: production)
  [1] remote-db (type: development)
 > 1

Creating SQL dump file: /Users/<username>/Downloads/db-tutorial/abasrpikfw4123--remote-db-ecpefky--mysql--main--dump.sql

Using the Adobe Commerce ECE-tools

If you do not have the Adobe Commerce CLI tool, you can ssh into your project and run the ece command vendor/bin/ece-tools db-dump:
Sample response:

ssh abasrpikfw4123-remote-db-ecpefky--mymagento@ssh.us-4.magento.cloud

 __  __                   _          ___ _             _
|  \/  |__ _ __ _ ___ _ _| |_ ___   / __| |___ _  _ __| |
| |\/| / _` / _` / -_) ' \  _/ _ \ | (__| / _ \ || / _` |
|_|  |_\__,_\__, \___|_||_\__\___/  \___|_\___/\_,_\__,_|
            |___/

 Welcome to Magento Cloud.

 This is environment remote-db-ecpefky
 of project abasrpikfw4123.

web@mymagento.0:~$ vendor/bin/ece-tools db-dump
The db-dump operation switches the site to maintenance mode, stops all active cron jobs and consumer queue processes, and disables cron jobs before starting the dump process.
Your site will not receive any traffic until the operation completes.
Do you wish to proceed with this process? (y/N)?y
[2024-02-13T19:01:45.130999+00:00] INFO: Starting backup.
[2024-02-13T19:01:45.155039+00:00] NOTICE: Enabling Maintenance mode
[2024-02-13T19:01:46.404427+00:00] INFO: Trying to kill running cron jobs and consumers processes
[2024-02-13T19:01:46.420149+00:00] INFO: Running Magento cron and consumers processes were not found.
[2024-02-13T19:01:46.420434+00:00] INFO: Waiting for lock on db dump.
[2024-02-13T19:01:46.420499+00:00] INFO: Start creation DB dump for main database...
[2024-02-13T19:01:50.697886+00:00] INFO: Finished DB dump for main database, it can be found here: /app/var/dump-main-1707850906.sql.gz
[2024-02-13T19:01:51.628328+00:00] NOTICE: Maintenance mode is disabled.
[2024-02-13T19:01:51.628419+00:00] INFO: Backup completed.
web@mymagento.0:~$ exit
logout
Connection to ssh.us-4.magento.cloud closed.

Use SFTP or rsync to pull the database dump to your local environment.

The following example uses rsync to pull the file to the ~/Downloads/db-tutorial folder.

rsync -avrp -e ssh abasrpikfw4123-remote-db-ecpefky--mymagento@ssh.us-4.magento.cloud:/app/var/dump-main-1707850906.sql.gz ~/Downloads/db-tutorial

The terminal window will output some information, here is some example output

rsync -avrp -e ssh abasrpikfw4123-remote-db-ecpefky--mymagento@ssh.us-4.magento.cloud:/app/var/dump-main-1707850906.sql.gz ~/Downloads/db-tutorial
receiving file list ... done
dump-main-1707850906.sql.gz

sent 38 bytes  received 2691041 bytes  358810.53 bytes/sec
total size is 2690241  speedup is 1.00

View the contents of the file to verify it was successfully downloaded.

ls -lah
total 29840
drwxr-xr-x    4 <username>  staff   128B Feb 13 13:02 .
drwx------@ 103 <username>   staff   3.2K Feb 13 12:52 ..
-rw-r--r--    1 <username>   staff    11M Feb 13 12:53 abasrpikfw4123--remote-db-ecpefky--mysql--main--dump.sql
-rw-r--r--    1 <username>   staff   2.6M Feb 13 13:01 dump-main-1707850906.sql.gz

Once you have the data, make sure you clean it up by removing or masking the customer data. The following sample script will help you get started.

This example turns customer data into random strings, but keeps all the items. This example contains a few extra tables to demonstrate that customer PII can be found in third-party tables as well as core tables. Carefully examine data in every table and mask or remove any customer data.

Typically the architect or lead developer is the only person responsible for masking and sanitizing database dumps. Having a dedicated sanitizer lowers the exposure of the raw data, which reduces the opportunity for violating compliance rules and regulations.

SET FOREIGN_KEY_CHECKS=0;
UPDATE customer_entity SET email = REPLACE(email, SUBSTRING(email, LOCATE('@', email) +1), CONCAT(UUID(), '.com'));
UPDATE email_contact SET email = REPLACE(email, SUBSTRING(email, LOCATE('@', email) +1), CONCAT(UUID(), '.com'));
UPDATE sales_invoice_grid SET customer_email = 'customer@example.com', customer_name  = 'Jack Smith';
UPDATE sales_order SET customer_email = 'customer@example.com', customer_firstname = 'Sally', customer_lastname = 'Smith', remote_ip = '127.0.0.1';
UPDATE sales_order_address SET region = 'Ohio', postcode = '12345-1234', lastname = 'Smith', street = '123 Main street', region_id = 44, city = 'Phoenix', telephone = NULL, firstname = 'Jane', company = NULL;
UPDATE sales_order_grid SET customer_email = 'customer@example.com', shipping_name = 'Jack', billing_name = 'Jack Smith', billing_address = '123 Main Street', shipping_address = '321 Pine Street', customer_name = 'Jane Smith';
UPDATE sales_shipment_grid SET customer_email = 'customer@example.com', customer_name = 'Jane Smith', billing_address = '123 Main street', billing_name = 'Jack Doe', shipping_name = 'Susie Smith';
UPDATE quote SET customer_email = 'customer@example.com', customer_firstname = 'Sally', customer_lastname = 'Jones', customer_dob = NULL, remote_ip = '127.0.0.1';
UPDATE quote_address SET email = 'customer@example.com', firstname = 'Jack', lastname = 'Smith', company = NULL, street = '123 Main st', city = 'AnyCity', region = 'Some State', region_id = 44, postcode = '12345-1234', telephone = NULL;
UPDATE magento_rma SET customer_custom_email = 'customer@example.com' WHERE customer_custom_email IS NOT NULL;
UPDATE customer_address_entity SET firstname = 'Jack', lastname = 'Smith', telephone = '909-555-1212', postcode = NULL,  region = NULL, street = '123 Main street', city = 'Anycity', company = NULL;
UPDATE customer_grid_flat SET name = 'Jane Doe', email = 'customer@example.com', dob = NULL, gender = NULL, taxvat = NULL, shipping_full = '', billing_full = '', billing_firstname = 'Jack', billing_lastname = 'Smith', billing_telephone = NULL, billing_postcode = NULL, billing_country_id = NULL, billing_region = NULL, billing_street = '123 Main street', billing_city = 'Anycity', billing_fax = NULL, billing_vat_id = NULL, billing_company = NULL;
UPDATE sales_creditmemo_grid SET billing_name = 'Sally', billing_address = '123 Main Street', customer_name = 'Jack Smith', customer_email = 'customer@example.com';
UPDATE magento_rma_grid SET customer_name = 'Jack Smith';
UPDATE newsletter_subscriber SET subscriber_email = 'customer@example.com';
UPDATE core_config_data SET value = '' WHERE path = 'orderexport/general/serial';
UPDATE core_config_data SET value = '' WHERE path = 'productexport/general/serial';
UPDATE core_config_data SET value = '' WHERE path = 'trackingimport/general/serial';
UPDATE core_config_data SET value = '' WHERE path = 'stockimport/general/serial';
UPDATE core_config_data SET value = '' WHERE path = 'remarketing/onescript/merchant_id';
UPDATE core_config_data SET value = '' WHERE path = 'remarketing/onescript/merchant_id';
UPDATE core_config_data SET value = '' WHERE path = 'algoliasearch_credentials/credentials/application_id';
UPDATE core_config_data SET value = '' WHERE path = 'algoliasearch_credentials/credentials/search_only_api_key';
UPDATE core_config_data SET value = '' WHERE path = 'tax/avatax/production_account_number';
UPDATE core_config_data SET value = '' WHERE path = 'tax/avatax/production_license_key';
UPDATE core_config_data SET value = '' WHERE path = 'design/head/includes';
UPDATE core_config_data SET value = '' WHERE path = 'payment/braintree/merchant_id';
UPDATE core_config_data SET value = '' WHERE path = 'payment/braintree/public_key';
UPDATE core_config_data SET value = '' WHERE path = 'payment/braintree/private_key';
UPDATE core_config_data SET value = '' WHERE path = 'system/full_page_cache/fastly/fastly_service_id';
UPDATE core_config_data SET value = '' WHERE path = 'system/full_page_cache/fastly/fastly_api_key';
UPDATE core_config_data SET value = '' WHERE path = 'google/analytics/container_id';
UPDATE core_config_data SET value = '' WHERE path = 'analytics/general/token';
UPDATE vault_payment_token SET public_hash = UUID(), details = '{"type":"VI","maskedCC":"1111","expirationDate":"01\/2019"}';
TRUNCATE customer_log;
TRUNCATE customer_visitor;
TRUNCATE magento_logging_event;
TRUNCATE oauth_consumer;
TRUNCATE oauth_nonce;
TRUNCATE oauth_token;
TRUNCATE password_reset_request_event;
TRUNCATE acknowledgement;
TRUNCATE acknowledgement_report;
TRUNCATE avatax_log;
TRUNCATE avatax_queue;
TRUNCATE cron_schedule;
SET FOREIGN_KEY_CHECKS=1;

Alternatively, you can delete the records instead of masking the information, which also make the new DB smaller. Once PII is masked or removed, the data can be safely provided to a teammate for use on their local environment.

Remote DB connection to an Adobe Commerce Cloud project

This method allows accidental editing and deletion of live data. Use it with caution. Prefer a database backup and offline review when you can. Sometimes you must access data directly on Adobe Commerce Cloud; that workflow still carries risk. GUIs do not add confirmation prompts, so you can alter or remove data by mistake.

A remote database connection is convenient but risky. You can easily forget that you are connected to production and delete or change data. You can connect to a read-only replica, but heavy SQL still affects the site. Adobe does not recommend routine remote connections to writable databases; use the steps below only when you understand the risks.

Establish an SSH tunnel:

magento-cloud tunnel:open

After the project is chosen and the environment is picked, there is output from the command that is used in the settings for the mysql graphical interface.

magento-cloud tunnel:open

Enter a number to choose a project:
  [0] demo-ralbin (ral32nryq4123)
  [1] adobe-commerce-demo (abc123zzkipexnqo)
  [2] DX Tutorials - Commerce (abasrpikfw4123)
 > 2

Enter a number to choose an environment:
Default: master
  [0] master (type: production)
  [1] remote-db (type: development)
 > 1

SSH tunnel opened to database at: mysql://user:@127.0.0.1:30000/main
SSH tunnel opened to redis at: redis://127.0.0.1:30001
SSH tunnel opened to opensearch at: http://127.0.0.1:30002
SSH tunnel opened to rabbitmq at: amqp://guest:guest@127.0.0.1:30003

Logs are written to: /Users/<user>/.magento-cloud/tunnels.log

List tunnels with: magento-cloud tunnels
View tunnel details with: magento-cloud tunnel:info
Close tunnels with: magento-cloud tunnel:close

Save encoded tunnel details to the MAGENTO_CLOUD_RELATIONSHIPS variable using:
  export MAGENTO_CLOUD_RELATIONSHIPS="$(magento-cloud tunnel:info --encode)"

Establish a connection using a MySQL graphical interface by using the SSH tunnel opened to database at command option.

SSH tunnel opened to database at: mysql://user:@127.0.0.1:30000/main

Now that you have the right information, enter these values in the Cloud Console.

You can find the SSH hostname and username from the cloud credentials in the Cloud Console.

Adobe Commerce Cloud Console

Here is one example: ssh abasrpikfw4123-remote-db-ecpefky--mymagento@ssh.us-4.magento.cloud
The SSH hostname is everything after the @ sign: ssh.us-4.magento.cloud in this example.
The SSH username is everything before the @ sign: abasrpikfw4123-remote-db-ecpefky--mymagento

Finding values to connect to the database

To access the MariaDB database directly, use SSH to log in to the remote Cloud environment and connect to the database.

  1. Use SSH to log in to the remote environment.

    code language-bash 
    magento-cloud ssh

  2. Retrieve the MySQL login credentials from the database and type properties in the $MAGENTO_CLOUD_RELATIONSHIPS variable.

    code language-bash 
    echo $MAGENTO_CLOUD_RELATIONSHIPS | base64 -d | json_pp

    or

    code language-bash 
    php -r 'print_r(json_decode(base64_decode($_ENV["MAGENTO_CLOUD_RELATIONSHIPS"])));'

    In the response, find the MySQL information. For example:

    code language-json 
    "database" : [
   {
      "password" : "",
      "rel" : "mysql",
      "hostname" : "nnnnnnnn.mysql.service._.magentosite.cloud",
      "service" : "mysql",
      "host" : "database.internal",
      "ip" : "###.###.###.###",
      "port" : 3306,
      "path" : "main",
      "cluster" : "projectid-integration-id",
      "query" : {
         "is_master" : true
      },
      "type" : "mysql:10.3",
      "username" : "user",
      "scheme" : "mysql"
   }
],

Then use the configuration values in your MySQL GUI. The following example uses MySQL Workbench, but any app that supports MySQL connections will have similar fields.

MySQL Workbench connection example TablePlus connection example

After you configure the connection, you can use a MySQL GUI to run queries on a remote Adobe Commerce Cloud project.

Connecting directly to the cloud project database to run SQL

The following method uses the magento-cloud CLI to connect directly to the MySQL database and run SQL for faster querying. If you need a copy of this database, use one of the alternative methods to create a database dump.

magento-cloud db:sql

Enter a number to choose a project:
  [0] demo-ralbin (ral32nryq4123)
  [1] adobe-commerce-demo (abc123zzkipexnqo)
  [2] DX Tutorials - Commerce (abasrpikfw4123)
 > 2

Enter a number to choose an environment:
Default: master
  [0] master (type: production)
  [1] remote-db (type: development)
 > 1

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 273454
Server version: 10.6.15-MariaDB-1:10.6.15+maria~deb10-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

For example, you can find all the records from the core_config_data table that contain the word secure as part of the column path:

MariaDB [main]> SELECT * FROM core_config_data WHERE path LIKE '%secure%' \G;
*************************** 1. row ***************************
 config_id: 5
     scope: default
  scope_id: 0
      path: web/unsecure/base_url
     value: http://remote-db-ecpefky-abasrpikfw4123.us-4.magentosite.cloud/
updated_at: 2024-02-02 18:03:17
*************************** 2. row ***************************
 config_id: 6
     scope: default
  scope_id: 0
      path: web/secure/base_url
     value: https://remote-db-ecpefky-abasrpikfw4123.us-4.magentosite.cloud/
updated_at: 2024-02-02 18:03:17
*************************** 3. row ***************************
 config_id: 8
     scope: default
  scope_id: 0
      path: web/secure/use_in_adminhtml
     value: 1
updated_at: 2023-04-26 19:43:58
3 rows in set (0.001 sec)

ERROR: No query specified

MariaDB [main]>

Additional resources

recommendation-more-help
3a5f7e19-f383-4af8-8983-d01154c1402f