mysql备份

热备： 

数据库 mysqldump -u root -p  database>  yourname.sql 

单表    mysqldump -u root -p  db table > yourname.sql 

恢复： mysql < yourname.sql  

实操请从第五部分开始阅读~

一、备份的目的

做灾难恢复：对损坏的数据进行恢复和还原
需求改变：因需求改变而需要把数据还原到改变以前
测试：测试新功能是否可用

二、备份需要考虑的问题

可以容忍丢失多长时间的数据；
恢复数据要在多长时间内完； 
恢复的时候是否需要持续提供服务；
恢复的对象，是整个库，多个表，还是单个库，单个表。

三、备份的类型

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

冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；
热备（hot backup）：备份的同时，业务不受影响。

注：

1、这种类型的备份，取决于业务的需求，而不是备份工具
2、MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具

2、根据要备份的数据集合的范围
完全备份：full backup，备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。
差异备份：differential backup 上次完全备份以来改变了的数据。
建议的恢复策略：
完全+增量+二进制日志
完全+差异+二进制日志

3、根据备份数据或文件

物理备份：直接备份数据文件。 为冷备份，必须先关闭数据库然后拷贝文件，否则恢复失败

优点：

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

建议：

不要假设备份一定可用，要测试
mysql>check tables；检测表是否可用
逻辑备份: 备份表中的数据和代码

优点：

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

缺点：

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

四：备份的对象

1、 数据；
2、配置文件；
3、代码：存储过程、存储函数、触发器
4、os相关的配置文件
5、复制相关的配置
6、二进制日志

  设计备份方案：

1、物理备份是必须的。对于大型数据库，逻辑备份太慢，资源限制，恢复时间长。

2、保留多个备份集

3、定期从备份中抽取数据恢复测试

4、保存二进制日志用于基于时间点故障恢复

五、备份和恢复的实现

5.1 物理备份：

If you can shut down your MySQL server, you can make a binary backup that consists of all files used by InnoDB to manage its tables. Use the following procedure:
1、Do a slow shutdown of the MySQL server and make sure that it stops without errors.
2、Copy all InnoDB data files (ibdata files and .ibd files) into a safe place.
3、Copy all the .frm files for InnoDB tables to a safe place.
4、Copy all InnoDB log files (ib_logfile files) to a safe place.
5、Copy your my.cnf configuration file or files to a safe place

来源： <https://dev.mysql.com/doc/refman/5.5/en/innodb-backup.html>

常见问题：

启动时提示pid问题，查看err日志

1、数据库没有正常关闭，就拷贝了ibd等文件，报错，正常关闭以后再拷

2、将ibd等文件拷贝到另外服务器上的mysql时，新建database 是root用户权限，记得修改权限

chown -R mysql:myql newpath

5.2 逻辑备份（全量+增量）：

 1、首先，我们需要开启MySQL服务器的二进制日志功能，其实现方法有很多种，最常用的是在MySQL的配置文件的mysqld项中加入log-bin=[filepath]项；也可以使用mysqld –log-bin=[filepath]重新启动MySQL服务器。

2、其次，使用mysqldump对数据库进行完全备份，它可以实现对数据据的联机，非阻塞的热备份，不会影响其他进程对数据库的读写操作。

（ 参考指令：mysqldump -uroot --password=123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > d:/mysql5.0/full_%date:~0,10%_1500_PM.sql）

--single-transaction: 基于此选项能实现热备InnoDB表；因此，不需要同时使用--lock-all-tables； 
--master-data=2  记录备份那一时刻的二进制日志的位置，并且注释掉，1是不注释的 
--databases hellodb 指定备份的数据库

  命令行下具体用法如下：  mysqldump -u用戶名 -p密码 -d 數據库名 表名 脚本名;

    1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）
    mysqldump -uroot -pdbpasswd -d dbname >db.sql;

    2、导出數據库為dbname某张表(test)结构
    mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

    3、导出數據库為dbname所有表结构及表數據（不加-d）
    mysqldump -uroot -pdbpasswd  dbname >db.sql;

    4、导出數據库為dbname某张表(test)结构及表數據（不加-d）
    mysqldump -uroot -pdbpasswd dbname test>db.sql;

来源： <http://www.cnblogs.com/yuanyouqi/archive/2010/04/28/1722738.html>

 

从meteo数据库的sdata表中导出sensorid=11 且 fieldid=0的数据到 /home/xyx/Temp.sql 这个文件中

mysqldump -uroot -p123456 meteo sdata --where=" sensorid=11 and fieldid=0" > /home/xyx/Temp.sql

3、第三，使用flush logs指令刷新创建新的二进制日志。可以通过mysqladmin flush logs或者flush logs语句实现。最方便有效的方法是将它做成批处理文件，然后让操作系统定期执行。

4、使用mysql < *.sql进行完全备份的恢复。


5、使用mysqlbinlog logs-bin.[0-9]* | mysql进行增量备份的恢复。一般的，假设我们周日下午三点进行了完全备份并生成备份文件full_backup_20100415_3_PM.sql，周一周二中午一点进行了两次增量备份，分别生成增量备份文件inc_backup_1_PM.0007和inc_backup_1_PM.0008，周三上午10：00数据库发生崩溃，需要进行数据库恢复，此时正在记录的二进制日志为inc_backup_1_PM.0009，我们还需要恢复其中记录的操作。具体的恢复操作为：
mysql < full_backup_20100415_3_PM.sql
mysqlbinlog inc_backup_1_PM.0007 inc_backup_1_PM.0008 inc_backup_1_PM.0009

5.3 结合Linux的cron命令实现定时备份

比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式，那么可在/etc/crontab配置文件中加入下面代码行：

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date '+%m-%d-%Y'`.sql.gz

References:

http://www.jb51.net/article/41570.htm

http://blog.csdn.net/hijiankang/article/details/10999501

http://www.jb51.net/article/45023.htm