mysql 数据库的备份

一、数据备份

　　1、使用mysqldump命令备份

　　mysqldump命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

　　mysqldump命令的工作原理很简单。它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

　　1、备份一个数据库

　　mysqldump基本语法：

　　mysqldump -u username -p dbname table1 table2 ...-> BackupName.sql

　　其中：

    dbname参数表示数据库的名称；
    table1和table2参数表示需要备份的表的名称，为空则整个数据库备份；
    BackupName.sql参数表设计备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库被分成一个后缀名为sql的文件；

　　使用root用户备份test数据库下的person表

mysqldump -u root -p test person > D:\backup.sql


　其生成的脚本如下：

　　

　　文件的开头会记录MySQL的版本、备份的主机名和数据库名。

　　文件中以“--”开头的都是SQL语言的注释，以"/*!40101"等形式开头的是与MySQL有关的注释。40101是MySQL数据库的版本号，如果MySQL的版本比1.11高，则/*!40101和*/之间的内容就被当做SQL命令来执行，如果比4.1.1低就会被当做注释。

　　2、备份多个数据库

　　语法：

mysqldump -u username -p --databases dbname2 dbname2 > Backup.sql

　　加上了--databases选项，然后后面跟多个数据库

mysqldump -u root -p --databases test mysql > D:\backup.sql

　　3、备份所有数据库

　　mysqldump命令备份所有数据库的语法如下：

mysqldump -u username -p -all-databases > BackupName.sql

　　示例：

mysqldump -u -root -p -all-databases > D:\all.sql

　　2、直接复制整个数据库目录

　　MySQL有一种非常简单的备份方法，就是将MySQL中的数据库文件直接复制出来。这是最简单，速度最快的方法。

不过在此之前，要先将服务器停止，这样才可以保证在复制期间数据库的数据不会发生变化。如果在复制数据库的过程中还有数据写入，就会造成数据不一致。这种情况在开发环境可以，但是在生产环境中很难允许备份服务器。

　　注意：这种方法不适用于InnoDB存储引擎的表，而对于MyISAM存储引擎的表很方便。同时，还原时MySQL的版本最好相同。

　　3、使用mysqlhotcopy工具快速备份

　　一看名字就知道是热备份。因此,mysqlhotcopy支持不停止MySQL服务器备份。而且，mysqlhotcopy的备份方式比mysqldump快。mysqlhotcopy是一个perl脚本，主要在Linux系统下使用。其使用LOCK TABLES、FLUSH TABLES和cp来进行快速备份。

　　原理：先将需要备份的数据库加上一个读锁，然后用FLUSH TABLES将内存中的数据写回到硬盘上的数据库，最后，把需要备份的数据库文件复制到目标目录。

　　命令格式如下：

[root@localhost ~]# mysqlhotcopy [option] dbname1 dbname2 backupDir/

    dbname：数据库名称；
    backupDir：备份到哪个文件夹下；

　　常用选项：

    --help：查看mysqlhotcopy帮助；
    --allowold：如果备份目录下存在相同的备份文件，将旧的备份文件加上_old；
    --keepold：如果备份目录下存在相同的备份文件，不删除旧的备份文件，而是将旧的文件更名；
    --flushlog：本次辈分之后，将对数据库的更新记录到日志中；
    --noindices：只备份数据文件，不备份索引文件；
    --user=用户名：用来指定用户名，可以用-u代替；
    --password=密码：用来指定密码，可以用-p代替。使用-p时，密码与-p之间没有空格；
    --port=端口号：用来指定访问端口，可以用-P代替；
    --socket=socket文件：用来指定socket文件，可以用-S代替；

　　mysqlhotcopy并非mysql自带，需要安装Perl的数据库接口包；下载地址为:http://dev.mysql.com/downloads/dbi.html

　　目前，该工具也仅仅能够备份MyISAM类型的表。
二、数据还原

　　1、还原使用mysqldump命令备份的数据库的语法如下：

