MySQL deployments in an enterprise are usually optimized to provide high-availability and load-balancing. One of the methods from MySQL to optimize the performance of the system is to utilize the native replication. The entire data or sub-set of data, based on the requirements, can be transferred asynchronously to one or more secondary servers using the replication. These secondary servers can be used to serve all read queries on the database to reduce the load on master server. The master server will now be used only for write operations. This is one of the several methods to optimize production server. For more information, refer to MySQL Replication documentation.
Normally, backups can affect the performance on the production server. The impact can be from a minute pause to complete blocking of transactions for that full backup window. For example, if you have MyISAM tables and if anyone has to take a consistent backup of these tables, it is mandatory to lock all the tables for entire duration of the backup. For InnoDB tables, though mysqldump utility does not lock transactions or tables for entire duration of backup, mysqldump creates load on the database server while dumping the databases. The slave replication can be utilized for backup operations to avoid the backup load on production server. In a healthy replication setup, slave will have the data that is eligible for backups similar to production server. In a master-slave replication setup, to maintain consistent backups, execute the stop slave command on the slave and then proceed with the data backup operation. After the data backup phase is complete, execute the start slave command.
Commvault MySQL proxy backup is built to utilize the replication slave for backup operations. This enables you to offload backup activity from the production (master) server to its slave (proxy) server. In a proxy backup configuration, the jobs that are started on master server are offloaded to be performed on a slave server. Backups are registered to the master server. If the replication is found to be broken at any point of time, the backup from such slave is not useful as it will not include the latest data. In such events, backups can be run on the master server itself (if configured to failover) until the proxy is repaired. This ensures a continuous backup activity.
The following are the key features of Commvault MySQL proxy backup:
-
Offloading the backup operations on master server for improved performance.
-
Flexibility to utilize the production server for seamless backup activity in the event of a proxy server failure.