家中有一台synology nas  內建的是 MariaDB 5.5.43 (mysql 分支)
另外有一fedora linux 的MySQL 5.1  
最近嘗試將兩部MySQL 表兄弟做replication,以下為實戰筆記....

區分以下段落
0.replication概要
1.環境說明
2.設定mater
3.設定slave
4.異常處理

0.mysql replication 官方說法
參考: http://dev.mysql.com/doc/refman/5.0/en/replication...

0.1 mysql的replication 單向複寫
可設定一個master,多個slave,靠啟用binary log來將異動資料由master送給slave
 

0.2 Scale out ,分散讀寫不同作業,提高效能

 

0.3 master異常時,切換到slave維持運作
mysql>change master to ...   可將master 指到的slave1
正常情況
 

異常時切換master
 



1.環境說明
master: MySQL 5.1 on fedora linux , ip=master.mydomain.com
slave : MariaDB 5.5 on Synology Nas (mysql 分支) , dns=slave.mydomain.com ,ip=slave_db_ip_address

2.設定master 
2.1.edit my.cnf@master db   (/etc/my.cnf)
#master config
[mysqld]
server-id=9419601
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=db1
binlog_do_db=db2
binlog_do_db=db3

2.2 create replication user @master db

CREATE USER 'rep_user'@'slave_db_ip_address' IDENTIFIED BY  'rep_user_password';

GRANT REPLICATION SLAVE , REPLICATION CLIENT ON * . * TO  'rep_user'@'slave_db_ip_address' IDENTIFIED BY  'rep_user_password';

2.3 restart@master db

2.4 顯示master狀態@master db
mysql>show master status;

變數
File mysql-bin.000009
Position 11724
Binlog_Do_DB db1,db2,db3
Binlog_Ignore_DB

2.5 顯示目前連線之slave host@master db
mysql>show slave hosts;

Server_id Host Port Rpl_recovery_rank Master_id
1444825616 slave.mydomain.com 3306 0 9419601


3.設定slave

3.1 edit  my.cnf@slave db   (/etc/mysql/my.cnf)

[mysqld]
server-id=1444822168                #由phpMyadmin之Replication頁籤/slave 取得
report-host=slave.mydomain.com  #此hostname 將回報給master 
slave-skip-errors = all                 #避免發生錯誤時,中斷replication

3.2 restart@slave db
/usr/syno/bin/synopkg restart MariaDB 

3.3 設定mater@slave db

CHANGE MASTER TO

  MASTER_HOST='master.mydomain.com',
  MASTER_USER='rep_user',

  MASTER_PASSWORD='rep_user_password',
 MASTER_PORT=3306

3.4 顯示slave 狀態@slave db
mysql>show slave status;

變數
Slave_IO_State Waiting for master to send event
Master_Host master.myadomain.com
Master_User rep_user
Master_Port 3306
Connect_Retry 60
Master_Log_File mysql-bin.000009
Read_Master_Log_Pos 11450
Relay_Log_File mysqld-relay-bin.000013
Relay_Log_Pos 8157
Relay_Master_Log_File mysql-bin.000009
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 11450
Relay_Log_Space 8452
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


4.replication異常停止 處理方法
4.1 顯示master 狀態@master db
mysql>show master status;
紀錄File 及Position

變數
File mysql-bin.000009
Position 11724


4.2 重設master_log_file 及master_log_pos @slave db
mysql>change master to 
master_host='master.mydomain.com',
 master_user='rep_user', 
 master_password='rep_user_password', 
 master_port=3306, 
 master_log_file='mysql-bin.000009', 
 master_log_pos=11724 ;






  





michaelfang 發表在 痞客邦 PIXNET 留言(0) 人氣()