MySQL实时增量备份

[root@localhost~]# vim  /etc/my.cnf
[mysqld]
.. ..
log-bin=mysql-bin                              //启用二进制日志，并指定前缀
.. ..
[root@dbsvr1 ~]# service mysqld restart

[root@localhost~]# ls /var/lib/mysql/mysql-bin.*
/var/lib/mysql/mysql-bin.000001  /var/lib/mysql/mysql-bin.index

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002
[root@localhost ~]# mysqlbinlog --start-datetime="2018-10-20 23:30" /var/lib/mysql/mysql-bin.000002

[root@loclahost ~]# mysqlbinlog \
    --start-datetime="2018-10-12 20:40:50" \ 
    --stop-datetime="2018-10-20 23:15:50" \
    /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
若是全部重做
[root@loclahost ~]# mysqlbinlog  /var/lib/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:

[root@localhost~]# mkdir -p /backup/mysql
[root@localhost~]# xtrabackup_56 –backup --datadir=/var/lib/mysql/ --target-dir=/backup/mysql/

[root@loclahost~]# ls /backup/mysql/

[root@localhost ~]# xtrabackup_56  --backup \        #xtrabackup_55 是 5.5 版本,xtrabackup_56 是 5.6 版本
    --datadir=/var/lib/mysql/ \
    --target-dir=/backup/inc01/ \
    --incremental-basedir=/backup/mysql/

[root@localhost~]# ls /backup/inc01/

[root@localhost~]# du -sh /backup/mysql/ /backup/inc01/
11M     /backup/mysql/                                  //完整备份的大小
264K    /backup/inc01/

[root@localhost ~]# xtrabackup_56 --prepare --target-dir=/backup/mysql/ 
[root@localhost ~]# xtrabackup_56 --prepare --target-dir=/backup/mysql/

[root@loclahost ~]# xtrabackup_56  --prepare --target-dir=/backup/mysql --apply-log-only

[root@localhost ~]# xtrabackup_56  --prepare \
    --target-dir=/backup/mysql --apply-log-only \
    --incremental-dir=/backup/inc01