MySQL Replication (Beginner)
4.6 (8 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,050 students enrolled
Wishlisted Wishlist

Please confirm that you want to add MySQL Replication (Beginner) to your Wishlist.

Add to Wishlist

MySQL Replication (Beginner)

Learn the basics of setting up MySQL replication
4.6 (8 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
1,050 students enrolled
Last updated 4/2017
English
Current price: $10 Original price: $25 Discount: 60% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 2.5 hours on-demand video
  • 2 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • At the end of this course, students will have a basic knowledge of MySQL replication and will be able to do basic setup with SBR/RBR with and without data.
  • Students will also gain a good understanding of the most important statuses of MySQL replication to help troubleshoot basic issues.
  • Students will have a great understanding of seconds_behind_master
View Curriculum
Requirements
  • New students should be knowledgeable in installing software on their computer. It's also helpful, but not required, to have some basic knowledge of MySQL
Description

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.  

Who is the target audience?
  • The target student is someone who has familiarity of MySQL however doesn't yet know much about replication, but is excited to learn.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
29 Lectures
02:21:25
+
Introduction
4 Lectures 15:49

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.

Preview 03:01

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.

Preview 02:16

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. 

Preview 07:32
+
Statement Based Replication (with no data)
8 Lectures 51:12

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. 

Preview 05:35

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.

Preview 01:58

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.

Preview 05:04

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.

Starting up replication
12:22

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

Binary Logs and Relay Logs
09:45

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.

Different permissions between master and replicas
07:15

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.

Non-Deterministic Functions with SBR
02:59

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.

Accidental Data Drift
06:14
+
Row Based Replication (no data)
7 Lectures 39:11
Sandbox Setup
00:19

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. 

Getting the master and replica ready
02:08

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.

Starting replication
06:18

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.

Binary Logs and Relay Logs
07:48

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.

Read Only
07:13

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.

Non-Deterministic Functions with RBR
03:35

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. 

Accidental Data Drift example breaks with RBR
11:50
+
Row Based Replication (with data)
9 Lectures 33:50
Sandbox Setup
00:19

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.

Adding Data
02:35

In this lecture, we'll be getting the master and replica configured for replication.

Getting the master and replica ready
01:48

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. 

Getting Backup
05:52

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.

Starting Replication
03:37

In this lecture, I'll be showing you how to install the percona toolkit to set us up for a later demo.

Installing percona-toolkit
02:09

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.

Deep Dive into Seconds_behind_master (part 1)
08:01

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.

Deep Dive into Seconds_behind_master (part 2)
06:20

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. 

Deep Dive into Seconds_behind_master (part 3)
03:09
+
Conclusion
1 Lecture 01:23
What's Next
01:23
About the Instructor
Dominique Flannery
4.6 Average rating
8 Reviews
1,050 Students
1 Course
Senior MySQL DBA

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.