• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

MySQL Replication Use Cases

February 6, 2016 by Andy Hayes Leave a Comment

In my last post, I summarized when MySQL replication is typically implemented. I now want to expand on that and talk about MySQL replication use cases.

Scaling out using MySQL Replication

Let’s take a typical example, the application is read-heavy versus writes. The server is under heavy read load and you want to reduce that. You have optimized indexes etc all you can but the database is still struggling to keep up with the demand. To alleviate the load, more read requests per second need to be processed.

You now have to make a decision, do you scale vertically with a larger server and hope the problem gets fixed this way or do you scale horizontally and add extra servers?

Scaling vertically with a bigger server may address the problem. More CPU’s, faster disks etc could well remove the bottleneck, however this involves a migration exercise from the old to the new server which can be a lengthy operation.

If the application is supporting a business which is experiencing growth and therefore increases in load, repeating the migrations each time (assuming that the hardware to support it has been invented that is faster than what you already have) is not a viable long term solution.

By scaling horizontally and implementing MySQL Replication, you can add additional servers and load balance read requests across them. Want more read throughput? You can add more replica’s.

I’ve used a read-heavy application as an example here. If you wanted more write throughput, then MySQL replication is not a solution to this problem and some form of sharding/HA cluster solution would be used.

Reducing Impact of Backups Taken By Mysqldump

If you use the out of the box tool for taking backups of your database – mysqldump, you may have noticed that it can cause locking problems as it takes the backup.

One get get around this by adding –single-transaction to the command but if you have a replica of your databases available on another server you may choose to take backups of mysql databases from the slave to take some of the load away from the master server.

After all, in a mysql master slave setup, there could be multiple slaves but only one master. So backing up a slave, rather than the master makes logical sense.

Facilitating Reporting Using MySQL Replication

The business wants information from its data. The data is in the production databases. Running queries against databases for reporting can be heavy and affect performance to the main application due to the need to join many tables and aggregate lots of rows.

So you could schedule your reports to run off peak via some cron or windows task. But wait, the business wants these reports sooner, it doesn’t want to have to wait for this information for many hours. The data analysts need them today!

This is another great reason to setup MySQL replication, the replica can be used for heavy reporting tasks, whether these are reports within the application, data analysts or ETL jobs, the fact that the replica is not used by the production application means these tasks have no impact. Everyone is happy 🙂

Providing Failover/High Availability

It’s worth noting that MySQL replication is not a complete high availability solution due to the master/slave configuration. If the master goes down for example, there is a bit of work needed to promote an existing slave to become the master. For more information on a complete HA solution for MySQL, check out MySQL Cluster

In my mind, when I think high availability, database services are not interrupted at any level. Instead, we could say that MySQL replication provides increased availability or higher availability because of the replica’s that exist which can be used in the event of failover.

For businesses which can suffer some brief interruptions whilst the master is promoted,  MySQL replication increases database availability and fulfills a need. With more investment, availability increases. There is a link here which I am quoting from that summarizes this perfectly;

Each architecture used to achieve highly available database services is differentiated by the levels of uptime it offers. These architectures can be grouped into three main categories:

  • Data Replication.
  • Clustered & Virtualized Systems.
  • Shared-Nothing, Geographically-Replicated Clusters.

In my next post, I am going to look more closely at the replication event types.

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…
  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…

Filed Under: All Articles, MySQL Administration Tagged With: backup and restore, mysql

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©