MySQL : Quick Recipes

Open-Source, Teknoblogia

Sebagai seorang DBA (Database Administrator) yang banyak berkecimpung antara lain dengan MySQL, disini saya akan menuliskan berbagai catatan “resep-resep” MySQL yang sering digunakan.

Resep-resep ini bersifat instan, yaitu bisa langsung Anda copas (copy-paste) di layar Terminal server.
Tentunya setelah menyesuaikan berbagai variable yang ada (username, password, dst)

Selamat menikmati 🙂

PERSIAPAN

Jalankan perintah-perintah beriku ini, agar Anda bisa langsung menjalankan berbagai resep yang ada disini :


### persiapan agar bisa menjalankan mysql (client)
### tanpa perlu memasukkan password lagi
echo "[client]" > ~/.my.cnf
echo "user = root" >> ~/.my.cnf
echo "pass = yourpassword" >> ~/.my.cnf

BENCHMARK DATABASE


mysqlslap --user=root --password="rahasiasaya" --host=10.0.8.8 --concurrency=50 --number-of-queries=100000 --auto-generate-sql-unique-query-number=100000 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key

# contoh hasil benchmark :
# Benchmark
# Average number of seconds to run all queries: 4.031 seconds
# Minimum number of seconds to run all queries: 4.031 seconds
# Maximum number of seconds to run all queries: 4.031 seconds
# Number of clients running queries: 50
# Average number of queries per client: 2000

Pada contoh diatas, ada 100.000 query yang dijalankan, dan membutuhkan waktu 4 detik = 25.000 query / detik.

Panduan lebih detail seputar mysqlslap :
http://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html

RECOVER ROOT USER


sudo /etc/init.d/mysql stop
sudo mysqld_safe --skip-grant-tables &

mysql -e "use mysql; update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; flush privileges;"

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

CREATE A NEW DATABASE & USER


# bikin database & user baru
mysql -e "create user newuser; create database newdatabase;"

# beri newuser akses full ke newdatabase
mysql -e "use mysql; update user set password=PASSWORD('newpassword') where User='newuser'; GRANT ALL PRIVILEGES ON newdatabase.* To 'newuser'@'localhost' IDENTIFIED BY 'newpassword'; flush privileges;"

SETUP MYSQL SERVER (dengan user biasa / bukan root)

Panduannya bisa dibaca disini : [ Install MySQL Server (tanpa root) ]

SETUP MYSQL CLUSTER (dengan user biasa / bukan root)

Panduannya bisa dibaca disini : [ Install MySQL Cluster (tanpa root) ]

SETUP AUTO BACKUP

Panduannya bisa dibaca disini : [ MySQL Archived Backup ]

SETUP REPLIKASI BERANTAI

Replikasi sering dianggap sebagai solusi untuk mengatasi beban kerja yang tinggi. Padahal, untuk banyak kasus, performa sebuah server MySQL sudah lebih dari mencukupi. Tidak perlu menggunakan replikasi lagi.

Replikasi justru paling bermanfaat sebagai fasilitas backup data yang selalu up to date. Dengan memasang fitur ini, maka kita jadi bisa memiliki backup database yang kemudian siap untuk berfungsi jika server database mengalami masalah.

Di resep ini, saya akan melakukan replikasi dengan alur sbb :
Server1 -> Server2 -> Server3

Ya, Server2 akan berfungsi sekaligus sebagai Slave (dari Server1) dan Master (untuk Server3)

Terlampir adalah resep untuk melakukan ini.
Perhatian, Anda perlu menyesuaikan variable-variable ini terlebih dahulu sebelum melakukan copy-paste di Terminal server ybs :

  • exampledb
  • server1
  • server2
  • server3
  • yourpassword
  • replicauser
  • replicapassword
  • Baris-baris “CHANGE MASTER”, yaitu variable MASTER_LOG_FILE dan MASTER_LOG_POS


########## SERVER1 ################
### install LAMP
sudo apt-get install phpmyadmin mysql-server

### persiapan agar bisa menjalankan mysql (client)
### tanpa perlu memasukkan password lagi
echo "[client]" > ~/.my.cnf
echo "user = root" >> ~/.my.cnf
echo "pass = yourpassword" >> ~/.my.cnf

### setting Master1
sudo bash -c 'echo server-id=1 >> /etc/mysql/my.cnf'
sudo bash -c 'echo log-bin = /var/log/mysql/mysql-bin.log >> /etc/mysql/my.cnf'
sudo bash -c 'echo bind-address = 0.0.0.0 >> /etc/mysql/my.cnf'

#### JANGAN lupa untuk hapus baris ini : bind-address = 127.0.0.1
#### jika tidak, maka slave tidak akan bisa connect ke Master1

### restart MySQL
/etc/init.d/mysql restart

### buat user untuk replikasi
mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'%' IDENTIFIED BY 'replicapassword'; FLUSH PRIVILEGES;"

### kunci database
mysql -e "FLUSH TABLES WITH READ LOCK;"

### catat output dari perintah berikut ini
mysql -e "SHOW MASTER STATUS;"

### outputnya seperti ini
#+------------------+----------+--------------+------------------+
#| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
#+------------------+----------+--------------+------------------+
#| mysql-bin.000001 | 618 | exampledb | |
#+------------------+----------+--------------+------------------+

### backup database tsb
mysqldump -u root -p --add-drop-database --databases exampledb > /tmp/exampledb.sql

