Mysql备份与恢复

mysqldump --help

下面举出几个常用的命令：

为了导出所有数据库，需要用--all-databases选项

shell > mysqldump --all-databases > dump.sql

示范命令：在shell中真正使用这条语句时是下面这样的（以日期的格式命名，

-uroot指的是以root的身份登录，-p‘123456‘指的是密码，注意不能有空格，后面的命令就不再一一示范了）：

[root@localhost ~]# mysqldump --all-databases -uroot -p‘123456‘ > /server/backup/bak_$(date +%F).sql

为了导出指定的数据库（一个或多个数据库），需要用--databases选项（或者-B）

shell > mysqldump --databases db1 db2 db3 > dump.sql

而导出指定数据库的哪一张表，则忽略掉--databases选项（db1数据库中的table1和table2）

shell > mysqldump db1 table1 table2> dump.sql

在导出单个指定的数据库时，除了像上面一样使用--databases以外，还可以这样：

shell > mysqldump db1 > dump.sql

但是，忽略了--databases选项后，需要注意这点：

输出文件中没有CREATE DATABASE和USE语句，所以当你重新导入这个导出文件时，你必须指定一个默认的数据库（或者自己创建一个数据库）；与此同时，你也可以把这个导出文件导入到另一个数据库上

导入SQL语句形式的文件

当使用上面的mysqldump语句导出文件的时候使用到了--all-databases或者

--databases选项后，文件中已经含有了CREATE DATABASE和USE语句，所以不需要再指定一个数据库，可以直接读入该文件

shell > mysql < dump.sql

示范语句：

[root@localhost ~]# mysql -uroot -p‘123456‘ </server/backup/dump.sql

或者，先登录mysql，在mysql中进行导入

mysql > source /server/backup/dump.sql;

但是，如果导出文件中不包含CREATE DATABASE和USE语句，此时就要手动创建数据，再读入该文件。

shell > mysqladmin create db1

示范语句：

[root@localhost backup]# mysqladmin -uroot -p‘123456‘ create db_test;

然后在导入文件时指定数据库名字：

shell > mysql db1 < dump.sql;

或者先登录mysql，然后导入文件

mysql > create database if not exists db1;

mysql > use db1;

mysql > source dump.sql;

 

以分隔符格式导出数据库

在使用mysqldump命令时，如果加上了--tab=dir_name选项，就会在dir_name目录为数据库中的每个表生成两个文件，以txt为后缀的文件用来存储数据，以sql为后缀的文件用来存储CREATE TABLE的建表语句，下面的语句用来导出db1数据库的内容到/tmp目录下

shell > mysqldump --tab=/tmp db1

示范语句：

[root@localhost backup]# mysqldump -uroot -p‘123456‘ --tab=/server/backup/tmp/ kylin_default

注意：如果看到了下面所示的错误

mysqldump: Got error: 1: Can‘t create/write to file ‘/server/backup/tmp/course.txt‘ (Errcode: 13 - Permission denied) when executing ‘SELECT INTO OUTFILE‘

是因为/server/backup/tmp目录的权限问题，导致mysql不能写入到该目录下，解决方法就是授权该目录

[root@localhost backup]# chmod a+rwx tmp/

当使用--tab选项时，最好只导出本地的mysql服务器的数据库，如果要导出远程的数据库，那么导出的目录必须同时存在于两个服务器中，并且txt文件会写入到远程的服务器的目录中，而sql文件会写入到本地服务器目录中

可以通过选项自定义txt文件的导出格式：

1. --fields-terminated-by=str 在txt文件中用来分隔每一列的内容（默认是tab）
2. --fields-enclosed-by=char  在txt文件中用来封闭每一个值的内容（例如用双引号把所有的值括起来，默认为空）
3. --fields-optionally-enclosed-by=char 在txt文件中用来封闭非数值的内容（默认为空）
4. --fields-escaped-by=char 忽略特定的字符
5. --lines-terminated-by=str 每一行的行终止符（默认是换行符）

譬如，导出的文件中，用逗号分隔每一列的值，并且用双引号括起来，在行末用\r\n（windows环境下的换行符）作为终止符

