MySQL怎麼實現主從同步和Django實現MySQL讀寫分離

MySQL讀寫分離
場景
我們的專案中已經存在非常多的資料庫表了，資料量也會逐漸增多，所以我們需要做一些資料庫的安全和效能的最佳化。對於資料庫的最佳化，我們選擇使用MySQL讀寫分離實現。涉及內容包括主從同步和Django實現MySQL讀寫分離。
MT4問題彙總
MySQL主從同步
主從同步機制
主從同步介紹和優點

在多臺資料伺服器中，分為主伺服器和從伺服器。一臺主伺服器對應多臺從伺服器。
主伺服器只負責寫入資料，從伺服器只負責同步主伺服器的資料，並讓外部程式讀取資料。
主伺服器寫入資料後，即刻將寫入資料的命令傳送給從伺服器，從而使得主從資料同步。
應用程式可以隨機讀取某一臺從伺服器的資料，這樣就可以分攤讀取資料的壓力。
當從伺服器不能工作時，整個系統將不受影響；當主伺服器不能工作時，可以方便地從從伺服器選舉一臺來當主伺服器
使用主從同步的優點：
提高讀寫效能
因為主從同步之後，資料寫入和讀取是在不同的伺服器上進行的，而且可以透過增加從伺服器來提高資料庫的讀取效能。
提高資料安全
因為資料已複製到從伺服器，可以在從伺服器上備份而不破壞主伺服器相應資料。
主從同步機制






注意：MySQL伺服器之間的主從同步是基於二進位制日誌機制，主伺服器使用二進位制日誌來記錄資料庫的變動情況，從伺服器透過讀取和執行該日誌檔案來保持和主伺服器的資料一致。


Docker安裝執行MySQL從機
本專案中我們搭建一主一從的主從同步。


主伺服器：ubuntu作業系統中的MySQL。


從伺服器：Docker容器中的MySQL


獲取MySQL映象


# 主從同步儘量保證多臺MySQL的版本相同或相近
 sudo docker image pull mysql:5.7.22 # 5.7.22版本 
 sudo docker load -i 檔案路徑/mysql_docker_5722.tar
1
2
3
指定MySQL從機配置檔案


在使用Docker安裝執行MySQL從機之前，需要準備好從機的配置檔案。
為了快速準備從機的配置檔案，我們直接把主機的配置檔案複製到從機中。
# 建立MySQL從機配置資料夾
mkdir mysql_slave
# 建立裝資料庫資料資料夾
mkdir data


cp -r /etc/mysql/mysql.conf.d ./
1
2
3
4
5
6
修改MySQL從機配置檔案


編輯 ~/mysql_slave/mysql.conf.d/mysqld.cnf檔案。
由於主從機都在同一個電腦中，所以我們選擇使用不同的埠號區分主從機，從機埠號是8306。
# 從機埠號
port = 8306
# 關閉日誌
general_log = 0
# 從機唯一編號
server-id = 2
1
2
3
4
5
6
Docker安裝執行MySQL從機


建立 root 使用者的密碼為 mysql


 sudo docker run --name mysql-slave -e MYSQL_ROOT_PASSWORD=mysql -d --network=host -v /home/python/mysql_slave/data:/var/lib/mysql -v /home/python/mysql_slave/mysql.conf.d:/etc/mysql/mysql.conf.d mysql:5.7.22
1
測試從機是否建立成功


mysql -uroot -pmysql -h 127.0.0.1 --port=8306
1
主從同步實現
配置主機（ubuntu中MySQL）


配置檔案如有修改，需要重啟主機。


sudo service mysql restart
# 開啟日誌
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# 主機唯一編號
server-id = 1
# 二進位制日誌檔案
log_bin = /var/log/mysql/mysql-bin.log
1
2
3
4
5
6
7
從機備份主機原有資料


# 在做主從同步時，如果從機需要主機上原有資料，就要先複製一份到從機
# 1. 收集主機原有資料
mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql


# 2. 從機複製主機原有資料
mysql -uroot -pmysql -h127.0.0.1 --port=8306 < ~/master_db.sql
1
2
3
4
5
6
主從同步實現


# 登入到主機
$ mysql –uroot –pmysql
# 建立從機賬號
$ GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
# 重新整理許可權
$ FLUSH PRIVILEGES;
# 展示ubuntu中MySQL主機的二進位制日誌資訊
$ SHOW MASTER STATUS;
# 可以看到master_log_file和master_log_pos=Position




