Mysql数据库备份和还原常用的命令

发布时间：2019-04-13浏览次数：1019 <p> </p> <table style="BORDER-RIGHT: #cccccc 1px dotted; TABLE-LAYOUT: fixed; BORDER-TOP: #cccccc 1px dotted; BORDER-LEFT: #cccccc 1px dotted; BORDER-BOTTOM: #cccccc 1px dotted" cellspacing="0" cellpadding="6" width="95%" align="center" border="0"><tbody><tr> <td style="WORD-WRAP: break-word" bgcolor="#fdfddf"> <font color="#ff0000">WebjxCom提示：</font><font color="#000000">Mysql数据库备份和还原常用的命令.</font> </td> </tr></tbody></table> <u><strong><font color="#617b85">Mysql</font></strong></u>数据库备份和还原常用的命令是进行Mysql数据库备份和还原的关键，没有命令，<u><strong><font color="#617b85">什</font></strong></u>么都无从做起，更谈不上什么备份还原，只有给系统这个命令，让它去执行，才能完成Mysql数据库备份和还原的操作，下面就是操作的常用命令。 <p><strong>一、备份命令</strong></p> <p>1、备份MySQL数据库的命令</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">hhostname</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">ppassword databasename</span><span style="COLOR: #808080">&gt;</span><span style="COLOR: #000000">backupfile.sql</span> </div> </div> <p>2、备份MySQL数据库为带删除表的格式</p> <p>备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump</span><span style="COLOR: #000000">-</span><span style="COLOR: #000000">&#8211;add</span><span style="COLOR: #000000">-</span><span style="COLOR: #000000">drop</span><span style="COLOR: #000000">-</span><span style="COLOR: #000000">table</span><span style="COLOR: #000000">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #000000">-</span><span style="COLOR: #000000">ppassword databasename</span><span style="COLOR: #000000">&gt;</span><span style="COLOR: #000000">backupfile.sql</span> </div> </div> <p>3、直接将MySQL数据库压缩备份</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">hhostname</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">ppassword databasename</span><span style="COLOR: #808080">|</span><span style="COLOR: #000000">gzip</span><span style="COLOR: #808080">&gt;</span><span style="COLOR: #000000">backupfile.sql.gz</span> </div> </div> <p>4、备份MySQL数据库某个(些)表</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">hhostname</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">ppassword databasename specific_table1 specific_table2</span><span style="COLOR: #808080">&gt;</span><span style="COLOR: #000000">backupfile.sql</span> </div> </div> <p>5、同时备份多个MySQL数据库</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">hhostname</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">ppassword &#8211;databases databasename1 databasename2 databasename3</span><span style="COLOR: #808080">&gt;</span><span style="COLOR: #000000">multibackupfile.sql</span> </div> </div> <p>6、仅仅备份数据库结构</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump &#8211;no</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">data &#8211;databases databasename1 databasename2 databasename3</span><span style="COLOR: #808080">&gt;</span><span style="COLOR: #000000">structurebackupfile.sql</span> </div> </div> <p>7、备份<u><strong><font color="#617b85">服务器</font></strong></u>上所有数据库</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump &#8211;</span><span style="COLOR: #808080">all-</span><span style="COLOR: #000000">databases</span><span style="COLOR: #808080">&gt;</span><span style="COLOR: #000000">allbackupfile.sql</span> </div> </div> <p><strong>二、还原命令</strong></p> <p>1、还原MySQL数据库的命令</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysql</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">hhostname</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">ppassword databasename</span><span style="COLOR: #808080">&lt;</span><span style="COLOR: #000000">backupfile.sql</span> </div> </div> <p>2、还原压缩的MySQL数据库</p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　gunzip</span><span style="COLOR: #808080">&lt;</span><span style="COLOR: #000000">backupfile.sql.gz</span><span style="COLOR: #808080">|</span><span style="COLOR: #000000">mysql</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">ppassword databasename</span> </div> </div> <p>3、将数据库转移到新<font color="#000000">服务器</font></p> <div style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 97%; PADDING-RIGHT: 5.4pt; BACKGROUND: #e6e6e6; WORD-BREAK: break-all; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 4px" id="div_code"> <style type="text/css"> #div_code img{border:0px;}</style> <div> <!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />--><span style="COLOR: #000000">　　mysqldump</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">uusername</span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">ppassword databasename</span><span style="COLOR: #808080">|</span><span style="COLOR: #000000">mysql &#8211;host</span><span style="COLOR: #808080">=*</span><span style="COLOR: #000000">.</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">.</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000">.</span><span style="COLOR: #808080">*</span><span style="COLOR: #000000"></span><span style="COLOR: #808080">-</span><span style="COLOR: #000000">C databasename</span> </div> </div> <p>总结：</p> <p>做好数据备份和还原，定好合适的备份策略，这是一个DBA所做事情的一小部分，万事开头难，就从现在开始吧!</p>