- ##################################
- 全量备份及恢复（使用xtrabackup，仅限InnoDB和xtradb表，且注意mysql配置文件my.cnf中“default_table_type = InnoDB”否则不成功）
- [root@acong data]# vim /etc/my.cnf
- [mysqld]
- datadir=/usr/local/mysql/data
- default_table_type = InnoDB
- ##################################
[root@acong data]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database aa;
Query OK, 1 row affected (0.00 sec)
mysql> use aa;
Database changed
mysql> create table aa(id int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into aa value(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into aa value(1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into aa value(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into aa value(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into aa value(1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from aa;
+------+
| id |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
5 rows in set (0.00 sec)
mysql> \q
Bye
[root@acong data]# mkdir /data/aa
[root@acong data]# ll /data/aa/
total 0
- [root@acong data]# xtrabackup --default-files=/etc/my.cnf --backup --target-dir=/data/aa/
xtrabackup version 2.0.2 for Percona Server 5.1.59 pc-linux-gnu (i686) (revision id: undefined)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /usr/local/mysql/data
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
>> log scanned up to (50048)
[01] Copying ./ibdata1 to /data/aa/ibdata1
[01] ...done
xtrabackup: The latest check point (for incremental): '50048'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (50048)
xtrabackup: Transaction log of lsn (50048) to (50048) was copied.
[root@acong data]# ll /data/aa/
total 10264
-rw-r----- 1 root root 10485760 Aug 26 18:49 ibdata1
-rw-r----- 1 root root 73 Aug 26 18:49 xtrabackup_checkpoints
-rw-r----- 1 root root 2560 Aug 26 18:49 xtrabackup_logfile
[root@acong data]# netstat -lnt
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
tcp 0 0 :::22 :::* LISTEN
[root@acong data]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use aa;
Database changed
mysql> select * from aa;
+------+
| id |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
5 rows in set (0.00 sec)
mysql> delete from aa where id=1;
Query OK, 5 rows affected (0.01 sec)
mysql> select * from aa;
Empty set (0.00 sec)
mysql> \q
Bye
[root@acong data]# pwd
/usr/local/mysql/data
[root@acong data]# ll
total 20560
drwx------ 2 mysql mysql 4096 Aug 26 18:47 aa
drwx------ 2 mysql mysql 4096 Aug 26 18:15 acong
-rw-rw---- 1 mysql root 15469 Aug 26 18:34 acong.err
-rw-rw---- 1 mysql mysql 5 Aug 26 18:33 acong.pid
-rw-r----- 1 mysql mysql 10485760 Aug 26 18:50 ibdata1
-rw-r--r-- 1 mysql mysql 5242880 Aug 26 18:50 ib_logfile0
-rw-r--r-- 1 mysql mysql 5242880 Aug 26 18:32 ib_logfile1
drwx------ 2 mysql mysql 4096 Aug 26 18:11 lincong
drwx------ 2 mysql root 4096 Aug 24 05:47 mysql
drwx------ 2 mysql root 4096 Aug 24 05:22 test
[root@acong data]# rm -rf ib*
[root@acong data]# ll
total 40
drwx------ 2 mysql mysql 4096 Aug 26 18:47 aa
drwx------ 2 mysql mysql 4096 Aug 26 18:15 acong
-rw-rw---- 1 mysql root 15469 Aug 26 18:34 acong.err
-rw-rw---- 1 mysql mysql 5 Aug 26 18:33 acong.pid
drwx------ 2 mysql mysql 4096 Aug 26 18:11 lincong
drwx------ 2 mysql root 4096 Aug 24 05:47 mysql
drwx------ 2 mysql root 4096 Aug 24 05:22 test
[root@acong data]# /etc/init.d/mysqld stop
Shutting down MySQL...... [ OK ]
[root@acong data]# ll /data/aa/
total 10264
-rw-r----- 1 root root 10485760 Aug 26 18:49 ibdata1
-rw-r----- 1 root root 73 Aug 26 18:49 xtrabackup_checkpoints
-rw-r----- 1 root root 2560 Aug 26 18:49 xtrabackup_logfile
#####################################
恢复：
- [root@acong data]# xtrabackup --default-files=/etc/my.cnf --prepare --target-dir=/data/aa/
xtrabackup version 2.0.2 for Percona Server 5.1.59 pc-linux-gnu (i686) (revision id: undefined)
xtrabackup: cd to /data/aa/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(50048)
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
120826 18:54:31 InnoDB: Initializing buffer pool, size = 100.0M
120826 18:54:31 InnoDB: Completed initialization of buffer pool
120826 18:54:31 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120826 18:54:31 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
120826 18:54:31 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log sequence number 50048
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120826 18:54:31 InnoDB: Starting shutdown...
120826 18:54:31 InnoDB: Shutdown completed; log sequence number 50048
- [root@acong data]# xtrabackup --default-files=/etc/my.cnf --prepare --target-dir=/data/aa/
xtrabackup version 2.0.2 for Percona Server 5.1.59 pc-linux-gnu (i686) (revision id: undefined)
xtrabackup: cd to /data/aa/
xtrabackup: 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:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: Temporary instance for recovery is set as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Warning: innodb_file_io_threads is deprecated. Please use innodb_read_io_threads and innodb_write_io_threads instead
120826 18:54:34 InnoDB: Initializing buffer pool, size = 100.0M
120826 18:54:34 InnoDB: Completed initialization of buffer pool
120826 18:54:34 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120826 18:54:35 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120826 18:54:35 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
120826 18:54:35 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
120826 18:54:35 Percona XtraDB (http://www.percona.com) 1.0.17-12.5 started; log sequence number 50188
[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
120826 18:54:35 InnoDB: Starting shutdown...
120826 18:54:35 InnoDB: Shutdown completed; log sequence number 50188
[root@acong data]# ll
total 36
drwx------ 2 mysql mysql 4096 Aug 26 18:47 aa
drwx------ 2 mysql mysql 4096 Aug 26 18:15 acong
-rw-rw---- 1 mysql root 15891 Aug 26 18:53 acong.err
drwx------ 2 mysql mysql 4096 Aug 26 18:11 lincong
drwx------ 2 mysql root 4096 Aug 24 05:47 mysql
drwx------ 2 mysql root 4096 Aug 24 05:22 test
[root@acong data]# ll /data/aa/
total 22576
-rw-r----- 1 root root 10485760 Aug 26 18:54 ibdata1
-rw-r--r-- 1 root root 5242880 Aug 26 18:54 ib_logfile0
-rw-r--r-- 1 root root 5242880 Aug 26 18:54 ib_logfile1
-rw-r----- 1 root root 73 Aug 26 18:54 xtrabackup_checkpoints
-rw-r----- 1 root root 2097152 Aug 26 18:54 xtrabackup_logfile
[root@acong data]# cp /data/aa/ib* .
[root@acong data]# ll
total 20556
drwx------ 2 mysql mysql 4096 Aug 26 18:47 aa
drwx------ 2 mysql mysql 4096 Aug 26 18:15 acong
-rw-rw---- 1 mysql root 15891 Aug 26 18:53 acong.err
-rw-r----- 1 root root 10485760 Aug 26 18:55 ibdata1
-rw-r--r-- 1 root root 5242880 Aug 26 18:55 ib_logfile0
-rw-r--r-- 1 root root 5242880 Aug 26 18:55 ib_logfile1
drwx------ 2 mysql mysql 4096 Aug 26 18:11 lincong
drwx------ 2 mysql root 4096 Aug 24 05:47 mysql
drwx------ 2 mysql root 4096 Aug 24 05:22 test
- [root@acong data]# chown mysql:mysql ib*
[root@acong data]# ll
total 20556
drwx------ 2 mysql mysql 4096 Aug 26 18:47 aa
drwx------ 2 mysql mysql 4096 Aug 26 18:15 acong
-rw-rw---- 1 mysql root 15891 Aug 26 18:53 acong.err
-rw-r----- 1 mysql mysql 10485760 Aug 26 18:55 ibdata1
-rw-r--r-- 1 mysql mysql 5242880 Aug 26 18:55 ib_logfile0
-rw-r--r-- 1 mysql mysql 5242880 Aug 26 18:55 ib_logfile1
drwx------ 2 mysql mysql 4096 Aug 26 18:11 lincong
drwx------ 2 mysql root 4096 Aug 24 05:47 mysql
drwx------ 2 mysql root 4096 Aug 24 05:22 test
[root@acong data]# /etc/init.d/mysqld start
Starting MySQL. [ OK ]
[root@acong data]# mysql -uroot -p123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.52 Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use aa;
Database changed
mysql> select * from aa;
+------+
| id |
+------+
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
+------+
5 rows in set (0.00 sec)
Xtrabackup 备份和恢复就完成了！
