[Backup Mode Selection] For MySQL laboratory DMB database monitoring and disaster recovery systems

Many enterprises, especially medium and large Internet enterprises, are using MySQL Databases in large quantities and use the InnoDB Storage engine in most cases. Using the InnoDB Storage engine has many factors, such as transaction security, automatic recovery, row lock, online backup, and so on. In MySQL database applications and O & M, besides database status and performance monitoring

Many enterprises, especially medium and large Internet enterprises, are using MySQL Databases in large quantities and use the InnoDB Storage engine in most cases. Using the InnoDB Storage engine has many factors, such as transaction security, automatic recovery, row lock, online backup, and so on. In MySQL database applications and O & M, besides database status and performance monitoring

Many enterprises, especially medium and large Internet enterprises, are using MySQL Databases in large quantities and use the InnoDB Storage engine in most cases. Using the InnoDB Storage engine has many factors, such as transaction security, automatic recovery, row lock, online backup, and so on.

In MySQL database applications and O & M, in addition to database status and performance monitoring, database backup has never been well managed by tools, DMB database monitoring and disaster recovery systems were born to solve this conflict.

In the DMB system, there are a total of 7 backup modes in the backup configuration. Next we will introduce them separately. Under what circumstances should we select the backup mode to achieve the most efficient backup, and minimize the impact on the business.

• MYSQLDUMP:
  • Call the mysqldump command of the MySQL System for backup. In the case of InnoDB only, online Hot Standby can be implemented without affecting online services. In the case of MyISAM tables, global locks will be added during the backup process, at this time, the system is read-only. After the backup, the master information is generated. You can use the backup SQL file and master_info. SQL to create the slave service.
• IBBACKUP_ALL:
  • Back up all InnoDB and MyISAM tables. If there is no MyISAM table, it does not affect online services during the backup process. If there is a MyISAM table, after InnoDB is backed up, the MySQL database is read-only during MyISAM backup. The backup will generate master_info. SQL for creating slave.
• IBBACKUP_INNODB:
  • For InnoDB tables only, MyISAM tables are not backed up and are in hot standby mode, which does not cause write congestion. The backup will generate master_info. SQL for creating slave.
• IBBACKUP_NONBLOCK:
  • If InnoDB is used only, the MyISAM table is not backed up, which is an online hot backup and does not cause any blockage to the system. The backup does not generate master_info. SQL. You cannot use this backup to create a Server Load balancer instance. It is only used for backup.
• XTRABACKUP_ALL: Same as IBBACKUP_ALL
• XTRABACKUP_INNODB: Same as IBBACKUP_INNODB
• XTRABACKUP_NONBLOCK: Same as IBBACKUP_NONBLOCK

Note:

1. In backup mode, IBBACKUP starts with ibbackup, and XTRABACKUP starts with xtrabackup.
2. All backups contain the MySQL database and the configuration file my. ini.
3. You can select MYSQLDUMP, IBBACKUP_ALL, and XTRABACKUP_ALL in the backup mode if you are not clear about them.
4. IBBACKUP and XTRABACKUP are both physical backups. The recovery speed is higher than MYSQLDUMP, which can shorten the recovery time.
5. IBBACKUP_NONBLOCK and XTRABACKUP_NONBLOCK do not call the flush operation. Therefore, online services are not affected under any circumstances. In other backup modes, in order to obtain the master info information, some of them will immediately add a global lock and release it. In extreme cases, this will have a certain impact on the database. The specific situation and patches for existing MySQL will be described in the subsequent blog.

Related posts:

1. MySQL enterprise-level database disaster recovery (Backup) system-DMB v2.1 released
2. New Features and improvements of MySQL5.5 replication/synchronization
3. MySQL vs NoSQL efficiency and cost competition
4. MySQL 5.5 Released (non-GA)
5. MySQL5.6 release and new features

? Tan junqing published a study on MySQL performance, MySQL Cluster, and MySQL HA High Availability-MySQL laboratory, 2011.
The source and author information of an article can be reproduced at will.
Link: http://www.mysqlab.net/blog/2011/04/dmb-database-mysql-backup-method/

Tags: DMB, ibbackup, xtrabackup, online hot backup, backup