MySQL Archived Backup

Open-Source, Teknoblogia
PDF 📄 eBook 📱

I regard backup as a very important matter. Because one of the certainties in life is this :

Computer WILL Fail

However, with so many computers in our care, we need a way to make it automatic. Otherwise, it will simply take too much time.

Thankfully, this is very easy to do in UNIX / Linux computers.

We have several kind of backup scripts. All of them are quite simple, but essential. Nobody should be without them.

Below you can find a backup script we use to backup MySQL database for our clients. Basically, this is how we use it :

  1. Modify the script to archive for the time range that we’d like.
    The script can archive the backup from 1 week to unlimited. Just remark out the time range that you don’t need (by putting the “#” character in the first column of the particular row).
  2. Make sure we can run mysqldump automatically / with no user intervention.
    This can be done easily by creating a file name ~/.my.cnf
  3. Setup the backup to run automatically via cron
  4. Routinely backup the backup to other servers.
    Yes, there is no such thing as “too much backup”.

Without further ado, here is the script : backup-archive-mysql.sh


#!/bin/bash

### Please change the variables below to suit you
targetdir="/home/myuser/mybackup"
targetfile="db-mydb"
targetsuffix=".mysql"

userdb="root"
database="mydb"

#################################
bcp="/bin/cp"
bmysqldump="/usr/bin/mysqldump"

################################## start backup now
### Archive : 1 week
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%A`$targetsuffix

### Archive : 2 weeks
$bcp $targetdir/$targetfile-`date +%A`$targetsuffix $targetdir/$targetfile-last-`date +%A`$targetsuffix
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%A`$targetsuffix

### Archive : 1 month
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%d`$targetsuffix

### Archive : 2 months
$bcp $targetdir/$targetfile-`date +%d`$targetsuffix $targetdir/$targetfile-last-`date +%d`$targetsuffix
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%d`$targetsuffix

### Archive : 1 year
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%m%d`$targetsuffix

### Archive : 2 years
$bcp $targetdir/$targetfile-`date +%m%d`$targetsuffix $targetdir/$targetfile-last-`date +%m%d`$targetsuffix
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%m%d`.mysql

### No Archive
### WARNING: always monitor your free disk space, or the following line may consume them all
$bmysqldump -u $userdb $database > $targetdir/$targetfile-`date +%Y%m%d`$targetsuffix

Here is a sample of ~/.my.cnf; if this file exist, then various MySQL tools & software will be able to run with no intervention.
Of course you’ll need to adjust its user & password :


[client]
user=root
password=mypassword

Hope you’ll find this useful.
I’ll post our other scripts here from time to time as well. Stay tuned.

55 thoughts on “MySQL Archived Backup”

  14. Jarang-jarang nih ada info detail seperti ini. Makasi banget gan, postingan ini sangat berguna.

    a href=”http://kolompemuda.com/sepeda-motor-injeksi-irit-harga-terbaik-cuma-honda/”>Sepeda Motor Injeksi Irit Harga Terbaik Cuma Honda

  24. Luar biasa..!! setelah melihat-lihat situs anda, saya mendapat pengetahuan baru. Pasti yang punya situs ini bisa dibilang lumayan hebat. Semoga bisa lebih ditingkatkan lagi kedepannya. Biar tambah rame pegunjungnya *_* ( salam dari PlatStainlessSteel )

  30. Yes, very true. I realized the importance of back up after my computer stopped functioning a year back. I lost some of my files and all the videos and pictures I had stored. I signed up for an online backup and set it automatically in my computer. So it will backup all my files even if I forget to do so.

  31. What you said is true. Any given time the computers will fail and the article will help them to be prepared and save their hard work.

  42. emang harus kita backup karena swaktu waktu komputer kita akan rusak jadi dengan cara back up kita bisa menyimpan pile pile kita

  46. Siapa yang tidak kenal dengan Clixsense?
    Sebuah PTC No Scam yang sangat direkomndasikan.
    Saya yakin Anda semua sudah mengetahuinya..
    Tapi bagi Anda yang ingin mengetahui lebih jelas apa itu Clixsense?
    Atau bagi Anda yang ingin mengetahui lebih dalam mengenai Clixsense..
    Silahkan ke http://www.panduanclixsense.blogspot.com/
    Sebuah blog sederhana yang semoga bermanfaat

  51. sangat membantu sekali, pernah kena hack untung punya backup dan bisa direstore,terima kasih sudah berbagi

  52. I realized the importance of back up after my computer stopped functioning a year back. I lost some of my files and all the videos and pictures I had stored.

  53. Terimakasih atas informasinya ,sangat bermanfaat sekali dan semoga menjadi amal baik bagi anda dan orang lain
    Sukses selalu

Leave a Reply

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