MariaDb

• 1: Backup
• 2: Configuration
• 3: Importing Data
• 4: Limitations
• 5: Overview
• 6: Restore
• 7: TLS Connection

1 - Backup

Mariabackup is used to create backups.

CCX backups the Primary server. In multi-primary setups the node with the highest wsrep_local_index is elected.

Backups are streamed directly to S3 staroge.

Mariabackup blocks DDL operations during the backup using the --lock-ddl flag. Any attempt to CREATE, ALTER, DROP, TRUNCATE a table during backup creation will be locked with the status Waiting for backup lock (see SHOW FULL PROCESSLIST). In this case, wait for the backup to finish and, perform the operation later.

Also see the section ‘Schedule’.

Schedule

The backup schedule can be tuned and backups can be paused

2 - Configuration

max_connections

• 75 connections / GB of RAM.
• Example: 4GB of RAM yields 300 connections.
• This setting cannot be changed as it affects system stability.

InnoDB settings

• These setting cannot be changed as it affects system stability.

innodb_buffer_pool_size

• 50% of RAM if total RAM is > 4GB
• 25% of RAM if total RAM is <= 4GB

innodb_log_file_size

• 1024 MB if innodb_buffer_pool_size >= 8192MB
• 512 MB if innodb_buffer_pool_size < 8192MB

innodb_buffer_pool_instances

• 8

InnoDB options

General options

Storage

Recommended storage size

• We recommend a maximum of 100GB storage per GB of RAM.
• Example: 4GB of RAM yields 400GB of storage.
• The recommendation is not enforced by the CCX platform.

3 - Importing Data

This procedure describes how to import data to a MariaDB datastore located in CCX.

• The MariaDB Datastore on CCX is denoted as the ‘replica’
• The source of the data is denoted as the ‘source’

note:

If you do not want to setup replication, then you can chose to only apply the sections:

• Create a database dump file
• Apply the dumpfile on the replica

Limitations of MariaDB

MariaDB does not offer as fine grained control over privileges as MySQL. Nor does it have the same level of replication features.

The following properties must be respected in order to comply with the SLA:

• There must be no user management happening on the source, while the data is imported and the replication link is active. This is avoid corruption of the mysql database and possibly other system databases.
• It is recommended to set binlog-ignore-db on the source to ‘mysql, performance_schema, and sys’ during the data import/sync process.

Preparations

Ensure that the source is configured to act as a replication source.

• Binary logging is enabled.
• server_id is set to non 0.

Also, prepare the replica with the databases you wish to replicate from the source to the master:

• Using the CCX UI, go to Databases, and issue a Create Database for each database that will be replicated.

Ensure the CCX Firewall is updated:

• Add the replication source as a Trusted Source in the Firewall section of the CCX UI.

Create a replication user on the source

Create a replication user with sufficient privileges on the source:

CREATE USER 'repluser'@'%' IDENTIFIED BY '<SECRET>';
GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%';

Prepare the replica to replicate from the source

The replica must be instrucuted to replicate from the source. Make sure to change <SOURCE_IP>, <SOURCE_PORT>, and <SECRET>. Run the following on the source:

CHANGE MASTER TO MASTER_HOST=<SOURCE_IP>, MASTER_PORT=<SOURCE_PORT>, MASTER_USER='repluser', MASTER_PASSWORD='<SECRET>', MASTER_SSL=1;

Create a database dump file of the source

The database dump contains the data that you wish to import into the replica. Only partial dumps are possible. The dump must not contains any mysql or other system databases.

danger: The dump must not contains any mysql or other system databases.

On the source, issue the following command. Change ADMIN, SECRET and DATABASES:

