Пробуем выгрузить базу размером в 70 Gb с удаленного сервера



mysql -h manikin.beget.ru -ureadonly -preadonly domain_statistic

mysql> show tables;
+----------------------------------------------+
| Tables_in_domain_statistic                   |
+----------------------------------------------+
| a_count_statistic                            |
| a_domain_old_count_statistic                 |
| all_stat                                     |
| api_call                                     |
| as_count_statistic                           |
| as_domain_old_count_statistic                |
| as_list                                      |
| beget_domain_as_from_count_statistic         |
| beget_domain_as_to_count_statistic           |
| beget_domain_ns_from_count_statistic         |
| beget_domain_ns_to_count_statistic           |
| beget_domain_registrant_from_count_statistic |
| beget_domain_registrant_to_count_statistic   |
| cname_count_statistic                        |
| domain                                       |
| domain_count_statistic                       |
| domain_history                               |
| domain_orders                                |
| domain_transfer_order                        |
| mx_count_statistic                           |
| ns_count_statistic                           |
| ns_domain_group_count_statistic              |
| ns_domain_old_count_statistic                |
| providers_like_statistic                     |
| registrant_count_statistic                   |
| rpki                                         |
| rpki_history                                 |
+----------------------------------------------+

time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction domain_statistic a_count_statistic > a_count_statistic2.sql
time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick domain_statistic a_count_statistic > a_count_statistic2.sql
time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=512M domain_statistic a_count_statistic > a_count_statistic2.sql
time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=512M --compress  domain_statistic a_count_statistic > a_count_statistic2.sql
time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=512M --compress --order-by-primary domain_statistic a_count_statistic > a_count_statistic2.sql

time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction domain_statistic a_count_statistic > a_count_statistic.sql

7.59 мин

time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick domain_statistic a_count_statistic > a_count_statistic2.sql

4.24 мин

time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M domain_statistic a_count_statistic > a_count_statistic3.sql

3.54 мин

time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress  domain_statistic a_count_statistic > a_count_statistic4.sql

1.36 мин

time mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary domain_statistic a_count_statistic > a_count_statistic5.sql

1.21 мин


Итого по скорости выйграли команды за 1.36 и 1.21 минуту:

mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress  domain_statistic a_count_statistic > a_count_statistic4.sql

и с сортировкой --order-by-primary

mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary domain_statistic a_count_statistic > a_count_statistic5.sql

Копируем данные

Сначала получаем структуру таблицы

mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction  -d domain_statistic > schema.sql

А теперь получаем только данные

mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic a_count_statistic > a_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic a_domain_old_count_statistic > a_domain_old_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic all_stat > all_stat.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic api_call > api_call.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic as_count_statistic > as_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic as_domain_old_count_statistic > as_domain_old_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic as_list > as_list.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic beget_domain_as_from_count_statistic > beget_domain_as_from_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic beget_domain_as_to_count_statistic > beget_domain_as_to_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic beget_domain_ns_from_count_statistic > beget_domain_ns_from_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic beget_domain_ns_to_count_statistic > beget_domain_ns_to_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic beget_domain_registrant_from_count_statistic > beget_domain_registrant_from_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic beget_domain_registrant_to_count_statistic > beget_domain_registrant_to_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic cname_count_statistic > cname_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic domain > domain.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic domain_count_statistic > domain_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic domain_history > domain_history.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic domain_orders > domain_orders.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic domain_transfer_order > domain_transfer_order.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic mx_count_statistic > mx_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic ns_count_statistic > ns_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic ns_domain_group_count_statistic > ns_domain_group_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic ns_domain_old_count_statistic > ns_domain_old_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic providers_like_statistic > providers_like_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic registrant_count_statistic > registrant_count_statistic.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic rpki > rpki.sql
mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info -d domain_statistic rpki_history > rpki_history.sql

Progress bar

Можно используя команду pv и зная размер таблицы — сделать progress bar:

