MySQL主從模式及配置
主服務器：192.168.20.239
從服務器：192.168.20.176
主從原理
主節點
1、當主節點上進行 insert、update、delete 操作時，會按照時間先后順序寫入到 binlog 中；
2、當從節點連接到主節點時，主節點會創建一個叫做 binlog dump 的線程；
3、一個主節點有多少個從節點，就會創建多少個 binlog dump 線程；
4、當主節點的 binlog 發生變化的時候，也就是進行了更改操作，binlog dump 線程就會通知從節點 (Push模式)，并將相應的 binlog 內容發送給從節點；
從節點
當開啟主從同步的時候，從節點會創建兩個線程用來完成數據同步的工作。
I/O線程： 此線程連接到主節點，主節點上的 binlog dump 線程會將 binlog 的內容發送給此線程。此線程接收到 binlog 內容后，再將內容寫入到本地的 relay log。
SQL線程： 該線程讀取 I/O 線程寫入的 relay log，并且根據 relay log 的內容對從數據庫做對應的操作。
主從配置一般都是和讀寫分離相結合，主服務器負責寫數據，從服務器負責讀數據，并保證主服務器的數據及時同步到從服務器。
一、配置主服務器：
1：在mysql下找到my.ini文件，一般在 C:\ProgramData\MySQL\MySQL Server 5.7
在其[mysqld] 下添加主服務器端配置：
#服務器 id
server-id=1
#二進制文件存放路徑
log-bin=C:\ProgramData\MySQL\MySQL Server 5.7\Data\mysql-bin
#待同步的數據庫 （如果這一行沒有，那么就是針對所有的不用忽略的數據庫進行同步）
#binlog-do-db=test_db
#忽略不同步的數據庫，這個可以不用寫
#binlog-ignore-db=information_schema
#忽略不同步的數據庫，這個可以不用寫
#binlog-ignore-db=mysql
注意，上面的mysql-bin 并不是指一個文件夾，而是指 存放為二進制數據
2：保存my.ini文件，重啟數據庫，然后會在C:\ProgramData\MySQL\MySQL Server 5.7\Data 下看到 mysql-bin.index 文件和 mysql-bin.000001 文件；
3：連接主數據庫
mysql -u root -p123456
4：給要連接的從服務器設置權限：(注意下面的 單引號 分號 )
grant replication slave,reload,super on *.* to 'backup'@'192.168.20.%' identified by '123456';
注釋： 給主機192.168.20.%添加權限，用戶名：backup，密碼：123456；（只需輸入一次就可以了）
*.* 表示任意數據庫中的任意表，'192.168.20.%' 表示只允許192.168.20.··網段的“從”訪問“主” 的數據庫
如果你把字符，單引號或者是分號漏掉了，那么運行會沒有反應的，如果運行成功，會顯示 query ok
mysql> use mysql; mysql> select user from user; +---------------+ | user | +---------------+ | test | | backup | | mysql.session | | mysql.sys | | root | +---------------+
#查看用戶發現多了backup
實際就是在mysql中添加一個backup的 slave 賬號，并授權給從服務器。創建backup用戶，并授權給192.168.20.··使用。
5：輸入命令 show master status; # 找到File 和 Position 的值記錄下來； （如果你不小心把主服務器上的數據庫刪除了，然后重新添加了一個同名的，但是這里的position 是會改變的，一定要注意）
6: 導出主數據庫的數據
mysqldump -u root -p123456 --databases test_db > test_db.mysql
7：把導出的sql語句，導入從數據庫
方法一：
mysql>source F:\dir\test_db.mysql
方法二：
先創建DB，然后：mysql -uroot -p123456 test_db < test_db.mysql
這樣從服務器上也有了 test_db 這個數據庫了
二、配置從服務器：
1：在mysql下找到my.ini文件，在其[mysqld] 下添加從服務器端配置：
#服務器 id ，不能和主服務器一致
server-id=2
#同步的數據庫(需要備份的數據庫名)，不寫本行 表示 同步所有數據庫
#replicate-do-db=test_db
#忽略不同步的數據庫，這個可以不用寫
#binlog-ignore-db=information_schema
#忽略不同步的數據庫，這個可以不用寫
#binlog-ignore-db=mysql
#忽略不同步的數據庫，這個可以不用寫
#binlog-ignore-db=test
2：保存my.ini文件，重啟從數據庫，在mysql5.1以上版本中是不支持1中master設置的，如果添加了master設置，數據庫就無法重啟了(這個可能是針對以前的低于5.1的數據庫的，反正我們沒有用到那么低的，所以不用管什么 master設置)；
3：用上面分配的賬號backup，嘗試連接主數據庫，成功
mysql -ubackup -p123456 -h192.168.20.239
4：重新打開一個dos界面，用管理員賬號連接從數據庫
mysql –uroot –p123456
修改對主數據庫的連接的參數：
mysql> change master to master_host='192.168.20.239',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
ps：可能會報一個錯誤，大概的意思是說slave線程正在運行，不能設置，這樣的話，執行mysql> stop slave; 停止slave線程，然后再設置連接的參數；
設置后，可以執行命令查看狀態，Master_User: backup 就是剛剛的賬號
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.20.239 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-P02RV6K-relay-bin.000019 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: No 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: 1258 Relay_Log_Space: 308 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 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: a2085118-c063-11ec-a4a8-00090faa0001 Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 220508 16:56:41 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
注意：上面的IO線程啟動失敗了
Slave_IO_Running: No
錯誤信息 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
原因：一方面是因為網絡通信的問題，也有可能是日志讀取錯誤的問題。因為我是重新設置的，所以可能是讀取順序的錯誤。
解決辦法：
在 slave 執行 mysql> stop slave 在 master 執行 mysql> show master status; 刷新日志：mysql> flush logs; 因為刷新日志file的位置會+1，即File變成為:mysqld-bin.000002 馬上到 slave 執行 mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002',MASTER_LOG_POS=154; mysql> slave start;
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.20.239 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-P02RV6K-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes ******************************************************
Slave_IO_Running: Yes 這個表示：連接到主庫，并讀取主庫的日志到本地，生成本地日志文件. yes表示 網絡正常
Slave_SQL_Running: Yes 這個表示：讀取本地日志文件，并執行日志里的SQL命令。 yes表示 表結構正常
5：在 master 中添加一行數據，發現 slave 中也自動增加了，并且Position保持一致。
6：使用 show processlist 語句可查看線程狀態
主數據庫上：可以看到線程Id=5的 State 是 「Master has sent all binlog to slave; waiting for more updates」，說明同步線程一直在運行中。
mysql> show processlist; +----+--------+-----------------------+---------+-------------+------+---------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+-----------------------+---------+-------------+------+---------------------------------------------------------------+------------------+ | 2 | root | localhost:11714 | test_db | Query | 0 | starting | show processlist | | 5 | backup | DESKTOP-P02RV6K:50511 | NULL | Binlog Dump | 2315 | Master has sent all binlog to slave; waiting for more updates | NULL | +----+--------+-----------------------+---------+-------------+------+---------------------------------------------------------------+------------------+
從數據庫上：
mysql> show processlist; +----+-------------+-----------------+---------+---------+------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------------+---------+---------+------+--------------------------------------------------------+------------------+ | 6 | root | localhost:58744 | test_db | Query | 0 | starting | show processlist | | 9 | system user | | NULL | Connect | 2230 | Waiting for master to send event | NULL | | 10 | system user | | NULL | Connect | 858 | Slave has read all relay log; waiting for more updates | NULL | +----+-------------+-----------------+---------+---------+------+--------------------------------------------------------+------------------+
三、可能出問題的地方
1：開始一定要把數據庫數據同步，保證數據一致性。
2：如果Slave_IO_Running: NO 那么要看看是不是 (1) A有賬戶在用dos界面在登陸數據庫而沒有退出 (2) A數據庫的postion和file有改變，而B里面change的時候，還是用的以前的數據？
3：主庫不小心死機，重啟之后發現主庫數據更新，但是從庫不執行，造成了主從的數據不同步。但是在從上面查看 Slave_IO_Running 和 從庫的狀態，都是正常的。這個時候要注意在主庫看看 show master status 看看postion和file是不是和從庫一致的，我就發現我的主庫死機之后再次開機就不一樣了。這個時候在從庫執行
CHANGE MASTER TO MASTER_HOST='192.168.20.239',MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=154
但是這里還是有個問題，雖然這個時候，主從可以開始同步了，但是由于這中間，如果不是及時發現，那么主從的數據還是會有差異的，那這個怎么解決呢？ 答案是，只能重新設置主從。
4：如果我在主里面 delete from xx表 那么在從里面，到底是讀出主里面刪除了哪些行，進而對從進行刪除？還是直接也是和主一樣，運行了 delete from xx呢？答案是后者，從會像主一樣，直接運行 delete from xx 。所以如果你是有上面3這個問題發生（主從數據不同步了），后面再進行了其他操作的話，會越來越不同步的，所以趕緊重新做主從
5：從庫掛了怎么辦
在主服務的 binlog dump 線程將指定的 binlog 信息發給從服務時，除了日志內容，還包括本次發送內容在主服務端的 bin-log 日志文件名稱以及位置信息。
從服務的 I/O 線程接收到信息后將日志內容寫入realy-log 文件(mysql-relay-bin.xxxxxx)的末端，并將讀取到的主服務端的 bin-log 的文件名和位置記錄到 master-info 中（通過 show slave status 中的 Master_Info_File 字段可以看到 master.info 保存的位置），以便下一次讀取時能告訴主服務從哪里開始同步。
從服務的 SQL 線程檢測到 realy-log 新增了內容后，解析日志文件生成對應的 sql 語句，并應用這些 sql 到數據庫，保證主從數據一致性。
所以，即使從庫掛掉了，因為有 master.info 記錄了上一次同步的位置，只要同步服務再次啟動，那就可以從上次同步的位置繼續增量同步了。
參考資料
1：公眾號[古時的風箏]MySQL主從配置和讀寫分離 http://www.rzrgm.cn/fengzheng/p/13401783.html
2：windows 下mysql 主從庫的配置 http://www.rzrgm.cn/joeylee/archive/2013/01/24/2875515.html
浙公網安備 33010602011771號