Slide 1
Slide 1 text
MySQL Replication
Andy
Slide 2
Slide 2 text
Database Replication
- Copying data over from one DB (Master) to one or more other DBs (Slaves)
- Most cases the master and the slave share the same DB type and version.
- Asynchronous by default
Slide 3
Slide 3 text
Database Replication - How?s
- Snapshot Replication
- E.g. Creating Staging DB
- Merging Replication
- E.g. Multiple MySQL -> Redshift Replication
- Transactional Replication
- E.g. MySQL Read Replicas
Slide 4
Slide 4 text
Database Replication - Why?s
- Availability
- Low latency
- Crash Recovery
- AWS RDS Multi-AZ and Failover
- Load Balancing
- Read Replica
- Staging Environment
- Analytics
- Near real time replication to Redshift or other equivalents
- Migration (HSKR, HSJP Database)
Slide 5
Slide 5 text
Database Replication -MySQL
1. Any changes (DML) will be
written to the master’s binlog
2. Slave’s IO thread reads the
master’s binlog and writes
corresponding logs to the
relay log.
3. Slave’s SQL thread replays
the DML to the slave
Slide 6
Slide 6 text
Database Replication - How (In MySQL before 8.0)
- Traditional (Binary log position based)
- Statement-based
- (A) Write less to binary logs (bulk updates and deletes)
- (A) Audit property
- (D) Nondeterministic DMLS
- (D) More row locks
- Row-based
- (A) All changes can be replicated -> Safest
- (A) Less row locks
Slide 7
Slide 7 text
Database Replication - How (In MySQL 8.0 and up)
- New (GTID based)
- GTID : Global Transaction ID
- ex) 3E11FA47-71CA-11E1-9E33-C80AA9429562:23
- (A) No need to refer to log files or positions
- (D) Need to synchronize masters and slaves entirely
Slide 8
Slide 8 text
Database Replication - Running Database Migrations
1. Create a read replica
Slide 9
Slide 9 text
Database Replication - Running Database Migrations
2. Extract a full dump of the source database
- Stop replication and record master log file and log position
- Show slave status;
- Create dump
Slide 10
Slide 10 text
Database Replication - Running Database Migrations
3. Create a new RDS instance with configurations of your choice
Slide 11
Slide 11 text
Database Replication - Running Database Migrations
4. Import the dump
Slide 12
Slide 12 text
Database Replication - Running Database Migrations
5. Setup manual replication
Slide 13
Slide 13 text
Database Replication - Running Database Migrations
6. Let the replication catch up and remove the read replica
- Check
- Seconds Behind Master = 0
- SQL Delay = 0
Slide 14
Slide 14 text
Database Replication - Running Database Migrations
7. Temporarily stop webserver and workers to allow for the replication to catch up
Slide 15
Slide 15 text
Database Replication - Running Database Migrations
8. Redirect all webserver and worker traffic to the new RDS instance
Slide 16
Slide 16 text
Database Replication - Running Database Migrations
9. Monitor the new DB sufficiently and remove the original DB
Slide 17
Slide 17 text
References and Diving Deeper
- https://dev.mysql.com/doc/refman/8.0/en/replication.html
- https://aws.amazon.com/blogs/database/amazon-rds-under-the-hood-multi-az/
- https://tobschall.de/2015/06/18/rds-replication/
-https://code.facebook.com/posts/1542273532669494/lessons-from-deploying-mysql-g
tid-at-scale/
- https://searchsqlserver.techtarget.com/definition/database-replication
- https://www.brianstorti.com/replication/