Detailed description of MySQL backup and recovery

The following articles mainly introduce the actual operations and specific applications of MySQL backup and recovery. We all know that the application rate of MySQL backup and recovery is still very high in actual operations, the following article describes the actual MySQL backup and recovery operations.

Backup/recovery policies:

1. Regular MySQL backup and recovery time that the system can afford should be taken into consideration.

2. Make sure that MySQL opens log-bin. With binarylog, MySQL can perform full recovery, time point-based recovery, or location-based recovery when necessary.

3. always perform Backup Recovery tests to ensure that the backup is valid and can be recovered.

Cold backup

Backup:

1. Stop the MySQL service and back up MySQL data files at the operating system level.

2. Restart the MySQL service and back up the binlog generated after restart.

Recovery:

1. Stop the MySQL service and restore MySQL data files at the operating system level.

2. Restart the MySQL service and use MySQLbinlog to restore the binlog since the MySQL backup.

Logical backup

Backup:

1. When the system is idle, for example, at night, use MySQLdump-F (flush-logs) to back up data.

Library.

 
 

  
  
MySQLdump –u root –p*** pointcard –F > pointcard.sql 
 
 

2. Back up the binlog generated after MySQLdump starts.

Recovery:

1. Stop the application and run MySQL to import the backup file.

 
 

  
  
MySQL –u root –p*** pointcard < pointcard.sql 
 
 

2. Use MySQLbinlog to restore the binlog since the MySQLdump backup.

MySQLbinlog $ HOME/data/MySQL-bin.123456 | MySQL-u root-p ***

Backup of a single table:

Backup:

1. Method 1:

 
 

  
  
MySQL > select * into outfile ‘/tmp/order_tab’ fields-terminated-by=’,’ from order_tab; 
 
 

2. Method 2:

 
 

  
  
MySQLdump –u root –p*** –T /tmp pointcard order_tab --fields-terminated-by=’,’; 
 
 

Recovery:

1. Method 1:

 
 

  
  
MySQL > load data [local] infile ‘/tmp/order_tab’ into table order_tab fields-terminated-by=’,’; 
 
 

2. Method 2:

 
 

  
  
MySQLimport –u root –p*** [--local] pointcatd order_tab.txt --fields-terminated-by=’,’; 
 
 

Note: If the import and export operations are cross-platform windows and linux, you must set the line-

Terminated-by, set to line-terminated-by = '\ r \ n' on windows, and set

 
 

  
  
line-terminated-by=’\n’. 
 
 

Use the MySQL backup tool ibbackup

Ibbackup is a hot backup tool for innodb company www.innodb.com. It is dedicated to physical hot backup for the innodb Storage engine. This tool is free of charge and cannot be used for free. Now innodb has been acquired by oracle

Usage:

Edit the configuration file my. cnf for startup and the configuration file my2.cnf for backup

An example of my. cnf is as follows:

Code

 
 

  
  
[MySQLd]  
  
  
datadir = /home/heikki/data   
  
  
innodb_data_home_dir = /home/heikki/data  
  
  
innodb_data_file_path = ibdata1:10M:autoextend  
  
  
innodb_log_group_home_dir = /home/heikki/data  
  
  
set-variable = innodb_log_files_in_group=2  
  
  
set-variable = innodb_log_file_size=20M  
 
 

If you want to back up data to/home/heikki/backup, the example of my2.cnf is as follows:

Code

 
 

  
  
[MySQLd]datadir = /home/heikki/backupinnodb_data_home_dir = 
/home/heikki/backupinnodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = /home/heikki/backupset-variable = innodb_log_files_in_group=2  
  
  
set-variable = innodb_log_file_size=20M  
 
 

Start to back up ibbackup my. cnf my2.cnf if you need to recover, redo the log ibbackup -- apply-log my2.cnf and restart the database service./bin/MySQLd_saft -- defaults-file = my2.cnf &

Time Point recovery:

1. If a misoperation occurs at ten o'clock A.M., you can use the following statement to recover data to the fault using backup and binglog.

Before:

 
 

  
  
MySQLbinlog --stop-date="2005-04-20 9:59:59" 
  
  
/var/log/MySQL/bin.123456 | MySQL -u root –pmypwd 
 
 

2. Skip the time point when the fault occurs. Continue to execute the subsequent binlog to complete the recovery.

 
 

  
  
MySQLbinlog --start-date="2005-04-20 10:01:00" 
  
  
/var/log/MySQL/bin.123456| MySQL -u root -pmypwd \ 
 
 

Location recovery:

The procedure is as follows:

 
 

  
  
MySQLbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20  
  
  
10:05:00" /var/log/MySQL/bin.123456 > /tmp/MySQL_restore.sql 
 
 

This command creates a small text file in the/tmp directory, edits the file, and finds the location numbers before and after the error statement, for example, the front and back positions are 368312 and 368315, respectively. After restoring the previous MySQL backup file, enter the following content from the command line:

 
 

  
  
MySQLbinlog --stop-position="368312" /var/log/MySQL/bin.123456 \  
  
  
| MySQL -u root -pmypwd  
  
  
MySQLbinlog --start-position="368315" /var/log/MySQL/bin.123456 \  
  
  
| MySQL -u root -pmypwd \ 
 
 

The above 1st rows restore all transactions until the stop position. The next row will be restored from the given starting position

All transactions that end with the binary log. Because the output of MySQLbinlog includes records of each SQL statement

In the previous set timestamp statement, the recovered data and related MySQL logs will reflect the original time when the transaction was executed.

.

MyISAM table repair:

A corrupted table is typically characterized by unexpected query interruptions and the following errors are displayed:

? "Tbl_name.frm" is locked and cannot be changed.

? The file "tbl_name.MYI" Errcode: nnn cannot be found ).

? File ended unexpectedly.

? The record file is destroyed.

? Error nnn obtained from table Processor

The solution is as follows:

Method 1:

 
 

  
  
myisamchk -r tablename 
 
 

The above method can solve almost all problems. If not, use:

 
 

  
  
myisamchk -o tablename 
 
 

Method 2:

 
 

  
  
1) CHECK TABLE tbl_name [, tbl_name] ... [option] ...  
  
  
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}  
  
  
2) REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE  
  
  
tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]  
 
 

The above content is an introduction to MySQL backup and recovery. I hope you will have some gains.