Could MyDumper support stream backup and loader？

[leafonsword]

Sometimes we need backup to stream and load from stream so that backup is less, could MyDumper support stream backup and loader?

[maxbube]

Hi @leafonsword ,

I got that question often :) it won't be easy as how mydumper actually works, it will need some refactoring, i.e. the order in which diff objects are dumper, at the moment we first do data then schema so we need to swap that, and for sure there are other considerations.

The only I can tell at this time, is that I will review the effort on this and see if I can have the time to develop it.

Thanks for the feedback!

[nathanielks]

+1 here 😄

[qianxiansheng90]

+1 need this

[bartoszcisek]

@maxbube Even without switching data->schema order, this streaming would be useful. azure-cli supports only single file upload to blob storage. Streaming mydumper output directly to tar would save us 50% IO on backup server.

[Esysteme]

any update ?

[davidducos]

Hello @Esysteme,

At this moment there is no plan to implement it. I know that it is a really useful feature, but we need to consider a writer/reader object which must be handling a queue, for writes in mydumper is simpler, but for myloader it is not, as we need to stop reading from the queue when a CREATE TABLE arrives to avoid issues in the next INSERTs.

[davidducos]

I'm answering to my self, we can use mysql comments that can be parsed by myloader to "send commands", so it will be able to know when a stage finished to start with the next stage.

[davidducos]

We can use:

#include <sys/syscall.h>
syscall(SYS_gettid)

to get the thread id, as I was thinking to implement this on function write_data and as write_data can be called several times before a file is close, we need to identify the write_data per thread.

When write_data is called and streaming is being used, we are going to be enqueuing, not just the statement, it will be the thread_id

Another consideration is that there will be a new thread which is going to be reading from the

[davidducos]

I already have a version of mydumper.c which serialize the export to the stdout. I'm going to start working on the myloader.c now...

[davidducos]

Hi All,

I'm still working on this solution, as I'm still not sure how to implement it. I learned a lot on what I did on mid-April, but it was going to be very difficult to maintain during the time. I'm still thinking in the best solution.

[davidducos]

Finally, I reached to a conclusion.
On mydumper will be adding 2 options:
--stream: it will indicate that the files created needs to be streamed through the STDOUT
--no-delete: the files created in the backup directory will not be deleted.

When we use --stream, the files are going to be created locally and they will be also streamed, by default, they will be deleted after transferred had completed. We only need to add a stream thread that will receive file names which are going to be streamed. verbose option will be only available if -L is set.

myloader will implement a thread to read the stream and creates the file locally. It will have the same 2 options:
--stream: it will indicate that the files are coming from the stream
--no-delete: the files created in the backup directory will not be deleted.

We need to consider if --stream and --no-delete is used, the connection to the database might not be needed, as the user might want to expand the backup files only.

[cyford]

Would be nice if we can stream directly into mysql without the need to create the files... It seems all files are completed before import begins

[davidducos]

Hi @cyford,
I thought about that but taking into account that backup takes less time than restore and we need to use FTWRL to get a consistent backup, I decided to implement a file based buffer. You can check https://www.percona.com/blog/backup-and-restore-with-mydumper-on-docker/ which might help on what you are trying to accomplish