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