#MySQLTips- Script Automatic Backup MySQL dengan CMD

Script Automatic Backup MySQL dengan CMD. Script kecil ini bertujuan membackup MySQL database  di  OS Windows dengan CMD,  bisa harian, per jam, perminggu atau sesuai dengan yang diinginkan . Konsepnya adalah sebagai berikut ;

1. Mengatur host/server, username dan password dengan MySQL Config Editor
2. Mengatur database yang akan dibackup.
3. Backup Database MySQL dengan MySQL Dump dengan nama file Database  yang dibackup.
4. Mengkompressnya dan memberi nama sesuai waktu backup.
5. Menaruhnya di startup program agar setiap logon otomatis backup, atau
6. Membuat task schedule untuk mengeksekusinya sesuai dengan jadwal yang diinginkan

Requirement Script Automatic Backup MySQL dengan CMD

• Windows OS
• Mysqldump.exe (you can find on mysqlbin 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 Script Automatic Backup MySQL dengan CMD

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

@echo off
echo ---------------------------------------------------------------------------
echo * MySQLDump backup script        *
echo * creted by djafa.org            *
echo * make sure you have set your user n password with mysql_config_editor
echo * Pastikan user dan password sudah di setting dengan mysql_config_editor
echo ---------------------------------------------------------------------------

    
    title autobackup
    color 0A

::-----------------------------------------------------------------
:: Set your database here or you can add it using mysql config editor
:: Atur database atau bisa diinlcudekan di mysql config editr
::-----------------------------------------------------------------

   set dbase=test

::-----------------------------------------------------------------
:: Create name of backup file with date
:: Memberi nama file backup dengan tanggal. Setting timestamp
::-----------------------------------------------------------------

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

::-----------------------------------------------------------------
:: Make dir for MySQL backup files. If exist goto backup
:: Membuat folder tempat file backup Database MySQL. Jika sudah ada goto backup
::-----------------------------------------------------------------

    if exist back goto backup
    if not exist back mkdir back

::-----------------------------------------------------------------
:: Show your configuration when executed
:: Menampilkan konfigurasi ketika dieksekusi
::-----------------------------------------------------------------

    :backup
    echo ---------------------------------------------------------------------------
    echo -
    echo - Database : %dbase%
    echo - Database isih dibek ap, ojo ditutup!!!!!!
    echo -
    echo ---------------------------------------------------------------------------

::-----------------------------------------------------------------
:: Processing. File backup disimpan dengan nama database.sql
:: Login path menggunakan nama koneksi_1. Setting di MySQL Config Editor
::-----------------------------------------------------------------

    echo backing up database ...
    ren mysqldump mysqldump.exe
    mysqldump.exe --login-path=koneksi_1 %dbase%  >  back%dbase%.sql
    ren mysqldump.exe mysqldump
    echo Finished
    echo ---------------------------------------------------------------------------

::-----------------------------------------------------------------
:: Rename file back up with timestamp
:: Mengganti file backup dengan tanggal 
::-----------------------------------------------------------------

    cd back
    ren *.sql %dbase%_%timestamp%.sql

::-----------------------------------------------------------------
:: rar.exe will compress backup file
::-----------------------------------------------------------------

    cd ..
    echo It may take times depend on your backup size
    echo Compressing with best compression ...
    ren rar rar.exe
    rar a -ag[ddmmyy-hhmmss] -m5 -df back%dbase%.rar back*.sql
    ren rar.exe rar
    echo finished
    explorer back
    exit

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

Tested on MySQL Server 5.5+

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