在windows和centos做mysql主從搭建方法

網上很多主從搭建的方法，但大多都是同平台下搭建，本篇文章是以centos服務器為主，windows2008服務器為從，搭建mysql主從。

一些想法：windows和centos平台不同，是否能做mysql主從，後來想了想，mysql也是可以在windows上跑的，同是mysql應該不會有什麼問題存在，然後此篇文章產生了……

MySQL主從複製原理：

MySQL的主從複製是一個異步的複製過程（雖然一般情況下感覺是實時的），數據將從一個Mysql數據庫（我們稱之為Master）複製到另一個Mysql數據庫（我們稱之為Slave），在Master與Slave之間實現整個主從複製的過程是由三個線程參與完成的。其中有兩個線程（SQL線程和IO線程）在Slave端，另一個線程（I/O線程）在Master端。

要實現MySQL的主從複製，首先必須要先打開Master端的binlog記錄功能。因為整個複製過程實際上就是slave從master端獲取binlog日誌，然後再在slave上以相同順序執行獲取的binlog日誌中所記錄的各種SQL操作。

簡單點來說：

就是主服務器將改變的內容記錄到二進製日誌文件（binlog）中，從服務器將主服務器的二進製文件拷貝到它的中繼日誌（relay log），並重新開啟SQL線程，從中繼日誌中讀取二進製日誌，使其數據和主服務器的保持一致，最後slave端的I/O線程和SQL線程將進入睡眠狀態，等待下一次被喚醒。

注意：如果你的版本和我所寫的版本不同，可能在配置過程中會有問題出現，為了確保成功性，建議現在本地環境測試成功，再對線上服務進行操作，請確保服務器的時間同步一致。

服務器配置：

linux主192.168.0.70
版本Centos6.7 nginx1.10 php5.4.45 mysql5.5.48

windows從192.168.0.71
版本IIS7 mysql5.5.54 php5.6.29

master centos

設置master mysql配置文件/etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id = 1
expire_logs_days = 7

log-bin表示開啟mysql的binlog日誌功能，指定的mysql-bin表示日誌文件的命名格式，會生成文件名為mysql-bin.000001、mysql-bin.000002、等的日誌文件。

server-id是節點標識，主、從的節點必須是全局唯一，不能相同。

expire_logs_days二進製日誌自動刪除的天數，0則是不自動刪除。

注意，log-bin和server-id都是在[mysqld]模塊內的。

注意，先在my.cnf中查找相關參數並修改，如果查找不到則手動添加，參數不能重複。

修改配置文件完成後重啟mysql

/etc/init.d/mysql restart

登錄mysql查看server-id的值是否為1

SHOW VARIABLES LIKE 'server_id';

查看binglog功能是否開啟

mysql>SHOW VARIABLES LIKE 'log_bin';

建立數據庫whsir（我這裏作為演示用，見附錄3）

mysql>CREATE DATABASE whsir;

查看bin-log的信息

mysql>SHOW MASTER STATUS;

注意：這裏記住File的值：mysql-bin.000008和Position的值：107，後麵會用到。

根據主從複製原理，從庫想要和主庫同步，必須要有一個連接的帳號，這個帳號是要在主庫上創建的。

授權可以來讀取日誌文件的用戶

mysql>GRANT REPLICATION SLAVE ON *.* TO 'zhu'@'192.168.0.71' IDENTIFIED BY '123456';

REPLICATION SLAVE為mysql的必須權限，此處不要ALL。

.表示所有庫和表，此處也可以指定具體的庫和表。例如aaa庫的bbb表：aaa.bbb

'zhu'@'192.168.0.71'，同步的帳號和授權的主機地址，主機地址可以使用%進行通配。

此處為演示用，密碼就設置了123456。

刷新權限，使其生效

mysql>FLUSH PRIVILEGES;

查看zhu帳號是否生效

mysql>SELECT USER,HOST FROM mysql.user;

+------+--------------+
| user | host |
+------+--------------+
| root | 127.0.0.1 |
| zhu | 192.168.0.71 |
| root | ::1 |
| root | localhost |
| test | localhost |
+------+--------------+

為了確保主服務器的賬戶配置正確，我們在從slave服務器上，登錄下master mysql（此處如果連不上，查看是不是防火牆問題）
mysql -uzhu -p123456 -h192.168.0.70

slave windows

修改my.ini 原來配置文件中有的話就不用在添加了

[mysqld]
server-id = 2
log-bin=mysql-bin #可以注釋掉log-bin，從庫一般不開啟log-bin功能
relay-log = mysql-relay-bin #中繼日誌，可注釋掉
expire_logs_days = 7 #自動清理7天前的日誌，前麵如果注釋了，此處也不用添加了，注釋掉吧
read_only = on #隻讀
replicate-wild-do-table=whsir.% #指定複製的庫和表，此處%表示通配所有，見附錄9

my.ini配置中一定要配置的就是server-id其他都可以省略掉！！！

配置好my.ini後重啟mysql（不知道windows中如何重啟mysql的請自行google）

net stop mysql
net start mysql

創建whsir庫（我這裏做演示用，直接就創建了，沒有導入數據）

mysql>CREATE DATABASE whsir;

登錄mysql查看server-id的值是否為2

mysql>SHOW VARIABLES LIKE 'server_id';

先停止slave同步，也可以mysql>STOP SLAVE;

mysql>SLAVE STOP;

