什么是資料庫同步
指在存盤型別、格式和計算機系統之間的資料轉換，這個名詞沒有嚴格的定義，反正就那個意思
mysql配置
mysql要實作資料庫同步，得配置下才行，
看是否有my.ini檔案，有就直接跳過到第4步，如果沒有就跟著我的操作來
1.洗掉mysql服務：
終端下，進入mysql的bin目錄：
sc delete MySql
2.在mysql的根目錄下創建一個my.ini檔案，
把下面的內容放進去保存：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = D:\MySQL
datadir = D:\MySQL\data
port = 3306
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
3.重新生成data檔案夾
此時此刻會洗掉之前的已有的資料，所以，這里我建議你先把之前的data檔案夾備份一下，但我個人是直接把data檔案夾重命名為data123
同樣的，終端下，bin目錄下，
mysqld --initialize-insecure --user=mysql
生成data之后，再進入data123檔案夾，把里面的檔案全部復制到data檔案夾下即可，我試了下，資料沒有丟失，但是不保證絕對沒有問題，如果擔心的朋友，可以提前把資料匯出成sql檔案，之后再把sql檔案匯入即可
4.重新添加服務并關聯上my.ini檔案
終端，bin目錄下
mysqld --install "MySql" --defaults-file="你剛才創建的my.ini目錄路徑"
此時就能在服務里看到它了
啟動服務，如果能正常啟動，那就沒有問題了，
如果已有my.ini的話，關閉mysql服務，重啟mysql即可
5.設定binlog
my.ini作如下配置：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4'
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = D:\programe\mysql-5.7.28-winx64
datadir = D:\programe\mysql-5.7.28-winx64\data
port = 3306
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#binlog日志名稱前綴
log-bin=mysql-bin
#默認值未0，如果使用默認值則不能和從節點通信，這個值的區間是：1到(2^32)-1
server-id=1
binlog-format=Row
binlog_row_image = full
max_binlog_size = 1G
相關的binlog引數：
log-bin [=file_name] 此引數表示啟用binlog日志功能，并可以定制路徑名稱，默認為mysql-bin，
binlog_format 此引數配置binlog的日志格式，默認為mixed，
max_binlog_size此引數配置binlog的日志最大值，最大和默認值是1GB，
max_binlog_cache_size此引數表示binlog使用最大記憶體的數，
binlog-do-db=db_name 此引數表示只記錄指定資料庫的二進制日志，
binlog-ignore-db=db_name此引數表示不記錄指定的資料庫的二進制日志，
expire_logs_days 此引數表示binlog日志保留的時間，默認單位是天，
6.重啟mysql服務即可
咋重啟就不說了
7.進入mysql終端驗證是否開啟
show variables like '%log_bin%';
show variables like '%log_bin%';
on意思就是開啟binlog成功
什么時候會產生binLog檔案
- 當mysql停止或重啟時，mysql會在重啟時生成一個新的binlog檔案，檔案序號依次遞增，
- binlog檔案超過max_binlog_size 系統變數配置的上限時
- mysql命令中執行flush logs命令
查看日志檔案
show master logs;
洗掉日志
按名字洗掉，只洗掉給出該名字之前的，并不包括該檔案
purge {master|binary} logs to 'log_name';
按日期洗掉，只洗掉給出該日志之前的，并不包括該檔案
purge {master|binary} logs before 'date’;
洗掉全部binlog:
reset master;
使用binlog恢復資料
mysqlbinlog xxx-bin.000001|mysql -u root –p password dataname
python 相關接入
其實使用mysql自帶的mysqlbinlog也可以恢復，但是諸多的使用場景下，并不太適合，所以就有了第三方的接入控制，
python的話，我找了一共四個庫可以處理binlog
- mysql-replication：國外一個大佬開發的，實作資料的復制
- binlog2sql：基于mysql-replication，大眾點評的dba開發的，將binlog日志的sql轉出
- py-mysql-binlogserver：實作資料的半同步
- binlog2cache：將binlog日志轉到redis內
本文主要介紹mysql-replication和binlog2sql
mysql-replication
安裝mysql-replication
pip install mysql-replication
然后使用如下代碼運行起來，實時監聽mysql資料的變動：
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.row_event import (
DeleteRowsEvent,
UpdateRowsEvent,
WriteRowsEvent,
)
import sys
import json
mysql_settings = {'host': '127.0.0.1','port': 3306,
'user': 'root', 'passwd': ''}
def main():
stream = BinLogStreamReader(
connection_settings=mysql_settings,
server_id=1,
blocking=True,
only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])
for binlogevent in stream:
for row in binlogevent.rows:
event = {"schema": binlogevent.schema, "table": binlogevent.table, "log_pos": binlogevent.packet.log_pos}
if isinstance(binlogevent, DeleteRowsEvent):
event["action"] = "delete"
event["values"] = dict(row["values"].items())
event = dict(event.items())
elif isinstance(binlogevent, UpdateRowsEvent):
event["action"] = "update"
event["before_values"] = dict(row["before_values"].items())
event["after_values"] = dict(row["after_values"].items())
event = dict(event.items())
elif isinstance(binlogevent, WriteRowsEvent):
event["action"] = "insert"
event["values"] = dict(row["values"].items())
event = dict(event.items())
print(json.dumps(event,ensure_ascii=False))
sys.stdout.flush()
if __name__ == "__main__":
main()
我這里用nvicat增，刪，改了三條資料
代碼自動列印如下：
雖然能很清晰的看出具體做了那些操作，但是，此時此刻我想將這些操作同步到另一個資料庫的話，我每一個命令都需要轉換一下，實話說，有點不順手，太繁瑣了
binlog2sql
我現在的場景是需要對資料庫進行遠程同步，一個主資料庫，一個從資料庫，主資料庫做了任何資料的更新操作，從資料庫都要同步到，所以我覺得binlog2sql更實用一點
1.首先，安裝binlog2sql，終端下執行：
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
2.my.ini配置添加如下：（其實我上面給的my.ini配置已經配置了）
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
3.給定mysql指定賬號的select, super/replication client, replication slave權限，
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 用戶名@'主機'
4.查看binlog日志：
5.執行binlog2sql
打開終端，進入剛才下載的binglog2sql目錄的binlog2sql.py檔案同檔案目錄下：我這里是 C:\Users\Administrator\Desktop\binlog2sql\binlog2sql
python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p '用戶名' -d 資料庫名 -t 表名 --start-file='日志檔案名'
列印結果：
由于我中間重啟過一次，所以，剛才的增刪改命令分散在000002和000003檔案里了：
好的，sql陳述句被我們拿到了，上面有些打了馬賽克的，是一些無關的資料
可以通過給定的開始和結束的位置，可以得出當前操作與上一次操作的變更，這個變更，在上面就給出來了
python binlog2sql.py --flashback -h127.0.0.1 -P3306 -u root -p '' -d 資料庫名 -t 表名 --start-file='mysql-bin.000002' --start-position=4 --stop-position=458
也可以把這些sql陳述句輸出到一個sql陳述句里：
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p -d 資料庫名 --start-file=日志名 --start-position=變更開始位置 --stop-position=變更結束位置 -B > 輸出的檔案名.sql
windows下的報錯：
但是這里卻報錯了，找到這個源檔案的該行：
修改如下：
再次運行命令，不再報錯，
同時binlog2sql目錄下也有了這個檔案：
打開這個檔案：沒有問題，即可直接匯入sql陳述句
完整實用的sql:
現在我的需求是，做資料庫同步，不管主資料庫做什么操作，我的從資料庫都要同步，所以，以下的命令即可完成我的需求
python3 binlog2sql.py -h%s -P%s -u %s -p "%s" -d %s -t %s --start-file="%s" --start-position=%s --stop-position=%s > %s --only-dml --sql-type INSERT DELETE UPDATE
將得到的sql陳述句同步到從資料庫
現在sql陳述句有這么多
我這有兩個方案：
第一個：讀取每一個sql檔案，然后一行一行的sql陳述句遍歷操作，使用pymysql執行
第二個：直接終端下做備份操作
我最開始使用的是第二個方案，借用Python直接呼叫終端命令，選了os.popen和subProcess.popen執行，mysql匯入sql資料有兩種，
一種是:
mysql -u root -p
mysql>use 資料庫
mysql>source xxx.sql
第二種：
mysql -u 賬號 -p 資料庫 < xzx.sql (也有這么用的，mysqljump -u 賬號 -p 資料庫 < xxx.sql）
我終端操作的話，那只能用第二種，結果，發現就是不行，匯入不進去，具體原因我也不清楚，使用第一種的話，需要互動，我找了個能實作終端互動的庫inspect，發現還是不夠實用，那么沒法，我就只能用第一個方案，讀取sql一句一句操作了，
操作自然是可行的，而當我把同等的配置遷移到mac上操作時，報了如下錯：
mac下操作的問題
網上查說是mysql8支持的字串長度不止255，我看了我mac上裝的mysql確實是8，而我之前在windows上測驗沒問題，其是mysql5.7
針對這個問題，網上一片的方案說是需要升級pymysql庫，我用pip升級之后，匯入成功
但是這里有個大坑，他媽的，當我再次用binlog2sql匯出sql時，報了一堆的錯，然后我突然反應過來，馬德，binlog2sql不是指定了pymysql版本7嗎，而我升級了pymyql版本是10以后，肯定binlog2sql用不了了，臥槽，頓時覺得好無語啊，解決方法就是要嘛對mysql降級，要嘛等binlog2sql更新支持最新版的mysql，要嘛自己去修改pymysql和binlog2sql和pymyreplication這三個庫里的配置
pymysql開發者在issue里也沒給出一個完美的解決方案：
https://github.com/PyMySQL/PyMySQL/pull/591
我也就不折騰了，在docker里拉了一個5.7版本的mysql操作了，結果自然是可行的
總結
之后要想做資料庫同步，就可以將所有的變更存到一個sql檔案里，然后再在另一個資料庫（此時這個資料庫可以是另一臺服務器上的）執行sql陳述句即可，具體怎么實作就根據場景變通即可，
binlog2sql更多具體的操作，看官方檔案：https://github.com/danfengcao/binlog2sql
根據你自己的使用場景對可選引數進行選擇即可
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/3028.html
標籤：Python