Set up MySQL service

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

Be careful when upgrading MariaDB from version 10.1 to 10.2. MariaDB 10.1 is the last version that support XtraDB as the storage engine. Version 10.2 uses InnoDB for the storage engine. Once you upgrade from 10.1 to 10.2, you cannot rollback the change. Magento 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. Read about this backwards incompatible change.

You can use the following instructions for service setup on Magento Commerce Cloud Pro Integration environments and Starter environments, including master branch. You must submit a support ticket to configure the service on Pro Production and Staging environments. See Services.

Enable MySQL

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

   1
   2
   3
   mysql:
       type: mysql:<version>
       disk: 2048
   

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

   1
   2
   relationships:
       database: "mysql:mysql"
   

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

   1
   git add -A && git commit -m "Enable mysql service" && git push   origin <branch-name>
   

4. Verify the service relationships.

MySQL errors such as PDO Exception: MySQL server has gone away may be a result of exhausting existing disk space. Verify that you have allocated sufficient disk space to the service in the .magento/services.yaml file.

Set up multiple database users

Optionally, you can set up multiple databases as well as multiple users with different permissions.

An endpoint is a set of credentials (or users) with specific privileges. By default, there is one endpoint named mysql that has administrator access to all defined databases. 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.

You cannot use multiple databases with Magento Commerce at this time, but you can create multiple endpoints to restrict access to the main database.

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 so on).

If no endpoints are defined, a single endpoint named mysql has admin access to the main database. For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql:
    type: mysql:10.2
    disk: 2048
    configuration:
        schemas:
            - main
        endpoints:
            admin:
                default_schema: main
                privileges:
                    main: admin
            reporter:
                privileges:
                    main: ro
            importer:
                privileges:
                    main: rw

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. This means that:

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

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

1
2
3
4
relationships:
    database: "mysql:admin"
    databasereporter: "mysql:reporter"
    databaseimporter: "mysql:importer"

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 a SSH to log in to the remote server, and connect to the database.

1. Log in to the remote server using SSH.

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

   1
   echo $MAGENTO_CLOUD_RELATIONSHIPS | base64 -d | json_pp
   

   or

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

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

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   "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.0",
         "username" : "user",
         "scheme" : "mysql"
      }
   ],
   

3. Connect to the database:

   • For Starter, use the following command:

     1
     mysql -h database.internal -u <username>
     

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

     1
     mysql -h<db> -P<number> -u<username> -p<password>