mysql>CHANGE MASTER TO
->MASTER_HOST='192.168.0.70',
->MASTER_USER='zhu',
->MASTER_PASSWORD='123456',
->MASTER_PORT=3306,
->MASTER_LOG_FILE='mysql-bin.000008',
->MASTER_LOG_POS=107,
->MASTER_CONNECT_RETRY=10;

MASTER_PORT是主服務器端口，默認就是3306。

MASTER_CONNECT_RETRY是連接失敗後等待的秒數。

注意上麵是分開寫的，你也可以寫在一行裏。

上述操作原理其實是把用戶的信息寫入到了從庫的data/master.info中了

上述配置好後啟動slave同步

mysql>SLAVE START;

查看狀態\G就是結束，再加一個";" 就多餘了，如果加上;會報錯

mysql>SHOW SLAVE STATUS\G

如果看到Slave_IO_Running和Slave_SQL_Running都是Yes，Seconds_Behind_Master:0，表示主從服務器已經配置成功

Slave_IO_Running是IO的線程狀態，IO線程負責從slave庫到master庫讀取binlog日誌，並寫入到slave的中繼日誌（relay-log），Yes表示IO線程工作正常。

Slave_SQL_Running是SQL的線程狀態，SQL線程負責讀取中繼日誌（relay-log）中的數據並轉換為SQL語句應用到slave庫中，Yes表示SQL線程工作正常。

Seconds_Behind_Master是複製過程中，slave庫比master庫延遲的秒數。

附錄：

1、從服務器遇到錯誤：ERROR 1201 <HY000>: Could not initialize master info structure; more error messages can be found in the mysql error log
錯誤原因是因為從數據庫之前已經做過主從複製了，所以要先停止從庫，再進行從庫設置。
解決方法：
mysql>stop slave;
mysql>reset slave;
mysql>CHANGE MASTER TO
mysql>MASTER_HOST='192.168.0.70',
mysql>MASTER_USER='zhu',
mysql>MASTER_PASSWORD='123456',
mysql>MASTER_PORT=3306,
mysql>MASTER_LOG_FILE='mysql-bin.000008',
mysql>MASTER_LOG_POS=107,
mysql>MASTER_CONNECT_RETRY=10;

2、從服務器Slave_SQL_Running:No
出現問題原因：
可能slave服務器重啟導致
也可能是在slave進行了寫操作
解決辦法一：
mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;
解決辦法二：
查看master服務器File和Position值，需要停止master的寫操作。
mysql>SHOW MASTER STATUS;
在從服務器上停掉slave服務
mysql>slave stop;
然後在執行下麵，注意更改File和Position值
mysql>CHANGE MASTER TO
mysql>MASTER_HOST='192.168.0.70',
mysql>MASTER_USER='zhu',
mysql>MASTER_PASSWORD='123456',
mysql>MASTER_PORT=3306,
mysql>MASTER_LOG_FILE='mysql-bin.000008',
mysql>MASTER_LOG_POS=107,
mysql>MASTER_CONNECT_RETRY=10;
開啟slave服務
mysql>slave start;

3、如果服務器的mysql已經在跑著了，需要先鎖定數據庫防止寫入並導出數據庫。
mysql>FLUSH TABLES WITH READ LOCK; #master鎖定數據庫防止寫入，鎖表後再開一個SSH進行備份操作（當前窗口不要動）。
mysqldump -u root -p123456 --all-databases --lock-tables=false > /root/all.sql #master導出數據庫
mysql -u root -p123456 < /root/all.sql #slave服務器導入數據
mysql>UNLOCK TABLES; #master解鎖表

4、Slave_IO_Running: No遇到了幾次這個問題，最後發現是主服務器防火牆擋住了，關閉主服務器的防火牆（或者自行添加規則），在從服務器上
mysql>slave stop;
mysql>slave start;

5、查看隻讀狀態：show global variables like "%read_only%";

修改隻讀狀態：set global read_only=off;或set global read_only=on;

6、重啟mysql不會影響主從服務，還是盡量避免重啟。

7、binglog日誌自動清理
在my.ini或my.cnf中設置expire_logs_days = 7
表示二進製日誌自動刪除的天數，0則是不自動刪除。
可以通過show variables like '%log%';查看
其中這一行就是自動刪除的天數expire_logs_days | 7

8、master的SHOW MASTER STATUS;沒有返回結果，檢查下master的binlog配置是否正確。
mysql>SHOW VARIABLES LIKE 'log_bin';

9、relicate-wild-ignore-table是複製過濾選項，可以過濾不需要複製的數據庫或表，例如：relicate-wild-ignore-table=mysql.%
replicate-wild-do-table用來指定需要複製的數據庫或表，例如：replicate-wild-do-table=test.%
過濾多個，就多寫一行。

注意：不要在主庫上使用binlog-do-db或binlog-ignore-db選項，也不要在從庫上使用relicate-do-db或relicate-ignore-db選項，因為這樣可能會產生跨庫更新失敗的問題，推薦直接在從庫上使用replicate-wild-do-table和relicate-wild-ignore-table兩個選項來解決複製過濾的問題。

不停止mysql服務配置主從 https://blog.whsir.com/post-606.html

windows 2008 安裝mysql5.5.54 https://blog.whsir.com/post-532.html

linux修改mysql字符集編碼 https://blog.whsir.com/post-487.html

最後更新：2017-06-29 10:02:42