Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Experiment with go-mysql toolkit #5

Closed
shlomi-noach opened this issue Mar 30, 2016 · 6 comments
Closed

Experiment with go-mysql toolkit #5

shlomi-noach opened this issue Mar 30, 2016 · 6 comments

Comments

@shlomi-noach
Copy link
Contributor

Easy to get up to speed with is https://github.com/siddontang/go-mysql#replication

Will check if it works with RBR and how events look like.
@shlomi-noach
Copy link
Contributor Author

HOLY 🎱 💀 🍕 🎉 😮 🏄 !
This was easy to set up and the output is utterly, utterly good:

=== RotateEvent ===
Date: 1970-01-01 01:00:00
Log position: 0
Event size: 43
Position: 4
Next log name: mysql-bin.000056

=== FormatDescriptionEvent ===
Date: 2016-03-30 11:04:08
Log position: 120
Event size: 116
Version: 4
Server version: 5.6.28-log����������������������������������������
Checksum algorithm: 1

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 313
Event size: 193
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: create table samplet(id int primary key, license int, name varchar(64), unique key license_uidx(license)) engine=innodb

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 385
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 439
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 485
Event size: 46
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:1
1:1
2:"a"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 516
Event size: 31
XID: 138011

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 588
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 642
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 713
Event size: 71
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:2
1:2
2:"extended"
--
0:3
1:3
2:"extended"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 744
Event size: 31
XID: 138013

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 816
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 870
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 926
Event size: 56
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:4
1:4
2:"transaction"

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 980
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 1036
Event size: 56
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:5
1:5
2:"transaction"

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 1090
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 1146
Event size: 56
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:6
1:6
2:"transaction"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 1177
Event size: 31
XID: 138015

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 1249
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 1303
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== UpdateRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 1352
Event size: 49
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:5
1:<nil>
2:<nil>
--
0:<nil>
1:<nil>
2:"update"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 1383
Event size: 31
XID: 138019

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 1455
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 1509
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== DeleteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 1549
Event size: 40
TableID: 74
Flags: 0
Column count: 3
Values:
--
0:2
1:<nil>
2:<nil>

=== UpdateRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 1612
Event size: 63
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:4
1:<nil>
2:<nil>
--
0:2
1:4
2:"replaced 2,4"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 1643
Event size: 31
XID: 138021

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 1715
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 1769
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 1815
Event size: 46
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:7
1:7
2:"7"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 1846
Event size: 31
XID: 138023

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 1918
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 1972
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 2018
Event size: 46
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:8
1:8
2:"8"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 2049
Event size: 31
XID: 138025

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 2121
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 2175
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== DeleteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 2220
Event size: 45
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:7
1:<nil>
2:<nil>
--
0:8
1:<nil>
2:<nil>

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 2251
Event size: 31
XID: 138027

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 2323
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 2377
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 2423
Event size: 46
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:9
1:9
2:"9"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 2454
Event size: 31
XID: 138029

=== QueryEvent ===
Date: 2016-03-30 11:04:33
Log position: 2526
Event size: 72
Slave proxy ID: 33741
Execution time: 0
Error code: 0
Schema: test
Query: BEGIN

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 2580
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== UpdateRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 2631
Event size: 51
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:9
1:<nil>
2:<nil>
--
0:<nil>
1:<nil>
2:"update 9"

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 2685
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== DeleteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 2725
Event size: 40
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:3
1:<nil>
2:<nil>

=== TableMapEvent ===
Date: 2016-03-30 11:04:33
Log position: 2779
Event size: 54
TableID: 74
Flags: 1
Schema: test
Table: samplet
Column count: 3
Column type: 
00000000  03 03 0f                                          |...|
NULL bitmap: 
00000000  06                                                |.|

=== WriteRowsEventV2 ===
Date: 2016-03-30 11:04:33
Log position: 2826
Event size: 47
TableID: 74
Flags: 1
Column count: 3
Values:
--
0:10
1:10
2:"10"

=== XIDEvent ===
Date: 2016-03-30 11:04:33
Log position: 2857
Event size: 31
XID: 138031

@shlomi-noach shlomi-noach mentioned this issue Mar 30, 2016
Merged
@shlomi-noach
Copy link
Contributor Author

