Hack The Sec-Leading Resource Of Linux Tutorial: Mysql

How To Install MySQL Server on Ubuntu 22.04

<p style="text-align: center;"><span style="color: #2b00fe; font-family: verdana; font-size: large;"><b><u>How To Install MySQL Server on Ubuntu 22.04</u></b></span></p><p><span style="font-family: verdana; font-size: large;">MySQL is an open-source relational database management system (RDBMS).Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A relational database organizes data into one or more data tables in which data may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database. In addition to relational databases and SQL, an RDBMS like MySQL works with an operating system to implement a relational database in a computer's storage system, manages users, allows for network access and facilitates testing database integrity and creation of backups.</span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqb_IjkqiXKg6rDmYejq7XSHARNEO7FQeNRUS_wOIn21jrjNbbAU7E6G9eRLRdYqi_Rde4_MExFxTI7vEQLKUepAv77VJY1ZcxLbpmQ-UaCqNfiVGecOeUXAnJxJOQqO-mHBETDm6S0oRb/s698/mysql-hackthesec.png" style="margin-left: 1em; margin-right: 1em;"><span style="font-size: large;"><img border="0" data-original-height="400" data-original-width="698" height="366" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqb_IjkqiXKg6rDmYejq7XSHARNEO7FQeNRUS_wOIn21jrjNbbAU7E6G9eRLRdYqi_Rde4_MExFxTI7vEQLKUepAv77VJY1ZcxLbpmQ-UaCqNfiVGecOeUXAnJxJOQqO-mHBETDm6S0oRb/w640-h366/mysql-hackthesec.png" width="640" /></span></a></div><span style="font-size: large;"><br /><span style="font-family: verdana;"><br /></span></span><p></p><p><span style="color: #2b00fe; font-family: verdana; font-size: large;"><b>Step 1 – Installing MySQL on Ubuntu 22.04</b></span></p><p><span style="font-family: verdana; font-size: large;">The default Ubuntu repositories contain MySQL 8.0. Which can be installed directly using the package manager without adding third-party PPA.</span></p><p><span style="font-family: verdana; font-size: large;">To install the available MySQL server version, execute the following command.</span></p><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="font-family: verdana; font-size: large;"><span style="color: #333333;"><span style="color: white; line-height: 15.6px;">[root@hackthesec]# </span></span><span style="color: white;">apt-get install mysql-server</span></span></pre><p><span style="font-family: verdana; font-size: large;">Press 'y' for any confirmation asked by the installer.</span></p><p><span style="font-family: verdana; font-size: large;">Once the installation is finished, you can secure the MySQL server by executing the following command.</span></p><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="font-family: verdana; font-size: large;"><span style="color: #333333;"><span style="color: white; line-height: 15.6px;">[root@hackthesec]# </span></span><span style="color: white;">sudo mysql_secure_installation</span></span></pre><p><span style="font-family: verdana; font-size: large;">You will go through a wizard of questions to secure the MySQL server. Follow the onscreen instructions below:</span></p><pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); color: #333333; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; line-height: 15.6px;"><span style="font-family: verdana; font-size: large;">[root@hackthesec]#</span></span></pre><p><span style="font-family: verdana; font-size: large;">Press ‘y’ to enable validate password plugin. This will allow you to set a strict password policy for user accounts.</span></p><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; font-family: verdana; font-size: large;"><span style="line-height: 15.6px;">[root@hackthesec]# </span>VALIDATE PASSWORD COMPONENT can be used to test passwords</span><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">and improve security. It checks the strength of password</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">and allows the users to set only those passwords which are</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">secure enough. Would you like to setup VALIDATE PASSWORD component?</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;"><br /></span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Press y|Y for Yes, any other key for No: y</span></p><span style="color: #333333;"><span style="color: white; line-height: 15.6px;"><span style="font-family: verdana; font-size: large;">

</span></span></span></pre><p><span style="font-family: verdana; font-size: large;">Chose the password complexity level. Read the all 3 options and choose one:</span></p><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; font-family: verdana;"><span style="font-size: large; line-height: 15.6px;">[root@hackthesec]# LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters

STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1 </span></span></pre><p><span style="font-family: verdana; font-size: large;">Enter a new password and re-enter it. Make sure it matches the complexity level as described above.</span></p><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; font-family: verdana;"><span style="font-size: large; line-height: 15.6px;">[root@hackthesec]# 
</span></span><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">New password: *************</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Re-enter new password: *************</span></p><span style="color: white; font-family: verdana; font-size: large;">Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y</span></pre><p><span style="color: #2b00fe; font-family: verdana; font-size: large;"><b>Step 2 – Connect to MySQL Server</b></span></p><p><span style="font-family: verdana; font-size: large;">Remember that the above password set for the root accounts is used for remote users only. To log in from the same system, just type mysql on terminal.</span></p><pre style="background: none 0px 0px repeat scroll rgb(60, 60, 60); border-radius: 4px; border: 1px solid rgb(229, 229, 229); color: #333333; margin-bottom: 20px; outline: 0px; overflow: auto; padding: 20px; vertical-align: baseline;"><span style="color: white; line-height: 15.6px;"><span style="font-family: verdana; font-size: large;">[root@hackthesec]# sudo mysql</span></span></pre><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;"><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Welcome to the MySQL monitor.  Commands end with ; or \g.</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Your MySQL connection id is 14</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Server version: 8.0.28-0ubuntu4 (Ubuntu)</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;"><br /></span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Copyright (c) 2000, 2022, Oracle and/or its affiliates.</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;"><br /></span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Oracle is a registered trademark of Oracle Corporation and/or its</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">affiliates. Other names may be trademarks of their respective</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">owners.</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;"><br /></span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;"><br /></span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">mysql></span></p><span style="color: #333333;"><span style="color: white; line-height: 15.6px;"><span style="font-family: verdana; font-size: large;">
</span></span></span></pre><p><span style="color: #2b00fe; font-family: verdana; font-size: large;"><b>Step 3 – Creating Database and Users</b></span></p><p><span style="font-family: verdana; font-size: large;">Here is few example queries to create database and users in MySQL server.</span></p><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: #333333;"><span style="color: white; line-height: 15.6px;"><span style="font-family: verdana; font-size: large;">[root@hackthesec]#
</span></span></span><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Create a database named ‘hackthesec’.</span></p><p style="white-space: normal;"><span style="font-family: verdana; font-size: large;"><span style="color: white;">CREATE DATABASE </span><span style="color: white;">hackthesec</span><span style="color: white;">; </span></span></p><p style="white-space: normal;"><span style="font-family: verdana; font-size: large;"><span style="color: white;">Next, create a user named ‘</span><span style="color: white;">hackthesec</span><span style="color: white;">’ accessible from ‘localhost’ only.</span></span></p><p style="white-space: normal;"><span style="font-family: verdana; font-size: large;"><span style="color: white;">CREATE USER '</span><span style="color: white;">hackthesec</span><span style="color: white;">'@'localhost' IDENTIFIED BY 'Secure321'; </span></span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Grant permissions on database to user.</span></p><p style="white-space: normal;"><span style="font-family: verdana; font-size: large;"><span style="color: white;">GRANT ALL ON mydb.* to '</span><span style="color: white;">hackthesec</span><span style="color: white;">'@'localhost'; </span></span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">Apply the permission changes at runtime.</span></p><p style="white-space: normal;"><span style="color: white; font-family: verdana; font-size: large;">FLUSH PRIVILEGES; </span></p></pre><p><span style="font-family: verdana; font-size: large;"><br /></span></p><p><span style="color: #2b00fe; font-family: verdana; font-size: large;"><b>Step 4 – Manage MySQL Service</b></span></p><p><span style="font-family: verdana; font-size: large;">To check the database server status.</span></p><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="font-family: verdana; font-size: large;"><span style="color: #333333;"><span style="color: white; line-height: 15.6px;">[root@hackthesec]# </span></span><span style="color: white;">sudo systemctl status mysql</span></span></pre><p><span style="font-family: verdana; font-size: large;">Use below command to start MySQL server.</span></p><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="font-family: verdana; font-size: large;"><span style="color: #333333;"><span style="color: white; line-height: 15.6px;">[root@hackthesec]# </span></span><span style="color: white;">sudo systemctl start mysql</span></span></pre><p><span style="font-family: verdana; font-size: large;">To stop MySQL server:</span></p><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="font-family: verdana; font-size: large;"><span style="color: #333333;"><span style="color: white; line-height: 15.6px;">[root@hackthesec]# </span></span><span style="color: white;">sudo systemctl stop mysql</span></span></pre><p><span style="font-family: verdana; font-size: large;">Restart MySQL database server, type:</span></p><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="font-family: verdana; font-size: large;"><span style="color: #333333;"><span style="color: white; line-height: 15.6px;">[root@hackthesec]# </span></span><span style="color: white;">sudo systemctl restart mysql</span></span></pre><p><span style="font-size: large;"><br /></span></p><p><span style="font-family: verdana; font-size: large;">@hackthesecurity</span></p>

