MySQL Master-Master Replication On 3 Nodes ( Centos 5)
Insya Allah berguna deh ya..tapi klo kata teman-teman di komunitas MySQL Indonesia..Mysql replikasi adalah solusi yang buruk untuk server produksi, lebih baik mysql cluster..Tapi gak apa-apa lah..Secara konsep seh hampir sama..
Kita ada 3 server..
Jadi nanti server2 akan update databasenya secara otomatis ketika ada query di server1 atau server3, begitu juga bagi server1 dan server3. Pokoknya semuanya akan jadi saling mengupdate.
ip masing-masing server tersebut adalah :
server1 = 192.168.0.1
server2 = 192.168.0.2
server3 = 192.168.0.3
Paket yang dibutuhkan mysql, mysql-server
Untuk menginstallnya :
yum install mysql mysql-server
Install di setiap masing-masing server.
Setelah itu jalankan mysqlnya
/etc/init.d/mysqld start
edit file my.cnf di server1 ( /etc/my.cnf )
tambahkan baris berikut ini :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [mysqld] server-id = 1 replicate-same-server-id = 0 auto-increment-increment = 4 auto-increment-offset = 1 master-host = 192.168.0.3 master-user = user1 password = password master-connect-retry = 60 replicate-do-db = testdb log-bin = /var/log/mysql/mysql-bin.log binlog-do-db = testdb log-slave-updates relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index expire_logs_days = 10 max_binlog_size = 500M |
restart mysqlnya
1 | /etc/init.d/mysqld restart |
edit file my.cnf di server2, tambahkan baris berikut :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [mysqld] server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 4 auto-increment-offset = 1 master-host = 192.168.0.1 master-user = user2 master-password = password master-connect-retry = 60 replicate-do-db = testdb log-bin = /var/log/mysql/mysql-bin.log binlog-do-db = testdb log-slave-updates relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index expire_logs_days = 10 max_binlog_size = 500M |
restart mysqlnya
1 | /etc/init.d/mysqld restart |
edit file my.cnf di server3, tambahkan juga baris seperti ini :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [mysqld] server-id = 3 replicate-same-server-id = 0 auto-increment-increment = 4 auto-increment-offset = 1 master-host = 192.168.0.2 master-user = user3 master-password = password master-connect-retry = 60 replicate-do-db = testdb log-bin = /var/log/mysql/mysql-bin.log binlog-do-db = testdb log-slave-updates relay-log = /var/lib/mysql/slave-relay.log relay-log-index = /var/lib/mysql/slave-relay-log.index expire_logs_days = 10 max_binlog_size = 500M |
1 | /etc/init.d/mysqld restart |
itu dulu ya..mau pulang dulu,,kantor udah sepi…hihihi…besok sambung lagi ^_^ …
Sambung lagi ^_^ …
Tadi kita udah setting file my.cnf di masing-masing server, sekarang kita akan kasih grant ke masing-masing user yang tadi kita setting di file my.cnf, caranya :
Login ke shell Mysql di server1 :
mysql -u root
lalu jalankan perintah ini:
1 2 3 | GRANT REPLICATION SLAVE ON *.* TO 'user1'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; quit; |
server2
1 2 3 | GRANT REPLICATION SLAVE ON *.* TO user2'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; quit; |
server3
1 2 3 | GRANT REPLICATION SLAVE ON *.* TO 'user3'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES; quit; |
nanti sambung lagi..kerja dulu ^_^ …
Sambung lagi neh ^_^
sekarang login ke mysql di server1, server2 dan server3
trus buat database testdb ( sesuaikan dengan yang ada di my.cnf tadi )
1 | create database testdb; |
di server1 jalankan command ini
1 2 3 4 5 | USE testdb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; <code> nanti akan keluar seperti ini |
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000010 | 98 | testdb | |
+——————+———-+————–+——————+
1 row in set (0.00 sec)
Ingat, jangan keluar dari shellnya mysql, karena kalau keluar, table locknya akan di-remove.
Lalu kita export databasenya
1 | mysqldump -u root -ppasswordrootsqlanda --opt testdb > test.sql |
lalu kirim ke server2 dan server3 melalui scp
1 2 | scp test.sql 192.168.0.2:/home/user2 scp test.sql 192.168.0.3:/home/user3 |
lalu unlock table yang di server1
1 2 | UNLOCK TABLES; quit; |
setting replikasi di server2
jalankan command ini
1 2 | mysqladmin --user=root --password=rootsqlpassword stop-slave mysql -u root -prootsqlpassword testdb < testdb.sql |
login ke shell mysql
jalankan command
1 2 3 4 5 6 7 8 9 | USE testdb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000010 | 1067 | testdb | | +------------------+----------+--------------+------------------+ |
lalu unlock table nya
1 | UNLOCK TABLES; |
Lalu jalankan perintah ini agar server2 slave dari server1
1 | CHANGE MASTER TO MASTER_HOST='192.168.0.1', MASTER_USER='user2', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=98; |
1 2 | START SLAVE; SHOW SLAVE STATUS \G; |
nanti akan keluar seperti ini
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.1 Master_User: user2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 98 Relay_Log_File: slave-relay.000002 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 |
setting replikasi di server3
jalankan command ini
1 2 | mysqladmin --user=root --password=rootsqlpassword stop-slave mysql -u root -prootsqlpassword testdb < testdb.sql |
login ke shell mysql
jalankan command
1 2 3 4 5 6 7 8 | USE testdb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000010 | 1067 | testdb | | +------------------+----------+--------------+------------------+ |
lalu unlock table nya
1 | UNLOCK TABLES; |
Lalu jalankan perintah ini agar server3 slave dari server2
1 | CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='user3', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1067; |
1 2 | START SLAVE; SHOW SLAVE STATUS \G; |
nanti akan keluar seperti ini
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.2 Master_User: user3 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 1067 Relay_Log_File: slave-relay.000002 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 |
login ke shell mysql di server1
jalankan
1 | STOP SLAVE; |
lalu jalankan ini
1 2 | mysqladmin --user=root --password=rootsqlpassword stop-slave mysql -u root -rootsqlpassword testdb < testdb.sql |
login ke shell mysql
jalankan command
1 2 3 4 5 6 7 8 9 | USE testdb; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000010 | 1067 | testdb | | +------------------+----------+--------------+------------------+ |
lalu unlock table nya
1 | UNLOCK TABLES; |
Lalu jalankan perintah ini agar server1 slave dari server3
1 | CHANGE MASTER TO MASTER_HOST='192.168.0.3', MASTER_USER='user3', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=1067; |
1 2 | START SLAVE; SHOW SLAVE STATUS \G; |
nanti akan keluar seperti ini
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.3 Master_User: user3 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 1067 Relay_Log_File: slave-relay.000002 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: testdb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 |
Sekarang coba insert data di testdb
perhatikan di masing2 database server1, server2 dan server3.
Akhirnya selesai juga postingnya..
Terima kasih bagi yang sudah membaca ^_^
referensi
http://www.howtoforge.com/setting-up-master-master-replication-on-four-nodes-with-mysql-5-on-debian-etch
