search
HomeDatabaseMysql Tutorial基于Innobackupex的不完全恢复

对于MySQL的不完全恢复,我们可以借助于Innobackupex的多重备份加上binlog来将数据库恢复到任意时刻。这里的不完全恢复(也叫时点恢复)是相对于完全恢复。本文主要演示了基于Innobackupex如何做一个不完全恢复,供大家参考。 a、创建演示环境 robin@localhost[

对于MySQL的不完全恢复,我们可以借助于Innobackupex的多重备份加上binlog来将数据库恢复到任意时刻。这里的不完全恢复(也叫时点恢复)是相对于完全恢复。本文主要演示了基于Innobackupex如何做一个不完全恢复,供大家参考。
a、创建演示环境 robin@localhost[(none)]> show variables like 'version'; --当前MySQL版本 +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.6.12-log | +---------------+------------+ robin@localhost[(none)]> reset master; Query OK, 0 rows affected (0.03 sec) robin@localhost[(none)]> use tempdb; robin@localhost[tempdb]> create table tb(id smallint,val varchar(20)); robin@localhost[tempdb]> insert into tb values(1,'fullbak'); --创建一个全备 SHELL> innobackupex --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock --defaults-file=/etc/my3606.cnf \ > /hotbak/full --no-timestamp b、创建一个增备 --在创建增备前插入一条记录到tb robin@localhost[tempdb]> insert into tb values(2,'Incbak'); SHELL> innobackupex --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock --defaults-file=/etc/my3606.cnf \ > --incremental /hotbak/inc --incremental-basedir=/hotbak/full --no-timestamp --再次新增一条记录 robin@localhost[tempdb]> insert into tb values(3,'pointrecover'); Query OK, 1 row affected (0.01 sec) --记下当前的时间点用于后续的不完全恢复 robin@localhost[tempdb]> system date; Thu Dec 25 11:53:54 CST 2014 --模拟误操作 robin@localhost[tempdb]> truncate table tb; Query OK, 0 rows affected (0.01 sec) c、再次全备 SHELL> innobackupex --user=robin -password=xxx --port=3606 --socket=/tmp/mysql3606.sock --defaults-file=/etc/my3606.cnf \ > /hotbak/full2 --no-timestamp --全备后新增一张表 robin@localhost[tempdb]> create table tb_after_truncate(id int,val varchar(20)); Query OK, 0 rows affected (0.02 sec)3、演示恢复过程
--下面理清一下思路: --当前备份情况: 全备+增备+全备 --我们在增备之后truncate了表tb,然后又创建了一个全备,新建了一个表tb_after_truncate。 --此时我们需要将数据库恢复到truncate(误操作)之前 --解决方案:我们需要利用第一次的全备+增备+binglog来恢复到truncate前,当前第二次全备用不上。 a、先做基于全备的apply,注意,此时使用了--redo-only SHELL> i【本文来自鸿网互联 (http://www.68idc.cn)】nnobackupex --apply-log --redo-only --user=robin -password=xxx --port=3606 \ > --defaults-file=/etc/my3606.cnf /hotbak/full b、基于增备的apply, --此时没有--redo-only,如果有多个增备,仅仅最后一个增备无需指定--redo-only SHELL> innobackupex --apply-log --user=robin -password=xxx --port=3606 --defaults-file=/etc/my3606.cnf \ > /hotbak/full --incremental-dir=/hotbak/inc c、进行copy back SHELL> mysqldown -P3606 --copy back前关闭实例 SHELL> netstat -nltp|grep mysql|grep 3606 SHELL> mv /data/inst3606/data3606 /data/inst3606/data3606bk SHELL> mkdir -p /data/inst3606/data3606 SHELL> innobackupex --user=robin -password=xxx --port=3606 --copy-back /hotbak/full --defaults-file=/etc/my3606.cnf SHELL> chown -R mysql:mysql /data/inst3606/data3606 d、启动恢复后的实例 SHELL> mysqld_safe --defaults-file=/etc/my3606.cnf & SHELL> mysql -uroot -pxxx -P3606 -S /tmp/mysql3606.sock \ > -e "select * from tempdb.tb" Warning: Using a password on the command line interface can be insecure. +------+---------+ | id | val | +------+---------+ | 1 | fullbak | | 2 | Incbak | +------+---------+ --获取增量之后的log position SHELL> cd /hotbak/inc/ SHELL> more xtrabackup_binlog_info inst3606bin.000001 774 --这里使用了stop-datetime去将日志追加到truncate之前 SHELL> mysqlbinlog /data/inst3606/log/bin/inst3606bin.000001 --start-position=774 --stop-datetime="2014-12-25 11:53:54" \ > |mysql -urobin -pxxx -P3606 -S /tmp/mysql3606.sock --验证结果如下,可以看到已经恢复到truncate之前了 SHELL> mysql -uroot -pxxx -P3606 -S /tmp/mysql3606.sock \ > -e "select * from tempdb.tb" Warning: Using a password on the command line interface can be insecure. +------+--------------+ | id | val | +------+--------------+ | 1 | fullbak | | 2 | Incbak | | 3 | pointrecover | +------+--------------+ --如果我们需要继续恢复后面的事务,我们可以找出truncate前后位置,然后跳过这个position SHELL> mysqlbinlog /data/inst3606/log/bin/inst3606bin.000001 --start-datetime="2014-12-25 11:53:54"|grep truncate -A5 truncate table tb /*!*/; # at 1180 #141225 11:55:35 server id 3606 end_log_pos 1260 CRC32 0x12f55fc5 Query thread_id=928 exec_time=0 error_code=0 SET TIMESTAMP=1419479735/*!*/; /*!\C latin1 *//*!*/; -- create table tb_after_truncate(id int,val varchar(20)) /*!*/; # at 1392 #141225 13:06:47 server id 3606 end_log_pos 1415 CRC32 0xf956f311 Stop DELIMITER ; # End of log file --我们找出的position为1260,跳过1260之前的继续追加binlog SHELL> mysqlbinlog /data/inst3606/log/bin/inst3606bin.000001 --start-position=1260 \ > |mysql -urobin -pxxx -P3606 -S /tmp/mysql3606.sock --验证追加后的结果,可以看到表tb_after_truncate存在 [mysql@app ~]$ mysql -uroot -pxxx -P3606 -S /tmp/mysql3606.sock \ > -e "desc tempdb.tb_after_truncate" Warning: Using a password on the command line interface can be insecure. +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | val | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+4、小结
f、也可以跳过故障点,继续追加后面的binlog日志至最新,如本文尾部的演示

 

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools