ဒါကေတာ့ sql replication ကို master to master လုပ္မွာျဖစ္ပါတယ္ . နမူနာအေနနဲ႔ ဆာဗာ ၁ နဲ႔ ၂ ဆိုျပီး စမ္းလုပ္ၾကည့္ရေအာင္
Master 1 IP Address : 172.168.0.1
Master 2 IP Address: 172.168.0.2
Database to replicate: db1, db2, and db3
ဒီနမူနာကို စမ္းမယ္သူအေနနဲ႔ လိုအပ္တဲ့ SQL ကိုသြင္းျပီးသားျဖစ္ေနေလာက္မွာပါ .. မရွိေသးရင္လည္းသြင္းလိုက္ျပီး ပါတ္စ္၀ါဒ့္တစ္ခါတည္းသက္မွတ္ေပးလိုက္ပါ ..
#yum install mysql mysql-server
#mysqladmin -u root password new_mysql_password
ေအာက္ကအဆင့္ေတြကေတာ့ ဆာဗာ ၂ ခုလံုးမွာ လုပ္မွာပါ .. ဆာဗာ ၁ ဆိုရင္ ဆာဗာ ၁ မွာ သံုးမယ့္ ကြန္မန္းလိုင္းေတြျဖစ္ျပီး ဆာဗာ ၂ ဆိုရင္ေတာ့ ၂ေအာက္မွာသံုးမွာပါ ..
ပထမဆံုးအေနနဲ႔ ဆာဗာ ၂ ခုလံုးရဲ႕ /var/log ေအာက္မွာ mysql ကိုတည္ေဆာက္ေပးျပီး owner ကိုလည္း mysql ဆိုျပီး ခ်ိန္းေပးထားလိုက္ပါ
#mkdir /var/log/mysql
#chown apache:apache /var/log/mysql
ွခု ဆာဗာ ၁ ကေနစရေအာင္
sql ထဲ၀င္မယ္ ျပီးရင္ေအာက္ကအတိုင္းဆက္သြားလိုက္မယ္ ကြန္မန္႔ေတြ ဖတ္ရင္ သံုးရင္းနားလည္လိမ့္မယ္ထင္လို႔ တစ္ခုခ်င္းမရွင္းေတာ့ပါဘူး .. လိုအပ္ရင္ေတာ့ ျပန္ေမးလို႔ရပါတယ္ .. ( ^_^ )..
#mysql -u root -p
Enter Password:
mysql> CREATE USER 'slave_user'@'172.168.0.2' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'172.168.0.2' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
ဆာဗာ ၂ မွာလည္း အလားတူပဲသက္မွတ္ပါ့မယ္ ..
#mysql -u root -p
Enter Password:
mysql> CREATE USER 'slave_user'@'172.168.0.1' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'172.168.0.1' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
ဒါဆိုရင္ မာစတာအတြက္ လိုအပ္တာေတြျပင္ျပီးပါျပိ ခု SLAVE အတြက္ လုပ္ပါ့မယ္ .. အဲဒါမတိုင္ခင္ sql config အရင္လုပ္ပါမယ္ . လုပ္မယ့္ ေနရာကေတာ့ /etc/my.cnf ဆိုတဲ့ configuration မွာပါ ..
ဆာဗာ ၁ ရဲ႕ /etc/my.cnf မွာေအာက္ကအတို္င္းထည့္ေပးမယ္.
server-id = 1
master-host =172.168.0.2
master-user = slave_user
master-password = password
master-connect-retry = 60
slave-net-timeout = 60
replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3
log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index
binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3
relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
ဒီေအာက္ကေတာ့ ဆာဗာ ၂ ရဲ႕ /etc/my.cnf အထဲမွာ ထည့္မွာပါ
server-id = 2
master-host = 172.168.0.1
master-user = slave_user
master-password = password
master-connect-retry = 60
slave-net-timeout = 60
replicate-do-db = db1
replicate-do-db = db2
replicate-do-db = db3
log-bin = /var/log/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/master-log-bin.index
binlog-do-db = db1
binlog-do-db = db2
binlog-do-db = db3
relay-log = /var/lib/mysqld-relay-bin
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
ျပီးသြားျပီဆိုရင္ ဆာဗာႏွစ္ခုလံုးရဲ႕ sql ေတြကို restart ခ်လိုက္ပါ့မယ္ .
#service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
ခုကေနစျပီးေတာ့ SLAVE အတြက္ စျပီး လုပ္ပါေတာ့မယ္
ဆာဗာ ၁ - ၂ အတူတူပဲျဖစ္လို တစ္ခါတည္းပဲေရးေပးလိုက္ေတာ့မယ္ ..
#mysql -u root -p
mysql>USE db1;
mysql>UNLOCK TABLES;
mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;
+------------------+----------+----------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+----------------------+-------------------+
| mysql-bin.000001 | 127 | radius,smbind,Syslog | |
+------------------+----------+----------------------+-------------------+
1 row in set (0.00 sec)
(မွတ္ခ်က္ ဆာဗာႏွစ္ခုလံုး အေပၚကအဆင့္ ၄ ဆင့္လံုးလုပ္ေပးရမွာပါ).ေနာက္ဆံုး table ကေတာ့ လိုအပ္တဲ့ log-file & position ၾကည့္ဖို႔ေခၚလိုက္တာပါ .. ဆက္သြားရေအာင္
ဒီေအာက္ကေတာ့ ဆာဗာ ၁ မွာျဖစ္ပါတယ္ .
mysql> CHANGE MASTER TO MASTER_HOST = '172.168.0.2', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS=127;
ဒီေအာက္ကေတာ့ ဆာဗာ ၂ မွာျဖစ္ပါတယ္ .
mysql> CHANGE MASTER TO MASTER_HOST = '172.168.0.1', MASTER_USER = 'slave_user', MASTER_PASSWORD = 'password', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS=127;
ခုတစ္ခါ ဆာဗာ ႏွစ္လံုးစလံုးရဲ႕ sql slave ေတြကို စတင္ရန္းခိုင္းပါေတာ့မယ္
ဒီတိုင္းေလးေပါ့
mysql> SLAVES START;
မိမိလုပ္လိုက္တဲ့ master-master replication အလုပ္လုပ္ေနလား မလုပ္ေနဘူးလားဆိုတာကို သိေအာင္လို႔ ေအာက္ကအတိုင္းေလးစစ္ၾကည့္ပါ့မယ္ .
mysql>SHOW SLAVE STATUS\G
mysql> show slave status\G
************************ 1. row *************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.168.0.2
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 127
Relay_Log_File: slave-relay.000001
Relay_Log_Pos: 127
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1,db2,db3
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: 1581
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
1 row in set (0.00 sec)
ဒီေအာက္ကလိုျဖစ္ရပါ့မယ္
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
အဲ့လိုမျဖစ္ပဲ အဲယာမက္ေဆဒ့္တက္ေနမယ္ဆိုရင္ေတာ့ sql error log file ျဖစ္တဲ့ /var/log/mysqld.log ကိုသြားဖတ္ပါ့မယ္
#tail /var/log/mysqld.log
အဲဒီေနရာကေန ေျပာတဲ့အခ်က္ေတြကိုလိုက္ျပင္ရင္းျပင္ရင္းနဲ႔ sql replication ကိုအဆင္ေျပစြာ အသံုးျပဳနိုင္ပါေစ ..