mysqldump -h manikin.beget.ru -ureadonly -preadonly --single-transaction --quick --max_allowed_packet=2048M --compress --order-by-primary --no-create-info  domain_statistic a_count_statistic | pv -s 690M > a_count_statistic.sql

получить размер таблиц в БД

SELECT 
    table_name AS "Table",  
    concat(round(((data_length + index_length) / 1024 / 1024), 0), "M") as size   
FROM information_schema.TABLES  
WHERE table_schema = "domain_statistic"  
ORDER BY size DESC; 

providers_like_statistic	              814M
beget_domain_registrant_from_count_statistic	7M
domain	                                     7976M
rpki	                                        6M
cname_count_statistic	                        5M
domain_history	                            59516M
domain_orders	                              575M
beget_domain_ns_to_count_statistic	       50M
rpki_history	                                3M
ns_domain_group_count_statistic	              375M
as_count_statistic	                      330M
as_domain_old_count_statistic	              253M
as_list	                                       23M
ns_count_statistic	                     2342M
a_count_statistic	                     2117M
a_domain_old_count_statistic	             1885M
domain_transfer_order	                      154M
mx_count_statistic	                      151M
ns_domain_old_count_statistic	             1510M
registrant_count_statistic	               14M
beget_domain_ns_from_count_statistic	       12M
domain_count_statistic	                        0M
all_stat	                                0M
beget_domain_registrant_to_count_statistic	0M
beget_domain_as_from_count_statistic	        0M
api_call	                                0M
beget_domain_as_to_count_statistic	        0M

Загружаем данные

mysql -uroot domain_statistic < schema.sql

mysql -uroot domain_statistic < a_count_statistic.sql
mysql -uroot domain_statistic < a_domain_old_count_statistic.sql
mysql -uroot domain_statistic < all_stat.sql
mysql -uroot domain_statistic < api_call.sql
mysql -uroot domain_statistic < as_count_statistic.sql
mysql -uroot domain_statistic < as_domain_old_count_statistic.sql
mysql -uroot domain_statistic < as_list.sql
mysql -uroot domain_statistic < beget_domain_as_from_count_statistic.sql
mysql -uroot domain_statistic < beget_domain_as_to_count_statistic.sql
mysql -uroot domain_statistic < beget_domain_ns_from_count_statistic.sql
mysql -uroot domain_statistic < beget_domain_ns_to_count_statistic.sql
mysql -uroot domain_statistic < beget_domain_registrant_from_count_statistic.sql
mysql -uroot domain_statistic < beget_domain_registrant_to_count_statistic.sql
mysql -uroot domain_statistic < cname_count_statistic.sql
mysql -uroot domain_statistic < domain.sql
mysql -uroot domain_statistic < domain_count_statistic.sql
mysql -uroot domain_statistic < domain_history.sql
mysql -uroot domain_statistic < domain_orders.sql
mysql -uroot domain_statistic < domain_transfer_order.sql
mysql -uroot domain_statistic < mx_count_statistic.sql
mysql -uroot domain_statistic < ns_count_statistic.sql
mysql -uroot domain_statistic < ns_domain_group_count_statistic.sql
mysql -uroot domain_statistic < ns_domain_old_count_statistic.sql
mysql -uroot domain_statistic < providers_like_statistic.sql
mysql -uroot domain_statistic < registrant_count_statistic.sql
mysql -uroot domain_statistic < rpki.sql
mysql -uroot domain_statistic < rpki_history.sql

—
PS возможный метод для боле быстрой загрузки данных

cd /path/to/backup/
mkdir splits
split -n 200 database_backup.sql splits/sql_

This produced several dozen files in order, and it took about 10 minutes. The -n option told split to split each file up into 200 lines each. So the files were then named sql_aa, sql_ab, sql_ac all the way to sql_fg. Then, I did the following command using cat to pipe the files to mysql:
cd splits
cat sql_* | mysql -u root -p database_name
взято с linuxize.com/post/how-to-back-up-and-restore-mysql-databases-with-mysqldump/