MySQL Data backup and recovery

Backup/Restore Policy:
1. Do a regular MySQL backup and consider the recovery time that the system can afford.
2. Make sure that MySQL opens Log-bin, with Binarylog,mysql to do full recovery when necessary, or point-in-time recovery, or location-based recovery.

3. To do regular backup recovery testing, make sure that backups are valid and can be recovered.

Cold backup

Backup:
1. Stop the MySQL service and back up the MySQL data files at the operating system level.
2. Restart the MySQL service and back up the binlog that were generated after the reboot.
Recovery:
1. Stop the MySQL service and restore the MySQL data file at the operating system level.
2. Restart the MySQL service and use Mysqlbinlog to recover the binlog since the backup.

Logical backup

Backup:
1. Choose to use Mysqldump–f (flush-logs) to back up data when the system is idle, such as at night
Library.

Mysqldump–u root–p*** pointcard–f > Pointcard.sql

2. And back up the binlog generated after mysqldump started.
Recovery:
1. Stop the application and perform the MySQL import backup file.

Mysql–u root–p*** Pointcard < Pointcard.sql

2. Use Mysqlbinlog to recover Binlog since mysqldump backup.

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 are cross-platform (Windows and Linux), then be aware of setting the parameter line-terminated-by, set to Line-terminated-by= ' \r\ on Windows N ', set to line-terminated-by= ' \ n ' on Linux.

Using the Backup tool ibbackup

Ibbackup is a hot backup tool for InnoDB Corporation (www.innodb.com), which specializes in physical hot-backup of the InnoDB storage engine, which is chargeable and cannot be used for free. Now InnoDB has been acquired by Oracle.
How to use:
Edit the profile used to start MY.CNF and the configuration file for backup my2.cnf
Examples of my.cnf are as follows:

[Mysqld]datadir =/home/heikki/data Innodb_data_home_dir =/home/heikki/datainnodb_data_file_path = Ibdata1:10M: Autoextendinnodb_log_group_home_dir =/home/heikki/dataset-variable = Innodb_log_files_in_group=2set-variable = innodb_log_file_size=20m

If you want to back up to/home/heikki/backup, the MY2.CNF example is as follows:

[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=2set-variable = innodb_log_file_size=20m

Start Backup Ibbackup my.cnf my2.cnf If recovery is required, log redo Ibbackup--apply-log my2.cnf Restart the database service./bin/mysqld_saft--defaults-file= MY2.CNF &

Point-in-time recovery:

1. If an error occurs 10 o'clock in the morning, you can use the following statement to restore data to the Binglog with backup and
Before the barrier:

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

2. Skip the point in time of failure, proceed to the back Binlog, complete the recovery

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

Location Recovery:

Similar to point-in-time recovery, but more precise, the steps are as follows:

Mysqlbinlog--start-date= "2005-04-20 9:55:00"--stop-date= "2005-04-2010: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 locates the position number before and after the error statement, for example, the front and back position numbers are 368312 and 368315, respectively. After restoring the previous backup file, you should enter the following from the command line:

Mysqlbinlog--stop-position= "368312"/var/log/mysql/bin.123456 | Mysql-u root-pmypwdmysqlbinlog--start-position= "368315"/var/log/mysql/bin.123456 | Mysql-u root-pmypwd \

The 1th row above reverts to all transactions until the stop location. The next line resumes from the given starting position
All transactions until the binary log ends. Because the output of mysqlbinlog includes each SQL statement record
Before the set TIMESTAMP statement, the recovered data and the associated MySQL log will react to the original time of the transaction execution
Room

MyISAM Table FIX:

The symptoms of a damaged table are usually a query that is interrupted unexpectedly and can see the following error:
? "Tbl_name.frm" is locked and cannot be changed.
? Cannot find file "Tbl_name. MYI "(errcode:nnn).
? The file ended unexpectedly.
? The record file was destroyed.
? Getting error nnn from the table processor
Here's how to fix it:

Method One:

MYISAMCHK-R TableName

The above approach solves almost any problem, and if not, use:

Myisamchk-o TableName

Method Two:

MySQL Data backup and recovery