mysqldump -uADMIN -p<SECRET>   --master-data --single-transaction --triggers --routines --events --databases DATABASES > dump.sql`

If your database dump contains SPROCs, triggers or events, then you must replace DEFINER. This may take a while:

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i dump.sql

Apply the dumpfile on the replica

cat dump.sql | mysql -uccxadmin -p -h<REPLICA_PRIMARY>

Start the replica

On the replica do:

START SLAVE

followed by

SHOW SLAVE STATUS;

And verify that:

             Slave_IO_State: Waiting for source to send event
	     ..
  	     Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

When the migration is ready

STOP SLAVE;
RESET SLAVE ALL;

Troubleshooting

If the replication fails to start then verify:

• All the steps above has been followed.
• Ensure that the replication source is added as a Trusted Source in the Firewall section of the CCX UI.
• Ensure that you have the correct IP/FQDN of the replication source.
• Ensure that users are created correctly and using the correct password.
• Ensure that the dump is fresh.

4 - Limitations

Every product has limitations. Here is a list MariaDB limitations:

Permissions

The privilege system is not as flexible as in MySQL.

The ‘ccxadmin’ user has the following privileges:

Global / all databases (.):

• CREATE USER, REPLICATION SLAVE, REPLICATION SLAVE ADMIN, SLAVE MONITOR

On databases created from CCX, the admin user can create new users and grant privileges:

• ALL PRIVILEGES WITH GRANT OPTION

This means that users can only create databases from the CCX UI. Once the database has been created from the CCX UI, then the ccxadmin user can create users and grant user privileges on the database using MariaDB CLI.

5 - Overview

CCX supports two types of MariaDB clustering:

• MariaDB Replication (Primary-replica configuration)x
• MariaDB Cluster (Multi-primary configuration)

For general purpose applications we recommend using MariaDB Replication, and we only recommend to use MariaDB Cluster if you are migrating from an existing application that uses MariaDB Cluster.

If you are new to MariaDB Cluster we stronly recommend to read about the MariaDB Cluster 10.x limitations and MariaDB Cluster Overview to understand if your application can benefit from MariaDB Cluster.

MariaDB Replication uses the standard asynchronous replication based on GTIDs.

Scaling

Storage and nodes can be scaled online.

Nodes (horizonal)

• The maximum number of database nodes in a datastore is 5.
• Multi-primary configuration must contain an odd number of nodes (1, 3 and 5).

Nodes (vertical)

A node cannot be scaled vertically currently. To scale to large instance type, then a larger instance must be added and then remove the unwanted smaller instances.

Storage

• Maximum size depends on the service provider and instance size
• Volume type cannot currently be changed

Further Reading

• Limitations of MariaDB
• Configuration
• Importing Data

6 - Restore

There are two options to restore a backup:

• Restore a backup on the existing datastore
• Restore a backup on a new datastore

Please note that restoring a backup may be a long running process.

This option allows to restore a backup with point in time recovery. The WAL logs are replayed until the desired PITR.

Warning! Running several restores may change the timelines.

This option allows to restore a backup on a new datastore. This option does not currently support PITR.

7 - TLS Connection

SSL Modes

CCX currently supports connections to MariaDB in two SSL modes:

1. REQUIRED: This mode requires an SSL connection. If a client attempts to connect without SSL, the server rejects the connection.
2. VERIFY_CA: This mode requires an SSL connection and the server must verify the client’s certificate against the CA certificates that it has.

CA Certificate

The Certificate Authority (CA) certificate required for VERIFY_CA mode can be downloaded from your datastore on CCX using an API call or through the user interface on page https://{your_ccx_domain}/projects/default/data-stores/{datastore_id}/settings. This certificate is used for the VERIFY_CA SSL mode.

Example Commands

Here are example commands for connecting to the MySQL server using the two supported SSL modes:

1. REQUIRED mode:

   mysql --ssl-mode=REQUIRED -u username -p -h hostname
   

2. VERIFY_CA mode:

   mysql --ssl-mode=VERIFY_CA --ssl-ca=ca.pem -u username -p -h hostname
   

require_secure_transport

This is a MariaDB setting that governs if connections to the datastore are required to use SSL. You can change this setting in CCX in Settings -> DB Parameters

Examples

ssl-mode=DISABLED and require_secure_transport=OFF

mysql -uccxadmin -p -h...  -P3306 ccxdb --ssl-mode=disabled
...
mysql> \s
--------------
...
Connection id:		52
Current database:	ccxdb
Current user:		ccxadmin@...
*SSL:			Not in use*
Current pager:		stdout
...

ssl-mode=PREFERRED and require_secure_transport=OFF

mysql -uccxadmin -p -h...  -P3306 ccxdb --ssl-mode=preferred
...
mysql> \s
--------------
...
Connection id:		52
Current database:	ccxdb
Current user:		ccxadmin@...
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		stdout
...

ssl-mode=DISABLED and require_secure_transport=ON

mysql -uccxadmin -p -h...  -P3306 ccxdb --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 3159 (08004): Connections using insecure transport are prohibited while --require_secure_transport=ON.

ssl-mode=PREFERRED|REQUIRED and require_secure_transport=ON

mysql -uccxadmin -p -h...  -P3306 ccxdb --ssl-mode=preferred|required
mysql> \s
--------------
...
Connection id:		52
Current database:	ccxdb
Current user:		ccxadmin@...
SSL:			Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:		stdout
...

tls_version

The tls_version is set to the following by default: