Menu
I am trying to understand how mysqldump works:
if I execute mysqldump on my pc and connect to a remote server:
mysqldump -u mark -h 34.32.23.23 -pxxx --quick | gzip > dump.sql.gz will the server compress it and send it over to me as gzip or will my computer receive all the data first and then compress it?
Because I have a very large remote db to export, and I would like to know the fastest way to do it over a network!
303
yes, it reduces a lot data transfer! But what I wanted to understand was: if the mysqldump caches the data on my pc or on the remote server.. From what I understood it does transfer all the data on my pc first.
To use the WinRAR command-line utility, we set the PATH variable on the database server. Create a T-SQL script to generate a compressed and copy_only backup of the database. Using WinRAR command-line utility, compress and divide the backup file in multiple WinRAR files and copy them to the network location.
Summary (for best dump restore performance):Set innodb_buffer_pool_size to half of RAM. Set innodb_log_file_size to 1G. Set innodb_flush_log_at_trx_commit to 0. Disabling innodb_double_write recommended only for fastest performance, it should be enabled on production.
You should make use of ssh + scp,
because the dump on localhost is faster,
and you only need to scp over the gzip (lesser network overhead)
likely you can do this
ssh [email protected] "mysqldump -u mark -h localhost -pxxx --quick | gzip > /tmp/dump.sql.gz" scp [email protected]:/tmp/dump.sql.gz . (optional directory of /tmp, should be change to whatever directory you comfortable with)
52
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
