MySQL 主從複製 詳細講解（一），mysql主從複製講解

MySQL 主從複製 詳細講解（一），mysql主從複製講解

<span style="font-size:18px;">mysql複製的優點主要包括以下3個方面：（一般把"增刪改"交給主要資料庫，"查詢"交給從資料庫 看需要而言）1：如果主伺服器出現問題，可以快速切換到從伺服器提供的服務2：可以在從伺服器上執行查詢操作，降低主伺服器的訪問壓力3：可以在從伺服器上執行備份，以避免備份期間影響主伺服器的服務--注意：一般只有更新不頻繁的資料或者對即時性要求高的資料可以通過從伺服器查詢，即時性高得資料仍能需要從主要資料庫獲得一：主伺服器的配置步驟：1:登入mysql資料庫  mysql -uroot -p1234562:給從伺服器設定授權使用者grant all on *.* to user1@192.168.10.2 identified by "pass123";   （all表示所有許可權的意思，授權所有的使用者，關於資料庫所有的表(只能是這台伺服器192.168.10.2，如果主機是字串單引號擴著'leyangjunpc')：帳號是user1  密碼是pass123）</span><span style="font-size:18px;">*  GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'192.168.0.%' IDENTIFIED BY 'p4ssword';</span><span style="font-size:18px;">（replication主從伺服器複製代碼的時候用的一個許可權）3：修改主要資料庫伺服器的設定檔my.cnf/ini，開啟bin-log日誌，並設定server-id的值log-bin=mysql-binserver-id=1     （主從server-id一定要保證唯一，不能相同） --一主多從也一樣4:在主伺服器上設定讀讀鎖定有效，確保沒有資料庫操作，以便獲得一個一致的快照（選做，建議這麼做,這樣好就行備份）flush tables with read lock;5：查看主伺服器上當前的二進位日誌名和位移量值show master status;6:目前主要資料庫伺服器已經停止更新操作，產生主要資料庫的備份，備份的方式兩種：一：cp全部的資料二：mysqldump備份資料方法（建議使用）如果主要資料庫的服務可以停止，那麼直接cp資料檔案應該是最快的產生快照方法（就是cp資料檔案去從資料庫執行 tar -cvf data.tar data(整個拷貝)）7：主要資料庫備份完畢後，主要資料庫可以恢複寫操作，剩下的操作只需要在從伺服器上去執行：unlock tables;8: 把主要資料庫的一致性備份恢複到從資料庫上，把以上的壓縮包解壓後放到對應的目錄即可二：從伺服器的配置步驟：->情境（先把資料庫複寫過來，能後玩同步--主要資料改動立馬同步過來）  主：mysql/bin/mysqldump -uroot -p test -l -F > "/tmp/test.sql";  主-備份檔案主：能後將本分檔案複製到從資料庫：linux命令:scp /tmp/test.sql 192.168.10.2:/tmp/   將主/tmp/test.sql  複製到從資料庫上去--隧道傳輸從：Reset master；  清下日誌從：將剛才複製過來的資料恢複資料test.sqlmysql/bin/mysql -uroot -p123 test </tmp/test.sql檢查看是否聯通主伺服器:mysql/bin/mysql -uuser1 -p123455 test -h192.168.10.11：設定檔中：修改資料庫的server-id，注意server-id的值必須是唯一的，不能和主要資料的配置相同，如果有多個   從伺服器，每個從伺服器必須有自己唯一的server-id值   開啟bin-log2：設定檔中->開啟和配置：（報錯的話用下面的）master-host = 192.168.10.1    (主要資料庫IP)master-user = user1           (授權配置的使用者名稱)master-password = 123456      (授權配置的密碼)master-port = 3306            (連結主伺服器用的連接埠)--重啟MySQL伺服器*重啟報錯處理方式*：錯誤如下：120401 15:45:44 [ERROR] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: unknown variable 'master-host=192.168.8.111'120401 15:45:44 [ERROR] Aborting  *千萬注意------解決方案--------------------Mysql版本從5.1.7以後開始就不支援“master-host”類似的參數在從庫上執行如下命令；change master to master_host='masterIP', master_user='slave', master_password='slvaePASS';slave start;重啟3：配置完後->檢測是否和主伺服器同步方法（資料同步成功不成功就看*）：show slave status\G看到：* Salve_IO_Running :Yes   （表示成功的把主伺服器的bin-log日誌拿回來了且同步，寫一行我就同步一行）  * Salve_SQL_Running :Yes  （拿過來的bin-log日誌並成功的執行了sql語句，一主要資料一致）  --看到為yes的說明已經和主伺服器取得同步  master_Log_File：mysql-bin.00002    (同步過來的bin-log日誌)  Read_Master_Log_Pos:106             (位置，show master status主看下就知道)  Connect_Retry:60                    (每隔60秒回去主伺服器同步bin-log日誌)  蹤跡日誌：  Relay-Log_File:localhost-relay-bin.00004   （從伺服器產生的記錄檔，不叫mysql-bin.xxx）  資料同步過來後，我們查詢資料就可以去從伺服器上查詢不用去主伺服器，減輕負載                 4：從資料庫常用命令（1）start slave       啟動複製線程（去主伺服器拿資料）（2）stop  salve       停止複製線程（3）show slave status 查看資料庫狀態-與主伺服器一些連結狀態資訊（4）show master logs   查看主要資料庫bin-log日誌（5）change master to   動態改變主伺服器的配置（6）show processlist   查看資料庫運行進程5：mysql常見錯誤********從資料庫無法同步********show slave status 顯示slave_sql_runing為NO,seconds_behind_master 為null原因：A:程式可能在slave上進行了寫操作  B:也可能是slave機器重啟後，事物復原造成的解決一：slave stop；    set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;  設定一個全域的參數    slave start;解決二：(1):salve（從）庫， slave stop;   --停掉slave服務    master（主）庫，show master status； 得到&A日誌文名 &B 位置--得到主伺服器上當前的二進位日誌和位移量 (2)salve庫：查看狀態，能後到salve伺服器上執行手動同步--人為操作change master to master_host="192.168.101.1",master_uset="user1",master_password="pass123456",master_port=3306,master_log_file="mysql-bin.00003",     步驟（1）查出來的&A 記錄檔名master_log_pos=98;   步驟（1）查出來的&B 位置 --能後手動同步(3)啟動salve服務salve start;(4)通過show salve status 查看Salve_SQL_Runing 為yes,Seconds_Behind_Master為0即為正常</span>

