Mysql主要的备份方式

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

 

 

把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支持多种不同的备份方式，包括物理备份，逻辑备份等方式，通过在生产环境中一般都是采用物理备份和逻辑备份相结合的方式来实现

本文转自    探花无情   51CTO博客，原文链接:http://blog.51cto.com/983865387/1917424