mysql备份恢复案例

冷备份操作：
1、关闭数据库实例(会触发全量检查点进行刷脏操作)；
2、拷贝数据库文件（系统表空间文件-ibdata1，用户表空间文件–.frm 和.ibd），为了保
障数据库的完整性，建议讲整个数据存放全部拷贝。
热备份操作:
逻辑备份：效率高，适用于数据量<100G，常用 mysqldump 工具，将数据库中的数据转
储为 sql 语句，存放到文本文件中。
全库备份语句：
mysqldump -uroot -pOf123456 --master-data=2 --single-transaction -A > all.sql
–master-data=1 或者 2 可以在备份集中标记 binlogfile 编号以及 pos（当恢复到备份
点之后下次全备之前的数据时，需要恢复备份的数据加 pos 点之后的 binlog 文件的信
息），但是会进行锁表操作，将无法修改只能查询。
master-data=1 表示在备份集中添加 chang master 命令，备份集恢复时，该命令会被执
行。
master-data=2 表示在备份集中添加 chang master 命令，仅以注释的形式存在，恢复时
该命令不会被执行。
–single-transaction 使用单个事务备份，才能保障数据库备份的一致性（统一备份某个
时间点的数据，不会因为事务的变化而影响备份数据的变化）。通过 mvcc 保证数据一致
性避免了锁机制。使用时会有一瞬间锁定一下获取 binlog，然后再释放。使用该参数时，
不论数据库的隔离级别是怎样的，都将改变为 RR 的隔离级别(RR 级别不会发生幻读和不
可重复读)。查看最新一个 binlog 日志的编号名称，及其最后一个操作事件结束点：
show master status;
查看 binlog 存放位置：
show variables like ‘%log_bin%’;
mysqldump 的备份恢复都是单线程无法并发并发。
mysqldump 为逻辑备份，需要将查询到的数据改写为 sql 语句，导致备份和恢复较慢(主
要是因为 mysql 是按照索引组织表插入，所以插入速度慢)。
备份最好是在本地备份，再将生成的备份集传到备份服务器，使用 shell 写脚本，通过
crontab 执行(配置完 crontab 需要针对新创建的定时任务进行 restart 一下才能生效)。
物理备份：效率高，适用于数据量>100G，常用 xtrabackup 工具，是直接对数据库底层
的数据文件进行的备份操作。
备份原理：首先备份当前备份的数据块，然后将备份期间产生的 redo log 重新应用到备
份出来的数据块上，保证数据块中的数据具有一致性。最后进行恢复。
xtrabackup 包含有两个工具：
（1）xtrabackup 只能备份 innodb 和 xtradb 两种引擎的表，而不能备份 myisam 引擎
的表；
（2）innobackupex 是一个封装了 xtrabackup 的 Perl 脚本，支持同时备份 innodb 和
myisam，但在对 myisam 备份时需要加一个全局的读锁。 还有就是 myisam 不支持增量备份。
xtrabackup 支持：
全量备份：建议每周一次全备
增量备份：增量是在上一次的全备或增量基础上进行增量备份。
完整增备恢复流程：
1 步备份的时候：
全备 -->增备 1（基于全备）–>增备 2（基于增备 1）–>增备 3（基于增备
2）.。。。。。。
2 步应用日志：
对全备应用日志
–apply-log --redo-only
将增备 1 应用到全备上；
将增备 2 应用到全备上；
将增备 3 应用到全备上；
对全备进行真正应用日志：
–apply-log
3 步恢复：
将全备拷贝到数据目录即可(已经包含了增备的内容)
查看 mysql 使用的套接字：netstat -ln | grep mysql
查看当前使用的 binlog 和 pos:
show master status;
使用 mysqldump 备份恢复
1、备份
导出某几个库
/usr/local/mysql/bin/mysqldump -uroot -p -S /tmp/mysql3306.sock --singletransaction -B test1
test2 > all.sql
或全库备份语句：
mysqldump -uroot -pOf123456 --master-data=2 --single-transaction -A > all.sql
或备份指定的 mysql 和 test 数据库的语句（包含建库和建表语句）：
mysqldump -uroot -pOf123456 --master-data=2 --single-transaction -B mysql
test > all_test.sql
或备份指定的 test 数据库下的 t 表的语句（包含建表语句）：
mysqldump -uroot -pOf123456 --master-data=2 --single-transaction test t>
test_t.sql
-A 指全库
-u 指定用户名
-p 指定密码
-S 指定套接字文件-h 指定主机
-P(大 P) 指定端口
-B 指定备份的数据库
–add-drop-database 在备份集中添加删库语句，当在恢复时在建库前进行删库，规避报
错风险。
–add-drop-table 在备份集中添加删表语句，当在恢复时在建表前进行删表，规避报错风
险。
–replace 恢复时如果存在就替换，不存在就插入。
2、恢复数据库(定点恢复)：
/usr/local/mysql/bin/mysql -uroot -proot -S /tmp/mysql3306.sock < /tmp/all.sql
或登陆数据库后执行：
source /bak/all.sql
3、应用 binlog 方法一，第一步：
进入/usr/local/mysql/bin
./mysqlbinlog /usr/local/mysql/data/binlog.000018 > /bak/binlog.sql
./mysqlbinlog /usr/local/mysql/data/binlog.000019 >> /bak/binlog.sql
应用 binlog 方法一、第二步：
source /bak/binlog.sql; 应用 binglog 文本文件。
----------------xtrabackup 增量备份恢复
一、安装 xtrabackup
安装依赖包
yum install perl-DBD-MySQL
yum install perl-DBI
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
rpm -ivh libev-4.15-3.el7.x86_64.rpm
安装 xtrabackup
rpm -ivh percona-xtrabackup-24-2.4.8-1.el6.x86_64.rpm
二、全备
全备
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --password
Of123456 /bak/all.bak
增备 1
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --password
Of123456 --incremental --incremental-basedir=/bak/all.bak /bak/inc-1.bak
增备 2
innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --password
Of123456 --incremental --incremental-basedir=/bak/inc-1.bak /bak/inc-2.bak–no-timestamp 不会自动创建以时间戳命名的目录
–/data/xtrabackup/all-20170417.bak 该命令会自动创建备份集的目录。
三、全量热备或者增备恢复
3.1、关闭 mysql
mysqladmin shutdown -uroot -p
或者 service mysql stop
3.2 清空 mysql 数据库中 data 文件目录：
tar -czvf /bak/back.tar.gz *
3.3、针对全备应用日志：
innobackupex --defaults-file=/etc/my.cnf --user root --password Of123456 –
apply-log --redo-only /bak/all.bak
3.4、应用增量备份到全量备份集(如果没有增备请跳过该步骤)
innobackupex --defaults-file=/etc/my.cnf --user root --password Of123456 –
apply-log --redo-only /bak/all.bak --incremental-dir=/bak/inc-1.bak
innobackupex --defaults-file=/etc/my.cnf --user root --password Of123456 –
apply-log --redo-only /bak/all.bak --incremental-dir=/bak/inc-2.bak
–将增量的备份 inc-0001.bak 应用到全备文件 all.bak 里。–apply-log 应用日志
–redo-log 只应用 redo log，不进行回滚。
–incremental-dir=/bak/inc-2.bak 针对前面的全量进行应用增量，并指定增量目录
3.5 应用日志到全备，此步骤针对没有提交的数据进行回滚
innobackupex --defaults-file=/etc/my.cnf --user root --password Of123456 –
apply-log /bak/all.bak
3.6、将备份集文件拷贝到 mysql 数据文件目录中，并授权新数据文件：
cp -r /bak/all.bak /usr/local/mysql/data
chown -R mysql:mysql /usr/local/mysql/data
3.7、开启 mysql：
service mysqld start