MySQL的複製架構與最佳化

標籤：mysql 主從複製

MySQL的複製架構與最佳化

###########原理###########
1.主伺服器將更新的資料的sql語句（例如，insert，update，delete等）寫入到
  二進位檔案中（由log-bin選項開啟）。此二進位檔案由一個索引檔案跟蹤維護。
  
2.從伺服器串連（使用I/O線程串連）主伺服器，將自己最後一次更新的位置通知
  主伺服器。然後，主伺服器將把從‘從伺服器’得知的位置開始之後的所有更新發
  送給‘從伺服器’（使用Binlog Dump線程來發送），而後‘從伺服器’再次使用I/O
  線程讀取由Binlog Dump線程發送過來的資料，並將資料拷貝到本地的‘中繼二進
  制檔案‘中。最後，再由SQL線程讀取’中繼二進位檔案‘並執行其中的更新。
  
  註：mysql的複製由三個線程來完成，一是，主伺服器上的Binlog Dump線程；二
  是，從伺服器上的I/O線程（用來串連和讀取主服務更新，並拷貝到中繼二進位文
  件）和SQL線程（用來讀取中繼二進位日誌和執行更新）。
  
#######################################
#        主從架構      #
#######################################

#############配置#############
註：此處使用的是 mysql-5.5.28的二進位包。安裝過程略。直接進行主從複製配置
##主伺服器
 1. 更改/etc/my.cnf：
   server-id = 1     #設定伺服器唯一標識
   log-bin=mysql-bin #開啟二進位日誌功能
 2. 添加複製使用者：
  GRANT REPLICATION CLIENT,REPLICATION SLAVE TO ‘repl‘@‘192.168.1.103‘ 
   IDENTIFIED BY ‘123‘;
##從伺服器
 1. 更改/etc/my.cnf:
  server-id = 2     #同主伺服器
  relay-log=relay-bin     #開啟中繼日誌
  relay-log-index=relay-bin.index #開啟跟蹤中繼日誌的索引，若未設定此選
          項系統也會自動產生索引檔案。
 2. 啟動mysql並設定為從伺服器
  1. mysql -uroot -p
  2. CHANGE MASTER TO MASTER_HOST=‘192.168.1.102‘,
       MASTER_USER=‘repl‘,
       MASTER_PASSWORD=‘123‘,
       MASTER_PORT=‘3306‘;
  3. START SLAVE;
  4. SHOW SLAVE STATUS \G; 若Slave_IO_Running:和Slave_SQL_Running: 均顯示
         Yes則說明從伺服器配置成功。
   注: SHOW SLAVE STATUS \G;顯示資訊中的Seconds_Behind_Master: 表示從服務
   器和主伺服器資料相差的時間間隔。
  5. 測試：在主服務上建立表或資料庫，查看是否在從伺服器上有相同的表和資料庫。
   若有，則主從複製搭建成功。
#############安全############
##阻止寫從伺服器
 1.修改/etc/my.cnf
  [mysqld]
  read-only = 1 # 此選項只對普通使用者起作用，對有SUPER許可權的使用者無效。
 2. FLUSH TABLES WITH READ LOCK；#為全域讀鎖命令，此時除了讀操作，其他動作無法執行
##實現半同步
說明：主——>從，為非同步模式。mysql從5.5開始支援半同步模式複製，半同步外掛程式為semisync,儲存
在/usr/local/mysql/plugin下。
 1. 在主伺服器，安裝semisync外掛程式
  CHANGE INSTALL rpl_semi_sync_master SONAME ‘semisync_master.so‘;
    查看是否安裝成功:
  SHOW PLUGINS; #若有rpl_semi_sync_master 則安裝成功。
    啟用半同步功能和設定逾時時間：
  SET GLOBAL rpl_semi_sync_master_enabled=1;
  SET GLOBAL rpl_semi_sync_master_timeout=1000; #單位是ms，如果半同步在此設定的
                時間內無法同步，則自動降回非同步模式。
 註：若使設定永久有效，把以上兩項寫入my.cnf的[mysqld]下即可。
 
 2. 在從伺服器，安裝semisync外掛程式
  CHANGE INSTALL rpl_semi_sync_slave SONAME ‘semisync_slave.so‘;
    查看是否安裝成功:
  SHOW PLUGINS; #若有rpl_semi_sync_slave 則安裝成功。
    啟用半同步功能和設定逾時時間：
  SET GLOBAL rpl_semi_sync_slave_enabled=1;
    重啟slave：
  stop slave;
  start slave;
  
 3. 檢測半同步功能是否已經生效
  SHOW STATUS LIKE ‘rpl_%‘;
    若Rpl_semi_sync_master_clients 的值不為0，則說明半同步功能已經生效。

##如何讓從伺服器的mysql服務在啟動的時候，不自動啟動從服務線程？
說明：從伺服器之所以在啟動的時候會自動啟動線程，是因為master.info和relay-log.info檔案的存在。
   master.info記錄的是CHANGE MASTER TO命令傳遞的參數；relay-log.info記錄的是當前從伺服器所使用的
   中繼日誌的位置和從主伺服器複製的二進位檔案和所處的位置。
   
 1. 在從伺服器上，禁止自動啟動線程
  更改my.cnf，加入以下選項：
  [mysqld]
  skip-slave-start=1
 
##資料庫複寫過濾
 主伺服器：
 1.[mysqld]
   binlog-do-db=test   #只複製test資料庫，相當於白名單。
   binlog-ignore-db=mysql #除了mysql資料庫外不複製外，其他的都要複製，相當於黑名單。
   註：一般這兩項不同時使用，若同時存在，則白名單生效。不過，在主伺服器上做過濾有個缺陷，就是任何
   涉及不到的資料庫，都不會記錄在二進位日誌中。因此，大多情況下不在主伺服器上做過濾。

 從伺服器：
 1.[mysqld]
    replicate-do-db=test1
    replicate-ignore-db=test1
    
    replicate-do-table=test2.t1
    replicate-ignore-table=test2.t2
    
    replicate-wild-do-table=test3.ta%
    replicate-wild-ignore-table=test3.tb%

##防止事務提交和寫入日誌，期間的伺服器崩潰問題
 主伺服器：
 1. [mysqld]
  sync_binlog=1 #每次事件後立即同步到磁碟上的二進位記錄檔中
  innodb_flush_logs_at_trx_commit=1 #

#######################################
#        主主架構      #
####################################### 
說明：主主架構，即伺服器互為主從。配置基本上和主從差不多。此處關鍵的是如果
   資料庫的表中使用了auto_incremnet 關鍵字，則需要設定auto-increment-increment
   和auto-increment-offset兩項以防止索引值衝突。
##主伺服器
 1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO ‘t1‘@‘192.168.1.103‘ 
    IDENTIFIED BY ‘123‘;
 2. [mysqld]
  server-id=10
  log-bin=mysql-bin
  auto-increment-increment=2
  auto-increment-offset=1
 3. mysql -uroot -p
 4. CHANGE MASTER TO MASTER_HOST=‘192.168.1.102‘,
      MASTER_USER=‘t2‘,
      MASTER_PASSWORD=‘123‘,
      MASTER_PORT=‘3306‘;
  
##從伺服器
 1. GRANT REPLICATION CLIENT,REPLICATION SLAVE TO ‘t2‘@‘192.168.1.102‘ 
    IDENTIFIED BY ‘123‘;
 2. [mysqld]
  server-id=10
  log-bin=mysql-bin
  auto-increment-increment=2
  auto-increment-offset=1
 3. mysql -uroot -p
 4. CHANGE MASTER TO MASTER_HOST=‘192.168.1.103‘,
      MASTER_USER=‘t1‘,
      MASTER_PASSWORD=‘123‘,
      MASTER_PORT=‘3306‘;   
  
  
#################MySQL複製架構解決方案###############
 1.主——>從（解決應用程式與耦合度較高的問題）
      1.分三層：
    1.讀寫分離器,產品有：MySQL Proxy和Amoeba
    2.主伺服器
    3.從伺服器
    
    2.分四層：
     1.讀寫分離器
     2.主伺服器
     3.偽從伺服器（所用引擎BLACKHOLE）
     4.從伺服器
    
 2.主——>主(解決更新資料時，資料不一致的情況)
  1.主動/被動模式
   即，將兩個主機server-id設定為相同值。
   產品：mmm，Multi Master Manager
  
