6.1 Menjadwalkan Backup Otomatis pada Windows

BACKUP

6.1 Menjadwalkan Backup Otomatis pada Windows Sebagai administrator database, Anda tentu sering menjadwalkan backup database, baik dalam kaitannya dengan rutinitas pekerjaan atau untuk tujuan keamanan dan kenyamanan dalam administrasi database. Dengan tip berikut ini, Anda dapat melakukan penjadwalan backup pada Windows dengan mudah. Misalkan Anda ingin mem-backup sebuah file yang diberi nama menggunakan tanggal. Ada banyak cara untuk melakukan backup tersebut, contohnya menggunakan MySQL Tools yang dapat digunakan secara umum, seperti MySQL Administrator, MySQL Service Center, PHPMyAdmin, dan sebagainya. Akan tetapi, cara yang paling mudah untuk melakukan backup secara otomatis pada Windows adalah menggunakan sebuah file batch. Berikut skrip yang dapat Anda gunakan untuk melakukan backup pada Windows. Nama File: BACKUP.bat Skrip ini diketikkan pada NOTEPAD & disave dengan ekstensi .bat @echo off title BACKUP DATABASE NOW color b

95

echo . echo +-+-+-+-+-+-+-+-+-+-+-+--+-+-+-+-+-+-+-+-+-+-+-+-+-+-++-+ echo * echo RUNNING SCHEDULED JOB FOR MySQL 5 BACKUP ON WINDOWS echo . echo AUTHOR: Tomy echo . echo %DATE:~7,2%-%DATE:~4,2%-%DATE:~10,4% echo +-+ echo echo echo echo

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+--+-+-+-+-+-+-+-+-

. . Current Time is: ~ time /t echo . echo . echo pause IF NOT EXIST C:\BACKUP. ( mkdir C:\BACKUP. echo Directory is OK echo Continuing process echo .. )

[

]

IF EXIST C:\BACKUP\*.sql goto tryagain IF NOT EXIST c:\BACKUP\*.sql. ( cd c:\appserv\mysql\bin\ mysqldump -uroot -p05400679 --databases 555 --resultfile="c:\BACKUP\backup.%DATE:~0,3%.sql". ) ELSE ( cd c:\appserv\mysql\bin\ mysqldump -uroot -p05400679 --databases 555 --resultfile="c:\BACKUP\backup.%DATE:~0,3%.sql". ) echo End of batch program.

Skrip di atas merupakan sebuah file batch yang berisi perintah mysqldump. Perintah mysqldump ini terdapat pada bagian berikut: mysqldump -uroot -p05400679 --databases db1 --resultfile="c:\BACKUP\backup.%DATE:~0,3%.sql".

File batch di atas akan dieksekusi secara otomatis menggunakan perintah AT yang terdapat pada Command Prompt. Untuk dapat menjalankan backup secara otomatis pada Windows melalui Command Prompt, ketikkan pada Command Prompt sebagai berikut: AT 02:00 /every:M,T,W,Th,F,S,Su c:\BACKUP\backup.bat

96

Misalkan Anda melakukan backup database db1 pada hari Senin pada jam 2 sore dengan menjalankan skrip di atas, maka dengan otomatis Anda telah mem-backup semua data dari dalam [db1] MySQL ke suatu file yang bernama backup.Mon.sql. String yang berupa % DATE:~0,3% artinya menjalankan tanggal DOS dan menguraikan tiga karakter yang pertama. Jika Anda ingin menggunakan format YYYYMMDD, gunakan %DATE:~10,4%%DATE:~7,2%%DATE:~4,2%. Keuntungan penggunaan nama hari pada nama file backup tersebut adalah bahwa Anda nantinya akan mempunyai tujuh file backup. Setelah menjalankan perintah AT melalui Command Prompt, Anda dapat melihat file job yang telah dibuat melalui Control Panel, lalu pilih Scheduled Task dan Anda akan melihat sebuah file yang bernama At1.job yang siap melakukan backup sesuai dengan waktu yang ditetapkan di atas.

