python3数据库同步_python实现mysql数据库远程同步

什么是数据库同步

指在存储类型、格式和计算机系统之间的数据转换，这个名词没有严格的定义，反正就那个意思

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=1binlog-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 importBinLogStreamReaderfrom pymysqlreplication.row_event import(

DeleteRowsEvent,

UpdateRowsEvent,

WriteRowsEvent,

)importsysimportjson

mysql_settings= {'host': '127.0.0.1','port': 3306,'user': 'root', 'passwd': ''}defmain():

stream=BinLogStreamReader(

connection_settings=mysql_settings,

server_id=1,

blocking=True,

only_events=[DeleteRowsEvent, WriteRowsEvent, UpdateRowsEvent])for binlogevent instream:for row inbinlogevent.rows:

event= {"schema": binlogevent.schema, "table": binlogevent.table, "log_pos": binlogevent.packet.log_pos}ifisinstance(binlogevent, DeleteRowsEvent):

event["action"] = "delete"event["values"] = dict(row["values"].items())

event=dict(event.items())elifisinstance(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())elifisinstance(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= 1log_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)

我终端操作的话，那只能用第二种，结果，发现就是不行，一开始在windows下导入不成功

后面我发现是密码检测不到的问题，而我windows下的mysql本来就没有设置密码的，所以导致出错，我设置了密码之后，使用命令：

mysql -h127.0.0.1 -P3306 -uroot -p"123456"  < test.sql

成功！

我之后在linux下操作直接就成功：

mysql -h127.0.0.1 -P3306 -uroot -p'root' < test.sql

只有一个警告，去数据库里查询确实有同步到数据，但是有个问题就是，这个写全都是一次性导入的，那么如果里面有些语句有问题的话，就不一定能检测到，而使用第一种的话，需要交互，我找了个能实现终端交互的库inspect，发现还是不够实用，那么没法，用第一个方案，读取sql然后一句一句操作了。

当然如果你不用考虑单条sql有问题的话，那可以直接导入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里也没给出一个完美的解决方案：

我也就不折腾了，在docker里拉了一个5.7版本的mysql操作了，结果自然是可行的

总结

之后要想做数据库同步，就可以将所有的变更存到一个sql文件里，然后再在另一个数据库(此时这个数据库可以是另一台服务器上的)执行sql语句即可，具体怎么实现就根据场景变通即可。

根据你自己的使用场景对可选参数进行选择即可