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
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)在主服務器上導出數據庫當時的快照,傳給從庫上.
root@10.1.1.45: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信息.
root@10.1.1.45: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)將主庫的快照灌入從庫
root@10.1.1.43: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
當然這只是最簡單的配置
還有很多參數可根據環境需求變化.
比如
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
root@192.168.71.1:~# 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)
查看錯誤日志
mysql@192.168.71.1:/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
root@192.168.1.1:mysql.bin# mysqlbinlog 99.000302 > /tmp/test root@192.168.1.1: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;
聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
Copyright ? 2019-2025 51dongshi.com 版權所有
違法及侵權請聯系：TEL:177 7030 7066 E-MAIL:11247931@qq.com 本站由北京市萬商天勤律師事務所王興未律師提供法律服務
