Cross region replication using Data-in replication with Azure Database for MySQL – Flexible Server

Cross region replication using Data-in replication with Azure Database for MySQL – Flexible Server

by | Jul 5, 2022 | Technology

This article is contributed. See the original author and article here.

MySQL workloads are often read-heavy and support customers with operations in different geographical locations. To provide for Disaster Recovery (DR) in the rare event of a regional disaster, Azure Database for MySQL – Flexible Server offers Geo-restore. An alternate option for DR or read scaling across regions is to create an Azure Database for MySQL flexible server as the source server and then to replicate its data to a server in another region using Data-in replication. This set up helps improve the Recovery Time Objective (RTO) as compared to geo-restore and the Recovery Point Objective (RPO) will be equal to the replication lag between the primary server and the replica server.


 


Data-in replication, which is based on the binary log (binlog) file position, enables synchronization of data from one Azure Database for MySQL flexible service to another. To learn more about binlog replication, see MySQL binlog replication overview.


 


In this blog post, I’ll use mydumper/myloader and Data-in replication to create cross region replication from one Azure Database for MySQL flexible server to another in a different region, and then I’ll synchronize the data.


 


Prerequisites


To complete this tutorial, I need:



  1. A primary and secondary Azure Database for MySQL flexible server, one in each of two different regions, running either version 5.7 or 8.0 (it is recommended to have the same version running on the two servers. For more information, see Create an Azure Database for MySQL flexible server.


    Note: Currently, this procedure is supported only on flexible servers that are not HA enabled.





  1. An Azure VM running Linux that can connect to both the primary and replica servers in different regions. The VM should have the following client tools installed.




  1. A sample database for testing the replication. Download mysqlsampledatabase.zip, and then run the included script on the primary server to create the sample classicmodels database.

  2. The binlog_expire_logs_seconds parameter on the primary server configured to ensure that binlogs aren’t purged before the replica commits the changes.

  3. The gtid_mode parameter set to same value on both the primary and replica servers. Configure this on the Server parameters page.

  4. Networking configured to ensure that primary server and replica server can communicate with each other.



  • For Public access, on the Networking page, under Firewall rules, ensure that the primary server firewall allows connection from the replica server by verifying that the Allow public access from any Azure service…check box is selected. For more information, in the article Public Network Access for Azure Database for MySQL – Flexible Server, see Firewall rules.

  • For Private access, ensure that the replica server can resolve the FQDN of the primary server and connect over the network. To accomplish this, use VNet peering or VNet-to-VNet VPN gateway connection.



Configure Data-in replication between the primary and replica servers


To configure Data-in replication, I’ll perform the following steps:



  1. On the Azure VM, use the mysql client tool to connect to the primary and replica servers.

  2. On the primary server, verify that log_bin is enabled by using the mysql client tool to run the following command:

    SHOW VARIABLES LIKE 'log_bin';​



 


3. On the source server, create a user with the replication permission by running the appropriate command, based on SSL enforcement.

If you’re using SSL, run the following command:


 


 

CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%' REQUIRE SSL;

 



If you’re not using SSL, run the following command:


 


 

CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword';
GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';

 


 


4. On the Azure VM, use mydumper to back up the primary server database by running the following command:


 


 

mydumper --host=<primary_server>.mysql.database.azure.com --user=<username> --password=<Password> --outputdir=./backup --rows=100 -G -E -R -z --trx-consistency-only --compress --build-empty-files --threads=16 --compress-protocol --ssl  --regex '^(classicmodels.)' -L mydumper-logs.txt​

 


 


  –host: Name of the primary server


  –user: Name of a user having permission to dump the database.


  –password: Password of the user above


  –trx-consistency-only: Required for transactional consistency during backup.

For more information about using mydumper, see mydumper/myloader.


 


5. Restore the database using myloader by running the following command:


 


 

myloader --host=<servername>.mysql.database.azure.com --user=<username> --password=<Password> --directory=./backup --queries-per-transaction=100 --threads=16 --compress-protocol --ssl --verbose=3 -e 2>myloader-logs.txt

 


 


–host: Name of the replica server.


–user: Name of a user. You can use server admin or a user with readwrite permission capable of restoring the schemas and data to the database.


–password: Password of the user above.



  1. Read the metadata file to determine the binary log file name and offset by running the following command:

    cat ./backup/metadata​


    In this command, ./backup refers to the output directory specified in the command in the previous step.

    The results should appear as shown in the following image:



image1_crossregion.png 


 



  1. Depending on the SSL enforcement on the primary server, connect to the replica server using the mysql client tool, and then perform the following the steps.


    If SSL enforcement is enabled, then:

    a. Download the certificate needed to communicate over SSL with your Azure Database for MySQL server from here.
    b. In Notepad, open the file, and then copy and paste the contents into the command below, replacing the text “PLACE PUBLIC KEY CERTIFICATE CONTEXT HERE“.

    SET @cert = ‘-----BEGIN CERTIFICATE-----
PLACE PUBLIC KEY CERTIFICATE CONTEXT HERE 
-----END CERTIFICATE-----'


    c. To configure Data-in replication, run the updated command above along with the following command to set @cert

    CALL mysql.az_replication_change_master(‘<Primary_server>.mysql.database.azure.com’, ‘<username>’, ‘<Password>’, 3306, ‘<File_Name>’, <Position>, @cert);

     


    If SSL enforcement isn’t enabled, then run the following command:

    CALL mysql.az_replication_change_master(‘<Primary_server>.mysql.database.azure.com’, ‘<username>’, ‘<Password>’, 3306, ‘<File_Name>’, <Position>, ‘’);

     




–Primary_server: Name of the primary server


–username: Replica user created in step 4


–Password:  Password of the replica user created in step 4


–File_Name and Position: From the information in step 7


8. On the replica server, to ensure that write traffic is not accepted, set the server parameter read_only to ON.


 


 

call mysql.az_replication_start;

 


 



  1. On the replica server, to ensure that write traffic is not accepted, set the server parameter read_only to ON.


 


Test the replication


On the replica server, to check the replication status, run the following command:


 


 

show slave status G;

 


 


In the results, if the state of Slave_IO_Running and Slave_SQL_Running shows “Yes” and Slave_IO_State is “Waiting for master to send event”, then replication is working well. You can also check Seconds_Behind_Master, which indicates how late the replica is. If the value is something other than 0, then the replica is still processing updates.


For more information on the output of the show slave status command, in the MySQL documentation, see SHOW SLAVE STATUS Statement.


For details on troubleshooting replication, see the following resources:



 


Optional


To confirm that cross region is working properly, you can verify that the changes to the tables in primary have been replicated to the replica.



  1. Identify a table to use for testing, for example the Customers table, and then confirm that it contains the same number of entries on both the primary and replica servers by running the following command on each server:


 

select count(*) from customers;

 


 



  1. Make a note of the count of entries in each table for later comparison.


To confirm that replication is working properly, on the primary server, add some data to the Customer table. Next, run the select count command each of the primary and replica servers to verify that the entry count on the replica server has increased to match the entry count on the primary server.


Note: For more information about how to monitor Data-in replication and create alerts for potential replication failures, see Monitoring and creating alerts for Data-in replication with Azure Database for MySQL-Flexible Server.


Conclusion


We’ve now set up replication between Azure Database for MySQL flexible servers in two different regions. Any changes to primary instance in one region will be replicated to the server in the other region by using the native replication technique. Take advantage of this solution to scale read workloads or to address DR considerations for potential regional disasters.



If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!

Prepare for a New Cryptographic Standard to Protect Against Future Quantum-Based Threats

by | Jul 5, 2022 | Security, Technology

This article is contributed. See the original author and article here.

The National Institute of Standards and Technology (NIST) has announced that a new post-quantum cryptographic standard will replace current public-key cryptography, which is vulnerable to quantum-based attacks. Note: the term “post-quantum cryptography” is often referred to as “quantum-resistant cryptography” and includes, “cryptographic algorithms or methods that are assessed not to be specifically vulnerable to attack by either a CRQC [cryptanalytically relevant quantum computer] or classical computer.” (See the National Security Memorandum on Promoting United States Leadership in Quantum Computing While Mitigating Risks to Vulnerable Cryptographic Systems for more information).

Although NIST will not publish the new post-quantum cryptographic standard for use by commercial products until 2024, CISA and NIST strongly recommend organizations start preparing for the transition now by following the Post-Quantum Cryptography Roadmap, which includes:

  • Inventorying your organization’s systems for applications that use public-key cryptography.
  • Testing the new post-quantum cryptographic standard in a lab environment; however, organizations should wait until the official release to implement the new standard in a production environment.
  • Creating a plan for transitioning your organization’s systems to the new cryptographic standard that includes:
    • Performing an interdependence analysis, which should reveal issues that may impact the order of systems transition;
    • Decommissioning old technology that will become unsupported upon publication of the new standard; and
    • Ensuring validation and testing of products that incorporate the new standard.
  • Creating acquisition policies regarding post-quantum cryptography. This process should include:
    • Setting new service levels for the transition.
    • Surveying vendors to determine possible integration into your organization’s roadmap and to identify needed foundational technologies.
  • Alerting your organization’s IT departments and vendors about the upcoming transition.
  • Educating your organization’s workforce about the upcoming transition and providing any applicable training.

For additional guidance and background, CISA and NIST strongly encourage users and administrators to review:

Introduction to clustering models by using R and Tidymodels – part 4 of 4

Introduction to clustering models by using R and Tidymodels – part 4 of 4

by | Jul 4, 2022 | Technology

This article is contributed. See the original author and article here.

bethanyjep_0-1656920903082.png


In the previous episodes, we have journeyed through airports, real estate, and wine industry, gaining insight on the different industries, and utilizing the data in decision making. Alas, now we are in the final episode of a Four-part series – An introduction to R and Machine learning. Join us for the session at Introduction to clustering models by using R and Tidymodels – part 4 of 4, Tue, Jul 12, 2022, 4:00 PM | Meetup. If you missed previous episodes, watch them on demand below: 



 


Introduction to clustering models by using R and Tidymodels – part 4 of 4


In this session, you will train a clustering model. Clustering is the process of grouping objects with similar objects. This kind of machine learning is considered unsupervised because it doesn’t make use of previously known values to train a model. 


Who is it aimed at? 
This session is aimed at anyone who would like to get started with data science in R 


Why should you attend? 
Get an introduction to clustering models and learn how to train a clustering model in R 


Any pre-requisites? 
Knowledge of basic mathematics 
Some experience programming in R 


 


Speaker Bio’s 
Carlotta Castellucio – Cloud Advocate, Microsoft 
Carlotta Castelluccio is a Cloud Advocate at Microsoft, focused on Data Analytics and Data Science. As a member of the Developer Relationships Academic team, she works on skilling and engaging educational communities to create and grow with Azure Cloud, by contributing to technical learning content and supporting students and educators in their learning journey with Microsoft technologies. Before joining the Cloud Advocacy team, she worked as an Azure and AI (ARTIFICIAL INTELLIGENCE) consultant in Microsoft Industry Solutions team, involved in customer-face engagements focused on Conversational AI solutions. Carlotta earned her master’s degree in Computer Engineering from Politecnico di Torino and her Diplôme d’ingénieur from Télécom ParisTech, by completing an E+/EU Double Degree Program. 


 


Eric Wanjau – Data Scientist/Researcher at the Leeds Institute for Data Analytics (LIDA) 


Eric is an Early Career Researcher who continually seeks to tackle real-world challenges using applied research, data analytics and machine learning; all wrapped in unbridled empathy and enthusiasm. He is currently a Data Scientist/Researcher at the Leeds Institute for Data Analytics (LIDA) in the University of Leeds, working on the British Academy project undertaking urban transport modelling in Hanoi. He has also done research in robotics, computer vision and speech processing in Japan and Kenya, aimed at creating safe working environments and exploring human-robot interaction in board games. Eric holds a BSc. in Electrical and Electronic Engineering (2021) from Dedan Kimathi University of Technology Kenya. He plays the guitar (terribly but passionately). 


Join us for the session.