mysqlbackup 使用学习
1、设置数据库用户的相关权限
create user backupuser@'127.0.0.1' identified by '';
grant reload,replication client,super,process on *.* to backupuser@'127.0.0.1';
grant create,insert,drop,update on mysql.backup_progress to backupuser@'127.0.0.1';
grant create,insert,select,drop,update on mysql.backup_history to backupuser@'127.0.0.1';
grant lock tables,select,create,alter on *.* to backupuser@'127.0.0.1';
grant create,insert,drop,update on mysql.backup_sbt_history to backupuser@'127.0.0.1'; create user backupuser@'localhost' identified by '';
grant reload,replication client,super,process on *.* to backupuser@'localhost';
grant create,insert,drop,update on mysql.backup_progress to backupuser@'localhost';
grant create,insert,select,drop,update on mysql.backup_history to backupuser@'localhost';
grant lock tables,select,create,alter on *.* to backupuser@'localhost';
grant create,insert,drop,update on mysql.backup_sbt_history to backupuser@'localhost';
2、backup-dir
可以看成是临时目录，备份期间mysqlbackup会向它写入一些数据
3、一个典型的mysqlbackup工作周期包涵
1：备份
2：校验
3：还原
4、mysqlbackup 备份整个mysql实例
mysqlbackup --host=127.0.0.1 --port= --user=backupuser --password= --backup-dir=/tmp/backup_temp --backup-image=/root/backup/full_backup.mbi backup-to-image
1：--backup-dir 临时目录在mysqlbackup执行备份时会向它里面写一些数据，但是备份完成之后mysqlbackup并没有自动的去删除它；如果下次再执行上面相同的命令，
会因为backup-dir不为空而失败，所以在执行备份之前要保证这个目录是空的。
5、校验备份
mysqlbackup --backup-image=/root/backup/full_backup.mbi validate
6、还原备份
mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/root/backup/full_backup.mbi --backup-dir=/tmp/backup_temp copy-back-and-apply-log
7、一些mysqlbackup的高级玩法
1:流式的备份到stdout
mysqlbackup --host=127.0.0.1 --port= --user=backupuser --password= --backup-image=- --backup-dir=/tmp/backup_temp backup-to-image >/tmp/.mbi
2:校验一个流式备份
mysqlbackup --backup-image=/tmp/.mbi validate
3:还原备份
mysqlbackup --host=127.0.0.1 --port= --user=backupuser --password= --backup-dir=/tmp/backup_temp --backup-image=/tmp/.mbi copy-back-and-apply-log
8、把一个单文件的备份转换为文件夹的备份
mysqlbackup --backup-image=/tmp/.mbi --backup-dir=/tmp/backup_temp/ image-to-backup-dir
9、把一个文件夹的备份转换成单一文件的备份
mysqlbackup --backup-image=/tmp/.mbi --backup-dir=/tmp/backup_temp/ backup-dir-to-image
10、查看单文件备份中所包涵的内容
mysqlbackup --backup-image=/tmp/.mbi list-image
11、抽取单文件中的内容到当前目录
mysqlbackup --backup-image=/tmp/.mbi extract
12、从单文件备份中抽取指定文件(文件的路径可以由list-image看到，) extract
mysqlbackup --backup-image=/tmp/.mbi --src-entry=server-all.cnf extract
13、备份时压缩 --compress --compress-level
mysqlbackup --host=127.0.0.1 --port= --user=backupuser --password= --compress --backup-dir=/tmp/backup_temp --backup-image=/tmp/full_backup.mbi backup-to-image
经过测试当前环境的压缩比大概是8/1 也就是说备份文件只是之前的1/8大小
14、用一个压缩的备份还原数据库 --uncompress
mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/tmp/full_backup.mbi --backup-dir=/tmp/backup_temp --uncompress copy-back-and-apply-log
15、演示一个全备加增备的还原过程
mysqlbackup --host=127.0.0.1 --port=3306 --user=backupuser --password=1234567890 --backup-image=/tmp/full.mbi --backup-dir=/tmp/backup_temp/ backup-to-image
rm -rf /tmp/backup_temp/*
mysqlbackup --incremental --incremental-base=history:last_backup --backup-dir=/tmp/backup_temp/ --backup-image=/tmp/incremental.mbi backup-to-image
rm -rf /tmp/backup_temp/*
cd /tmp/full/
mysqlbackup --backup-image=/tmp/full.mbi extract
cd /tmp/incremental/
mysqlbackup --backup-image=/tmp/incremental.mbi extract
mysqlbackup --backup-dir=/tmp/full apply-log
mysqlbackup --backup-dir=/tmp/full --incremental-backup-dir=/tmp/incremental apply-incremental-backup
mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/tmp/full copy-back