Mysql主從複製的問題

--主機開兩個視窗，一個進入mysql，一個是shell
--主機阻斷寫操作
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 983407 | openser | mysql,test |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
--另外一個視窗匯出主機資料庫
mysqldump -u root -ppassword --master-data=2 --opt -R openser > openser20120204.sql
--剛才的視窗主機解鎖
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>
--打包資料檔案到從機
drop database openser;
create database openser;
mysql -u root -ppassword openser < openser20120204.sql
--從機操作
SLAVE STOP;
reset slave;
CHANGE MASTER TO MASTER_HOST='192.168.21.26',
MASTER_USER='repl_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=983407;
start slave;
show slave status\G
 
問：1，mysql主從複製是什概念，什場合下用，最好舉例說明；

1 主從複製，是用來建立一個和主要資料庫完全一樣的資料庫環境，稱為從資料庫；主要資料庫一般是即時的業務資料庫，從資料庫的作用和使用場合一般有幾個：
一是作為後備資料庫，主要資料庫伺服器故障後，可切換到從資料庫繼續工作；
二是可在從資料庫作備份、資料統計等工作，這樣不影響主要資料庫的效能；
2 讀寫分離，是指讀與寫分別使用不同的資料庫，當然一般是在不同伺服器上的；在同一台伺服器上的讀寫環境，估計只是用來測試吧。
一般讀寫的資料庫環境配置為，一個寫入的資料庫，一個或多個讀的資料庫，各個資料庫分別位於不同的伺服器上，充分利用伺服器效能和資料庫效能；當然，其中會涉及到如何保證讀寫資料庫的資料一致，這個就可以利用主從複製技術來完成。
一般應用場合為：業務輸送量很大，讀資料庫（可簡單理解為select語句的 比例和影響）的負載較大；
官方的mysql-proxy就是一個實現了讀寫分離、負載平衡等多個功能的軟體。