#####################故障解決################
##解決：出現錯誤時，不能啟動從伺服器
 1. SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; #此語句可以跳過來自主服務的下一個語句
    START SLAVE;
或 2. 使用pt-slave-restart工具，來自percona-toolkit包。 
 
##解決：資料出現不一致
 1. 檢查一致性使用：
  pt-table-checksum #此工具四種功能：1.校正主從資料
             2.監控複寫延遲時間
             3.系統開銷很小
             4.檢查資料一致性
 2. 修複不一致性使用：
  pt-table-sync
 
 
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
######################MySQL的最佳化#######################
##技巧
 1.使用RegexREGEXP,取出匹配資料
  例：SELECT name,email FROM t WHERE email REGEXP ‘@126[.,]com$‘;
   如果使用like方式查詢
  例:SELECT name,email FROM t WHERE email LIKE ‘%126.com‘ or email LIKE ‘%126,com‘;
 註：使用正則比使用like的一個缺點是系統資源的開銷會更大一下。
 
 2.使用RAND()隨機取出資料
  例：SELECT * FROM t ORDER BY RAND();
   SELECT * FROM t ORDER BY RAND() LIMIT 3;
 
 3.使用GROUP BY的WITH ROLLUP，進一步分組彙總資料。
  例：SELECT cname,pname,COUNT(cname) FROM demo GROUP BY cname,pname WITH ROLLUP;
 註：WITH ROLLUP 不能與ORDER BY 同時使用
 