Gambar 6.1 Scheduled Task pada Windows

Selanjutnya, Anda dapat merestorasi file backup tersebut sampai dengan hari ke-7. Setiap harinya file backup tersebut akan ditindih. Jadi, dengan format YYYYMMDD akan memungkinkan Anda untuk menciptakan file backup dengan jumlah yang tak terbatas, dan merestorasi file backup ke tanggal manapun yang Anda inginkan. Untuk merestorasi file dump ke dalam tabelspace, caranya sebagai berikut: c:\mysql -e "source /c:\BACKUP\backup.Mon.sql"

97

atau c:\mysql db1 < c:\BACKUP\backup.Mon.sql

Catatan:

Selain menggunakan perintah AT, untuk menjalankan job melalui Command Prompt juga dapat digunakan perintah Schtasks (perintah Schtasks sama artinya dengan perintah AT). Untuk itu, Anda dapat lihat kembali pada dokumentasi Windows.

6.2 Mem-backup Data Tabel Menggunakan SELECT … INTO OUTFILE Proses backup data tabel dapat dilakukan secara manual dengan perintah SELECT … INTO OUTFILE. Untuk lebih jelasnya perhatikan contoh berikut: select * into outfile 'c:/copypembelian.txt' fields terminated by ',' lines terminated by '\n' from pembelian;

Query tersebut akan menghasilkan file yang bernama copypembelian.txt pada direktori c: dengan data yang berbentuk format kolom yang dipisahkan dengan tanda koma (commaseparated value/CSV). Anda juga dapat mengganti tanda koma dengan tanda lainnya yang tentunya harus digunakan secara konsisten. Berikut ini contoh gambar dari sebuah file backup yang dihasilkan dengan SELECT … INTO OUTFILE.

Gambar 6.2 Isi File Backup (SELECT … INTO OUTFILE)

Jika Anda ingin menyimpan nilai BLOB ke dalam sebuah file backup, gunakanlah SELECT … INTO DUMPFILE sebagai pengganti dari SELECT … INTO OUTFILE. Dengan SELECT .. INTO 98

DUMPFILE, MySQL akan menuliskan data yang di-backup ke file backup dalam sebuah baris tanpa terminasi kolom atau baris, dan tanpa melakukan proses escape karakter.

6.3 Restore File Backup ke dalam Tabel File backup yang telah Anda backup dengan perintah SELECT … INTO OUTFILE dapat direstorasi ke dalam tabel dengan contoh seperti berikut.

Gambar 6.3 File Backup yang Bernama copypembelian.txt LOAD DATA LOCAL INFILE 'c:/copypembelian.txt' INTO TABLE pembelian FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (nofaktur,noreff,tgl,kontan,disc,ppn,jatuhtempo,kodestock,nama barang,qtydisc, `harga@`,jumlah,totpembelian,totdisc,totpembelian_disc,totppn, netto);

Hasil restorasi file backup tersebut dapat dilihat pada gambar di bawah ini.

99

Gambar 6.4 Restorasi Data ke dalam Tabel Pembelian

6.4 Meng-update Kolom Menggunakan LOAD DATA INFILE dan REPLACE INTO Jika Anda ingin memuat file ke dalam tabel dengan hanya menindih suatu kolom tertentu, Anda dapat menggunakan REPLACE INTO … Misalkan Anda memiliki suatu tabel yang bernama test dan memiliki kolom id, firstname, lastname, zip. Disamping itu, Anda juga memiliki sebuah file backup.txt yang misalkan isinya adalah sebagai berikut: backup.txt 1,kurt,russell,45678 2,worth,tim,45687 3,ridd,sherly,45698 Anda dapat mengisikan data dari file backup.txt tersebut ke dalam tabel test dengan query berikut: LOAD DATA LOCAL INFILE 'c:/backup.txt' INTO TABLE test

100

FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (id, lastname, firstname, zip); SELECT * FROM test;

Output: id ----1 2 3

firstname --------russell tim sherly

lastname -------kurt worth ridd

zip -----45678 45687 45698

Jika Anda menggunakan REPLACE, maka baris yang diinput akan menggantikan baris yang ada. Dengan kata lain, baris-baris yang mempunyai nilai yang sama bagi primary key atau unique index sebagai baris yang ada. Misalkan isi file backup.txt pada kolom 4 (kolom zip) nilainya telah diubah atau di-update menjadi seperti di bawah ini: backup.txt 1,kurt,russell,10 2,worth,tim,20 3,ridd,sherly,30 Selanjutnya, Anda dapat menggunakan REPLACE INTO … untuk merestorasi data dari file backup.txt tersebut ke dalam tabel test dengan menjalankan query sebagai berikut: LOAD DATA LOCAL INFILE 'c:/backup.txt' REPLACE INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (id, lastname, firstname, zip); SELECT * FROM test;

Output: id ----1 2 3

firstname --------russell tim sherly

lastname -------kurt worth ridd

zip -----10 20 30

101

6.5 Meng-update Kolom Menggunakan LOAD DATA INFILE dan UPDATE Mengacu pada contoh di atas, misalkan file backup.txt mempunyai isi seperti berikut: backup.txt 1,10 2,20 3,30 kemudian pada tabel test berisi data seperti di bawah ini: SELECT * FROM test;

Output: id ---1 2 3

firstname ---------russell tim sherly

lastname -------kurt worth ridd

zip -----45678 45687 45698

Jika Anda hendak merestorasi data file backup.txt ke dalam tabel test, Anda tidak dapat menggunakan statement REPLACE INTO … di dalamnya karena REPLACE INTO hanya akan meng-update data berdasarkan data pada tabel backup.txt. Untuk itu, gunakanlah cara sebagai berikut: DROP TEMPORARY TABLE IF EXISTS temporarytest; CREATE TEMPORARY TABLE IF NOT EXISTS temporarytest ENGINE=MEMORY SELECT id, zip FROM test LIMIT 0; LOAD DATA LOCAL INFILE 'c:/backup.txt' INTO TABLE temporarytest FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (id, zip); UPDATE test, temporarytest SET test.zip = temporarytest.zip WHERE test.id = temporarytest.id; DROP TEMPORARY TABLE IF EXISTS temporarytest; SELECT * FROM test;

102

Ouput: id ----1 2 3

firstname --------russell tim sherly

lastname -----kurt worth ridd

zip ----10 20 30

6.6 Mem-backup Seluruh Trigger Stored procedure Backuptrigger yang diulas pada tip berikut ini diciptakan secara khusus untuk mem-backup seluruh trigger dari seluruh database yang digunakan pada Server MySQL 5.0.1x atau yang mempunyai fitur INFORMATION_SCHEMA.TRIGGERS. Anda dapat mengecek keberadaan fitur trigger tersebut melalui INFORMATION_SCHEMA. /*** 12 September 2007 - By Tomy ***/ DELIMITER $$ DROP PROCEDURE IF EXISTS `555`.`backuptrigger`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `backuptrigger`() COMMENT 'backuptrigger' begin set collation_server='utf8_general_ci'; set collation_database='utf8_general_ci'; set collation_connection='utf8_general_ci'; set character_set_connection='utf8'; set character_set_results='utf8'; set character_set_client='utf8'; set @@session.sql_mode='pipes_as_concat'; prepare backuptrigger from "SELECT 'CREATE TRIGGER '|| SENDER.TRIGGER_SCHEMA|| '.'||SENDER.TRIGGER_NAME|| CONVERT(char(32),char) ||SENDER.ACTION_TIMING|| CONVERT(char(32),char) ||SENDER.EVENT_MANIPULATION|| ' ON '|| SENDER.EVENT_OBJECT_SCHEMA|| '.'||SENDER.EVENT_OBJECT_TABLE|| ' FOR EACH ROW '|| SENDER.ACTION_STATEMENT|| '$$' INTO OUTFILE 'c:/Triggers.sql' FROM INFORMATION_SCHEMA.TRIGGERS AS SENDER;"; execute backuptrigger; end$$ DELIMITER ;