How To Install MySQL Server on Ubuntu 22.04

How To Install MySQL Server on Ubuntu 22.04 MySQL is an open-source relational database management system (RDBMS).Its name is a combination ...

Executing MySQL Queries Directly From the Command Line

<div dir="ltr" style="text-align: left;" trbidi="on">
<h1 class="entry-title" style="background-color: white; box-sizing: border-box; color: #1e73be; font-family: Cabin; line-height: 50px; margin: 0px 0px 7px; word-wrap: break-word;">
<span style="font-size: large;"><u>Executing MySQL Queries Directly From the Command Line</u></span></h1>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px;">MySQL is a central component of the LAMP open-source web application software stack (and other "AMP" stacks). <a href="http://www.hackthesec.co.in/search/label/LAMP">LAMP</a> is an acronym for "<a href="http://www.hackthesec.co.in/search/label/Linux%20Tutorial">Linux</a>, <a href="http://www.hackthesec.co.in/search/label/Apache">Apache</a>, <a href="http://www.hackthesec.co.in/search/label/Mysql_Database">MySQL</a>, </span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px;"><br /></span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px;">Perl/PHP/Python". Applications that use the MySQL database include: TYPO3, MODx, Joomla, WordPress, phpBB, MyBB, and Drupal.</span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqb_IjkqiXKg6rDmYejq7XSHARNEO7FQeNRUS_wOIn21jrjNbbAU7E6G9eRLRdYqi_Rde4_MExFxTI7vEQLKUepAv77VJY1ZcxLbpmQ-UaCqNfiVGecOeUXAnJxJOQqO-mHBETDm6S0oRb/s1600/mysql-hackthesec.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqb_IjkqiXKg6rDmYejq7XSHARNEO7FQeNRUS_wOIn21jrjNbbAU7E6G9eRLRdYqi_Rde4_MExFxTI7vEQLKUepAv77VJY1ZcxLbpmQ-UaCqNfiVGecOeUXAnJxJOQqO-mHBETDm6S0oRb/s1600/mysql-hackthesec.png" /></a></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px;"><br />MySQL is also used in many high-profile, large-scale websites, including Google(though not for searches), Facebook, Twitter,Flickr, and YouTube.</span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px;"><br /></span></span></div>
<div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
<b><u>Viewing list of Databases in your System</u></b></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;"><span style="color: white;"><span style="font-size: 15px;">$ mysql -u root -p -e "SHOW DATABASES;"</span></span></pre>
</div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
<b><u>To create Databases and start working with them</u></b></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;"><span style="color: white;"><span style="font-size: 15px;">$ mysql -u root -p -e "CREATE DATABASE hackthesec;"</span></span></pre>
</div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
Without output from MySQL, nothing will be returned after running a command like the command above.</div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
<br /></div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
<b><u>Create tables and view tables</u></b></div>
<div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;"><span style="color: white;"><span style="font-size: 15px;">$ mysql -u root -p -e "USE hackthesec; CREATE TABLE IF NOT EXISTS staffs ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR( 200 ) NOT NULL )"</span></span></pre>
</div>
</div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;"><span style="color: white;"><span style="font-size: 15px;">$ mysql -u root -p -e "USE hackthesec; SHOW TABLES;"</span></span></pre>
</div>
<div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif;">
<span style="font-size: 15px;"><b><u>To Insert into the table</u></b></span></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;"><span style="color: white;"><span style="font-size: 15px;">$ mysql -u root -p -e "USE hackthesec; INSERT INTO staffs ( name ) VALUES ( 'John' );"</span></span></pre>
</div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif;">
<span style="font-size: 15px;"><b><u>To view data in the table</u></b></span></div>
<div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;"><span style="color: white; font-family: Courier 10 Pitch, Courier, monospace;"><span style="font-size: 15px;">$ mysql -u root -p -e "USE hackthesec; SELECT * FROM staff;</span></span></pre>
</div>
</div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif;">
<span style="font-size: 15px;">Now we want to output the result to a file, the tee command comes in handy or output redirection by running</span></div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif;">
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-size: 15px; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">$ mysql -u root -p -e "USE hackthesec; SELECT * FROM staff;" | tee output.txt
</pre>
</div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif;">
<span style="font-size: 15px;">The output will be saved to output.txt of the working directory. Which can be viewed using cat command</span></div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif;">
<span style="font-size: 15px;"><br /></span></div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif;">
<span style="font-size: 15px;">$ cat output.txt</span></div>
</div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
<br /></div>
<div class="separator" style="clear: both; color: #333333; font-family: verdana, geneva, sans-serif; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7t3elDc8es80uXNwtCgf2nBBTd0WazhP5jnJimKQo5hydVpKIR5WZ8FdVZD8tHYvnfaCwOXaPn1W8-OwFmXLGEuARtAej98plKSBAFJfGwz223-SsqjZsUvEU61vsQf9lx-HDCvnwCbgx/s1600/mysql_hackthesec.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7t3elDc8es80uXNwtCgf2nBBTd0WazhP5jnJimKQo5hydVpKIR5WZ8FdVZD8tHYvnfaCwOXaPn1W8-OwFmXLGEuARtAej98plKSBAFJfGwz223-SsqjZsUvEU61vsQf9lx-HDCvnwCbgx/s1600/mysql_hackthesec.png" /></a></div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
<br /></div>
<div style="color: #333333; font-family: verdana, geneva, sans-serif; font-size: 15px;">
<a href="http://www.hackthesec.co.in/">www.hackthesec.co.in</a></div>
</div>
</div>

Executing MySQL Queries Directly From the Command Line

Executing MySQL Queries Directly From the Command Line MySQL is a central component of the LAMP open-source web application software sta...

How to install and configure AutoMySQLBackup

<div dir="ltr" style="text-align: left;" trbidi="on">
<h1 class="entry-title" style="background-color: white; box-sizing: border-box; color: #333333; font-family: Lato, sans-serif; font-size: 36px; font-weight: 500; line-height: 1.1; margin: 20px 0px 10px;">
Install and configure AutoMySQLBackup</h1>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">AutoMySQLBackup is very useful utility for creating daily, weekly or monthly backups of one or more MySQL databases from one of more <a href="http://www.hackthesec.co.in/search/label/Mysql">MySQL </a>servers. It dumps the databases and compress them in to archives.</span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><div style="font-size: 15px; line-height: 26px;">
it comes with many features such as:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOcRzNn6j_dutRU2TreKpBtsovpEF5kSOBkQM9_Nd44C3u4Nm_JmkrnDuUnn2zRVJYLuGKWeO6H1M_CRA9UCd5s4MfGqR-wf7NfdntcmY5pCGo3316kpVimXkp3PEbq0wkdZU4TIOrmH60/s1600/mysql.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="284" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOcRzNn6j_dutRU2TreKpBtsovpEF5kSOBkQM9_Nd44C3u4Nm_JmkrnDuUnn2zRVJYLuGKWeO6H1M_CRA9UCd5s4MfGqR-wf7NfdntcmY5pCGo3316kpVimXkp3PEbq0wkdZU4TIOrmH60/s640/mysql.jpg" width="640" /></a></div>
<div style="font-size: 15px; line-height: 26px;">
<br /></div>
<div>
<ul style="font-size: 15px; line-height: 26px; text-align: left;">
<li>Email notification of backups</li>
<li>Backup Compression and Encryption</li>
<li>Configurable backup rotation</li>
<li>Incremental database backups</li>
</ul>
<div>
<span style="font-size: 15px; line-height: 26px;">For Debian based systems execute</span></div>
</div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-size: 15px; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">apt-get update && apt-get upgrade</pre>
</div>
</span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">For RPM based systems execute</span></span></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-size: 15px; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">yum -y update</pre>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">If you are running Debian based distro <a href="http://www.hackthesec.co.in/search/label/Mysql">AutoMySQLBackup</a> is available in the repositories and you can easily install it by executing the following</span></span></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-size: 15px; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">apt-get install automysqlbackup</pre>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">If you have a RPM based distro you will have to download AutoMySQLBackup’s installation scripts</span></span></div>
<div>
<pre style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #333333; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: 13px; line-height: 1.42857; margin-bottom: 10px; overflow: auto; padding: 9.5px; word-break: break-all; word-wrap: break-word;">wget http://downloads.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz</pre>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Create a directory for Automysqlbackup's scripts and unpack the downloaded tar archive</span></span></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-size: 15px; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">mkdir /opt/automysqlbackup
tar zxvf automysqlbackup-v3.0_rc6.tar.gz -C /opt/automysqlbackup</pre>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Once the archive is unpacked run the Automysqlbackup installation script</span></span></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-size: 15px; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">cd /opt/automysqlbackup
./install.sh

