В настоящее время я использую mysqldump для создания резервных копий, что медленно, но нормально. Большая проблема - это восстановление базы данных, что занимает несколько дней. Дамп составляет около 7 ГБ в сжатом виде, поэтому это не крошечная база данных, но она не должна выходить за пределы разумного диапазона с mysql.
Итак, какие у меня другие варианты? Что-то вроде mysqlhotcopy было бы идеально.
Взгляните на Percona's Xtrabackup, который позволяет выполнять «горячее» резервное копирование и является совершенно бесплатным.
Если у вас есть --innodb_file_per_table включено, чем вы можете переместить файл .ibd и связанную таблицу из одной базы данных в другую, используйте оператор RENAME TABLE:
RENAME TABLE db1.tbl_name TO db2.tbl_name;
Если у вас есть «чистая» резервная копия файла .ibd, вы можете восстановить ее в той установке MySQL, из которой он был создан, следующим образом:
Выполните этот оператор ALTER TABLE, чтобы удалить текущий файл .ibd:
ALTER TABLE tbl_name DISCARD TABLESPACE;
Скопируйте резервный файл .ibd в соответствующий каталог базы данных.
Выполните этот оператор ALTER TABLE, чтобы сообщить InnoDB использовать новый файл .ibd для таблицы:
ALTER TABLE tbl_name IMPORT TABLESPACE;
В этом контексте «чистая» резервная копия файла .ibd - это резервная копия, для которой выполняются следующие требования:
В файле .ibd нет незафиксированных изменений транзакциями.
В файле .ibd нет не объединенных записей буфера вставки.
Очистка удалила все отмеченные удаления индексные записи из файла .ibd.
mysqld сбросил все измененные страницы файла .ibd из пула буферов в файл.
Вы можете создать чистую резервную копию .ibd-файла, используя следующий метод:
Остановите все действия с сервера mysqld и зафиксируйте все транзакции.
Подождите, пока SHOW ENGINE INNODB STATUS не покажет, что в базе данных нет активных транзакций, а статус основного потока InnoDB - Ожидание активности сервера. Затем вы можете сделать копию файла .ibd.
Другой метод создания чистой копии файла .ibd - использование коммерческого инструмента InnoDB Hot Backup.
Есть Инструмент горячего резервного копирования InnoDB / Oracle, что стоит денег. Я никогда им не пользовался, но предполагаю, что он делает то, что написано на банке.
Лично я Снимок LVM моих таблиц InnoDB и резервное копирование из моментального снимка - при восстановлении похоже, что произошел сбой системы в InnoDB, который проходит через обычный процесс воспроизведения журнала при запуске. Мне нравится подход к резервному копированию с поясом и браслетами, поэтому я комбинирую обычные снимки и ...rsyncс реже mysqldumps (и иногда бывает удобно иметь в наличии достаточно свежий дамп SQL).
Я подозреваю, что я знаю причину, по которой текущее восстановление занимает так много времени, вместо решения для дампа / восстановления.
У нас есть довольно большие базы данных (до 40 ГБ), и я восстановил ваш размер менее чем за час.
Одна вещь, которую mysqldump сделает для ускорения вставки во время восстановления, - это отключение индексов, выполнение всех вставок и затем применение индексов к вашим таблицам.
Если пара переменных mysql недостаточно велика, mysql скажет, что это «восстановление с помощью ключевого кеша» или что-то подобное ... это очень, очень, очень медленно.
Если правильные переменные установлены достаточно большими, будет сказано «исправить путем сортировки», что намного быстрее.
Я не могу вспомнить точные переменные, но если это звучит так, как будто это так, дайте мне знать в комментарии, и я посмотрю, смогу ли я отследить детали.
Как уже упоминал Ник, определенно стоит взглянуть на Xtrabackup от Percona.
Если у вас есть таблицы MyISAM, я бы предложил изменить их на InnoDB. Вот что я использую:
mysql -u root --password=<password> --database=db_name -B -N -e "SHOW TABLES" | awk '!/not_this_db/ && !/or_this_one/ && /^[a-z]/ {print "ALTER TABLE", $1, "ENGINE=INNODB;"}' | mysql -u root --password=<password> --database=db_name
Вы можете исключить и включить базы данных с регулярным выражением awk, например, только dbs, начинающиеся со строчной буквы в моем примере выше. Это, конечно, заблокирует таблицы во время изменения.
Затем используйте xtrabackup, чтобы скопировать всю базу данных прямо на другой сервер без блокировки каких-либо таблиц или использования слишком большого количества операций ввода-вывода диска (после настройки ключей ssh rsa):
innobackupex --throttle=500 --compress --stream=xbstream /doesntneedtoexist | ssh user@otherhost "xbstream -x -C /root/backup/"
а затем вы можете выполнить шаг применения журнала, полностью отделив его, и сэкономить дисковое пространство, ввод-вывод и ЦП на рабочем сервере.
Если вы используете InnoDB, то есть много параметров, которые вы можете настроить для повышения производительности. Если вы используете InnoDB, то в вашем случае совершенно очевидно, что ваш innodb_file_per_table очень маленький. Увеличьте размер этого параметра как минимум до 50% от общего объема оперативной памяти и проверьте время, необходимое для восстановления.
Кроме того, вы можете увеличить параметр innodb_thread_concurrency.
Также попробуйте Percona xtrabackup. Я использую его для своих ТБ Db, и это отлично работает.
Я добавил поддержку горячих копий в mysql_manager rubygem, но поддерживает только горячие копии всего каталога mysql. Он выполняет это, сначала выполняя повторяющиеся rsync без блокировок таблиц, пока продолжительность синхронизации не станет допустимой, а затем получает блокировку, используя FLUSH TABLES WITH READ LOCK перед завершением rsync. Поскольку он использует rsync, он также поддерживает удаленные хосты.
Использовать довольно просто.
Сначала установите драгоценный камень.
gem install mysql_manager
Вот как бы вы его запустили.
mysql-manager --hotcopy \
--hotcopy:data-dir /var/lib/mysql/ \
--hotcopy:backup-dir user@remote.host.com:/tmp/mysql/ \
--hotcopy:rsync-args "-av --exclude=*.err" \
--hotcopy:rsync-ttl 30 \
--db:user root \
--db:pass $MYSQL_ROOT_PASSWORD