103

Definisi procedure backup trigger di atas menggunakan pernyataan INTO OUTFILE. File backup akan diciptakan pada server host. Anda dapat menggunakan nama file yang sesuai dengan keinginan Anda. Agar 100% aman, periksa definisi dalam file .sql, dan tentunya Anda bisa membuat copy-an file tersebut. Untuk menggunakan stored procedure backuptrigger tersebut, caranya cukup dengan mengetikkan: Call backuptrigger();

File backup akan diciptakan pada direktori c:/, dengan nama Triggers.sql. Anda dapat memodifikasi stored procedure di atas menggunakan direktori dan nama yang sesuai dengan keinginan Anda. Seluruh trigger yang telah berhasil direstorasi ke dalam INFORMATION_SCHEMA bisa Anda lihat menggunakan perintah SHOW TRIGGERS.

6.7 Mem-backup Database Menggunakan Event Scheduler Event scheduler adalah fitur terbaru saat ini yang diperkenalkan dari MySQL 5.1. Dengan adanya fitur event scheduler, Anda dapat menjalankan perintah secara otomatis di mana Anda hanya tinggal menjadwalkan perintah SQL ke dalam event scheduler untuk dieksekusi oleh server MySQL 5.1 secara otomatis sesuai dengan waktu yang ditentukan. Berikut ini sebuah procedure untuk mem-backup database menggunakan event scheduler. Procedure di bawah ini akan digunakan sebagai perintah SQL dalam event scheduler. /*** 16 September 2007 - By Tomy ***/ DELIMITER $$ DROP PROCEDURE IF EXISTS `555`.`speventbackup`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `speventbackup`(in existdb varchar(65),in newdb varchar(65)) BEGIN declare table_name2 varchar(64); set global log_bin_trust_routine_creators=true;

104

/*-- mengaktifkan variabel global event_scheduler --*/ Set global event_scheduler = 1; /*-- memberikan hak kepada user1 untuk mengakses database 555-*/ Grant event on `555`.* to user1; /*-- membuat database baru untuk backup --*/ set @q1=concat('drop database if exists ',newdb,';'); set @q2=concat('create database if not exists ',newdb,';'); prepare stmt from @q1; execute stmt; prepare stmt from @q2; execute stmt; /*-- membackup database --*/ Scan: loop drop table if exists temp; create table temp(id int(10) auto_increment,foundtable varchar(65),primary key(id)); insert into temp(id,foundtable)select '', table_name from information_schema.tables as t where t.table_type = 'BASE TABLE' and t.table_schema=existdb; set @r=0; set @maks=(select max(id)from temp); while @r <= @maks do set table_name2=(select foundtable from temp where id=@r); If @r>0 then Set @q0=concat('drop table if exists ','`',newdb,'`','.',table_name2,'backup',';'); Set @q1=concat('create table if not exists ','`',newdb,'`','.',table_name2,'backup',' ','as select * from ','`',existdb,'`','.',table_name2,';'); Set @q2=concat('alter table ','`',newdb,'`','.',table_name2,'backup',' ','engine=InnoDB;'); Prepare stmt from @q0; Execute stmt; Prepare stmt from @q1; Execute stmt; Prepare stmt from @q2; Execute stmt; End if; set @r=@r+1; end while; If @r=0 then Leave scan; end if; End loop scan; drop table if exists temp; END$$ DELIMITER ;

105

Cara menggunakan speventbackup adalah dengan menginputkan 2 parameter yang terdiri atas existdb(nama database sumber atau yang akan di-backup) dan newdb(nama database sebagai backup). Contoh: Call speventbackup('555','tombackup')

