我参考了MYSQL5.1+简体中文参考文档.chm,特别是 5.9. 备份与恢复,感觉是雾里看花。后来直接去参考 8.8. mysqldump:数据库备份程序 ,其中尝试了两条命令
<1>.mysqldump --tab=路径 --user=root --password=root 数据库名,这个命令在指定路径下生成了所有表的.sql文件和数据.txt文件,这些文件的个数很多,如何将他们恢复进数据库。
<2>.mysqldump -R -C --user=root --password=root 数据库名>路径,这个命令在指定路径下生成了一个.sql文件,我想问当数据库数据量很大时,这个.sql文件是不是会被分成了好几个.sql文件分别存储。
我现在做的是一个c/s结构的系统,每天第一次运行系统的时候,会自动备份昨天的数据库数据。(也许不是每一天,可能以周为单位备份,具体多长周期备份一次才合适,还请大侠们赐教。)现在求教备份的具体步骤,假设数据名为test 用户名root 密码root 数据库中存在视图、存储过程、触发器,最好有能参考的命令。有二进制更新日志文件,假设名为myupdate.000066,备份之后如何恢复进数据库,最好能有参考的命令。

解决方案 »

  1.   

    备份数据库包括三层含义1.导出整个数据库进入mysql安装目录下bin目录(当然如果你的bin已经包含在path目录下,在任何目录下都可以)用法:mysqldump -u 用户名 -p 数据库名 > 导出的文件名 mysqldump -uroot -p test>c:/temp/db_test.backup输入密码:******备份成功!现在我们以文本的方式打开c:/temp/db_test.backup这个文件,你会发现新大陆的^_^^_^2.导出一个表
    用法:mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名mysqldump -uroot -p test t_student>c:/temp/db_test.t_student.backup3.导出一个数据库结构这个说起来有点抽象,一会儿,我慢慢解释……用法:mysqldump -uroot -p -d --add-drop-table 数据库名>导出文件名mysqldump -uroot -p -d --add-drop-table test>c:/temp/db_test.structure.backup**注意:-d表示没有数据--add-drop-table表示在每一个create语句之前添加一个drop table语句打开c:/temp/db_test.structure.backup文件,和先前的两个文件比较,你会发现该文件里没有我们插入的数据二、导入数据库1.source命令用法:mysql -uroot -p输入密码:******mysql>use 数据库名;mysql>source 备份的数据库文件mysql>source c:/temp/db_test.backup;2.直接mysql -uroot -D数据库名< 备份的数据库文件>mysql-uroot -p -Dtest<c:/temp/db_test.backup;输入密码:******三、数据的导入、导出1.数据的导入**注意:文本数据应符合的格式:字段数据之间用tab键隔开,null值用n来代替这里我给t_student表添加一条记录,格式如下:1    zhangsan    male    1986-11-24保存为a.data文件名用法: load data infile "文件名" into table 表名;或者load data infile "文件名" into table 数据库名.表名mysql -uroot -p输入密码:******mysql>use test;mysql>load data infile "c:/temp/a.data" into table t_student;**注意:你最好将文件复制到mysql/bin目录下,并且要先用use命令打表所在的库2.数据的导出mysql>select * from 数据库.数据表名 into outfile "文件名"mysql>select * from test.t_student into outfile "c:/temp/data.out"当然你也可以先use test;再select * from t_student into outfile "c:/temp/data.out"1、mysqldump
    1.1 备份
    mysqldump 是采用SQL级别的备份机制,它将数据表导成 SQL 脚本文件,在不同的 MySQL 版本之间升级时相对比较合适,这也是最常用的备份方法。
    现在来讲一下 mysqldump 的一些主要参数:--compatible=name 
    它告诉 mysqldump,导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等,要使用几个值,用逗号将它们隔开。当然了,它并不保证能完全兼容,而是尽量兼容。 --complete-insert,-c 
    导出的数据采用包含字段名的完整 INSERT 方式,也就是把所有的值都写在一行。这么做能提高插入效率,但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此,需要谨慎使用该参数,至少我不推荐。 --default-character-set=charset 
    指定导出数据时采用何种字符集,如果数据表不是采用默认的 latin1 字符集的话,那么导出时必须指定该选项,否则再次导入数据后将产生乱码问题。--disable-keys 
    告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE table ENABLE KEYS */; 语句,这能大大提高插入语句的速度,因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。--extended-insert = true|false 
    默认情况下,mysqldump 开启 --complete-insert 模式,因此不想用它的的话,就使用本选项,设定它的值为 false 即可。 --hex-blob 
    使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。--lock-all-tables,-x 
    在开始导出之前,提交请求锁定所有数据库中的所有表,以保证数据的一致性。这是一个全局读锁,并且自动关闭 --single-transaction 和 --lock-tables 选项。--lock-tables 
    它和 --lock-all-tables 类似,不过是锁定当前导出的数据表,而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表,如果是 Innodb 表可以用 --single-transaction 选项。--no-create-info,-t 
    只导出数据,而不添加 CREATE TABLE 语句。--no-data,-d 
    不导出任何数据,只导出数据库表结构。--opt 
    这只是一个快捷选项,等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqldump 很快的导出数据,并且导出的数据能很快导回。该选项默认开启,但可以用 --skip-opt 禁用。注意,如果运行 mysqldump 没有指定 --quick 或 --opt 选项,则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。--quick,-q 
    该选项在导出大表时很有用,它强制 mysqldump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。--routines,-R 
    导出存储过程以及自定义函数。--single-transaction 
    该选项在导出数据之前提交一个 BEGIN SQL语句,BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表,例如 InnoDB 和 BDB。
    本选项和 --lock-tables 选项是互斥的,因为 LOCK TABLES 会使任何挂起的事务隐含提交。
    要想导出大表的话,应结合使用 --quick 选项。 --triggers 
    同时导出触发器。该选项默认启用,用 --skip-triggers 禁用它。其他参数详情请参考手册,我通常使用以下 SQL 来备份 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
    使用以下 SQL 来备份 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
    另外,如果想要实现在线备份,还可以使用 --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
    它只是在一开始的瞬间请求锁表,然后就刷新binlog了,而后在导出的文件中加入CHANGE MASTER 语句来指定当前备份的binlog位置,如果要把这个文件恢复到slave里去,就可以采用这种方法来做。 1.2 还原
    用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本,有两种方法可以将数据导入。直接用 mysql 客户端 
    例如:/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql用 SOURCE 语法 
    其实这不是标准的 SQL 语法,而是 mysql 客户端提供的功能,例如:SOURCE /tmp/db_name.sql;
    这里需要指定文件的绝对路径,并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。2、 mysqlhotcopy
    2.1 备份
    mysqlhotcopy 是一个 PERL 程序,最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库文件(包括数据表定义文件、数据文件、索引文件)所在的机器上。mysqlhotcopy 只能用于备份 MyISAM,并且只能运行在 类Unix 和 NetWare 系统上。mysqlhotcopy 支持一次性拷贝多个数据库,同时还支持正则表达。以下是几个例子:root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
    db_name /tmp (把数据库目录 db_name 拷贝到 /tmp 下)
    root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
    db_name_1 ... db_name_n /tmp
    root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr \
    db_name./regex/ /tmp
    更详细的使用方法请查看手册,或者调用下面的命令来查看 mysqlhotcopy 的帮助:perldoc /usr/local/mysql/bin/mysqlhotcopy
    注意,想要使用 mysqlhotcopy,必须要有 SELECT、RELOAD(要执行 FLUSH TABLES) 权限,并且还必须要能够有读取 datadir/db_name 目录的权限。 2.2 还原
    mysqlhotcopy 备份出来的是整个数据库目录,使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可,同时要注意权限的问题,如下例:root#cp -rf db_name /usr/local/mysql/data/
    root#chown -R nobody:nobody /usr/local/mysql/data/ (将 db_name 目录的属主改成 mysqld 运行用户)3、 SQL 语法备份
    3.1 备份
    BACKUP TABLE 语法其实和 mysqlhotcopy 的工作原理差不多,都是锁表,然后拷贝数据文件。它能实现在线备份,但是效果不理想,因此不推荐使用。它只拷贝表结构文件和数据文件,不同时拷贝索引文件,因此恢复时比较慢。
    例子:BACK TABLE tbl_name TO '/tmp/db_name/';
    注意,必须要有 FILE 权限才能执行本SQL,并且目录 /tmp/db_name/ 必须能被 mysqld 用户可写,导出的文件不能覆盖已经存在的文件,以避免安全问题。 SELECT INTO OUTFILE 则是把数据导出来成为普通的文本文件,可以自定义字段间隔的方式,方便处理这些数据。
    例子:SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
    注意,必须要有 FILE 权限才能执行本SQL,并且文件 /tmp/db_name/tbl_name.txt 必须能被 mysqld 用户可写,导出的文件不能覆盖已经存在的文件,以避免安全问题。 3.2 恢复
    用 BACKUP TABLE 方法备份出来的文件,可以运行 RESTORE TABLE 语句来恢复数据表。
    例子:RESTORE TABLE FROM '/tmp/db_name/';
    权限要求类似上面所述。用 SELECT INTO OUTFILE 方法备份出来的文件,可以运行 LOAD DATA INFILE 语句来恢复数据表。
    例子:LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;
    权限要求类似上面所述。倒入数据之前,数据表要已经存在才行。如果担心数据会发生重复,可以增加 REPLACE 关键字来替换已有记录或者用 IGNORE 关键字来忽略他们。4、 启用二进制日志(binlog)
    采用 binlog 的方法相对来说更灵活,省心省力,而且还可以支持增量备份。启用 binlog 时必须要重启 mysqld。首先,关闭 mysqld,打开 my.cnf,加入以下几行:server-id = 1
    log-bin = binlog
    log-bin-index = binlog.index
    然后启动 mysqld 就可以了。运行过程中会产生 binlog.000001 以及 binlog.index,前面的文件是 mysqld 记录所有对数据的更新操作,后面的文件则是所有 binlog 的索引,都不能轻易删除。关于 binlog 的信息请查看手册。需要备份时,可以先执行一下 SQL 语句,让 mysqld 终止对当前 binlog 的写入,就可以把文件直接备份,这样的话就能达到增量备份的目的了:FLUSH LOGS;如果是备份复制系统中的从服务器,还应该备份 master.info 和 relay-log.info 文件。 备份出来的 binlog 文件可以用 MySQL 提供的工具 mysqlbinlog 来查看,如:/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
    该工具允许你显示指定的数据库下的所有 SQL 语句,并且还可以限定时间范围,相当的方便,详细的请查看手册。恢复时,可以采用类似以下语句来做到:/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
    把 mysqlbinlog 输出的 SQL 语句直接作为输入来执行它。如果你有空闲的机器,不妨采用这种方式来备份。由于作为 slave 的机器性能要求相对不是那么高,因此成本低,用低成本就能实现增量备份而且还能分担一部分数据查询压力,何乐而不为呢?5、 直接备份数据文件
    相较前几种方法,备份数据文件最为直接、快速、方便,缺点是基本上不能实现增量备份。为了保证数据的一致性,需要在靠背文件前,执行以下 SQL 语句:FLUSH TABLES WITH READ LOCK;也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件。因为当 Innodb 表损坏时,就可以依靠这些日志文件来恢复。6、 备份策略
    对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做一次全量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份。7、 数据维护和灾难恢复
    作为一名DBA(我目前还不是,呵呵),最重要的工作内容之一是保证数据表能安全、稳定、高速使用。因此,需要定期维护你的数据表。以下 SQL 语句就很有用:CHECK TABLE 或 REPAIR TABLE,检查或维护 MyISAM 表
    OPTIMIZE TABLE,优化 MyISAM 表
    ANALYZE TABLE,分析 MyISAM 表
    当然了,上面这些命令起始都可以通过工具 myisamchk 来完成,在这里不作详述。Innodb 表则可以通过执行以下语句来整理碎片,提高索引速度:ALTER TABLE tbl_name ENGINE = Innodb;
    这其实是一个 NULL 操作,表面上看什么也不做,实际上重新整理碎片了。通常使用的 MyISAM 表可以用上面提到的恢复方法来完成。如果是索引坏了,可以用 myisamchk 工具来重建索引。而对于 Innodb 表来说,就没这么直接了,因为它把所有的表都保存在一个表空间了。不过 Innodb 有一个检查机制叫 模糊检查点,只要保存了日志文件,就能根据日志文件来修复错误。可以在 my.cnf 文件中,增加以下参数,让 mysqld 在启动时自动检查日志文件:innodb_force_recovery = 4
      

  2.   

    可以看一下梁版的这个贴子。步骤很详细。用mysqldump 做全备。然后利用binlog 做增量恢复http://topic.csdn.net/u/20090608/20/e50feb34-0a07-4675-80d4-2eac35973286.html?94623
    使用mysqlbinlog恢复数据
      

  3.   

    本想贴个官方中文手册的链接的,不过好象不对了。你自己到 http://dev.mysql.com/doc/ 下个ZIP版的吧。http://downloads.mysql.com/docs/refman-5.1-zh.html-chapter.zip
    这是英文版的地址,讲得很详细。
    http://dev.mysql.com/doc/refman/5.1/en/backup-and-recovery.html
    Chapter 6. Backup and Recovery6.1. Database Backups 
    6.2. Example Backup and Recovery Strategy     [+/-]
    6.2.1. Backup Policy 
    6.2.2. Using Backups for Recovery 
    6.2.3. Backup Strategy Summary 
    6.3. Point-in-Time Recovery     [+/-]
    6.3.1. Specifying Times for Recovery 
    6.3.2. Specifying Positions for Recovery 
    6.4. Table Maintenance and Crash Recovery     [+/-]
      

  4.   

    谢谢up!
    仔细看了梁版的例子,例子中介绍的法子仅仅是恢复策略。在恢复之前要有正确的备份,如何生成整个数据库的正确备份。我参考mysql参考文档,虽然上面提供了很多工具,但是正是因为这么多方法,且每种法子都有不同的适用场景,这样造成了现在的困难。~~ 我迫切想知道,现在做系统开发都是如何备份数据的?忘大侠不吝赐教
      

  5.   

    系统开发都是如何备份数据的? 我一般喜欢,把整个datadir 文件一起复制,先把mysqld停了,然后整个文件 数据库的目录一起复制备份。恢复也容易,把原来的目录改个名然后直接制回来就行了。或者你就用 mysqldump 来导出。生成一个 xxx.sql 文件。