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';"
