[proposal] use SQL to manage the TiDB-Binlog toolset

[WangXiangUSTC]

Feature Request

Support some SQLs to manage the TiDB-Binlog toolset.

Background

1. Now we don't know whether a TiDB instance enable the binlog, we need check the TiDB's config file, or look the config information in TiDB's log. It is inconvenience.

2. We must restart the TiDB instance if we need enable or close the TiDB's binlog, it is inconvenience too.

3. If we want check the pump and drainer's status, we need use another tool binlogctl, and the command of binlogctl is complicated, not easy to use.

Feature

View status of binlog and TiDB-Binlog toolset

1. Check binlog is enable or not

Add a variable named log_bin, just same with MySQL.

mysql> show variables like '%log_bin%';
+----------------+--------+
| Variable_name   | Value    |
+----------------+--------+
| log_bin                | ON        |
+----------------+--------+

2. View Pump's status

Support SQL show pump status

mysql> show pump status;
+-------------+---------------------+------------+------------------------------+-----------------------------+
|  NodeID   |      Address       |   State     |    Max_Commit_Ts      |     Update_Time         |
+-------------+---------------------+------------+------------------------------+-----------------------------+
|   pump1   | 127.0.0.1:8249 | Online     | 405808620969656321| 2019-01-01 01:01:01 |
+-------------+---------------------+------------+------------------------------+-----------------------------+
|   pump2   | 127.0.0.2:8249 | Online     | 405808620969656322| 2019-01-01 01:01:02 |
+-------------+---------------------+------------+------------------------------+-----------------------------+

3. View Drainer's status

Support SQL show drainer status

mysql> show drainer status;
+-------------+---------------------+------------+------------------------------+-----------------------------+
|  NodeID   |      Address       |   State     |    Max_Commit_Ts      |     Update_Time         |
+-------------+---------------------+------------+------------------------------+-----------------------------+
|   drainer1  | 127.0.0.1:8250 | Online    | 405808620969656321| 2019-01-01 01:01:01 |
+-------------+---------------------+------------+------------------------------+-----------------------------+
|  drainer2  | 127.0.0.2:8250 | Online     | 405808620969656322| 2019-01-01 01:01:02 |
+-------------+---------------------+------------+------------------------------+-----------------------------+

Manage binlog and TiDB-Binlog toolset

4. Close binlog

Add a new variable log_bin, if set this value to 0, will close the binlog. The scope can be global, server or session.

set log_bin = 0;

5. Enable binlog

Use the variable above, set this value to 1 to enable binlog.

set log_bin = 1;

6. Change the write binlog's strategy in pump client

Add a new variable named binlog_strategy, the value can be range, hash and so on.
The scope can be global to server.

set binlog_strategy = "range";

7. Update Pump's state

Add SQL looks like below:

change pump to pump_state='paused' for NodeID "127.0.0.1:8249";

8. Update Drainer's state

Add SQL looks like below:

change drainer to drainer_state='paused' for NodeID "127.0.0.1:8250";

TODO List

1. Support variable log_bin

2. Support SQL show pump status and show drainer status

just like binlogctl, get pump and drainer status in PD, reference: binlogctl

3. Support close or enable binlog by set log_bin

4. Support change the write binlog's strategy in pump client

pump client's code reference: pump client

5. Support update Pump or Drainer's status

just like binlogctl, if Pump or Drainer is alive, send http request to Pump/Drainer, otherwise update the status saved in PD directly.

[morgo]

On (4), MySQL does not support global scope for this because it is dangerous. It would be better not to encourage the syntax, even if TiDB behaves differently.

May I recommend either:
a) Modeling based on the command start slave? So for example 'start binlog', 'stop binlog'.
b) Using the name tidb_binlog instead.

There are security issues with letting a user disable binlogging for their session too. Since the toolchain supports filtering downstream, we could maybe also propose that the tidb_binlog setting is only global? I am open to feedback here.

For commands (7) and (8) these don't quite feel natural to me (it's not namespaced). Maybe it could be modeled on the syntax for replication channels in MySQL 5.7+?

[WangXiangUSTC]

