Как я могу оптимизировать mysqldump большой базы данных?

У меня есть приложение symfony с базой данных InnoDB, которая составляет ~ 2 ГБ с 57 таблицами. Большая часть базы данных находится в одной таблице (~ 1,2 ГБ). В настоящее время я использую mysqldump для резервного копирования базы данных в ночное время.

Из-за моего соединения в comcast, часто, если я запускаю дамп вручную, мое соединение с сервером истечет до завершения дампа, заставив меня перезапустить дамп. [В настоящее время я запускаю cron, который выполняет сброс в ночное время, это просто для дампов, которые я запускаю вручную.]

Есть ли способ ускорить дампы для проблемы с таймаутом соединения, но также ограничить время, в течение которого сервер занят этим процессом?

Кстати, я сейчас работаю над уменьшением размера общей базы данных для решения этой проблемы.

157 голосов | спросил Patrick 4 Jam1000000amTue, 04 Jan 2011 00:13:48 +030011 2011, 00:13:48

7 ответов


121

Основным узким местом в дампе, таким образом, является ввод-вывод. Вы читаете загрузку данных и записываете их снова. Вы можете ускорить это несколькими способами:

  • Убедитесь, что ваш выход идет на другой диск (диски), чем тот, на котором хранятся файлы базы данных. Это будет иметь огромное значение для вращающихся дисков, поскольку головки дисков не будут постоянно переключаться между местоположением считывается и записывается место.
  • Выход mysqldump будет очень сжимаемым, поэтому, если вы не можете отделить вывод от входа, как упомянуто выше, вывести его через gzip или аналогичный. Это уменьшит количество выполняемых операций записи (так что уменьшите общую нагрузку ввода-вывода и количество движения головы) за счет некоторого времени процессора (что может быть слишком много запасных в это время). Кроме того, (а также вместо сжатия) передают выходные данные через служебную программу (например, pv ), которая поддерживает большие буферов записи, чтобы группировать блоки, записанные на диски вместе, еще раз, чтобы уменьшить эффект латентности движения головы - это будет иметь большое значение, если использовать опцию --quick, чтобы уменьшить влияние резервной копии оперативной памяти больших таблиц).
  • Выполняйте резервный процесс только тогда, когда нагрузка IO в противном случае будет низкой.

Возможно, вы исправляете неправильную проблему: вместо этого может быть проще решить проблему с соединением (хотя уменьшение нагрузки ввода-вывода, наложенной вашими резервными копиями, поможет уменьшить эффект, который у вас есть у других пользователей, поэтому стоит попробовать все равно) , Можете ли вы запустить резервное копирование вручную через экран (или аналогичные инструменты, такие как tmux )? Таким образом, если ваше соединение с сервером падает, вы можете просто повторно подключиться и подключиться к сеансу screen без прерывания процессов.

Если вы отправляете данные непосредственно по соединению (т. е. вы запускаете mysqldump на вашем локальном компьютере в отношении удаленной базы данных, поэтому дамп появляется локально), вам может быть лучше сначала запустить дамп на сервере, сжимая при необходимости , затем перенося данные по сети с помощью инструмента (например, rsync), который поддерживает частичные передачи, поэтому вы можете возобновить передачу (вместо перезапуска), если прерывание соединения прерывает его.

Как часть вашего «уменьшения размера общей базы данных для решения этой проблемы», я бы предположил, что большой фрагмент ваших данных не изменяется. Возможно, вы сможете перенести большую часть 1.2Gb из этой основной таблицы в другую и удалить ее из тех, которые копируются вызовом mysqldump. Вам не нужно делать резервные копии этих данных каждый раз, если они никогда не меняются. Разделение данных между таблицами и базами данных таким образом обычно называется разделением данных, а также позволяет распространять данные и нагрузку ввода-вывода на несколько дисков. Высокопроизводительная база данных построена для поддержки автоматического разбиения на разделы, хотя в mysql вам, вероятно, придется вручную это сделать и изменить свой уровень доступа к данным для его учета.

Отклонение от темы для этого сайта (так что вам, вероятно, следует перейти к ServerFault или SuperUser, чтобы узнать, нужно ли вам больше деталей): Если вы, кажется, теряете соединения из-за неактивности, проверьте параметры на вашем SSH-сервере и SSH чтобы убедиться, что пакеты keep-alive включены и отправляются достаточно часто. Если просмотр падает, даже если соединение активно, вы также можете попробовать использовать OpenVPN или аналогично обернуть соединение - он должен обрабатывать короткое падение, даже полное падение, если все ваше соединение не работает на несколько секунд, так что клиент SSH и сервер не замечает.

ответил David Spillett 4 Jam1000000amTue, 04 Jan 2011 00:36:50 +030011 2011, 00:36:50
105

ВХОДИТЬ В РЕЗУЛЬТАТЫ РЕЗУЛЬТАТОВ С mysqldump

IMHO Выполнение резервных копий стало более художественной формой, если вы знаете, как к ней подойти

У вас есть опции

Вариант 1: mysqldump для всего экземпляра mysql

