Mysql 数据库备份和恢复

目前 MySQL 支持的免费备份工具有：mysqldump、mysqlhotcopy、SQL 语法

进行备份：BACKUP TABLE 或者 SELECT INTO OUTFILE、备份二进制日志（binlog）、

以及直接拷贝数据文件和相关的配置文件。

MyISAM 表是保存成文件的形式，因此相对比较容易备份，上面提到的几种

方法都可以使用。

Innodb 所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，

或者是独立的表空间文件），相对来说难备份一些，常用的备份方法可以是拷贝

数据文件、备份 binlog 或 mysqldump。

1 利用 Mysqldump 命令备份 Mysql 数据库

mysqldump 是采用 SQL 级别的备份机制，它将数据表导成 SQL 脚本文件，

在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。对

于所有类型的表均可采用该工具进行转储备份。然而，如果你在服务器上进行备

份，并且表类型均为 MyISAM 表，则应考虑使用 mysqlhotcopy 工具备份，因为它

可以更快地进行备份和恢复。

mysqldump 有 3 种方式：

shell> mysqldump [options] db_name [tables]：备份指定数据库指定表；

shell>mysqldump [options] --database DB1 [DB2 DB3...]：备份指定的数据

库；

shell>mysqldump [options] --all—database：备份所有的数据库。

如果没有指定任何表或使用了--database 或--all--database 选项，则转储

备份整个数据库。如果使用最新版本的 mysqldump 程序生成一个备份，恢复到旧版本的 MySQL

服务器中，不应使用--opt 或-e 选项。

Mysqldump 可以用 mysqldump --help 或者-?显示各个选项及选项的说明。

常用的选项：

·--add-drop—database：在每个 CREATE DATABASE 语句前添加 DROP DATABASE

语句。

·--add-drop-tables：在每个 CREATE TABLE 语句前添加 DROP TABLE 语句。

·--add-locking：用 LOCK TABLES 和 UNLOCK TABLES 语句引用每个表转储。重

载转储文件时插入得更快。

·--all--database，-A：转储所有数据库中的所有表。与使用---database 选

项相同，在命令行中命名所有数据库。

·--compact：产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、

--no-set-names、--skip-disable-keys 和--skip-add-locking 选项。

·--compatible=name：产生与其它数据库系统或旧的 MySQL 服务器更兼容的输

出。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、

maxdb、no_key_options、no_tables_options 或者 no_field_options。要使用

几个值，用逗号将它们隔开。这些值与设置服务器 SQL 模式的相应选项有相同的

含义。

该选项不能保证同其它服务器之间的兼容性。它只启用那些目前能够使转储

输出更兼容的 SQL 模式值。例如，--compatible=oracle 不映射 Oracle 类型或

使用 Oracle 注释语法的数据类型。

·--complete-insert，-c：使用包括列名的完整的 INSERT 语句。

·--compress，-C：压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。

·--database，-B：转储几个数据库。通常情况，mysqldump 将命令行中的第 1

个名字参量看作数据库名，后面的名看作表名。使用该选项，它将所有名字参量

看作数据库名。CREATE DATABASE IF NOT EXISTS db_name 和 USE db_name 语句

包含在每个新数据库前的输出中。

·--default-character-set=charset：使用 charsetas 默认字符集。如果没有

指定，mysqldump 使用 utf8。

·--delayed-insert：使用 INSERT DELAYED 语句插入行。

·--delete-master-logs：在主复制服务器上，完成转储操作后删除二进制日志。

该选项自动启用--master-data。

·--disable-keys，-K：对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE

KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用 INSERT

语句。这样可以更快地装载转储文件，因为在插入所有行后创建索引。该选项只

适合 MyISAM 表。

·--extended-insert，-e：使用包括几个 VALUES 列表的多行 INSERT 语法。这

样使转储文件更小，重载文件时可以加速插入。

·--flush-logs，-F：开始转储前刷新 MySQL 服务器日志文件。该选项要求 RELOAD

权限。请注意如果结合--all--database(或-A)选项使用该选项，根据每个转储

的数据库刷新日志。例外情况是当使用--lock-all-tables 或--master-data 的

时候：在这种情况下，日志只刷新一次，在所有 表被锁定后刷新。如果你想要

同 时 转 储 和 刷 新 日 志 ， 应使用 --flush-logs 连 同 --lock-all-tables 或

--master-data。

·--force，-f：在表转储过程中，即使出现 SQL 错误也继续。

·--host=host_name，-h host_name：从指定主机的 MySQL 服务器转储数据。默认主机是 localhost。