[root@localhost tmp]# mysqldump -uroot -p‘123456‘ --tab=/server/backup/tmp/ --fields-terminated-by=, --fields-enclosed-by=‘"‘ --lines-terminated-by=0x0d0a kylin_default

输出格式有前面的图变为下面这个

导入分隔符格式的文件

先导入sql后缀的文件，再导入txt文件

shell > mysql db1 < t1.sql

shell > mysqlimport db1 t1.txt

示范语句：

[root@localhost tmp]# mysql -uroot -p‘123456‘ kylin_default < test.sql

[root@localhost tmp]# mysqlimport -uroot -p‘123456‘ kylin_default /server/backup/tmp/test.txt;

或者登陆mysql后操作

mysql > use db1;

mysql > load data infile ‘t1.txt’ into table t1;

注意：如果你导出数据库时使用了自定义的格式，在导入该文件到数据库时也必须指定同样的格式，否则会发生错误

如：

shell > mysqlimport --fields-terminated-by=, --fields-enclosed-by=‘"‘ --lines-terminated-by=0x0d0a db1 t1.txt

或者

mysql > use db1;

mysql > load data infile ‘t1.txt’ into table t1

> fields terminated by ‘,‘ fields enclosed by ‘ " ‘

> lines terminated by ‘\r\b‘ ;

 

mysqldump技巧

复制一个数据库的内容到另一个数据库

shell > mysqldump db1 > dump.sql

shell > mysqladmin create db2

shell > mysql db2 < dump.sql

在使用mysqldump命令时只要不加上--databases选项，就会使得导出的SQL文件中没有USE db1语句，从而使得该文件可以导入到另一个不同名的数据库中

 

复制一个数据库到另一台主机上

在服务器1上执行：

shell > mysqldump --databases db1 > dump.sql

然后把dump.sql文件拷贝到服务器2上，在服务器2上执行：

shell > mysql < dump.sql

在使用mysqldump命令时加上--databases选项，就会使得导出的SQL文件中包含有CREATE DATABASE和USE db1语句，所以可以在没有db1的服务器上直接导入该文件（因为会自动生成db1）

当然，也可以不使用--databases选项，只要在服务器2上手动创建一个数据库，然后往该数据库导入该文件即可

 

导出已经存储的程序

mysqldump还可以处理存储的程序（stored programs）（存储进程，函数，触发器和事件(events)）：

1. --events：Dump Event Scheduler events
2. --routines: Dump stored procedures and functions
3. --triggers: Dump triggers for tables

--triggers选项是默认执行的，而其余两个选项是要自己手动添加的，如果要明确禁止导出这三个程序，就使用选项：--skip-events, --skip-routines, --skip-triggers

 

分开导出数据库表的定义和数据内容

使用--no-data选项可以告诉mysqldump只导出数据库的表定义语句，而不导出数据内容

shell > mysqldump --no-data test > dump-defs.sql

示范语句：

[root@localhost backup]# mysqldump -uroot -p‘123456‘ --no-data kylin_default > /server/backup/no_data.sql

可以看到该文件只包含了创建表的语句

使用--no-create-info选项可以告诉mysqldump只导出数据库的数据操作语句，而不导出数据库的表定义

shell > mysqldump --no-create-info test > dump-data.sql

示范语句：

[root@localhost backup]# mysqldump -uroot -p‘123456‘ --no-create-info kylin_default > /server/backup/no_create_into.sql

可以看到该文件只包含了操作数据（这里仅仅是插入数据）的内容

 

使用mysqldump来测试升级数据库的兼容性

当打算升级mysql服务器时，应当首先测试新版本数据库。可以把旧服务器的数据导入到新服务器中，测试新版本的服务器是否能正确处理数据。

在旧的服务器中执行：

shell > mysqldump --all-databases --no-data --routines --events > dump-defs.sql

在新的服务器中执行

shell > mysql < dump-defs.sql

因为导出的文件中没有数据，所以可以快速执行，你应当留意在执行过程中出现的所有警告和错误

在你确认了在新服务器中已经正确生成了数据库，此时就可以往新服务器中导入数据

在旧的服务器中执行：

