MariaDB

WhyInstalling a MariaDB server with replication done on a second host, encryption will be used to protect communications.

Tuning

Tuning occurs principally in the server.cnf file in the mysqld section. It is raised this early because some of these settings cannot be changed once the server has been initialised.

ZFS

Adapt ZFS filesystem characteristics to have good performance with the selected storage engine.

https://www.percona.com/resources/technical-presentations/zfs-mysql-percona-technical-webinar

The configuration proposed is for MariaDB using InnoDB as main storage engine

with innodb_file_per_table=on data are created in $datadir/$db/$table
with ZFS primarycache disabled, it is vital to have recordsize matching MariaDB read

Due to 1+2 it is strongly advised to not create database/tables with a storage engine other than InnoDB if applying this configuration.

# Adapt to InnoDB storage engine
zfs set atime=off             system/services/mysql
zfs set sync=disabled         system/services/mysql
zfs set primarycache=metadata system/services/mysql
zfs set recordsize=16k        system/services/mysql

# Fallback to standard behaviour, but compress
zfs set sync=standard         system/services/mysql/logs
zfs set primarycache=all      system/services/mysql/logs
zfs set recordsize=128k       system/services/mysql/logs
zfs set compress=lz4          system/services/mysql/logs

# Ensure that newly created mountpoints have correct ownership
chown -R mysql:mysql /var/db/mysql

This operation must be done before MariaDB starts for the first time or creates files, otherwise the block size used will be the one configured when the various files are created.

The logs dataset keeps sync=standard precisely so the InnoDB redo log and the binary log can live there: with sync=disabled on the data dataset, fsync is a no-op, and durability options such as those of the Master section would silently be ineffective. The redo log is relocated accordingly (the binary log is placed there when configuring replication):

innodb-log-group-home-dir = /var/db/mysql/logs

In MariaDB the double write will be disabled and file system directly accessed as ZFS with its copy-on-write policy provide the necessary warranties to avoid half written data in case of crash:

innodb-doublewrite  = FALSE

SSL

Setting up SSL takes place in the client-server section of the my.cnf configuration file if it is to be shared between client and server; otherwise it can be put in the dedicated section of the client.cnf and server.cnf files.

Client side (without authenticating the client)

[client]
ssl-ca   = Chain of certificate authorities

Server side:

[mysqld]
ssl-ca   = Chain of certificate authorities
ssl-cert = Server certificate
ssl-key  = Certificate key

To require SSL encryption for a particular user add REQUIRE SSL to the GRANT, for example:

GRANT USAGE ON *.* TO 'user'@'hostname' REQUIRE SSL;

If an SSL certificate has been updated, it can be reloaded without interrupting the server:

FLUSH LOCAL SSL; -- using LOCAL so it is not propagated to the replicas

Replication

Replication can be incorrect with the use of LIMIT clauses in DELETE, UPDATE, and INSERT ... SELECT statements if ORDER BY is not specified (See MariaDB documentation for details).
Servers, master or slaves, participating in the replication process need to be identified by a unique id set with the server-id directive.

Master

The master will be configured (in server.cnf) using these steps:

We specify the server unique id and the file to use for the log file (which is at the heart of replication), placed on the logs dataset (see ZFS)
```
server-id                      = Unique server identifier
log-bin                        = /var/db/mysql/logs/mysql-binlog
```
For a behavior more resistant to crashes (ACID), it is necessary to activate the following options, but it has a cost on performances:
```
innodb-flush-log-at-trx-commit = 1
sync-binlog                    = 1
```
The log file can grow indefinitely, so it is wise to limit its expansion, we will assume that slaves would have time to synchronize in a reasonable amount of time and only keep here the last 45 days. But if your database sustains heavy modification and you don’t have disk space you should reduce it to a few days:
```
expire-logs-days               = 45
```

Checking master configuration and correct operations:

SHOW MASTER STATUS;

To setup the replication it is necessary to have an administrative account with “replication” rights, we choose to name it db-sync. We will consider that the backup server for our slave (Replication server) is not on the same network and will require an SSL connection to maintain confidentiality:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
      TO 'db-sync'@Replication server
      IDENTIFIED BY Password REQUIRE SSL;

Slave

If for some reasons the mysql database used by MariaDB to hold information about privileges, users and other internal data should not be replicated, it is possible to exclude it (and other databases as well if needed) by setting in server.cnf: replicate-wild-ignore-table=mysql.% and restarting mariadbd.

The slave configuration can be done entirely in the my.cnf file, but in this case one need to be aware that future modification of the file concerning master information will be ignored. This is why the configuration will be split into two steps:

The configuration file server.cnf specifying the unique server id and a relay log file:
```
server-id                      = Unique server identifier
relay-log                      = relay-log
```
The server-id value must be different for each server.
(Verify that the value for the master server hasn’t been reused)

