MySQL Replication

By Andy

Transcript

1. MySQL Replication Andy
2. 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
3. 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
4. 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)
5. 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
6. 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
7. 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
8. Database Replication - Running Database Migrations 1. Create a read replica
9. 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
10. Database Replication - Running Database Migrations 3. Create a new RDS instance with configurations of your choice
11. Database Replication - Running Database Migrations 4. Import the dump
12. Database Replication - Running Database Migrations 5. Setup manual replication
13. Database Replication - Running Database Migrations 6. Let the replication catch up and remove the read replica - Check - Seconds Behind Master = 0 - SQL Delay = 0
14. Database Replication - Running Database Migrations 7. Temporarily stop webserver and workers to allow for the replication to catch up
15. Database Replication - Running Database Migrations 8. Redirect all webserver and worker traffic to the new RDS instance
16. Database Replication - Running Database Migrations 9. Monitor the new DB sufficiently and remove the original DB
17. 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/