DocumentationCommerceCommerce on Cloud Infrastructure Guide

Set up MySQL service

Last update: Wed Oct 18 2023 00:00:00 GMT+0000 (Coordinated Universal Time)

CREATED FOR:

  • Admin
  • Developer

The mysql service provides persistent data storage based on MariaDB versions 10.2 to 10.4, supporting the XtraDB storage engine and reimplemented features from MySQL 5.6 and 5.7.

Reindexing on MariaDB 10.4 takes more time compared to other MariaDB or MySQL versions. See Indexers in the Performance Best Practices guide.

WARNING
Be careful when upgrading MariaDB from version 10.1 to 10.2. MariaDB 10.1 is the last version that supports XtraDB as the storage engine. MariaDB 10.2 uses InnoDB for the storage engine. After you upgrade from 10.1 to 10.2, you cannot roll back the change. Adobe Commerce supports both storage engines; however, you must check extensions and other systems used by your project to make sure they are compatible with MariaDB 10.2. See Incompatible Changes Between 10.1 and 10.2.

Use the following instructions for service setup on Pro Integration environments and Starter environments, including the master branch.

NOTE
Submit an Adobe Commerce Support ticket to change the service configuration on Pro Production and Staging environments.

To enable MySQL:

  1. Add the required name, type, and disk value (in MB) to the .magento/services.yaml file.

    code language-yaml 
    mysql:
    type: mysql:<version>
    disk: 5120
    note tip
    TIP
    MySQL errors, such as PDO Exception: MySQL server has gone away, can occur as a result of insufficient disk space. Verify that you have allocated sufficient disk space to the service in the .magento/services.yaml file.

  2. Configure the relationships in the .magento.app.yaml file.

    code language-yaml 
    relationships:
    database: "mysql:mysql"

  3. Add, commit, and push your code changes.

    code language-bash 
    git add .magento/services.yaml .magento.app.yaml && git commit -m "Enable mysql service" && git push origin <branch-name>

  4. Verify the service relationships.

TIP
After initial service setup, you can change the software version for an installed service by updating the services.yaml and .magento.app.yaml configuration files. See Change service version for guidance on upgrading or downgrading a service.

Configure MySQL database

You have the following options when configuring the MySQL database:

  • schemas—A schema defines a database. The default schema is the main database.
  • endpoints—Each endpoint represents a credential with specific privileges. The default endpoint is mysql, which has admin access to the main database.
  • properties—Properties are used to define additional database configurations.

The following is a basic example configuration in the .magento/services.yaml file:

mysql:
    type: mysql:10.4
    disk: 5120
    configuration:
        properties:
            optimizer_switch: "rowid_filter=off"
            optimizer_use_condition_selectivity: 1

The properties in the above example modifies the default optimizer settings as recommended in the Performance Best Practices guide.

MariaDB configuration options:

Options
Description
Default value
default_charset
The default character set.
utf8mb4
default_collation
The default collation.
utf8mb4_unicode_ci
max_allowed_packet
Maximum size for packets, in MB. Range 1 to 100.
16
optimizer_switch
Set values for the query optimizer. See MariaDB documentation.
optimizer_use_condition_selectivity
Select the statistics used by the optimizer. Range 1 to 5. See MariaDB documentation.
4 for 10.4 and later

Set up multiple database users

Optionally, you can set up multiple users with different permissions for accessing the main database.

By default, there is one endpoint named mysql that has administrator access to the database. To set up multiple database users, you must define multiple endpoints in the services.yaml file and declare the relationships in the .magento.app.yaml file. For Pro Staging and Production environments, Submit an Adobe Commerce Support ticket to request the additional user.

Use a nested array to define the endpoints for specific user access. Each endpoint can designate access to one or more schemas (databases) and different levels of permission on each.

The valid permission levels are:

  • ro: Only SELECT queries are allowed.
  • rw: SELECT queries and INSERT, UPDATE, and DELETE queries are allowed.
  • admin: All queries are allowed, including DDL queries (CREATE TABLE, DROP TABLE, and more).

For example:

mysql:
    type: mysql:10.4
    disk: 5120
    configuration:
        schemas:
            - main
        endpoints:
            admin:
                default_schema: main
                privileges:
                    main: admin
            reporter:
                privileges:
                    main: ro
            importer:
                privileges:
                    main: rw
        properties:
            optimizer_switch: "rowid_filter=off"
            optimizer_use_condition_selectivity: 1

In the preceding example, the admin endpoint provides admin-level access to the main database, the reporter endpoint provides read-only access, and the importer endpoint provides read-write access, which means:

  • The admin user has full control of the database.
  • The reporter user has SELECT privileges only.
  • The importer user has SELECT, INSERT, UPDATE, and DELETE privileges.

Add the endpoints defined in the above example to the relationships property of the .magento.app.yaml file. For example:

relationships:
    database: "mysql:admin"
    databasereporter: "mysql:reporter"
    databaseimporter: "mysql:importer"
NOTE
If you configure one MySQL user, you cannot use the DEFINER access control mechanism for stored procedures and views.

Connect to the database

Accessing the MariaDB database directly requires you to use an 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"
   }
],

  3. Connect to the database.

    • For Starter, use the following command:

      code language-bash 
      mysql -h database.internal -u <username>

    • For Pro, use the following command with hostname, port number, username, and password retrieved from the $MAGENTO_CLOUD_RELATIONSHIPS variable.

      code language-bash 
      mysql -h <hostname> -P <number> -u <username> -p'<password>'
TIP
You can use the magento-cloud db:sql command to connect to the remote database and run SQL commands.

Connect to secondary database

Sometimes, you have to connect to the secondary database to improve database performance or resolve database locking issues. If this configuration is required, use "port" : 3304 to establish the connection. See the Best practice to configure the MySQL slave connection topic in the Implementation Best Practices guide.

Troubleshooting

See the following Adobe Commerce Support articles for help with troubleshooting MySQL problems:

05f2f56e-ac5d-4931-8cdb-764e60e16f26