全量
[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)