Hack The Sec-Leading Resource Of Linux Tutorial: Mysql-Server

How to Create, List or Drop Indexes on MySQL Table

<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;">
<u><span style="color: #990000;">Create, List or Drop Indexes on MySQL Table</span></u></h2>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><a href="http://www.hackthesec.co.in/search/label/Mysql_Database">MySQL </a>is an open-source relational database management system (RDBMS); in July 2013, it was the world's second most widely used RDBMS, and the most widely used open-source client–server model RDBMS.It is named after Michael Widenius' (who is a co-founder of <a href="http://www.hackthesec.co.in/search/label/Mysql">MySQL</a>) daughter, while "SQL" stands as the abbreviation for Structured Query Language. </span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglu6BHfIUQ1AeBTzwlsKiSNhaOqYuocrXR44DVliL0FgZSkJCAMrliMI-GKV_mNDYKbTqCkPXivMsMMn6tr2Isb_RqaVSQ-laoP5agXcTDjkayCuJXQgiZiDvAHJB4MimneZ_d3aYZ9nD9/s1600/mysq-linux-www.hackthesec.co.in.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="233" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglu6BHfIUQ1AeBTzwlsKiSNhaOqYuocrXR44DVliL0FgZSkJCAMrliMI-GKV_mNDYKbTqCkPXivMsMMn6tr2Isb_RqaVSQ-laoP5agXcTDjkayCuJXQgiZiDvAHJB4MimneZ_d3aYZ9nD9/s320/mysq-linux-www.hackthesec.co.in.jpg" width="320" /></a></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><br /></span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation. For proprietary use, several paid editions are available, and offer additional functionality.</span></span></div>
<div>
<div style="background-color: white;">
<div>
<span style="color: #0b5394; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><b><u>CREATE INDEX:-</u></b></span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">This will creates index on mysql table. This will allow duplicate values also.</span></span></div>
</div>
<div style="background-color: white;">
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-size: medium;"><span style="line-height: 20.8px;">CREATE INDEX index_name ON table_name (COLUMN_NAME1, COLUMN_NAME2, ...)</span></span></pre>
</div>
</div>
<div>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><div>
<b><u><span style="color: #0b5394;">CREATE UNIQUE INDEX:-</span></u></b></div>
<div style="color: #333333;">
This will creates index on mysql table. With uses of UNIQUE keyword this will not allow duplicate values.</div>
</span></span></div>
<div>
<div>
<div style="background-color: white;">
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-size: medium;"><span style="line-height: 20.8px;">CREATE UNIQUE INDEX index_name ON table_name (COLUMN_NAME1, COLUMN_NAME2, ...)</span></span></pre>
</div>
</div>
</div>
<div>
<div style="font-family: verdana, geneva, sans-serif; font-size: 15px; line-height: 26px;">
<b><u><span style="color: #0b5394;">1. CREATE INDEX Examples:-</span></u></b></div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px; line-height: 26px;">
For this example I have a table named Users with thousands of records in mysql database. The table structure is as following in screenshot.</div>
<div class="separator" style="clear: both; font-family: verdana, geneva, sans-serif; font-size: 15px; line-height: 26px; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3z2qQOnt4I3LKrqoI_EozExpVygth-cm1ix67-oPGafGtvzuOPi4bhw34QPRhDrky4PBw0me1PMR4EvN3Ya-D90I7PJewb3gs75DprQqbNjeQvgj-B-nTUFjsddzj0EAv2eB6zhKJGpTb/s1600/mysql-index-hackthesec.co.in.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3z2qQOnt4I3LKrqoI_EozExpVygth-cm1ix67-oPGafGtvzuOPi4bhw34QPRhDrky4PBw0me1PMR4EvN3Ya-D90I7PJewb3gs75DprQqbNjeQvgj-B-nTUFjsddzj0EAv2eB6zhKJGpTb/s1600/mysql-index-hackthesec.co.in.png" /></a></div>
<div style="font-family: verdana, geneva, sans-serif;">
<span style="color: #333333;"><span style="font-size: 15px; line-height: 26px;">Now use following command to create index named Users_Idx1 on table Users based on column username.</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-size: medium;"><span style="line-height: 20.8px;">mysql> CREATE INDEX Users_Idx1 ON Users (username);</span></span></pre>
</div>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Use UNIQUE keyword with the query to create index with unique records.</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-size: medium;"><span style="line-height: 20.8px;">mysql> CREATE UNIQUE INDEX Users_Idx1 ON Users (username);</span></span></pre>
</div>
<div>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><div>
<b><span style="color: #0b5394;"><u>2. SHOW INDEX Examples:-</u></span></b></div>
<div style="color: #333333;">
Use following sql command to list all indexes for a mysql table. The below query will delete Users_Idx1 index from Users table in current database.</div>
</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-size: medium;"><span style="line-height: 20.8px;">mysql> SHOW INDEX FROM Users;</span></span></pre>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDYacfjmKXhzjowDlol4bz38hgMHGasimYiSfGQH9qubNrw8FR3xCKJLO27uVZu1cuC-qxlmMQpTA_VOXbih3VO5ztw4sMc7VmsqpUzfoQMx_J5JgmL4_Dz6ArVx37e7Mf9B27ypYr99pr/s1600/mysql-index2-hackthesec.co.in.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDYacfjmKXhzjowDlol4bz38hgMHGasimYiSfGQH9qubNrw8FR3xCKJLO27uVZu1cuC-qxlmMQpTA_VOXbih3VO5ztw4sMc7VmsqpUzfoQMx_J5JgmL4_Dz6ArVx37e7Mf9B27ypYr99pr/s1600/mysql-index2-hackthesec.co.in.png" /></a></div>
<div>
<br /></div>
<div>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><div>
<b><u><span style="color: #0b5394;">3. DELETE INDEX Examples:-</span></u></b></div>
<div style="color: #333333;">
Use following sql command to delete index on mysql table.</div>
</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-size: medium;"><span style="line-height: 20.8px;">mysql>  ALTER TABLE Users DROP INDEX Users_Idx1;</span></span></pre>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-size: medium;"><span style="line-height: 20.8px;"><a href="http://www.hackthesec.co.in/">www.hackthesec.co.in</a></span></span></pre>
</div>
</div>

