MySQL Using xtrabackup backup and recovery example

installation xtrabackup

insert data

innodb

[root@test ~]# /service/mysql/bin/mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 18Server version: 5.5.25-log zhongwc_DBCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use zwcDatabase changedmysql> DELIMITER ;;mysql> CREATE PROCEDURE proc_buildata(IN total INT)    -> BEGIN    ->     DECLARE var INT DEFAULT 0;    ->     PREPARE DDL_SQL FROM 'CREATE TABLE  `employee` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`employeeid` int(10) unsigned NOT NULL,`employeename` varchar(64) NOT NULL DEFAULT '''',PRIMARY KEY (`id`)) ENGINE=InnoDB';     ->     EXECUTE DDL_SQL;    ->     WHILE var<total DO    ->         SET var=var+1;    ->         INSERT INTO employee (employeeid,employeename) VALUES (var,CONCAT('test',var));    ->     END WHILE;    -> END ;;Query OK, 0 rows affected (0.06 sec)mysql> call proc_buildata(1000000);Query OK, 1 row affected (1 min 5.79 sec)mysql> select count(*) zwc.employee;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.employee' at line 1mysql> select count(*) from zwc.employee;+----------+| count(*) |+----------+|  1000000 |+----------+1 row in set (0.17 sec)mysql> show create table zwc.employee\G*************************** 1. row ***************************       Table: employeeCreate Table: CREATE TABLE `employee` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `employeeid` int(10) unsigned NOT NULL,  `employeename` varchar(64) NOT NULL DEFAULT '',  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf81 row in set (0.03 sec)

myisam

mysql> create table zwc.employee_myisam engine = myisam as select * from zwc.employee;Query OK, 1000000 rows affected (0.90 sec)Records: 1000000  Duplicates: 0  Warnings: 0mysql> show create table zwc.employee_myisam\G*************************** 1. row ***************************       Table: employee_myisamCreate Table: CREATE TABLE `employee_myisam` (  `id` int(10) unsigned NOT NULL DEFAULT '0',  `employeeid` int(10) unsigned NOT NULL,  `employeename` varchar(64) NOT NULL DEFAULT '') ENGINE=MyISAM DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> select count(*) from zwc.employee_myisam;+----------+| count(*) |+----------+|  1000000 |+----------+1 row in set (0.00 sec)

datafile,. ibd for innodb datafile

[root@test ~]# ls /data/mysql/zwc/ -lhtotal 72M-rw-rw---- 1 mysql mysql   61 Jan 10 11:20 db.opt-rw-rw---- 1 mysql mysql 8.5K Jan 10 11:27 employee.frm-rw-rw---- 1 mysql mysql  48M Jan 10 11:28 employee.ibd-rw-rw---- 1 mysql mysql 8.5K Jan 10 11:30 employee_myisam.frm-rw-rw---- 1 mysql mysql  23M Jan 10 11:30 employee_myisam.MYD-rw-rw---- 1 mysql mysql 1.0K Jan 10 11:30 employee_myisam.MYI

create backup recovery users

mysql> GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bakuser'@'localhost' IDENTIFIED BY 'bakuser';Query OK, 0 rows affected (0.03 sec)

run full backup

