0

My question is whether it is safe to perform multiple dumps at the same time in mysql, i.e. running:

mysql -u root -p database1 < database1.sql

mysql -u root -p database2 < database2.sql

on the same host and database server of course;

The databases will be empty when the process starts; (just created from cmd)

These are huge dumps if it matters, one file is 10G and the other 100G.

  • In most cases serial data load is more fast than parallel one. – Akina Sep 27 '18 at 8:21
  • The processes are likely to be heavy on I/O, so multiple parallel loads may saturate the I/O bandwidth. – Rick James Oct 11 '18 at 4:38
2

In general terms is safe as long as the databases are "independent". With this I mean each database has references only to itself.

For MySQL in particular VIEWs to missing tables are a problem.

Let me illustrate with an example:

create schema database1;
create table database1.mytable (id int);
create schema database2;
create view database2.myview as select * from database1.mytable;

We take separate dumps: database1.sql and database2.sql

If you try to restore them on a different server, we must pay attention to the restore order, let's say we are starting with empty schemas database1 and database2

This will work:

mysql -u root database1 < database1.sql
mysql -u root database2 < database2.sql

But this will fail:

mysql -u root -p database2 < database2.sql
mysql -u root -p database1 < database1.sql

Because the database2.myview references a table that does not exist.

TLDR: if you are running database restores in parallel and there are references between them you could get into trouble.

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.