MySQL主從複製原理實踐

MySQL主從複製原理實踐

MySQL主從複製
  MySQL主從複製實則是將原本建立和修改資料庫的SQL命令集合複製到從庫本地，在從庫本地重新執行這些SQL命令來建立與主庫一樣的資料。搭建從庫以後，包括複製主庫當前截止位置的所有資料，和接下來主庫還在不斷增長的資料。事實上，停庫備份全部資料再複製過去沒什麼問題，問題在於怎麼即時複製資料庫增長的這段資料？而這就要藉助binlog日誌的功能...

主從複製原理

1）開啟主庫log-bin功能，是為了使用者在寫資料庫過程中，MySQL會同時建立一份binlog日誌來記錄資料庫所做的增刪改等操作；還要建立一個從庫和主庫之間驗證關係的換用帳號rep，並授權；

2）鎖表，對資料庫做全備操作，最大程度保證資料一致性，最重要的是擷取當前資料庫binlog日誌分界點；
3）將停庫後的資料庫全量恢複到從庫
4）在從庫中執行CHANGE MASTER TO...命令來驗證使用者，和告訴從庫當前備份的資料庫binlog日誌分界點在哪，並產生master.info檔案；
5）開啟從庫，此時mysql replication功能才真正發揮作用，從庫B會利用自己的IO線程不斷詢問主庫A：哥們，我有暗號（密碼）是自己人，呃...我這本地有這些這些貨，你那有沒有新貨（新增的資料），也給我搞點。然後主庫A就會去看自己binlog日誌，發現真有些新貨，就會給這些新貨發給從庫B，並且附贈一份清單，告訴從庫B他現在倉庫裡有哪些貨，下次從庫B再來找他要新貨只要拿出這個清單A就知道他那裡的貨是不是和自己一樣的了，如果一樣就不發了，如果不一樣，就按照這個清單上的貨截止位置發最新的，這裡的新貨就可以理解為binlog檔案內容，清單就為binlog位置資訊post；
  從庫每次拿到新貨就會很開心啊，啟動自己的SQL線程將這些新貨解析到自己的資料庫中，保證與主庫A完全一致。

主從複製案例實踐

在本地多mysql多執行個體中，將/data/3306/mysql.sock作為主庫，/data/3307/mysql.sock作為從庫

主庫

1）開主庫binlog功能，並且保證server id不一樣
[root@db02 ~]# egrep "log-bin|server-id" log-bin /data/3306/my.cnf /data/3307/my.cnf
/data/3306/my.cnf:log-bin = /data/3306/mysql-bin
/data/3306/my.cnf:server-id = 1
/data/3307/my.cnf:#log-bin = /data/3307/mysql-bin
/data/3307/my.cnf:server-id = 3

2）主庫授權從庫帳號rep
mysql> grant replication slave on *.* to rep@'172.16.2.%' identified by 'oldboy123';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;

3）鎖表（鎖表後不能退出當前mysql控制視窗）
mysql> flush table with read lock;

4）查看binlog臨界點
mysql> show master status;
+------------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 |    3204 |              |                  |
+------------------+----------+--------------+------------------+

5）全備

[root@db02 ~]# mysqldump -uroot -poldboy1234 -S /data/3306/mysql.sock -A -B --events|gzip > /server/backup/mysql_$(date +%F).sql.gz
[root@db02 backup]# ll mysql_2016-07-07.sql.gz
-rw-r--r-- 1 root root 144620 Jul  7 04:18 mysql_2016-07-07.sql.gz

6）解鎖表
1 mysql> unlock table;

從庫

1）確保server ID 不同
[root@db02 ~]# grep server-id /data/3307/my.cnf
server-id = 4

2）恢複全備

[root@db02 backup]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock </server/backup/mysql_2016-07-07.sql

3）配置master，填寫正確的binlog位置點

mysql> CHANGE MASTER TO MASTER_HOST='172.16.2.10', MASTER_PORT=3306, MASTER_USER='rep'，
MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=3204;
##此時如果master密碼配置錯誤，重新改master.info檔案不生效，需執行reset slave all;
[root@db02 data]# cat master.info
18
mysql-bin.000015
3204
172.16.2.10
rep
oldboy123
3306
60
0

4）開啟從庫複製開關，驗證資料

mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.2.10
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 3289
              Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 338
        Relay_Master_Log_File: mysql-bin.000015
            Slave_IO_Running: Yes    #從庫從主庫複製binlog日誌進程
            Slave_SQL_Running: Yes    #從庫讀取中繼日誌轉換成SQL語句應用到資料庫進程
              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: 3289
              Relay_Log_Space: 488
              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: 0
                Last_IO_Error:
              Last_SQL_Errno: 0
              Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
            Master_Server_Id: 1

驗證完成。

本文永久更新連結地址：