[root@test mysql]# /service/mysql/bin/innobackupex --user=bakuser --password=bakuser --port=30306 --slave-info --tmpdir=/backup/mysql --defaults-file=/service/mysql/my.cnf  /backup/mysqlInnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2011.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.130110 14:33:36  innobackupex: Starting mysql with options:  --defaults-file='/service/mysql/my.cnf' --password='bakuser' --user='bakuser' --port='30306' --unbuffered --130110 14:33:36  innobackupex: Connected to database with mysql child process (pid=13503)130110 14:33:42  innobackupex: Connection to database server closedIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".innobackupex: Using mysql  Ver 14.14 Distrib 5.5.25, for Linux (x86_64) using readline 5.1innobackupex: Using mysql server version Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.innobackupex: Created backup directory /backup/mysql/2013-01-10_14-33-42130110 14:33:42  innobackupex: Starting mysql with options:  --defaults-file='/service/mysql/my.cnf' --password='bakuser' --user='bakuser' --port='30306' --unbuffered --130110 14:33:42  innobackupex: Connected to database with mysql child process (pid=13530)130110 14:33:46  innobackupex: Connection to database server closed130110 14:33:46  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/service/mysql/my.cnf" --backup --suspend-at-end --target-dir=/backup/mysql/2013-01-10_14-33-42innobackupex: Waiting for ibbackup (pid=13541) to suspendinnobackupex: Suspend file '/backup/mysql/2013-01-10_14-33-42/xtrabackup_suspended'xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (x86_64) (revision id: undefined)xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/mysqlxtrabackup: Target instance is assumed as followings.xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 268435456130110 14:33:46 InnoDB: Using Linux native AIOxtrabackup: use O_DIRECT130110 14:33:47  InnoDB: Warning: allocated tablespace 2, old maximum was 0>> log scanned up to (297286751)[01] Copying ./ibdata1      to /backup/mysql/2013-01-10_14-33-42/ibdata1[01]        ...done[01] Copying ./zwc/employee.ibd      to /backup/mysql/2013-01-10_14-33-42/zwc/employee.ibd[01]        ...done130110 14:33:50  innobackupex: Continuing after ibbackup has suspended130110 14:33:50  innobackupex: Starting mysql with options:  --defaults-file='/service/mysql/my.cnf' --password='bakuser' --user='bakuser' --port='30306' --unbuffered --130110 14:33:50  innobackupex: Connected to database with mysql child process (pid=13556)>> log scanned up to (297286751)130110 14:33:54  innobackupex: Starting to lock all tables...>> log scanned up to (297286751)>> log scanned up to (297286751)130110 14:34:06  innobackupex: All tables locked and flushed to disk130110 14:34:06  innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files ininnobackupex: subdirectories of '/data/mysql'innobackupex: Backing up file '/data/mysql/zwc/employee_myisam.MYD'innobackupex: Backing up file '/data/mysql/zwc/employee_myisam.frm'innobackupex: Backing up file '/data/mysql/zwc/employee_myisam.MYI'innobackupex: Backing up file '/data/mysql/zwc/db.opt'innobackupex: Backing up file '/data/mysql/zwc/employee.frm'innobackupex: Backing up files '/data/mysql/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)innobackupex: Backing up files '/data/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)130110 14:34:07  innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt filesinnobackupex: Resuming ibbackupxtrabackup: The latest check point (for incremental): '297286751'>> log scanned up to (297286751)>> log scanned up to (297286751)xtrabackup: Stopping log copying thread..xtrabackup: Transaction log of lsn (297286751) to (297286751) was copied.130110 14:34:09  innobackupex: All tables unlocked130110 14:34:09  innobackupex: Connection to database server closedinnobackupex: Backup created in directory '/backup/mysql/2013-01-10_14-33-42'innobackupex: MySQL binlog position: filename 'binlog-master.000004', position 305779142innobackupex: MySQL slave binlog position: master host '', filename '', position 130110 14:34:09  innobackupex: completed OK!

Note:

In the backup process, the INNODB data file backup is completed, will lock the database, and began to copy MYISAM and non affairs engine data and  .frm. So if you have more MYISAM table, lock database will last very long. If it is in the master database to run on, must pay attention to.

full recovery

apply log

This process is mainly produce REDOLOG and backup produced during REDO applied to data file, xtrabackup will start a INNODB process to do it, and your current in running don't conflict.

