基于mycat的mysql主從讀寫(xiě)分離配置詳解與示例_正能量_20150721

1、基于 Mycat 開(kāi)源數(shù)據(jù)庫(kù)中間件MySQL 數(shù)據(jù)庫(kù)主從讀寫(xiě)分離配置詳解與示例參考文檔：Mycat 權(quán)威指南王金劍本文說(shuō)明：根據(jù)心跳問(wèn)題 heartbeat bug #393，做了修正，請(qǐng)使用附件中的 Mycat 安裝包：Mycat-Server-1.4-RC-Linux-RW-20150721.tar.gz或者將附件中的 Mycat-server-1.4-RC.jar 更新到 Mycat 安裝目錄下的 lib 目錄，替換原文件一、MySQL 主服務(wù)器 Master 配置1、修改主服務(wù)器配置:#vi /etc/fbinlog-do-db=db1binlog-do-db=db2 bin

2、log-do-db=db3binlog-ignore-db = mysqllog-bin=mysql-bin#啟用二進(jìn)制日志server-id=158#服務(wù)器唯一 ID，一般取 IP 最后一段2、重啟 MySQL/etc/init.d/mysql restart3、建立帳戶并授權(quán) slave:#/usr/local/mysql/bin/mysql -uroot -p123456mysqlGRANT FILE ON *.* TO backup% IDENTIFIED BY 123456;mysqlGRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*

3、 to backup% identified by 123456;#一般不用 root 帳號(hào)，“%”表示所有客戶端都可能連，只要帳號(hào)，具體客戶端 IP 代替，如 26，加強(qiáng)安全。正確，此處可用刷新權(quán)限mysql FLUSH PRIVILEGES;查看 mysql 現(xiàn)在有哪些用戶mysqlselect user,host from mysql.user;4、登錄主服務(wù)器的 mysql，查詢 master 的狀態(tài)mysql show master status;+ + + + + | File | Position | Binlog_Do_DB | Binlog_Igno

4、re_DB | + + + + + | mysql-bin.000010 | 106 | db1,db2,db3 | mysql | + + + + + Master 重啟后會(huì)修改 mysql-bin（序號(hào)加 1） 二、MySQL 從服務(wù)器 Slave配置1、修改從服務(wù)器配置:#vi /etc/freplicate-do-db=db1replicate-do-db=db2 replicate-do-db=db3replicate-ignore-db=mysqlmaster-connect-retry=60server-id=78master-host=58 mas

5、ter-user=backupmaster-password=123456 master-port=3306master-connect-retry=60 #log-slave-updatesskip-slave-start#防止復(fù)制隨著 mysql 啟動(dòng)而自動(dòng)啟動(dòng)。即 slave 端的 mysql 服務(wù)重啟后需手動(dòng)來(lái)啟動(dòng)主從復(fù)制（slave start），最好加上，slave 端數(shù)據(jù)庫(kù)服務(wù)重啟后手動(dòng)啟動(dòng) slave比較安全2、重啟 MySQL/etc/init.d/mysql restart3、登錄 mysql 并停止 slave 服務(wù)# cd /usr/local/mysql/bin/#

6、./mysql -uroot p123456 或# /usr/local/mysql/bin/mysql -uroot p123456mysqlslave stop;設(shè)置與 master 服務(wù)器相關(guān)的配置參數(shù)mysqlchangemastertomaster_host=58,master_user=backup,master_password=123456,MASTER_LOG_FILE=mysql-bin.000015,MASTER_LOG_POS=106;注意：Master 重啟后 slave 要修改 MASTER_LOG_FILE，106 無(wú)單引號(hào)。啟動(dòng)從服務(wù)器復(fù)制功

7、能Mysqlstart slave;ERROR 1201 (HY000):Could not initialize master info structure 的問(wèn)題解決方案是：運(yùn)行命令 stop slave;成功執(zhí)行后繼續(xù)運(yùn)行 reset slave;4、檢查從服務(wù)器復(fù)制功能狀態(tài)mysql show slave statusG以下兩個(gè)參數(shù)必須為 YES： Slave_IO_Running: YesSlave_SQL_Running: Yes返回如下：* 1. row * Slave_IO_State: Waiting for master to send eventMaster_Host:

