MySQL replication and high availability
You set up GTID-based, semi-synchronous and group replication, route traffic with MySQL Router and ProxySQL, and rehearse failover and failback.
Level 401 · 17 lessons · 15 practice labs
Lessons
Replication
- Replication architecture: source, replica and threads
- Setting up replication with GTID
- Building a new replica from a backup
- Measuring and reducing lag
- Fixing replication errors
- Semi-synchronous replication
- Short task: build a two-replica setup
High availability
- Group replication
- InnoDB Cluster and MySQL Shell
- Routing with MySQL Router
- Read/write splitting with ProxySQL
- Short task: shut down the primary and watch what happens
Operations
- A failover plan and failback
- Split brain and how to prevent it
- Multi-site replication
- Rolling upgrades without downtime
- Capstone: an order database that never goes down
Practice labs
- Connect a replica with GTID: Enable GTID and connect a new replica to the source with auto-positioning.
- Build a new replica from a backup: Build a second replica from a physical backup taken without stopping the source and verify it syncs.
- Measure semi-synchronous replication: Enable semi-synchronous replication, measure its write latency cost and watch what happens when a replica drops.
- Measure replica lag: Generate load on the source, track lag on the replica in seconds and write down the cause.
- Recover a replica stopped by a duplicate key: Find the error on a replica stopped by a manually written row and restart it without losing data.
- Detect and repair a drifted replica: Find rows that differ between source and replica with checksums and restore consistency.
- Set up a three-node group: Set up group replication with three servers and read member status from the system table.
- Make the group lose a member: Shut down one node to show the group keeps working, and show why it stops after losing two.
- Recover a cluster that lost its majority: Apply the steps to restart a cluster that lost its majority without losing data.
- Connect the app through a router: Set up a router so the app writes to the primary through a single address.
- Spread reads across replicas: Send SELECT queries to replicas and writes to the source with a proxy rule.
- Fix a report reading stale data: Show that a page reading right after a write gets stale data from a replica and fix the routing.
- Shut down the primary and watch: Stop the primary and watch a new one get chosen and the app reconnect.
- Bring the old primary back: Rejoin the recovered old primary as a replica and verify it has no extra transactions.
- Upgrade without downtime: Upgrade the cluster with no downtime by upgrading replicas in turn and switching over the primary.
About this course
You set up GTID-based, semi-synchronous and group replication, route traffic with MySQL Router and ProxySQL, and rehearse failover and failback.
What you will learn
- Replication
- High availability
- Operations
Prerequisites
You should have practised the topics of the same path up to level 301.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 401; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.