thanks for your suggestions, I will take a look and update this proposal. @morgo

[IANTHEREAL]

I perfer the tidb_binlog setting is only global， it would simplify something (e.g. maybe we don't need run-ddl any more). Or we can imagine, what scenario would we need to turn off tidb_binlog of one tidb server/session in TiDB cluster?

[WangXiangUSTC]

I think the global scope is necessary for tidb_binlog, and we can also support the session and server scope. we need set some limit to avoid some error, for example ddl must generate binlog in all scope type, and we need notice user must be very careful when change the tidb_binlog's scope. @morgo @GregoryIan

[WangXiangUSTC]

hi morgo, could you please provide a example of sql for update pump's state. we have several pumps, every pump have a unique nodeID, and the nodeID is set by user. @morgo

[IANTHEREAL]

we can also support the session and server scope. we need set some limit to avoid some error. is it necessary? give a example why we need it?

[WangXiangUSTC]

some user want ignore generate binlog in load stage @GregoryIan

[morgo]

@WangXiangUSTC I will get back to you with example syntax early next week. I want to run it by some users at FOSDEM.

In regards to if session should be supported, I would say we need to understand the use case. For an example in MySQL that really increases complexity: you can change the character set on a per column, table, schema or global basis. There is no strong use case for this, and it makes debugging very hard!

[WangXiangUSTC]

@morgo thanks
ok, we can only support global scope for open/close binlog now.

[morgo]

Okay, I have the following feedback:

• For changing the global value, it should be log_bin (not sql_log_bin). It is safe to use the MySQL name log_bin as the purpose is identical. There would just be one difference in that this variable is read-only in MySQL. (If you don't use the MySQL name, it gets confusing - because this variable exists in TiDB, and you would have to choose what to display it as.)

• For changing on a per-session basis, the use case is that you might be restoring a schema, and not want to generate binary log events. You don't want to disable globally, because there are other schemas which you want to keep logging which are unrelated (i.e. the database server has multiple tenants).

(If you want to implement per-session at a later point in time, that's fine too.)

[WangXiangUSTC]

1. I have already use sql set tidb_binlog = 0; and set tidb_binlog = 1; to close or open binlog, is it necessary to change tidb_binlog to log_bin?
2. we can support scope global firstly. And if need support scope session, we can do it later
   @morgo

[morgo]

1. I have already use sql set tidb_binlog = 0; and set tidb_binlog = 1; to close or open binlog, is it necessary to change tidb_binlog to log_bin?

I am slowly trying to fix variables so they no longer show dummy data (it creates issues for users who are trying to debug problems). So we would need to at least alias log_bin to tidb_binlog. Aliases are good to help upgrades, but they are not good for usability since they need to be explained and documented (which is the preferred way? is there a difference which option I use?)

2. we can support scope global firstly. And if need support scope session, we can do it later
   @morgo

Sounds good.

[WangXiangUSTC]

thanks for your suggestion, I already updated this proposal @morgo

[aliiohs]

i want to implements the frist one int todo list.

[yangwenmai]

I want to impl the 3rd one in todo list.

• 3.Support close or enable binlog by set log_bin

[WangXiangUSTC]

@aliiohs @yangwenmai thanks

[caohe]

I'd like to implement the 2nd one.

2. Support SQL show pump status and show drainer status

[WangXiangUSTC]

@caohe thanks

[aliiohs]

I want to impl the 5rd one in todo list.

[qxhy123]

I'd like to impl the 4th：
4. Support change the write binlog's strategy in pump client

[WangXiangUSTC]

@aliiohs @qxhy123 thanks

[morgo]

One more feature request: For any SHOW command we add, we should also add an information_schema table. It is quite quick to do, and helps users join against the tables.

[morgo]

I showed this to a MySQL user today, and they suggested that this command is not MySQL-like:

change drainer to drainer_state='paused' for NodeID "127.0.0.1:8250";

It should be modeled on stop slave:

STOP DRAINER for NodeID "127.0.0.1:8250";

[ghost]

@WangXiangUSTC can this issue be closed?

[WangXiangUSTC]

@nullnotnil OK