1.备份xtrabackup工具安装(只能备份数据,不能备份表结构)
http://tongcheng.blog.51cto.com/6214144/1562538
2.xtrabackup基本参数
--defaults-file --数据库的默认配置文件
--target-dir --备份文件存放的目录
--backup --备份到--target-dir目录中
--prepare --备份文件恢复前的准备
--use-memory --分配多大内存用于恢复
--throttle --限制使用的IO个数
--databases --备份指定的数据库
--compress --压缩备份
--incremental-basedir --增量备份的目录
3.数据库完全备份
[root@tong1 ~]# mysql -u root -p -e "select * from tong.t1" --查看数据库中的数据
Enter password:
+------+
| q |
+------+
| 9 |
| 8 |
+------+
[root@tong1 ~]# xtrabackup --default-file=/etc/my.cnf --backup --datadir=/usr/local/mysql-5.6.22/data --target-dir=/opt/all/ --备份所有库
[root@tong1 ~]# ll /opt/all/
total 12300
-rw-r-----. 1 root root 12582912 Apr 24 15:35 ibdata1
drwx------. 2 root root 4096 Apr 24 15:35 tong
-rw-r-----. 1 root root 89 Apr 24 15:35 xtrabackup_checkpoints
-rw-r-----. 1 root root 2560 Apr 24 15:35 xtrabackup_logfile
[root@tong1 ~]#
4.删除数据后恢复
[root@tong1 ~]# mysql -u root -p -e "delete from tong.t1" --删除数据
Enter password:
[root@tong1 ~]# mysql -u root -p -e "select * from tong.t1" --查看数据是否还存在
Enter password:
[root@tong1 ~]# cd /usr/local/mysql-5.6.22/data/tong
[root@tong1 tong]# cp -a /opt/all/tong/* . --将备份的数据覆盖现在的数据文件
cp: overwrite `./t1.ibd'? y
cp: overwrite `./t.ibd'? y
[root@tong1 tong]# chown mysql:mysql * --修改权限
[root@tong1 tong]# ll
total 220
-rw-r--r--. 1 mysql mysql 61 Apr 24 14:37 db.opt
-rw-r--r--. 1 mysql mysql 8554 Apr 24 14:37 t1.frm
-rw-r-----. 1 mysql mysql 98304 Apr 24 15:35 t1.ibd
-rw-r--r--. 1 mysql mysql 8554 Apr 24 14:37 t.frm
-rw-r-----. 1 mysql mysql 98304 Apr 24 15:35 t.ibd
[root@tong1 tong]# /etc/init.d/mysqld restart --重启服务
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@tong1 data]# mysql -u root -p -e "select * from tong.t1"
Enter password:
+------+
| q |
+------+
| 9 |
| 8 | --数据回来了
+------+
[root@tong1 data]#
5.增量备份
[root@tong1 data]# mysql -u root -p -e "insert into tong.t1 values(7),(6),(5)"
Enter password:
[root@tong1 data]# mysql -u root -p -e "select * from tong.t1"
Enter password:
+------+
| q |
+------+
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
+------+
[root@tong1 tong]# xtrabackup --default-file=/etc/my.cnf --backup --datadir=/usr/local/mysql-5.6.22/data --target-dir=/opt/incr/ --incremental-basedir=/opt/all/
[root@tong1 tong]# ll /opt/incr/
total 176
-rw-r-----. 1 root root 163840 Apr 24 15:47 ibdata1.delta
-rw-r-----. 1 root root 44 Apr 24 15:47 ibdata1.meta
drwx------. 2 root root 4096 Apr 24 15:47 tong
-rw-r-----. 1 root root 93 Apr 24 15:47 xtrabackup_checkpoints
-rw-r-----. 1 root root 2560 Apr 24 15:47 xtrabackup_logfile
[root@tong1 tong]#
6.删除数据后恢复数据
[root@tong1 data]# mysql -u root -p -e "delete from tong.t1"
Enter password:
[root@tong1 data]# mysql -u root -p -e "select * from tong.t1"
Enter password:
[root@tong1 data]# xtrabackup --default-file=/etc/my.cnf --prepare --target-dir=/opt/all/
[root@tong1 data]# xtrabackup --default-file=/etc/my.cnf --prepare --target-dir=/opt/all/ --incremental-dir=/opt/incr/
[root@tong1 data]# cd /opt/all/tong/
[root@tong1 tong]# cp -a * /usr/local/mysql-5.6.22/data/tong
cp: overwrite `/usr/local/mysql-5.6.22/data/tong/t1.ibd'? y
cp: overwrite `/usr/local/mysql-5.6.22/data/tong/t.ibd'? y
[root@tong1 tong]# cd /usr/local/mysql-5.6.22/data/tong
[root@tong1 tong]# chown mysql:mysql *
[root@tong1 tong]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@tong1 tong]# mysql -u root -p -e "select * from tong.t1"
Enter password:
+------+
| q |
+------+
| 9 |
| 8 |
| 7 |
| 6 |
| 5 |
+------+
[root@tong1 tong]#