　　mysql -u root -p [dbname] < backup.sq

　　示例：

mysql -u root -p < C:\backup.sql

　　2、还原直接复制目录的备份

　　通过这种方式还原时，必须保证两个MySQL数据库的版本号是相同的。MyISAM类型的表有效，对于InnoDB类型的表不可用，InnoDB表的表空间不能直接复制。



　　1.进入MySQL目录下的bin文件夹：e：回车；

e:\>cd mysql\bin? 回车

2.导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名

范例：
mysqldump -uroot -p abc > abc.sql
（导出数据库abc到abc.sql文件）
提示输入密码时，输入该数据库用户名的密码（如果导出单张表的话在数据库名后面输入表名即可）

如果提示locktable错误：则在mysqldump -uroot -p abc后面空一格加上--skip-lock-tables

3、会看到文件news.sql自动生成到bin文件下?

MySQL命令行导入数据库：

1，将要导入的.sql文件移至bin文件下，这样的路径比较方便
2，同上面导出的第1步
3，进入MySQL：mysql -u 用户名 -p
如我输入的命令行:mysql -u root -p?? (输入同样后会让你输入MySQL的密码)
4，在MySQL-Front中新建你要建的数据库，这时是空数据库，如新建一个名为news的目标数据库
5，输入：mysql>use 目标数据库名
如我输入的命令行:mysql>use news;
6，导入文件：mysql>source 导入的文件名;
如我输入的命令行：mysql>source news.sql;

