MySQL数据备份与恢复_MySQL

一.备份数据的方式

MySQL参数说明：http://www.linuxidc.com/Linux/2014-04/99673.htm

MySQL管理之数据备份及恢复http://www.linuxidc.com/Linux/2014-04/99674.htm

1.物理备份 

    1.1冷备 cp tar等 

    1.2热备 mysqlhotcopy    只能备份存储引擎是myisam的表，依赖perl-DBD-MySQL

2.逻辑备份：用mysql自己提供的备份命令或第三放方软件提供的备份命令，备份创建库、表、和产生表记录时的sql语句。

二.数据备份策略：

1.完整备份        备份数据库服务器上的所有库所有表。

2.差异备份        备份自完整备份后所有产生的新数据。

3.增量备份        备份自上一次备份后所有产生的新数据

生产环境一般采用  完整备份+增量备份 或者 完整备份+差异备份

三.逻辑备份与恢复

1.mysqldump命令做数据的完整备份

命令格式：

mysqldump -h数据库服务器ip -u用户名 -p密码  数据库名 > 目录名/备份文件名

数据库名的表示方式：

--all-databases                    备份数据库服务器上的所有库所有表

数据库名                            对某个库做完整备份

数据库名  表                        对指定库里的指定表做完整备份

-B 数据库名1 数据库名2 数据库N      对某几个库做完整备份

#目录名 ：若备份时不写目录名备份文件就存放在，执行备份命令时 所在的目录下

#备份文件名 ：可以自己定义，要有标识性，约定俗成mysql的备份文件都以.sql 结尾

                      若文件重名，上一次备份会被当前备份覆盖。

# 备份用来连接数据库服务器的用户，要有被备份的目标库有相应的权限才可以。

2.恢复完整备份数据

命令格式：

mysql  -h数据库服务器ip  -u用户名  -p密码  数据库名 

mysql  -h数据库服务器ip  -u用户名  -p密码   

# 数据库名：可选项，当备份文件中有 create database,use  sql语句时,恢复数据时可以不指定数据库名。

# 用完整备份文件 恢复数据，只能把数据恢复到备份时的状态，完整备份之后产生的信息数据是无法恢复。

四.使用binlog日志做增量备份与数据恢复

1.mysql日志类型

mysql共有4种类型日志：

binlog日志（二进制日志）  记录除查询之外sql语句。

错误日志                  记录mysql服务在启动和运行过程中产生的错误。

查询日志                  记录执行的所有的sql操作。

慢查询日志                只记录超过指定时间，显示查询结果的sql语句。默认超时时间是10秒。

#默认情况下，mysql只开启错误日志。

#默认情况下日志都存放在数据库目录下。每种日志文件都默认命名方式。

2.启用binlog日志

vim /etc/my.cnf

[mysqld]

log_bin=/binlogdir/filename        //设置存放binlog目录和文件名,自定义目录必须让mysql用户有写入权限，目录不设置则默认在数据库目录/var/lib/mysql/下，文件名不设置则默认为：主机名-bin.000001                                   

max-binlog-size=100M                //设置binlog日志文件的大小,不设置则默认为500M自动生成第二个binlog日志文件

service  mysql  restart

# 对所有库操作的使用数据发送改变的sql语句都会写进当前编号最大的binlog日志文件里。

# localhost-bin.index  记录当前已有binlog日志文件

3.分析binlog日志文件内容。

binlog日志记录sql语句的方式：

① 时间方式

② 字符偏移量方式

mysqlbinlog  [选项]  binlog日志文件名

选项：

偏移量

--start-position=100        起始位置

--stop-position=1200        结束位置

时间点：

--start-datetime="YYYY-mm-dd  HH:MM:SS"  起始时间

--stop-datetime="YYYY-mm-dd  HH:MM:SS"    结束时间

# 不加任何选项则默认查看全部日志内容

4.手动生成新的binlog日志文件方式：

① mysql>flush logs;

② #mysql -h数据库服务器IP -u用户名 -p密码 -e "flush logs"

③ #mysqldump -h数据库服务器IP -u用户名 -p密码 --flush-logs 数据库名> /目录名/xxx.sql

④ service  mysql  restart              //一般不用

5.增量备份数据恢复

原理：使用mysqlbinlog提取历史sql操作管道给mysql命令重做

命令格式：

mysqlbinlog  [选项]  binlog日志文件名  |  mysql  -h数据库服务器ip  -u用户名  -p密码  数据库名

批量恢复增量备份：

mysqlbinlog  `cat localhost-bin.index`| mysql -h数据库服务器ip -u用户名 -p密码  [数据库名]

6.清理binlog日志

删除早于指定编号的binlog日志文件：

mysql> PURGE  MASTER LOGS TO  'binlog日志文件名';

删除所有binlog日志文件，重建新日志：

mysql> RESET  MASTER;

更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2014-05/102266p2.htm