MySQL的备份和还原

MySQL的备份和还原

本文主要参考manu，请阅读manu 

mysql 备份工具：mysqldump mysqlhotcopy，select into out_file

一、为什么要有备份工具：

有人就说你的数据不就是文件吗？为什么还要用工具，闲的蛋疼，直接就是tar zcf /tmp/`date +%Y.%m.%d.%H.%M`.Mysql_backup.gz  mysql_data_directory. 对你说的对！但是你的方法要想成功前提你必须stop mysqld ，之后在备份，撒时候完毕再启动mysqld。这个不仅实验的时候不方便，生产环境那是要死人的，谁让你停服务呀，24h必须的。我们说的东东是可以在线备份，还可以定期备份（提供备份策略）。

二、如何实现

1.mysqldump ：最常用的数据库备份工具，举例说明：

A.对MyISAM 数据库的备份

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \

--default-character-set=utf8 --opt --extended-insert=false \

--triggers -R --hex-blob -x db_name > db_name.sql

解析：调用mysqldump（当然可以写入到crontab 中），-u username -p password

--opt(4.1的时候添加的一个复合选项，包括：add-drop-table 等等，导出的时候最优化处理）--default_character-set(如果不是latin1 还是要显示说明，不然在导入的时候可能出现乱码现象）--extended-insert=false，因为默认的-c/--complete-insert，就是把所有插入的数据，写在

一条insert 语句中，这样由于数据太多可能报错，还是insert into 那样一条条写比较好，所以建议--extended-insert置为false。

--trigger -R ：就是要把trigger 和存储过程和自定义函数等都导出来；--hex-blob 就是有二进制文件16进制显示（blob,binary,varbinary)

-x:在导入全局锁定数据库（lock表），db_name:要备份的数据库的名字 > db_name.sql （导出的sql 脚本）

当然以上的option 都不是很全，你可以mysqldump --help查看在线帮助。

B.InnoDB:

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \

--default-character-set=utf8 --opt --extended-insert=false \

--triggers -R --hex-blob --single-transaction db_name > db_name.sql   --single-transaction 替换-x。

补充：现在线备份，还可以使用--master-data 参数来实现

/usr/local/mysql/bin/mysqldump -uyejr -pyejr \

--default-character-set=utf8 --opt --master-data=1 \

--single-transaction --flush-logs db_name > db_name.sql

mysqldump 的一些选项： 

1) --result-file=db_name.sql   ==== >db_name.sql

2) 如果你想对多个数据库进行操作， --databases music wedding （就是对这个两个数据库操作，必须加--databases），如果不加

就是music 数据库，wedding表了当然还可以加where语句。

3）如果备份所有的数据库，--all-databases. 更多参考可以看帮助，google等等。

4）实现regular 备份：crontab -e    45 4 * * sun mysqldump。。。。。命令就可以了。

C. mysqldump 还原

备份下来的是文本文档的sql 语句，可以gzip ：| gzip --best --to-stdout >/dump_dirctory/

`date +%Y.%m.%d.%H.%M`.Mysql_backup.gz. 用| 命令实现。

Loading Data 从dump file 中：  source dumpfile.sql ;  mysql mysql_options < dumpfile.sql

建议用source ，当然在之前你可以drop database  xxx  ,create database xxx,use xxx ;

2、mysqlhotcopy

使用perl 写的，一般都用在unixLike 系统上，是binary copy，你会得到的是数据库文件，不是text files。非常快，缺点perl，myisam 数据库，你还原的时候stop server？

A.备份：把所有你需要的都备份（mysql数据库可能也要----权限）

root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr db_name_1 ... db_name_n /tmp

可以把 db1到n个数据库备份到/tmp

B.还原：停止服务

root#cp -rf db_name /usr/local/mysql/data/   复制所有数据库到/data 目录。

root#chown -R nobody:nobody /usr/local/mysql/data/ (将db_name 目录的属主改成mysqld 运行用户)  可能不是nobody 要注意。

对于备份的建议：根据实际需求制定备份时间、频率。  备份介质选择要好，储存要好。如果实在机密可以备份、压缩、加密。

3、SQL 语法备份 ---------这个没有找到。

很多网上说的，我个人理解的是对数据的备份。show create table table_name ------->可以导出表结构，select articleID,title into outfile '/tmp/articles2.txt' from article; 这个命令可以导出数据默认的是tab 分隔字段。LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name。

4、启用二进制日志(binlog)

A.背景介绍

其实这个binlog 在网上有大把的资料你可以看得。我们简单介绍一下它在备份中的作用。日志故名思议就是记录你做了什么（当然你select，或者没有改变数据库实质的update都不记录）5.0之后作为binary 存储日志，只要快速等等一些好处，mysqlbinlog 是一个工具，可以用它来看到我们的binary形式的的日志。启用binlog 功能（）会在mysql 的数据文件xxx/data/ 下建立XXX-bin.001和xxx-bin.index ，其中：xxx-bin.00001 是第一个日志文件，你重启一次，或者到了max_binlog_size 文件序号递增，而 xxx-bin.index 中放的这些真正的日志文件的索引，方便查找。应该用最近一次制作的完整备份恢复数据库，然后使用备份之后的日志文件把数据库恢复到最接近现在的可用状态。使用日志进行恢复时需要依次进行，即最早生成的日志文件要最先恢复：

mysqlbinlog xxx-bin.00001 | mysql –u root –p 

mysqlbinlog xxx-bin.00002 | mysql –u root –p

B.实际操作

首先启动binlog这项服务功能：

[mysqld]

log-bin = log_directory/mysql-bin.log 那么在log_directory 下边就有： mysql-bin.000001   mysql-bin.index 

需要备份时，可以先执行一下SQL 语句，让mysqld 终止对当前binlog 的写入，就可以把文件直接备份，这样的话就能达到增量备份的目的了：  FLUSH LOGS;

/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name

mysqlbinlog 一般用法：

/usr/local/mysql/bin/mysqlbinlog --start-position="2010-09-29 18:00:00" -d test -h 127.0.0.1 /var/lib/mysql/mysql-bin.000002 ｜mysql -u root -p

从什么位置， -d 还原哪个数据库的增量备份 -h 那台主机的

5、直接copy 文件

copy的特点： 快速，方便，不用掌握什么工具。缺点就是基本没有备份策略！

FLUSH TABLES WITH READ LOCK ;注意，对于Innodb 类型表来说，还需要备份其日志文件，即ib_logfile* 文件。因为当Innodb 表损坏时，就可以依靠这些日志文件来恢复.

6、备份中出现的意外，产生的错误

CHECK TABLE 或REPAIR TABLE，检查或维护MyISAM 表

check table music.artist;  检测表

repair table music.artist;  修复表

shell 命令行工具：mysqlcheck -u root -p --repair music(extend 比repair更加慢点,修复彻底）

myisamchk --recover ......artist.MYI  文件。

附录： mysql database 出现问题了，重建授权文件 mysql_install_db