程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL的主從復制步調詳解及罕見毛病處理辦法

MySQL的主從復制步調詳解及罕見毛病處理辦法

編輯：MySQL綜合教程

MySQL的主從復制步調詳解及罕見毛病處理辦法。本站提示廣大學習愛好者：（MySQL的主從復制步調詳解及罕見毛病處理辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL的主從復制步調詳解及罕見毛病處理辦法正文

mysql主從復制(replication同步)如今企業用的比擬多,也很成熟.它有以下長處:

1.下降主辦事器壓力,可在從庫上履行查詢任務.

2.在從庫長進行備份,防止影響主辦事器辦事.

3.當主庫湧現成績時,可以切換到從庫上.

不外,用它做備份時就會也有弊病,假如主庫有誤操作的話,從庫也會收到敕令.

上面直接進入操作.這裡應用的是debian5操作體系,mysql5.0,默許引擎innodb

10.1.1.45 主庫

10.1.1.43 從庫

1.設置主庫

1)修正主庫my.cnf,這裡重要是server-id必定主從不要設置成一樣的.翻開binlog日記

log-bin   = /opt/log.bin/45
server-id  = 45

2)在主辦事器上樹立同步賬號

mysql> grant REPLICATION SLAVE ON *.* TO 'repl'@'10.1.1.43' IDENTIFIED BY 'replpass';

留意:mysql的權限體系在完成上比擬簡略,相干權限信息重要存儲在幾個體系表中:mysql.user,mysql.db,mysql.host,mysql.table_priv,mysql.columm_priv.因為權限信息的數據量比擬小,拜訪又異常頻仍,所以mysql在啟動的時刻,就會將一切的權限信息都加載到內存中,並保留在幾個特定的構造裡.這就使得每次手動修正了相干權限表以後,都須要履行flush privileges，告訴mysql從新加載mysql的權限信息.固然,假如經由過程grants,revoke或drop user 敕令來修正相干權限，則不須要手動履行flush privileges敕令.

3)在主辦事器上導出數據庫其時的快照,傳給從庫上.

[email protected]:mysql# mysqldump -uroot -p --single-transaction --flush-logs --master-data --all-databases > all.sql
--single-transaction:這個選項可以或許讓innoDB和Falcon數據表在備份進程中堅持不變.這一做法的症結在於它是在統一個事務裡來導入各有關數據表的.mysqldump應用repeatable read事務隔離層來生成一份穩固分歧的轉儲文件,同時不會壅塞其他客戶(關於非事務性表,轉儲進程能夠有變更),它不克不及與--lock-all-tables選項一路應用.

--flush-logs:在導收工作開端之前先清空mysql辦事器的日記文件.如許更輕易恢復操作,曉得在檢討點時光以後創立的二進制日記文件是在備份給定命據庫以後完成的.聯合應用--lock-all-tables或--master-data,只要在一切數據表都鎖定以後才消除日記.這個選項須要具有reload權限.

--master-data:應用後mysqldump會在dump文件中發生changer master to敕令,外面記載了dump時辰所對應的具體的log position信息.

[email protected]:mysql# sed -n '1,40p' all.sql 
-- MySQL dump 10.11
--
-- Host: localhost Database: 
-- ------------------------------------------------------
-- Server version 5.0.51a-24+lenny1-log
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Position to start replication or point-in-time recovery from
--
 
CHANGE MASTER TO MASTER_LOG_FILE='45.000064', MASTER_LOG_POS=98;
 
