mysql优化 个人笔记 (mysql 主从复制 ) 非礼勿扰 -m11
生活随笔
收集整理的這篇文章主要介紹了
mysql优化 个人笔记 (mysql 主从复制 ) 非礼勿扰 -m11
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
1、為什么需要主從復制
- 在業務中 有一條sql需要鎖表 就會導致其他讀業務不能執行 ，那么就很影響業務，使用主從復制，讓主庫寫，從庫讀。這樣即使主庫出現鎖表情況，從庫也可以正常使用，保證業務正常進行
- 做數據熱備
- 架構的擴展 ，業務量越來越大，I/O訪問頻率過高，此時做多庫的存儲，降低磁盤I/O訪問的頻率，提高單個機器的I/O性能
2、什么是主從復制
Mysql主從復制是指數據可以從一個Mysql數據庫服務器主節點中復制到另一個或多個從節點中，Mysql默認采用異步復制的方式，這樣從節點不用一直訪問主服務器來更新自己的數據，數據更新可以在遠程連接上操作，從節點可以復制主數據庫的所有數據庫或者特定的數據庫，或者特定的表
3、Mysql復制原理
(1) 主節點將數據改變放入binlog日志
(2) 從節點會定時去檢測binlog是否改變，如果發生改變，則開始一個I/O Thread 請求主節點二進制事件
(3) 同時主節點每個I/O線程啟動一個dump線程，用于向其發送二進制事件，并保存至從節點本地的中繼日志中，從節點將啟動SQL線程從中繼日志中讀取二進制事件，在本地重放，使得其數據與主節點保持一致，最后I/O Thread 和SQLThread 將進入休眠狀態 等待下一次喚醒
也就是說：
- 從庫會生成兩個線程 一個 I/O Thread 、一個SQL Thread
- I/O Thead 會去請求主節點的binlog 并將得到的binlog寫到本地的replay-log(中繼日志)中
- 主節點會生成一個log dump 線程，用來給從節點的I/O 線程傳遞binlog
- SQL線程會讀取replay-log 文件中的日志，并解析成sql 語句 主句執行
注意：
其中I/O線程負責讀取主節點的binlog內容到replay-log中
Sql線程負責從replay-log中讀取內容 并更新到從節點中
具體步驟：
4、Mysql主從復制形式
一主一從
主主復制
一主多從
多主一從
聯級復制
5、主從復制安裝配置
如果 Slave_IO_Running Slave_SQL_Running 不是yes可以考慮是不是防火墻沒有關閉
我操作關閉了主節點和從節點的防火墻 service iptables stop
7、此時可以在主服務器進行相關的數據添加刪除工作，在從服務器看相關的狀態
5、Mysql主從同步延時分析
- mysql的主從復制都是單線程操作，主庫對所有的DDL和DML產生的日志寫進binlog,
- 由于binlog是順序寫的，所以效率很高
- slave的sql thread 將主庫的DDL DML 操作事件在slave中重放，DML和DDL 的IO 操作是隨機的，不是順序的，所以成本高
- 由于sql thread 也是單線程的，當主數據庫并發較高時，產生的DML 數據量超過slave的SQL thread 所處理的的速度，或者當slave中有大型的query語句產生了等待，那么延時就產生了
解決方案：
mysql5.7之前
mysql5.7之后 不存在了 MTS enhanced multi-threaded slave 號稱解決了延時問題
具體技術沒有看
6、讀寫分離
讀寫分離介紹：
Mysql讀寫分離基本原理是讓master數據庫處理寫操作，slave數據庫處理讀操作
master將寫操作變更到各個slave節點
Mysql讀寫分離提高性能的原因在于 ：
(1) 物理服務器增加 機器處理能力提升，拿硬件換性能
(2) 主從只負責各自的讀和寫 極大程度緩解了共享鎖和排它鎖爭用
(3)slave可以配置myisam引擎 提升查詢性能以及節約系統開銷
(4)master直接寫是并發的，slave通過主庫發送過來的binlog恢復數據是異步的
(5)slave可以單獨設置一些屬性來提升性能
(6)增加冗余 提高可用性
- Mysql Proxy
使用比較少
5.創建文件 并添加內容 mysql-proxy.conf
cd /opt/mysql-proxy/conf vim mysql-proxy.conf --添加 [mysql-proxy] user=root proxy-address=192.168.2.127:4040 proxy-backend-addresses=192.168.2.125:3306 proxy-read-only-backend-addresses=192.168.2.126:3306 proxy-lua-script=/opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua log-file=/opt/mysql-proxy/logs/mysql-proxy.log log-level=debug daemon=true給mysql-proxy.conf 660權限
chmod 660 mysql-proxy.conf6.開啟mysql-proxy
mysql-proxy --defaults-file=/opt/mysql-proxy/conf/mysql-proxy.conf報錯：
/opt/mysql-proxy/bin/mysql-proxy: /opt/mysql-proxy/libexec/mysql-proxy: /lib/ld-linux.so.2: bad ELF interpreter: 沒有那個文件或目錄解決：
yum install glibc.i686 -y mysql-proxy --defaults-file=/opt/mysql-proxy/conf/mysql-proxy.confcd /opt/mysql-proxy/logstail -f -n100 mysql-proxy.log
這個時候可以連接
host:192.168.2.127
port:4040
user:root
password:123456
- amoeba
什么是amoeba
? Amoeba(變形蟲)項目，專注 分布式數據庫 proxy 開發。座落與Client、DB Server(s)之間。對客戶端透明。具有負載均衡、高可用性、sql過濾、讀寫分離、可路由相關的query到目標數據庫、可并發請求多臺數據庫合并結果。
主要解決：
? 降低 數據切分帶來的復雜多數據庫結構
? 提供切分規則并降低 數據切分規則 給應用帶來的影響
? 降低db 與客戶端的連接數
? 讀寫分離
為什么要用Amoeba
目前要實現mysql的主從讀寫分離，主要有以下幾種方案：
1、 通過程序實現，網上很多現成的代碼，比較復雜，如果添加從服務器要更改多臺服務器的代碼。
2、 通過mysql-proxy來實現，由于mysql-proxy的主從讀寫分離是通過lua腳本來實現，目前lua的腳本的開發跟不上節奏，而寫沒有完美的現成的腳本，因此導致用于生產環境的話風險比較大，據網上很多人說mysql-proxy的性能不高。
3、 自己開發接口實現，這種方案門檻高，開發成本高，不是一般的小公司能承擔得起。
4、 利用阿里巴巴的開源項目Amoeba來實現，具有負載均衡、高可用性、sql過濾、讀寫分離、可路由相關的query到目標數據庫，并且安裝配置非常簡單。
3.amoeba安裝
3、配置amoeba的配置文件
cd /opt/amoeba-mysql-3.0.5-RC/conf vim dbServers.xml 刪除所有內容(d+G(大寫G)) 復制下邊內容到文件中dbServers.xml
<?xml version="1.0" encoding="gbk"?><!DOCTYPE amoeba:dbServers SYSTEM "dbserver.dtd"> <amoeba:dbServers xmlns:amoeba="http://amoeba.meidusa.com/"><!--abstractServer 名字隨便起 下邊會用 --><dbServer name="abstractServer" abstractive="true"><factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory"><property name="connectionManager">${defaultManager}</property><property name="sendBufferSize">64</property><property name="receiveBufferSize">128</property><!-- mysql port --><property name="port">3306</property><!-- mysql schema --><property name="schema">hhei</property><!-- mysql user --><property name="user">root</property><property name="password">123456</property></factoryConfig><poolConfig class="com.meidusa.toolkit.common.poolable.PoolableObjectPool"><property name="maxActive">500</property><property name="maxIdle">500</property><property name="minIdle">1</property><property name="minEvictableIdleTimeMillis">600000</property><property name="timeBetweenEvictionRunsMillis">600000</property><property name="testOnBorrow">true</property><property name="testOnReturn">true</property><property name="testWhileIdle">true</property></poolConfig></dbServer><!--parent 會繼承abstractServer的所有屬性 writedb也是隨便寫的名字 后邊配置文件會用 --><dbServer name="writedb" parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.2.125</property></factoryConfig></dbServer><!--parent 會繼承abstractServer的所有屬性 slave也是隨便寫的名字 后邊配置文件會用 --><dbServer name="slave" parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.2.126</property></factoryConfig></dbServer><!-- 配置節點屬性 這個 myslave會在下邊配置成讀節點 --><dbServer name="myslave" virtual="true"><poolConfig class="com.meidusa.amoeba.server.MultipleServerPool"><!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA--><property name="loadbalance">1</property><!----><property name="poolNames">slave</property></poolConfig></dbServer> </amoeba:dbServers>amoeba.xml
cd /opt/amoeba-mysql-3.0.5-RC/conf vim amoeba.xml 刪除所有內容(d+G(大寫G)) 復制下邊內容到文件中 <?xml version="1.0" encoding="gbk"?><!DOCTYPE amoeba:configuration SYSTEM "amoeba.dtd"> <amoeba:configuration xmlns:amoeba="http://amoeba.meidusa.com/"><proxy><service name="Amoeba for Mysql" class="com.meidusa.amoeba.mysql.server.MySQLService"><!-- port --><property name="port">8066</property><property name="connectionFactory"><bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory"><property name="sendBufferSize">128</property><property name="receiveBufferSize">64</property></bean></property><property name="authenticateProvider"><bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator"><property name="user">root</property><property name="password">123456</property><property name="filter"><bean class="com.meidusa.toolkit.net.authenticate.server.IPAccessController"><property name="ipFile">${amoeba.home}/conf/access_list.conf</property></bean></property></bean></property></service><runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext"><!-- proxy server client process thread size --><property name="executeThreadSize">128</property><!-- per connection cache prepared statement size --><property name="statementCacheSize">500</property><!-- default charset --><property name="serverCharset">utf8</property><!-- query timeout( default: 60 second , TimeUnit:second) --><property name="queryTimeout">60</property></runtime></proxy><!-- Each ConnectionManager will start as threadmanager responsible for the Connection IO read , Death Detection--><connectionManagerList><connectionManager name="defaultManager" class="com.meidusa.toolkit.net.MultiConnectionManagerWrapper"><property name="subManagerClassName">com.meidusa.toolkit.net.AuthingableConnectionManager</property></connectionManager></connectionManagerList><!-- default using file loader --><dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader"><property name="configFile">${amoeba.home}/conf/dbServers.xml</property></dbServerLoader><queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter"><property name="ruleLoader"><bean class="com.meidusa.amoeba.route.TableRuleFileLoader"><property name="ruleFile">${amoeba.home}/conf/rule.xml</property><property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property></bean></property><property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property><property name="LRUMapSize">1500</property><property name="defaultPool">writedb</property><!--寫節點--><property name="writePool">writedb</property><!--讀節點--><property name="readPool">myslave</property><property name="needParse">true</property></queryRouter> </amoeba:configuration>啟動
cd /opt/amoeba-mysql-3.0.5-RC/bin ./launcher 報錯： Java HotSpot(TM) 64-Bit Server VM warning: ignoring option PermSize=16m; support was removed in 8.0 Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=96m; support was removed in 8.0The stack size specified is too small, Specify at least 228k （至少228）解決： cd /opt/amoeba-mysql-3.0.5-RC vim jvm.properties 修改-Xss265k JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss265k -XX:PermSize=16m -XX:MaxPermSize=96m"安裝成功 測試
連接： mysql -h192.168.2.127 -uroot -p123456 -P8066 開始為所欲為： 在這里 寫會走master 讀會走slave 測試方式： 查詢 數據來自slave(可以提前在slave庫上新增幾條與master不一樣數據) 新增數據是走的master(關閉master就不能新增了)這個是真正的 master寫 slave讀Mycat
Mycat數據庫分庫分表中間件
活躍的、性能好的開源數據庫中間件！
http://www.mycat.org.cn/
后續再寫Mycat使用方式
總結
以上是生活随笔為你收集整理的mysql优化 个人笔记 (mysql 主从复制 ) 非礼勿扰 -m11的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： vivado笔记
- 下一篇： html5 设计 form,为HTML5