Cara menggunakan replikasi sinkronisasi database mysql
|
Untuk dapat mencoba melakukan replikasi master to master mysql ini, silakan perangkat lunak (minimal) 2 server mysql siap pakai, contoh dengan IP 10. 2. 9. 1 dan 10. 2. 9. 2 Langkah Replikasi Master ke Master MySQL1. Sesuaikan konfigurasi masing-masing server MySQL sebagai master. Ubah pada tiap file /etc/my. cnf [mysqld] port=3306 datadir=/home/mysql/ socket=/usr/local/mysql/tmp/mysql.sock user=mysql old_passwords=0 #symbolic-links=0 #query_cache_size = 268435456 query_cache_size = 256M query_cache_type=1 query_cache_limit = 1048576 log = 1 long_query_time = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log general_log = 1 general_log_file = /var/log/mysqld.log server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 bind-address = 0.0.0.0 local-infile = 0 sedangkan untuk server master 2 [mysqld] port=3306 datadir=/home/mysql socket=/usr/local/mysql/tmp/mysql.sock user=mysql old_passwords=0 #symbolic-links=0 query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576 log = 1 long_query_time = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log general_log = 1 general_log_file = /var/log/mysqld.log server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 bind-address = 0.0.0.0 local-infile = 0_ 2. Instal replikasi ke masing-masing server, login ke server1 sebagai root mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 36 Server version: 5.5.12-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement mysql> create user 'mysqlchkuser'@'localhost' identified by 'mysql321'; Query OK, 0 rows affected (0.06 sec) mysql> create user 'mmm_monitor'@'%' identified by 'monitor_password'; Query OK, 0 rows affected (0.06 sec) mysql> create user 'mmm_agent'@'%' identified by 'agent_password'; Query OK, 0 rows affected (0.00 sec) mysql> create user 'replication'@'%' identified by 'replication_password'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'monitor_password'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY 'agent_password'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication_password'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1044 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) h. Dump database master server 1 ke master server 2 dengan mysqldump mysqldump -u root -p --all-databases > /tmp/database-backup.sql i. Pindahkan file backup ke master server 2 dengan scp, scp /tmp/database-backup.sql 10.2.9.2:~/_ j. Buka kunci tabel di master 1 mysql> flush privileges; Query OK, 0 rows affected (0.46 sec) mysql> CHANGE MASTER TO master_host='10.2.9.1', master_port=3306, master_user='replication', -> master_password='replication_password', master_log_file='mysql-bin.000001', master_log_pos=1044; c. jalankan slave pada master server 2 dan cek status slave-nya mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G run command
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.2.9.1
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1044
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 1044
Relay_Log_Space: 409
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
_d. cek plus status master-nya mysql> show master status -> ; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 27957968 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 27957968 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 4. Kembali ke master server 1 dan setting master mysql> CHANGE MASTER TO master_host='10.2.9.2', master_port=3306, master_user='replication', -> master_password='replication_password', master_log_file='mysql-bin.000002', master_log_pos=27957968; Query OK, 0 rows affected (0.34 sec)_ b. jalankan slave dan cek statusnya mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G run command
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.2.9.1
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 27957968
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 27957968
Relay_Log_Space: 409
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)
_5. Uji coba replikasi [mysqld] port=3306 datadir=/home/mysql socket=/usr/local/mysql/tmp/mysql.sock user=mysql old_passwords=0 #symbolic-links=0 query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576 log = 1 long_query_time = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log general_log = 1 general_log_file = /var/log/mysqld.log server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 bind-address = 0.0.0.0 local-infile = 0_0 c. Coba cek di master server 2, harusnya database contohreplikasi otomatis juga dibuatkan [mysqld] port=3306 datadir=/home/mysql socket=/usr/local/mysql/tmp/mysql.sock user=mysql old_passwords=0 #symbolic-links=0 query_cache_size = 268435456 query_cache_type=1 query_cache_limit=1048576 log = 1 long_query_time = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log general_log = 1 general_log_file = /var/log/mysqld.log server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 bind-address = 0.0.0.0 local-infile = 0_1 Sekian. Semoga berhasil dan selamat menikmati replikasi data master ke master MySQL |