How to Create, List or Drop Indexes on MySQL Table

Create, List or Drop Indexes on MySQL Table MySQL is an open-source relational database management system (RDBMS); in July 2013, it was ...

How To Retrieve or Reset the Root MySQL Password

<div dir="ltr" style="text-align: left;" trbidi="on">
<h1 class="entry-title" style="background-color: white; border: 0px; box-sizing: inherit; font-family: proxima-nova, sans-serif; line-height: 3.4rem; margin: 0px; outline: 0px; padding: 0px; vertical-align: baseline;">
<span style="color: #990000; font-size: large;"><u>Retrieve or Reset the Root MySQL Password</u></span></h1>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">MySQL AB was a software company that was founded in 1995. It was acquired by Sun Microsystems in 2008; Sun was in turn acquired by Oracle Corporation in 2010.</span></span></div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">This is a quick tutorial how to reset your <a href="http://www.hackthesec.co.in/search/label/Mysql_Database">MySQL</a> root password. It does require root access on your server. </span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5sGSkdiY6rimv0IB8iREbyy1vyE5VOacsLnlh5LPLdoInHSPOaxVR6h0TApVBc3Zh5ovP0Cfr-XsJn1tFthuOYMnwyYupGoPn3zjvrfrowqAwqbs32Qj4qTAIiFnKjAM7v6E7TLETS7el/s1600/reset-mysql-root-password-www.hackthesec.co.in.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5sGSkdiY6rimv0IB8iREbyy1vyE5VOacsLnlh5LPLdoInHSPOaxVR6h0TApVBc3Zh5ovP0Cfr-XsJn1tFthuOYMnwyYupGoPn3zjvrfrowqAwqbs32Qj4qTAIiFnKjAM7v6E7TLETS7el/s1600/reset-mysql-root-password-www.hackthesec.co.in.jpg" /></a></div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><br /></span></span></div>
<div>
<span style="color: #0b5394; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><b><u>Step #1</u></b></span></span></div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Stop MySQL:</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: trebuchet ms, sans-serif;"><span style="font-size: 15px; line-height: 15.6px;">service mysql stop</span></span></pre>
</div>
<div>
<b style="color: #0b5394; font-family: verdana, geneva, sans-serif; font-size: 15px; line-height: 26px;"><u>Step #2</u></b></div>
<div>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Start MySQL in single-user mode, bypassing password authorization, by pasting in the command below (the ampersand is required):</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: trebuchet ms, sans-serif;"><span style="font-size: 15px; line-height: 15.6px;">mysqld_safe --skip-grant-tables &</span></span></pre>
</div>
<div>
<b style="color: #0b5394; font-family: verdana, geneva, sans-serif; font-size: 15px; line-height: 26px;"><u>Step #3</u></b></div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Now type "mysql" to connect:</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: trebuchet ms, sans-serif;"><span style="font-size: 15px; line-height: 15.6px;">mysql</span></span></pre>
</div>
<div>
<b style="color: #0b5394; font-family: verdana, geneva, sans-serif; font-size: 15px; line-height: 26px;"><u>Step #4</u></b></div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">At the mysql prompt, change the root password using the following three commands:</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: trebuchet ms, sans-serif;"><span style="font-size: 15px; line-height: 15.6px;">UPDATE mysql.user SET password=password("YourNewStrongPassword") WHERE user='root';
FLUSH PRIVILEGES;
exit;</span></span></pre>
</div>
<div>
<b style="color: #0b5394; font-family: verdana, geneva, sans-serif; font-size: 15px; line-height: 26px;"><u>Step #4</u></b></div>
<div>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Now stop MySQL and start it in normal mode:</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: trebuchet ms, sans-serif;"><span style="font-size: 15px; line-height: 15.6px;">service mysql stop
service mysql start</span></span></pre>
</div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><div>
<b style="color: #0b5394;"><u>Step #5</u></b></div>
<div>
Verify that you are able to connect to MySQL by entering "mysql".</div>
</span></span><span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><div>
<b style="color: #0b5394;"><u>Step #6</u></b></div>
<div>
Finally, update the MySQL root password in<b> /etc/.my.cnf</b> to match the new value. You can use vi, vim or nano:</div>
</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: trebuchet ms, sans-serif;"><span style="font-size: 15px; line-height: 15.6px;">vi /root/.my.cnf</span></span></pre>
</div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><b><u>Recovering the Root MySQL Password on a cPanel server</u></b></span></span></div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">The root <a href="http://www.hackthesec.co.in/search/label/Mysql">MySQL</a> credentials are stored locally in a file that only the root user can access. After you’ve connected to the server as root via <a href="http://www.hackthesec.co.in/search/label/SSH">SSH</a>, you can view the credentials by running the following command:</span></span></div>
<div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: trebuchet ms, sans-serif;"><span style="font-size: 15px; line-height: 15.6px;">cat /root/.my.cnf</span></span></pre>
</div>
<div>
<span style="color: #222222; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">That should output a result containing the MySQL password, which will appear similar to the following:</span></span></div>
<div>
<pre class="xtypo_info" style="background: url("assets/shadow.png") 50% 100% repeat-x rgb(245, 245, 245); border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-shadow: rgb(204, 204, 204) 0px 0px 3px; box-sizing: border-box; font-stretch: normal; margin-bottom: 10px; overflow: auto; padding: 9.5px; tab-size: 4; text-shadow: rgb(255, 255, 255) 0px 1px 0px; word-break: break-all; word-wrap: break-word;"><span style="color: #333333; font-family: menlo, monaco, consolas, courier new, monospace; font-size: large;"><span style="line-height: 18.5714px; white-space: pre-wrap;">[root@host ~]# cat /root/.my.cnf
[client]
password=#@hackthesec
user=root</span></span></pre>
<pre class="xtypo_info" style="background: url("assets/shadow.png") 50% 100% repeat-x rgb(245, 245, 245); border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-shadow: rgb(204, 204, 204) 0px 0px 3px; box-sizing: border-box; font-stretch: normal; margin-bottom: 10px; overflow: auto; padding: 9.5px; tab-size: 4; text-shadow: rgb(255, 255, 255) 0px 1px 0px; word-break: break-all; word-wrap: break-word;"><span style="color: #333333; font-family: menlo, monaco, consolas, courier new, monospace;"><span style="font-size: 12px; line-height: 18.5714px; white-space: pre-wrap;"><a href="http://www.hackthesec.co.in/">www.hackthesec.co.in</a>
<a href="https://www.twitter.com/Hackthesecurity">https://www.twitter.com/Hackthesecurity</a>
<a href="http://facebook.com/htsecu">http://facebook.com/htsecu</a>
<a href="http://feeds.hackthesec.co.in/hackthesec">http://feeds.hackthesec.co.in/hackthesec</a></span></span></pre>
</div>
</div>