shell > mysqldump --all-databases --no-create-info > dump-data.sql

在新的服务器中执行

shell > mysql < dump-data.sql

然后检查数据内容和运行一些测试程序

 

增量备份与时间点恢复

时间点恢复指的是首先指定一个时间点，然后让mysql服务器执行该时间点之后的全部操作。通常，时间点恢复会在执行了一个全备份之后才能运行，因为在完成全备份的时候，服务器会记录当时的状态和时间（换个角度想，有a，b两个数据库，b首先全备份了a的数据库，然后a的数据库的内容发生了改变，这时候b数据库只要执行a数据改变的语句就能保持和a的一致）

操作日志文件

时间点恢复的要点：

时间点恢复需要用到一个二进制的日志文件，该文件是在执行全备份之后生成的，因此，服务器必须先启用--long-bin配置选项才能生成二进制文件。服务器默认会在数据存储的目录存放该日志，当然你可以指定另一个存放目录

修改你的my.cnf文件，在[mysqd]模块下增加内容：log-bin=mysql-bin，然后重启数据库。然后马上执行一次全备份，因为日志文件是记录全备份之后的SQL语句，如果不在每一次导入日志文件前先导入全备份的数据库，就会造成错误（譬如对于一条插入语句，如果直接导入日志文件，就会提示ERROR 1062 (23000) at line XX: Duplicate entry ‘X‘ for key ‘PRIMARY‘，因为这条数据在导入日志文件前就已经存在）。

所以，要测试下面的文件，每一次导入日志文件前，都必须先导入备份的数据库

登陆mysql，使用命令

mysql > show bianry logs;

可以看到二进制日志的列表（由于我清理了全部的日志，所以现在只有一个日志）

判断当前的二进制文件

mysql > show master status \G

该二进制文件存放在mysql的数据目录中，如果直接查看该文件，会显示乱码。要使用mysqlbinlog命令才能正确显示该文件。

[root@localhost data]# mysqlbinlog mysql-bin.000001

我截取了该文件的最后一部分，留意上面箭头所指的时间，表明数据库在2015年6月30日 15:47:28分执行了三条语句：

COMMIT；

DELIMITER；

ROLLBACK；

然后我登陆数据库，插入了一条数据，再查看这个日志内容：

可以看到，日志文件增长了， 并在15:48:37产生了一条插入语句，而这条语句就是我使用的语句。因此可见，这个二进制日志完全记录了对数据库的所有操作。

 

使用mysqlbinlog命令来执行日志文件中的SQL语句

shell > mysqlbinlog binlog_files | mysql -u root -p

我在数据库里不小心写了一条删除语句，我现在要恢复执行这条语句之前的数据库状态，该怎样做呢？

首先，把日志文件内容另存为一个可阅读的文件

[root@localhost data]# mysqlbinlog mysql-bin.000002 > tmpfile

[root@localhost data]# vi tmpfile

然后找到该文件中的那条删除数据的语句并删除该语句

在上图中可以看到在2015年6月30日15:49:38执行了一条delete语句，现在删除该语句，保存并退出

记住：首先导入之前的备份数据库，然后再把这个文件导入到mysql服务器中

shell > mysql < lastest-backup.sql

shell > mysql -uroot -p < tmpfile

就可以看到被删除的数据出现了

如果要导入多个二进制日志，最好不要单独到导入每一个日志，譬如要导入两个日志。

shell > mysqlbinlog mysql-bin.000001 | mysql -u root -p

shell > mysqlbinlog mysql-bin.000002 | mysql -u root -p

上面的做法是有风险的，假如第一个日志中含有CREATE TABLE语句，而第二个日志中含有USE语句，当第一个日志导入进程失败时也会导致第二个日志导入失败（因为建表语句执行失败，所以导入第二个日志时就没有表）

为了避免出现这种错误，可以把多个日志一次性导入

shell > mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

另一个方法如下

shell > mysqlbinlog mysql-bin.000001 > /tmp/tmpfile

shell > mysqlbinlog mysql-bin.000002 >> /tmp/tmpfile

shell > mysql -uroot -p -e “source /tmp/tmpfile;”

