Feeling Cold from MySQL Backups that Take Forever? Hot Backups to the Rescue!

Exist Managed IT Services

Feeling Cold from MySQL Backups that Take Forever? Hot Backups to the Rescue!

Feeling Cold from MySQL Backups that Take Forever? Hot Backups to the Rescue! 800 507 Warren Cruz

Many business entities have entrusted their data to MySQL, and for certain use cases, this is a sound choice. If you need an RDBMS for basic transaction processing that does not involve complex tasks and does not require full SQL standard compliance, then MySQL may be right for you (for enterprise-scale workloads, Postgres is the only choice). In fact, Facebook and YouTube have used MySQL for a while now, just to name a couple.

However, there is a conundrum that commonly afflicts MySQL users when their data suddenly assumes massive proportions (terabyte range): long backups and the database unavailability that goes along with it.

Simply copying the data files to a backup location will result in internal data inconsistency since data may have been added or changed while the copying was taking place. An alternative to this is stopping the database and performing the backup, which renders the database unavailable! Availability is the rage and rendering the database unusable is very much unacceptable. And even if, by a stroke of luck, management allows for some downtime, the slowness of MySQL’s native backup mechanism would eventually cause anybody to reconsider.

managed IT services

Consider this actual scenario from one of our clients, a large retail outfit in Southeast Asia. Their POS transactions are fed into a MySQL database that is involved in a replication cluster. The database has grown to 2 TB in size and their backups take 15 hours to complete–if at all. The backup process encroaches in upon operating hours and they are forced to just kill the backup. The problem escalates when the Slave server needs to be refreshed (often by lost binary logs preventing it from synchronizing with the Master) since resetting replication involves restoring a backup of the Master on the Slave node. What to do??!!

Enter hot backups and Percona’s Xtrabackup!

Percona XtraBackup

A hot backup is a database backup that can be performed while the database is online.

From Percona’s website:

“Percona XtraBackup is a free, online, open source, complete database backups solution for all versions of Percona Server for MySQL and MySQL®. Percona XtraBackup performs online non-blocking, tightly compressed, highly secure full backups on transactional systems so that applications remain fully available during planned maintenance windows.”

Xtrabackup solved the problem for this retail giant, reducing the backup time from 15 hours to 4 hours! Database availability was no longer an issue and re-establishing replication is now a fast and straightforward endeavor.

The following are the basic steps to beating the cold:

  1. Simply install the tool: https://www.percona.com/doc/percona-xtrabackup/LATEST/installation.html.
  2. Create a backup directory if one does not yet exist like so, for example: mkdir /var/lib/mysql-backup.
  3. Run the utility: innobackupex –user=root –password=<password> /var/lib/mysql-backup.
  4. Apply the binary logs. For example: innobackupex –apply-log /var/lib/mysql-backup/2018-10-04_15-05-25, “2018-10-04_15-05-25” being the directory where the utility created the backup.

If you need expert database services, contact us and we will be more than happy to solve your DB woes.

Stay warm!