Logical backup and physical backup

Logical backup

Logical backup is used to back up the structure of a DATABASE (CREAET DATABASE, CREATE TABLE) and data (INSERT). This type of backup is suitable for scenarios where a small amount of data is needed, cross-SQL servers are used, and data needs to be modified. For example, the mysqldump command generates a logical backup tool. The output file using mysqldump contains CREATE TABLE and INSERT statements, which can directly rebuild the TABLE content and TABLE structure.

Using logical backup has the following advantages and disadvantages:

advantage
  • High portability, SQL statements can be directly applicable to other SQL servers;
  • You can add or modify data before data recovery.
  • The data recovery granularity can be at the server, database, or table level.
  • Use text format, high readability;
disadvantage
  • The mysql server needs to be accessed during backup, which affects other clients.
  • Need to convert data to logical format (SQL, CSV);
  • If the command is run on the client, the mysql server also needs to send data to the client.
  • Because the output format is a text file, it occupies a large space.

The physical backup

A physical backup is a copy of the directory and files that store the contents of the database. This type of backup is suitable for large and important databases that need to be quickly restored in the event of a problem.

advantage
  • Full Mysql file and directory backup, only need to copy files without conversion, faster than logical backup;
  • In addition to backing up data, you can also back up configuration files and log files.
  • No need to run Mysql server to complete backup;
  • You can use cp, SCP, and tar commands to back up data.
disadvantage
  • Low portability, recovery data is only applicable to the same or similar machine;
  • In order to maintain the consistency of database files, need to stop backup;
  • The recovery granularity cannot be restored by table or user.

Online backup and offline backup

Online backup requires the mysql server to handle the running status so that the backup tool can obtain data from the mysql server. Offline backup indicates that mysql server processing has stopped. The two backup modes can also be called “hot backup” and “cold backup”.

Key features of online backup

  • Backup does not need to be stopped and has little impact on other clients. Other connections can access the mysql server normally (depending on operation types, such as read operations).
  • The backup needs to be locked to prevent changes to the data during the backup.

Main features of offline backup

  • The server is unavailable during backup.
  • The backup process is simpler without interference from clients.

Logical backup (mysqldump)

Mysqldump is a logical backup command. The advantage of using mysqldump is that it is very convenient and flexible. You can edit the output file directly or import it to other SQL servers, but it cannot be used as a quick solution to backup large amounts of data. But recovering data can also be slow because executing SQL statements involves disk I/O inserts, index creation, and so on. Mysqldump is very simple to use:

shell> mysqldump [options] db_name [tbl_name ...]

shell> mysqldump [options] --databases db_name.

shell> mysqldump [options] --all-databases

Copy the code

When using mysqldump for backup, pay attention to the following: the consistent state of the database. When running mysqldump, ensure that the data will not be changed again. There are two methods to ensure the consistency of data:

  • Make Mysql server read-only
  • Using transactions plus isolation level:REPEATABLE READ

Execute mysqldump with REPEATABLE READ transaction isolation level (using transactions to maintain consistent state of database) :

mysqldump --master-data=2 \

 --flush-logs  \

 --single-transaction  \

 --all-databases > /backup/`date +%F-%H`-mysql-all.sql  

Copy the code

Backup parameters:

  • –master-data: backs up the names and locations of binary log files
  • –flush-logs: Flush mysql server log files before starting backup
  • –single-transaction: Before starting backup, set transaction isolation level to REPEATABLE READ and send a START transaction command.
  • –all-databases: backs up all databases

Physical backup (copy original files)

To ensure the integrity of the copied files, it is best to stop the mysql server to back up the original files.

  1. Stop mysql server

    $ mysqladmin shutdown
  2. Copy the original data files using appropriate tools

    $ tar cf /tmp/dbbackup.tar ./data
  3. After the backup is complete, run the mysql server

    $ mysqld_safe

Use the master/slave backup mode

With mysqldump and tar backups, there are more or less business implications. Using mysqldump backups requires data to be locked, which means that other client operations are limited. Using tar requires the server to be stopped and the database server becomes unavailable. Is there a way to solve both problems? The answer is yes, use a master/slave backup mode.

Add a Slave machine on the basis of a single machine to synchronize data of the Master machine:

The Slave is backed up at the beginning of the backup. In this way, services are not affected even if the Slave is stopped or the data is locked. If the company has conditions or the service is very important, you can choose this method to back up data.






Welcome to follow the wechat public account “Architecture Digest”, the first time to push high-quality technical articles.