• Home
  • The Song
  • The Avatar
  • The Cat
  • Contact the Cat

Gyp the Cat dot Com

Backing Up All MySQL Databases Individually using Cron
Computers

Backing Up All MySQL Databases Individually using Cron

If we’ve got a whole pile of databases on a server, it’s possible that not all of them will change regularly.  If we’re backing up with something like rsync it makes sense not to do a whole database dump which would mean big perpetually backups so lets backup our databases individually.


Firstly we need to create a user account who we’re run the backups as, so logon with SSH as normal.

Now lets logon to SQL:

mysql -uroot -p

Type in your root MySQL password.

We will need a decent password, go and create a random one somewhere like www.random.org is usually pretty good.

Type in the following to create a user:

create user [user account here]@localhost identified by ‘[your random password]‘;

For instance:

create user [email protected] identified by ’28dbidh2jd9dhj29haka’;

This account will need specific permissions:

grant show databases, select, lock tables, reload on *.* to [email protected] identified by ’28dbidh2jd9dhj29haka’;

Give the permissions a kick:

flush privileges;

Now exit:

exit

Lets now create a directory for where we will store the backups, something like:

mkdir /var/dbbackups

No lets create a file that we will use as our Cron job:

nano /root/DatabaseBackup.sh

Paste the following and update the top of the file with the details you added above:

#!/bin/sh
UserAccount=[put you user account here]
UserPassword=[put your password here]
BackupDirectory=[put your directory here for instance /var/dbbackups]

for Database in $(mysql -u$UserAccount -p$UserPassword -e ‘show databases’ -s –skip-column-names );
do mysqldump -u$UserAccount -p$UserPassword $Database | bzip2 -c > “$BackupDirectory$Database.sql.bz2”;
done

[ctrl]+o to save and [ctrl]+x to exit.

Make the file executable:

chmod +x /root/DatabaseBackup.sh

Now lets add a Cron job:

crontab -e

If prompted select Nano (I find it the easiest but it’s your personal choice).

At the bottom of the screen paste the following:

@daily /root/DatabaseBackup.sh

[ctrl]+o to save and [ctrl]+x to exit.

There you go, you now have a regular SQL backup that will save all your individual databases into their own compressed files.

Related

Written by gyp - November 11, 2012 - 1330 Views
Tags | business, cron, internet, linux, mysql

You Might Also Like

Setting up Nginx with Cloudflare

September 19, 2013
Wordpress Asking for FTP Details

WordPress not Updating – Asking for FTP Details – Fixed

August 13, 2011
Control versus Escape

An Immature Way to Use Sudo

July 17, 2015

No Comment

Please Post Your Comments & Reviews
Cancel reply

Your email address will not be published. Required fields are marked *

Previous Post
Next Post

Latest Posts

  • Monitoring Tor Usage in Azure Sentinel, ASC, MDATP and ALA
  • HTTP to HTTPS Redirect on Azure CDN
  • Strongswan IPSec (Including Cryptomap) to Microsoft Azure Virtual Network Gateway
  • Black Ops 3 NAT Type Strict & PS4 NAT Type 3 with pfSense Fixed!
  • Sorry for the lack of posts
  • How to Block Internet Access with Group Policy (GPO)
  • Enforcing Microsoft Office 365 and Azure Tennancy with McAfee Web Gateway (MWG)
  • Scanning Subnet for Issuing Certificate Authority with OpenSSL
  • How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
  • Steam & Valve IP Ranges

Top Posts & Pages

  • How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
    How to Configure Windows 2012 NPS for Radius Authentication with Ubiquiti Unifi
  • How to Block Internet Access with Group Policy (GPO)
    How to Block Internet Access with Group Policy (GPO)
  • Tinyproxy A Quick and Easy Proxy Server on Ubuntu
    Tinyproxy A Quick and Easy Proxy Server on Ubuntu
  • IPSec VPN Host to Host on Ubuntu 14.04 with strongSwan
    IPSec VPN Host to Host on Ubuntu 14.04 with strongSwan
  • Why Does Lightening McQueen Stick His Tongue Out?
    Why Does Lightening McQueen Stick His Tongue Out?
  • How to Add Different Disclaimers using alterMIME and Postfix based on Domain
    How to Add Different Disclaimers using alterMIME and Postfix based on Domain
  • A Tinyproxy Transparent Installation on Ubuntu 12.04 with HTTPS Support
    A Tinyproxy Transparent Installation on Ubuntu 12.04 with HTTPS Support
  • How to Create a Site to Host VPN on Ubuntu for AWS, Azure and Linode with pfsense
    How to Create a Site to Host VPN on Ubuntu for AWS, Azure and Linode with pfsense
  • ISEB Business Analysis (BA) Revision Notes
    ISEB Business Analysis (BA) Revision Notes
  • How to install mod_cloudflare on Apache for Ubuntu
    How to install mod_cloudflare on Apache for Ubuntu

Tags

adobe apache2 blops business cheating cissp cloudflare cryptography dns exchange game google gyp internet iphone ipsec isc iseb linux mac marketing microsoft misc mw2 mx mysql nginx pfsense postfix proxy ps3 qualification revision security seo smtp socks squid ssh strongswan tinyproxy ubuntu windows 2012 wordpress xdecrypt.com
Gyp the Cat dot Com

Some rights retained Gyp the Cat Dot Com