Desktop
System
- Ansible from scratch
- Ansible
- AWX
- Using Docker
- MySQL Replication
- Nginx
- Percona XtraDB Cluster
- SELinux Samba share
- Sphinx
- Systemd
Bash
Awk
- Getting Started with awk
- Running awk and gawk
- Regular Expressions
- Reading Input Files
- Record number
- Record splitting with standard awk
- Record splitting with gawk
- Fields
- Contents of a field
- How fields are separated
- Each character a separate field
- FS from the command line
- Field-splitting summary
- Record-splitting summary
- Multiple-line records
- Explicit input with getline
- Getline summary
- Input with a timeout
- Printing Output
- Expressions
- Patterns, Actions, and Variables
- Arrays in awk
- Functions
MySQL Replication¶
Standard Replication Concepts¶
Replication Workflow
The master server writes all database changes to the binary log – or binlog.
The slave checks the binlog for these changes and writes them into a relay log.
The relay log then writes these changes to the database.

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

Asynchronous Replication¶

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