Shulou(Shulou.com)06/01 Report--

I believe that through the previous article, "data loss is like a slap on the head, data backup is as heavy as a mountain!" Many people have a deep understanding of the importance of data backup. This article will take MySQL as an example to further understand the principle and preliminary practice of data backup.

Data backup methods vary according to different requirements. MySQL officially provides three backup methods, namely cold backup, hot backup and warm backup. If the backup data set is used as the division standard, it can be divided into full backup and partial backup. If the backup interface is used as the division standard, it can be divided into physical backup, logical backup, full backup, incremental backup and differential backup.

The classification of the latter two backups is easy to understand. Let's focus on cold backup, hot backup and warm backup.

Cold backup: to put it simply, the data must be backed up after being offline. Stop the operation of the MySQL instance before the backup, and users cannot access the database throughout the backup.

Hot backup: to put it simply, the data is not offline, reads and writes can be carried out normally, and the MySQL instance is always running.

Wen Bei: to put it simply, it means that a shared lock is imposed globally, which can only be read, not written. During the backup process, although the MySQL instance is still running, it cannot be updated or modified.

Data, configuration files, code (stored procedures, stored functions, triggers), os-related configuration files, replication-related configurations, and binary logs are all objects that we want to back up. So the question is, how to back up the above? We take file system snapshot (LVM), logical backup tool Mysqldump, Mydumper and physical backup tool Xtrabackup as examples to analyze the principle of MySQL data backup.

Snapshot backup

Snapshot backup is one of the hot backups we mentioned above, which backs up the database through the snapshot feature supported by the file system. To use snapshot backup in MySQL, all you need to do is to put the data files and log files in a logical volume and take a snapshot of the logical volume. Snapshot backups tend to prevent misoperation, and once the operation fails, you can use the snapshot to restore to the previous point in time, but because the snapshot can only be local, if the disk is damaged, the snapshot is damaged and the backup disappears.

Graph source network

Mysqldump

Mysqldump is a backup tool that comes with MySQL, which exports the data in the database to .sql files through SQL statements such as create table, drop table, and insert into. Because Mysqldump relies on the transformation of the database layer and does not care about the underlying storage engine, it is more widely applicable and can be switched between different MySQL versions. Also because of this, the backup files generated by Mysqldump are very large, slow, and have a great impact on database access in the backup process, so it is not suitable for instances with large amount of data and high business pressure.

Mydumper

The principle of Mydumper is similar to that of Mysqldump, but one of its features is that it can use multi-thread parallel backup, which greatly improves the speed of data export. The tool is developed and maintained by developers of MySQL, Facebook and skysql, and is developed by lightweight C language. It is said to be 10 times faster than mysqldump, supporting consistent backups of transactional and non-transactional tables, exporting binlog, multithreaded backups, working as daemons, scheduled backups, and so on.

Graph source network

XtraBackup

XtraBackup is probably the most popular backup method now. It is a backup tool developed by Percona for MySQL database physical hot backup. It supports MySQl (Oracle), Percona Server and MariaDB, and all open source. Because XtraBackup is a physical backup, it completes the backup by copying database files and logs, which is faster. The main toolsets are innobackupex (backing up InnoDB tables) and xtrabackup (backing up non-InnoDB tables), the former is a perl script, and the latter is a binary compiled by CAccord +.

Graph source network

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.