mysql备份实例方案

231211

在线备份如何保障备份的数据的一致性，使用逻辑卷快照功能来实现，先锁几秒


把blog日志重定向到一个文件里，再用source导入到数据库里，因为每操作一步都会产生新的blog日志
物理备份：直接复制数据库文件，适用于大型数据库环境，一般结构lvm snapshot的方式实现，缺点是不能恢复到异构系统中如Windows中
逻辑备份备份的是建表，建库，插入等操作所执行的sql语句，适用于中小型数据库，效率比较低

Mysqldump实现逻辑备份
语法
#mysqldump -h 服务器 -u用户名  -p密码 数据库名 > 备份文件.sql

数据库名的选项
--all -databases   所有库
School   数据库名
School stu_info t1 school数据库的表stu_info t1
--databases bbs test mysql 多个数据库

实例：
单库备份：
#mysqldump -uroot -p123 bbs >bbs.sql
#mysqldump -uroot -p123 bbs table1 table 2 > bbs table1 table2.sql

多库备份
#mysqldump -uroot -p123 -databases bbs test mysql > bbs_test_mysql,sql

备份所有库
#mysqldump -uroot -p123 --all-databases > all.sql

#mysqldump -uroot -p123 --all-databses --lock-all-tables > /tmp/all2.sql

备份/恢复案例：情况1数据库损坏了，等于是机器都坏了
操作步骤：
备份
1#mysqldump -uroot -p123 --all-databases --lock-all-tables>/backup/`date+%F`_all.sql
2mysql -uroot -p123 -e ‘flush logs’   //截断并产生新的binlog
3插入数据   //模拟服务器正常运行
4mysql>set sql_log_bin=0;
Mysql>drop database bbs   //模拟服务器损坏

恢复：（干净的环境，新机器）
1mysql>set sql_log_bin=0;
Mysql>source /backup/2014-02-13 all.sql            //恢复最近一次完全备份
2mysqlbinlog 最后一个binlog |mysql -uroot -p 123     //恢复最后一个binlog文件


情况2如果有误删除
备份
1#mysqldump -uroot -p123 --all-databases --lock-all-tables>/backup/`date+%F`_all.sql
2mysql -uroot -p123 -e ‘flush logs’   //截断并产生新的binlog
3插入数据   //模拟服务器正常运行
4Mysql>drop table db1.t1  //模拟误删除

恢复：（干净的环境，新机器）
1mysql>set sql_log_bin=0;
Mysql>source /backup/2014-02-13 all.sql            //恢复最近一次完全备份
2mysqlbinlog 最后一个binlog  --stop-position=260|mysql -uroot -p 123  
mysqlbinlog 最后一个binlog  --start-position=500|mysql -uroot -p 123    这种情况下就需要用到blog字符截取来恢复


备份/恢复注意事项：
1）完全备份到一个干净的环境（例如新的数据库或删除原有的数据库）
2）恢复期间所有sql语句不应该记录到binlog中
3）单库恢复前需要手动创建数据库
实现自动化备份1）写脚本
Vim /mysql_back.sql
#!/bin/Bash
#The mysql_back
# Author: luliechu （E-mail: 983765387@qq.com）
Back_dir=/backup
Back_file=`date+%F`_all.sql
User=root
Pass=123
If[! -d /backup];
Then
Mkdir -p /backup
Fi

#备份并切断日志
Mysqldump -u${user} -p${pass} -- events --all-databases --lock-all-tables>${back_dir}/${back_file}
Mysql  -u${user}  -p${pass} -e ‘flush’ logs

#只保留最近一周的备份
Cd $back_dir
Find -mtime +7 -exec rm -rf {}\;


2）测试
Chmod a+x /mysql_back.sql //給脚本执行权限
Chattr +i /mysql_back.sql   //为了安全给这脚本文件上锁，禁止任何人做改动
/mysql_back.sql    //执行脚本
3）配置定时任务到生产环境中
Crontab  -l
0 2 * * * /mysql_back.sql   //每天凌晨2点执行脚本
导出表将表导入到文本文件中
表的导出和导入
Select ....into outfile 导出文本文件
实例：
Mysql>select * from school.student1
Into outfile ‘student1.txt’
Fields terminated by `,`   //定义字段分割符
Optinon
Ally enclosed by `’’` 定义字符串使用什么符号括起来
Lines terminated by ‘\n’  //定义换行符


数据库迁移注意要尽量是相同版本之间迁移

Mysqldump -h 迁移源ip -uroot -p123 --databases bbs |mysql -h 目标ip  -uroot
-p456
//迁移源为本机，不用申请
总结：mysql支持多种不同的备份方式，包括物理备份，逻辑备份等方式，通过在生产环境中一般都是采用物理备份和逻辑备份相结合的方式来实现