歡迎光臨
每天分享高質量文章
當前位置：知識星球 > 後端 > 正文

mysql+mycat搭建穩定高可用叢集，負載均衡，主備複製，讀寫分離

2018-09-12 分類：後端 閱讀(322) 評論(0)

作者：北島知寒

連結：https://www.cnblogs.com/crazyacking/p/5967246.html

資料庫效能最佳化普遍採用叢集方式，oracle叢集軟硬體投入昂貴，今天花了一天時間搭建基於mysql的叢集環境。

主要思路

簡單說，實現mysql主備複製–>利用mycat實現負載均衡。

比較了常用的讀寫分離方式，推薦mycat，社群活躍，效能穩定。

測試環境

MYSQL版本：Server version: 5.5.53，到官網可以下載WINDWOS安裝包。

註意：確保mysql版本為5.5以後，以前版本主備同步配置方式不同。

linux實現思路類似，修改my.cnf即可。

A主mysql。

192.168.110.1:3306, 使用者root，密碼root。作業系統：win7 x64，記憶體：4g

安裝路徑：

C:Program FilesMySQLMySQL Server 5.5in

B備mysql。

192.168.110.2:3306, 使用者root，密碼root。作業系統：win2003 x64，記憶體：1g

安裝路徑：

C:Program FilesMySQLMySQL Server 5.5in

A主、B備的mysql中建立sync_test資料庫

實現mysql主備複製

主要思路：A主mysql開啟日誌，B備mysql讀取操作日誌，同步執行。

一般為主備同步，主主同步不推薦使用。

配置A主mysql

1）修改my.ini。需要在log-bin=”C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log”的相關位置建立log目錄，以及mysql-bin.log檔案。

[mysqld]
server-id=1 #主機標示，整數
port=3306    
log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log"  #確保此檔案可寫
read-only=0   #主機，讀寫都可以
binlog-do-db=sync_test   #需要備份資料庫，多個寫多行
binlog-ignore-db=mysql #不需要備份的資料庫，多個寫多行

2）允許MYSQL遠端訪問

#登入mysql console
進入%home%/bin，執行mysql -uroot -proot
#授權。允許root使用者，從192.168.110.*的IP範圍 遠端訪問A主mysql
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.110.*' IDENTIFIED BY 'root' WITH GRANT OPTION;
#生效。該操作很重要！
mysql>FLUSH PRIVILEGES;

3）重啟A主mysql資料庫

進入%home%/bin，執行mysql -uroot -proot
mysql>net stop mysql;
mysql>net start mysql;

4）檢視主mysql日誌狀態

mysql> show master statusG;
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 107
    Binlog_Do_DB: sync_test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR:
No query specified

配置B備mysql

1）修改my.ini。需要在log-bin=”C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log”的相關位置建立log目錄，以及mysql-bin.log檔案。

[mysqld]
# add for sycn test
server-id=2                       #從機標識
log-bin="C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-bin.log" #確保此檔案可寫
#master-host="192.168.110.1" #主機Ip
#master-user=root                  #資料庫訪問使用者名稱
#master-pass=root                     #資料庫訪問密碼
#master-port=3306          #主機埠
#master-connect-retry=60     #如果從伺服器發現主伺服器斷掉，重新連線的時間差(秒)
replicate-do-db=sync_test     #只複製某個庫
replicate-ignore-db=mysql     #不複製某個庫

2）重啟B備mysql資料庫

進入%home%/bin，執行mysql -uroot -proot
mysql>net stop mysql;
mysql>net start mysql;

3）配置B備資料庫的資料來源，核實高亮處的狀態是否正常。

mysql>change master to master_host='192.168.110.1',master_port='3306',master_user='root',master_password='root';
mysql>slave start;
mysql>show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send even
                  Master_Host: 192.168.110.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 107
               Relay_Log_File: wjt-1c698d8a032-relay-bin.00001
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: sync_test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 565
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

驗證同步配置結果

  • A主mysql：使用navicat工具，在sync_test庫中建立sync_table表，並新增一些資料

  • B備mysql：使用navicat工具，檢視sync_test庫，可以看到sync_table表和資料已被同步

實現讀寫分離

主要思路：使用mycat中介軟體，轉發sql指令到後端mysql節點。mycat不負責資料庫同步。

安裝mycat

mycat是什麼？可以認為它是一個資料庫訪問中介軟體，但更像f5、ngnix等產品，具備訪問路由、多表分表分片操作等功能。總之很強大。

下載：http://www.mycat.io/ ，本文使用的是：1.6-RELEASE

解壓Mycat-server-1.6-RELEASE-20161012170031-win.tar，到D:dev-binmycat目錄

確保java環境為jdk1.7以上，否則mycat將不支援

安裝完畢

配置mycat

1）server.xml。配置訪問使用者及許可權。修改高亮處資訊，其中admin、user為訪問mycat的使用者，TESTDB為mycat虛擬的資料庫，供上層應用訪問。

