mysql数据库的备份和恢复

mysql数据库的备份和恢复

写太详细了，估计很浪费时间，对MySQL数据库的备份和恢复做个宏观的总结，想初步了解下的可以花两分钟看下。

1. 根据是否需要数据库离线

冷备（cold backup） ：需要关mysql服务，读写请求均不允许状态下进行；

温备（warm backup） ： 服务在线，但 仅支持读请求 ，不允许写请求；

热备（hot backup） ：备份的同时，业务不受影响。

2、根据要备份的 数据集合 的范围

完全备份 ：full backup，备份全部字符集。

增量备份 : incremental backup 上次完全备份或增量备份以来 改变了的数据 ，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。

差异备份 ：differential backup 上次 完全备份以来改变了的数据 。

建议的恢复策略：完全+增量+二进制日志 || 完全+差异+二进制日志

3、根据备份数据或文件

物理备份 ：直接备份数据文件

优点：备份和恢复操作都比较简单，能够 跨mysql的版本 ，恢复速度快，属于 文件系统 级别的

建议：不要假设备份一定可用，要测试 mysql>check tables；检测表是否可用

逻辑备份 : 备份表中的数据和代码

优点：恢复简单、备份的结果为 ASCII文件 ，可以编辑， 与存储引擎无关 ，可以通过网络备份和恢复

缺点：备份或恢复都需要 mysql服务器进程 参与，备份结果占据更多的空间，浮点数可能会丢失精度，还原之后，索引需要重建

4.需要备份的内容：

1、 数据；

2、配置文件；

3、代码：存储过程、存储函数、触发器

4、os相关的配置文件

5、复制相关的配置

6、二进制日志

5.备份与恢复:

5.1 利用select into outfile实现 数据 的备份与还原

mysql> use testdb;     //打开testdb库 

mysql> select * from students;  查看students的属性 

mysql> select * from students where Age > 30 into outfile ‘/tmp/stud.txt' ;   //将年龄大于三十的同学的信息备份出来 

恢复：使用load data infile 恢复

[root@www ~]# cd /tmp

[root@www tmp]# cat stud.txt

mysql> load data infile '/tmp/stud.txt' into table students;

6.逻辑备份和恢复：

6.1 备份为 sql文件 ：

shell>mysqldump -uroot -p --all-database > all.sql

shell>mysqldump -uroot -p database01 > database01.sql

shell>mysqldump -uroot -p database01 tablse01 > table01.sql

shell>mysqldump -uroot -p database01 tablse01 tablse02 > table01_tablse02.sql

6.2 备份为文本文件：所有表逗号分隔的 文本

shell> mysqldump -uroot -T/tmp database01 txt01--fields-terminated-by ','

shell>more txt01.txt

7.备份和恢复流程：

7.1 上午九点备份数据库：

shell > mysqldump -uroot -p -l -F database01 > database001.dmp

-l表示给所有表加锁，-F表示生成一个新的日志文件；

7.2 上午九点半备份完毕，插入数据：

shell>insert into tablse01 values(5,'z5');

shell>insert into tablse01 values(6,'z6');

7.3 上午10点出现故障，需要恢复

shell>mysql -uroot -p database01 < database001.dmp

shell> mysqlbinlog localhost-bin.000015 | mysql -uroot -p database01

8.基于时间点的恢复：

10点误删除，发生故障, 恢复到之前 ：

shell>mysqlbinlog --stop-date="2017-03-31 9:59:59" /var/log/mysql/bin.123456 | mysql -uroot -pmypwd

跳过故障 继续执行后面的语句：

shell>mysqlbinlog --start-date="2017-03-31 10:01:00" /var/log/mysql/bin.123456 | mysql -uroot -pmypwd

在同一个时间点可能执行多条语句，有时候根据位置恢复更好。

9.基于位置的恢复：

shell>mysqlbinlog --start-date="2017-03-31 9:55:00" --stop-date="2017-03-31 10:05:00" /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

从上面的文件里面找出错误语句前后的 位置号， 假设是3683212----3683215

shell>mysqlbinlog --stop-position="3683212" /var/log/mysql/bin.123456 | mysql -uroot -pmypwd

跳过故障 继续执行后面的语句：

shell>mysqlbinlog --start-position="3683215" /var/log/mysql/bin.123456 | mysql -uroot -pmypwd

10.MyISAM存储引擎的热备份

10.1 使用 mysqlhotcopy 工具

shell> mysqlhotcopy db_name [/path/to/new_directory]

10.2 手工锁表 copy

mysql>flush tables for read;

11.InnoDB存储引擎的热备份

编辑启动配置文件my.cnf和用于配置的文件backup-my.cnf

shell> ibbackup /home/peppka/mycnf /home/pekka/backup-my.cnf

日志重做：

shell> ibbackup --apply-log /home/pekka/backup-my.cnf

重启数据库服务：

shell>./bin/mysql_saft --defaults-file=/home/pekka/backup-my.cnf &

利用binlog日志将备份点和故障点之间的剩余数据恢复：

mysqlbinlog binlog-file | mysql -uroot -p***

12.表的导入和导出：

12.1

mysql> select * from students where Age > 30 into outfile ‘/tmp/stud.txt'

mysql> load data infile '/tmp/stud.txt' into table students;

12.2

shell> mysqldump -uroot -p database01 tablse01 > table01.sql

shell > mysqldump -uroot -p -l -F database01 > database001.dmp

shell>mysql -uroot -p database01 < database001.dmp

shell> mysqlimport -uroot database01 /tmp/emp.txt --fields-terminated-by=',' --fields-enclosed-by ' " '

--fields-terminated-by 字段分割符号；

--fields-enclosed-by 字段引用符号