Setting up replication on the MySQL database.

Setting up replication on the MySQL database.
Photo by Jandira Sonnendeck / Unsplash

Base AMI

Setup filesystem

Create filesystem on nvme volume
Mount the nvme volume
Get UUID, setup file table

sudo lsblk -o +UUID

Test auto mount
Test mount point

sudo chown -R mysql:mysql /data/mysql/
sudo chown -R 750 /data/mysql/

Move MySQL data dir

Mount point is /data,

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

datadir         = /data/mysql
sudo mv /var/lib/mysql /var/lib/mysql.bak/
sudo rsync -av /var/lib/mysql.bak/ /data/mysql
sudo mkdir /var/lib/mysql/mysql -p

This was needed to determine/fix startup issues

sudo mysqld_safe --datadir=/data/mysql

Recovery strategy

So far only manual or non AWS native solutions

  • periodic rsync, i.e. copying to an attached EBS
  • any other system backup tools

Configure AppArmor

sudo nano /etc/apparmor.d/tunables/alias

alias /var/lib/mysql/ -> /data/mysql/,

sudo systemctl restart apparmor.service

Finally, create the AMI

Configure Master

Set up the new data directory

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

datadir         = /data/mysql
explicit_defaults_for_timestamp = 1
sudo nano /etc/mysql/my.cnf

# Replication config
[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Create replication user

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

Dump existing data

Either stop the mysql-server or run this, keep the session open

FLUSH TABLES WITH READ LOCK;

-- after dump completes
UNLOCK TABLES;

Tmux or in another session, this also attaches the binlog and position info.

mysqldump --master-data -uroot -p --databases sourcedbname > sourcedbname.sql

Configure replica

If the server UUID conflicts/is the same, then

sudo rm /data/mysql/auto.cnf
sudo systemctl restart mysql

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='172.31.XXX.XXX', MASTER_USER='repl', MASTER_PASSWORD='********', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=76706;
START SLAVE;

-- debug info
SHOW MASTER STATUS;
SHOW RELAYLOG EVENTS
SHOW SLAVE STATUS;

Getting started with sharding in MongoDB
Setting up sharding on a MongoDB collection isn’t as complicated as it sounds. In fact, it usually is an overkill. However, once you decide to take the plunge, here’s a basic primer on how to go about it. Will be adding the caveats at a later point in time.