Dari pernyataan sql tersebut di atas, sekarang kita akan menjadwalkan backup database dengan membuat event scheduler seperti berikut ini: set global event_scheduler=1; grant event on `555`.* to user1; create event eventbackup on schedule every '14:00:00' day do call speventbackup('555','tombackup');

Nama dari event scheduler pada contoh ini adalah eventbackup dan eventbackup secara otomatis akan dijalankan oleh server MySQL setiap hari pada jam 14:00:00(jam:menit:detik). Pada jam 14:00:00 setiap harinya, Anda akan mendapatkan database yang telah dibackup secara otomatis tersebut seperti yang ditunjukkan pada gambar berikut ini.

Gambar 6.5 Backup Database dengan speventbackup

106

Nama tabel hasil backup otomatis tersebut akan berekstensi backup pada akhir dari nama tabel sumber. Anda dapat memodifikasi nama tabel tersebut menggunakan speventbackup yang telah dikemukakan di atas.

6.8 Ekspor-Impor Data Menggunakan File Excel Jika Anda sering mengolah data menggunakan aplikasi Excel atau StarOffice Calc, kini Anda dapat mengekspor dan mengimpor data menggunakan data dari aplikasi Excel tersebut untuk diolah atau disimpan ke dalam database MySQL. Untuk dapat melakukan ekspor dan impor data Excel, MySQL memperkenalkan sebuah fitur yang berbentuk storage engine (tabel) yang bernama CSV (Comma-Separated Values). Tabel CSV menyimpan 3 jenis file yang terdiri atas file .CSV, file .frm sebagai file data, dan file .CSM sebagai Meta-file. (File .CSM ini diperkenalkan sejak MySQL 5.1.9, yaitu sebuah file yang berhubungan dengan tabel yang berguna untuk menyimpan keadaan tabel dan jumlah baris pada tabel.) Anda dapat mengekspor dan mengimpor data menggunakan file .CSV. File .CSV dapat dibuka menggunakan aplikasi Excel atau StarOffice Calc. Jika Anda menggunakan file Excel sebagai data untuk diekspor ke dalam tabel MySQL, simpanlah file Excel tersebut dengan format CSV, misalnya Book1.CSV, kemudian Anda dapat menggunakan perintah LOAD DATA INFILE untuk memasukkan seperti yang telah dibahas pada bab terdahulu. Berikut ini contoh ekspor - impor data menggunakan file Excel. CREATE TABLE excel(Id INT, Citation CHAR(250), Author Char(80), AboutAuthor char(70), Source CHAR(60)) ENGINE = CSV; INSERT INTO excel VALUES(1, "No nation can be competitive in (and can be a net exporter of) everything.", "Michael E. Porter (1947 - )", "U.S. author.", "The Competitive Advantage of Nations"); INSERT INTO excel VALUES(2,

107

"While we may need a balanced budget to discipline our politicians, what we need for export growth is a balanced economy.", "Michael Armstrong", "U.S. business executive.", "Speech"); INSERT INTO excel VALUES(3, "The failure of communism as an economic system is made all the more ironic by the arrogance of the original conception, which was no less than to export the 'revolution' to the entire world.", "William Keegan (1938 - )", "British author and journalist.", "The Spectre of Capitalism"); INSERT INTO excel VALUES(4, "Exports are becoming obsolete, because they are too slow. Marketers today must sell the latest product everywhere at once—and that means producing locally.", "Carlo de Benedetti (1934 - )", "Italian business executive.", "Observer (London)"); INSERT INTO excel VALUES(5, "To attempt to export revolution is nonsense.", "Joseph Stalin (1879 - 1953)", "Soviet dictator.", "Message sent to Roy Howard, U.S. newspaper owner");

Contoh sebuah file Excel dari output yang dihasilkan adalah seperti gambar di bawah ini.

Gambar 6.6 Sebuah File Excel dari Output Tabel CSV

108