Hack The Sec-Leading Resource Of Linux Tutorial: mysql

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

Backup Multiple MySQL Or MariaDB Databases Automatically

<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: #990000; font-size: large;"><u>Backup Multiple MySQL Or MariaDB Databases Automatically</u></span><br />
<span style="color: #0b5394; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><b><u>Introduction</u></b></span></span><br />
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">In this write-up, we'll walk through how to backup multiple <a href="http://www.hackthesec.co.in/search/label/Mysql">MySQL</a> or <a href="http://www.hackthesec.co.in/search/label/MariaDB">MariaDB </a>databases that sit on the same machine using a custom bash script and setting up a cron job.</span></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgO88uCszO-9Zrw3ZVIW1PEmPD6ZihgWILMfBTJtMn1wiCRu8_yiGhcfqtsK2u4jg3I_p2HmYzPpKzF0_p3CEO2-100-5x3FQbKeW9vtauEeE-XP_v2W2rF8DJtchISn2kjVsTI1ZccTN8X/s1600/mariadb_backup_mysql_hackthesec.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgO88uCszO-9Zrw3ZVIW1PEmPD6ZihgWILMfBTJtMn1wiCRu8_yiGhcfqtsK2u4jg3I_p2HmYzPpKzF0_p3CEO2-100-5x3FQbKeW9vtauEeE-XP_v2W2rF8DJtchISn2kjVsTI1ZccTN8X/s1600/mariadb_backup_mysql_hackthesec.jpg" /></a></div>
<span style="color: #333333; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><br /></span></span>
<span style="color: #0b5394; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><b><u>Database Backup(s)</u></b></span></span><br />
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">We have three MySQL databases on our linux server named db-hackthesec-site, db-hackthesec-blog, db-hackthesec-app.</span></span><br />
<br />
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Log on to your MySQL or MariaDB database and run below query to create a database user <b>db_user_backups</b> to handle backups</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">GRANT LOCK TABLES, SELECT ON *.*, SHOW VIEW ON *.*, REPLICATION CLIENT ON *.* TO 'db_user_backups'@'%' IDENTIFIED BY '{COMPLEX-PASSWORD}';</span></span></pre>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Also run below to make sure MySQL is configured to properly restore stored procedures</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">SET GLOBAL log_bin_trust_function_creators = 1;</span></span></pre>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Setup the necessarily directory structure and files needed</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;"># create backup directory with environment and log file
sudo mkdir /backups && cd /backups
sudo touch .env db-backup.sh db-backup.log
sudo chmod -R 775 /backups
sudo chmod -R g+s /backups
sudo chmod +x db-backup.sh

# add mysql backup user credentials into environment file
echo "export MYSQL_USER=db_user_backups" > /backups/.env
echo "export MYSQL_PASS={COMPLEX-PASSWORD}" >> /backups/.env</span></span></pre>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Open db-backup.sh <b>nano /backups/db-backup.sh</b> and paste the code below inside it, then save the file (Ctrl+X -> Y -> hit Enter).</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">DB_NAMES=( 'db-hackthesec-site' 'db-</span></span><span style="color: white; font-family: 'Courier New', Courier, monospace; line-height: 15.6px;">hackthesec</span><span style="color: white; font-family: 'Courier New', Courier, monospace; line-height: 15.6px;">-blog' 'db-</span><span style="color: white; font-family: 'Courier New', Courier, monospace; line-height: 15.6px;">hackthesec</span><span style="color: white; font-family: 'Courier New', Courier, monospace; line-height: 15.6px;">-app' ) #replace with your own database name(s)</span>
<span style="color: white; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">BKUP_NAMES=()
BKUP_DIR="/backups"

# get total number of directories
total_dbs=${#DB_NAMES[@]}

# create backup file names
for (( i=0; i<${total_dbs}; i++ )); do
    BKUP_NAMES[$i]="`date +%Y%m%d%H%M`-backup-$${DB_NAMES[$i]}.sql.gz"
done

# get backup users credentials
source $BKUP_DIR/.env

# create backups
for (( i=0; i<${total_dbs}; i++ )); do
    # NOTE: --routines flag makes sure stored procedures are also backed up
    mysqldump --routines -u ${MYSQL_USER} -p${MYSQL_PASS} | gzip > ${BKUP_DIR}/${BKUP_NAMES[$i]}
done</span></span></pre>
<span style="color: #0b5394; font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><b><u>Cronjob Setup</u></b></span></span><br />
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Setup a cronjob to run every midnight that runs the backup script and saves the result/output to backup log.</span></span><br />
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;"><br /></span></span>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Open crontab</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">crontab -e</span></span></pre>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Add below entry to crontab</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">0 0 * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log</span></span></pre>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">Note: While testing you can set cronjob to run every 1 minute instead like below</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">* * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log</span></span></pre>
<span style="font-family: verdana, geneva, sans-serif;"><span style="font-size: 15px; line-height: 26px;">-OR- every 5 minutes (replace 5 with the number of minutes you want)</span></span><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; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;">*/5 * * * * /usr/bin/env bash /backups/db-backup.sh &>> /backups/db-backup.log</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;"><span style="color: white; font-family: Courier New, Courier, monospace;"><span style="line-height: 15.6px;"><a href="http://www.hackthesec.co.in/">www.hackthesec.co.in</a></span></span></pre>
</div>

Backup Multiple MySQL Or MariaDB Databases Automatically

Backup Multiple MySQL Or MariaDB Databases Automatically Introduction In this write-up, we'll walk through how to backup multiple MyS...