Welcome to the MySQL Replication course for beginners. I've created this course to teach students how to setup MySQL replication at a basic level using a hands on approach. I've developed a sandbox to ensure that what you see on my screen should be the same on yours. Being a forever student of technology myself, I've found it frustrating while learning a new subject to have to deal with environmental differences between my system and the instructors. My goal is to eliminate such differences as much as possible.
The course is going to be the first in a three part series that I'm creating on MySQL replication. The lessons in this course are meant to be taken in order as I'll be building upon pieces learned in previous lessons. I've personally found this to be a better approach to learning technology so you don't become overwhelmed with complex topics you may not be ready for yet.
In this video I'll be describing the automated mysql-sandbox that I've created for this course. It's freely available on github.com/domflannery/mysql-sandbox and requires limited software to use. The mysql-sandbox ensures that the base installation and configuration is the same for you and I. I discuss some basic understanding of the sandbox and where to get it. For a better understanding, the mysql-sandbox is well documented in github as well.
In this video I discuss how to troubleshoot and report bugs found in my mysql-sandbox as well as course to ensure a positive and productive learning experience.
I've in included this video to describe the basics of using the vim editor that you'll see me use throughout the course to edit things like the my.cnf config file. I only discuss the basics such as opening a file, going between insert and edit mode and saving/exiting the files. I did this mainly for students that may have MySQL knowledge but have mainly worked with it in Windows instead of Linux/Mac.
This video is to walk you through starting up the mysql-sandbox environment for the first time. It's a simple process requiring only a couple of commands that I'll be showing you in the the lecture. At the end of this lecture, students should be comfortable starting and rebuilding the sandbox for later sections.
In this lecture, I give a brief overview of how asynchronous replication works within MySQL. We'll be exploring this is much greater detail throughout the remainder of the course. At the end of this lecture, students should have a basic understanding of what asynchronous replication is, and throughout the course this understanding will become much more clear.
In this lecture, students will learn the basic configuration settings to get the master and replica ready for replication. We won't be starting replication in this lecture yet, simply getting the configuration in place. Students should have an understanding of the minimal configuration settings required to setup replication in MySQL including unique server-id for each server as well as how to turn on binary logging for the master.
In this lecture, we learn how to start up replication. We'll learn about how to see the binlog position on the master using SHOW MASTER STATUS. How to set up the user on the master with replication slave privileges so that the replica will be able to connect to the master to read the binlogs. We'll learn how to issue the CHANGE MASTER command as well as what it means. We'll then issue the START SLAVE command to start replication up and take a look at SHOW SLAVE STATUS to see that we've set it up correctly. I'll then touch briefly on what the slave io thread and slave sql thread do and why they're important. We'll learn how to tell when the io and sql threads are running or not. We'll also briefly discuss what seconds_behind_master is and that it's not what a lot of people believe. At the end of the course we'll go into this in much greater detail.
We'll be learning about the basics of the binary/relay logs as well as how to view them for statement based replication. We'll be looking a little deeper at the relay logs to find events and get a better understanding of why we started replication at a given position. After this lecture, students should be able to locate, open and view either the binary or relay logs for statement based replication (SBR).
In this lecture, you'll learn how to ignore replicating the mysql.* tables per replica. This will allow you to create different users on the replicas vs the master. This will also allow you to create the same user on the master and replicas, however with different permissions. After this lecture, students should understand how to configure this type of setup as well as understand when and why they may want to or when not to.
In this lecture, I demonstrate how non-deterministic functions can cause data drift between your master and replicas with statement based replication (SBR). Students will learn to look out for these types of functions in queries to help prevent this type of data drift. In a later lecture with row based replication (RBR), I show how RBR is better able to handle these types of functions.
In this lecture, I demonstrate how you can get accidental data drift on statement based replication (SBR) setups by accidentally running a write query on the replica. With this, I'll show how with SBR, it's much harder to identify that this has happened vs row based replication (RBR). In the RBR lecture in a later section, I'll demonstrate how RBR handles this differently.
In this lecture, we'll learn the basic setup for replication with row based replication (RBR). Students will understand what the minimal configuration settings required for this are as well as where to set them.
In this lecture, we'll be starting up replication. This is very similar to the "Starting Replication" in the previous section. We'll also learn how to view the binary/relay logs, however this time for row based replication (RBR) setups. We'll learn that we can actually start replication from an earlier position than what SHOW MASTER STATUS is showing us. After this, students should be able to start up replication with RBR as well as read the binary/relay logs in such a setup.
In this lecture, we taken a closer look the differences between what events look like in SBR vs RBR. After this lecture, students should be able to identify specific events in a RBR setup as well as their position on the binary logs as well as the relay logs.
In this lecture, we'll be discussing the read_only parameter. This will show us how to prevent users with write privileges from actually writing to a replica. We'll learn how to set this dynamically as well as how to then make it persist a reboot.
In this lecture, we'll learn about how row based replication (RBR) handles non-deterministic functions vs what we saw with statement based replication (SBR) earlier. After this lecture, students should understand that RBR is the safer option and know when to choose it for their applications.
In this lecture, we'll look at accidental data drift again, only this time with row based replication (RBR). We'll see how with RBR, when you do run a statement on the replica, that this can break replication to alert you. We'll also understand that this isn't always going to happen.
In this lecture, we'll be adding data to our master to get it setup for a common scenario in the real world where you have a database with existing data, but you don't have replication setup yet.
In this lecture, we'll be getting the master and replica configured for replication.
In this lecture, we'll be getting a backup from our master so we can make sure that our replica has the same data as our master before starting up replication. We'll learn how to safely do this so that data isn't changing on our master while we're taking a backup.
In this lecture, we'll be starting up replication similar to how we've set it up in the past. This time, however, we'll be doing so with a master that already has data.
In this lecture, I'll be showing you how to install the percona toolkit to set us up for a later demo.
In this lecture, we'll begin to look deeper into what seconds_behind_master really means. I'll demonstrate how to cause and view seconds_behind_master. We'll also take a look at the relay log to get a deeper understanding of seconds_behind_master.
In this lecture, we continue to look deeper at seconds_behind_master, only this time, we're going to see how network latency can effect this value. After this lecture, students should have a better understanding of how to identify network issues causing issues with replication.
In this lecture, we continue to look at seconds_behind_master. This time, we use the pt-heartbeat tool from the Percona toolkit to better identify true seconds behind master.
I started my technical career in 2004 working for a few small web shops as a PHP developer. In 2007 at started at a large global corporation as a PHP developer and when we realized that we needed a MySQL DBA, I assumed that role in 2009. In that same year, I took and passed the MySQL DBA certification exam and have been working as a MySQL DBA since that time. For the first couple of years, I didn't have any other DBA's to lean on and had to rapidly solve problems in production that I had never seen. Most of the books, lessons, tutorials that I used to learn MySQL taught what happens when everything goes right. Although certainly useful, it didn't always help when things went wrong and I needed to quickly solve these problems. That's why in my teaching approach, I like to show you what happens when things go wrong, how to recognize them as well as avoid/fix them.