<user name="admin">
        <property name="password">adminproperty>


        <property name=“schemas”>TESTDBproperty>
        
        
    user>
    <user name=“user”>
        <property name=“password”>userproperty>
        <property name=“schemas”>TESTDBproperty>
        <property name=“readOnly”>trueproperty>
    user>

2）schema.xml。這部分不太好理解，精簡了一下，主要分schema、dataNode、dataHost三個主要配置。

節點定義了mycat的虛擬資料庫為TESTDB， balance=”1″：write操作路由到A機，讀操作路由到B。


mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        
    schema>


    <dataNode name=“dn1” dataHost=“localhost1” database=“sync_test” />
    <dataHost name=“localhost1” maxCon=“1000” minCon=“10” balance=“1”
              writeType=“0” dbType=“mysql” dbDriver=“native” switchType=“1”  slaveThreshold=“100”>
        <heartbeat>select user()heartbeat>
        
        <writeHost host=“hostM1” url=“192.168.110.1:3306” user=“root”  password=“root”>
            
            <readHost host=“hostS2” url=“192.168.110.2:3306” user=“root” password=“root” />
        writeHost>
    dataHost>
mycat:schema>

啟動mycat

1）啟動mycat

D:dev-binmycatbin>startup_nowrap.bat

後臺資訊如下：

D:dev-binmycatin>startup_nowrap.bat
D:dev-binmycatin>REM check JAVA_HOME & java
D:dev-binmycatin>set "JAVA_CMD=C:Program Files (x86)Javajdk1.7.0_13/bin/java"
D:dev-binmycatin>if "C:Program Files (x86)Javajdk1.7.0_13" == "" goto noJavaHome
D:dev-binmycatin>if exist "C:Program Files (x86)Javajdk1.7.0_13injava.exe" goto mainEntry
D:dev-binmycatin>REM set HOME_DIR
D:dev-binmycatin>set "CURR_DIR=D:dev-binmycatin"
D:dev-binmycatin>cd ..
D:dev-binmycat>set "MYCAT_HOME=D:dev-binmycat"
D:dev-binmycat>cd D:dev-binmycatin
#如果啟動失敗，請修改D:dev-binmycatinstartup_nowrap.bat檔案中的以下引數。預設佔用記憶體為2G
D:dev-binmycatin>"C:Program Files (x86)Javajdk1.7.0_13/bin/java" -server -Xms512m -Xmx512m -XX:MaxPermSize=64M  -XX:+AggressiveOpts -XX:MaxDirectMemorySize=768m -DMYCAT_HOME=D:
p "..conf;..lib*" io.mycat.MycatStartup
MyCAT Server startup successfully. see logs in logs/mycat.log   #啟動成功將看到如下資訊。

註意：如日誌中出現192.168.110.2 not connected 等資訊，請允許B備mysql遠端訪問。

#登入mysql console
進入%home%/bin，執行mysql -uroot -proot
#授權。允許root使用者，從192.168.110.*的IP範圍 遠端訪問Bmysql
mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.110.*' IDENTIFIED BY 'root' WITH GRANT OPTION;
#生效,該操作很重要！
mysql>FLUSH PRIVILEGES;

測試讀寫分離

驗證是否同步

使用navicat連線mycat，操作方式和連線物理mysql庫一致，使用者admin，密碼admin，埠8066

在TESTDB虛擬庫中，建立新表test2，增加一些資料

檢視A節點、B節點資料已同步

關閉B備mysql的同步，驗證讀寫分離

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.110.1
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 478
               Relay_Log_File: wjt-1c698d8a032-relay-bin.00001
                Relay_Log_Pos: 624
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: sync_test
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 478
              Relay_Log_Space: 936
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

3）使用navicat連線mycat，操作方式和連線物理mysql庫一致，使用者admin，密碼admin，埠8066

連線成功後，將看到TESTDB資料庫和test資料表

在test表中新增一些資料，儲存

執行select * from test檢視test操作，將看到資料未更新

原因：mycat將查詢sq路由到B，因此讀取的結果集不一致。

最後，mycat使用可以參見官網的權威指南學習。雙主雙備架構待後續更新。

●編號407，輸入編號直達本文

●輸入m獲取文章目錄

推薦↓↓↓

 

Web開發

更多推薦《18個技術類微信公眾號》

涵蓋：程式人生、演演算法與資料結構、駭客技術與網路安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

贊(0)
標籤：

相關推薦

熱門標籤

iOS (11238)微軟 (4955)Linux (4274)安全 (4180)Python (4161)效能 (3165)運維 (2774)最佳化 (2419).NET (2262)Google (2136)機器學習 (1795)併發 (1613)分散式 (1559)叢集 (1240)SQL (1174)Mysql (1060)區塊鏈 (1017)Docker (977)微服務 (922)面試 (919)Apache (743)NLP (725)Redis (719)Android (668)Git (640)架構師 (632)Nginx (630)Facebook (599)JVM (595)爬蟲 (476)

熱門文章

分享創造快樂