How To Retrieve or Reset the Root MySQL Password

Retrieve or Reset the Root MySQL Password MySQL AB was a software company that was founded in 1995. It was acquired by Sun Microsystems ...

How to Backup and Restore a MySQL Database

<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...

MySQL Replication in 5 Minutes

<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #990000; font-family: "verdana" , sans-serif; font-size: x-large;">MySQL Replication in 5 Minutes</span><br />
<span style="font-family: "verdana" , sans-serif; font-size: x-large;"><br /></span>
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">Configure MySQL replication is extremely simple. This article demonstrates how to create a master in minutes replicating it for a slave. Replication is a native MySQL feature and has multiple uses, such as backup, high availability, redundancy and geographical data distribution, horizontal scalability, among others.</span></span><br />
<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" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuoxi5s0F8G1O0EBBaQZsBkS53hGtJLk_5eq5KH15vOG2iLfA9nuLja5cUU2fWhS1X4g5P4ZlYYQqXwgEsl-yiFo4igoYjJqXmPJwPtqi0z2OfxgfDGLmW_NcgzWNb-Bd7hqB52-V5rAxI/s1600/mysq-linux-www.hackthesec.co.in.jpg" /></a></div>
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;"><br /></span></span>
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">For this simple test, we use Linux and configure replication in MySQL 5.6 between two instances: a master and a slave. We will create two instances MySQL from scratch, that is, without data. They will be on the same machine, but responding in different ports: 3310 and 3311.</span></span><br />
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;"><br /></span></span>
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">The only requirement is to have MySQL 5.6 installed.</span></span><br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium; line-height: 23.1000003814697px;">If you have it installed, simply use the path where resides bin/mysqld as basedir in the steps below. For example, in Oracle Linux 7 or RHEL 7 the binary is located in </span><b style="font-family: arial, helvetica, sans-serif; font-size: large; line-height: 23.1000003814697px;"><span style="color: red;">/usr/sbin/mysqld</span></b><span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium; line-height: 23.1000003814697px;">, therefore</span><b style="font-family: arial, helvetica, sans-serif; font-size: large; line-height: 23.1000003814697px;"> <span style="color: red;">basedir=/usr;</span></b></li>
</ul>
<br />
<br />
<ul style="text-align: left;">
<li><span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium; line-height: 23.1000003814697px;"> If you do not have MySQL 5.6 binaries, just download the tar file and unpack it in a convenient directory that will be your basedir, such as</span><b style="font-family: arial, helvetica, sans-serif; font-size: large; line-height: 23.1000003814697px;"> <span style="color: red;">/opt/mysql/mysql-5.6</span></b><span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium; line-height: 23.1000003814697px;"><span style="color: red;">:</span></span></li>
</ul>
<br />
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white;"><span style="font-size: 12px; line-height: 15.6000003814697px;"># mkdir /opt/mysql
# cd /opt/mysql
# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
# tar xvzf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
# rm mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
# mv mysql-5.6.23-linux-glibc2.5-x86_64 mysql-5.6</span></span></pre>
<div style="background-color: white; margin-bottom: 10px;">
<span style="background-color: transparent; line-height: 18px;"><span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;">Note: In this case, consider the steps below <b><span style="color: red;">basedir=/opt/mysql/mysql-5.6;</span></b> always try to work with the latest versions — replace 5.6.23 in the above commands if a brand new one is available in <b><span style="color: red;">http://dev.mysql.com/downloads/mysql.</span></b></span></span></div>
<div style="background-color: white; margin-bottom: 10px;">
<span style="background-color: transparent;"></span><br />
<div style="font-family: arial, helvetica, sans-serif; font-size: large; font-weight: bold; line-height: 18px; margin-bottom: 10px;">
<span style="background-color: transparent;"><span style="color: #0b5394;"><u>Simple replication</u></span></span></div>
<span style="background-color: transparent;">
<div style="margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">Create an instance for the master:</span></span></div>
<div style="margin-bottom: 10px;">
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white;"><span style="line-height: 15.6000003814697px;"># mkdir /opt/mysql/master /opt/mysql/master/data /opt/mysql/master/tmp
# cd /opt/mysql/master
# nano master.cnf
[client]
port=3310
socket=/opt/mysql/master/tmp/my-master.sock
[mysql]
prompt=master>\\_
[mysqld]
server-id=10
port=3310
basedir=/usr
datadir=/opt/mysql/master/data
socket=/opt/mysql/master/tmp/my-master.sock
log-bin=master-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# chown mysql:mysql *
# /usr/bin/mysql_install_db --defaults-file=/opt/mysql/master/master.cnf --user=mysql</span></span></pre>
</div>
</span></div>
<div style="background-color: white; margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">Start and test the new instance:</span></span></div>
<div style="background-color: white; margin-bottom: 10px;">
</div>
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white;"><span style="font-size: 12px; line-height: 15.6000003814697px;"># /usr/bin/mysqld_safe --defaults-file=/opt/mysql/master/master.cnf &
# mysql --defaults-file=/opt/mysql/master/master.cnf -uroot -p
master> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3310 |
+---------------+-------+</span></span></pre>
<div style="background-color: white; margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.0999984741211px;">Note: To stop the MySQL process when necessary, make a clean shutdown:</span></span></div>
<div style="background-color: white; margin-bottom: 10px;">
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white;"><span style="font-size: 12px; line-height: 15.6000003814697px;"># mysqladmin --defaults-file=/opt/mysql/master/master.cnf -uroot –p shutdown</span></span></pre>
<div style="margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.0999984741211px;">Open another terminal and create another instance to be the Slave:</span></span></div>
<div style="margin-bottom: 10px;">
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white;"><span style="font-size: 11.9999990463257px; line-height: 15.6000003814697px;"># mkdir /opt/mysql/slave /opt/mysql/slave/data /opt/mysql/slave/tmp
# cd /opt/mysql/slave
# nano slave.cnf
[client]
port=3311
socket=/opt/mysql/slave/tmp/my-slave.sock
[mysql]
prompt=slave>\\_
[mysqld]
server-id=11
port=3311
basedir=/usr
datadir=/opt/mysql/slave/data
socket=/opt/mysql/slave/tmp/my-slave.sock
log-bin=slave-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
# chown mysql:mysql *
# /usr/bin/mysql_install_db --defaults-file=/opt/mysql/slave/slave.cnf --user=mysql
Start and test the new instance:
# /usr/bin/mysqld_safe --defaults-file=/opt/mysql/slave/slave.cnf &
# mysql --defaults-file=/opt/mysql/slave/slave.cnf -uroot -p
slave> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3311 |
+---------------+-------+</span></span></pre>
<div style="margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">Now that we have two instances with different server-ids and log-bin enabled, create a user in the master instance so the slave can connect to it:</span></span></div>
<div style="margin-bottom: 10px;">
<pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white;"><span style="font-size: 11.9999990463257px; line-height: 15.6000003814697px;">master> CREATE USER repl_user@127.0.0.1;
master> GRANT REPLICATION SLAVE ON *.* TO repl_user@127.0.0.1 IDENTIFIED BY 'repl_user_password';</span></span></pre>
<div style="margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">Note: In an actual installation, the slave instance will probably be in another host — replace 127.0.0.1 with the host IP where your slave instance is.</span></span></div>
<div style="margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 23.1000003814697px;">Before starting replication, check the Master status:</span></span></div>
<div style="margin-bottom: 10px;">
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white;"><span style="font-size: 11.9999990463257px; line-height: 15.6000003814697px;">master> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: master-bin.000003
Position: 433</span></span></pre>
<div style="margin-bottom: 10px;">
<div style="margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 18px;">Use the data status above and initiates replication on the slave:</span></span></div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: "verdana" , "geneva" , sans-serif;"><span style="font-size: 11.9999990463257px; line-height: 15.6000003814697px;">slave> CHANGE MASTER TO
MASTER_HOST=’127.0.0.1′,
MASTER_PORT=3310,
MASTER_USER=’repl_user’,
MASTER_PASSWORD=’repl_user_password’,
MASTER_LOG_FILE=’master-bin.000003′,
MASTER_LOG_POS=433;
slave> START SLAVE;</span></span></pre>
<div style="margin-bottom: 10px;">
<div style="margin-bottom: 10px;">
<span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium;"><span style="line-height: 18px;">Basic test</span></span><span style="font-family: "arial" , "helvetica" , sans-serif; font-size: medium; line-height: 1.5em;">:</span></div>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: "verdana" , "geneva" , sans-serif;"><span style="font-size: 11.9999990463257px; line-height: 15.6000003814697px;">master> CREATE DATABASE teste_repl;
master> CREATE TABLE teste_repl.simples (id INT NOT NULL PRIMARY KEY);
master> INSERT INTO teste_repl.simples VALUES (999),(1),(20),(5);
slave> SELECT * FROM teste_repl.simples;</span></span></pre>
<pre style="background-attachment: scroll; background-clip: initial; background-color: #3c3c3c; background-image: none; background-origin: initial; background-position: 0px 0px; background-repeat: repeat; background-size: initial; border-image-outset: initial; border-image-repeat: initial; border-image-slice: initial; border-image-source: initial; border-image-width: initial; border-radius: 4px; border: 1px solid rgb(229, 229, 229); margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; font-family: "verdana" , "geneva" , sans-serif;"><span style="font-size: 11.9999990463257px; line-height: 15.6000003814697px;"><b>References:</b>
http://dev.mysql.com/doc/refman/5.6/en/binary-installation.html
http://dev.mysql.com/doc/refman/5.6/en/replication-howto.html

<a href="http://www.hackthesec.co.in/" target="_blank">HackTheSecurity</a>
<a href="https://www.twitter.com/hackthesecurity" target="_blank">HackTheSecurity Twitter</a>
<a href="https://www.facebook.com/htsecu/" target="_blank">HackTheSecurity Facebook</a></span></span></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>

MySQL Replication in 5 Minutes

MySQL Replication in 5 Minutes Configure MySQL replication is extremely simple. This article demonstrates how to create a master in minut...