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

Why backup: disaster recovery: hardware failure (redundancy), software failure (bug), natural disasters, *, misoperation,... Testing: when testing, in order to simulate user access in the real environment, you usually need to test with real data. The principle of backup and recovery: the strategy is correct: usually you should design the personnel involved in backup and restore to ensure that you can do the right thing. There is no problem with execution: usually do drills to make sure that when something goes wrong, you can do the right thing. Do the right thing when something goes wrong. Necessity of remote disaster recovery: prevent the service from being unavailable due to the unavailability of the same machine, the same network environment, and the same physical data center. Backup items: configuration files, perimeter configurations, periodic scheduled tasks. Backup precautions: can tolerate the maximum amount of data lost: determines how long it takes to use backup means and tools to restore data. If the e-commerce site fails, when the data is restored, hundreds of millions of data may be lost in an hour. If you use binary files to recover, the recovery time may be very long, and the data may be inconsistent with the real data due to the large amount of business and the different order in which a single statement is written into the binary file. Which data sets need to be restored online production data sets, online authentication, configuration and other data backup need to often test the availability of the backup, on the other hand, it can also enhance the efficiency of the recovery operation and be methodical when the data really needs to be restored. Type of data backup

According to the scope of the backed up dataset, it can be divided into full backup and partial backup.

Full backup: a partial backup of an entire dataset: a backup of a portion of an entire dataset, such as a partial table.

Full backup, incremental backup, differential backup

Full backup: backup all data incremental backup: backup the part of the data that has changed since the last full backup or incremental backup: back up only the data that has changed since the last full backup

According to the backup mode, it is divided into physical backup and logical backup.

Physical backup: directly copy data file logical backup: export data from the database and there are one or more files. According to whether the data service is online or not, it can be divided into hot backup, warm backup, cold backup and hot backup. Warm backup when both read and write operations can be performed; cold backup: backup made in a state where both read and write operations cannot be performed; cold backup: backup made in a state where both read and write operations cannot be performed; the most reliable, but the least feasible. It can be realized with master-slave replication. Factors to consider when backing up: do you want to lock the resource, and how long do you want to lock the resource? How long is the backup process? What is the server load during the backup? How long does the recovery process take? Backup tool

Mysqldump

Mysql service comes with backup tools, logical backup tools. Full and partial backup supports hot backup for InnoDB and warm backup for MyISAM storage engine.

Cp/tar

Lvm2: snapshot (request a global lock), then release the lock immediately, achieving almost hot standby effect; physical backup; Note: not only data files can be backed up; transaction logs should be backed up at the same time; premise: data files and transaction logs are required to be located in the same logical volume.

Innobackup/xtrabackup

Provided by Percona, open source tools, support for InnoDB hot backup, physical backup tools; support full backup, partial backup; full backup, incremental backup; full backup, differential backup; mysqlhotcopy: almost cold backup select: said to be equivalent to LOW backup: SELECT cluase INTO OUTFILE 'FILENAME'; restore: CREATE TABLE import: LOAD DATAmysqldump usage

Features: support logical backup, full backup, partial backup

Logical backup: if the data service is to run normally, listen on a certain port, and mysqldump can access mysql through the port. Query through the SELECT statement and export to a file, restore through the INSERT statement to insert all the query results into the database. Accuracy may be lost.

Mysqldump usage:

SYNOPSIS:mysqldump [OPTIONS] database [tables] mysqldump [OPTIONS]-- databases [OPTIONS] DB1 [DB2 DB3...] Backup specified table mysqldump [OPTIONS]-- all-databases [OPTIONS] backs up all tables

OPTIONS

-- all-databases backs up all libraries-- databases db1,db2.... Backup specifies one or more databases: db backs up all target objects in a single database (does not automatically create libraries, but simply backs up tables, code, stored procedures, stored functions, triggers, event schedulers, etc.) db table1,table2... Back up the specified table

MyISAM storage engine: warm backup is supported at most, and all backup tables should be locked during backup (if which table is backed up and which table is locked, the time points of each table are inconsistent during restore)

-x,-- lock-all-tables locks all tables in all libraries, read lock-l,-- lock-tables locks all tables in the specified library

InnoDB storage engine: supports warm backup and hot backup

-- single-transaction creates a transaction and performs backups based on this snapshot. This option must be added to support hot backup at the REPEATABLE-READ isolation level. When mysqldump connects to the database, the view is consistent, which is the state at the moment the transaction was started. In this case, start a transaction and all the data backed up is at this point in time.

Other options:

-R,-- routines backup specifies the stored procedures and functions of the library;-- triggers backup specifies the trigger of the library-- events backup event scheduler-- master-data [= #] records a CHANGE MASTER TO statement in the target file of the backup, recording which binary file and which location at the moment of the backup. When mysqldump exports data, when the value of this parameter is 1, the file from mysqldump will include the statement CHANGE MASTER TO. CHANGE MASTER TO is followed by the records of file and position. This statement will be executed when the data is imported on slave, and salve will copy the binlog from the masterside according to the specified file location. By default, this value is 1. Record an annotated CHANGE MASTER TO statement for our reference only, and do not intend to use it to set the startup of the replication thread from the server to 2. -- after the flush-logs locking table is completed, the log refresh operation will be performed.

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.