https://github.com/hanafiah/live005
repo:
master
sebelum teruskan langkah di bawah. pastikan anda dah ada db. dalam contoh ni db yang digunakan adalah employees . setup db di master server sahaja
1. edit my.cnf seperti di bawah
bind-address = 0.0.0.0
# boleh gunakan ip address server. jika direct install mariadb/mysql
# bind-address = 178.128.219.253
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = employees
pastikan restart mysql selepas edit my.cnf
2. run mysql shell dan create replika user
mysql shell
mysql -u root
create user
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
3. flush priviledge
FLUSH PRIVILEGES;
4. lock table
USE employees;
FLUSH TABLES WITH READ LOCK;
5. run command master status dan catat result
SHOW MASTER STATUS;
contoh result
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
---|---|---|---|
mysql-bin.000001 | 645 | employees |
1 row in set (0.000 sec)
kita akan gunakan maklumat di kolum File dan Position masa setup slave nanti
6. exit mysql shell
exit
7. export db employees
mysqldump -u root --opt employees > /sql/master_employees.sql
8. masuk ke mysql shell semula
mysql -u root
9. unlock table semula
use employees;
UNLOCK TABLES;
10. exit mysql shell. dan selesai bahagian master
slave
pastikan kita dah setup bahagian master terlebih dulu
1. edit my.cnf seperti di bawah
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = employees
pastikan restart mysql selepas edit my.cnf
2. copy master_employees yg dah di export dari master . kit aboleh gunakan command scp
scp root@178.128.219.253:/root/docker-webstack/sql/master_employees.sql master_employees.sql
3. masuk ke mysql shell dan create database employees
mysql -u root
CREATE DATABASE employees;
EXIT;
4. import master_employees.sql kedalam db employees
mysql -u root employees < master_employees.sql
5. masuk balik ke mysql shell dan run command berikut
mysql -u root
CHANGE MASTER TO MASTER_HOST='178.128.219.253',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=645;
178.128.219.253
adalah ip address master server slave_user
adalah user yg kita create di mastermysql-bin.000001
dan 645
adalah result yg kita dapat masa run SHOW MASTER STATUS;
di master
contoh result dari master tadi
File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
---|---|---|---|
mysql-bin.000001 | 645 | employees |
1 row in set (0.000 sec)
6. start slave
START SLAVE;
7. semak status
SHOW SLAVE STATUS\G
perhatikan bagian
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
pastikan dua2 ni running. jika ada error, boleh semak di bahagian error msg
10. exit mysql shell. dan selesai bahagian slave
anda boleh insert rekod di master dan semak sama ada rekod tersebut dapat di sync ke slave. untuk slave yg melebihi 1, ulangi proses dari step 1 di atas
Ulasan
Catat Ulasan