📗 MySQL Replication

📗 MySQL Replication#

See also

percona replication overwiew
dzone replication overwiew
mariadb 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.webp

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.webp

Asynchronous Replication#

../_images/semi-synchronous-replication.webp

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#

See also

mariadb gtid
severalnines replication

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
Contents