##最佳化
 一.最佳化SQL語句常用命令
  1.通過SHOW STATUS命令查詢各種SQL的執行頻率。
   SHOW [SESSION|GLOBAL] STATUS;
   其中：SESSION（預設）表示當前串連。
      GLOBAL表示自資料庫啟動至今。
    @@主要查詢以com開頭的參數：
   SHOW STATUS LIEK ‘com_%‘; #Com_XXX表示每個XXX語句執行的次數
    @@需要查看的主要的以com開頭的參數
   com_select:執行select操作的次數，一次查詢只累計加1
   com_update:執行update操作的次數
   com_insert:執行insert操作的次數，對批量插入只算一次
   com_delete:執行delete操作的次數
    註：以上參數是對所有引擎的。
    @@以下是只針對InnoDB儲存引擎的。
   InnoDB_rows_read:執行select操作的次數
   InnoDB_rows_updated:執行update操作的次數
   InnoDB_rows_inserted:執行insert操作的次數
   InnoDB_rows_deleted:執行delete操作的次數
     注:以上針對InnoDB的操作次數是影響的資料的“行”數，而不是相應語句的次數。
    @@其他重要參數
   connections：串連mysql的次數，包括成功和不成功的。
   uptime:伺服器已經工作的秒數。
   slow_queries:慢查詢的次數。#可通過SHOW VARIABLES LIKE ‘%slow_queries%‘;查看是否開啟
  2.定位執行效率較低的SQL語句
   1.explain(或describe) select * from table where id=1000;
 2.最佳化SQL語句
  1.查詢慢查詢日誌
  2.解析查詢語句
  3.判斷是否要加索引和索引是否可使用上 
 
 3.索引最佳化
  1.添加索引，主要是在WHERE，HAVING，GROUP BY，OREDER BY後所使用的欄位上。
  2.使用LIKE時，不要把%萬用字元放在前面，否則索引就無法使用的到。
  3.在使用OR和AND時，前後的兩個條件都要使用索引，否則索引就用不到
  4.如果給定的條件運算式的值的資料類型和定義的不一樣，則無法用到索引
  5.查看索引使用方式：SHOW STATUS LIKE ‘Handler_read%‘;
   其中所顯示的參數：Handler_read_key的值，表示讀取索引的次數。
         Handler_read_rnd_next的值越高則，需要添加索引的列越多。
        
 4.表最佳化
  1.分析和檢查表
   CHECK TABLE t1; #檢查表t1是否有錯誤
  2.最佳化資料表空間
   OPTIMIZE TABLE t1; #最好在非工作時間使用
 5.常用SQL最佳化
  1.匯入匯出最佳化
   @@匯出使用：SELECT * FROM table INTO OUTFILE ‘/tmp/table.txt‘;
   @@匯入使用：LOAD DATA INFILE ‘/tmp/table.txt‘ INTO TABLE table;
  2.關閉索引使匯入速度更快
   [email protected]@關閉索引：ALTER TABLE tbl_name DISABLE KEYS;
     @@匯入資料
     @@開啟索引：ALTER TABLE tbl_name ENABLE KEYS;
    註：以上只對MyISAM表的資料匯入能提高速度，對InnoDB無效
   [email protected]@關閉唯一索引：SET unique_checks=0
     @@匯入資料
     @@恢複唯一索引：SET unique_checks=1
    註：如果能確定資料的唯一性，則可以使用關閉唯一索引來提高速度。否則不建議關閉。
  3.針對InnoDB表類型的資料匯入的最佳化
   1.將匯入的資料按主鍵的順序來排列，可提高匯入速度
   [email protected]@關閉自動認可：SET autocommit=0
     @@匯入資料
     @@恢複自動認可：SET autocommit=1
 6.INSERT語句的最佳化
  1.插入資料時，使用INSERT INTO tbl_name VALUES(‘aa‘),(‘bb‘)......(‘zz‘);
 7.GROUP BY語句的最佳化
  1.禁用分組排序，使用SELECT * FROM tbl_name GROUP BY cloumn ORDER BY NULL;
 8.嵌套最佳化查詢
  1.使用巢狀查詢，內部嵌套的查詢會用到索引，而外層的用不到。
    將巢狀查詢改為，內串連或是外串連，則可最佳化查詢。
 二.資料庫最佳化
  1.使用中間表
   @@建立新表。#不夠靈活
   @@建立視圖。#推薦做法
  2.分區（海量資料的最佳化，在Mysql5.1及以後提供）
   ##MyISAM引擎：
    @@RANGE類型：
    CREATE TABLE t1(id int,name varchar(30))
    -->PARTITION BY RANGE(id)(
    -->PARTITION p0 VALUES LESS THAN (11),
    -->PARTITION p1 VALUES LESS THAN (21)
    -->);
    @@LIST類型：
    CREATE TABLE t1(id int,name varchar(30))
    -->PARTITION BY LIST(id)(
    -->PARTITION p0 VALUES IN(1，3，6，7，10),
    -->PARTITION p1 VALUES IN(2，4，5，8，11)
    -->);
    @@HASH類型：
    CREATE TABLE t1(id int,name varchar(30))
    -->PARTITION BY HASH(id)
    -->PARTITIONS 2;
   ##InnoDB引擎
    @@修改my.cnf
     [mysqld]
     innodb_file_per_table=1 #開啟InnoDB的隔離儲存區 (Isolated Storage)空間
    @@其他的和MyISAM相同
 三. Mysql伺服器最佳化
  ##鎖機制
   1.MyISAM讀鎖定
    @@命令：LOCK TABLE tbl_name READ #所有使用者只能讀，不能更新，刪除等。
   2.MyISAM寫鎖定
    @@命令：LOCK TABLE tbl_name WRITE #只有目前使用者可增刪改查，其他使用者無法進行任何操作。
   3.解鎖：UNLOCK TABLES;
  ##字元集
   [email protected]@使用：STATUS或\s,可查看基本資料和字元集。
    其中，有伺服器字元集、資料庫字元集、用戶端字元集、串連字元集，可設定。
     @@用戶端和串連字元集設定
    [client]
    default-character-set=utf8
     @@伺服器和資料庫字元集設定
    [mysqld]
    character-set-server=utf8
     @@校正字元集
    [mysqld]
    collation-server=utf8_general_ci 
    註：可使用SHOW CHARACTER SET;查看字元集對應的校正字元集。
  ##開啟慢查詢日誌
   [email protected]@使用：SHOW VARIABLES LIKE ‘%slow%‘;查看慢查詢日誌是否開啟
     @@開啟：[mysqld]
       slow_query_log=slow.log
     @@慢查詢時間：[mysqld]
       long_query_time=5
  ##socket問題
   1.如果mysql.sock丟失，則可使用mysql -uroot -p --protocol tcp -h localhost 
    註：只是臨時的啟動解決方案。
 
   2. Mysql 密碼丟失
    @@跳過授權表：mysqld_safe --skip-grant-tables --user=mysql &

本文出自 “一切皆有可能” 部落格，請務必保留此出處http://noican.blog.51cto.com/4081966/1656574

MySQL的複製架構與最佳化