"Three high" Mysql - Mysql backup overview

introduction

​ The content is the study notes of the video "High Concurrency, High Performance and High Availability MySQL Practice" of MOOC.com and the notes after personal organization and expansion. The content of this article focuses on the basic principles and common introduction of Mysql backup, most of which are related to theory. Content.

​ Data backup may be used less in daily work and study, but it is an indispensable part of an online project. It is necessary for developers to be familiar with and understand the relevant knowledge of backup. , Learning about backup can help us understand some basic backup operations in operation and maintenance.

​ This section focuses on theory, and focuses on understanding how Mysqldump implements incremental backup and full backup, which lays the foundation for the introduction of Mysql active-standby synchronization in the following articles.

Knowledge points:

• Outfile native mysql tool introduction
• MysqlDump improves the Outfile tool, and introduces the characteristics of MysqlDump
• The details of MysqlDump's implementation of incremental backup and full backup
• An introduction to the Xtrabackup backup tool and the details of implementing incremental and full backups

Introduction to Backup

Why do you need a backup?

1. Most of the modern services and most systems are highly available, data is priceless, and loss will bring unbearable losses.
2. A complete set of backup mechanism can make the data repair cost minimized or even zero loss when the system encounters force majeure.
3. A good habit of regularly backing up your data should be in place for any project, production or not.

Backup form

1. Physical backup: For example, we use the hard disk to copy our important data, which has general flexibility and high security.
2. Cloud server backup: data is transferred to a third-party cloud database for storage. The maintenance cost is average, and the security depends on the quality of the third-party maintainer.
3. Self-built server backup: The overhead is relatively high, but the data security and stability are the highest, and physical backup can also be performed offline, with strong operability.

There are three data states during backup:

• Hot Standby: The backup is running normally. The database is now readable and writable.
• Cold Standby: Standby backup. The database cannot do anything.
• Warm standby : The database is read-only . Database availability is weaker than that of hot standby. During backup, the database can only perform read operations and cannot perform write operations.

Backup file format

The format of the backup file means what it will look like when exported:

• Logical backup: output or SQL statement, which can be read by technicians.
• Physical backup (bare file): backup database underlying files but not readable

Backup content

• Full Backup: Backup complete data
• Incremental backup: backup data difference after full backup
• Log backup: that is, Binlog backup

Common tool

There are two commonly used backup tools:

• Mysqldump : logical backup, hot backup, full volume
• xtrabackup : physical, hot, full + incremental backup

summary

• Basic form of backup: From the perspective of backup form, you can use physical disk backup, you can also rely on the server of a third-party service provider or a self-built server for backup, and from the perspective of backup data status, there can be hot backup, cold backup And warm preparation, the concept of warm preparation needs to be careful here.
• There are two commonly used backup tools: Mysqldump and xtrabackup . Both of these tools need to focus on basic operations, practice redundant theory, and use them more.
• Logical backups are often used during development, but for operation and maintenance personnel, it may be faster to use physical backups. Logical backups are often used in scenarios where online problems occur.

Outfile command backup (understand)

How come?

​ About this command, we only need to understand that it does not involve usage scenarios in daily use. This command is a command that comes with mysql and is also a reserved keyword for mysql. It can be said to be the most primitive logical backup method, which can be used to understand MysqlDump the pre-foundation.

Prerequisites for use

1. To know the absolute path of the website, you can know it through error information, phpinfo interface, 404 interface, etc.
2. To have file read and write permissions, it is recommended to execute chmod -R /xxx/xxx for the relevant folder.
3. The written file name must not exist in the file management, otherwise it will be unsuccessful!

Features

1. Simple export of SQL results is mainly used in scenarios that temporarily require data validation.
2. The export method supported by Mysql native commands has high execution efficiency.
3. The commands are simple and easy to operate, and a consistent view can be exported.

defect

• The exported format is relatively simple, and usually requires secondary processing of the data before it can be used normally.
• Only SQL execution results can be exported, and there is no way to restore the exported data.

From the above introduction, it can be seen that the Outfile command can only be used for temporary export of test data in daily development scenarios, and cannot be used as the main tool for hot backup, but this command is inspiring for Mysqldump.

how to use?

Prerequisite: Before the specific export, we need to know the specific path of Mysql export. Use the following statement to check the current security file export prefix. Note that if the result is NULL, it will not affect the Mysql5.6 version, but the Mysql5.7 version has an impact. of.

In addition, although the file system management of the Mac system used by individuals is generally the same as that of Linux, there are actually many details such as permissions that have also stepped on a wave of pitfalls.

 show variables like '%secure%'
-- secure_file_priv  NULL

Why do you say that using secure_file_priv as NULL has an impact?

answer:

In the version of Mysql 5.7, when Mysql is started, if the configuration of this parameter is used, it will limit the scope of the file that you can use LOAD DATA INFILE to load, which means that if you want to export, it must be in the directory specified by this configuration. Success, here are the changes corresponding to this configuration:

1. When secure\_file\_priv is NULL, it means that mysqld is not allowed to import or export .

2. When secure\_file\_priv is /tmp, it means that mysqld can only perform import and export in the /tmp directory, other directories cannot.

3. When secure\_file\_priv has no value , it means that the import and export of mysqld in any directory is not restricted .

After completing the above preparations, we need to build a basic operating environment, such as creating a new database or table. The sakila database is still used here. We can use the following command to try to export. For example, in the following statement, we will all the data in the payment table export.

 select * from payment into Outfile '/Users/xxx/xxx/a.csv' 

Note: The Sakila database can be downloaded directly in the official Mysql example.

However, the following error will appear during the actual execution. From the error message, we can see that this is because secure_file_priv is NULL The problem:

 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement, Time: 0.004000s

It is emphasized again that the macos system is used for personal learning. It is more troublesome to set up. I will not go into details here. I will mainly talk about the processing ideas:

• Set a custom configuration my.ini file and put it in the directory of /etc (Mysql reads the configuration file with the highest priority), and set this parameter at the end of the file: secure_file_priv=/Users/xxxx/xxx/ :x save (note to use sudo vim my.ini ), the export path is recommended to select the current /User/xxx home directory, which is convenient to open immediately after exporting. (The root path path is not very safe, and the macos system does not allow you to do this)
• Restart Mysql or restart the computer, continue to execute the above command after connecting to Mysql, and find an error: PermissionError: [Errno 13] Permission denied , which is obviously a permission problem of macOs, through the command chmod 777 导出文件夹/* can open permissions to the entire folder (root directory do not do this).
• If a file with the same name is used with the same command, it will report an error , indicating that the export file already exists. Remember to check whether the file with the same name is the same before each execution.

Macos use brew install Mysql and find that there is no my.ini file, I personally found a copy from the Internet that can be used directly and add secure_file_priv=/Users/xxxx/xxx/ at the end of the file provided by the link below. , save everyone's time, of course, it is for a MacOs system like me, other operating systems should be able to directly find the relevant configuration files.

Link: https://pan.baidu.com/s/1bM3cQtaXMl3ZGNgQRzhEMA Extraction code: phkg

Interlude: Maxos startup and shutdown using the homebrew installed version:

Closed: sudo pkill -9 mysql

Boot: cd /usr/local/mysql/support-file/mysql.server start（stop关闭）

After a lot of long-winded above, the following is the final export result. You can see that only spaces are used by default, and the format is messy:

The use of Outfile is also easy to remember. At the same time, the following are some parameters of Outfile, which can be freely configured through these parameters:

 SELECT ... INTO Outfile 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
 
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

We found that the above format is confusing, and we want to export it in the form of a standardized table, so we can add between the data of each row, so that the data after exporting can be standardized.

 select * from payment into Outfile '/Users/xxx/xxx/a.csv' FIELDS terminated by ','

It can be seen from the results that Outfile can only be used as an export operation for some simple scenarios:

So far, we only need to briefly understand this command, and lay the foundation for the following understanding Mysqldump .

Mysqldump command use

The Mysqldump command can be regarded as an extension of the Outfile command. As a very important backup tool, it is often used in development and testing scenarios. Of course, this kind of command operation is not recommended online. Generally, it needs to be operated by the operation and maintenance personnel. operation to export the required data, it is easy to cause problems if the hot backup of the entire library is directly performed.

"Knowledge Point"

1. The pain point of Outfile, or the improvement point of Mysqldump
2. Mysqldump Features
3. Operation of Mysqldump (practical case)

4. How to implement incremental backup of Mysqldump (principle)

   • Binlog faithfully records mysql changes
   • Mysqldump can usually only be backed up in full, so Binlog is used as an incremental backup.
   • The key: Mysqldump backup, switch the new Binlog file , and then copy the binlog file as an incremental backup, pay attention to the difference between full backup and incremental backup files.
   • Restore from scratch, use full restore + Binlog restore.

5. Mysqldump can usually only be backed up in full, using Binlog incremental backup

   • The key: Mysqldump backup, switch the new Binlog file.
6. Restore from scratch: full restore + Binlog restore

Pain Points of Outfile

​ As long as you simply operate the Outfile command, you will find that Outfile has the following obvious shortcomings. Mysqldump actually solves many practical problems of Outfile, and improves it on this basis to make it easier to use.

• Only export data, it is difficult to import data again
• Cannot do logical backup, that is, backup SQL logic
• The export form is single, usually only excel can be exported.

Mysqldump Features

• Mysql official built-in command, built-in implementation can avoid many unnecessary problems.
• Supports remote backup and can generate files in various formats.
• Regardless of the storage engine, backup and recovery can be performed under a variety of storage engines. It supports hot backup for the innodb engine and warm backup (applies table locks) for the MyISAM engine .
• free.

How to learn Mysqldump?

The official development is of course the best to learn from the official documents. The link provides the version of Mysql 8.0. Other versions need to be switched and read according to the version you are currently using. In addition, the command parameters do not need to be memorized and meaningless. Come out and take a look at the official documentation:

It is meaningless to memorize any tool-like thing when it is suitable for use. In the end, you will find that you only need to remember the commonly used methods.

​https://dev.mysql.com/doc/refman/8.0/en/Mysqldump.html

Permissions required for backup

• If you need to back up data, you need at least SELECT permission.
• Backup view requires SHOW VIEW permission.
• Backup trigger requires TRIGGER permission.
• If the parameter --single-transaction is not used, relevant permissions are required to lock the table.
• (Since MySQL 8.0.21) PROCESS permission is required if the --no-tablespaces option is not used.
• If you need to import backup data, you need to include all permissions to execute the statement, such as CREATE、ALTER、DELETE permission

Practice: MysqlDump Backup Case

​ We can try to back up the officially provided examples such as sakila. Here are some simple operation commands:

​ Backup a database

 -- 第一种备份方法
./Mysqldump -uroot -pxxxxxx sakila > /Users/xxx/xxx/xxx/xxxx/backup-file.sql
-- Mysqldump: [Warning] Using a password on the command line interface can be insecure.

​ Backup multiple databases to one sql file

 ./Mysqldump  --databases sakila sakila-db -uroot -xxx > /Users/xxx/xx/xxxx/xxx/backup-file_bk2.sql     

​Backup data from one server to another

 -- 个人是本地单机没有进行虚拟机模拟，实验结果未知
Mysqldump --opt db_name | mysql --host=remote_host -C db_name

​ If you use InnoDB's storage engine Mysql, there is an online backup method:

 -- 参数解释
-- --all-databases 所有数据库
-- --single-transaction RR级别的备份，也就是确保一致性的视图(Innodb存储引擎)
-- --master-data 将二进制日志文件的名称和位置写到输出端(留意一下，为下文的增量备份铺垫)
-- 如果不是InnoDB,需要使用下面的参数：
-- 1. --lock-all-tables 使用FTWRL锁锁住所有表（MyISAM）
-- 2. --lock-tables 使用READ LOCAL锁住当前库的表（MyISAM）

Mysqldump -uroot -pxx --all-databases --master-data --single-transaction > /Users/xxx/xxx/all_databases.sql

​ Restore the database

 -- 
./Mysqldump -uroot -pxxxxxx sakila < /Users/xxx/xxx/xxx/xxxx/backup-file.sql

-- 第二种还原备份方法
-- 1. 使用具备相关权限的用户名和密码登陆连接到mysql服务器 mysql -uroot -proot 
-- 2. source /xxx路径/xx.sql文件 source xxx.sql

-- 第三种方式
mysql -e "source /path-to-backup/backup-file.sql" db_name

​ Regarding other commands, it will not be expanded here. Here are some commonly used ones that are basically enough for daily development. If you need more writing methods, you can refer to the official documents above.

Incremental backup implementation principle of Mysqldump

​ The above are all full backup methods. Although we can copy the consistent view through --single-transaction when copying, although the data records at the moment of copying are full and complete, the database is still If there is incremental data that is still being executed, how should this part of the data be backed up?

​ To use Mysqldump for incremental backup, you first need to understand the details of incremental backup, so here is the turn of Binlog log. Binlog backup includes the following points:

1. Binlog faithfully records MySQL changes, full incremental backup and restore processes.
2. Mysqldump can usually only be backed up in full, so Binlog is used as an incremental backup.
3. The key: Mysqldump backup, switch the new Binlog file , and then copy the binlog file as an incremental backup, pay attention to the difference between full backup and incremental backup files.
4. Restore from scratch, use full restore + Binlog restore.

Why can't both incremental and full backups be done at the same time:

We can regard the process of Mysql logging as writing on paper. At this time, Mysql records the content in the latest Binlog log. If we back up the content that is being written and the previous log content, it is likely to cause the backup to appear. Half of the data is written , as if we were suddenly drawn into the book when we were writing, which is likely to cause data corruption.

​ Binlog faithfully records MySQL changes, full incremental backup and restore processes.

​ The key point of implementing incremental backup is how to make an entry point for Binlog logs. The biggest problem with incremental backup of Mysqldump is that we cannot know the dividing point between the current full backup and incremental data . Binlog log records the changes of Mysql, such as CRUD data record change records and data structure adjustment, etc., and maintains transaction consistency with InnoDB storage engine redo log double write.

​ According to the above content, we know that Mysqldump can only back up in full, and we need to use Binlog logs to complete incremental backups .

The idea of ​​incremental backup is to stop the Binlog log currently being read and write during backup, and copy the file, but it should be noted that the Binlog file is copied at this time, which is different from the daily written logical SQL. Same, remember.

​Key points: Mysqldump backup, Mysql server stops the current Binlog writing and switches the new Binlog file

​ Mysqldump provides operations similar to those mentioned above. The following is the operation process of Mysqldump full backup + incremental backup:

 -- --all-databases 所有数据库
-- --single-transaction RR级别的备份，也就是确保一致性的视图(Innodb存储引擎)
-- --master-data=[=Value]（8.0.26改为--source-data命令） 将二进制日志文件的名称和位置写到输出端(留意一下，为下文的增量备份铺垫)
-- --flush-logs 在备份之前刷新服务器的日志 
Mysqldump -uroot -pxx --all-databases --master-data=2 --flush-logs --single-transaction > /Users/xxx/xxx/all_databases.sql

​ After executing the above command, a full backup will be performed first, and Binlog will be switched to the next log file to start reading and writing again. At this time, you can stop writing and backup the binlog log file for subsequent additions. Backup and restore, in short: Mysql backup and switch Binlog at the same time, and copy the currently written part of the Binlog log.

Mysql actually has another backup method, which is Binlog manual incremental backup. The implementation method is to directly use the command to flush the cached log to the disk and switch to the next Binlog. Its command format is as follows:

 mysqladmin -uroot -p123456 flush-logs

​ It should be noted that the mysqladmin tool is used here. After executing the command, we can manually back up all Binlogs.

​ Restore method: full restore + Binlog restore . The restore operation corresponds to the incremental full backup method. Because it is Mysqldump full + Binlog incremental backup, it is also necessary to perform full restore first and then incremental restore.

​ Restore full backup: The easiest way to restore is to execute source xxx.sql after connecting to the server, and the case of Binlog incremental restore operation is as follows:

 mysqlBinlog Mysql-bin.00002 ... | mysql -uroot -p123456

​

summary

1. Mysqldump + Binlog can effectively perform full + incremental backup.
2. Mysqldump is actually an extension and upgrade of the Outfile tool.
3. Binlog backup, Binlog restore, Mysqldump backup can see the collocation of different components.
4. In theory, Binlog can be restored to any time.
5. Mysqldump has many parameters, and it takes more practice to be familiar with and master it.
6. Need to pay attention to distinguish mysqladmin, mysqlBinlog, mysqldump

XtraBackup physical backup

​ Although XtrqBackup is not an officially developed tool, it is used much more frequently than mysqldump. Physical backups are more reliable than mysql logical backups, and have less impact on the system.

There are usually the following reasons why physical backups are needed:

1. Logical backups are very slow for large data backups.
2. The export speed is fast and no secondary conversion is required.
3. Less pressure on the database.
4. Incremental backups are easier.

Is it possible to directly copy the raw file?

Can we directly file the CV database? It is feasible in theory, but there will be many problems in practical operation. Taking the data of Innodb's storage engine as an example, it not only involves Binlog files, idb files (database raw data) and frm files, but also includes unique redo log and undo Log these files, etc. At this time, you will find that if you want to copy these files, you can only use cold backup , but only cold backup is not enough, because it also involves the compatibility of the operating system and the database version, and there are obvious cross-platform problems.

From the conclusion, it is theoretically feasible to directly copy raw files, but in practice the data backed up may be completely unavailable or even incompatible.

How to achieve physical + full + hot backup?

The implementation idea is as follows: the core idea is to monitor the redo log file changes, and back up the Idb file and the ---83cf8ffbf4f60965c25f2e05241bcc6d redo log file that has been changed during the backup process.

1. Start the listening thread and collect the redo log
2. Back up the idb file and record the newly generated redo log during the listening process
3. Backup idb completed, stop collecting redo log logs
4. Add FTWRL lock copy metadata frm

What is FTWRL lock?

FLUSH TABLES WITH READ LOCK abbreviation (FTWRL), this command is mainly used for backup tools to obtain consistent backups (the data matches the Binlog site). It should be noted that the granularity of this lock is very large, basically locking the level of the entire library. If the master library is backed up, the entire master library will be "stuck", and the slave library will cause threads to wait.

Required permissions: FLUSH_TABLES and RELOAD permissions.

Since the main content here is backup, if you want to know more about FTWRL lock implementation details and usage tutorials, you can refer to the following blog:

• Detailed explanation of FLUSH TABLE WITH READ LOCK - Tian Shi Dream - Blog Park (cnblogs.com) ")
• MySQL global lock and table lock - keme - Blog Park (cnblogs.com) ")

Note that in the fourth step, adding a global lock to the entire library will have a period of time when the database is in a warm standby state (read and write cannot be performed).

There is still a problem here, how to know which data is incremental data? The idea of ​​Xtrabackup is that there is an LSN number for each data page in Mysql, and this LSN number can be used to determine which page has changed during backup. After a full backup is performed, the LSN number of the changed data is recorded, and the next backup You can directly find a larger value than the last LSN number for backup.

LSN (log sequence number): The log sequence number is an integer number that is always increasing and occupies 8 bytes after MySQL 5.6.3. It represents the total amount of bytes written to the log by the transaction. LSN is mainly used to recover data when a crash occurs! Every data page , redo log , checkpoint has an LSN.

Description of Xtrabackup

Before introducing Xtrabackup, you need to know about Mysql ibbackup , which was officially developed by Innodb and later renamed Mysql Enterprise Backup , because this software is not many users of paid software, so it appeared later A full replacement Xtrabackup and is widely used.

Xtrabackup is a free database backup software open sourced by percona. Unlike Mysqldump, which is a software developed by a third-party company, the Mysqldump command mentioned above is a logical backup. It is very slow and greatly affects the read and write performance of the database, and the database needs to be locked at the "RR level" or table lock (MyISAM) when exporting. Therefore, it is recommended to use physical backup for large data volumes.

After Xtrabackup is installed, there are 4 executable files, of which 2 are more important backup tools are innobackupex and xtrabackup . The following is a general introduction to the other tools of xtrabackup:

1) xtrabackup is specially used to back up InnoDB tables and has no interaction with mysql server;

2) innobackupex is a Perl script that encapsulates xtrabackup. It supports backing up innodb and myisam at the same time, but a global read lock needs to be added when backing up myisam.

3) xbcrypt encryption and decryption backup tool

4) xbstream streaming package transmission tool, similar to tar

Features of XtraBackup

• The backup speed is fast, hardly affecting the normal business processing of the server
• Compressed storage, saves disk capacity, and can be stored to another server at the same time
• Restoration is fast and the load on the server is small.

XtraBackup installation process

Xtrabackup does not have windows and mac versions, only linux versions, so if you need to do experiments, you can only use linux systems, so here is a brief record of how to install:

Download address: Percona Software downloads for databases

Note that it contains a lot of software. Here you can find the interface shown in the screenshot above and download it according to your own Mysql version:

• 8.0: Corresponds to Mysql8.0 or later.
• 2.4: Corresponding to Mysql5.0 - Mysql5.7 version.

The following is the general installation operation process of xtrabackup:

 wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

[root@centos ~]# ll

total 703528

-rw-r--r-- 1 root root 654007697 Sep 27 09:18 mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

-rw-r--r-- 1 root root  65689600 Nov 30 00:11 Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

[root@centos ~]# tar xf Percona-XtraBackup-2.4.9-ra467167cdd4-el6-x86_64-bundle.tar

[root@centos ~]# yum install percona-xtrabackup-24-2.4.9-1.el6.x86\_64.rpm -y

[root@centos ~]# which xtrabackup 

/usr/bin/xtrabackup

[root@centos ~]# innobackupex -v

innobackupex version 2.4.9 Linux (x86\_64) (revision id: a467167cdd4)

#已经安装完成

XtraBackup full backup and recovery

After the Xtrabackup installation is complete, we can use the following command to perform backup operations:

 [root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password="123456" --backup /root

After the execution is completed, a date file directory will be added in the corresponding directory, and then we need to synchronize the log log:

 #使用此参数使用相关数据性文件保持一致性状态
[root@centos ~]#innobackupex --apply-log /root/(日期)/

Finally, we restore the backup file with the following command:

 [root@centos ~]# innobackupex --defaults-file=/etc/my.cnf --copy-back /root/(日期)/

Xtrabackup Incremental Backup and Recovery

It should be noted that incremental backup can only be applied to InooDB or XtraDB tables, the following commands are used to create incremental backup data.

 [root@Vcentos ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/root/(日期)

#--incremental /backup/   指定增量备份文件备份的目录

#--incremental-basedir    指定上一次全备或增量备份的目录

Recovery command for incremental backup:

 [root@centos ~]# innobackupex --apply-log --redo-only /root/(日期)/

[root@centos ~]# innobackupex --apply-log --redo-only /root/(日期)/ --incremental-dir=/backup/(日期)/

If you need to restore all the data, you can use the following command:

 [root@centos ~]#innobackupex --defaults-file=/etc/my.cnf --copy-back /root/(日期)/

To merge incremental backups into full backups, use the following command:

 innobackupex --apply-log bakdir/xxx-xx-xx/ --incremental-dir=basedir/YYYY-YY-YY/

summary

• Physical backup is an efficient backup method.
• XtraBackup implements full hot backup + incremental backup by backing up idb + monitoring the redo log for changes during backup.
• XtraBackup is a commonly used Mysql physical backup tool.
• The biggest disadvantage of physical backup is that the files after backup cannot be read directly.

Innovation from Mysql Backup

From the improvement process of Mysqldump for backup, we can think in the following ways:

1. Copy disk directly: It is more direct than copying data files, directly copying the physical disk device image backup.
2. Multi-threaded backup: Speed ​​up backup through multi-threading.
3. Backup tool management: We can find that traditional backups are all small black boxes, and the software that manages the backup tool itself is required.

From this, the following special backup mode extensions are extended:

• Mylvmbackup: LVM backup disk. Backup disk is a backup method of physical warm backup . The backup disk itself is a good idea extension, but the backup disk also has a serious problem, which is the problem of compatibility, so this backup tool uses LVM logical volumes Do disk management.
• Mydumper: Multithreaded backup. This tool is even more frequently used than Mysqldump. Mydumper mainly has the following features

​ 1. A tool similar to Mysqldump, 2. Implemented multi-threaded art of war backup and restoration, 3. Faster.

• Zmanda Recovery Manager (ZRM): Backup tool management. Provides a visual way to manage backup files, similar to navicat in database management tools. This tool is characterized by integrating Binlog and multiple backup tools.

How to develop good data management habits

​ In the end, no matter how much backup software is used, the best situation is that we will never use the backed up data. In addition to backup, we have other ways to prevent data loss, such as following the following specifications:

• Privilege isolation

  • The business only has DML authority, so try to use fake delete for deletion
  • Developers only have read-only accounts, although in many cases slightly larger companies have explicit permission management.
  • DBAs only use read-only accounts on a daily basis, and switch accounts after special operations
  • Never use root to connect to the client directly, disable root connection to mysql

• SQL Audit

  • Audit SQL statements before DBA environment goes live
  • Development and modification of data requires DBA execution
  • Inception Automated Audit Tool

• Pseudo delete table

  • Change the name before deleting the table to observe the business impact

• The delete process uses a script to give a special tag table name to delete instead of doing it manually

  • Tables that need to be backed up for local development can be marked with join _bak .

• complete process

  • Backup is required before going live

Summarize

​ This section starts with the ancient command Outfile, introduces the predecessor of the mysqldump command and the improvement and optimization of the outfile command, describes how to implement incremental and full backup through mysqldump, and introduces the internal details. However, logical backup is usually only suitable for the case where the amount of data is not large and the system operation accepts a certain delay in response. Once the amount of data is too large and fast response is required, if you want hot backup without affecting the system, Xtrabackup is more recommended. This tool can be said to be a great killer for O&M backup Mysql DB. It is very powerful and very easy to use. Here is a brief introduction to the details of the implementation. I will not do too much demonstration for the detailed exploration of XtraBack. It is recommended to refer to official materials. Familiarize yourself with the use of tools.

write at the end

​ This article also focuses on theory, and the next article focuses on how to build the "three highs" structure at the core of the entire course.