SQL configuration of the master information, we will setup an SSL connection without verification of the master certificate (such a verification can be done by changing value for the MASTER_SSL_VERIFY_SERVER_CERT):

CHANGE MASTER Connection name TO
  MASTER_HOST                   = Master host,
  MASTER_USER                   = "db-sync",
  MASTER_PASSWORD               = Password,
  MASTER_USE_GTID               = slave_pos,
  MASTER_HEARTBEAT_PERIOD       = 30,
  MASTER_SSL                    = 1,
  MASTER_SSL_CA                 = Chain of certificate authorities,
  MASTER_SSL_VERIFY_SERVER_CERT = 0;

The MASTER_SSL_CA file must contain the complete certificate chain.
If it holds only an intermediate certificate, the I/O thread fails with the opaque message:
SSL connection error: error:00000000:lib(0)::reason(0).
A CA path is only exercised when MASTER_SSL = 1.

Checking slave configuration and correct operations:

SHOW ALL SLAVES STATUS\G           -- Show status for all replicas
SHOW SLAVE Connection name STATUS\G  -- Show status for the specified replica connection

Renaming a replicated database

It can be necessary, especially when doing multi-master replication, to rename the replicated database to avoid a clash with an already existing database on the replication server.

There is no option to do it when dumping or importing the database, so the SQL statements are patched in the dump before loading it during initialization:

db-rename

# Usage: cat dump.sql | db-rename db1_from db1_to | db-rename db2_from db2_to
gsed -E \
  -e 's/^(CREATE DATABASE (\/*(.*?)*\/ )?`)'$1'(`( \/*(.*?)*\/)?;)/\1'$2'\4/' \
  -e 's/^(USE `)'$1'(`;)$/\1'$2'\2/'

cat Dumped databases | db-rename Database name on master Database name on replicated server | db-rename Database name on master Database name on replicated server

and renaming rules are inserted in server.cnf on the slave:

master-name.replicate-rewrite-db=Database name on master->Database name on replicated server
master-name.replicate-rewrite-db=Database name on master->Database name on replicated server

Renaming does not protect the mysql database: replicate-rewrite-db only affects statements involving tables, and DROP USER / CREATE USER statements would still apply to it, clobbering the replica’s own privileges. To exclude it:

master-name.replicate-ignore-db=mysql

Initialization

As SET GLOBAL gtid_slave_pos replaces the whole position list (there is no way to update a single domain), the positions of the other domains have to be merged in by hand. A script to do that:

Download the script gtid-merge
Setting sql_log_bin to 0 keeps the restore out of the binary log. Without it the restore is written to the binlog and sent to any downstream replica — possibly back to the master it came from — and adds one GTID in the local domain per transaction. It is provisioning, not new data.

Creating an SQL dump on the master side:

Session 1	Session 2
`FLUSH TABLES WITH READ LOCK;`
(keep the client running)	`mariadb-dump --gtid --opt \ --apply-slave-statements \ --all-databases --flush-logs \ --master-data=2 > dump.sql`
`UNLOCK TABLES;`

Session 1 will protect against table modification (such as ALTER, DROP, RENAME, TRUNCATE), while session 2 will perform the dump locking all tables (implicit with --master-data) protecting against row modifications (such as INSERT, DELETE, UPDATE). If all databases are of InnoDB type it is possible to add --single-transaction so to have a dump without blocking row modifications while still having consistent data.

Using --apply-slave-statements will insert necessary “STOP/START SLAVE” statements, so it won’t be necessary to manually stop/start slave when importing dump.

With --master-data the CHANGE MASTER statement is generated without the connection name: using =2 it stays commented out (the position is set through gtid_slave_pos in the next step); with =1 the dump would have to be edited to insert the connection name.

Loading and starting the slave (on slave side):

mariadb-admin stop-all-slaves                   # Stop all slaves
gtid=`gtid-merge Dumped databases`                      # Query DB and merge GTID
mariadb -e "SET GLOBAL gtid_slave_pos='$gtid'"  # Position new GTID
{ echo "SET SESSION sql_log_bin=0;";            # Avoid re-propagating
  cat Dumped databases; } | mariadb                     #  ... and load data
mariadb-admin start-all-slaves                  # Restart slaves

Flushing privileges on slave (eventually)

mariadb -e 'FLUSH PRIVILEGES'    # If `mysql` table was altered

Backup

The following script allows to perform a full backup from the slave while ensuring that data is in a consistent state:

#!/bin/sh
dir=Backup directory
file=backup-`date +%Y%m%d`.sql.bz2
mariadb-admin stop-all-slaves
mariadb-dump --comments --quote-names --routines --opt \
          --lock-all-tables --all-databases | bzip2 > "${dir}/${file}"
mariadb-admin start-all-slaves

Commands

Show grants for user

SHOW GRANTS FOR user

Show table creation statement

SHOW CREATE TABLE table_name