Step-by-step setup of the replicated session store

This section gives step-by-step instructions on how to set up the replicated session store.

The first steps deal with the creation of the database and the users. Log in as root into your MariaDB client. Replace the example passwords by the values of your choice and run the following SQL statements to set up the DB schema.

1. Create the replicated database with the following commands:

CREATE DATABASE IF NOT EXISTS replicated_session_store CHARACTER SET = 'utf8mb4' COLLATE = 'utf8mb4_general_ci';

2. Create the nevisProxy user by running the following commands:

CREATE USER IF NOT EXISTS nevisproxy_user IDENTIFIED BY 'nevisproxypassword';
GRANT REPLICATION CLIENT ON *.* TO nevisproxy_user;
GRANT ALL PRIVILEGES ON replicated_session_store.* TO nevisproxy_user;

We recommend installing the DB for the remote session store on another host as nevisProxy to improve the fail-safe scheme. In case you wish to have the proxy and the DB on the same host, you might have to grant the DB users the permission to connect from 'localhost':

CREATE USER IF NOT EXISTS 'nevisproxy_user'@'LocalHostName' IDENTIFIED BY 'nevisproxypassword';
GRANT REPLICATION CLIENT ON *.* TO 'nevisproxy_user'@'LocalHostName';
GRANT ALL PRIVILEGES ON replicated_session_store.* TO 'nevisproxy_user'@'LocalHostName';

3. Create the replication user by running the following commands:

CREATE USER IF NOT EXISTS replication_user IDENTIFIED BY 'replicationpassword';
GRANT REPLICATION SLAVE ON *.* TO replication_user;

4. Create the binary log user with the following commands:

CREATE USER IF NOT EXISTS binarylog_user IDENTIFIED BY 'binarylogspassword';
GRANT SUPER ON *.* TO binarylog_user;

5. Install the plug-ins for the semi-synchronous replication:

This step is no longer necessary as of MariaDB version 10.3.3.

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

6. Configure the MariaDB service. To configure the MariaDB service, add the following entries to the file /etc/my.cnf as super user. The two configuration files (host-db1 and host-db2) differ at some points. The different lines are marked with ( * ).

• Configure the MariaDB service on host-db1:

Configuration of MariaDB service on host-db1

[mariadb]
# Enabling binary log
log-bin
# The ID of this master (*)
server_id=1
# The ID of the replication stream created by this master (*)
gtid-domain-id=1
# The basename and format of the binary log
log-basename=mariadbmaster
binlog-format=MIXED
# Setting which tables are replicated
replicate_wild_do_table="replicated_session_store.%session"
replicate_wild_do_table="replicated_session_store.%attribute"
replicate_wild_do_table="replicated_session_store.%key_id_map"
replicate_wild_do_table="replicated_session_store.%conf"
# Avoiding collisions of primary IDs for tables where the primary ID is auto-incremented
# Auto-increment value
auto_increment_increment=2
# Auto-increment offset (*)
auto_increment_offset=1
# Suppressing duplicated keys errors for multi-master setup
slave_exec_mode=IDEMPOTENT
# Ignoring some data definition language errors
slave-skip-errors=1452,1062
# Suppressing binary logs after a delay regardless of the replication status
expire_logs_days=1
# Maximum number of connections
max_connections=1000
# Size of each of the binary log files (default: 1GB)
max_binlog_size=500M
# Enabling writing to the DB in parallel threads for the replication
slave-parallel-threads=10
# enabling semi-synchronous replication
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON
# change to READ COMMITTED
transaction-isolation=READ-COMMITTED
# will make sure sync process does not stop after a deadlock
innodb_deadlock_detect=0

• Configure the MariaDB service on host-db2:

Configuration of MariaDB service on host-db2

[mariadb]
# Enabling binary log
log-bin
# The ID of this master (*)
server_id=2
# The ID of the replication stream created by this master (*)
gtid-domain-id=2
# The basename and format of the binary log
log-basename=mariadbmaster
binlog-format=MIXED
# Setting which tables are replicated
replicate_wild_do_table="replicated_session_store.%session"
replicate_wild_do_table="replicated_session_store.%attribute"
replicate_wild_do_table="replicated_session_store.%key_id_map"
replicate_wild_do_table="replicated_session_store.%conf"
# Avoiding collisions of primary IDs for tables where the primary ID is auto-incremented
# Auto-increment value
auto_increment_increment=2
# Auto-increment offset (*)
auto_increment_offset=2
# Suppressing duplicated keys errors for multi-master setup
slave_exec_mode=IDEMPOTENT
# Ignoring some data definition language errors
slave-skip-errors=1452,1062
# Suppressing binary logs after a delay regardless of the replication status
expire_logs_days=1
# Maximum number of connections
max_connections=1000
# Size of each of the binary log files (default: 1GB)
max_binlog_size=500M
# Enabling writing to the DB in parallel threads for the replication
slave-parallel-threads=10
# enabling semi-synchronous replication
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_slave_enabled=ON
# change to READ COMMITTED
transaction-isolation=READ-COMMITTED
# will make sure sync process does not stop after a deadlock
innodb_deadlock_detect=0

• Restart the mysql servers on both hosts:

sudo service mysql restart

7. Start the replication. To start the replication, log in as root into your MariaDB client and run the following commands.

• On host-db1 (master is host-db2):

Setting master on host-db1

CHANGE MASTER TO
MASTER_HOST='host-db2',
MASTER_USER='replication_user',
MASTER_PASSWORD='replicationpassword',
MASTER_PORT=3306,
MASTER_USE_GTID=current_pos,
MASTER_CONNECT_RETRY=10;

• On host-db2 (master is host-db1):

Setting master on host-db2

CHANGE MASTER TO
MASTER_HOST='host-db1',
MASTER_USER='replication_user',
MASTER_PASSWORD='replicationpassword',
MASTER_PORT=3306,
MASTER_USE_GTID=current_pos,
MASTER_CONNECT_RETRY=10;

• On host-db1:

Starting replication on host-db1

START SLAVE;

• On host-db2:

Starting replication on host-db2

START SLAVE;