·--hex-blob：使用十六进制符号转储二进制字符串列(例如，'abc' 变为

0x616263)。影响到的列有 BINARY、VARBINARY、BLOB。

·--lock-all-tables，-x：所有数据库中的所有表加锁。在整体转储过程中通

过全局读锁定来实现。该选项自动关闭--single-transaction 和--lock-tables。

·--lock-tables，-l：开始转储前锁定所有表。用 READ LOCAL 锁定表以允许并

行插入 MyISAM 表。对于事务表例如 InnoDB 和 BDB，--single-transaction 是一

个更好的选项，因为它不根本需要锁定表。

请注意当转储多个数据库时，--lock-tables 分别为每个数据库锁定表。因此，

该选项不能保证转储文件中的表在数据库之间的逻辑一致性。不同数据库表的转

储状态可以完全不同。

·--master-data[=value]：该选项将二进制日志的位置和文件名写入到输出中。

该选项要求有 RELOAD 权限，并且必须启用二进制日志。如果该选项值等于 1，

位置和文件名被写入 CHANGE MASTER 语句形式的转储输出，如果你使用该 SQL

转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。

如果选项值等于 2，CHANGE MASTER 语句被写成 SQL 注释。如果 value 被省略，

这是默认动作。

--master-data 选项启用 --lock-all-tables ， 除 非 还 指 定

