專題《一》mysql優化 ---------主從複製，讀寫

x號開發者發表於2019-03-19
MySql優化

主從複製是mysql自帶的功能，讀寫分離用第三方外掛

 

主從複製作用：讀寫分離（mycat），資料備份，高可用，叢集

　　　　原理：二進位制sql執行檔案-----insert，update，delete，create語句

 

讀寫分離：提高io效能，減少阻塞（寫的操作有連線）

 

步驟

1.準備兩臺伺服器，不同ip地址。   或者用虛擬主機，如果虛擬主機採用克隆方式獲得，修改mysql server uuid。mysql5.7版本，要在my.cnf裡增加sql_model

https://www.cnblogs.com/skymyyang/p/7551646.html

2.

修改主(master)伺服器

vi /etc/my.cnf  新增以下內容

server_id=177  ###伺服器id

log-bin=mysql-bin   ###開啟日誌檔案       依靠此檔案進行兩臺伺服器通訊

主伺服器給從伺服器賬號授權

主伺服器給從伺服器賬號授權

GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456';

//一般不用root帳號，“%”表示所有客戶端都可能連，只要帳號，密碼正確，此處可用具體客戶端IP代替，如192.168.145.226，加強安全。

重啟伺服器

service mysqld restart

登入主伺服器的mysql，查詢master的狀態

show master status;

 

修改從(slave)伺服器

server_id=178

log-bin=mysql-bin

binlog_do_db=test

 

change master to master_host='192.168.110.177',master_user='mysync',master_password='q123456',

         master_log_file='mysql-bin.000002',master_log_pos=343;

啟動同步

start slave

 

檢查從伺服器複製功能狀態

SHOW SLAVE STATUS

 

 

 

 

Slave_IO_Running: Yes    //此狀態必須YES
Slave_SQL_Running: Yes     //此狀態必須YES

 

 注意：在某臺伺服器重啟後，又要重新同步，因為relay_log_pos的數值發生了改變。

 

讀寫分離

好處：負載均衡，不暴露真實ip

       官網下載1.6.5版本

在linux上安裝，修改兩個配置檔案        vim   mycat/conf/server.xml        vim   mycat/conf/schema.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
    - you may not use this file except in compliance with the License. - You 
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
    - - Unless required by applicable law or agreed to in writing, software - 
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
    License for the specific language governing permissions and - limitations 
    under the License. -->
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
   

   <!-- 讀寫都可用的使用者 -->
    <user name="root" defaultAccount="true">
        <property name="password">123456</property>
        <property name="schemas">mycat_testdb</property>

        <!-- 表級 DML 許可權設定 -->
        <!--        
        <privileges check="false">
            <schema name="TESTDB" dml="0110" >
                <table name="tb01" dml="0000"></table>
                <table name="tb02" dml="1111"></table>
            </schema>
        </privileges>       
         -->
    </user>

    <!-- 只讀使用者 -->
    <user name="user">
        <property name="password">user</property>
        <property name="schemas">mycat_testdb</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

 

注意修改  ip      使用者密碼   和自己的匹配

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <!-- TESTDB1 是mycat的邏輯庫名稱，連結需要用的 -->
    <schema name="mycat_testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
        <!-- database 是MySQL資料庫的庫名 -->
    <dataNode name="dn1" dataHost="localhost1" database="test" />
    <!--
    dataNode節點中各屬性說明：
    name：指定邏輯資料節點名稱；
    dataHost：指定邏輯資料節點物理主機節點名稱；
    database：指定物理主機節點上。如果一個節點上有多個庫，可使用表示式db$0-99，     表示指定0-99這100個資料庫；

    dataHost 節點中各屬性說明：
        name：物理主機節點名稱；
        maxCon：指定物理主機服務最大支援1000個連線；
        minCon：指定物理主機服務最小保持10個連線；
        writeType：指定寫入型別；
            0，只在writeHost節點寫入；
            1，在所有節點都寫入。慎重開啟，多節點寫入順序為預設寫入根據配置順序，第一個掛掉切換另一個；
        dbType：指定資料庫型別；
        dbDriver：指定資料庫驅動；
        balance：指定物理主機服務的負載模式。
            0，不開啟讀寫分離機制；
            1，全部的readHost與stand by writeHost參與select語句的負載均衡，簡單的說，當雙主雙從模式(M1->S1，M2->S2，並且M1與 M2互為主備)，正常情況下，M2,S1,S2都參與select語句的負載均衡；
            2，所有的readHost與writeHost都參與select語句的負載均衡，也就是說，當系統的寫操作壓力不大的情況下，所有主機都可以承擔負載均衡；
-->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- 可以配置多個主從 -->
        <writeHost host="hostM1" url="192.168.52.145:3306" user="root" password="root">
            <!-- 可以配置多個從庫 -->
            <readHost host="hostS2" url="192.168.52.146:3306" user="root" password="root" />
        </writeHost>
    </dataHost>
</mycat:schema>

在bin裡   啟動      ./mycat   start                                ------------（停止是    ./mycat  stop）

 

接下來可以用nivacat  進行連線驗證

mycat埠號為   8066

 

相關文章