Mysql热备增量备份与恢复(-)--备份部分

来源:互联网 发布:买卖时机软件下载 编辑:程序博客网 时间:2024/03/29 17:56
1 进行一次全备[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf /data/backup/db_full_20141103InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p141113 18:27:59  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).141113 18:27:59  innobackupex: Connected to MySQL server141113 18:27:59  innobackupex: Executing a version check against the server...141113 18:27:59  innobackupex: Done.141113 18:27:59  innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".innobackupex:  Using server version 5.5.38-loginnobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3999        main::make_backup_dir() called at /usr/bin/innobackupex line 3650        main::init() called at /usr/bin/innobackupex line 1557innobackupex: Error: Failed to create backup directory /data/backup/db_full_20141103: No such file or directory at /usr/bin/innobackupex line 3999.[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf  /home/mysql/db_full_20141103InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p141113 18:28:35  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).141113 18:28:35  innobackupex: Connected to MySQL server141113 18:28:35  innobackupex: Executing a version check against the server...141113 18:28:35  innobackupex: Done.141113 18:28:35  innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".innobackupex:  Using server version 5.5.38-loginnobackupex: Created backup directory /home/mysql/db_full_20141103141113 18:28:35  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/db_full_20141103 --tmpdir=/tmp --extra-lsndir='/tmp'innobackupex: Waiting for ibbackup (pid=21949) to suspendinnobackupex: Suspend file '/home/mysql/db_full_20141103/xtrabackup_suspended_2'xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/mysql/dao_3306/data/xtrabackup: open files limit requested 65535, set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 268435456xtrabackup: using O_DIRECT>> log scanned up to (2081824)xtrabackup: Generating a list of tablespaces[01] Copying ./ibdata1 to /home/mysql/db_full_20141103/ibdata1>> log scanned up to (2081824)>> log scanned up to (2081824)>> log scanned up to (2081824)>> log scanned up to (2081824)>> log scanned up to (2081824)>> log scanned up to (2081824)>> log scanned up to (2081824)[01]        ...done[01] Copying ./dao/t_dao.ibd to /home/mysql/db_full_20141103/dao/t_dao.ibd[01]        ...done>> log scanned up to (2081824)xtrabackup: Creating suspend file '/home/mysql/db_full_20141103/xtrabackup_suspended_2' with pid '21949'141113 18:28:44  innobackupex: Continuing after ibbackup has suspended141113 18:28:44  innobackupex: Executing FLUSH TABLES WITH READ LOCK...141113 18:28:44  innobackupex: All tables locked and flushed to disk141113 18:28:44  innobackupex: Starting to backup non-InnoDB tables and filesinnobackupex: in subdirectories of '/data/mysql/dao_3306/data/'innobackupex: Backing up files '/data/mysql/dao_3306/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)innobackupex: Backing up file '/data/mysql/dao_3306/data//dao/t_dao.frm'innobackupex: Backing up file '/data/mysql/dao_3306/data//dao/db.opt'innobackupex: Backing up files '/data/mysql/dao_3306/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)>> log scanned up to (2081824)141113 18:28:45  innobackupex: Finished backing up non-InnoDB tables and files141113 18:28:45  innobackupex: Executing FLUSH ENGINE LOGS...141113 18:28:45  innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '2081824'xtrabackup: Stopping log copying thread..>> log scanned up to (2081824)xtrabackup: Creating suspend file '/home/mysql/db_full_20141103/xtrabackup_log_copied' with pid '21949'xtrabackup: Transaction log of lsn (2081824) to (2081824) was copied.141113 18:28:46  innobackupex: All tables unlockedinnobackupex: Backup created in directory '/home/mysql/db_full_20141103'innobackupex: MySQL binlog position: filename 'mybinlog.000001', position 107141113 18:28:46  innobackupex: Connection to database server closed141113 18:28:46  innobackupex: completed OK!2 查看数据并增加数据[mysql@server ~]$ mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.5.38-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, 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."root@localhost Thu Nov 13 18:29:00 2014 18:29:00 [(none)]>use dao ;Database changed"root@localhost Thu Nov 13 18:29:02 2014 18:29:02 [dao]>select count(*)  from t_dao ;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 'from t_dao' at line 1"root@localhost Thu Nov 13 18:29:20 2014 18:29:20 [dao]>select count(*) from t_dao ;+----------+| count(*) |+----------+|     1100 |+----------+1 row in set (0.01 sec)"root@localhost Thu Nov 13 18:29:29 2014 18:29:29 [dao]>call insert_t_dao (100) ;Query OK, 1 row affected (0.04 sec)"root@localhost Thu Nov 13 18:29:50 2014 18:29:50 [dao]>commit ;Query OK, 0 rows affected (0.00 sec)"root@localhost Thu Nov 13 18:29:53 2014 18:29:53 [dao]>select count(*) from t_dao ;+----------+| count(*) |+----------+|     1200 |+----------+1 row in set (0.00 sec)"root@localhost Thu Nov 13 18:29:55 2014 18:29:55 [dao]>exitBye3 进行增量备份1[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_201411 /home/my[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_2014110 /home/m[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_20141103/ /home[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_20141103/ /home/mysql/db_full_20141113-incr1;InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p141113 18:31:57  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).141113 18:31:57  innobackupex: Connected to MySQL server141113 18:31:57  innobackupex: Executing a version check against the server...141113 18:31:57  innobackupex: Done.141113 18:31:57  innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".innobackupex:  Using server version 5.5.38-loginnobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3999        main::make_backup_dir() called at /usr/bin/innobackupex line 3650        main::init() called at /usr/bin/innobackupex line 1557innobackupex: Error: Failed to create backup directory /home/mysql/db_full_20141113-incr1: File exists at /usr/bin/innobackupex line 3999.[mysql@server ~]$ rm -rf /home/mysql/db_full_20141113-incr1[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_20141103/ /home/mysql/db_full_20141113-incr1InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p141113 18:32:08  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).141113 18:32:08  innobackupex: Connected to MySQL server141113 18:32:08  innobackupex: Executing a version check against the server...141113 18:32:08  innobackupex: Done.141113 18:32:08  innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".innobackupex:  Using server version 5.5.38-loginnobackupex: Created backup directory /home/mysql/db_full_20141113-incr1141113 18:32:08  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/db_full_20141113-incr1 --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/home/mysql/db_full_20141103/'xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )incremental backup from 2081824 is enabled.xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/mysql/dao_3306/data/xtrabackup: open files limit requested 65535, set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 268435456xtrabackup: using O_DIRECTinnobackupex: Waiting for ibbackup (pid=22199) to suspendinnobackupex: Suspend file '/home/mysql/db_full_20141113-incr1/xtrabackup_suspended_2'>> log scanned up to (2117817)xtrabackup: Generating a list of tablespacesxtrabackup: using the full scan for incremental backup[01] Copying ./ibdata1 to /home/mysql/db_full_20141113-incr1/ibdata1.delta>> log scanned up to (2117817)[01]        ...done>> log scanned up to (2117817)[01] Copying ./dao/t_dao.ibd to /home/mysql/db_full_20141113-incr1/dao/t_dao.ibd.delta[01]        ...donextrabackup: Creating suspend file '/home/mysql/db_full_20141113-incr1/xtrabackup_suspended_2' with pid '22199'141113 18:32:11  innobackupex: Continuing after ibbackup has suspended141113 18:32:11  innobackupex: Executing FLUSH TABLES WITH READ LOCK...141113 18:32:11  innobackupex: All tables locked and flushed to disk141113 18:32:11  innobackupex: Starting to backup non-InnoDB tables and filesinnobackupex: in subdirectories of '/data/mysql/dao_3306/data/'innobackupex: Backing up files '/data/mysql/dao_3306/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)>> log scanned up to (2117817)innobackupex: Backing up file '/data/mysql/dao_3306/data//dao/t_dao.frm'innobackupex: Backing up file '/data/mysql/dao_3306/data//dao/db.opt'innobackupex: Backing up files '/data/mysql/dao_3306/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)141113 18:32:12  innobackupex: Finished backing up non-InnoDB tables and files141113 18:32:12  innobackupex: Executing FLUSH ENGINE LOGS...141113 18:32:12  innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '2117817'xtrabackup: Stopping log copying thread..>> log scanned up to (2117817)xtrabackup: Creating suspend file '/home/mysql/db_full_20141113-incr1/xtrabackup_log_copied' with pid '22199'xtrabackup: Transaction log of lsn (2117817) to (2117817) was copied.141113 18:32:13  innobackupex: All tables unlockedinnobackupex: Backup created in directory '/home/mysql/db_full_20141113-incr1'innobackupex: MySQL binlog position: filename 'mybinlog.000001', position 24907141113 18:32:13  innobackupex: Connection to database server closed141113 18:32:13  innobackupex: completed OK!4 增加数据[mysql@server ~]$ mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.5.38-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, 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."root@localhost Thu Nov 13 18:32:18 2014 18:32:18 [(none)]>use dao ;Database changed"root@localhost Thu Nov 13 18:32:25 2014 18:32:25 [dao]>select count(*) from t_dao ;+----------+| count(*) |+----------+|     1200 |+----------+1 row in set (0.00 sec)"root@localhost Thu Nov 13 18:32:31 2014 18:32:31 [dao]>call insert_t_dao(80);Query OK, 1 row affected (0.01 sec)"root@localhost Thu Nov 13 18:32:45 2014 18:32:45 [dao]>commit ;Query OK, 0 rows affected (0.00 sec)"root@localhost Thu Nov 13 18:32:47 2014 18:32:47 [dao]>exitBye5 进行第二次增量备份[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_201411 /home/my[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_2014111 /home/m[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_20141113 /home/[mysql@server ~]$ innobackupex --no-timestamp --defaults-file=/etc/my.cnf --incremental --incremental-basedir=/home/mysql/db_full_20141113-incr1/ /home/mysql/db_full_20141113-incr2InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oyand Percona LLC and/or its affiliates 2009-2013.  All Rights Reserved.This software is published underthe GNU GENERAL PUBLIC LICENSE Version 2, June 1991.Get the latest version of Percona XtraBackup, documentation, and help resources:http://www.percona.com/xb/p141113 18:33:24  innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_file=/etc/my.cnf;mysql_read_default_group=xtrabackup' (using password: NO).141113 18:33:24  innobackupex: Connected to MySQL server141113 18:33:24  innobackupex: Executing a version check against the server...141113 18:33:24  innobackupex: Done.141113 18:33:24  innobackupex: Starting the backup operationIMPORTANT: Please check that the backup run completes successfully.           At the end of a successful backup run innobackupex           prints "completed OK!".innobackupex:  Using server version 5.5.38-loginnobackupex: Created backup directory /home/mysql/db_full_20141113-incr2141113 18:33:25  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-file="/etc/my.cnf"  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/mysql/db_full_20141113-incr2 --tmpdir=/tmp --extra-lsndir='/tmp' --incremental-basedir='/home/mysql/db_full_20141113-incr1/'xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )incremental backup from 2117817 is enabled.xtrabackup: uses posix_fadvise().xtrabackup: cd to /data/mysql/dao_3306/data/xtrabackup: open files limit requested 65535, set to 1024xtrabackup: using the following InnoDB configuration:xtrabackup:   innodb_data_home_dir = ./xtrabackup:   innodb_data_file_path = ibdata1:1G:autoextendxtrabackup:   innodb_log_group_home_dir = ./xtrabackup:   innodb_log_files_in_group = 3xtrabackup:   innodb_log_file_size = 268435456xtrabackup: using O_DIRECTinnobackupex: Waiting for ibbackup (pid=22365) to suspendinnobackupex: Suspend file '/home/mysql/db_full_20141113-incr2/xtrabackup_suspended_2'>> log scanned up to (2146660)xtrabackup: Generating a list of tablespacesxtrabackup: using the full scan for incremental backup[01] Copying ./ibdata1 to /home/mysql/db_full_20141113-incr2/ibdata1.delta>> log scanned up to (2146660)[01]        ...done[01] Copying ./dao/t_dao.ibd to /home/mysql/db_full_20141113-incr2/dao/t_dao.ibd.delta[01]        ...done>> log scanned up to (2146660)xtrabackup: Creating suspend file '/home/mysql/db_full_20141113-incr2/xtrabackup_suspended_2' with pid '22365'141113 18:33:28  innobackupex: Continuing after ibbackup has suspended141113 18:33:28  innobackupex: Executing FLUSH TABLES WITH READ LOCK...141113 18:33:28  innobackupex: All tables locked and flushed to disk141113 18:33:28  innobackupex: Starting to backup non-InnoDB tables and filesinnobackupex: in subdirectories of '/data/mysql/dao_3306/data/'innobackupex: Backing up files '/data/mysql/dao_3306/data//mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)>> log scanned up to (2146660)innobackupex: Backing up file '/data/mysql/dao_3306/data//dao/t_dao.frm'innobackupex: Backing up file '/data/mysql/dao_3306/data//dao/db.opt'innobackupex: Backing up files '/data/mysql/dao_3306/data//performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)141113 18:33:28  innobackupex: Finished backing up non-InnoDB tables and files141113 18:33:28  innobackupex: Executing FLUSH ENGINE LOGS...141113 18:33:28  innobackupex: Waiting for log copying to finishxtrabackup: The latest check point (for incremental): '2146660'xtrabackup: Stopping log copying thread..>> log scanned up to (2146660)xtrabackup: Creating suspend file '/home/mysql/db_full_20141113-incr2/xtrabackup_log_copied' with pid '22365'xtrabackup: Transaction log of lsn (2146660) to (2146660) was copied.141113 18:33:29  innobackupex: All tables unlockedinnobackupex: Backup created in directory '/home/mysql/db_full_20141113-incr2'innobackupex: MySQL binlog position: filename 'mybinlog.000001', position 44815141113 18:33:29  innobackupex: Connection to database server closed141113 18:33:29  innobackupex: completed OK!

0 0