MYSQL Master Slave 正庫備庫架構建置

MYSQK版本
version : mysql-server-5.1.73-3

Master 端

1.修改 my.cnf , 把參數加進去

[root@node02 log]# cat /etc/my.cnf
[mysqld]
server-id=1
log_bin=/vincentyuan/mysql/bin-log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
binlog-do-db=test
binlog_format=mixed

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@node02 log]#

2.重啟資料庫讓參數生效

service mysqld restart

3. 在Master端建立同步用帳號, 並且授與 REPLICATION SLAVE  權限

CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';

FLUSH PRIVILEGES;

4.將Master 資料庫鎖定

 FLUSH TABLES WITH READ LOCK;

5.查看一下 Master 狀態

show master status;

記好 Binlog File 及Position 號碼, 等等要給 Slave 知道從哪邊開始同步

6. exp Master 端的資料庫

mysqldump --opt -u root -p test > test.sql

並把檔案傳送到Slave 端

7. 設定Slave my.cnf

[root@node02 log]# cat /etc/my.cnf 

[mysqld]

server-id=2

replicate-do-db=test

report-host=10.1.53.46

log_bin=/vincentyuan/mysql/bin-log

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

*.server-id 設定為2, server-id  在ㄧ套 Master Slave 架構中, server-id 每台DB都不能一樣 

8.重啟資料庫讓參數生效

service mysqld restart

9.把剛剛的備份檔案匯入Salve 端

mysql -u root -proot123 test < test.sql

10.Slave端設定同步設定

CHANGE MASTER TO

 MASTER_HOST='10.1.53.46',

 MASTER_USER='repl',

 MASTER_PASSWORD='repl',

 MASTER_LOG_FILE='bin-log.000007',

 MASTER_LOG_POS=2083;

設定剛剛在Master 的所看到的 File及Position 

11. 啟動 Slave 

start slave;

12.查看Slave狀態

show slave status \G;

看到

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

都是YES 表示設定成功

13.將Master 端解lock

 unlock tables;

在master 端寫入資料..看Slave 有沒有成功

要是沒有請看... show Slave status \G; 查看有無錯誤訊息 

PS:MYSQL Master、Slave　架構建置好後，Slave 資料是可以被修改的
要是Slave 不想被修改可以在 /etc/my.cnf 加入 read only , 
重啟後除了super user外 其他USER就不能修改資料了

延伸實做
Master、Slave 架構建置好後，Slave 因被修改了PK值導致在同步時找不到PK而同步失敗

已下是Slave status 錯誤訊息

 Last_Errno: 1032
 Last_Error: Could not execute Update_rows event on table test.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log bin-log.000007, end_log_pos 2527

很明顯得跟你說目前同步停在 bin-log.000007, 及Position 號碼為 2527,
意思是說 2527 上一個SQL語句出了錯誤

接下來的同步 Slave 報了錯誤
160805 22:28:17 [Warning] Slave: Can't find record in 'test' Error_code: 1032
160805 22:28:17 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'bin-log.000007' position 2342

上頭是說修復問題後可以從  bin-log.000007' position 2342 重新開始執行同步

這邊也可以看到Slave 是停止同步的

mysql> show slave status \G;

Slave_IO_Running: Yes

Slave_SQL_Running: No

首先先看一下2342 得上一個是執行什麼語法

show binlog events in 'bin-log.000007' from 2083 ;

這邊可以看到他是用ROW 的方式去紀錄 ,(這個資料庫 binlog_format 是 MIXED )
2342 的SQL 語句上一段是2083, 2083的SQL語句是 2273

在使用指令查看 2273的SQL
mysqlbinlog -vv /mysql/bin-log.000007 --base64-output=DECODE-ROWS --start-pos=2273

可以看到2273的最上頭的部分它是一段update 語法, 利用where 條件在去Master , 比對一下欄位資料 並且修改Slave 資料

要是它 where 條件欄位多的話就可以依照資料去回推 Slave 並且修改

修改完後,重新設定一下Slave 端的設定

change master to master_host='10.1.53.46', master_user='repl', 
master_password='repl', 
master_port=3306, 
master_log_file='bin-log.000007', 
master_log_pos=2342;

啟動Slave

start slave 

設定完後, Slave 端就會自己開始在追補以後的資料了

查看/var/log/mysqld.log　　

160805 23:05:59 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='10.1.53.46', master_port='3306', master_log_file='bin-log.000007', master_log_pos='3605'. New state master_host='10.1.53.46', master_port='3306', master_log_file='bin-log.000007', master_log_pos='2342'.
160805 23:06:02 [Note] Slave SQL thread initialized, starting replication in log 'bin-log.000007' at position 2342, relay log './mysqld-relay-bin.000001' position: 4
160805 23:06:12 [Note] Slave I/O thread: connected to master 'repl@10.1.53.46:3306',replication started in log 'bin-log.000007' at position 2342

沒有在看到錯誤了！！！！