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

1、基于 Mycat 開源數(shù)據(jù)庫中間件MySQL 數(shù)據(jù)庫主從讀寫分離配置詳解與示例參考文檔：Mycat 權(quán)威指南王金劍本文說明：根據(jù)心跳問題 heartbeat bug #393，做了修正，請使用附件中的 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#啟用二進制日志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 帳號，“%”表示所有客戶端都可能連，只要帳號，具體客戶端 IP 代替，如 26，加強安全。正確，此處可用刷新權(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 重啟后會修改 mysql-bin（序號加 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 啟動而自動啟動。即 slave 端的 mysql 服務(wù)重啟后需手動來啟動主從復(fù)制（slave start），最好加上，slave 端數(shù)據(jù)庫服務(wù)重啟后手動啟動 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ù)器復(fù)制功

7、能Mysqlstart slave;ERROR 1201 (HY000):Could not initialize master info structure 的問題解決方案是：運行命令 stop slave;成功執(zhí)行后繼續(xù)運行 reset slave;4、檢查從服務(wù)器復(fù)制功能狀態(tài)mysql show slave statusG以下兩個參數(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ù)庫中間件 Mycat 配置1、不使用 Mycat 托管 MySQL 主從服務(wù)器，簡單使用如下配置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)綁定的讀寫分離機制，讓讀更加安全可靠，配置如下：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”，所有讀操作都隨機的發(fā)送到 readHost。3. balance=“2”，所有讀操作都隨機的在 writeHost、readhost 上分發(fā)WriteType 參數(shù)設(shè)置：1. writeType=“0”, 所有寫操作都發(fā)送到可用的 writeHost 上。2. writeType=“1”，所有寫操作都隨機的發(fā)送到 readHost。3

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

14、hType=2 與 slaveThreshold=100“Mycat 心跳檢查語句配置為 show slave status ，dataHost 上定義兩個新屬性：switchType=2 與 slaveThreshold=100，此時意味著開啟 MySQL 主從復(fù)制狀態(tài)綁定的讀寫分離與切換機制。Mycat 心跳機制通過檢測 show slave status 中的Seconds_Behind_Master, Slave_IO_Running, Slave_SQL_Running三個字段來確定當(dāng)前主從同步的狀態(tài)以及 Seconds_Behind_Master 主從復(fù)制時延?！八?、主從測試，執(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