Hack The Sec-Leading Resource Of Linux Tutorial: Backup

<div dir="ltr" style="text-align: left;" trbidi="on">
<h2 class="title single-title title20" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 28px; line-height: 38px; margin: 0px 0px 5px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #990000;"><u><i>Backup and Restore a MySQL Database</i></u></span></h2>
<div>
<div style="background-color: white; border: 0px; font-size: 16px; line-height: 28px; margin-bottom: 20px; outline: 0px; padding: 0px; vertical-align: baseline;">
<span style="font-family: Georgia, Times New Roman, serif;"><span style="border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border: 0px; font-size: inherit; font-weight: 700; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">MySQL</span> is a database server for storing data permanently. If you are using <a href="http://www.hackthesec.co.in/search/label/Mysql" target="_blank">MySQL </a>server for an production use, then its necessary to create dataase backup for recovering from any crash. <a href="http://www.hackthesec.co.in/search/label/Mysql" target="_blank">MySQL </a>provides an utility <span style="border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border: 0px; font-size: inherit; font-weight: 700; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;"><a href="http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html" style="outline: none; text-decoration: none; transition: all 0.25s linear;" target="_blank">mysqldump</a> </span>for taking backups. In this article you will learn, to taken databases backup in<span style="border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border: 0px; font-size: inherit; font-weight: 700; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">.sql</span> format for archive format. Also we will explain various options for it.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuoxi5s0F8G1O0EBBaQZsBkS53hGtJLk_5eq5KH15vOG2iLfA9nuLja5cUU2fWhS1X4g5P4ZlYYQqXwgEsl-yiFo4igoYjJqXmPJwPtqi0z2OfxgfDGLmW_NcgzWNb-Bd7hqB52-V5rAxI/s1600/mysq-linux-www.hackthesec.co.in.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="145" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuoxi5s0F8G1O0EBBaQZsBkS53hGtJLk_5eq5KH15vOG2iLfA9nuLja5cUU2fWhS1X4g5P4ZlYYQqXwgEsl-yiFo4igoYjJqXmPJwPtqi0z2OfxgfDGLmW_NcgzWNb-Bd7hqB52-V5rAxI/s200/mysq-linux-www.hackthesec.co.in.jpg" width="200" /></a></div>
<div style="background-color: white; border: 0px; font-size: 16px; line-height: 28px; margin-bottom: 20px; outline: 0px; padding: 0px; vertical-align: baseline;">
<span style="color: #0b5394; font-family: 'Roboto Slab'; font-size: 24px; font-weight: bolder; line-height: 34px;">Options for Creating <a href="http://www.hackthesec.co.in/search/label/Mysql" target="_blank">MySQL </a>Databases Backup</span></div>
<div style="background-color: white; border: 0px; font-size: 16px; line-height: 28px; margin-bottom: 20px; outline: 0px; padding: 0px; vertical-align: baseline;">
<span style="font-family: Georgia, Times New Roman, serif;">You have many options for creating databases backups. read few options below. For this example we have using databse name “<b>mydb</b>”.</span></div>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">1. Full Database Backup in Plain .sql File</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"> # mysqldump -u root -p </span><span style="color: lime;">mydb > mydb.sql</span><span style="color: white;">
</span></span></pre>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">2. Full Database Backup in Archive</span><span style="color: red;"> .sql.gz File</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"> # mysqldump -u root -p mydb | </span><span style="color: lime;"><orange>gzip</orange> </span><span style="color: white;">> mydb.sql.gz
</span></span></pre>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">3. Backup Single Table Only</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"> # mysqldump -u root -p </span><span style="color: lime;">mydb <orange>tbl_student</orange></span><span style="color: white;"> > tbl_student.sql
</span></span></pre>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">4. Backup Multiple Databases</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"># mysqldump -u root -p </span><orange><span style="color: lime;">--databases mydb1 mydb2 mydb3</span></orange><span style="color: white;"> > mydb1-mydb2-mydb3.sql
</span></span></pre>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">5. Backup All Databases</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"> # mysqldump -u root -p </span><orange><span style="color: lime;">--all-databases</span></orange><span style="color: white;"> > all-db-backup.sql
</span></span></pre>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">6. Backup Database Structure Only (no data)</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"> # mysqldump -u root -p </span><span style="color: lime;"><orange>--no-data</orange> mydb > mydb.sql</span><span style="color: white;">
</span></span></pre>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">7. Backup Database Data Only (no table structure)</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"> # mysqldump -u </span><span style="color: lime;">root -p <orange>--no-create-info</orange></span><span style="color: white;"> mydb > mydb.sql
</span></span></pre>
<h3 class="heading2" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 20px; font-weight: bolder; line-height: 30px; margin: 0px 0px 8px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">8. Backup MySQL Database in XML Format</span></h3>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"> # </span><span style="color: lime;">mysqldump -u root -p <orange>--xml</orange></span><span style="color: white;"> mydb > mydb.xml
</span></span></pre>
<h2 class="heading1" style="background-color: white; border: 0px; font-family: 'Roboto Slab'; font-size: 24px; font-weight: bolder; line-height: 34px; margin: 45px 0px 12px; opacity: 1; outline: 0px; padding: 0px; transition: opacity 0.24s ease-in-out; vertical-align: baseline; visibility: visible;">
<span style="color: #0b5394;">How to Restore MySQL Backup</span></h2>
<div style="background-color: white; border: 0px; font-size: 16px; line-height: 28px; margin-bottom: 20px; outline: 0px; padding: 0px; vertical-align: baseline;">
<span style="font-family: Georgia, Times New Roman, serif;">For restoring databases from backup is quite simple. We use mysql command for it. For example following command will restore all backup from <b>mydb.sql</b> to <b>mydb database</b>.</span></div>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); line-height: 1.3em; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="font-family: Courier New, Courier, monospace; font-size: large;"><span style="color: white;"># mysql</span><span style="color: lime;"> -u root -p mydb</span><span style="color: white;"> < mydb.sql

HackTheSec
<a href="https://www.twitter.com/hackthesecurity" target="_blank">Hack The Sec Twitter</a>
<a href="http://www.hackthesec.co.in/">www.hackthesec.co.in</a></span></span></pre>
</div>
</div>

How to Backup and Restore a MySQL Database

Backup and Restore a MySQL Database MySQL is a database server for storing data permanently. If you are using MySQL server for an prod...