2

Origin server: 1 table - InnoDB -> 700gb - 1.5 billion records (Free disk space: 200gb)

Destination server is in the same network.

The destination server will have a lot of free space, the origin only has 200gb free.

1 - The table has 1.5 billion records, so querying line by line would not be the best way.

2 - Possibly the only viable way I found at the moment was to copy the IBD file and try to restore it using TABLESPACE, according to the instructions in this link: https://community.spiceworks.com/t/recover-mysql-database-from-frm-and-ibd-files/1014394

With MariaBackup I would be able to save the file at the source if I had free space. Unfortunately, it is not possible to send the file via MariaBackup directly to the origin server over the network, right?

Is there any better way to export besides these two mentioned?

Thanks a lot. =)

Improve this question
2
  • 1
    Maybe pipe the output to ssh then pipe it back in on the other side? But I don't think you can do that using mariabackup because you need to use --prepare first. Maybe rsync?
    – Charlieface
    Commented May 1 at 16:10
  • Destination server is in the same network. Think about INSERT INTO federated_table SELECT * FROM local_table; - see mariadb.com/kb/en/federatedx-storage-engine
    – Akina
    Commented May 1 at 16:14

3 Answers 3

Reset to default
4

Assuming the bandwidth is high, do this on the destination server:

$ mysqldump -u ... -p... -h source_host dbname tablename |
      mysql -u ... -p...                dbname

(If the bandwidth is low, then zipping/unzipping as Kondybas suggests might run up to 3x faster than without zip.)

Improve this answer
5
  • Also gzip is a preferred compression tool over xz or bzip2 as far as it combines pretty good compression factor along with a very good speed of compression.
    – Kondybas
    Commented May 1 at 19:39
  • This works if one of the two hosts allows remote connections from other hosts on the same network. Otherwise Kondybas's answer using ssh is the right option.
    – jcaron
    Commented May 2 at 13:59
  • Bandwidth is good, the servers are in the same rack and will have VLAN between them in the datacenter, 1Gbps dedicated connection between them. So with this bandwidth, this solution without compression will be faster and better and the Kondybas solution?
    – Cloud Strifes
    Commented May 2 at 19:38
  • @CloudStrifes Your mileage may vary depending on the data content and the CPU power available, but for general data the speed difference will not be orders of magnitude.
    – fraxinus
    Commented May 2 at 22:27
  • 1
    @CloudStrifes -- same rack & 1Gbps -- skip the compression; it will add too much overhead. The ssh is for a different reason (as Kondybas points out).
    – Rick James
    Commented May 2 at 23:22
6

You can stream your dump directly from one host to another with no temporary files on disk at all:

mysqldump -u user -ppass dbname tablename \
   | gzip --stdout                        \
   | ssh [email protected] "gunzip --stdout | mysql -u user -ppass dbname"
Improve this answer
4
  • 2
    If zstd is available, that'll probably result in faster compression. Using zstdmt (or zstd -T0) would also allow parallel compression if there's enough CPU cycles to spare. pigz is also another parallel compression alternative that has been used a lot for transferring backups.
    – markusjm
    Commented May 2 at 6:25
  • @markusjm Unfortunately pigz (and I suspect any other multithreaded compressor) can't compress streams in parallel efficiently. Just because streams are of sequential nature and cannot be read in parts at the same time.
    – Kondybas
    Commented May 2 at 13:29
  • Probably easier to use ssh's -C option to compress and decompress on the fly rather than piping through gzip on both sides.
    – jcaron
    Commented May 2 at 13:57
  • 1
    @Kondybas pigz works with streams pretty well, as long as you feed it with data fast enough. For this very use case, it is better to limit it to (number of available cores)-2 or -1 in order to have enough CPU bandwidth for ssh.
    – fraxinus
    Commented May 2 at 15:21
1

You can, certainly in Linux/UNIX, mount a network filesystem from your target system - say, your DB is on src and you want to copy to tgt. In linux you can use NFS (install NFS server on tgt, NFS client on src) and do:

src $ sudo mount tgt:/exported/directory /your/local/mountpoint

See, for example, Network File System (NFS) - this works on MacOS too, I believe. In windows, you have the map network drive feature, which also works with linux, but is somewhat harder to set up.

And, if you are in the cloud, you can attach a new, larger disk to the source, dump your data, detach it and attach it to the target. That is usually faster than copying over the network.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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