Mysql data backup and recovery, mysql backup and recovery

Mysql data backup and recovery, mysql backup and recovery
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 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 parameter, set line-terminated-by = '\ r \ n' in windows and line-terminated-by =' \ n' in linux '.

Use the backup tool ibbackup

Ibbackup is a hot backup tool used by innodb (www.innodb.com) to perform 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:

[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 data to/home/heikki/backup, the example of my2.cnf 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 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-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 finds the location numbers before and after the error statement, for example, the front and back positions are 368312 and 368315, respectively. After the previous backup file is restored, enter the following content 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 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] TABLEtbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]