8、58 Master_User: backupMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 106Relay_Log_File: bogon-relay-bin.000002 Relay_Log_Pos: 251Relay_Master_Log_File: mysql-bin.000010Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: db1,db2,db3 Replicat

9、e_Ignore_DB: mysqlReplicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0 Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 106Relay_Log_Space: 406 Until_Condition: None Until_Log_File:Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File:Maste

10、r_SSL_CA_Path: Master_SSL_Cert:Master_SSL_Cipher: Master_SSL_Key:Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:1 row in set (0.00 sec)三、數(shù)據(jù)庫(kù)中間件 Mycat 配置1、不使用 Mycat 托管 MySQL 主從服務(wù)器，簡(jiǎn)單使用如下配置dataNodedataNodedataNode/name=localho

11、st1 maxCon=1000 minCon=10 balance=0writeType=0 dbType=mysql dbDriver=native switchType=1 slaveThreshold=100select user()2、Mycat 1.4 支持 MySQL 主從復(fù)制狀態(tài)綁定的讀寫(xiě)分離機(jī)制，讓讀更加安全可靠，配置如下：dataNodedataNodedataNode/name=localhost1 maxCon=1000 minCon=10 balance=1writeType=0 dbType=mysql dbDriver=native switchType=2 sla

12、veThreshold=100show slave status(1) 設(shè)置 balance=1與 writeType=0Balance 參數(shù)設(shè)置：1. balance=“0”, 所有讀操作都發(fā)送到當(dāng)前可用的 writeHost 上。2. balance=“1”，所有讀操作都隨機(jī)的發(fā)送到 readHost。3. balance=“2”，所有讀操作都隨機(jī)的在 writeHost、readhost 上分發(fā)WriteType 參數(shù)設(shè)置：1. writeType=“0”, 所有寫(xiě)操作都發(fā)送到可用的 writeHost 上。2. writeType=“1”，所有寫(xiě)操作都隨機(jī)的發(fā)送到 readHost。3

13、. writeType=“2”，所有寫(xiě)操作都隨機(jī)的在 writeHost、readhost 分上發(fā)?！皉eadHost 是從屬于 writeHost 的，即意味著它從那個(gè) writeHost 獲取同步數(shù)據(jù)，因此，當(dāng)它所屬的 writeHost 宕機(jī)了，則它也不會(huì)再參與到讀寫(xiě)分離中來(lái)，即“不工作了”，這是因?yàn)榇藭r(shí)，它的數(shù)據(jù)已經(jīng)“不可靠”了?；谶@個(gè)考慮，目前 mycat 1.3 和1.4 版本中，若想支持 MySQL 一主一從的標(biāo)準(zhǔn)配置，并且在主節(jié)點(diǎn)宕機(jī)的情況下，從節(jié)點(diǎn)還能讀取數(shù)據(jù)，則需要在 Mycat 里配置為兩個(gè) writeHost 并設(shè)置 banlance=1?！?2) 設(shè) 置 switc

14、hType=2 與 slaveThreshold=100“Mycat 心跳檢查語(yǔ)句配置為 show slave status ，dataHost 上定義兩個(gè)新屬性：switchType=2 與 slaveThreshold=100，此時(shí)意味著開(kāi)啟 MySQL 主從復(fù)制狀態(tài)綁定的讀寫(xiě)分離與切換機(jī)制。Mycat 心跳機(jī)制通過(guò)檢測(cè) show slave status 中的Seconds_Behind_Master, Slave_IO_Running, Slave_SQL_Running三個(gè)字段來(lái)確定當(dāng)前主從同步的狀態(tài)以及 Seconds_Behind_Master 主從復(fù)制時(shí)延?！八?、主從測(cè)試，執(zhí)行

15、以下命令mysql explain create table company(id int not null primary key,name varchar(100); + + + | DATA_NODE | SQL | + + + | dn1 | create table company(id int not null primary key,name varchar(100) | | dn2 | create table company(id int not null primary key,name varchar(100) | | dn3 | create table company(id int not null primary key,name varchar(100) | + + + 3 rows in set (0.32 sec) mysql create table company(id int not null primary key,name varchar(100); Query OK, 0 rows affected (0.26 sec) mysql insert into com