Mysql 主從設定

標籤：

一、準備伺服器

由於MySQL不同版本之間的(二進位日誌)binlog格式可能會不一樣，因此最好的搭配組合是Master的MySQL版本和Slave的版本相同或者更低，Master的版本肯定不能高於Slave版本。

我測試設定成功的伺服器：
主伺服器(以下簡稱Master)的版本是3.23.58，
從伺服器(以下簡稱Slave)的版本是5.0.91，這個跨度應該算比較大了吧。

Master主機名稱為：A(IP:192.168.0.1)，
Slave  主機名稱為：B(IP:192.168.0.2)，
2個MySQL的basedir目錄都是/usr/local/mysql，datadir都是：/var/lib/mysql/ 。
預設安裝的就如此。

二、設定同步Master 伺服器

1、設定my.cnf

# cp /usr/share/doc/mysql-server-3.23.58/my-large.cnf /etc/my.cnf
對於my-large.cnf這個檔案個人覺得只是mysql大型資料庫配置的demo檔案，copy該目錄下的my-small.cnf應該是一樣的;
因為我伺服器配置比較高，看large裡面參數多，省事就選他用了。
修改 my.cnf 檔案：

log-bin = mysql-bin        #指定產生binlog檔案名稱
server-id       = 1            #此id不可重複
binlog-do-db = news        #需要備份的資料庫名，如果備份多個資料庫，重複設定這個選項即可
binlog-ignore-db = mysql  #不需要同步的資料庫名，可以設定多條
binlog-ignore-db = test     #不需要同步的資料庫名，可以設定多條
log-slave-updates            #這個參數一定要加上，否則不會給更新的記錄些到二進位檔案裡
slave-skip-errors            #是跳過錯誤，繼續執行複製操作

2、建立使用者
$mysql -u root -p    #進入mysql
mysql>CREATE USER TO ‘backup‘@‘%‘ DENTIFIED BY ’backuppass’;   #建立備份使用者backup
mysql>GRANT FILE,SELECT,RELOAD ON *.* TO ‘backup‘@‘%‘;      #設定許可權
因為mysql版本太老了，所以不支援REPLICATION SLAVE，否則用下面的：
mysql>GRANT FILE,SELECT,RELOAD,REPLICATION SLAVE ON *.* TO ‘backup‘@‘%‘;        #設定許可權

mysql>FLUSH PRIVILEGES ;

3、在Replication完成前，Master跟Slave的資料內容必須一致，故在備份匯出資料庫前先Lock成唯讀。
mysql > FLUSH TABLES WITH READ LOCK;

4、需要同步的news資料庫匯出，可以使用mysqldump或者是tar的方式壓縮起來。

如果用mysqldump的方式：
$mysqldump -u root -p news > news.sql       #匯出資料庫成檔案news.sql

因為2個資料版本不一樣，且儲存編碼不一樣，我用的上面的方法。

如果用壓縮方式：
cd /var/lib/mysql/  #mysql庫目錄
tar zcvf news.tar.gz news

5、匯出資料庫後，解除唯讀：

mysql>UNLOCK TABLES;

6、 重啟mysql，前面設定的my.cnf生效；重啟後，在Master伺服器上的add、update、delete等操作都會記錄在binlog檔案中

$/etc/init.d/mysql restart   或者 service mysqld restart

7、記錄複製啟動斷點
執行以下命令，記錄從伺服器開始啟動複製的斷點，包括日誌名和位移量。
　 mysql> SHOW MASTER STATUS；
比如：
　 mysql> show master status\G
　　　　 File: mysql-bin.001
　　　　 Position: 73
　　　　 Binlog_Do_DB:
                 Binlog_Ignore_DB:
記錄檔名是：mysql-bin.001
位移量是：73
注意，等會從伺服器將從這個位置開始複製。需要記錄下這2個地方。

二、設定同步Slave伺服器

1.   匯入資料到從伺服器器:
我用的是$mysql -u root -p news < news.sql

2、如果是用壓縮的辦法：
傳輸拿到主庫資料包、解包
# cd /var/lib/mysql/
# scp 192.168.0.1:/var/lib/mysql/news.tar.gz .
# tar zxvf var.tar.gz
查看修改news檔案夾許可權
# chown -R mysql:mysql news

3, 設定同步Slave的my.cnf
server-id       = 2 
# log-bin=mysql-bin    #我沒有用到,只需要從A同步到B即可
master-host     =   192.168.0.1
master-user     =   backup
master-password =  backuppass
master-port     =  3306
#上面是前面添加的使用者名稱和密碼,下面3行是新版本的sql內建的表。
replicate-ignore-db = mysql       #忽略掉的資料庫
replicate-ignore-db = test           #忽略掉的資料庫
replicate-ignore-db = information_schema  #忽略掉的資料庫
replicate-do-db = news  #需要備份的資料庫名
replicate_do_table = news.user   #需要備份的資料表名
master-connect-retry=60  #如果從伺服器發現主伺服器斷掉，重新串連的時間差(秒)
#log-slave-updates  #我沒有用到,如果從伺服器也要更新記錄到二進位檔案裡
slave-skip-errors  #是跳過錯誤，繼續執行複製操作

設定完成後，首先檢查/var/lib/mysql/目錄下是否有mysql的啟動關閉日誌，類似的為*.info,localhost*,ib*之類的日誌 資訊。如果有全部刪除(提示：先停止mysql服務，再刪除)，然後啟動mysql服務。

4、驗證串連MASTER
# mysql -h192.168.0.1 -ubackup -pbackuppass
 主機IP、使用者名稱、密碼前面不能帶有空格

5、設定Master伺服器資訊，讓Slave知道Master的位置

以往的方式是直接在my.cnf中設定master的相關訊息；但是，這樣的方式已經改了，必須使用在 mysql下執行【CHANGE MASTER TO】的指令

在my.cnf中master是錯誤的

master-host     =   192.168.0.1
master-user     =   backup
master-password =  backuppass
master-port     =  3306

slave stop;  
MySQL> CHANGE MASTER TO 
-> MASTER_HOST=‘192.168.0.1‘,  
-> MASTER_USER=‘backup‘,  
-> MASTER_PASSWORD=‘backuppass‘,  
-> MASTER_LOG_FILE=‘mysql-bin.001‘,  
-> MASTER_LOG_POS=73;  
slave start; 

6，檢查同步是否成功
用show slave status;看一下從伺服器的同步情況
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果都是yes，那代表已經在同步 。

Seconds_Behind_Master是否為0，0就是已經同步了

出現Slave_IO_Running:NO，可能需要開啟：
slave stop;  
SLAVE start IO_THREAD
slave start; 

另外，出現Slave_IO_Running:NO還有一個原因是slave上沒有許可權讀master上的資料。

Mysql 主從設定