MySQL Replication

Standard Replication Concepts

Replication Workflow
1. The master server writes all database changes to the binary log – or binlog.
2. The slave checks the binlog for these changes and writes them into a relay log.
3. The relay log then writes these changes to the database.

../_images/replication-architecture-diagram.png — MySQL Replication

Types:

Asynchronous	Slaves do not need to be connected permanently to receive updates from the master.
Semi-synchronous	At least one slave acknowledges receipt from master.
Synchronous	Slaves must acknowledge receipt.

../_images/asynchronous-replication.png — Asynchronous Replication

../_images/semi-synchronous-replication.png — Semi-Synchronous Replication

Formats:

Statement-based	Propagation of SQL statements from master to slave.
Row-based	Changes in individual rows.
Mixed	Combination of statement and row-based.

Global Transaction ID

The purpose of Global transaction ID (GTID) is to make slave position independent of internal details of master’s binlog (file name, file offset).

Every transaction has an unique identifier which identifies it in a same way on every server.

GTID is built from two parts - the unique identifier of a server where a transaction was first executed, and a sequence number.

You can use either statement-based or row-based replication with GTIDs.

Setting up Replication with GTID

SQL Master

Install packages

apt install mysql-server mysql-client
systemctl status mysql.service
systemctl is-enabled mysql.service

Configure master replication, /etc/mysql/mariadb.conf.d/50-server.cnf

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
#binlog_do_db           = include_database_name
binlog_ignore_db        = "mysql"

systemctl restart mysql.service

Create slave user

> grant replication slave on *.* to 'slave_user'@'192.168.122.%' identified by "xxxxxxxxxx";
> flush privileges;

Lock and dump database

> flush tables with read lock;

mysql -N -e "show databases like '%';" | grep -v "mysql\|information_schema\|performance_schema" \
| xargs mysqldump --databases > master_dump_$(date +%F|sed 's/-//g').sql

> unlock tables;
> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     1272 |              | mysql            |
+------------------+----------+--------------+------------------+

SQL Slave

Import master dump

mysql < master_dump_20190308.sql

Configure slave replication, /etc/mysql/mariadb.conf.d/50-server.cnf

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
relay-log               = /var/log/mysql/mysql-relay-bin.log

Start slave replication

> CHANGE MASTER TO MASTER_HOST='192.168.122.61',
    -> MASTER_USER='slave_user',
    -> MASTER_PASSWORD='xxxxxxxxxx',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=  1272;
> start slave;
> show slave status\G

Use Global Transaction ID

> show slave status\G
   Using_Gtid: No
  Gtid_IO_Pos:
> stop slave;
> CHANGE MASTER TO MASTER_USE_GTID = slave_pos;
> start slave;
> show slave status\G
   Using_Gtid: Slave_Pos
  Gtid_IO_Pos: 0-1-9