mysql数据库备份

一 手动备份：

1 phpmyadmin管理界面下进行备份：

就是手动在phpmyadmin界面的，进行数据导出。

2 使用mysqldump命令备份

使用mysqldump进行Mysql备份

mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。

基本语法：

Shell> mysqldump [OPTIONS] database [tables] > data_backup.sql      （不指定数据库名表示全部备份）

例如：

mysqldump -uroot -p --default-character-set=cp932 --opt --extended-insert=false --hex-blob -R -x mysql > E:\mysql.sql

主要参数：

--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 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。

--extended-insert = true|false

默认情况下，mysqldump 开启 --complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。

--default-character-set=charset

指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

--disable-keys

告诉 mysqldump 在 INSERT 语句的开头和结尾增加 /*!40000 ALTER TABLE table DISABLE KEYS */; 和 /*!40000 ALTER TABLE table ENABLE KEYS */; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。

--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 禁用它。

事例：

1）.将整个数据库备份成all.sql文件

#/usr/local/mysql/bin/mysqldump -uroot --default-character-set=cp932 --opt --extended-insert=false --hex-blob -x --all-databases > all.sql

2）.将整个数据库的表结构备份成table.sql，不要数据和trigger

#/usr/local/mysql/bin/mysqldump -uroot --default-character-set=cp932 --opt --extended-insert=false --hex-blob -x --all-database --no-data --skip-triggers database > table.sql

3）.将所有数据备份成data.sql，注意不要表结构和trigger

#/usr/local/mysql/bin/mysqldump -uroot --default-character-set=cp932 --opt --extended-insert=false --hex-blob -x --all-database --no-create-info --skip-triggers database > data.sql

4）.只将trigger备份成trigger.sql,数据和表结构都不要。

#/usr/local/mysql/bin/mysqldump -uroot --default-character-set=cp932 --opt --extended-insert=false --hex-blob -x triggers >trigger.sql

---------

5）.导出整个数据库

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

例：mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

6）.导出一个表

mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名

例：mysqldump -u wcnc -p smgp_apps_wcnc users> wcnc_users.sql

7）.导出一个数据库结构

mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc > wcnc_db.sql

说明：-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

恢复技术：

使用mysqldump导出的文件恢复数据库

用 mysqldump 备份出来的文件是一个可以直接倒入的 SQL 脚本，有两种方法可以将数据导入。

1）直接用 mysql 客户端

shell> mysql 库名 < 文件名

例如：

#/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql

2）用 source 语句

其实这不是标准的 SQL 语法，而是 mysql 客户端提供的功能，例如：

# source /tmp/db_name.sql;  （这里需要指定文件的绝对路径，并且必须是 mysqld 运行用户有权限读取的文件）

Restore table tb1 from ‘/aaa’

二 自动备份：

1.利用SQLyog的Scheduled备份功能实现自动化备份

SQLyog这mysql管理工具，自从8.x版本之后，已经提供SQLyog’s Scheduled备份功能，帮助DBA执行自动化数据库备份。有个这一强大功能，就可以实现数据库集中和自动化备份，免去了自己写脚本和测试脚本的痛苦。

SQLyog支持生成.sql转储文件和zip压缩

SQLyog支持生成备份脚本并自动加入到Windows任务计划程序库，生成规范文件名格式的备份文件

2.利用Windows Powershell实现过期备份文件删除

该工具的缺憾是，不提供过期备份文件删除功能。

我们可以通过Windows的powershell脚本来弥补这一功能缺憾。

3 Win7下实现mysql数据库定时自动备份

这个方法是用bat批处理来完成的。

MySQL 安装位置：C:\Program Files\MySQL\MySQL Server 5.0\data\bookstore

如图：

把数据库名称为“bookstore”的数据库备份到目的地：d:\db_bak\ （该目录是根据自己的情况确定的）备到D盘的db-bak目录下面。

具体实现步骤如下：

第一步：新建一个TXT文档命名为db_bak，保存为.bat文件。在db_bak.bat写入以下代码：

net stop mysql

Xcopy  "C:\Program Files\MySQL\MySQL Server 5.0\data\bookstore\*.*"  d:\db_bak\bookstore\%date:~0,3%\ /y

net start mysql

第二步：打开程序—>附件—>系统工具—>任务计划程序。

1）创建任务：

输入任务名称，（可以自己定义名称）

2）新建触发器

具体情况可以根据自己的需要进行选择。

3）新建操作

后面的条件和设置，可以根据自己的应用情况进行选择。

最后点击确定。

创建好的任务就出现在列表中：

选中，点击右键，选择运行。正确运行之后，D盘将出现：

到这里就完成自动定时备份了，很简单吧。

最后再给大家多讲一句就是这个批处理命令 \%date:~0,3% 的意思提取星期几信息
当然如果你想创建年月日的话就用%date:~0,10% ,系统就会每天给你建日期目录了。很方便吧。

%date:~0,10% //提取年月日信息
%date:~-3% //提取星期几信息
%time:~0,5% //提取时间中的时和分
%time:~0,-3% //提取时和分和秒信息

注意两点：

1.mysql的安装路径中不能有空格，如不要安装在D:\Program Files\……文件夹下，否则bat文件不能正确运行。（尽管可以手动在cmd中cd到带空格的目录下，但用在cmd中直接运行带空格路径的命令，就会出错）。

解决方法：

1）用缩写：比如c:\Program Files缩写为c:\Progra~1

规则：

文件夹（sub-directry)名称，以前是不允许带空白的，后来允许带空白，但由于有了空白，许多命令出现二义性，于是采用8个字符缩写，即写头六个字母(略去空白），另加波浪号和1。例如：

Documents and Settings -- DOCUME~1

Local Settings -- LOCALS~1 （注意略去空白，用了第二个词的字母，凑成六个，再加波浪号和1）。

那么，如果多个文件前6字符一样怎么办？为什么最后是1而不是0或者其他数字呢？看看这个例子

假设下面是你的C盘根目录中的文件夹：

Program Files

Progra file

Progra zhang

则三个目录分别表示为：C:\Progra~1; C:\Progra~2; C:\Progra~3;

2）用引号将路径引起来

3）d:

cd D:\test 1\test 2\test 3\

x.exe

2.目的文件所在的文件夹要提前建好，如上面的D:\db_backup

3.解释%date:~0,4%：%……%是取命令运行的结果，就像Unix下的''。date是查看日期命令，time是查看时间命令。0,4是取结果中索引0开始共4个元素的字符串。之所以不直接用%time:~0,8%了事，是因为windows文件的命名中不能有冒号，所以拆开了。

三 快速修复MySQL数据库

有的时候因为掉电或者其他原因导致数据库损坏,我们可以使用mysql自带的mysqlcheck命令来快速修复所有的数据库或者特定的数据库;例如
检查优化并修复所有的数据库用:
# mysqlcheck -A -o -r -p
Enter password:
guestbook.simpgb_avatars OK
guestbook.simpgb_bad_words OK
guestbook.simpgb_banlist OK
guestbook.simpgb_data OK
......
......
......
修复指定的数据库用
# mysqlcheck -A -o -r Database_NAME -p
即可