[root@test ~]# rm -rf /data/mysql/zwc[root@test ~]# /service/mysql/bin/innobackupex --user=bakuser --password=bakuser --defaults-file=/service/mysql/my.cnf  --apply-log /backup/mysql/2013-01-10_14-33-42/InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2011.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.IMPORTANT: Please check that the apply-log run completes successfully.           At the end of a successful apply-log run innobackupex           prints "completed OK!".130110 14:47:06  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/service/mysql/my.cnf" --prepare --target-dir=/backup/mysql/2013-01-10_14-33-42xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (x86_64) (revision id: undefined)xtrabackup: cd to /backup/mysql/2013-01-10_14-33-42xtrabackup: This target seems to be not prepared yet.xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(297286751)xtrabackup: Temporary instance for recovery is set as followings.xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 1xtrabackup:   innodb_log_file_size = 2097152130110 14:47:06 InnoDB: Using Linux native AIOxtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)130110 14:47:06 InnoDB: The InnoDB memory heap is disabled130110 14:47:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins130110 14:47:06 InnoDB: Compressed tables use zlib 1.2.3130110 14:47:06 InnoDB: Using Linux native AIO130110 14:47:06 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead130110 14:47:06 InnoDB: Initializing buffer pool, size = 100.0M130110 14:47:06 InnoDB: Completed initialization of buffer pool130110 14:47:06 InnoDB: highest supported file format is Barracuda.InnoDB: The log sequence number in ibdata files does not matchInnoDB: the log sequence number in the ib_logfiles!130110 14:47:06  InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004130110 14:47:06  InnoDB: Error: table 'zwc/t_test'InnoDB: in InnoDB data dictionary has tablespace id 1,InnoDB: but tablespace with that id or name does not exist. It will be removed from data dictionary.130110 14:47:06  InnoDB: Waiting for the background threads to start130110 14:47:07 InnoDB: 1.1.5 started; log sequence number 297286751[notice (again)]  If you use binary log and don't use any hack of group commit,  the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004xtrabackup: starting shutdown with innodb_fast_shutdown = 1130110 14:47:07  InnoDB: Starting shutdown...130110 14:47:08  InnoDB: Shutdown completed; log sequence number 297288039130110 14:47:08  innobackupex: Restarting xtrabackup with command: xtrabackup_55  --defaults-file="/service/mysql/my.cnf" --prepare --target-dir=/backup/mysql/2013-01-10_14-33-42for creating ib_logfile*xtrabackup_55 version 1.6.2 for MySQL server 5.5.10 Linux (x86_64) (revision id: undefined)xtrabackup: cd to /backup/mysql/2013-01-10_14-33-42xtrabackup: This target seems to be already prepared.xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.xtrabackup: Temporary instance for recovery is set as followings.xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 268435456130110 14:47:08 InnoDB: Using Linux native AIOxtrabackup: Starting InnoDB instance for recovery.xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)130110 14:47:08 InnoDB: The InnoDB memory heap is disabled130110 14:47:08 InnoDB: Mutexes and rw_locks use GCC atomic builtins130110 14:47:08 InnoDB: Compressed tables use zlib 1.2.3130110 14:47:08 InnoDB: Using Linux native AIO130110 14:47:08 InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead130110 14:47:08 InnoDB: Initializing buffer pool, size = 100.0M130110 14:47:08 InnoDB: Completed initialization of buffer pool130110 14:47:08  InnoDB: Log file ./ib_logfile0 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile0 size to 256 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200130110 14:47:11  InnoDB: Log file ./ib_logfile1 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile1 size to 256 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200130110 14:47:14  InnoDB: Log file ./ib_logfile2 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile2 size to 256 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100 200130110 14:47:17 InnoDB: highest supported file format is Barracuda.InnoDB: The log sequence number in ibdata files does not matchInnoDB: the log sequence number in the ib_logfiles!130110 14:47:17  InnoDB: Database was not shut down normally!InnoDB: Starting crash recovery.InnoDB: Reading tablespace information from the .ibd files...InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004130110 14:47:18  InnoDB: Waiting for the background threads to start130110 14:47:19 InnoDB: 1.1.5 started; log sequence number 297288204[notice (again)]  If you use binary log and don't use any hack of group commit,  the binary log position seems to be:InnoDB: Last MySQL binlog file position 0 305778818, file name ./binlog-master.000004xtrabackup: starting shutdown with innodb_fast_shutdown = 1130110 14:47:19  InnoDB: Starting shutdown...130110 14:47:19  InnoDB: Shutdown completed; log sequence number 297288204130110 14:47:19  innobackupex: completed OK!

copy the data files to data directory

This process will restore good data copy to my. cnf specified in the data directory. Then you need to put the original case stop.

[root@test ~]# /service/mysql/bin/mysqladmin -uroot shutdown -pEnter password: [root@test ~]# ps -ef|grep mysql |grep -v grep[root@test ~]# 

[root@test opt]# /service/mysql/bin/innobackupex --user=bakuser --password=bakuser --defaults-file=/service/mysql/my.cnf --copy-back /backup/mysql/2013-01-10_14-33-42/InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona Inc 2009-2011.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.IMPORTANT: Please check that the copy-back run completes successfully.           At the end of a successful copy-back run innobackupex           prints "completed OK!".sh: autodetect: command not foundinnobackupex: fatal error: no 'mysqld' group in MySQL optionsinnobackupex: fatal error: OR no 'datadir' option in group 'mysqld' in MySQL options

Estimate here is the 1.62 version of the bug.There is no way, manual copying datafile.

[root@test ~]# cd /backup/mysql/2013-01-10_14-33-42/[root@test 2013-01-10_14-33-42]# cp -rp zwc/ /data/mysql/[root@test 2013-01-10_14-33-42]# chown -R mysql.mysql /data/mysql/zwc

startup mysql validation recovery

[root@test 2013-01-10_14-33-42]# /service/mysql/bin/mysqld_safe --defaults-file=/service/mysql/my.cnf --basedir=/service/mysql --datadir=/data/mysql --user=mysql &[1] 14663[root@test 2013-01-10_14-33-42]# 130110 15:05:01 mysqld_safe Logging to '/data/mysql/test.localdomain.err'.130110 15:05:01 mysqld_safe Starting mysqld daemon with databases from /data/mysql[root@test 2013-01-10_14-33-42]# /service/mysql/bin/mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.25-log zhongwc_DBCopyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use zwcDatabase changedmysql> select count(*) from employee;+----------+| count(*) |+----------+|  1000000 |+----------+1 row in set (0.60 sec)mysql> select count(*) from employee_myisam;+----------+| count(*) |+----------+|  1000000 |+----------+1 row in set (0.01 sec)