mysql使用mysqldump备份、还原数据库详解教程
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、mysqldump 备份操作</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.1 备份基础语法</a></li><li><a href="#_lab2_0_1">1.2 备份案例</a></li></ul><li><a href="#_label1">二、mysqldump 还原操作</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_2">2.1 还原基础语法</a></li><li><a href="#_lab2_1_3">2.2 还原案例</a></li></ul><li><a href="#_label2">三、mysqldump 按条件导出与导入</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_4">3.1 按条件导出</a></li><ul class="third_class_ul"><li><a href="#_label3_2_4_0">3.1.1 按条件导出基础语法</a></li><li><a href="#_label3_2_4_1">3.1.2 按条件导出案例</a></li></ul><li><a href="#_lab2_2_5">3.2 按条件导入</a></li><ul class="third_class_ul"><li><a href="#_label3_2_5_2">3.2.1 按条件导入基础语法</a></li><li><a href="#_label3_2_5_3">3.2.2 按条件导入案例</a></li></ul></ul><li><a href="#_label3">四、mysqldump 表导出操作</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_6">4.1 表导出基础语法</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_3_7">4.2 表导出案例（仅导出表结构，不含数据）</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label4">五、mysqldump 主要参数说明</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_8">5.1 --compatible=name</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_9">5.2 --complete-insert，-c</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_10">5.3 --default-character-set=charset</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_11">5.4 --disable-keys</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_12">5.5 --extended-insert = true|false</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_13">5.6 --hex-blob</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_14">5.7 --lock-all-tables，-x</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_15">5.8 --lock-tables</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_16">5.9 --no-create-info，-t</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_17">5.10 --no-data，-d</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_18">5.11 --opt</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_19">5.12 --quick，-q</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_20">5.13 --routines，-R</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_21">5.14 --single-transaction</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_22">5.15 --triggers</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_4_23">5.16 其他参数说明</a></li><ul class="third_class_ul"></ul></ul><li><a href="#_label5">六、mysqldump 常用备份命令示例</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_24">6.1 MyISAM 表备份命令</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_25">6.2 InnoDB 表备份命令</a></li><ul class="third_class_ul"></ul><li><a href="#_lab2_5_26">6.3 在线备份命令（含 binlog 信息）</a></li><ul class="third_class_ul"><li><a href="#_label3_5_26_4">6.3.1 在线备份语法</a></li><li><a href="#_label3_5_26_5">6.3.2 在线备份特性</a></li></ul></ul><li><a href="#_label6">七、mysqldump 备份文件还原方法</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_27">7.1 方法一：直接用 mysql 客户端导入</a></li><ul class="third_class_ul"><li><a href="#_label3_6_27_6">7.1.1 导入语法</a></li></ul><li><a href="#_lab2_6_28">7.2 方法二：用 SOURCE 语法导入（实验不成功！！！）</a></li><ul class="third_class_ul"><li><a href="#_label3_6_28_7">7.2.1 语法说明</a></li><li><a href="#_label3_6_28_8">7.2.2 注意事项</a></li></ul></ul><li><a href="#_label7">总结 </a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>一、mysqldump 备份操作</h2><p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.1 备份基础语法</h3>
<div class="jb51code"><pre class="brush:ps;">mysqldump -u用户名 -p密码 -h主机 数据库 表名 -w "sql条件" --lock-all-tables > 备份路径
</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>1.2 备份案例</h3>
<div class="jb51code"><pre class="brush:ps;">mysqldump -uroot -p1234 -hlocalhost db1 a -w "id in (select id from b)" --lock-all-tables > c:\aa.txt
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>二、mysqldump 还原操作</h2>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>2.1 还原基础语法</h3>
<div class="jb51code"><pre class="brush:ps;">mysql -u用户名 -p密码 -h主机 数据库 < 备份文件路径
</pre></div>
<blockquote><p>注：原文中“mysqldump还原”语法表述存在笔误，正确还原需使用<code>mysql</code>命令而非<code>mysqldump</code></p></blockquote>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2.2 还原案例</h3>
<div class="jb51code"><pre class="brush:ps;">mysql -uroot -p1234 db1 < c:\aa.txt
</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>三、mysqldump 按条件导出与导入</h2>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>3.1 按条件导出</h3>
<p class="maodian"><a name="_label3_2_4_0"></a></p><h4>3.1.1 按条件导出基础语法</h4>
<div class="jb51code"><pre class="brush:ps;">mysqldump -u用户名 -p密码 -h主机 数据库 表名 --where "条件语句" --no-create-info > 导出路径
</pre></div>
<blockquote><p>注：原文中“–no-建表”为简化表述，标准参数为<code>--no-create-info</code></p></blockquote>
<p class="maodian"><a name="_label3_2_4_1"></a></p><h4>3.1.2 按条件导出案例</h4>
<div class="jb51code"><pre class="brush:ps;">mysqldump -uroot -p1234 dbname a --where "tag='88'" --no-create-info > c:\a.sql
</pre></div>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>3.2 按条件导入</h3>
<p class="maodian"><a name="_label3_2_5_2"></a></p><h4>3.2.1 按条件导入基础语法</h4>
<div class="jb51code"><pre class="brush:ps;">mysql -u用户名 -p密码 -h主机 数据库 < 导出文件路径
</pre></div>
<blockquote><p>注：原文中“mysqldump按导入”语法表述存在笔误，正确导入需使用<code>mysql</code>命令而非<code>mysqldump</code></p></blockquote>
<p class="maodian"><a name="_label3_2_5_3"></a></p><h4>3.2.2 按条件导入案例</h4>
<div class="jb51code"><pre class="brush:ps;">mysql -uroot -p1234 db1 < c:\a.txt
</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>四、mysqldump 表导出操作</h2>
<p class="maodian"><a name="_lab2_3_6"></a></p><h3>4.1 表导出基础语法</h3>
<div class="jb51code"><pre class="brush:ps;">mysqldump -u用户名 -p密码 -h主机 数据库 表名
</pre></div>
<p class="maodian"><a name="_lab2_3_7"></a></p><h3>4.2 表导出案例（仅导出表结构，不含数据）</h3>
<div class="jb51code"><pre class="brush:ps;">mysqldump -uroot -p sqlhk9 a --no-data
</pre></div>
<p class="maodian"><a name="_label4"></a></p><h2>五、mysqldump 主要参数说明</h2>
<p class="maodian"><a name="_lab2_4_8"></a></p><h3>5.1 --compatible=name</h3>
<ul><li>功能：告知 mysqldump 导出的数据需兼容的数据库类型或旧版本 MySQL 服务器</li><li>兼容值：ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等</li><li>说明：多值用逗号分隔，仅保证“尽量兼容”，非“完全兼容”</li></ul>
<p class="maodian"><a name="_lab2_4_9"></a></p><h3>5.2 --complete-insert，-c</h3>
<ul><li>功能：导出数据采用包含字段名的完整 INSERT 语句（所有值写在一行）</li><li>优势：提高插入效率</li><li>风险：可能受 max_allowed_packet 参数影响，导致插入失败，不推荐使用</li></ul>
<p class="maodian"><a name="_lab2_4_10"></a></p><h3>5.3 --default-character-set=charset</h3>
<ul><li>功能：指定导出数据的字符集</li><li>必要性：若数据表非默认 latin1 字符集，不指定此参数会导致再次导入后产生乱码</li></ul>
<p class="maodian"><a name="_lab2_4_11"></a></p><h3>5.4 --disable-keys</h3>
<ul><li>功能：在 INSERT 语句开头添加 <code>/*!40000 ALTER TABLE table DISABLE KEYS */;</code>，结尾添加 <code>/*!40000 ALTER TABLE table ENABLE KEYS */;</code></li><li>优势：插入完所有数据后再重建索引，大幅提高插入速度</li><li>限制：仅适用于 MyISAM 表</li></ul>
<p class="maodian"><a name="_lab2_4_12"></a></p><h3>5.5 --extended-insert = true|false</h3>
<ul><li>默认值：true（开启 --complete-insert 模式）</li><li>功能：关闭 --complete-insert 模式时，需将此参数设为 false</li></ul>
<p class="maodian"><a name="_lab2_4_13"></a></p><h3>5.6 --hex-blob</h3>
<ul><li>功能：使用十六进制格式导出二进制字符串字段</li><li>必要性：存在二进制数据时必须使用</li><li>影响字段类型：BINARY、VARBINARY、BLOB</li></ul>
<p class="maodian"><a name="_lab2_4_14"></a></p><h3>5.7 --lock-all-tables，-x</h3>
<ul><li>功能：开始导出前，请求锁定所有数据库的所有表，保证数据一致性</li><li>特性：属于全局读锁，会自动关闭 --single-transaction 和 --lock-tables 选项</li></ul>
<p class="maodian"><a name="_lab2_4_15"></a></p><h3>5.8 --lock-tables</h3>
<ul><li>功能：锁定当前导出的数据表（区别于 --lock-all-tables 锁定全部库下的表）</li><li>限制：仅适用于 MyISAM 表；InnoDB 表需使用 --single-transaction 选项</li></ul>
<p class="maodian"><a name="_lab2_4_16"></a></p><h3>5.9 --no-create-info，-t</h3>
<ul><li>功能：仅导出数据，不添加 CREATE TABLE 语句</li></ul>
<p class="maodian"><a name="_lab2_4_17"></a></p><h3>5.10 --no-data，-d</h3>
<ul><li>功能：不导出任何数据，仅导出数据库表结构</li></ul>
<p class="maodian"><a name="_lab2_4_18"></a></p><h3>5.11 --opt</h3>
<ul><li>本质：快捷选项，等同于同时添加以下参数：<br />–add-drop-tables、–add-locking、–create-option、–disable-keys、–extended-insert、–lock-tables、–quick、–set-charset</li><li>优势：加快导出速度，且导出数据可快速导回</li><li>默认状态：默认启用，可通过 --skip-opt 禁用</li><li>注意事项：未指定 --quick 或 --opt 时，会将整个结果集放入内存，导出大数据库可能出现问题</li></ul>
<p class="maodian"><a name="_lab2_4_19"></a></p><h3>5.12 --quick，-q</h3>
<ul><li>功能：强制 mysqldump 从服务器查询取得记录后直接输出，不缓存到内存</li><li>适用场景：导出大表时非常有用，避免占用过多内存</li></ul>
<p class="maodian"><a name="_lab2_4_20"></a></p><h3>5.13 --routines，-R</h3>
<ul><li>功能：导出存储过程以及自定义函数</li></ul>
<p class="maodian"><a name="_lab2_4_21"></a></p><h3>5.14 --single-transaction</h3>
<ul><li>功能：导出数据前提交 BEGIN SQL 语句，保证导出时数据库的一致性状态</li><li>特性：BEGIN 不阻塞任何应用程序</li><li>适用表类型：仅适用于事务表（如 InnoDB、BDB）</li><li>互斥性：与 --lock-tables 互斥（LOCK TABLES 会使挂起事务隐含提交）</li><li>建议：导出大表时结合 --quick 选项使用</li></ul>
<p class="maodian"><a name="_lab2_4_22"></a></p><h3>5.15 --triggers</h3>
<ul><li>功能：同时导出触发器</li><li>默认状态：默认启用，可通过 --skip-triggers 禁用</li></ul>
<p class="maodian"><a name="_lab2_4_23"></a></p><h3>5.16 其他参数说明</h3>
<p>其他参数详情请参考 MySQL 官方手册</p>
<p class="maodian"><a name="_label5"></a></p><h2>六、mysqldump 常用备份命令示例</h2>
<p class="maodian"><a name="_lab2_5_24"></a></p><h3>6.1 MyISAM 表备份命令</h3>
<div class="jb51code"><pre class="brush:ps;">/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
</pre></div>
<p class="maodian"><a name="_lab2_5_25"></a></p><h3>6.2 InnoDB 表备份命令</h3>
<div class="jb51code"><pre class="brush:ps;">/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
</pre></div>
<p class="maodian"><a name="_lab2_5_26"></a></p><h3>6.3 在线备份命令（含 binlog 信息）</h3>
<p class="maodian"><a name="_label3_5_26_4"></a></p><h4>6.3.1 在线备份语法</h4>
<div class="jb51code"><pre class="brush:ps;">/usr/local/mysql/bin/mysqldump -uyejr -pyejr \
--default-character-set=utf8 --opt --master-data=1 \
--single-transaction --flush-logs db_name > db_name.sql
</pre></div>
<p class="maodian"><a name="_label3_5_26_5"></a></p><h4>6.3.2 在线备份特性</h4>
<ul><li>仅在开始瞬间请求锁表，随后刷新 binlog</li><li>导出文件中会加入 CHANGE MASTER 语句，指定当前备份的 binlog 位置</li><li>适用场景：将备份文件恢复到 slave 服务器</li></ul>
<p class="maodian"><a name="_label6"></a></p><h2>七、mysqldump 备份文件还原方法</h2>
<p>mysqldump 备份文件为可直接导入的 SQL 脚本，共两种导入方法：</p>
<p class="maodian"><a name="_lab2_6_27"></a></p><h3>7.1 方法一：直接用 mysql 客户端导入</h3>
<p class="maodian"><a name="_label3_6_27_6"></a></p><h4>7.1.1 导入语法</h4>
<div class="jb51code"><pre class="brush:ps;">/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql
</pre></div>
<p class="maodian"><a name="_lab2_6_28"></a></p><h3>7.2 方法二：用 SOURCE 语法导入（实验不成功！！！）</h3>
<p class="maodian"><a name="_label3_6_28_7"></a></p><h4>7.2.1 语法说明</h4>
<ul><li>非标准 SQL 语法，属于 mysql 客户端提供的功能</li><li>导入语法：<div class="jb51code"><pre class="brush:sql;">SOURCE /tmp/db_name.sql;
</pre></div></li></ul>
<p class="maodian"><a name="_label3_6_28_8"></a></p><h4>7.2.2 注意事项</h4>
<ul><li>需指定文件绝对路径</li><li>文件需让 mysqld 运行用户（如 nobody）拥有读取权限</li></ul>
<p class="maodian"><a name="_label7"></a></p><h2>总结 </h2>
頁:
[1]