首页 > 系统管理 > mysql的备份

mysql的备份

2008年9月23日
  1. 使用mysqldump

  2. mysqldump -u root -p your-new-password databasename [tablename] > db.sql

    比较大的表需要用优化的dump以节省内存:
    mysqldump --opt database > backup-file.sql

    mysqldump支持下列选项:

    –add-locks
    在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。
    –add-drop-table
    在每个create语句之前增加一个drop table。
    –allow-keywords
    允许创建是关键词的列名字。这由在列名前面加表名的方法做到。
    -c, –complete-insert
    使用完整的insert语句(用列名字)。
    -C, –compress
    如果客户和服务器均支持压缩,压缩两者间所有的信息。
    –delayed
    用INSERT DELAYED命令插入行。
    -e, –extended-insert
    使用全新多行INSERT语法。(给出更紧缩并且更快的插入语句)
    -#, –debug[=option_string]
    跟踪程序的使用(为了调试)。
    –help
    显示一条帮助消息并且退出。
    –fields-terminated-by=…
    –fields-enclosed-by=…
    –fields-optionally-enclosed-by=…
    –fields-escaped-by=…
    –fields-terminated-by=…
    这些选择与-T选择一起使用,并且有相应的LOAD DATA INFILE子句相同的含义。
    LOAD DATA INFILE语法。
    -F, –flush-logs
    在开始导出前,洗掉在MySQL服务器中的日志文件。
    -f, –force,
    即使我们在一个表导出期间得到一个SQL错误,继续。
    -h, –host=..
    从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。
    -l, –lock-tables.
    为开始导出锁定所有表。
    -t, –no-create-info
    不写入表创建信息(CREATE TABLE语句)
    -d, –no-data
    不写入表的任何行信息。如果你只想得到一个表的结构的导出,这是很有用的!
    –opt
    同–quick –add-drop-table –add-locks –extended-insert –lock-tables。
    应该给你为读入一个MySQL服务器的尽可能最快的导出。
    -pyour_pass, –password[=your_pass]
    与服务器连接时使用的口令。如果你不指定“=your_pass”部分,mysqldump需要来自终端的口令。
    -P port_num, –port=port_num
    与一台主机连接时使用的TCP/IP端口号。(这用于连接到localhost以外的主机,因为它使用 Unix套接字。)
    -q, –quick
    不缓冲查询,直接导出至stdout;使用mysql_use_result()做它。
    -S /path/to/socket, –socket=/path/to/socket
    与localhost连接时(它是缺省主机)使用的套接字文件。
    -T, –tab=path-to-some-directory
    对于每个给定的表,创建一个table_name.sql文件,它包含SQL CREATE 命令,和一个table_name.txt文件,它包含数据。 注意:这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据–fields-xxx和–lines–xxx选项来定。
    -u user_name, –user=user_name
    与服务器连接时,MySQL使用的用户名。缺省值是你的Unix登录名。
    -O var=option, –set-variable var=option
    设置一个变量的值。可能的变量被列在下面。
    -v, –verbose
    冗长模式。打印出程序所做的更多的信息。
    -V, –version
    打印版本信息并且退出。
    -w, –where=’where-condition’
    只导出被选择了的记录;注意引号是强制的!
    “–where=user=’jimf'” “-wuserid>1” “-wuserid<1”

    最常见的mysqldump使用可能制作整个数据库的一个备份:
    mysqldump --opt database > backup-file.sql

    但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的:
    mysqldump --opt database | mysql --host=remote-host -C database

    由于mysqldump导出的是完整的SQL语句,所以用mysql客户程序很容易就能把数据导入了:
    mysqladmin create target_db_name
    mysql target_db_name < backup-file.sql

  3. 启用二进制日志(binlog)

  4. 采用 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.inforelay-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. 直接备份数据文件

  6. 相较前几种方法,备份数据文件最为直接、快速、方便,缺点是基本上不能实现增量备份。为了保证数据的一致性,需要在靠背文件前,执行以下 SQL 语句:
    FLUSH TABLES WITH READ LOCK;
    也就是把内存中的数据都刷新到磁盘中,同时锁定数据表,以保证拷贝过程中不会有新的数据写入。这种方法备份出来的数据恢复也很简单,直接拷贝回原来的数据库目录下即可。
    注意,对于 Innodb 类型表来说,还需要备份其日志文件,即 ib_logfile* 文件。因为当 Innodb 表损坏时,就可以依靠这些日志文件来恢复。

  7. 备份策略

  8. 对于中等级别业务量的系统来说,备份策略可以这么定:第一次全量备份,每天一次增量备份,每周再做一次全量备份,如此一直重复。而对于重要的且繁忙的系统来说,则可能需要每天一次全量备份,每小时一次增量备份,甚至更频繁。为了不影响线上业务,实现在线备份,并且能增量备份,最好的办法就是采用主从复制机制(replication),在 slave 机器上做备份。

  9. 数据维护和灾难恢复

  10. 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
    关于该参数的信息请查看手册。

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

系统管理

  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.

This blog is kept spam free by WP-SpamFree.