A few days ago to see the news, a factory user data lost, is said to be the pot intern.

Data security is one of the most important responsibilities of a DBA. Today we will systematically discuss MySQL backup.

_ VOICEover: _ It’s probably not that DBAs and opos are too concerned about this issue.

MySQL backup is classified into two types:

(1) Physical Backup

(2) Logical Backup

What is a physical backup?

Backing up the contents of a database by copying files. Typically, backing up a database:

  • Data file/directory

  • Configuration file/directory

  • Log files/directories

  • …

What is a logical backup?

Back up the logical structure of the database, the typical play is to back up the database as a series of SQL statements:

  • create database;

  • create table;

  • create index;

  • Insert… ;

  • …

_ Voiceover: _ Some SQL contains meta information, and some SQL contains data content.

What are the features of physical backup?

In general, physical backups:

(1) the speed is relatively fast, it is only a simple copy of the file backup;

(2) The size is relatively small, files are often compressed;

(3) Database instances are often required to be offline, and database files are not allowed to change to ensure data consistency;

Even though the Enterprise edition supports so-called online physical backups, it adds large locks and poor database availability during backups.

(4) The portability of backup is often poor, and data recovery must be almost the same hardware architecture;

_ Voiceover: _Physical backup in Windows cannot be directly used to restore MySQL in Linux.

When it comes to online backup consistency, different storage engines have different requirements for lock granularity:

(1) InnoDB engine, one table one file, online physical backup can be one table (file) lock backup;

(2) MyISAM engine, a table corresponds to a group of files, online physical backup must lock a group of files;

(3) MEMORY engine, well, the content does not exist on disk;

_ Voiceover: _ Strongly recommended, don’t make online physical backups.

How to implement physical backup?

Since it is a backup file/directory, common file tools can be done, such as: cp, SCP, tar, rsync, etc.

_ Voiceover: __ There are also MySQL tools that are either enterprise or don’t support all storage engines (for example, mysqlHotCopy for MyISAM only).

What are the features of logical backups?

In general, logical backups:

(1) Access to MySQL services to obtain the structural information and data content of the database;

(2) Slow speed. In addition to converting database information into logical format, it may have to be transmitted back to the client backup program;

(3) More granular backup, full database, single database, full table, single table can be backed up, all storage engines can be backed up (including MEMORY engine);

(4) often cannot back up database logs, database configuration files;

(5) Because the backup is a logical format, portability is very good;

_ Voiceover: _Backup in Windows can also be quickly applied to Linux.

(6) During backup, the database instance must be online;

_ Voiceover: _ Online backup and offline backup, more commonly known as “hot” and “cold”.

How to implement logical backup?

The common gameplay is:

(1) the mysqldump

(2) select… into outfile

_ Voiceover: _ Again, the MEMORY engine can also make logical backups.

What are local and remote backups?

Local backup is performed on the host where the MySQL server resides.

Remote backup is performed on another host.

_ Voiceover: _ Local backup Often has the problem that backup and database server compete for local resources. Remote backup often has high network overhead.

Mysqldump: can be implemented locally or remotely.

The select… Into outfile: Same as above.

Mysqlhotcopy: can only be implemented locally.

Other physical backups: They are basically done locally, because the MySQL server handles the offline state.

Snapshot Backup?

MySQL itself does not support this.

Full backup vs. Incremental Backup?

Full backup refers to backing up all data up to a point in time. There are many methods for full backup. All the schemes mentioned above are full backup schemes.

Incremental backup is a backup performed at a certain point in time using binlog to record data changes.

As a DBA and OP, data security is a top priority and full and incremental backups are a must!

Deleted data, how to “low-cost, high security, and fast recovery” to prevent runaway?

Key points: (1) Full backup + incremental backup + regular drill; (2) 1-hour delay from the library; (3) Double 1-hour delay from the library; This is not the focus of this article and will not be covered in detail. See How fast DB rollback + Restore: The Magic of dbAs. _ VOiceover: _ Perhaps this article is of more interest to you.

After reading this article, most people will probably feel that it is not useful (physical backup, logical backup, online backup, offline backup, hot backup, cold backup, local backup, remote backup, snapshot backup, full backup, incremental backup, people are confused).

After all, more people use MySQL without having to operate and maintain MySQL, but it doesn’t hurt to have a systematic knowledge of MySQL backup.