### Checking archive files for existence, readability and integrity.

automysqlbackup ... exists and is readable ... md5sum okay :)
automysqlbackup.conf ... exists and is readable ... md5sum okay :)
README ... exists and is readable ... md5sum okay :)
LICENSE ... exists and is readable ... md5sum okay :)

Select the global configuration directory [/etc/automysqlbackup]:
Select directory for the executable [/usr/local/bin]:
### Creating global configuration directory /etc/automysqlbackup:

success</pre>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><div>
You can use the default paths for the global configuration directory and the directory for the executable. You just have to press Enter when the paths are required.</div>
<div>
<br /></div>
<div>
Next, we have to configure AutoMySQLBackup. Open its configuration file located in the directory we set when we ran the installation script.</div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">vim /etc/automysqlbackup/automysqlbackup.conf</pre>
</div>
</span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">And uncommend and set the following confituration directives</span></span></div>
<div>
<pre style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; color: #333333; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: 13px; line-height: 1.42857; margin-bottom: 10px; overflow: auto; padding: 9.5px; word-break: break-all; word-wrap: break-word;">CONFIG_mysql_dump_username='root'
CONFIG_mysql_dump_password='YourPassword'
CONFIG_mysql_dump_host='localhost'
CONFIG_backup_dir='/var/backup/db'
CONFIG_do_monthly="01"
CONFIG_do_weekly="5"
CONFIG_rotation_daily=6
CONFIG_rotation_weekly=35
CONFIG_rotation_monthly=150
CONFIG_mysql_dump_port=3306
CONFIG_mysql_dump_compression='gzip'</pre>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><div>
Once you are done with editing, save the configuration file. All settings are optional and there is description for all of them, so it is best to check the configuration file for more information about the settings of AutoMySQLBackup.</div>
<div>
<br /></div>
<div>
Create a directory for the MySQL backups. This is the directory we set as ‘backup_dir’ in the configuration file.</div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">mkdir /var/backup</pre>
</div>
</span></span></div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">If you want to create a backup of your MySQL databases, run AutoMySQLBackup by executing the following</span></span></div>
<div>
<pre style="background: rgb(68, 68, 68); border-radius: 3px; border: 1px solid rgb(221, 221, 221); color: white; font-family: "Courier 10 Pitch", Courier, monospace; font-size: 15px; font-stretch: inherit; line-height: 19px; margin-bottom: 30px; margin-top: 30px; max-width: 100%; overflow-x: auto; overflow-y: hidden; padding: 18px 20px; vertical-align: baseline; word-wrap: normal;">automysqlbackup</pre>
</div>
<div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">You can also create a crontab entry to run AutoMySQLBackup at a specific time or you can use bellow script</span></span></div>
<div>
<pre style="background-color: whitesmoke; border-radius: 4px; border: 1px solid rgb(204, 204, 204); box-sizing: border-box; margin-bottom: 10px; overflow: auto; padding: 9.5px; word-break: break-all; word-wrap: break-word;"><span style="color: #333333; font-family: Menlo, Monaco, Consolas, Courier New, monospace;"><span style="line-height: 18.5714px;">#!/bin/sh

/usr/local/bin/automysqlbackup /etc/automysqlbackup/automysqlbackup.conf

chown root.root /var/backup/db* -R
sudo chmod -R a-x+X /var/backup/db</span></span><span style="color: #333333; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; font-size: 13px; line-height: 1.42857;">
</span></pre>
</div>
<div>
<br /></div>
<div>
<a href="http://www.hackthesec.co.in/">www.hackthesec.co.in</a></div>
</div>

How to install and configure AutoMySQLBackup

Install and configure AutoMySQLBackup AutoMySQLBackup is very useful utility for creating daily, weekly or monthly backups of one or mor...

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 Temporarily Disable Foreign Key Checks in MySQL

How to Temporarily Disable Foreign Key Checks in MySQL

Temporarily Disable Foreign Key Checks in MySQL You must have faced the foreign key constrains issues many times while working with MyS...

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