Either way, don't rely on a continuous long running connection. You need to be able to restart or retry without starting over. That's table stakes, and it makes a resumable pure streaming API look a lot like a paginated one. The hard bit of pagination is efficient resuming, not limiting to a page size.
Efficient pagination means having enough information on each subsequent request to pick up reliably and efficiently from where you left off, e.g. using a sequentially allocated indexed ID on the root table (to which other data is joined).
Sometimes the database needs convincing to use the right index of you have joins, as DBs are generally tuned for extracting a handful of rows at a time. I've had more reliable success (over a multi-year maintenance timeline) by doing a single paginated query over a root table and doing separate queries to get the joined data, using a page of root IDs at a time for the joins. Doing the join in code also gives leeway for parallelization of the data load across cores (MySQL queries are single threaded), or even across replicas.