# 登入到從機
$ mysql -uroot -pmysql -h 127.0.0.1 --port=8306
# 從機連線到主機
$ change master to master_host='127.0.0.1', master_user='slave', master_password='slave',master_log_file=XXX, master_log_pos=XXX;
# 開啟從機服務
$ start slave;
# 展示從機服務狀態
$ show slave status \G
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Django實現MySQL讀寫分離
增加slave資料庫的配置
DATABASES = {
    'default': { # 寫（主機）
        'ENGINE': 'django.db.backends.mysql', # 資料庫引擎
        'HOST': 'XXXX', # 資料庫主機
        'PORT': 3306, # 資料庫埠
        'USER': 'XX', # 資料庫使用者名稱
        'PASSWORD': 'XXX', # 資料庫使用者密碼
        'NAME': 'XXX' # 資料庫名字
    },
    'slave': { # 讀（從機）
        'ENGINE': 'django.db.backends.mysql',
        'HOST': 'XXXX',
        'PORT': 8306,
        'USER': 'XXX',
        'PASSWORD': 'XXX',
        'NAME': 'XXX'
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
建立和配置資料庫讀寫路由
建立資料庫讀寫路由


# 在meiduo_mall.utils.db_router.py中實現讀寫路由




class MasterSlaveDBRouter(object):
    """資料庫讀寫路由"""


    def db_for_read(self, model, **hints):
        """讀"""
        return "slave"


    def db_for_write(self, model, **hints):
        """寫"""
        return "default"


    def allow_relation(self, obj1, obj2, **hints):
        """是否執行關聯操作"""
        return True
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
配置資料庫讀寫路由


# 配置資料庫讀寫分離路由
DATABASE_ROUTERS = ['meiduo_mall.utils.db_router.MasterSlaveDBRouter']
1
2
冷備份
冷備份發生在資料庫已經正常關閉的情況下，當正常關閉時會提供給我們一個完整的資料庫。冷備份是將關鍵性檔案複製到另外位置的一種說法。對於備份資料庫資訊而言，冷備份是最快和最安全的方法。簡單說法就是：離線（沒有新的資料來）複製一份儲存，有事就直接用這備份來恢復。




1
冷備份的優點：
是非常快速的備份方法（只需複製檔案）
容易歸檔（簡單複製即可）
容易恢復到某個時間點上（只需將檔案再複製回去）
能與歸檔方法相結合，作資料庫“最新狀態”的恢復。
低度維護，高度安全。
冷備份的缺點：
單獨使用時，只能提供到“某一時間點上”的恢復。
在實施備份的全過程中，資料庫必須要作備份而不能作其它工作。也就是說，在冷備份過程中，資料庫必須是關閉狀態。
若磁碟空間有限，只能複製到磁帶等其它外部儲存裝置上，速度會很慢。
不能按表或按使用者恢復。
值得注意的是冷備份必須在資料庫關閉的情況下進行，當資料庫處於開啟狀態時，執行資料庫檔案系統備份是無效的 。而且在恢復後一定要把資料庫檔案的屬組和屬主改為mysql。


熱備份
熱備份是在資料庫執行的情況下，備份資料庫操作的sql語句，當資料庫發生問題時，可以重新執行一遍備份的sql語句。簡單說就是：線上的儲存對資料庫操作的sql語句，有事就再跑一遍這些sql語句。


# 在做主從同步時，如果從機需要主機上原有資料，就要先複製一份到從機
# 1. 收集主機原有資料
mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql


# 2. 從機複製主機原有資料（在配置檔案所在目錄下，資料會寫入data檔案中）
mysql -uroot -pmysql -h127.0.0.1 --port=8306 < ~/master_db.sql
1
2
3
4
5
6
熱備份的優點：
可在表空間或資料檔案級備份，備份時間短。
備份時資料庫仍可使用。
可達到秒級恢復（恢復到某一時間點上）。
可對幾乎所有資料庫實體作恢復。
恢復是快速的，在大多數情況下在資料庫仍工作時恢復。
熱備份的缺點：
不能出錯，否則後果嚴重。
若熱備份不成功，所得結果不可用於時間點的恢復。
因難於維護，所以要特別仔細小心，不允許“以失敗而告終”