Posted by: M'Creative | 28 April 2014

Batch Script – Cara Mudah Otomatis Backup MySQL dengan MySQLDump


Tujuan : Otomatis backup MySQL database

Requirement
– Windows OS
– Mysqldump.exe (you can find on mysql\bin dir)
– mysql_config_editor (—login-part=remote) for secure your login,read this
– Username n Password MySQL User with select and lock table privileges
– Rar.exe (Comprress sql files)

 

Step

0. Set your host, user n password with mysql_config_editor fisrt
1. Make batch script listed bellow
2. Create a shurtcut n put in on startup directory
3. It will be executed every windows startup
4. Or you can create a a task every time you want to execute

   1: @echo off

   2: echo ---------------------------------------------------------------------------

   3: echo * MySQLDump backup script        *

   4: echo * creted by djafa.org            *

   5: echo * make sure you have set your host,user,password with mysql_config_editor

   6: echo ---------------------------------------------------------------------------

   7:

   8:

   9:     title autobackup

  10:     color 0A

  11:

  12: ::-----------------------------------------------------------------

  13: :: Set your host, database here

  14: :: Sesuaikan dengan yang akan dipakai

  15: ::-----------------------------------------------------------------

  16:

  17:     set host=192.168.100.46

  18:     set dbase=akend

  19:

  20: ::-----------------------------------------------------------------

  21: :: Create name of backup file with date

  22: :: Memberi nama file backup dengan tangal

  23: ::-----------------------------------------------------------------

  24:

  25:     set timestamp=%date:~6,4%%date:~3,2%%date:~0,2%

  26:

  27: ::-----------------------------------------------------------------

  28: :: Make dir for MySQL backup files. If exist goto backup

  29: :: Membuat dir tempat file backup Database MySQL. Jika sudah ada langsung ke backup

  30: ::-----------------------------------------------------------------

  31:

  32:     if exist back goto backup

  33:     if not exist back mkdir back

  34:

  35: ::-----------------------------------------------------------------

  36: :: Show your configuration when executed

  37: :: Menampilkan konfigurasi ketika dieksekusi

  38: ::-----------------------------------------------------------------

  39:

  40:     :backup

  41:     echo ---------------------------------------------------------------------------

  42:     echo -

  43:     echo - Server : %host%

  44:     echo - Database : %dbase%

  45:     echo - Database isih dibek ap, ojo ditutup!!!!!!

  46:     echo -

  47:     echo ---------------------------------------------------------------------------

  48:

  49: ::-----------------------------------------------------------------

  50: :: Processing. File backup disimpan dengan nama database.sql

  51: ::-----------------------------------------------------------------

  52:

  53:     echo backing up database ...

  54:     ren mysqldump mysqldump.exe

  55:     mysqldump.exe --login-path=remote %dbase%  >;  back\%dbase%.sql

  56:     pause

  57:     ren mysqldump.exe mysqldump

  58:     echo Finished

  59:     echo ---------------------------------------------------------------------------

  60:

  61: ::-----------------------------------------------------------------

  62: :: Rename file back up with timestamp

  63: ::-----------------------------------------------------------------

  64:

  65:     cd back

  66:     ren *.sql %dbase%_%timestamp%.sql

  67:

  68: ::-----------------------------------------------------------------

  69: :: rar.exe will compress backup file

  70: ::-----------------------------------------------------------------

  71:

  72:     cd ..

  73:     echo It may take times depend on your backup size

  74:     echo Compressing with best compression ...

  75:     ren rar rar.exe

  76:     rar a -ag[ddmmyy-hhmmss] -m5 -df back\%dbase%.rar back\*.sql

  77:     ren rar.exe rar

  78:     echo finished

  79:     pause

  80:     explorer back

  81:     exit

  82:

  83: ::-----------------------------------------------------------------

  84: :: It will create compressed backup file with database name and timestamp

  85: ::-----------------------------------------------------------------

 

Tested on MySQL Server 5.5+

You can Download all file (script, mysql_config_editor.exe, mysqldump.exe and rar.exe) HERE


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: