In mysql (8.0), there is a table with over 10 million rows, I want to dump it as bulk insert sql.

I wish each insert sql contains maybe 1k or 10k rows, so that could execute the sql file quickly, overall.

So how to do such a dump ?


That's what mysqldump does by default, or at least with some option. Look at the output to confirm.

It will build INSERT statements will hundreds or thousands of rows in each. This is optimal. (A single INSERT with 10M rows is probably not possible.)

| improve this answer | |
  • @EricWang - seems like -opt or some subset of it invokes that. – Rick James Oct 19 at 4:34
  • I did more test, basically you're right, though the bulk size is about 5000. I dumped 100k records, there are 20 insert statements. Wondering is there anyway to control the bulk size. – Eric Wang Oct 19 at 6:28
  • 1
    Yes, there is a setting. But I would not change it. The difference between 500 and 5K or 5K and 50K will probably be less than 1% in speed difference. At some point (probably before 100K, you will hit some "hard" limit. (It may be max_allowed_packet.) – Rick James Oct 19 at 21:53

mysqldump has a"WJERE" clause in which can specify which data or number you want

mysqldump --opt --where="1 limit 1000000" database table > yourdumplimited.sql

or you can LIMIT byy time

 --where "your_date BETWEEN '2020-01-01' AND '2020-06-30'"

You can divide your table into fragmentss which you need.

| improve this answer | |
  • So, I just run the 2nd command N times with different params, to get N small sql files? That might work I guess. – Eric Wang Oct 10 at 12:35
  • 1
    as yuu don't declare how your table looks like, i gave you some examples hw you would get to do it, date columns is one otion most take thier id column for that an d you can use it as normal where clause , sp oyu can combine columns. you have to give it a try – nbk Oct 10 at 12:39
  • You don't need to do the chunking; mysqldump will do it for you. – Rick James Oct 18 at 21:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.