How can I make a huge (140Gb) sql dump of a MariaDB database?

Question

I need to make a dump to my database with approx. 140 Gb. Can I just run the command to the whole database? Do you know if there are some limits for the dumps?

Dumping remotely. I need this dump to upload it in a new instance in the cloud.

We do not have access to the large database yet.

Answer 1

Remotely. I need this dump to upload it in a new instance in the cloud.

There is essentially no limit on dump size. Be aware that the dump, if materialized on disk, may be noticeably bigger (or smaller) than the disk footprint on the old_server.

If you can run a command on the new instance, then this would be optimal for a 'logical' dump:

mysqldump -h old_instance ... | mysql 

That avoids the extra time and CPU for zipping/unzipping at the cost of extra network time. (YMMV.) And it avoids writing the 140GB to disk.

If you cannot run a script on the new instance but can run mysqldump on a third server (your laptop?), then do something like this on it:

mysqldump -h old_server ... | mysql -h new_server ...

This requires no shell access to either remote server, but does depend on network bandwidth in both directions to the third_server.

Answer 2

If copying to the same or newer MariaDB version you can copy the entire data directory while MariaDB is stopped especially after a clean shutdown. - danblack

---

mysqldump database | gzip -c > dump.sql.gz - Barmar