xtrabackup全量+增量备份手记

全量

[root@localhost ~]# rm -rf /backups/mysql/20151026/
[root@localhost ~]# ll /backups/mysql/
total 0
[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=root --password="root" --port=3306 --backup --target-dir=/backups/mysql/$(date +%Y%m%d)/
...
sent 2148828291 bytes  received 301 bytes  138634102.71 bytes/sec
total size is 2148565014  speedup is 1.00

[root@localhost 20151026]# ll /home/data/mysql/data/
total 5255264
-rw-rw----. 1 root  root  1073741824 Oct 26 04:28 IBdata1
-rw-rw----. 1 root  root  1073741824 Oct 26 04:28 IBdata2

-rw-r--r--. 1 root  root          22 Oct 26 04:28 xtrabackup_binlog_pos_innodb
[root@localhost 20151026]# chown -R mysql:mysql /home/data/mysql/data/
[root@localhost 20151026]# ll /home/data/mysql/data/
total 5255264
-rw-rw----. 1 mysql mysql 1073741824 Oct 26 04:28 IBdata1
-rw-rw----. 1 mysql mysql 1073741824 Oct 26 04:28 IBdata2
-rw-r--r--. 1 mysql mysql         22 Oct 26 04:28 xtrabackup_binlog_pos_innodb
[root@localhost 20151026]# service mysqld5612 start
Starting MySQL........................... SUCCESS! 

[root@localhost 20151026]# mysql -uroot -proot
....
mysql> select * from t5.test1;
+------+------+
| id   | name |
+------+------+
|    3 | c    |
|    2 | b    |
|    1 | a    |
+------+------+
3 rows in set (0.05 sec)

增量

[root@localhost ~]# mysql -uroot -proot -e "CREATE USER 'backup'@'%' IDENTIFIED BY 'backup'";
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -ubackup -pbackup

[root@localhost ~]# mysql -uroot -proot -e "GRANT RELOAD,LOCK TABLES,REPLICATION CLIENT,CREATE TABLESPACE,SUPER ON *.* TO 'backup'@'%'";
Warning: Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -ubackup -pbackup
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.13-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| business_db        |
| mysql              |
| performance_schema |
| t1                 |
| t5                 |
| test               |
+--------------------+
7 rows in set (0.12 sec)

备份

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --user=backup --password="backup" --port=3306 --backup --target-dir=/backups/mysql/full_incre_$(date +%Y%m%d)
...
xtrabackup: Transaction log of lsn (177047752) to (177047752) was copied.

恢复

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --backup --user=backup --password="backup" --target-dir=/backups/mysql/incre_20151027/ --incremental-basedir=/backups/mysql/incre_20151026/
....
xtrabackup: Transaction log of lsn (177051860) to (177051860) was copied.

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=backup --password="backup" --apply-log-only --target-dir=/backups/mysql/full_incre_20151026/

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=backup --password="backup" --apply-log-only --target-dir=/backups/mysql/full_incre_20151026/ --incremental-dir=/backups/mysql/incre_20151027/

[root@localhost ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --user=backup --password="backup" --target-dir=/backups/mysql/full_incre_20151026/

[root@localhost ~]# service mysqld5612 stop
Shutting down MySQL...... SUCCESS! 
[root@localhost ~]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' /backups/mysql/full_incre_20151026/ /home/data/mysql/data/

[root@localhost ~]# chown -R mysql:mysql /home/data/mysql/data
[root@localhost ~]# service mysql start
mysql: unrecognized service
[root@localhost ~]# service mysqld5612 start
Starting MySQL................................. SUCCESS! 
[root@localhost ~]# 

========================mysql操作=======随后整理==================

[root@localhost ~]# mysql -uroot -proot

mysql> use t1;
Database changed
mysql> create table test2 select 1 as id,'aaa' as name;
Query OK, 1 row affected (0.46 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.03 sec)

mysql> create table test3 select 1 as id,'bbb' as name;
Query OK, 1 row affected (0.25 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show tables;
+--------------+
| Tables_in_t1 |
+--------------+
| test1        |
| test2        |
| test3        |
+--------------+
3 rows in set (0.14 sec)

mysql> select * from test2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.00 sec)

mysql> select * from test3;
+----+------+
| id | name |
+----+------+
|  1 | bbb  |
+----+------+
1 row in set (0.04 sec)

mysql> delete from test2;delete from test3;
Query OK, 1 row affected (0.15 sec)

Query OK, 1 row affected (0.01 sec)

mysql> select * from test3;
Empty set (0.00 sec)

mysql> select * from test2;
Empty set (0.00 sec)

mysql> exit


[root@localhost ~]# mysql -uroot -proot

mysql> select * from t1.test1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.06 sec)

mysql> select * from t1.test2;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
+----+------+
1 row in set (0.07 sec)

mysql> select * from t1.test3;
+----+------+
| id | name |
+----+------+
|  1 | bbb  |
+----+------+
1 row in set (0.10 sec)