--single-transaction(在这种情况下，只在刚开始转储时短时间获得全局读锁

定。又见--single-transaction。在任何一种情况下，日志相关动作发生在转储

时。该选项自动关闭--lock-tables。

·--no-create-db，-n：该选项禁用 CREATE DATABASE /*!32312 IF NOT EXISTS*/

db_name 语句，如果给出---database 或--all--database 选项，则包含到输出

中。

·--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。它可以给出很快的转储操作并产生一个可以很快装入 MySQL 服

务器的转储文件。该选项默认开启，但可以用--skip-opt 禁用。要想只禁用确

信用-opt 启用的选项，使用--skip 形式；例如，--skip-add-drop-tables 或

--skip-quick。

·--password[=password]，-p[password]：连接服务器时使用的密码。如果你

使用短选项形式(-p)，不能在选项和密码之间有一个空格。如果在命令行中，忽

略了--password 或-p 选项后面的 密码值，将提示你输入一个。

·--port=port_num，-P port_num：用于连接的 TCP/IP 端口号。

·--protocol={TCP | SOCKET | PIPE | MEMORY}：使用的连接协议。

·--quick，-q：该选项用于转储大的表。它强制 mysqldump 从服务器一次一行

地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

如果运行 mysqldump 没有--quick 或--opt 选项，mysqldump 在转储结果前将整

个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用，但可

以用--skip-opt 禁用。

要想获得 mysqldump 支持的更多选项，执行 mysqldump –help。

1.1 备份单个数据库

$mysqldump -hhostname -uusername -ppassword databasename >

backupfile.sql1.2 备份多个数据库

$mysqldump -hhostname -uusername -ppassword –databases databasename1

databasename2 databasename3 > multibackupfile.sql

1.3 备份所有数据库

$mysqldump -hhostname -uusername -ppassword > backupfile.sql

1.4 备份多个数据库的结构

$mysqldump –no-data –databases databasename1 databasename2

databasename3 > structurebackupfile.sql

1.5 数据库压缩备份

$mysqldump -hhostname -uusername -ppassword databasename | gzip >

backupfile.sql.gz

1.6 备份单个表

$mysqldump -uusername -ppassword databasename users>

databasename_users.sql

2 利用 mysqldump 备份的数据恢复

2.1 用 mysql 客户端恢复

$mysql -uusername -ppassword databasename < databasename.sql2.2 用 SOURCE 恢复

Mysql>SOURCE /tmp/databasename.sql;

3 利用 mysqlhotcopy 命令备份 Mysql 数据库

mysqlhotcopy 是一个 PERL 程序。它使用 LOCK TABLES、FLUSH TABLES 和

cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只

能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器

上。

mysqlhotcopy 只能用于备份 MyISAM，并且只能运行在类 Unix 和 NetWare

系统上。

mysqlhotcopy 支持一次性拷贝多个数据库。

注意，想要使用 mysqlhotcopy，必须要有 SELECT、RELOAD(要执行 FLUSH

TABLES) 权限，并且还必须要能够有读取 datadir/db_name 目录的权限。

选项解释

// 显示帮助消息并退出。

--help

// 如果目标存在不放弃(加上一个_old 后缀重新命名它)。

--allowold

// 不要重命名目标目录（如果存在）;新的备份自动覆盖原来的，可以实现增

量备份。

--addtodest// 在指定的 db_name.tbl_name 插入检查点条目。

--checkpoint=db_name.tbl_name

// 启用调试输出

--debug

// 报告动作而不执行它们。

--dryrun，-n

// 所有表锁定后刷新日志。

--flushlog

// 完成后不删除以前(重新命名的)的目标。

--keepold

// 复制方法(cp 或 scp)。

-- method=command

// 备份中不包括全部索引文件。这样使备份更小、更快。可以在以后用

myisamchk -rq 重新构建索引。

--noindices

// 当连接服务器时使用的密码。请注意该选项的密码值是不可选的，不像其它

MySQL 程序。

--password=password，-p password

// 当连接本地服务器时使用的 TCP/IP 端口号。--port=port_num，-P port_num

// 除了出现错误时保持沉默。

--quiet，-q

// 复制所有数据库名匹配给出的正则表达式的数据库。

--regexp=expr

// 用于连接的 Unix 套接字文件。

--socket=path，-S path

// 所复制的数据库名的后缀。

--suffix=str

// 临时目录(代替/tmp)。

--tmpdir=path

// 当连接服务器时使用的 MySQL 用户名.要想执行 mysqlhotcopy，你必须可以

访问备份的表文件，具有那些表的 SELECT 权限和 RELOAD 权限(以便能够执行

FLUSH TABLES).

--user=user_name，-u user_name

3.1 备份 mysql 整个数据库

#mysqlhotcopy -uusername -ppassword mysql /backup/mysql

3.2 备份 mysql 和 test1 两个数据库

# mysqlhotcopy -uusername -ppassword mysql test1 /backup/mysql3.3 备份 mysql 数据库里的 user 表

# mysqlhotcopy -uusername -ppassword mysql./user*/ /backup/mysql

3.3 利用 mysqlhotcopy 编写的备份脚本

1）建立数据库自动备份脚本

# vi mysql-backup.sh

自动备份的脚本内容如下：

#！/bin/bash

PATH=/usr/local/sbin:/usr/bin:/bin # The Directory of Backup BA

CKDIR=/backup/mysql

# The Password of MySQL ROOTPASS=123456

# Remake the Directory of Backup rm -rf $BACKDIR mkdir -p

$BACKDIR

# Get the Name of Database

DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /` # Backup

with Database for dbname in $DBLIST do

mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger

-t mysqlhotcopy done

2）增加执行权限

# chmod 700 mysql-backup.sh

3）运行脚本# ./mysql-backup.sh

4）查看备份是否成功

# ll /backup/mysql

5）设置备份脚本每天凌晨 3 点自动运行

# crontab –e

// 在文本里添加下面一行

00 03 * * * /root/mysql-backup.sh

// 查看作业情况

# crontab -l

00 03 * * * /root/mysql-backup.sh

4 利用 mysqlhotcopy 备份的数据恢复

1）停止 mysql 服务器。

2）复制备份的数据库目录到 mysql 数据目录下。

3）更改目录所有者为 mysql 服务器运行的用户（如 mysql）。

4）启动 mysql 服务器

// 复制备份的数据库 test 到相应目录，注意目录的权限。

# /bin/cp -rf /backup/mysql/test/ /var/lib/mysql/5 数据库常用管理命令

5.1 启动 mysql 数据库

#/ect/init.d/mysql start

//重启 mysql 的命令：

#/ect/init.d/mysql restart

5.2 停止 mysql 数据库

#/ect/init.d/mysql shutdown

或

#mysqladmin -uroot -p shutdown

5.3 查看 mysql 运行状态

//查看 mysql 服务器运行状态

mysqladmin -uroot -p status

//查看 mysql 服务器运行情况

mysqladmin -uroot -p ping

//查看版本

mysqladmin -uroot -p version

//检查维护表

mysqlcheck -uroot -p mytest6 常用数据库操作命令

要对数据库进行操作，必须先登录到数据库中，以下操作在 mysql 的提示符

下进行，而且每个命令必须以分号结束。

6.1 显示数据库

show databases;

6.2 显示 mysql 库中的表

use mysql；

show tables;

6.3 显示数据表结构

describe 表名;

6.4 建库

create database 库名;

6.5 建表

use 库名；

create table 表名(字段设定列表)；

6.6 删库和删表

drop database 库名;

drop table 表名;6.7 编码的修改

如果要改变整个 mysql 的编码格式：

启动 mysql 的时候，mysqld_safe 命令行加入

--default-character-set=gbk

如果要改变某个库的编码格式：在 mysql 提示符后输入命令

alter database db_name default character set gbk;