MySQL Replication Setup

by Mark Nielsen
Copyright Dec 2021


This is going to be a different setup. We are manually going to start MySQL multiple times. We are not going to use start files, but do everything manual --- because I am lazy and don't want to write service files and because Docker doesn't run service unless you set it up right.

This will be an advanced setup. This document assume you already have the versions below installed on your laptop, a vm, or docker instance. An explanation will be added later, but for now these are tested commands. All the commands can be executed in the bash prompt and not the mysql prompt.

This is for Ubuntu 20.04 and MariaDB 10.6

  1. Links
  2. Files to use
  3. Setup MariaDB
  4. Start MySQL
  5. Check Replication

Links


Files to Use

Copy these files and directories or use this tar file and then set the files permissions. 
  # uncomment to run the tar file.
  # tar -xcvf mysql_rep_files.tgz /

  mkdir -p /var/log/mysql
  cd /var/log/mysql
  mkdir -p mysql1/relay mysql2/relay mysql3/relay mysql4/relay mysql5/relay mysql6/relay
  mkdir -p mysql1/binlogs mysql2/binlogs mysql3/binlogs mysql4/binlogs mysql5/binlogs mysql6/binlogs
    
  mkdir -p /data/mysql
  cd /data/mysql
  mkdir -p mysql1/data mysql2/data mysql3/data mysql4/data mysql5/data mysql6/data
  
  export web=http://odendata.com/docs/database/mysql/rep1/data/mysql

  for i in 1 2 3 4 5 6; do
    dest=/data/mysql/mysql$i/my.cnf
    webcnf=$web/mysql$i/my.cnf
    echo " wget -vo $dest $webcnf"
    wget -O $dest $webcnf
  done
  

  mkdir -p /opt/odendata/mysql
  for f in Initialize.sh  Make_cnf.sh  Replication.sh  my_template.cnf ; do
    dest=/opt/odendata/mysql/$f
    web=http://odendata.com/docs/database/mysql/rep1/opt/odendata/mysql/$f
    wget -O $dest $web 
  done
  chown -R mysql.mysql /var/log/mysql
  chown -R mysql.mysql /data/mysql

We are setting up two severs if Master - Master setup. Each master will have a slave with delayed replication. Each master will have a normal slave. Both masters will require the transaction to be committed before there is a return to the application. All writes should go to one master so that the other responds quickly.

Start MySQL

For the first time only: 
  

chown -R mysql.mysql /var/log/mysql
chown -R mysql.mysql /data/mysql

mysql_install_db --user=mysql --datadir=/data/mysql/mysql1/data \
 --defaults-file=/data/mysql/mysql1/my.cnf


mysql_install_db --user=mysql --datadir=/data/mysql/mysql2/data \
		 --defaults-file=/data/mysql/mysql2/my.cnf

mysql_install_db --user=mysql --datadir=/data/mysql/mysql3/data \
		 --defaults-file=/data/mysql/mysql3/my.cnf

mysql_install_db --user=mysql --datadir=/data/mysql/mysql4/data \
		 --defaults-file=/data/mysql/mysql4/my.cnf

mysql_install_db --user=mysql --datadir=/data/mysql/mysql5/data \
		 --defaults-file=/data/mysql/mysql5/my.cnf

mysql_install_db --user=mysql --datadir=/data/mysql/mysql6/data \
		 --defaults-file=/data/mysql/mysql6/my.cnf
After each time you start Docker or the server. Its amazing all the articles about starting MySQL manually is not manually. A service script is not manually 

#rm -f /var/log/mysql/mysql*/*.log

#export web=http://odendata.com/docs/database/mysql/rep1/data/mysql

#for i in 1 2 3 4 5 6; do
# dest=/data/mysql/mysql$i/my.cnf
# webcnf=$web/mysql$i/my.cnf
# echo " wget -vo $dest $webcnf"
# wget -O $dest $webcnf
#done

for i in 1 2 3 4 5 6; do
  echo "starting mysql $i"
  echo "mysqld --defaults-file=/data/mysql/mysql$i/my.cnf &"
  mysqld --defaults-file=/data/mysql/mysql$i/my.cnf &
  sleep 5
done

#cat /var/log/mysql/mysql1/error.log

ps auxww | grep mysqld
#ls -al /var/lib/mysql/

Setup Mariadb

Setup accounts on all servers. 


sql=" CREATE USER 'rep'@'localhost' IDENTIFIED BY 'rep'; FLUSH PRIVILEGES;"

sql2="GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*
TO 'rep'@'localhost'"

# We assume root's password is set in ~/.mysql.my.cnf or its blank.
# If not do it.

for i in 1 2 3 4 5 6; do

    echo "Setting up rep in mysql $i"
    mysql -v -u root -e "$sql" -S /tmp/mysql$i.sock
    mysql -vv -u root -e "$sql2" -S /tmp/mysql$i.sock
    mysql -v -u root -e "show grants for rep@localhost" -S /tmp/mysql$i.sock
done
Setup test database and tables. 


sql="create database if not exists test_od;"

sql2="
use test_od;
drop table if exists hosts;
 create table if not exists hosts (
host varchar(64),
date_insert timestamp,
index ( host, date_insert )
);
"

for i in 1 2 3 4 5 6; do
    echo "setup schema in mysql $i"
    mysql -v -u root -e "$sql" -S /tmp/mysql$i.sock
    mysql -vv -u root -e "$sql2" -S /tmp/mysql$i.sock
done

