mysqldump domain_statistic
Пробуем выгрузить базу размером в 70 Gb с удаленного сервера
mysql -h manikin.beget.ru -ureadonly -preadonly domain_statistic





Итого по скорости выйграли команды за 1.36 и 1.21 минуту:
—
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/
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-primarymysqldump -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/
Нет комментариев