Further output sample (correlates with https://github.com/github/gh-osc/blob/experimenting-go-mysql/go/binlog/testdata/mysql-bin.000070):

The output format is mostly my own dumps; this also shows what type go-mysql infers from the values.

WriteRowsEventV2
1
notbin 0:1, int32
notbin 1:1, int32
notbin 2:"a", string
notbin 3:<nil>, <nil>
---
WriteRowsEventV2
2
notbin 0:2, int32
notbin 1:2, int32
notbin 2:"extended", string
notbin 3:<nil>, <nil>
---
notbin 0:3, int32
notbin 1:3, int32
notbin 2:"extended", string
notbin 3:<nil>, <nil>
---
WriteRowsEventV2
1
notbin 0:4, int32
notbin 1:4, int32
notbin 2:"transaction", string
notbin 3:<nil>, <nil>
---
WriteRowsEventV2
1
notbin 0:5, int32
notbin 1:5, int32
notbin 2:"transaction", string
notbin 3:<nil>, <nil>
---
WriteRowsEventV2
1
notbin 0:6, int32
notbin 1:6, int32
notbin 2:"transaction", string
notbin 3:<nil>, <nil>
---
UpdateRowsEventV2
2
notbin 0:5, int32
notbin 1:5, int32
notbin 2:"transaction", string
notbin 3:<nil>, <nil>
---
notbin 0:5, int32
notbin 1:5, int32
notbin 2:"update", string
notbin 3:<nil>, <nil>
---
DeleteRowsEventV2
1
notbin 0:2, int32
notbin 1:2, int32
notbin 2:"extended", string
notbin 3:<nil>, <nil>
---
UpdateRowsEventV2
2
notbin 0:4, int32
notbin 1:4, int32
notbin 2:"transaction", string
notbin 3:<nil>, <nil>
---
notbin 0:2, int32
notbin 1:4, int32
notbin 2:"replaced 2,4", string
notbin 3:<nil>, <nil>
---
WriteRowsEventV2
1
notbin 0:7, int32
notbin 1:7, int32
notbin 2:"7", string
yesbin 3:"\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x00\x10\x00\x00\x00\x10\b\x02\x00\x00\x00\x90\x91h6\x00\x00\x00\x01sRGB\x00\xae\xce\x1c\xe9\x00\x00\x00\x04gAMA\x00\x00\xb1\x8f\v\xfca\x05\x00\x00\x00\tpHYs\x00\x00\x0e\xc3\x00\x00\x0e\xc3\x01\xc7o\xa8d\x00\x00\x00\x1eIDAT8OcP\xda\xe8C\x12b I5P\xf1\xa8\x06bBl4\x94\x06G(\x01\x00j\xc9\x1f\x10@\xf7\x96\xbd\x00\x00\x00\x00IEND\xaeB`\x82", []uint8
---
WriteRowsEventV2
1
notbin 0:8, int32
notbin 1:8, int32
notbin 2:"8", string
notbin 3:<nil>, <nil>
---
DeleteRowsEventV2
2
notbin 0:7, int32
notbin 1:7, int32
notbin 2:"7", string
yesbin 3:"\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x00\x10\x00\x00\x00\x10\b\x02\x00\x00\x00\x90\x91h6\x00\x00\x00\x01sRGB\x00\xae\xce\x1c\xe9\x00\x00\x00\x04gAMA\x00\x00\xb1\x8f\v\xfca\x05\x00\x00\x00\tpHYs\x00\x00\x0e\xc3\x00\x00\x0e\xc3\x01\xc7o\xa8d\x00\x00\x00\x1eIDAT8OcP\xda\xe8C\x12b I5P\xf1\xa8\x06bBl4\x94\x06G(\x01\x00j\xc9\x1f\x10@\xf7\x96\xbd\x00\x00\x00\x00IEND\xaeB`\x82", []uint8
---
notbin 0:8, int32
notbin 1:8, int32
notbin 2:"8", string
notbin 3:<nil>, <nil>
---
WriteRowsEventV2
1
notbin 0:9, int32
notbin 1:9, int32
notbin 2:"9", string
notbin 3:<nil>, <nil>
---
UpdateRowsEventV2
2
notbin 0:9, int32
notbin 1:9, int32
notbin 2:"9", string
notbin 3:<nil>, <nil>
---
notbin 0:9, int32
notbin 1:9, int32
notbin 2:"update 9", string
yesbin 3:"\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x00\x10\x00\x00\x00\x10\b\x02\x00\x00\x00\x90\x91h6\x00\x00\x00\x01sRGB\x00\xae\xce\x1c\xe9\x00\x00\x00\x04gAMA\x00\x00\xb1\x8f\v\xfca\x05\x00\x00\x00\tpHYs\x00\x00\x0e\xc3\x00\x00\x0e\xc3\x01\xc7o\xa8d\x00\x00\x00\x1eIDAT8OcP\xda\xe8C\x12b I5P\xf1\xa8\x06bBl4\x94\x06G(\x01\x00j\xc9\x1f\x10@\xf7\x96\xbd\x00\x00\x00\x00IEND\xaeB`\x82", []uint8
---
UpdateRowsEventV2
2
notbin 0:9, int32
notbin 1:9, int32
notbin 2:"update 9", string
yesbin 3:"\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x00\x10\x00\x00\x00\x10\b\x02\x00\x00\x00\x90\x91h6\x00\x00\x00\x01sRGB\x00\xae\xce\x1c\xe9\x00\x00\x00\x04gAMA\x00\x00\xb1\x8f\v\xfca\x05\x00\x00\x00\tpHYs\x00\x00\x0e\xc3\x00\x00\x0e\xc3\x01\xc7o\xa8d\x00\x00\x00\x1eIDAT8OcP\xda\xe8C\x12b I5P\xf1\xa8\x06bBl4\x94\x06G(\x01\x00j\xc9\x1f\x10@\xf7\x96\xbd\x00\x00\x00\x00IEND\xaeB`\x82", []uint8
---
notbin 0:9, int32
notbin 1:9, int32
notbin 2:"update 9b", string
yesbin 3:"\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x00\x10\x00\x00\x00\x10\b\x02\x00\x00\x00\x90\x91h6\x00\x00\x00\x01sRGB\x00\xae\xce\x1c\xe9\x00\x00\x00\x04gAMA\x00\x00\xb1\x8f\v\xfca\x05\x00\x00\x00\tpHYs\x00\x00\x0e\xc3\x00\x00\x0e\xc3\x01\xc7o\xa8d\x00\x00\x00\x1eIDAT8OcP\xda\xe8C\x12b I5P\xf1\xa8\x06bBl4\x94\x06G(\x01\x00j\xc9\x1f\x10@\xf7\x96\xbd\x00\x00\x00\x00IEND\xaeB`\x82", []uint8
---
DeleteRowsEventV2
1
notbin 0:3, int32
notbin 1:3, int32
notbin 2:"extended", string
notbin 3:<nil>, <nil>
---
WriteRowsEventV2
1
notbin 0:10, int32
notbin 1:10, int32
notbin 2:"10", string
notbin 3:<nil>, <nil>
---
UpdateRowsEventV2
4
notbin 0:5, int32
notbin 1:5, int32
notbin 2:"update", string
notbin 3:<nil>, <nil>
---
notbin 0:5, int32
notbin 1:5, int32
notbin 2:"update 5,6", string
notbin 3:<nil>, <nil>
---
notbin 0:6, int32
notbin 1:6, int32
notbin 2:"transaction", string
notbin 3:<nil>, <nil>
---
notbin 0:6, int32
notbin 1:6, int32
notbin 2:"update 5,6", string
notbin 3:<nil>, <nil>
---

@shlomi-noach
Copy link
Contributor Author

@ggunson given this library, which hooks up to a MySQL server and imposes as a SLAVE, then proceeds to get the binlog stream, and reads values in what seems like their original format & type, and assuming it does so well, we may choose to go the original way of applying ghost changes directly from the data retrieved by the binlog reader, as opposed to just reading the PK values and then proceeding to issue a INSERT INTO ghost SELECT * FROM original WHERE....
What do you think?

@shlomi-noach
Copy link
Contributor Author

One general thing that I liked about parsing via mysqlbinlog is that I could have flat, stale files, and just parse them locally. With go-mysql (or vitess, for that matter), I need to actually connect to a running MySQL server. This makes testing more complicated.

This was referenced Mar 31, 2016
Closed
Closed
@shlomi-noach
Copy link
Contributor Author

This is really working wonderfully.
Todo:

  • handle disconnect. The library connects to MySQL as a SLAVE and keeps getting events (blocking when there's no events in the stream, self-rotating logs -- moi fun!)
    But we need to handle the case where it disconnects. Say, after a long throttle/pause. Whatever. We need to reconnect at same position.

@shlomi-noach
Copy link
Contributor Author

This just works. I haven't seen disconnects, or else the client library auto-reconnects. I'm closing this.

cenkore pushed a commit to cenkore/gh-ost that referenced this issue Feb 23, 2021
@shlomi-noach
Merge pull request github#5 from openark/parse-alter-statement
9ccde4f 
Support a complete ALTER TABLE statement in --alter
RainbowDashy referenced this issue in RainbowDashy/gh-ost Jun 29, 2022
@RainbowDashy
feat: change logger to zap.Logger (#5)
b224353 
* Revert "chore: revert zap logger (#1)"

This reverts commit 51944d9.

* fix: log

* fix: log

* fix: wrap logger in log.go

* fix: add dummy "stack" flag

* fix: address comment

* fix: wrap error

* fix: some leftovers
RainbowDashy referenced this issue in RainbowDashy/gh-ost Jun 29, 2022
@RainbowDashy
Revert "feat: change logger to zap.Logger (#5)" (github#6)
c6a7a11 
This reverts commit b224353.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@shlomi-noach