MYSQL的备份和还原
1.备份类型
根据备份时服务器能否在线分为:
热备份:在线备份,读写都不受影响
温备份:只能读不能写
冷备份:关机,离线备份,读写操作均不能进行
根据直接是直接COPY文件还是将数据导出后在进行备份分为:
物理备份:直接复制数据文件
逻辑备份:通常是将数据导出至文本文件中,必要时还原回去
根据备份数据集包含了全部内容还是部分内容分为:
完全备份:备份全部数据
增量备份:仅备份上次完全备份或增量备份以后变化的数据
差异备份:仅备份上次完全备份以来变化的数据,
差异备份比增量备份要多占据硬盘空间,但是管理起来非常方便
2.应该备份的目标:
数据、配置文件、二进制日志、事物日志
3.应该选择什么备份方式:
按道理将离线备份是最安全的,但是服务器必须下线
热备份看起来不错,但是其实现细节非常复杂,因为其可能出现数据的不一至情况
对于MyISAM存储引擎来说最好使用:温备份
对于InnoDB存储引擎来说推荐使用:热备份 xtrabackup,mysqldump
所以综合来说离线备份是最好的,要是能停下来的情况下就优先考虑离线备份
离线备份推荐使用主从服务器的方式来备份,这样做的好处,是可以将从服务器
离线在备份,或者将从服务器改为只读,从而进行温备份
我们应该采用逻辑备份还是物理备份呢?
逻辑备份缺点:逻辑备份需要MYSQL服务器进程参与,所以速度慢,还可能会丢失精度,
逻辑备份优点:逻辑备份由于备份出来的是文本文件所以我们可以对其进行二次处理,可移植能力强,甚至于可以跨MYSQL版本
物理备份缺点:无法进行二次处理,可移植能力弱,备份数据大
物理备份优点:速度快,不会丢失精度
我们应该完全备份还是增量备份还是差异备份呢?
可以使用完全+增量
或者使用完全+差异。
问题是我们多长时间做一次备份呢?
如果数据变化量不是特别的大,而且可以忍受一定的还原时间长度
推荐一周一次完全,每天一次增量
如果数据变化两比较大
推荐一月一次完全备份,每天一次差异
4.备份工具有哪些?
MYSQL自带:
mysqldump:逻辑备份工具、MyISAM(温)、InnoDB(热)
mysqlhotcopy:物理备份工具、温备份工具,几乎冷备;仅适用于MyISAM存储引擎
文件系统工具:
lvm2:逻辑卷管理器的快照:几乎热备,只需要在全局请求一个读锁,就可以进行快照
cp,tar:等复制归档工具:物理备份工具,适用于所有存储引擎,但只能进行冷备份,完全备份,部分备份
专业备份工具
ibbackup:商业工具
xtrabackup:开源备份工具,由Percona提供的支持对InnoDB做热备(物理备份)的工具,同时还支持完全备份、增量备份、差异备份
5.备份工具的选择
1.mysqldump+复制binlog:
mysqldump进行完全备份,然后再复制binlog中指定时间范围内的event进行增量备份
2.lvm2快照+复制binlog:
lvm2快照以后,然后使用cp、tar等工具做物理备份,完全备份, 然后再复制binlog中指定时间范围内的event进行增量备份
3.xtrabackup:
由Percona提供的支持对InnoDB做热备(物理备份)的工具,同时还支持完全备份、增量备份、差异备份
6.备份演示之mysqldump
6.1.备份单个数据库:
mysql>FLUSH TABLES WITH READ LOCK; mysql>FLUSH LOGS mysql>SHOW BINARY LOGS mysql>mysqldump -uroot -p test_db > /root/test_db.sql; mysql>UNLOCK TABLES; 或者: mysql>FLUSH TABLES WITH READ LOCK; mysql>mysqldump -uroot -master-data=2 -p test_db > /root/test_db.sql; 再或者: mysql>mysqldump -uroot -master-data=2 --lock-all-tables --flush-logs -p test_db > /root/test_db.sql; 如果指定库中的表类型均为InnoDB引擎,可以使用--single-transaction启动热备,该参数不要和--lock-all-tables同时使用
6.2.备份多个数据库:
--all-databases //备份所有库
--databases DB_NAME，DB_NAME... //备份指定库
mysql>mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > /root/all.sql
6.3.补充选项:
--events
--routines
--triggers
6.4.及时点还原演示:
mysqldump+复制binlog
mysql>mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/all_demo.sql mysql>PURGE BINARY LOGS TO 'mysql-bin.000011' //将此前的日志文件删除,但是不建议删除,应该做备份 mysql>SHOW BINARY LOGS; mysql>做了一些更改,比如删除增加 mysql>FLUSH LOGS //将修改写入日志文件 shell>cd /data/mysql/ && mysqlbinlog mysql-bin.00001 > /root/mysql-bin.00001.sql //备份二进制增量文件 mysql>又做了一些更改,比如删除增加 mysql>FLUSH LOGS //将修改写入日志文件 shell>cd /data/mysql/ && mysqlbinlog mysql-bin.00002 > /root/mysql-bin.00002.sql //备份二进制增量文件 shell>rm -R ./* //模拟数据损坏 挽救操作: shell>killall mysqld //先杀掉所有mysqld进程 shell>scripts/mysql_install_db --user=mysql --datadir=/data/mysql //初始化数据库 mysql>mysql < /root/all_demo.sql mysql>mysql < /root/mysql-bin.00001.sql //导入第一个增量备份 mysql>mysql < /root/mysql-bin.00002.sql //到日第二个增量备份 还原方法: mysql>CREATE DATABASE new_test_db; mysql>mysql new_test_db < /root/test_db.sql
lvm2快照+复制binlog
1.请求锁定所有表: mysql>FLUSH TABLES WITH READ LOCK; 2.一旦锁定完成后最好滚动一下二进制日志,因为用lvm方式不想mysqldump有--master-data=2选项, 它不会帮我们记录是从哪个二进制文件的哪个位置开始的，所以我们需要手动记录 mysql>FLUSH LOGS; mysql -e 'SHOW MASTER STATUS' > /root/binlog_pos.`date +%F`; //手动记录下二进制日志文件的位置 3.这时候就可以创建快照了 lvcreate -L 指定大小 -n mydata-snap -p r -s /dev/myvg/mydata 4.快照创建完成就可以释放锁了 mysql>UNLOCK TABLES; 5.这时候数据库就回复了读写 6.我们得到快照了以后就可以去备份数据了 mount -r /dev/myvg/mydata-snap /mnt cd /mnt cp -a mysql/ /root/backups 7.备份完成后删除快照卷 8. 制定好策略,通过原卷备份二进制日志