使用-e选项可以不登陆数据库，直接运行双引号里的语句

使用时间点来恢复数据

使用日志文件可以很明确地指出把数据恢复到哪个时间点。正如我上面举的例子，在上图中可以看到在2015年6月30日15:49:38执行了一条delete语句，但是我当时并没有发现，在我又执行了很多语句之后才发现了这个巨坑。此时，我该如何挽救呢？

1. 首先，我要先导入之前备份的最新的数据库

shell > mysql < lastest-backup.sql

2. 把日志文件导出到一个可以阅读的文件中

shell > mysqlbinlog mysql-bin.000002 > /tmp/tmpfile

3. 打开该文件，找到delete语句执行时候的时间点（如上一张图所示）

4. 然后在导入日志文件时，只执行到2015年6月30日15:49:38之前的语句

shell > mysqlbinlog --stop-datetime="2015-06-30 15:49:37" 
      mysql-bin.000002 | mysql -u root -p

注意，--stop-datetime要指向前一个时间

5. 再一次导入日志文件，这次只执行从2015年6月30日15:49:38之后的语句

shell > mysqlbinlog --start-datetime="2015-06-30 15:50:11" 
      mysql-bin.000002 | mysql -u root -p

注意，--start-datetime要指向后一个时间

注意：

1. 其实删除该日志文件的那条delete语句，然后导入到mysql中会更简单，不过这里为了演示如何指定使用时间点来恢复数据
2. 指定时间点来恢复数据其实并不好用，因为可能会有多条语句在同一个时间里执行，这个方法就完全不管用了，可以参考下面使用事件点来恢复数据。

使用事件点来恢复数据

留意上图所指的at 508，这个文件中的所有跟在at后面的数字就是事件点，这个数字是递增且唯一的。

所以，先恢复到这个事件点之前

shell > mysqlbinlog --stop-position=411 
      mysql-bin.000001 | mysql -u root -p

同样地，往前移一个点

再恢复从这个事件点之后的数据

shell > mysqlbinlog --start-position=627 
      mysql-bin.000001 | mysql -u root -p

往后移一个时间点

 

自动化备份

自动执行备份脚本

写好备份脚本后，通过把脚本添加到contrab中就能实现自动备份。

1. 确认crond服务正在运行

[root@FirstCentOS backup]# service crond status

如果没有启动，则使用命令启动：

[root@FirstCentOS backup]# service crond start

2. 然后把脚本添加到contrab中（每天01:00自动备份）

[root@FirstCentOS backup]# crontab -e

#backup all databases in mysql on 01:00 everyday by root

00 01 * * * /bin/sh /server/backup/backup_automatically.sh >/dev/null 2>&1

把上面那行的内容添加进去，然后保存退出即可

 

上传到ftp

我们每天执行一次备份，时间长了之后就会产生大量的文件，浪费磁盘空间，所以可以在每天备份的时候就自动把备份文件上传到ftp上，然后删除本地磁盘的文件，就能节省本地磁盘的空间。

1. 我是参考这篇文章在windows上搭建的ftp服务器：http://jingyan.baidu.com/article/63f23628f04e420209ab3d70.html

2. 然后在linux上安装ftp

[root@FirstCentOS backup]# yum install ftp -y

3. 测试上传能否成功

[root@FirstCentOS backup]# ftp -i -n <<EFO

open 192.168.1.205              #ftp的ip地址

user Administrator 123456        #用户名和密码

cd mysql                      #上传到ftp的哪个文件夹上

lcd /server/backup               #要上传的文件的路径

hash

binary        

put 2015-07-07.tar.bz2            #上传的文件名

prompt

close

bye

EFO

注意上面的binary选项，非常重要，因为从linux上传文件到windows的服务器中会改变格式，而binary选项能保证不改变任何格式

下载文件：

[root@FirstCentOS backup]# ftp -i -n <<EFO

open 192.168.1.205              #ftp的ip地址

user Administrator 123456        #用户名和密码

cd mysql                      #要下载的文件位于ftp的哪个文件夹上

binary        

get 2015-07-07.tar.bz2            #要下载的文件名

close

bye

EFO

Mysql备份与恢复

标签：