Это самый простой, без проблем !!!

mysqldump -h... -u... -p... --hex-blob --routines --triggers --all-databases | gzip > MySQLData.sql.gz

Все написанное в одном файле: структуры таблиц, индексы, триггеры, хранимые процедуры, пользователи, зашифрованные пароли. Другие опции mysqldump могут также экспортировать разные стили команд INSERT, файл журнала и координат позиции из двоичных журналов, варианты создания базы данных, частичные данные (опция «где угодно») и т. Д.

Вариант 2: отдельные базы данных mysqldump в отдельные файлы данных

Начните с создания списка баз данных (2 метода для этого)

Техника 1

mysql -h... -u... -p... -A --skip-column-names -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Техника 2

mysql -h... -u... -p... -A --skip-column-names -e"SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfDatabases.txt

Техника 1 - самый быстрый путь. Техника 2 самая надежная и безопасная. Техника 2 лучше, потому что иногда пользователи создают папки для общих целей в /var /lib /mysql (datadir), которые не связаны с базой данных. Информационная_схема зарегистрировала бы эту папку в качестве базы данных в таблице information_schema.schemata. Техника 2 будет обходить папки, которые не содержат данных mysql.

После того, как вы скомпилируете список баз данных, вы можете перейти к циклу через список и mysqldump, хотя бы параллельно, если это необходимо.

for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

Если слишком много баз данных запускается за один раз, параллельно дамп их 10 за раз:

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DB in `cat ListOfDatabases.txt`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Вариант 3: отдельные таблицы mysqldump в отдельные файлы данных

Начните с создания списка таблиц

mysql -h... -u... -p... -A --skip-column-names -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')" > ListOfTables.txt

Затем выгрузите все таблицы в группы по 10

COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump -h... -u... -p... --hex-blob --triggers ${DB} ${TB} | gzip > ${DB}_${TB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Вариант 4: ИСПОЛЬЗОВАТЬ ВАШЕ ВООБРАЖЕНИЕ

Попробуйте варианты вышеупомянутых опций плюс методы для чистых снимков

Примеры

  1. Закажите список таблиц по размеру каждой таблицы по возрастанию или убыванию.
  2. Используя отдельный процесс, запустите «FLUSH TABLES WITH READ LOCK: SELECT SLEEP (86400)» перед запуском mysqldumps. Убейте этот процесс после завершения mysqldumps. Это полезно, если база данных содержит как InnoDB, так и MyISAM
  3. Сохраните mysqldumps в датированных папках и покрутите старые папки резервного копирования.
  4. Загрузка всего экземпляра mysqldumps в автономные серверы.

CAVEAT

Только вариант 1 приносит все. Недостатком является то, что mysqldumps, созданный таким образом, может быть перезагружен только в ту же версию выпуска mysql, что и mysqldump. Другими словами, mysqldump из базы данных MySQL 5.0 не может быть загружен в версии 5.1 или 5.5. Причина ? Схема mysql полностью отличается от основных выпусков.

Варианты 2 и 3 не включают сохранение имен пользователей и паролей.

Вот общий способ сброса SQL-грантов для пользователей, которые являются доступными и более портативными.

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -h... -u... -p... --skip-column-names -A | sed 's/$/;/g' > MySQLGrants.sql

Вариант 3 не сохраняет хранимые процедуры, поэтому вы можете выполнитьСледующий

mysqldump -h... -u... -p... --no-data --no-create-info --routines > MySQLStoredProcedures.sql &

Еще один момент, который следует отметить, касается InnoDB. Если у вас есть большой пул буферов InnoDB, имеет смысл очистить его как можно лучше до выполнения любых резервных копий. В противном случае MySQL расходует таблицы сброса времени с оставшейся грязной страницей из пула буферов. Вот что я предлагаю:

Примерно за 1 час до выполнения резервного копирования выполните эту команду SQL

SET GLOBAL innodb_max_dirty_pages_pct = 0;

В MySQL 5.5 по умолчанию innodb_max_dirty_pages_pct равно 75. В MySQL 5.1 и обратно по умолчанию innodb_max_dirty_pages_pct равно 90. Установив innodb_max_dirty_pages_pct равным 0, это ускорит очистку грязных страниц на диске. Это предотвратит или, по крайней мере, уменьшит влияние очистки любых неполных двухфазных фиксаций данных InnoDB до выполнения любого mysqldump против любых таблиц InnoDB.

ЗАКЛЮЧИТЕЛЬНОЕ СЛОВО НА mysqldump

Большинство людей уклоняются от mysqldump в пользу других инструментов, и эти инструменты действительно хороши.

Такие инструменты включают

  1. MAATKIT (параллельный dump / восстановить скрипты из Percona [Устаревшие, но большие])
  2. XtraBackup (резервное копирование моментального снимка TopNotch из Percona)
  3. CDP R1Soft ( Опция модуля MySQL , которая моментально отображает моментальные снимки)
  4. MySQL Enterprise Backup (ранее InnoDB Hot Backups [коммерческий])

Если у вас есть дух истинного MySQL DBA, вы можете охватить mysqldump и получить полное мастерство над ним, которое может быть достигнуто. Пусть все ваши резервные копии станут отражением ваших навыков как DBA MySQL .

ответил RolandoMySQLDBA 17 AMpSun, 17 Apr 2011 07:31:08 +040031Sunday 2011, 07:31:08
16

Посмотрите на master-репликацию MySQL на ведомый. Он позволяет клонировать базу данных мастера на другой сервер базы данных с той же базой данных. Это включает в себя идентификаторы ведущего и ведомого. Ведомое устройство является точной копией основного сервера базы данных и его баз данных. Между мастерами (ов) и подчиненными (-ами) может быть одно-одно, одно-многое, много-одно.

Ведомое устройство непрерывно считывает двоичный журнал в главном (журнал bin хранит запросы, написанные на главном сервере базы данных) и получает вход на сервер подчиненной базы данных. (это означает, что ваша основная база данных не будет затронута вообще)

Хорошей новостью является то, что она не будет сильно влиять на ваш сервер MySQL, поскольку вы не заметите каких-либо простоев или медленных ответов на запросы. Мы используем его для баз данных 10 ГБ, и он работает как шарм без простоя.

Репликация MySQL на одном компьютере

ответил poelinca 4 Jam1000000amTue, 04 Jan 2011 00:18:21 +030011 2011, 00:18:21
14

План A: Смотрите также Xtrabackup из Percona. Это позволяет онлайн-резервное копирование InnoDB без существенной блокировки.

План B: Ведомый может быть остановлен, и вы можете сделать последовательную резервную копию любым из нескольких способов (файлы копирования, mysqldump, xtrabackup и т. Д.)

План C: Снимок LVM. После некоторой критической настройки время простоя для резервного копирования составляет менее минуты, независимо от размера базы данных. Вы останавливаете mysqld, делаете снимок, перезапускаете mysqld, а затем копируете моментальный снимок. Последний шаг может занять много времени, но MySQL не работает.

План D: Снимок ведомого - нулевое время простоя.

ответил Rick James 21 Mayam11 2011, 00:55:55
13

Несколько пунктов администрирования: вы подключаетесь к ftp или находитесь в ssh'ed, и он умирает? Если ssh, то обязательно используйте screen , чтобы возобновить работу после сбоя в comcast. Если ftp, то убедитесь, что вы сжимаете его /tar перед отправкой.

Также попробуйте параметр -opt или --quick

- опция Эта опция включает набор дополнительных параметров, чтобы сделать операции сброса и перезагрузки более эффективными. В частности, это эквивалентно совместному использованию параметров -add-drop-table, -add-locks, -all, -quick, -extended-insert, -lock-tables и -disable-keys. Обратите внимание, что эта опция делает вывод менее переносимым и с меньшей вероятностью понимается другими системами баз данных.

- быстрый Этот параметр указывает mysqldump написать вывод дампа, когда он считывает каждую строку с сервера, что может быть полезно для больших таблиц. По умолчанию mysqldump считывает все строки из таблицы в память перед записью вывода; для больших таблиц это требует больших объемов памяти, что может привести к сбою дампа.

ответил David Hall 4 Jam1000000amTue, 04 Jan 2011 00:23:47 +030011 2011, 00:23:47
4

У меня были проблемы с таймаутами во время дампов больших баз данных. Наконец, я решил, что, отправив отдельные команды для каждой таблицы в db и добавив все в один файл следующим образом:

TABLES=`mysql -u $USER -p$PWD -Bse 'show tables' $DB`
for TABLE in $TABLES
do
    mysqldump -u $USER -p$PWD $DB $TABLE >> dump.sql
done
ответил Patrick Heck 5 J000000Tuesday11 2011, 20:34:36
2

Я думаю, речь идет о том, как быстрее восстанавливать файлы созданных дампов mysqldump, а не другое решение для резервного копирования.

Один из способов сделать это - создать группы таблиц в вашей схеме и создать отдельного пользователя БД для каждой группы, а затем, наконец, использовать разрешения MySQL, чтобы запретить вставки таблиц для использования всех, кроме одного пользователя БД .

Это проверенная, быстрая, почти параллельная техника, но не на 100% уверен, сколько времени потребуется для восстановления с больших отвалов, таких как 500G или около того. Но по моему скромному мнению, вам нужно что-то параллельное. Ознакомьтесь с приведенной ниже ссылкой.

[Быстрое параллельное восстановление из SQL-дампов (mysqldump) для MySQL] [1]

http://geeksww.com/tutorials/database_management_systems/mysql/tips_and_tricks /fast_parallel_restore_from_sql_dumps_mysqldump_for_mysql.php

«Быстрое параллельное восстановление из SQL-дампов (mysqldump) для MySQL»

ответил syed 4 FebruaryEurope/MoscowbWed, 04 Feb 2015 06:56:29 +0300000000amWed, 04 Feb 2015 06:56:29 +030015 2015, 06:56:29

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132