主從 · JAVA · 看云

[TOC] # 主從讀寫分離可以看這個: [https://help.aliyun.com/document\_detail/85143.html?spm=a2c4g.11186623.2.14.6ab869abTLXJBz](https://help.aliyun.com/document_detail/85143.html?spm=a2c4g.11186623.2.14.6ab869abTLXJBz) ## 簡介 ![](https://img.kancloud.cn/df/fe/dffe83f643c93f9dfac5596581659b56_724x346.png) mysql復制過程分成三步: 1. master將改變記錄到二進制日志(binary log).這些記錄過程叫做二進制日志事件,`binary log events` 2. slave將master的`binary log events`拷貝到他的中繼日志 3. slave重做中繼日志中的事件,將改變應用到自己的數據庫中.mysql復制是異步的而且串行化的 **復制的基本原則** * 每個slave只有一個master * 每個slave只能有一個唯一的服務器ID * 每個master可以有多個slave **異步半同步區別** 1. 異步復制簡單的說就是master把binlog發送過去，不管slave是否接收完，也不管是否執行完，這一動作就結束了. 2. 半同步復制簡單的說就是master把binlog發送過去，slave確認接收完，但不管它是否執行完，給master一個信號我這邊收到了，這一動作就結束了。（谷歌寫的代碼，5.5上正式應用。） 3. 異步的劣勢當master上寫操作繁忙時，當前POS點例如是10，而slave上IO\_THREAD線程接收過來的是3，此時master宕機，會造成相差7個點未傳送到slave上而數據丟失。 **binlog作用** 記錄數據庫的增刪改查sql語句,二進制文件. 參數說明 -F: 備份后立即刷新binlog日志 `--master-date`=1或者2.備份語句中添加`change master`語句,記錄binlog位置點開啟binlog,創建rep用戶,授權`replication slave` binlog轉儲線程當從服務器與主服務器連接時，主服務器會創建一個線程將二進制日志內容發送到從服務器。該線程可以使用語句`SHOW PROCESSLIST`(下面有示例介紹) 在服務器 sql 控制臺輸出中標識為Binlog Dump線程。二進制日志轉儲線程獲取服務器上二進制日志上的鎖，用于讀取要發送到從服務器的每個事件。一旦事件被讀取，即使在將事件發送到從服務器之前，鎖會被釋放。 **master.info** 記錄主庫的binlog信息 **relay log** 將slaveIO線程讀取到的binlog數據寫入relaylog,由relaylog.info管理. 等待sql線程讀取內容并轉為sql語句并寫入到slave ## 常見配置 **注意創建文件夾的時候權限問題** * 主從都配置在`[mysqld]`節點下,而且都是小寫 * 主機修改my.ini配置文件(必須主服務器唯一id) * 從修改my.ini(必須: 從服務器唯一ID, 可選: 啟用二進制日志) * 必須啟用二進制配置文件 * 停止從服務復制功能`stop slave;` ### 主節點配置配置文件`[mysqld]` 配置唯一id ~~~ server-id=1 ~~~ 配置mysqlbin和錯誤日志沒有data文件夾,那就自己創建一個 log-bin=自己本地路徑/mysqlbin log-err=自己本地路徑/mysqlerr ~~~ log-bin=/usr/share/mysql/data/mysqlbin log-error=/usr/share/mysql/data/mysqlerr ~~~ 主機讀寫都可以 ~~~ read-only=0 ~~~ 可選 * 根目錄,basedir="路徑" * 臨時目錄,tmpdir="路徑" * 數據目錄="自己本地路徑/data" * 設置不要復制的數據庫 `binlog-ignore-db=mysql` * 設置需要復制的數據庫 `binlog-do-db=需要復制的主數據庫名` 其余配置 ~~~bash relay_log=relay-bin #開啟中繼日志 (日志存儲位置盡量不要同數據存儲同一磁盤同一目錄，這里測試方便不重新指向) binlog-format=row #日志格式三種:STATEMENT，ROW，MIXED log-slave-updates=true # 配置從服務器的更新寫入二進制日志 ~~~ ~~~sql mysql> show variables like 'log_%'; #查看日志是否開啟 ~~~ log\_bin、relay\_log，二進制日志和中繼日志盡量不要跟數據存儲放在同一磁盤同一目錄，防止硬盤損壞時日志也丟失 ### 從節點配置必須:從節點id 可選: 啟用二進制日志 ~~~ log-bin=mysql-bin ~~~ 其余配置 ~~~bash relay_log=relay-bin binlog-format=row log-slave-updates=true sever_id=2 #relay_log_purge=0 #禁止自動刪除中繼日志(slave配置文件多了下面這兩條），如果是MHA開啟此 ~~~ 啟動從庫 ~~~bash mysql -uroot -p123456 -e "set global read_only=1" #從庫只讀，不建議寫在配置文件中 ~~~ ### 主節點備份數據要把表結構什么的都在從庫上回放,不然主從復制會報錯 ~~~bash [root@node01 /]# mysqldump -uroot -p123456 -h192.168.2.70 --master-data=2 --single-transaction -R --triggers -A > /home/soft/all.sql; ~~~ 說明： \--master-data=2代表備份時刻記錄master的Binlog位置和Position \--single-transaction意思是獲取一致性快照 \-R意思是備份存儲過程和函數 \--triggres的意思是備份觸發器 \-A代表備份所有的庫查看更多信息mysqldump --help **從庫要回放數據** ### 創建用戶 ~~~ flush privileges; # 創建個從用戶replication Grant replication slave on *.* to 'repl'@'%' identified by '123456'; flush privileges; ~~~ 查詢master的狀態,并記錄下file和position的值(意思就是這個文件的這個位置開始復制). ~~~ mysql> show master status; +-----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-----------------+----------+--------------+------------------+-------------------+ | mysqlbin.000002 | 154 | | mysql | | +-----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ~~~ **建議用這種方式,上一種,如果中間有人修改數據,復制點就變了** 查看Master主庫備份時的binlog名稱和位置，MASTER\_LOG\_FILE和MASTER\_LOG\_POS： ~~~bash [root@node01 soft]# head -n 30 /home/soft/all.sql | grep 'CHANGE MASTER TO' -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154; ~~~ ### 從庫配置重啟從庫連接從庫,準備從當前點開始復制 ~~~ mysql> stop slave; #暫停從庫 mysql> CHANGE MASTER TO MASTER_HOST='192.168.33.80', -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysqlbin.000002', -> MASTER_LOG_POS=1264; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) ~~~ 查看下狀態 ~~~ mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.33.80 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqlbin.000002 Read_Master_Log_Pos: 1264 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 319 Relay_Master_Log_File: mysqlbin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ~~~ 看 `Slave_IO_State`以及`Slave_IO_Running`和`Slave_SQL_Running`都為yes 出問題,就看下面的error是什么,或者看日志 ## 故障切換確保所有主從數據庫都開啟二進制日志 ~~~ mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.01 sec) ~~~ 確保切換時數據時從庫都是最新先把主庫設為只讀：set global read\_only=1; 并且刷新一下主庫log-bin日志 ~~~sql mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> flush logs; Query OK, 0 rows affected (0.02 sec) ~~~ ### 主故障把主節點關閉從節點 ~~~ mysql> show slave status\G; ~~~ 會發現,io也連接不上 ~~~ Last_IO_Error: error reconnecting to master ~~~ 1. 確保所有的`relay log`全部讀取完畢 **在所有從庫上** ~~~ mysql> stop slave io_thread; Query OK, 0 rows affected (0.00 sec) mysql> show processlist; +----+-------------+--------------+--------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+--------------+--------+---------+------+--------------------------------------------------------+------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | | 4 | system user | | NULL | Connect | 2284 | Slave has read all relay log; waiting for more updates | NULL | | 5 | root | 192.168.33.5 | mytest | Sleep | 457 | | NULL | | 6 | root | 192.168.33.5 | mytest | Sleep | 458 | | NULL | +----+-------------+--------------+--------+---------+------+--------------------------------------------------------+------------------+ 4 rows in set (0.00 sec) ~~~ 直到看到`Slave has read all relay log; waitingfor more updates`，則表示從庫更新都執行完畢了. 或者通過`show slave status\G;`查看 2. 選擇新的主庫 `show slave status\G;` 對比選擇`Relay_Master_Log_File`，`Exec_Master_Log_Pos`最大的作為新的主庫，這里我們選擇slave1為新的主庫（如果兩個從IO進程一直都是正常，沒有落后于主，且`relay log`都已經重放完成，兩個從是一樣的，選擇哪個都可以） 3. 進行相應的配置在slave1上，執行： ~~~ mysql> stop slave; ~~~ 進入datadir，刪除`master.info` 和 `relay-log.info`（刪除前，可以先備份） ~~~ $ rm -rf master.info $ rm -rf relay-log.info ~~~ 配置my.cnf文件，開啟`log-bin`,如果有`log-slaves-updates=1`和`read-only=1`則要注釋掉，然后重啟slave1或在線修改參數。 master.info：記錄了mysql主服務器上的日志文件和記錄位置、連接的密碼。 4. 其他slave配置 slave2也要刪除這2個文件,或者`reset slave;` `reset slave`將清除slave上的同步位置，刪除所有舊的同步日志，使用新的日志重新開始，這正是我們想要的。需要注意的是，必須先停止slave服務（STOP SLAVE），我們已經在第一步停止了它。 5. `reset master` 在slave1上`reset master`，重新生成二進制日志。（RESET MASTER將刪除所有的二進制日志，創建一個.000001的空日志。如果盲目的在主庫上執行這個命令會導致slave找不到master的binlog，造成同步失敗。） ~~~ mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 154 | +------------------+-----------+ 1 row in set (0.00 sec) ~~~ 6. 在slave1上創建用于同步的用戶 ~~~ flush privileges; # 創建個從用戶replication Grant replication slave on *.* to 'repl'@'%' identified by '123456'; flush privileges; ~~~ 6. 重建主從,將slave2指向slave1 記得是slave1的ip ~~~ mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.33.81', -> MASTER_USER='repl', -> MASTER_PASSWORD='123456', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) ~~~ `show slave status\G;`查看下 ## 常見問題常見排查問題 ~~~ mysql> show processlist; 查看下進程是否Sleep太多。發現很正常。? mysql> show master status\G; mysql> show slave status\G; ~~~ **主從復制，中繼日志不斷增長，如何設置中繼日志自動清除?** 配置文件my.cnf,在mysqld下增添 ~~~ relay_log_purge=1 (自動清除中繼日志打開) ~~~ **主從同步失敗，如何快速同步?** 跳過出現指定錯誤的SQL. 如果要斷開主從架構，應先stop slave io\_thread; 等待執行完relay log里的內容再stop slave; ~~~ #表示跳過一步錯誤，后面的數字可變 stop slave; set global sql_slave_skip_counter=1; start slave; # 之后再用mysql> show slave status\G 查看： ~~~