MySQL备份和还原,都是利用mysqldump、mysql和source命令来完成的。
1.Win32下MySQL的备份与还原
1.1 备份
开始菜单 | 运行 | cmd |利用“cd \Program Files\MySQL\MySQL Server 5.0\bin”命令进入bin文件夹 | 利用“mysqldump? -u 用户名 -p databasename >exportfilename”导出数据库到文件，如mysqldump -u root -p voice>voice.sql，然后输入密码即可开始导出。
?
1.2 还原
进入MySQL Command Line Client，输入密码，进入到“mysql>”，输入命令"show databases；"，回车，看看有些什么数据库；建立你要还原的数据库，输入"create database voice；"，回车；切换到刚建立的数据库，输入"use voice；"，回车；导入数据，输入"source voice.sql；"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。
?
2.Linux下MySQL的备份与还原
2.1 备份
[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
[root@localhost mysql]# mysqldump -u root -p voice>voice.sql，输入密码即可。
2.2 还原
法一：
[root@localhost ~]# mysql -u root -p 回车，输入密码，进入MySQL的控制台"mysql>"，同1.2还原。
法二：
[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
[root@localhost mysql]# mysql -u root -p voice<voice.sql，输入密码即可。


mysql备份的三种方式详解

备份的本质就是将数据集另存一个副本，但是原数据会不停的发生变化，所以利用备份只能回复到数据变化之前的数据。那变化之后的呢？所以制定一个好的备份策略很重要

一、备份的目的

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

二、备份需要考虑的问题

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

三、备份的类型

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、利用select into outfile实现数据的备份与还原
1.1把需要备份的数据备份出来

复制代码 代码如下:

mysql> use hellodb;     //打开hellodb库
mysql> select * from students;  查看students的属性
mysql> select * from students where Age > 30 into outfile ‘/tmp/stud.txt' ;   //将年龄大于三十的同学的信息备份出来

注意：

备份的目录路径必须让当前运行mysql服务器的用户mysql具有访问权限

备份完成之后需要把备份的文件从tmp目录复制走，要不就失去备份的目的了

回到tmp目录下查看刚才备份的文件

[root@www ~]# cd /tmp

[root@www tmp]# cat stud.txt

3Xie Yanke53M216

4Ding Dian32M44

6Shi Qing46M5\N

13Tian Boguang33M2\N

25Sun Dasheng100M\N\N

[root@www tmp]#

你会发现是个文本文件。所以不能直接导入数据库了。需要使用load data infile 恢复

回到mysql服务器端，删除年龄大于30的用户，模拟数据被破坏

mysql> delete from students where Age > 30;

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

2、利用mysqldump工具对数据进行备份和还原

mysqldump 常用来做温备，所以我们首先需要对想备份的数据施加读锁，

2.1 施加读锁的方式：

1.直接在备份的时候添加选项

--lock-all-tables 是对要备份的数据库的所有表施加读锁

--lock-table 仅对单张表施加读锁，即使是备份整个数据库，它也是在我们备份某张表的时候才对该表施加读锁，因此适用于备份单张表

2、在服务器端书写命令，

mysql> flush tables with read lock; 施加锁，表示把位于内存上的表统统都同步到磁盘上去，然后施加读锁

mysql> flush tables with read lock;释放读锁

但这对于InnoDB存储引擎来讲，虽然你也能够请求道读锁，但是不代表它的所有数据都已经同步到磁盘上，因此当面对InnoDB的时候，我们要使用mysql> show engine innodb status; 看看InnoDB所有的数据都已经同步到磁盘上去了，才进行备份操作。

2.2备份的策略：

完全备份+增量备份+二进制日志

演示备份的过程;

2.3 先给数据库做完全备份：

复制代码 代码如下:

[root@www ~]# mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql

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

然后回到mysql服务器端，

2.4回到mysql服务器端更新数据

复制代码 代码如下:

mysql> create table tb1(id int); 创建表
mysql> insert into tb1 values (1),(2),(3);  插入数据，这里只做演示，随便插入了几个数据

2.5先查看完全备份文件里边记录的位置：


复制代码 代码如下:
[root@www backup]# cat hellodb_2013-09-08.sql | less
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=15684; 记录了二进制日志的位置

2.6 在回到服务器端：

复制代码 代码如下:

mysql> show master status; 显示此时的二进制日志的位置
从备份文件里边记录的位置到我们此时的位置，即为增量的部分
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      15982 |              |                  |
+------------------+----------+--------------+------------------+

2.7做增量备份

复制代码 代码如下:

[root@www backup]# mysqlbinlog --start-position=15694 --stop-position=15982
/mydata/data/mysql-bin.000013 > /backup/hellodb_`date +$F_%H`.sql

2.8再回到服务器

复制代码 代码如下:

mysql> insert into tb1 values (4),(5); 在插入一些数值
mysql> drop database hellodb;   删除hellodb库

2.9导出这次得二进制日志：


复制代码 代码如下:

[root@www backup]# mysqlbinlog --start-position=15982 /mydata/data/mysql-bin.000013 查看删除操作时二进制日志的位置
[root@www backup]# mysqlbinlog --start-position=15982 --stop-position=16176 /mydata/data/mysql-bin.000013 > /tmp/hellodb.sql  //导出二进制日志

2.10先让mysql离线

回到服务器端：

复制代码 代码如下:

mysql> set sql_log_bin=0;  关闭二进制日志
mysql> flush logs; 滚动下日志

2.11模拟数据库损坏

复制代码 代码如下:
mysql> drop database hellodb;

2.12开始恢复数据：

复制代码 代码如下:

[root@www ]# mysql < /backup/hellodb_2013-09-08.sql  //导入完全备份文件
[root@www ]# mysql < /backup/hellodb_2013-09-08_05.sql //导入增量备份文件
[root@www ]# mysql< hellodb.sql //导入二进制文件

验证完成，显示结果为我们预想的那样

注：

1、真正在生产环境中，我们应该导出的是整个mysql服务器中的数据，而不是单个库，因此应该使用--all-databases
2、在导出二进制日志的时候，可以直接复制文件即可，但是要注意的是，备份之前滚动下日志。
3、利用lvm快照实现几乎热备的数据备份与恢复

3.1策略：

完全备份+二进制日志；

3.2准备：

注：事务日志必须跟数据文件在同一个LV上；

3.3创建lvm Lvm的创建这里就不多说了，想了解话点击http://www.jb51.net/LINUXjishu/105937.html

3.4 修改mysql主配置文件存放目录内的文件的权限与属主属组，并初始化mysql

复制代码 代码如下:

[root@www ~]# mkdir /mydata/data             //创建数据目录
[root@www ~]# chown mysql:mysql /mydata/data  //改属组属主
[root@www ~]#
[root@www ~]# cd /usr/local/mysql/    //必须站在此目录下     
[root@www mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data  //初始化mysql

3.5修改配置文件：

复制代码 代码如下:

vim /etc/my.cof
datadir=/mydata/data   添加数据目录
sync_binlog = 1  开启此功能

3.6 启动服务

复制代码 代码如下:

[root@www mysql]# service mysqld start
mysql> set session sql_log_bin=0;  关闭二进制日志
mysql> source /backup/all_db_2013-09-08.sql   读取备份文件

3.7回到mysql服务器：

复制代码 代码如下:

mysql> FLUSH TABLES WITH READ LOCK; 请求读锁
注：不要退出，另起一个终端：
mysql> SHOW MASTER STATUS;          查看二进制文件的位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> FLUSH LOGS;  建议滚动下日志。这样备份日志的时候就会很方便了

3.8导出二进制文件，创建个目录单独存放

复制代码 代码如下:

[root@www ~]# mkdir /backup/limian
[root@www ~]# mysql -e 'show master status;' > /backup/limian/binlog.txt
[root@www ~]#

3.9为数据所在的卷创建快照：

复制代码 代码如下:
[root@www ~]# lvcreate -L 100M -s -p r -n mysql_snap /dev/myvg/mydata

回到服务器端，释放读锁

复制代码 代码如下:

mysql> UNLOCK TABLES;
[root@www ~]# mount /dev/myvg/mysql_snap /mnt/data
[root@www data]# cp * /backup/limian/
[root@www data]#lvremove /dev/myvg/mylv_snap

3.10更新数据库的数据，并删除数据目录先的数据文件，模拟数据库损坏

复制代码 代码如下:

mysql>  create table limiantb (id int,name CHAR(10));
mysql> insert into limiantb values (1,'tom');
[root@www data]# mysqlbinlog --start-position=187 mysql-bin.000003 > /backup/limian/binlog.sql
[root@www backup]# cd /mydata/data/
[root@www data]#  rm -rf *
[root@www ~]# cp -a /backup/limian/* /mydata/data/
[root@www data]# chown mysql:mysql *

3.11测试

启动服务

复制代码 代码如下:

[root@www data]# service mysqld start
[root@www data]# mysql 登陆测试
mysql> SHOW DATABASES;
mysql> SET sql_log_bin=0
mysql> source/backup/limian/binlog.sql; #二进制恢复
mysql> SHOW TABLES;         #查看恢复结果
mysql> SET sql_log_bin=1;   #开启二进制日志

注：此方式实现了接近于热备的方式备份数据文件，而且数据文件放在lvm中可以根据数据的大小灵活改变lvm的大小，备份的方式也很简单。

4、基于Xtrabackup做备份恢复

官方站点：www.percona.com

优势：

1、快速可靠的进行完全备份
2、在备份的过程中不会影响到事务
3、支持数据流、网络传输、压缩，所以它可以有效的节约磁盘资源和网络带宽。
4、可以自动备份校验数据的可用性。

安装Xtrabackup

复制代码 代码如下:

[root@www ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.i686.rpm

其最新版的软件可从 http://www.percona.com/software/percona-xtrabackup/ 获得

注意：在备份数据库的时候，我们应该具有权限，但需要注意的是应该给备份数据库时的用户最小的权限，以保证安全性，

4.1前提：

应该确定采用的是单表一个表空间，否则不支持单表的备份与恢复。
在配置文件里边的mysqld段加上

innodb_file_per_table = 1

4.2备份策略
完全备份+增量备份+二进制日志
4.3准备个目录用于存放备份数据

复制代码 代码如下:

[root@www ~]# makdir /innobackup

4.4做完全备份：

复制代码 代码如下:

[root@www ~]# innobackupex --user=root --password=mypass /innobackup/

注：

1、只要在最后一行显示 innobackupex: completed OK!，就说明你的备份是正确的。
2、另外要注意的是每次备份之后，会自动在数据目录下创建一个以当前时间点命名的目录用于存放备份的数据，那我们去看看都有什么

[root@www 2013-09-12_11-03-04]# ls
backup-my.cnf ibdata1 performance_schema xtrabackup_binary xtrabackup_checkpoints
hellodb mysql test xtrabackup_binlog_info xtrabackup_logfile
[root@www 2013-09-12_11-03-04]#
xtrabackup_checkpoints ：备份类型、备份状态和LSN(日志序列号)范围信息；
xtrabackup_binlog_info ：mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
xtrabackup_logfile ：非文本文件，xtrabackup自己的日志文件
xtrabackup_binlog_pos_innodb ：二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
backup-my.cnf ：备份时数据文件中关于mysqld的配置

4.5回到mysql服务器端对数据进行更新操作

复制代码 代码如下:

mysql> use hellodb;
mysql> delete from students where StuID>=24;

4.6增量备份

复制代码 代码如下:

innobackupex --user=root --password=mypass --incremental /innobackup/--incremental-basedir=/innobackup/2013-09-12_11-03-04/
--incremental  指定备份类型
--incremental-basedir= 指定这次增量备份是基于哪一次备份的，这里是完全备份文件,这样可以把增量备份的数据合并到完全备份中去

4.7第二次增量

先去修改数据

复制代码 代码如下:

mysql> insert into students (Name,Age,Gender,ClassID,TeacherID) values ('tom',33,'M',2,4);
innobackupex --user=root --password=mypass --incremental /innobackup/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/
这里只须要把最后的目录改为第一次增量备份的数据目录即可

4.8最后一次对数据更改但是没做增量备份

复制代码 代码如下:
mysql> delete from coc where id=14;

4.9把二进制日志文件备份出来，(因为最后一次修改，没做增量备份，要依赖二进制日志做时间点恢复)

复制代码 代码如下:
[root@www data]# cp mysql-bin.000003 /tmp/

4.10模拟数据库崩溃

复制代码 代码如下:

[root@www data]# service mysqld stop
[root@www data]# rm -rf *

恢复前准备

4.11对完全备份做数据同步

复制代码 代码如下:
[root@www ~]# innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/

4.12对第一次增量做数据同步

复制代码 代码如下:

innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/

4.13对第二次增量做数据同步

复制代码 代码如下:

innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-45-53/
--apply-log 的意义在于把备份时没commit的事务撤销，已经commit的但还在事务日志中的应用到数据库

注：

对于xtrabackup来讲，它是基于事务日志和数据文件备份的，备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据库文件中的事务，还应该对其做预处理，把已提交的事务同步到数据文件，未提交的事务要回滚。因此其备份的数据库，不能立即拿来恢复。

预处理的过程：

首先对完全备份文件只把已提交的事务同步至数据文件，要注意的是有增量的时候，不能对事务做数据回滚，不然你的增量备份就没有效果了。

然后把第一次的增量备份合并到完全备份文件内，

以此类推，把后几次的增量都合并到前一次合并之后的文件中，这样的话，我们只要拿着完全备份+二进制日志，就可以做时间点恢复。

4.14数据恢复

复制代码 代码如下:

[root@www ~]# service mysqld stop
[root@www data]# rm -rf *  模拟数据库崩溃
[root@www ~]# innobackupex --copy-back /innobackup/2013-09-12_11-03-04/
--copy-back数据库恢复，后面跟上备份目录的位置

4.15检测：

复制代码 代码如下:

[root@www ~]# cd /mydata/data/
[root@www data]# chown mysql:mysql *
[root@www data]#service mysqld start

检测结果数据正常。