Panduan Pendayagunaan Open Source Software : RDBMSMySQL
Hak Cipta © 2007 Kementerian Negara Riset dan Teknologi dan Yayasan Penggerak Linux Indonesia Distribusi : Kementerian Negara Riset dan Teknologi Republik Indonesia Deputi Bidang Pendayagunaan dan Pemasyarakatan Iptek Asisten Deputi Pengembangan dan Pemanfaatan Teknologi Informasi Gedung II BPP Teknologi Lantai 6 Jl. MH. Thamrin No.8 – Jakarta 10340 Telepon : (021) 316 9181 / 316 9166 Faksimili : (021) 310 1952 Web : www.ristek.go.id
Yayasan Penggerak Linux Indonesia (YPLI) Jln. Mampang Prapatan X No. 4 Jakarta Selatan 12790 Indonesia Telp. 021 93740960, Fax. 021 7874225, Email:
[email protected] Pengurus YPLI: Rusmanto Maryanto (Ketua); Resza Ciptadi (Sekretaris); Effendy Kho (Bendahara) Penyusun: Firdaus Tjahyadi; Henry Saptono; Irfan Gustiarahman; Mgs. Hendri Thoyyib; Prihantoosa; Resza Ciptadi; Rusmanto Maryanto; Sirojul Munir; Yan Farmawan
ISBN 9789796300372
Panduan Pendayagunaan Open Source Software: RDBMSMySQL i
SAMBUTAN MENEGRISTEK Pembangunan teknologi informasi merupakan sumber terbentuknya iklim yang menjadi landasan bagi tumbuhnya kreativitas sumberdaya manusia yang dapat menjadi sumberdaya pertumbuhan dan daya saing ekonomi. Dengan menyadari akan hal tersebut, peran teknologi informasi sangatlah penting dalam kehidupan masyarakat, sehingga mendorong peningkatan kemandirian, daya saing, kreativitas serta inovasi bangsa yang merupakan kunci utama keberhasilan pembangunan bangsa. Melalui semangat untuk membangkitkan inovasi ini, “Program Indonesia, Go Open Source (IGOS)” dideklarasikan. Tumbuhnya kesadaran untuk meraih kemandirian dalam pemanfaatan teknologi informasi dan komunikasi (information and communication technologyICT), ditopang dengan tersedianya berbagai pilihan terhadap perangkat lunak (software) yang dapat digunakan oleh masyarakat. Pilihan untuk mengembangkan dan memanfaatkan open source software (OSS) merupakan pilihan yang rasional, baik dari segi legalitas, ekonomi, maupun tingkat keamanannya (security). Untuk kepentingan pendayagunaan open source serta memudahkan pengenalan perangkat lunak berbasis Open Source kepada masyarakat, Kementerian Negara Riset dan Teknologi menginisiasi penyusunan Panduan Pendayagunaan Open Source Software yang bekerjasama dengan komunitas Iptek, Yayasan Penggerak Linux Indonesia (YPLI). Dalam konteks ini, saya menyambut gembira penyusunan Panduan Pendayagunaan Open Source Software yang tentunya diharapkan dapat lebih memudahkan masyarakat dalam mengenal perangkat lunak berbasis open source. Dokumen tersebut terdiri atas delapan dokumen yaitu Perangkat Lunak Bebas dan Open Source, Petunjuk Instalasi IGOS Nusantara, Konfigurasi Server Linux, Aplikasi untuk Server, RDBMS (Relational Database Management System): MySQL, Bahasa Pemrograman Open Source, Aplikasi Perkantoran OpenOffice.org, dan CMS, CRM, dan ERP. Akhir kata, semoga kedelapan Dokumen ini dapat bermanfaat bagi peningkatan kemampuan masyarakat dalam penguasaan teknologi piranti lunak bebas dan open source. Dengan semangat “Kreativitas Tanpa Batas” dan maju bersama Indonesia, Go Open Source! Kita besarkan produk aplikasi perangkat lunak Indonesia. Jakarta, Juli 2007 Menteri Negara Riset dan Teknologi
Kusmayanto Kadiman
Panduan Pendayagunaan Open Source Software:RDBMSMySQL ii
PENGANTAR Dokumen ini disusun sebagai salah satu bahan acuan untuk pelatihan tentang perangkat lunak bebas dan open source dengan semangat IGOS (Indonesia, Go Open Source!) yang dimotori oleh Kementerian Negara Riset dan Teknologi Republik Indonesia. Dokumen ini merupakan bagian dari delapan dokumen IGOS Training Tool Kit yang disusun oleh Yayasan Penggerak Linux Indonesia dan diterbitkan oleh Kementerian Negara Riset dan Teknologi Republik Indonesia. Kedelapan dokumen itu adalah Perangkat Lunak Bebas dan Open Source, Petunjuk Instalasi IGOS Nusantara, Konfigurasi Server Linux, Aplikasi untuk Server, RDBMS (Relational Database Management System): MySQL, Bahasa Pemrograman Open Source, Aplikasi Perkantoran OpenOffice.org, dan CMS, CRM, dan ERP. Sebagian bahan penyusunan dokumen ini diambil dari Open Source Training Tool Kit yang diterbitkan oleh Open Source Resource Center (OSRC) Pakistan Software Export Board (PSEB). Lisensi dokumen ini OPL (Open Publication License) versi 1.0 atau yang lebih baru dan dapat diakses di http://opencontent.org/openpub/. Jakarta, Juli 2007 Rusmanto Maryanto Ketua Yayasan Penggerak Linux Indonesia
iii Panduan Pendayagunaan Open Source Software: RDBMSMySQL
DAFTAR ISI SAMBUTAN MENEGRISTEK.......................................................................................................................ii PENGANTAR............................................................................................................................iii DAFTAR ISI..............................................................................................................................iv I Pengantar RDBMS...................................................................................................................1 1.1 Pendahuluan......................................................................................................................1 1.2 SQL92.............................................................................................................................2 1.3 Relational Databases.........................................................................................................2 1.3.1 Relational vs. flat file databases................................................................................2 1.3.2 Normalisasi database................................................................................................4 1.3.3 Relasi Antar Tabel ....................................................................................................5 1.3.3.1 Relasi satukebanyak (OnetoMany)...............................................................5 1.3.3.2 Relasi satukesatu (OnetoOne)......................................................................6 1.3.3.3 Relasi banyakkebanyak (ManytoMany).......................................................7 II Pengantar MySQL.................................................................................................................9 2.1 Pendahuluan......................................................................................................................9 2.2 Penyesuaian dengan standarstandar SQL........................................................................9 2.3 Programprogram klien MySQL......................................................................................9 2.3.1 Program klien mysql...............................................................................................10 2.3.2 Program klien mysqlimport....................................................................................12 2.3.3 Program klien mysqldump......................................................................................12 2.3.3.1 Operasi mysqldump.........................................................................................13 2.3.3.2 Memuat ulang hasil mysqldump.....................................................................13 2.3.4 Program klien mysqlcheck dan myisamchk............................................................13 2.4 Database secara umum dan manipulasi tabel.................................................................14 2.5 Storage Engine dan Tipetipe tabel.................................................................................15 2.6 Tipetipe kolom...............................................................................................................16 2.6.1 NUMERIK..............................................................................................................17 2.6.2 String.......................................................................................................................19 2.6.3 Date.........................................................................................................................21 2.6.4 Tipe data Komplek.................................................................................................23 2.7 Opsiopsi Kolom.............................................................................................................24 2.8 Operator..........................................................................................................................24 2.8.1 Peraturan ketentuan hak yang lebih tinggi .............................................................25 2.8.2 Operator Arithmatic...............................................................................................25 2.8.3 Operator Comparison.............................................................................................26 2.8.4 Operator Logical.....................................................................................................27 2.9 Memulai dan mengakhiri koneksi ke database server....................................................27 2.10 Membuat dan menggunakan database.........................................................................29 Panduan Pendayagunaan Open Source Software:RDBMSMySQL iv
2.11 Manipulasi tabel............................................................................................................30 2.11.1 Membuat tabel.......................................................................................................30 2.11.2 Menghapus tabel....................................................................................................31 2.11.3 Merubah tabel........................................................................................................31 2.11.4 Merubah nama tabel..............................................................................................32 2.12 Index.............................................................................................................................33 2.12.1 Nonunique index..................................................................................................34 2.12.2 Unique index.........................................................................................................34 2.12.3 Primary Key index................................................................................................34 2.12.4 Menghapus index..................................................................................................35 2.13 MySQL query ..............................................................................................................35 2.13.1 Perintah SELECT..................................................................................................35 2.13.1.1 Pengambilan data sederhana..........................................................................36 2.13.1.2 Membatasi suatu perintah SELECT dengan WHERE...................................37 2.13.1.3 Mengurutkan pemilihan data menggunakan ORDER BY.............................37 2.13.1.4 Membatasi pengambilan data menggunakan klausa LIMIT..........................38 2.13.1.5 Mengelompokkan pemilihan baris data menggunakan klausa GROUP BY.39 2.13.2 Perintah INSERT..................................................................................................40 2.13.3 Perintah REPLACE...............................................................................................43 2.13.4 Perintah UPDATE.................................................................................................43 2.13.5 Perintah DELETE.................................................................................................44 2.14 Join tabel.......................................................................................................................45 2.14.1 Crossjoin..............................................................................................................45 2.14.2 Inner Join...............................................................................................................45 2.14.3 Outer Join..............................................................................................................46 2.14.3.1 Left Join.........................................................................................................46 2.14.3.2 Right Join......................................................................................................48 2.15 Referensi.......................................................................................................................48 III Administrasi MySQL..........................................................................................................49 3.1 Cara Download dan Install MySQL...............................................................................49 3.2 Direktori Data MySQL...................................................................................................51 3.2.1 Lokasi Direktori Data.............................................................................................52 3.2.2 Struktur Direktori Data...........................................................................................52 3.2.3 Bagaimana MySQL Server memberikan akses ke Data.........................................52 3.2.4 Representasi dari Tabel Database...........................................................................53 3.3 StartUp dan Shutdown MySQL....................................................................................53 3.3.1 MySQL server dan Skripskrip...............................................................................53 3.3.1.1 Sekilas pandang terhadap Skrip Serverside ...................................................54 3.3.1.2. Mengaktifkan Server......................................................................................55 3.3.1.3 Jika tidak bisa terhubung ke server.................................................................55 3.4 Manajemen User Account MySQL................................................................................56 3.4.1 Username dan Password.........................................................................................56 v Panduan Pendayagunaan Open Source Software: RDBMSMySQL
3.4.2 Menambah user account baru................................................................................57 3.4.2.1 Akses ke Database...........................................................................................60 3.4.2.2 Akses Tabel.....................................................................................................61 3.4.2.3 Akses Kolom...................................................................................................61 3.4.3 Menampilkan Izin Akses.......................................................................................61 3.4.4 Menghapus Account User.......................................................................................62 Penggunaan Revoke.............................................................................62 3.4.5 Membatasi jumlah penggunaan resource..............................................................64 3.5 Mengatasi Kerusakan Data dan Recovery.....................................................................65 3.5.1 Backup Database...................................................................................................65 3.5.2 Prinsipprinsip Backup.........................................................................................65 3.5.3 Metode Backup.....................................................................................................66 3.5.3.1 Copy secara langsung.....................................................................................66 3.5.3.2 mysqlhotcopy.................................................................................................66 3.5.3.3 mysqldump.....................................................................................................67 3.6 Replikasi........................................................................................................................69 3.6.1 Memanfaatkan Replikasi pada MySQL versi 5.0..................................................69 3.6.2 Menginstall MySQL versi 5.0 dan mensetupnya sebagai server master................69 3.6.2.1 Install MySQL server di mesin master :........................................................69 3.6.2.3 File Konfigurasi.............................................................................................70 3.6.2.4 Menjalankan server master............................................................................71 3.6.2.5 Menyeting hak akses untuk replikasi.............................................................72 3.6.2.6 Membuat arsip base backup...........................................................................72 3.6.3 Setup server Slave..................................................................................................73 3.6.3.1 Instal mysql server di mesin slave..................................................................73 3.6.3.2 Setup datadir dari arsip base backup...............................................................74 3.6.3.3 File konfigurasi..............................................................................................74 3.6.3.4 Menjalankan server slave...............................................................................75 3.6.3.5 Setup parameter replikasi...............................................................................75 3.6.3.6 Menjalankan dan memeriksa thread slave.....................................................76 3.6.4 Menguji replikasi dan troubleshooting..................................................................76 3.6.4.1 Menyimpan binary log di direktori tertentu...................................................76 3.6.4.2 Menyediakan disk space yang cukup besar untuk file temporary di slave. .77 3.6.5. Catatan bagi database administrator......................................................................77 3.6.5.1 Memeriksa status master dan slave................................................................77 3.6.5.2 Ketika mesin server slave restart....................................................................77 3.6.5.3 Memperhatikan disk space untuk binary log..................................................78 3.6.5.4 Berhatihati dalam menshutdown server slave...............................................78 3.6.6 Ketika master crash.................................................................................................78 3.6.6.1 Mempromosikan slave menjadi master..........................................................78 3.6.6.2 Membuat slave baru.......................................................................................79 3.6.6.3 Alternatif lain: mebuat master baru................................................................79 Panduan Pendayagunaan Open Source Software:RDBMSMySQL vi
3.7 Membuat database backup secara periodik...................................................................79 3.7.1 Mengapa perlu backup lagi?....................................................................................79 3.7.1.1 Sebagai backup sekunder.................................................................................79 3.7.1.2 Memulihkan kesalahan eksekusi update SQL pada master...........................80 3.7.2 Apa saja dan bagaimana.........................................................................................80 3.7.3 Skrip untuk otomatisasi.........................................................................................80 3.8 Memulihkan dari kesalahan eksekusi update SQL di master.........................................82 3.8.1 Mengunci master dari user.....................................................................................82 3.8.2 Mencari posisi binlog terakhir...............................................................................82 3.8.3 Menggunakan base backup terakhir......................................................................83 3.8.4 Menjalankan thread slave sampai posisi tertentu..................................................83 3.8.5 Mengganti data master ..........................................................................................83 3.8.6 Menjalankan thread slave seperti biasa.................................................................84 3.9 Keterbatasanketerbatasan Replikasi.............................................................................84 3.10 Penutup........................................................................................................................86 IV Perbandingan DBMS Oracle, MySQL, dan PostgreSQL...................................................87 4.1 Fiturfitur dasar..............................................................................................................87 4.1.1 Kesesuaian dengan standard SQL :........................................................................87 4.1.2 Biner dan karakter objek besar...............................................................................87 4.1.3 Tipe data Userdefined...........................................................................................88 4.1.4 Perluasan objectrelational.....................................................................................88 4.1.5 Tipe data khusus (multimedia,dll)..........................................................................89 4.1.6 Perintah subquery ................................................................................................89 4.1.7 Primary dan Unique key.........................................................................................89 4.1.8 Foreign Key............................................................................................................90 4.1.9 Check......................................................................................................................90 4.1.10 Views....................................................................................................................90 4.1.11 Updatable Views...................................................................................................91 4.1.12 Synonims(Alias pada table, view atau objek lain di database).............................91 4.1.13 Auto Increment (penambahan otomatis) kolom....................................................91 4.1.14 Konversi otomatis code pages (antara klien dan server).......................................91 4.2 Transaksi........................................................................................................................92 4.2.1 Mendukung proses transaksi..................................................................................92 4.2.2 Transaksi Rollback Sebagian.................................................................................92 4.2.3 Level Penguncian (table, page dan baris)..............................................................93 4.2.4 Deteksi Deadlock dan pemecahannya...................................................................93 4.3 DB programming...........................................................................................................93 4.3.1 Bahasa untuk menulis stored procedure................................................................93 4.3.2 Triggers..................................................................................................................94 4.4 Administrasi..................................................................................................................95 4.4.1 Otorisasi User........................................................................................................95 4.4.2 Enkripsi koneksi klientserver ..............................................................................96 vii Panduan Pendayagunaan Open Source Software: RDBMSMySQL
4.4.3 Izin pengaksesan objek (level)...............................................................................96 4.4.4 Izin grouping..........................................................................................................96 4.4.5 Incremental dan backup online.............................................................................97 4.4.6 Tools untuk eksport data........................................................................................97 4.4.7 Tools untuk mengimport data...............................................................................97 4.5 Portability dan Scalability.............................................................................................98 4.5.1 Hardware dan system platform yang mendukung...................................................98 4.5.2 Portability data dan kode (procedure penyimpanan).............................................98 4.5.3 Mendukung system SMP (eksekusi query parallel)..............................................99 4.6 Performa dan VLDB.....................................................................................................99 4.6.1 Implementasi VLDB yang diketahui.....................................................................99 4.6.2 Maximum jumlah baris dan kolom........................................................................99 4.6.3 Maximum jumlah table dan index.......................................................................100 4.6.4 Tuning ‘manual’ alokasi.......................................................................................100 4.6.5 Partisi otomatis pada table/index besar dan menggunakan partisi dan engoptimalan query........................................................................................................101 4.6.6 Akses ke multiple database dalam satu sessi........................................................101 4.6.7 Replikasi...............................................................................................................101 4.6.8 Gerbang(Gateway) ke DBMS lain ......................................................................102 4.7 Interfaces dan Pengembangan Aplikasi.......................................................................102 4.7.1 Interface standar ODBC dan JDBC.....................................................................102 4.7.2 Support aplikasi....................................................................................................103 4.7.3 Dedicated Server Web..........................................................................................103 4.7.4 Support XML terintegrasi dengan DBMS...........................................................104 4.7.5 Mendukung paket CASE.......................................................................................104 4.8 Kehandalan..................................................................................................................105 4.8.1 Otomatis recovery dari kegagalan.........................................................................105 4.9 Isuisu komersial..........................................................................................................105 4.9.1 Tipe lisensi ..........................................................................................................105 4.9.2 Tehnical Support..................................................................................................106 4.9.3 Pembagian pasar..................................................................................................106 4.9.4 Segmen pasar secara spesifik...............................................................................107
Panduan Pendayagunaan Open Source Software:RDBMSMySQL viii
