Table of content
  1. Tuning
    1. InnoDB
    2. ZFS
  2. SSL
  3. Replication
    1. Master
    2. Slave
    3. Initialization
  4. Backup
  5. Commands

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

Build information

Ensure the following options:

databases/mariadb105-server
1
2
3
4
5
6
7
8
9
10
11
12

[x] CONNECT_EXTRA   Enable ODBC and XML in CONNECT engine
[x] WSREP           Build wsrep clustering
[x] LZ4             LZ4 compression support
[x] LZO             LZO compression support
[x] INNOBASE        InnoDB default engine
[ ] MROONGA         Mroonga Full Text Search engine
[x] OQGRAPH         Open Query Graph Computation engine
[x] SPHINX          SphinxSE engine
[x] SPIDER          Partitioning and XA-transactions engine
[x] ZMQ             ZeroMQ support
[x] MSGPACK         MsgPack support
(*) GSSAPI_NONE     Disable GSSAPI support

Tuning

Tuning occurs principally in the server.cnf file in the mysqld section. We are talking tuning this soon as some modifications won’t be possible after MySQL initialisation.

InnoDB

Increase the size of the log file (which default value is around 5MB) so that InnoDB can optimize its I/O without taking too much time in case of recovery:

1

innodb-log-file-size=64M

ZFS

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

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

  1. with innodb_file_per_table=on data are created in $datadir/$db/$table
  2. with ZFS primarycache disabled, it is vital to have recordsize matching mysql 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 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 MySQL start for the first time or create files, otherwise the block size used will be the one configured when the various files are created.

In MySQL 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:

server.cnf
1

innodb-doublewrite  = FALSE

SSL

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

Client side (without authenticating the client)
client.cnf
1
2

[client]
ssl-ca   = /etc/cert/cachain.pem
Server side:
server.cnf
1
2
3
4

[mysqld]
ssl-ca   = /etc/cert/cachain.pem
ssl-cert = /etc/cert/wildcard.example.com.crt
ssl-key  = /etc/cert/wildcard.example.com.key

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

1

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

Replication

Master

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

  1. We specify the server unique id and the file to use for the log file (which is at the heart of replication)

    1
2

    server-id                      = 0001
log-bin                        = mysql-binlog

  2. For a behavior more resistant to crashes (ACID), it is necessary to activate the following options, but it has a cost on performances:

    1
2

    innodb-flush-log-at-trx-commit = 1
sync-binlog                    = 1

  3. 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 sustain heavy modification and you don’t have disk space you should reduce it to a few days:

    1

    expire-logs-days               = 45

Checking master configuration and correct operations:

1

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 (backup.example.com) is not on the same network and will require an SSL connection to maintain confidentiality:

1
2
3

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* 
      TO 'db-sync'@'backup.example.com'
      IDENTIFIED BY 'a_real_password' REQUIRE SSL;

Slave

If for some reasons the mysql database used by MySQL 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), the mysqld must be restarted with the option: --replicate-wild-ignore-table=mysql.%

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 to steps:

  1. The configuration file server.cnf specifying the unique server id and a relay log file:

    1
2

    server-id                      = 1001
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)

  2. 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):

    1
2
3
4
5
6
7
8
9

    CHANGE MASTER "master-name" TO
  MASTER_HOST                   = "mysql.example.com",
  MASTER_USER                   = "db-sync", 
  MASTER_PASSWORD               = "a_real_password",
  MASTER_USE_GTID               = slave_pos,
  MASTER_HEARTBEAT_PERIOD       = 30,
  MASTER_SSL                    = 1,
  MASTER_SSL_CA                 = "/etc/cert/cachain.pem",
  MASTER_SSL_VERIFY_SERVER_CERT = 0;

Checking slave configuration and correct operations:

1
2

show all slaves status\G    -- Show status for all replicas
show slave "master-name"\G  -- Show status for the specified replica connection

Initialization

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

Unfortunately there is no option to do it when dumping or importing the database, so we will need to “patch” the SQL statements in the dumped database:

db-rename
1
2
3
4

# 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/'

1

cat dump.sql | db-rename db1_from db1_to | db-rename db2_from db2_to

and insert renaming rules in server.cnf on the slave:

server.cnf
1
2

master-name.replicate-rewrite-db=db1_from->db1_to
master-name.replicate-rewrite-db=db2_from->db2_to

It could be necessary to skip the mysql database during replication if you don’t want to clobber your slave instance. Note that doing a replicate-rewrite-db is not enough as it will execute some DROP USER / CREATE USER statement that would apply anyway to the mysql database.

1

master-name.replicate-ignore-db=mysql

When using mariadb-dump --master-data the CHANGE MASTER statement is generated without the connection name so it will be necessary to either

  • use –master-data=2 so that the related statement are commented, you will then need to apply them manually (with the connection name added)
  • edit the dump to insert the connection name

  1. 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 > 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.

  2. Loading and starting the slave (on slave side):

    1
2
3

    mysqladmin stop-all-slaves     # Stop all slaves
mysql < dump.sql               # Load data
mysqladmin start-all-slaves    # Restart slaves

  3. Flushing privileges on slave (eventually)

    1

    mysql -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:

1
2
3
4
5
6
7

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

Commands

Show grants for user
1

SHOW GRANTS FOR user
Show table creation statement
1

SHOW CREATE TABLE table_name