Setup Replication

Setup replication on each server.
NOTE: MariaDB 10.3.3 has semi-synchronous replication built-in if you want it. 
killall mysqld
sleep 5

echo "removing binlogs"
rm -vf /var/log/mysql/mysql*/binlogs/*
rm -vf /var/log/mysql/mysql*/relay/*

rm -vf /data/mysql/mysql*/data/mysql-bin*
rm -vf /data/mysql/mysql*/data/*relay*

for i in 1 2 3 4 5 6; do
  echo "starting mysql $i"
  echo "mysqld --defaults-file=/data/mysql/mysql$i/my.cnf &"
  mysqld --defaults-file=/data/mysql/mysql$i/my.cnf &
  sleep 5
done

for i in 1 2 3 4 5 6; do
  echo "flushing logs mysql $i"
  mysql -v -u root -e "flush logs" -S /tmp/mysql$i.sock
  mysql -v -u root -e "show master logs" -S /tmp/mysql$i.sock
done

for i in 1 2 3 4 5 6; do
  echo "start slave mysql $i"
  mysql -v -u root -e "stop slave\G" -S /tmp/mysql$i.sock
done

sql_slave1="
change master to MASTER_HOST='127.0.0.1',
  MASTER_USER='rep',
  MASTER_PASSWORD='rep',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=375,
  master_port=3301;
"

sql_slave2="
change master to MASTER_HOST='127.0.0.1',
  MASTER_USER='rep',
  MASTER_PASSWORD='rep',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=375,
  master_port=3302;
"

sql_delay="CHANGE MASTER TO MASTER_DELAY = 10;"
#SHOW VARIABLES LIKE 'rpl_semi_sync%';

# m-m
mysql -v -u root -e "$sql_slave2" -S /tmp/mysql1.sock
mysql -v -u root -e "$sql_slave1" -S /tmp/mysql2.sock

# slaves off first master
mysql -v -u root -e "$sql_slave1" -S /tmp/mysql3.sock
mysql -v -u root -e "$sql_slave1" -S /tmp/mysql5.sock
# slave off 2nd master

mysql -v -u root -e "$sql_slave2" -S /tmp/mysql4.sock
mysql -v -u root -e "$sql_slave2" -S /tmp/mysql6.sock

# 10 second dlay slaves
mysql -v -u root -e "$sql_delay" -S /tmp/mysql5.sock
mysql -v -u root -e "$sql_delay" -S /tmp/mysql6.sock

for i in 1 2 3 4 5 6; do
  echo "show slave mysql $i"
  mysql -v -u root -e "show slave status\G" -S /tmp/mysql$i.sock \
  | egrep -i 'running|master_host|master_port';
done


for i in 1 2 3 4 5 6; do
  echo "start slave mysql $i"
  mysql -v -u root -e "start slave\G" -S /tmp/mysql$i.sock
  sleep 1
done

sleep 10

for i in 1 2 3 4 5 6; do
  echo "show slave mysql $i"
  mysql -v -u root -e "show slave status\G" -S /tmp/mysql$i.sock \
	| egrep -i 'running|master_host|master_port';
done


# mysql -v -u root -e "show slave status\G" -S /tmp/mysql1.sock

Check replication

Check the replication 
# This checks the data is on each server. 
# Also test the delayed reaction.

sql="insert into test_od.hosts values ('1',now())"
mysql -v -u root -e "$sql" -S /tmp/mysql1.sock

sql="select @@server_id, host, date_insert from test_od.hosts where host = '1'             
       order by date_insert desc limit 1"

for i in 1 2 3 4; do
     echo "server $j", `mysql -u root -e "$sql" -S /tmp/mysql$i.sock`
done
echo "The above lines should all be the same."
echo ""

for i in 1 2 3 4 5 6 7 8 9 10 11 12; do

  sleep 1
  for j in 1 5 6; do
      echo "second $i, server $j'", `mysql -u root -e "$sql" -S /tmp/mysql$j.sock`
  done
  echo "" 
done
echo "You should have seen a time change after 10 seconds on server 5 and 6."

Other Stuff

Take backup of delayed Slave with "show master status" of the slave and "show slave status". The backup will be compressed. There will be another article on encryption of backups.

Now do a mysqldump backup. 



mkdir -p /backups/mysql/mysql1

d=`date +%y%m%d_%H%M%S`
f=/backups/mysql/mysql1/mysql1_$d

mysqldump -u root --all-databases -S /tmp/mysql1.sock | gzip  > $f.gz

Now do a Xtrabackup compressed encrypted backup. 

# This is mariabdb, so we have to use mariadb-backup
apt-get install mariadb-backup

# We can use xtrrabackup or innobackupex
# If xtrabackup, you should also backup the mysql database.
mkdir -p /backups/mysql/mysql1

d=`date +%y%m%d_%H%M%S`
mariabackup --defaults-file=/data/mysql/mysql1/my.cnf \
           --datadir=/data/mysql/mysql1/data \
	   --user=root --compress --backup \
	   --slave-info \
	   --target-dir=/backups/mysql/mysql1/mysql1_$d \
	   --socket=/tmp/mysql1.sock \
	   --compress

exclude="--exclude=gtid*,innodb*,transaction*"
S=/data/mysql/mysql1/data/mysql
D=/backups/mysql/mysql1/mysql1_$d/MYSQL_copy

mkdir -p /backups/mysql/mysql1/mysql1_$d/MYSQL_copy
rsync -av $exlude $S $D