[MUSIC].
>> Hello, everyone. This is Sudheesh.
In this episode of beginner's series,
we will discuss on the replication feature with
Azure Database for MySQL Flexible Server.
We will try to cover what replication is,
how replication works in MySQL Server, to read replicas,
which is available with that short database for
MySQL flexible server,
and data in replication with few demos.
What is replication?
In MySQL server,
replication would mean transfer of data from one MySQL server,
which is termed as primary,
to one or more MySQL servers which is generally termed as Replica.
The replication configuration is
based on the binary log mechanism.
The primary source server is usually a dump terminal.
We use the coordinates of the binary log file to read
the data and replicate it to the Replica server.
A binary log file is a file which actually
stores the changes which is there in the database.
Usually, these logs are used for
replication as well as for binding time restore in MySQL servers.
If you look at the replication architecture
as you see in this picture, you will see the clients are
actually writing the data into the database.
A copy of the change has been written into the binary log.
Now, the Replica server
has IO thread which connects to the dump thread of
the primary server and then
reads this data and writes into the relay logs.
These relay logs are read by
the SQL thread and have been written in the Replica.
This is how MySQL replication works natively.
In Azure database for MySQL, you can
create a read Replica which is fully managed.
The replication is asynchronous and you can
go up to 10 Replica servers.
Replicas servers are usually used where
you would like to scale the read workloads.
Some of the use cases are like when you wanted to use a database
for your BI or reporting purpose or analytic purpose,
or you have an IoT database where you write one to read
many times those workloads also the read Replica would help.
Some facts about read replicas.
It's only supported in general purpose and memory optimized SKUs.
It is created with the same compute and
storage configuration of your primary server.
You have an option to change it later.
Billing. It's done separately, as you would be doing for
the primary server based on
bound history and the compute you have.
For monitoring, we have a metrics
called as replication lag in seconds,
which explains how much your Replica is behind the primary server.
In case of a disaster, there is an option where you can stop
the replication and make the Replica server read
write and continue using this server
for your application workloads.
Let's get in a demo and see how we can create a Replica server.
For creating a Replica server,
you are to come to the Azure portal primary server,
navigate to the "Replications" tab and then click on "Add Replica".
Give the name of the server and you can
press "Okay" which will create
the Replica server with the same SKU as your primary server,
post creation of the Replica server,
you will be able to see the Replica server name here,
it will also give you an option to delete
the Replica server and stop the replication.
If you stop the replication,
the server becomes standalone and you can no longer
add it back as a Replica to the current primary server.
If you go to the Replica server,
it's a flexible server on its own.
But the only difference will be that it can only read.
You cannot write into the server.
If you want to change the compute,
you will have an option to change the computer of
the Replica server post the replicas have a creation.
Now let's see what's data in replication means.
This feature helps to bring data from an On-Premise and
another Azure database for MySQL or
from other Cloud providers into Azure.
That's what data in means.
The source server can be on On-Prem,
it can be on a VM, it can be
on another Azure database for MySQL flexible server
or MySQL server running in any of the
other managed PaaS Cloud services.
Now, what are the use cases for data-in replication?
The first use case or the important use case,
I would call it, as is for migration.
When you want to migrate to Azure you usually dump the data
from the source server using MySQLDumper or MySQLDump.
Then when you want to make the changes,
what is happening to your source database,
get replicated into the target database,
you use this data in the application.
This would help you to do a selected cut-over of
your applications from the source to
the target in case of a migration scenario.
The second scenario is the hybrid Cloud scenario,
where you can have the source on the On-Prem and
the target which is on the Azure and then using data replication,
you can sync the data between the source of
the target and two different sets of
applications can use these databases which has the data in sync.
Third is between the multi-Cloud scenario
where you can have source in any of the Cloud which is
available and the destination as Azure database for MySQL,
replicate and sync the data so that they can go ahead and use it.
Now, coming to the steps in data and replication.
For data and replication,
the first thing is that we go ahead and create
a MySQL flexible server and then we turn on
the binary log and make sure
that the server parameters are set so that
the binary logs are not deleted on the source server,
this needs to be done on the source server
wherever source server is located.
Then we put this to read-only more to identify the position,
if you are using tools like MyDumper which actually can
take a consistent snapshot
but the details of the positions being shared with you,
you don't have to make it read only, otherwise,
you'll have to make read only and get
the position of the file from the server and then we dump
the database which has been backed up and whose position we know.
Post that, we configure the server
and ask the Replica set up which is Azure database for
my flexible server to get the data
from the master server from
the position from where we took the backup.
That's why the consistent backup is important.
Or if the tools you're using does not support that,
then you'll have to put it to read only mode,
get the status of the position and then use that to configure,
later make that server read write.
Now, let's get in the demo of data in replication.
For the purpose of the demo,
I'm using two Azure database for flexible server itself.
My primary server or the source would be
one flexible server with the name sndemomysqlpubprimary,
and the destination will be on the server, demomysqlpubrepril.
The first thing you would have to do is to
make sure that the networking in
configure such a way that it is able to
communicate between the source and the target.
The important connection which you would require is a connectivity
from your target server to your source server,
and for that, you can use the option of allowing
public access from Azure database for
MySQL within the Azure to the server.
Doing this will make sure that any services within
Azure will be able to connect to the server.
This configuration you would have to do it on
the primary server from which you are trying to
connect and that will
make sure that the networking bit of it is correct.
I have used MySQL workbench and connected to
the primary instance and the Replica instance here.
On the primary instance, if you see,
you will have to make sure that the login is enabled,
and then you create a user, the name and
a password which would require SSL connection,
which is a safe way of connecting it.
You can even do it without SSL also and give it to
have a replication because if you're using
this user only will be connecting to the primary server.
Then you take a dump of
the database either with a consistent backup,
with tools like MyDumper or MySQLDumper, or as you just said,
this particular database primary server to read only take a dump
of it and get the master position.
If you run this command and show master status,
it will show you the master position and the file name.
Once these two are available,
we would have to use that in the secondary server.
Coming to the Replica server configuration,
if you are using SSL based,
you have to use a certificate indicament.
In the Replica server we just use Azure database for MySQL.
We have a store procedure
called as mysql_az_replication_change_master,
there you give the name of the server,
the user which you created for the Replica permission,
the password, and then you give the order,
the digit its connected, and then you give
the file position and the file name
here, and also the availability of
the server that you have inserted onto the top.
Once you run this command,
it actually configures your server telling that
your master server or the primary server
is the server which is being configured here.
In our case, it's another flexible server,
it's the primary server there.
Post that you can just go ahead and start the replication
using this stored procedure mysql_az_replication_start.
If I go and look at the slave status as of now,
you will find that the slave is waiting for the master.
I'll just create a live database here in
the primary server and see if it is getting replicated since
we have already configured the replication.
You can see it is created here.
Let's come back to the Replica server and then try to refresh
here and you can see that the database server got created here.
I'll quickly draw the database as
well for the purpose of the demo.
I just said drop the database and you will see the database
is gone from here and once this Replica gets that changes,
you would see that it is going from here as well.
This is how the data in replication works.
These are the resources,
which is there in the description which will give you
more details about the read replicas and data-in replication.
Thank you. If there is
anything further you would like to ask questions,
please feel free to email us on this email ID.
[MUSIC]