Menu
I have to dump a large database over a network pipe that doesn't have that much bandwidth and other people need to use concurrently. If I try it it soaks up all the bandwidth and latency soars and everyone else gets messed up.
I'm aware of the --compress flag to mysqldump which help somewhat.
How can I do this without soaking up all the bandwidth over this connection?
Update:
The suggestion to copy a dumpfile using scp with the -l flag is a good one, but I should note that I don't have SSH access to the database server.
921
mysqlpump is the 4th fastest followed closer by mydumper when using gzip. mysqldump is the classic old-school style to perform dumps and is the slowest of the four tools. In a server with more CPUs, the potential parallelism increases, giving even more advantage to the tools that can benefit from multiple threads.
mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if the --single-transaction option is not used, and (as of MySQL 8.0. 21) PROCESS if the --no-tablespaces option is not used.
The mysqldump of all the tables (into separate files) is fairly fast, takes maybe 20 minutes. It generates about 15GB of data. The largest dumped files are in the 2GB range. When I load the data into MySQL on another box, a six-core, 8GB machine, it takes forever.
trickle?
trickle is a portable lightweight userspace bandwidth shaper
You don't mention how you are actually transffering the DB dump, but if the transfer happens over TCP/IP, trickle should work. For example, if you use nc (for example: nc -L 1234 > backup.sql) the following command will transfer the backup at no greater than 20KB/s:
mysqldump [database name] | trickle -u 20 nc backup.example.com:1234
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
