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

This article mainly gives you a brief talk about the knowledge of MySQL recovery data backup. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on MySQL recovery data backup knowledge can bring you some practical help.

Knowledge points of database backup and recovery:

1. Timeline backup type classification:

Full backup: backing up the entire dataset

Incremental backup: the last full backup, or the backup of data that has changed since the last incremental backup (restore hassle, save space)

Differential backup: back up only the data that has changed since the last full backup (easy to restore, large space consumption)

two。 What is physical backup, logical backup:

Physical backup: directly copy data files for backup (may take up more space, backup speed is fast, it is difficult to do hot backup)

Logical backup: a backup made by exporting data "saved as" from a database (converting from binary to text format may lose precision and requires a dedicated protocol client to do so. It has nothing to do with the data storage engine, long backup recovery time, and easy to do hot backup)

3. What should be backed up?

① data ② binary log, innodb transaction log ③ code (stored procedure, stored function, trigger, event scheduler) configuration file of ④ CVM

4. Backup tool:

① MySQLdump # mysql comes with logical backup tools ② cp, tar physical backup tools, cold backup ③ lvm2 snapshots: almost hot backup (request to impose global lock), backup with the help of file system management tools ④ mysqlhotcopy: almost cold backup, only suitable for myisam storage engine

5. Choice of backup scheme:

Option 1: MySQLdump+ replicates binlog (slow time, supports remote backup)

MySQLdump: full backup

Copy event for a specified time range in binlog: incremental backup

Scenario 2: lvm2 Snapshot + replication binlog

Lvm2 Snapshot: use cp or tar for physical backup: full backup

Copy event for a specified time range in binlog: incremental backup

Option 3: XtraBackup

Tools provided by percona to support hot backup (physical backup) of innodb

Support full backup, incremental backup

The MySQLdump backup tool explains:

1. What is MySQLdump?

Mysql or mariadb comes with client commands and logical backup tools. It is the backup calling tool of graphical backup management tools mydumper and phpmyadmin. Based on mysql client protocol, suitable for all storage engines, warm backup: full backup, partial backup. Hot backup for innodb (for a long time) is suitable for backing up smaller databases (below GB).

two。 Support for common storage engines:

InnoDB: hot standby or warm standby

MyISAM: warm standby

3. Backup mechanism:

Connect to the mysql CVM through the mysql protocol. Initiate a full query operation to the mysql CVM, get all the data locally, and save the read data in a file to complete the backup. Library: CREATE DATABASE table: CREATE TABLE data: INSERT INTO

4. Use method Usage:

① mysqldump [OPTIONS] database [tables] # back up a single library, and you can back up only some of the tables (partial backup, you need to create the database manually during restore)

② mysqldump [OPTIONS]-B/--databases [OPTIONS] DB1 [DB2 DB3...] # backup multiple libraries (recommended)

③ mysqldump [OPTIONS]-A/--all-databases [OPTIONS] # back up all libraries

5. Example of a primary backup lab:

① backup: mysqldump-uroot-p-B test > test.sql

② logs in to mysql to delete the test library: drop database test

③ recovery: mysql-uroot-p shudian.sql--single-transaction: create a transaction and commit a BEGIN SQL statement before exporting the data. BEGIN does not block any applications and ensures the consistent state of the database at the time of export. -- master-data=2: record the location of the binary file recorded during the backup. This statement is annotated-- flush-logs: after locking the table, log scrolling operation is performed-- lock-all-tables,-x submit request to lock all tables in all databases to ensure data consistency. This is a global read lock and automatically turns off the-- single-transaction and-- lock-tables options. Mysqldump-uroot-p-- host=localhost-- all-databases-- lock-all-tables--lock-tables,-l lock all tables before you start exporting. Lock the table with READ LOCAL to allow MyISAM table inserts in parallel. Tables that support transactions, such as InnoDB and BDB,--single-transaction, are a better choice because it does not need to lock the table at all. Note that when exporting multiple databases,-- lock-tables locks tables for each database. Therefore, this option does not guarantee the logical consistency of the tables in the exported file between databases. The export status of different database tables can be completely different. Mysqldump-uroot-p-host=localhost-all-databases-lock-tables

two。 Modify host A data

Insert into mybook values (5 flush logs; # refresh to generate a new binlog log)

Modified data:

3. Send backup information

① global backup files

Scp shudian.sql B host IP:/app

② looks at the global backup file to see which node it is backed up to and records:

Grep "MASTER_LOG_POS" shudian.sql | head-1 Murray-CHANGE MASTER TO MASTER_LOG_FILE='test.000002', MASTER_LOG_POS=1545

Binary log after ③ backup node

Mysqlbinlog-- start-position=1545 / app/test.000002-D > incre.sql#ROW mode export mysqlbinlog-- base64-output=decode-rows-v-d DBNAME mysqlbin.000001 > incre.sql#mysqlbinlog-d-D ops test.000002 > ops_incre_bin.sql-- start-position= /-j starting position-d # specify database-D /-- disable-log # when recovery is disabled, binary logs are generated, and the generated sql files need root permission to execute

After misoperation, you need to modify the sql file to remove the entries related to the operation. For example, delete a table by mistake:

DROP TABLE `troombind` / * generated by server * / *! * /; # at 341cm 180824 15:04:11 server id 1 end_log_pos 389 CRC32 0x2f0eec92 Rotate to master-bin.000004 pos: 4SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! /

Send binary logs to host B:

Scp incre.sql B host IP:/app

④ host B data recovery

Set sql_log_bin=0; temporarily shuts down recording binary log mysql

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.