全量备份与增量备份实践

###########################################################开发云主机域名############################1、安装Xtrabackup备份命令wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repoyum -y install perl perl-devel libaio libaio-develperl-Time-HiRes perl-DBD-MySQLwget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpmyum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpminnobackupex ******2、创建logs目录并配置权限mkdir /application/mysql/logs -pchown -R /mysql.mysql /application/mysql/logs3、配置/etc/my.cnf文件[client]user=rootpassword=oldboy123[mysqld]basedir= /application/mysql/ datadir= /application/mysql/data/###########binlog############log_bin = /application/mysql/logs/oldboy-binexpire_logs_days = 7sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES[mysqld_safe]log-error = /application/mysql/logs/oldboy.err配置完成重启生效：[root@db02 data]#/etc/init.d/mysqld restartShutting down MySQL.. SUCCESS!Starting MySQL… SUCCESS!4、全被备份mkdir /server/backup -pinnobackupex –defaults-file=/etc/my.cnf–user=root –password=oldboy123–socket=/application/mysql-5.6.34/tmp/mysql.sock –no-timestamp/server/backup/full5、恢复数据前的准备（合并xtrabackup_log_file和备份的物理文件）innobackupex –apply-log –use-memory=32M/server/backup/full/停库：/etc/init.d/mysqld stoplsof -i :3306破坏数据：cd /application/mysql/mv data /opt/恢复：cp -a /server/backup/full/ /application/mysql/datachown -R mysql.mysql /application/mysql/data启动：/etc/init.d/mysqld startmysql -e “select * from oldboy.test”数据恢复成功。环境准备：du -sh /application/mysql/data/free -mcd /server/backup/rm -rf *1、全量备份（周一0点）innobackupex –defaults-file=/etc/my.cnf–user=root –password=oldboy123–socket=/application/mysql-5.6.34/tmp/mysql.sock –no-timestamp/server/backup/base_full2、第一次增量备份（周二0点）a.准备use oldboy；mysql> insert into test values(8,’outman’); 插入两条数据Query OK, 1 row affected (0.00 sec)mysql> insert into test values(9,’outgirl’);Query OK, 1 row affected (0.00 sec)select *from test;b.增量innobackupex –defaults-file=/etc/my.cnf–user=root –password=oldboy123–socket=/application/mysql-5.6.34/tmp/mysql.sock –no-timestamp–incremental-basedir=/server/backup/base_full –incremental/server/backup/one_inc2、第二次增量mysql> use oldboymysql> insert into test values(10,’two_inc1′);Query OK, 1 row affected (0.00 sec)mysql> insert into test values(11,’two_inc2′);Query OK, 1 row affected (0.00 sec)innobackupex –defaults-file=/etc/my.cnf–user=root –password=oldboy123–socket=/application/mysql-5.6.34/tmp/mysql.sock –no-timestamp –parallel=3–incremental-basedir=/server/backup/one_inc/ –incremental/server/backup/two_inc增量恢复：innobackupex –apply-log –use-memory=32M–redo-only /server/backup/base_full/innobackupex –apply-log –use-memory=32M–redo-only –incremental-dir=/server/backup/one_inc /server/backup/base_full/innobackupex –apply-log –use-memory=32M–incremental-dir=/server/backup/two_inc /server/backup/base_full/正式数据文件恢复过程：[root@db02 backup]# /etc/init.d/mysqld stopShutting down MySQL.. SUCCESS![root@db02 backup]# cd /application/mysql[root@db02 mysql]# mv data /tmp/[root@db02 mysql]# /bin/cp -a/server/backup/base_full data[root@db02 mysql]# chown -R mysql.mysql data[root@db02 mysql]# /etc/init.d/mysqld startStarting MySQL……… SUCCESS![root@db02 mysql]# mysql -e “select * fromoldboy.test;”+—-+———-+| id | name|+—-+———-+| 1 |oldboy || 2 |oldgirl || 3 |inca || 4 |zuma || 5 |kaka || 6 |bingbing || 7 |xiaoting || 8 |outman || 9 |outgirl || 10 | two_inc1 || 11 | two_inc2 |+—-+———-+由此可以看到增量恢复数据恢复成功！

相关推荐: MySql基础部署以及基本使用(用于个人学习与回顾)

MySQL数据库介绍最为著名、应用最为广泛的开源数据库软件 -最早隶属于瑞典的MySQL AB公司 -2008年1月，MySQL AB被SUN收购 -2009年4月，SUN被Oracle收购崭新的开源分支MariaDB -为应付MySQL可能会闭源的风险而诞生…