Database crashes, using Backup and log for disaster recovery _mysql

In the actual work, we may often encounter database downtime, data loss, below, I will demonstrate a simulated environment

1. The database starts inserting data normally:

[root@client103 ~]# mysql-uroot-pkongzhong
mysql> use test;
mysql> INSERT into a select * from A;
#注: No tables are shown here, and everyone knows by default.
below make a full preparation
[root@client103 ~]# innobackupex--user=root--password=kongzhong- DEFAULTS-FILE=/ETC/MY.CNF--port=3306/tmp/backup/>/tmp/backup/innoback.log 2>&1
# Insert data
MySQL again > INSERT into a select * from A;
# Implement incremental backup
[root@client103 ~]# innobackupex--user=root--password=kongzhong--defaults-file=/etc/my.cnf 3306--incremental--incremental-basedir=/tmp/backup/2014-02-27_13-24-51//tmp/backup/
# Insert data
MySQL again > INSERT into a select * from A;
# At this point, we record the total number of rows now (a restore needs to check the data)
# At this time the database is
down #模拟宕机操作为:
# 1. Copy the binary log from the data directory to the/tmp/backup, and you will need to replay the log (this step is important , copy operation by default everyone will be able to)
# 2. Delete all the files in the data directory, the database crashes (if you close the database, it is not close, remember to kill the process, kill-9 ...)

2. Perform recovery operations after database downtime

In actual work, we may often encounter database downtime, data loss, below, I will demonstrate a simulated environment 1. Database startup Insert Data normally: [root@client103 ~]# Mysql-uroot-pkongzhong mysql> use
Test
mysql> INSERT into a select * from A; #注: No tables are shown here, and everyone knows by default. Below make a full preparation [root@client103 ~]# Innobackupex--user=root--password=kongzhong--defaults-file=/etc/ 
MY.CNF--port=3306/tmp/backup/>/tmp/backup/innoback.log 2>&1 # Insert data again mysql> INSERT into a select * from A; # Implement incremental backups [root@client103 ~]# Innobackupex--user=root--password=kongzhong--defaults-file=/etc/my.cnf- Incremental--incremental-basedir=/tmp/backup/2014-02-27_13-24-51//tmp/backup/# Insert data again mysql> INSERT into a Select
* from A; # At this point, we'll record the total number of rows now (one will restore the data) # at this time the database is down #模拟宕机操作为: # 1. Copy the binary log from the data directory to/tmp/backup, and a replay of the log is required (this step is important, the replication operation defaults to Everyone) # 2.
Delete all the files in the data directory, at this time the database crashes (if the database is closed, is not closed, remember to kill the process, kill-9 ...) 2. Perform recovery operations after database downtime # full Application log [root@client103 ~]# innobackupex--apply-log/tmp/backup/2014-02-27_13-24-51/# Add Application log [ROOT@CLI ent103 ~]# Innobackupex--apply-log/tmp/backup/2014-02-27_13-24-51/--incremental-dir=/tmp/backup/2014-02-27_13-32-44/# Application log is completed using full recovery data [root@client103 ~]# Innobackupex--copy-back/tmp/backup/2014-02-27_13-24-51/# Modify Data directory permissions to Mysql:mysql [root@client103 ~]# chown mysq:mysql/ Var/lib/mysql-r # Start the database [root@client103 ~]#/etc/init.d/mysql Start # Log in to the database, view the number of rows, find that the value is inconsistent with the previous crash, so you need to use log recovery [
root@client103 ~]# mysql-uroot-pkongzhong mysql> use test;
Mysql> Select COUNT (*) from A;
# View the log file name and pos number (automatically logged at backup time) when the last replenishment completes [root@client103 ~]# cat/tmp/backup/2014-02-27_13-32-44/xtrabackup_binlog_info mysql-103-bin.000005 3694 # So, when we use binary to perform recovery, the starting point is 3694 # using log recovery [root@client103 ~]# mysqlbinlog--start-pos=3694/tmp/back
up/mysql-103-bin.000005 |mysql-uroot-pkongzhong # At this time in the login database to see if the data match, please test yourself mysql> use test;
 Mysql> Select COUNT (*) from A;

  above is the entire content of this article, I hope you can enjoy.