Topics We are not going to include:Security (Access to backup, permissions to restore data, whether files need to be encrypted) where backups are stored, including how far they should be from source data, and how to move data from source to destination retention policies, audits, legal requirements, and corresponding terms storage solutions and media, compression, and incremental backup Storage format for backup monitoring and reporting storage tier built-in backup functionality, or other specialized devices, such as prefabricated file server restores, means getting data from backup files, loading these files into MySQL, or placing them in the desired path of MySQL. Recovery generally means the rescue of a system or part of an exception after it has occurred, including restoring data from a backup and all necessary actions to make the server recover, such as restarting MySQL, altering configuration, and warming up the server's cache. The storage engine's crash recovery requires consistent data and log files. To make sure that the data file contains only the modifications that have been made by the transaction that you have committed, the restore operation will re-execute the food in the log that has not yet been applied to the data file.
why to back upDisaster recovery: A hardware failure, a casual bug that causes data corruption, or the server and its data are not accessible or unusable for some reason. People change their minds: Delete certain data and want to recover these data audits: data snapshots, data status tests at a certain point in time: taking data to a test environment for test use
Define recovery Requirements
Not only to have a backup system, but also to have a powerful recovery system. When planning your recovery strategy, there are two important requirements that can help you think: Recovery point Objective (PRO) and recovery time objective (RTO). They define how much data can be tolerated and how long it will take to recover the data. When defining RPO and RTO, first try to answer the following types of questions:
- How much data can be tolerated without causing serious consequences? Need a failback, or can you accept all of your work lost since your last daily backup? Is there any legal or regulatory requirement?
- How long does recovery need to be completed? What kind of downtime is acceptable? What impact (for example, partial service unavailability) is acceptable to the app and user? How do you continue to serve when those scenarios occur?
- What do I need to recover? A common requirement is to recover an entire server, a single database, a single table, or just a specific transaction or statement
Copy! = Backup: For example, after the drop table needs to be restored, replication will not recover, need to rely on backup to recover, of course, delayed replication is possible.
Design MySQL backup solution
Recommendations before the details:
- In production practice, physical backups are necessary for large databases: Logical backups are too slow and resource-constrained, and recovery in logical backups takes a long time. Snapshot-based backup, such as Perconna xtrabackup and MySQL Enterprise backup, is the best choice.
- Keep Multiple backup sets
- Periodically extract data from logical backups (or physical backups) for recovery testing
- Save the binary log for recovery based on a point-in-time failure. Expire_logs_days should be set long enough. At least the interval is greater than the full amount of backups.
- The process of monitoring backups and backups without using the Backup tool itself. You need to verify that the backup is healthy.
- Test backup and recovery by walkthrough the entire recovery process. Calculate the resources required for recovery (CPU, disk space, actual time, and network bandwidth, etc.)
- Be careful about security.
Online Backup or offline backupPlanning a backup is that there are a number of performance-related factors to consider:
- Lock time: How long does it take to hold the lock, such as the global flush TABLES with READ lock held during backup?
- Backup time: How long does it take to copy the backup to the destination
- Backup load: How much impact the server performance has on replication backup to destination
- Recovery time: How long does it take to replay the binary log by copying the backup image from the storage location to the MySQL server?
The biggest tradeoff is backup time versus backup load. Increasing the priority of backups is at the expense of reducing server performance.
logical Backup or physical backupA logical backup (also called an "export") and a physical backup of the original file is copied directly. Logical backups have the following advantages:
- Logical backups can be used to view and manipulate common files using commands such as the editor or grep and sed.
- Recovery is very simple. Import directly.
- Can be backed up and restored over the network
- Can be used in a system like Amazon RDS that cannot access the underlying filesystem
- Very flexible, because mysqldump most people like the tools.
- Independent of storage Engine
- Helps avoid data corruption, physical backups may be disk corruption
Disadvantages of logical backups:
- A logical backup must be completed by the database server, so more CPU cycles are used
- Logical backup In some scenarios the database file itself is larger
- There's no guarantee that it's going to be the same data after the two are restored.
- Restoring from a logical backup requires MySQL loading and interpreting statements, converting to a storage format, and rebuilding the index, all of which will be slow
The biggest drawback is recovery, and the time it takes to test recovery is very important. Physical backups have the following benefits:
- Based on a physical backup of a file, you simply need to copy the required files to a different location to complete the backup. No additional work is required to generate the original file
- Recovery is much simpler, depending on the storage engine. For MyISAM copy to destination. InnoDB need to stop the database service take additional steps
- Physical backups of InnoDB and MyISAM are very easy to cross-platform, OS and MySQL versions
- Recovering from a home backup is faster because there is no need to execute any SQL or build indexes
In fact, the most frightening part of a logical backup is an indeterminate restore time. Disadvantages of physical Backup:
- InnoDB's original files are usually much larger than the corresponding logical backups. The InnoDB table space contains a lot of unused space. There's a lot of room to use for storing data unexpectedly (insert buffer, rollback segment, etc.)
- Physical backups are not always available across platforms, operating systems, and MySQL versions. File name case sensitive and floating-point format is a problem that you may encounter. It is possible that a file cannot be moved to another system due to a different floating point format
Backup What
The requirements for recovery determine what needs to be backed up, the simplest of which is the backup data and the table definition below are some points to consider for MySQL backup:
- Non-significant data: data that is easily ignored, such as binary logs and InnoDB transaction logs
- Code: triggers and stored procedures
- Replication configuration: There is a replication relationship. Binary logs, trunk logs, log index files, and info files
- Server Configuration: Configuration file
- Selected operating system file management scripts, etc.
Zhengliang Backup and differential backup: A differential backup is a backup of all the changes since the last full backup, whereas an incremental backup is a backup of all the modifications made since the last backup of any type below are some recommendations:
- Using the incremental backup feature in Percona xtrabackup and MySQL Enterprise backup
- Back up the binary log.
- Do not back up tables that do not change.
- Do not back up rows that have not changed
- Some data doesn't need to be backed up at all
- Backs up all the data and sends it to a destination with a deduplication feature.
The disadvantages of incremental backups include increased recovery complexity, additional risk, and longer recovery times. If it is fully prepared, we recommend that we do everything possible, taking into account the simplicity.
storage engine and consistency
Data consistency: As long as the repeatable read transaction isolation level is used on the server, and there is no DDL, there is always a perfect consistency and a point-in-time snapshot of the data, and no subsequent work is blocked during the backup process. File consistency: If it is myisam, the table may be locked and refreshed. Replication: The biggest benefit of backing up from misery is the ability to avoid interfering with the main library and avoiding additional load on the main library.
managing and backing up binary logsThe server's binary log is one of the most important factors for backup. MySQL 5.6 version of Mysqlbinlog has a very convenient feature that can be connected to the server to mirror binary logs in real time. Binary log format: Use the Mysqlbinlog tool to view the contents of the binary log. The time log and time, the server Id,end_log_pos of the original server, the offset byte value of the next time, the time type, and the thread ID of the execution time on the meta server are important for auditing and executing the connection_id () function. Exec_time, the error code generated on the original server at the time. If you are using row-based logs in MySQL5.1, the event will no longer be SQL. It is a "mirror" of the statement's changes made to the table by a poor readability. Secure cleanup binary log: expire_log_days variable to tell MySQL to clean up the log regularly. This variable is only introduced by MySQL 4.1. It was previously all RM mysql-bin. [0-9]* do not do this in the new version, deleting the log with RM will cause the Mysql-bin.index turntable file to be inconsistent with the file on the disk, and some statements, such as show master logs, may be affected and silently fail. Manually modifying the Mysql-bin.index file will not fix the problem should iyong Riley under the cron command:/usr/bin/mysql-e "PURGE MASTER LOGS before Current_data-interval N Day "Expire_logs_days settings are in effect when the server starts or MySQL switches binary logs, so if the binary logs never grow and switch, the server will not be clear about the old entries. This setting determines which file needs to be purged by producing a log modification time instead of the content.
Backing up data
Logical backup: SQL export and Delimited file SQL export: Mysqldump is not the only tool for generating SQL logical backups. For example, you can also use the Mydumper or phpMyAdmin tool to create. SQL Logical Backup Disadvantages:
- Schema and data are stored together
- A huge SQL statement
- A single huge file
- The cost of logical backups is high
Symbolic split file backup: You can use the SQL command select INTO outfile to create a logical backup of the data in the symbolic split file format. (You can use the mysqldump--tab option everywhere in the symbol delimited file). The advantage is faster backup and restore. You can use the load data infile method to load some of the restrictions in the table for the SELECT into OutFile method:
- Can only be backed up to a file on a machine running MySQL server
- A system user running MySQL must have write access to the file directory because it is the MySQL server that performs the write to the file, not the user who runs the SQL command.
- For security reasons, you cannot overwrite files that already exist, regardless of file permissions
File system snapshots: A very good way to back up online. A file system that supports snapshots can instantly create a consistent view of the content used to back up. File systems and devices that support snapshots include FreeBSD's file system, ZFS file system, gnu/linux Logical Volume Management (LVM), and many San system and file storage solutions, such as NetApp storage. LVM mode should not be used, look at, do not write.
recovering from a backupHow the data is recovered depends on how it was backed up. Some or all of the steps may be required:
- Stop the MySQL server
- Logging server configuration and file permissions
- Move data from backup to MySQL data Catalog
- Change Configuration
- Change file permissions
- Restart the server in restricted access mode and wait for the boot to complete
- Load logical backup Files
- Check and replay binary logs
- Detecting data that has been restored
- Restart the server with full privileges
During the recovery process, it is often important to ensure that MySQL does not accept other accesses outside of the recovery process. Add parameters--skip-networking and--socket=/tmp/mysql_recover.sock to start MySQL recovery physical backup: Check directory permissions, configuration file parameters, observe error log restore logical backup: MySQL < Backup.sql load Symbol split Rune Blade: Load DATA INFILE or use Mysqlimport point-in-time recovery: The common method is to restore the last full backup and then replay the binary log from that point in time. 623 page More advanced recovery technology: Delayed replication for fast replies: Setting up a delayed database use the log server for recovery:
InnoDB Crash Recovery
Applies a transaction to a data file based on the log file, rolling back uncommitted changes from the data file InnoDB the cause of the corruption: hardware-related (such as a power problem or memory corruption that causes a corrupted page to be written). misconfigured hardware is the source of more problems. Common error configurations include opening the write-back cache for RAID cards that do not contain a battery backup unit, or opening the witty cache of the Iale hard drive itself. Serious damage can cause InnoDB or MySQL to crash, instead of being so severely damaged that it may simply be that some transactions are lost because the log files are not actually synced to the disk.
How to recover corrupted InnoDB data
There are three main types of InnoDB damage:
- Secondary index corruption: You can usually use optimize table to repair a corrupted level two index, or you can use SELECT into outfile, delete and rebuild the table, and then LOAD the DATA infile method. This is done by constructing a new table that is affected by the index to repair the corrupted index data.
- Clustered index Corruption: You may only be able to export the table using the Innodb_force_recovery option. I have encountered more than once, is due to accidental power outages, or storage hung off.
- Corrupt system structure: includes the InnoDB transaction log, the undo log (undo log) area of the tablespace, and the data dictionary. This damage may require the export and restore of the entire database, as most of the work within InnoDB may be affected.
If the InnoDB data is corrupted to a point where MySQL does not start at all, you can also use Percona InnoDB Recovery Toolkit to extract data directly from the data file in the tablespace. Get address: Http://www.percona.com/softwarePercona server also has the option to allow the server to run when some tables are corrupted, rather than the default force crash when a single table damage page is detected as MySQL does.
Backup and recovery tools
- MySQL Enterprise Backup: Previously known as InnoDB hot Backup or ibbackup. Backups do not require you to stop MySQL, nor do you need to set a lock or interrupt normal database activity. Support for similar compressed backups, incremental backups, and streaming backup features to other servers. The official Backup tool,
- Percona xtrabackup: Open source and free, supports similar streaming, incremental, compression, and multithreaded (parallel) backup operations. The way to work is to keep track of the tail of the InnoDB log file in a background thread, and then copy the InnoDB data file. This is a lightweight intrusion process that relies on special detection mechanisms to ensure that replicated data is consistent. and Xtraback Manager project: Http://code.google.com/p/xtrabackup-manager
- mylvmbackup:http://lenz.homelinux.org/ mylvmbackup is a Perl script that helps MySQL automatically back up with LVM snapshots. The tool first receives a global read lock, creates a snapshot, and releases the lock. It then compresses the data through tar and removes the snapshot. It names the compressed package by the timestamp of the backup.
- Zmanda Recovery manager:http://www.zmanda.com. Configuration, backup, verification, recovery, reporting, and scheduling are available in both free and commercial versions. A replacement for the
- mydumper:mysqldump. Multithreaded backup and restore: http://www.mydumper.org
- mysqldump: A program that is released with MySQL. Most commonly.
--all-databases back up all databases--databases Sakila > Dump.sql Backup Database single mysqldump Sakila actor > Dump.sql backup sakila.ac Tor table--result-file=dump.sql Specifies the output file--opt enable a set of optimization options, including closing the buffer, exporting more data when the data is unloaded in less SQL statements--ALLOW-KEYWORKDS,--Quote-nam ES is when users export and restore tables, they can use reserved words as the name of the table--complete-insert enable users to move data between tables that are not exactly the same column--TZ-UTC enable users to move data between servers with different time zones--locak-all- Tables use flush TABLE with READ Lock to obtain a globally consistent backup--tab export the file with a SELECT into outfile--skip-extended-insert so that each row of data has its own in The SERT statement. --single-transaction for InnoDB backups, this time use InnoDB's MVCC feature to create a consistent backup at a single point-of-use without having to lock all tables with lock tables--master-data The backup will include the binary log file location of the server at the time of backup, which is helpful for point-in-and-click Recovery and set-up replication.
Summary:Everyone knows that backup is needed, but not everyone realizes that a recoverable backup is needed. We need to identify and document recovery point objectives and recovery time objectives, and use them as a reference when selecting a backup system.
----content from the third edition of High-performance MySQL
High-performance MySQL "15th Backup and recovery" learning record