### buka kembali kunci database
mysql -e "UNlOCK TABLES;"

########## SERVER2 ################
### install LAMP
sudo apt-get install phpmyadmin mysql-server

### persiapan agar bisa menjalankan mysql (client)
### tanpa perlu memasukkan password lagi
echo "[client]" > ~/.my.cnf
echo "user = root" >> ~/.my.cnf
echo "pass = yourpassword" >> ~/.my.cnf

### copy backup dari server1
scp -v root@server1:/tmp/exampledb.sql /tmp/exampledb.sql
mysql -u root -p < /tmp/exampledb.sql ### setting Slave1 / Master2 sudo bash -c 'echo server-id=2 >> /etc/mysql/my.cnf'
sudo bash -c 'echo replicate-do-db=exampledb >> /etc/mysql/my.cnf'

### restart MySQL
/etc/init.d/mysql restart

### mulai setting sebagai Slave1
mysql -e "SLAVE STOP;"

mysql -e "CHANGE MASTER TO MASTER_HOST='server1', MASTER_USER='replicauser', MASTER_PASSWORD='replicapassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=618;"

mysql -e "START SLAVE;"

# ---------------------------------
### mulai setting sebagai Master2
sudo bash -c 'echo log-slave-updates >> /etc/mysql/my.cnf'
sudo bash -c 'echo relay-log=server1-relay-bin >> /etc/mysql/my.cnf'

### restart MySQL
/etc/init.d/mysql restart

### tunggu agar sempat sync dengan Master1
sleep 600

### buat user untuk replikasi
mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'replicauser'@'%' IDENTIFIED BY 'replicapassword'; FLUSH PRIVILEGES;"

### kunci database
mysql -e "FLUSH TABLES WITH READ LOCK;"

### catat output dari perintah berikut ini
mysql -e "SHOW MASTER STATUS;"

### outputnya seperti ini
#+------------------+----------+--------------+------------------+
#| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
#+------------------+----------+--------------+------------------+
#| mysql-bin.000003 | 777 | exampledb | |
#+------------------+----------+--------------+------------------+

### backup database tsb
mysqldump -u root -p --add-drop-database --databases exampledb > /tmp/exampledb.sql

### buka kembali kunci database
mysql -e "UNlOCK TABLES;"

########## SERVER3 ################
### install LAMP
sudo apt-get install phpmyadmin mysql-server

### persiapan agar bisa menjalankan mysql (client)
### tanpa perlu memasukkan password lagi
echo "[client]" > ~/.my.cnf
echo "user = root" >> ~/.my.cnf
echo "pass = yourpassword" >> ~/.my.cnf

### copy backup dari server1
scp -v root@server2:/tmp/exampledb.sql /tmp/exampledb.sql
mysql -u root -p < /tmp/exampledb.sql ### setting Slave2 sudo bash -c 'echo server-id=3 >> /etc/mysql/my.cnf'
sudo bash -c 'echo replicate-do-db=exampledb >> /etc/mysql/my.cnf'

### restart MySQL
/etc/init.d/mysql restart

### mulai setting sebagai Slave1
mysql -e "SLAVE STOP;"

mysql -e "CHANGE MASTER TO MASTER_HOST='server2', MASTER_USER='replicauser', MASTER_PASSWORD='replicapassword', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=777;"

mysql -e "START SLAVE;"

### restart MySQL
/etc/init.d/mysql restart

MEMPROMOSIKAN SERVER2 MENJADI MASTER


### set server2 = master
mysql -e "STOP SLAVE;"

### jangan lupa remark baris
### "log-slave-updates" & "relay-log"
### dari /etc/mysql/my.cnf

BERBAGAI SKEMA REPLIKASI LAINNYA

http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-performance.html

http://dev.mysql.com/doc/refman/5.1/en/replication-solutions-switch.html

Presentasi soal MySQL Replication dari tim engineer Yahoo

PURGE BINARY LOGS

### Untuk hapus binlog jadul = hemat disk space
mysql -e "PURGE BINARY LOGS BEFORE '2012-11-01 00:00:01';"

88 thoughts on “MySQL : Quick Recipes”

  1. inti daru tag ini adalah ganti pasword agar aman dari serangan hacker.namun terkadang hacker punya jutaan cara untuk biSA obrak-abrik

  16. wah tutorial yang bermanfaat gan,
    minta izin backlink gan ke asiapartshardware

    makasih sebelumnya

  47. Mysql itu sangat bermanfaat sekali,, dulu saya waktu sekolah di smtm pernh diajarin mysql,, ternyata berguna sekali ,,buat bikin database rumah sakit ,,database sekloh,, pokonya kalo pake database enak gan

    thanks bwat infonya
    sukses selalu

    kunjungi balik gan

  59. Blog saya juga menampung banyak tutorial bagus loh mas.. hehe
    saya mohon kritik dan saran mengenai project yang saya post di blog saya…

    Terimakasih sebelumnya, silahkan berkunjung

  60. bagus juga buat masukan.. nih…
    biar tahu cara replikasi… untuk konsep database yang lainnya sama g ya..misalkan di sql server..

  80. ketika membuka aplikasi yang menggunakan mysql ketika mysql dijlankan muncul tulisan ini

    InnoDB: Error: unable to create temporary file; errno: 2

    akhirnya tidak bisa menjalankan mysql, apa yang harus dilakukan mas?

Leave a Reply

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