--
-- Current Database: `bbs`
--
 
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bbs` /*!40100 DEFAULT CHARACTER SET latin1 */;
 
USE `bbs`;
 
--
-- Table structure for table `davehe`
--
 
DROP TABLE IF EXISTS `davehe`;
SET @saved_cs_client  = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `davehe` (

2.設置從庫

1).修正從庫my.cnf

server-id = 43 #主從可1對多 從各id不克不及雷同

2)將主庫的快照灌入從庫

[email protected]:tmp# cat all.sql | mysql -uroot -p

3)在從庫上設置同步.檢查從庫狀況.

mysql> change master to master_host='10.1.1.45', master_user='repl',master_password='replpass',master_log_file='45.000064',master_log_pos=98;
Query OK, 0 rows affected (0.01 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: 10.1.1.45
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
   Master_Log_File: 45.000064
  Read_Master_Log_Pos: 98
    Relay_Log_File: mysqld-relay-bin.000002
    Relay_Log_Pos: 228
  Relay_Master_Log_File: 45.000064
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Replicate_Do_DB: 
  Replicate_Ignore_DB: 
   Replicate_Do_Table: 
  Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
     Last_Errno: 0
     Last_Error: 
    Skip_Counter: 0
  Exec_Master_Log_Pos: 98
   Relay_Log_Space: 228
   Until_Condition: None
    Until_Log_File: 
    Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File: 
   Master_SSL_CA_Path: 
   Master_SSL_Cert: 
   Master_SSL_Cipher: 
    Master_SSL_Key: 
  Seconds_Behind_Master: 0
1 row in set (0.00 sec)
 
 
ERROR: 
No query specified

測試OK

固然這只是最簡略的設置裝備擺設

還有許多參數可依據情況需求變更.

好比

replicate-do-db=test 過濾拉主日記到從只須要這個庫和上面的表
replicate-wild-do-table=test.dave
replicate-wild-do-table=test.davehe

mysql數據庫同步跳過暫時毛病

stop slave;
set GLOBAL SQL_SLAVE_SKIP_COUNTER=1; (事務類型,能夠須要履行幾回)
start slave;

stop slave IO_THREAD //此線程把master段的日記寫到當地
start slave IO_THREAD
stop slave SQL_THREAD //此線程把寫到當地的日記運用於數據庫
start slave SQL_THREAD

Slave_IO_Running: No毛病

因為主庫的主機192.168.1.1宕機,再次啟來後，從庫192.168.71.1銜接主庫發明報錯. Slave_IO_Running: No

[email protected]:~# mysql -uroot -p --socket=/opt/mysql/3399/3399.sock 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 452723
Server version: 5.0.51a-24+lenny2 (Debian)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> show slave status\G;
*************************** 1. row ***************************
    Slave_IO_State: 
    Master_Host: 192.168.1.1
    Master_User: repl
    Master_Port: 3306
    Connect_Retry: 60
   Master_Log_File: 99.000302
  Read_Master_Log_Pos: 165112917
    Relay_Log_File: 3399-relay-bin.000013
    Relay_Log_Pos: 165113047
  Relay_Master_Log_File: 99.000302
   Slave_IO_Running: No
   Slave_SQL_Running: Yes
   Replicate_Do_DB: 
  Replicate_Ignore_DB: mysql
   Replicate_Do_Table: 
  Replicate_Ignore_Table: 
 Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
     Last_Errno: 0
     Last_Error: 
    Skip_Counter: 0
  Exec_Master_Log_Pos: 165112917
   Relay_Log_Space: 165113047
   Until_Condition: None
    Until_Log_File: 
    Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File: 
   Master_SSL_CA_Path: 
   Master_SSL_Cert: 
   Master_SSL_Cipher: 
    Master_SSL_Key: 
  Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

檢查毛病日記

[email protected]:/opt/mysql/3399$ cat 192.168.71.1.err
140115 1:51:01 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
140115 1:51:01 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
140115 1:51:01 [Note] Slave I/O thread exiting, read up to log '99.000302', position 165112917

依據毛病地位,查找主庫上log ‘99.000302' 對應的地位 165112917

[email protected]:mysql.bin# mysqlbinlog 99.000302 > /tmp/test
[email protected]:mysql# tail -n 10 /tmp/test 
#140115 0:50:25 server id 1176 end_log_pos 165111351  Query thread_id=111 exec_time=0 error_code=0
SET TIMESTAMP=1389718225/*!*/;
INSERT INTO user_info_db_86.region_info_table_56 (userid, region, gameflag) VALUES (563625686, 0, 2) ON DUPLICATE KEY UPDATE gameflag = (gameflag | 2)/*!*/;
# at 165111351
#140115 0:50:25 server id 1176 end_log_pos 165111378  Xid = 17877752
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

成果發明主庫上地位最初是165111351 比165112917要小. 也就是從庫同步找的地位比主庫要年夜，故同步不勝利

為何會如許,這是由於這個在sync_binlog=0的情形，很輕易湧現。
sync_binlog=0，當事務提交以後，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓體系自行決議甚麼時刻來做同步，或許cache滿了以後才同步到磁盤。

sync_binlog=n，當每停止n次事務提交以後，MySQL將停止一次fsync之類的磁盤同步指令來將binlog_cache中的數據強迫寫入磁盤。

在MySQL中體系默許的設置是sync_binlog=0，也就是不做任何強迫性的磁盤刷新指令，這時候候的機能是最好的，然則風險也是最年夜的。由於一旦體系Crash，在binlog_cache中的一切binlog信息都邑被喪失。而當設置為“1”的時刻，是最平安然則機能消耗最年夜的設置。由於當設置為1的時刻，即便體系Crash，也最多喪失binlog_cache中未完成的一個事務，對現實數據沒有任何本質性影響。從以往經歷和相干測試來看，關於高並發事務的體系來講，“sync_binlog”設置為0和設置為1的體系寫入機能差距能夠高達5倍乃至更多。

這裡因為mysql是默許設置裝備擺設所以該報錯緣由是： sync_binlog=0時，master binlog文件的flush log buffer（這個buffer是因為binlog文件的os buffer) 到disk是依附於OS自己的，但Slave IO 線程在讀取master dump 線程的地位，普通是直接讀取log buffer的,這個地位，能夠遠弘遠於binlog文件現實年夜小。所以當主機宕機後，binlog buffer未刷盤，當Master主機再次啟動後，此時從庫的binlog pos 165112917 曾經比現實的binlog地位年夜小165111351 還年夜了。

處理辦法:

直接做change master to到當下一個binlog。

CHANGE MASTER TO

 MASTER_HOST='192.168.1.1',

 MASTER_USER='repl',

 MASTER_PASSWORD='replpass',

 MASTER_PORT=3306,

 MASTER_LOG_FILE='99.000303',

 MASTER_LOG_POS=98;