-
Резервное копирование и восстановление MySQL Суббота, 30 сентября 2006 г., 14:21 — yejr
Резервное копирование и восстановление MySQL
Автор/переводчик: Е Цзиньжун (электронная почта: ), Источник: http://imysql.cn При перепечатке укажите автора/переводчика. Нарушители будут привлечены к ответственности.
Дата: 1 октября 2006 г.
В этой статье обсуждается механизм резервного копирования и восстановления MySQL, а также способы обслуживания таблиц данных, включая два основных типа таблиц: MyISAM и Innodb. Версия MySQL, разработанная в этой статье, — 5.0.22.
В настоящее время MySQL поддерживает следующие бесплатные инструменты резервного копирования: mysqldump, mysqlhotcopy. Вы также можете использовать синтаксис SQL для резервного копирования: BACKUP TABLE или SELECT INTO OUTFILE, резервное копирование двоичных журналов (binlog) или прямое копирование файлов данных и связанных файлов конфигурации. Таблицы MyISAM сохраняются в виде файлов, поэтому их резервное копирование относительно легко выполнить. Можно использовать некоторые из упомянутых выше методов. Все таблицы в Innodb хранятся в одном файле данных ibdata1 (это также может быть несколько файлов или независимые файлы табличного пространства), резервное копирование которого относительно сложно выполнить. Бесплатные решения могут заключаться в копировании файлов данных и резервном копировании binlog. Или используйте mysqldump. .
1.mysqldump
1.1 Резервное копирование
mysqldump использует механизм резервного копирования на уровне SQL. Он экспортирует таблицы данных в файлы сценариев SQL. Это также наиболее часто используемый метод резервного копирования.
Теперь давайте поговорим о некоторых основных параметрах mysqldump:
--совместимость=имя
Он сообщает mysqldump, с какой базой данных или более старой версией сервера MySQL будут совместимы экспортированные данные. Значения могут быть ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options и т. д. Чтобы использовать несколько значений, разделяйте их запятыми. Конечно, это не гарантирует полной совместимости, но старается быть совместимым.
--complete-insert, -c
В экспортированных данных используется полный метод INSERT, включая имена полей, то есть все значения записываются в одну строку. Это может повысить эффективность вставки, но на это может повлиять параметр max_allowed_packet и привести к сбою вставки. Поэтому этот параметр нужно использовать с осторожностью, по крайней мере, я не рекомендую.
--default-character-set=кодировка
Укажите, какой набор символов использовать при экспорте данных. Если в таблице данных не используется набор символов Latin1 по умолчанию, этот параметр необходимо указать при экспорте, иначе после повторного импорта данных будут искажены символы.
--disable-ключи
Скажите mysqldump добавить операторы /*!40000 ALTER TABLE DISABLE KEYS */ и /*!40000 ALTER TABLE table ENABLE KEYS */; в начале и конце оператора INSERT. Это может значительно повысить скорость оператора вставки. потому что это так. Индекс перестраивается после вставки всех данных. Этот вариант подходит только для таблиц MyISAM.
--extended-insert = true|false
По умолчанию mysqldump включает режим --complete-insert, поэтому, если вы не хотите его использовать, просто используйте эту опцию и установите для нее значение false.
--hex-blob
Экспортируйте поля двоичной строки в шестнадцатеричном формате. Эту опцию необходимо использовать, если имеются двоичные данные. Затронутые типы полей — BINARY, VARBINARY и BLOB.
--lock-all-tables,-x
Прежде чем начать экспорт, отправьте запрос на блокировку всех таблиц во всех базах данных, чтобы обеспечить согласованность данных. Это глобальная блокировка чтения, которая автоматически отключается с помощью опций --single-transaction и --lock-tables.
--lock-таблицы
Он похож на --lock-all-tables, но блокирует текущую экспортируемую таблицу данных вместо одновременной блокировки всех таблиц в базе данных. Эта опция применима только к таблицам MyISAM. Если это таблица Innodb, вы можете использовать опцию --single-transaction.
--no-create-info, -t
Экспортируйте данные только без добавления оператора CREATE TABLE.
--no-data,-d
Никакие данные не экспортируются, экспортируется только структура таблицы базы данных.
--opt
Это всего лишь быстрый вариант, эквивалентный добавлению --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set- параметры кодировки. Эта опция позволяет mysqldump быстро экспортировать данные, а экспортированные данные можно быстро импортировать обратно. Эта опция включена по умолчанию, но ее можно отключить с помощью --skip-opt. Обратите внимание: если вы запустите mysqldump без указания опции --quick или --opt, весь набор результатов будет помещен в память. Проблемы могут возникнуть, если вы экспортируете большую базу данных.
--быстро,-q
Эта опция полезна при экспорте больших таблиц. Она заставляет mysqldump выводить записи, полученные по запросу сервера, напрямую, вместо того, чтобы извлекать все записи и кэшировать их в памяти.
--программы, -R
Экспортируйте хранимые процедуры и пользовательские функции.
--одна транзакция
Этот параметр отправляет оператор SQL BEGIN перед экспортом данных. BEGIN не блокирует никакие приложения и обеспечивает согласованное состояние базы данных во время экспорта. Он работает только с транзакционными таблицами, такими как InnoDB и BDB.
Этот параметр и параметр --lock-tables являются взаимоисключающими, поскольку LOCK TABLES вызывает неявное подтверждение любых ожидающих транзакций.
Для экспорта больших таблиц следует использовать опцию --quick.
--триггеры
Также экспортируйте триггеры. Эта опция включена по умолчанию, используйте --skip-triggers, чтобы отключить ее.
Подробную информацию о других параметрах см. в руководстве. Обычно для резервного копирования таблиц MyISAM я использую следующий SQL:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob -x имя_базы > имя_базы.sql
Используйте следующий SQL для резервного копирования таблиц Innodb:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --single-transaction db_name > db_name.sql
Кроме того, если вы хотите реализовать онлайн-резервное копирование, вы также можете использовать параметр --master-data следующим образом:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--single-transaction --flush-logs имя_базы_данных > имя_базы_данных.sql
Он только запрашивает таблицу блокировок в начале, затем обновляет бинлог, а затем добавляет оператор CHANGE MASTER к экспортированному файлу, чтобы указать местоположение бинлога текущей резервной копии. Если вы хотите восстановить этот файл на подчиненном устройстве, вы можете использовать его. вот так сделать это.
1.2 Восстановление Файл, резервная копия которого создана с помощью mysqldump, представляет собой сценарий SQL, который можно импортировать напрямую. Импортировать данные можно двумя способами.
Используйте клиент MySQL напрямую, например:
/usr/local/mysql/bin/mysql -uyejr -pyejr имя_базы_данных <имя_базы_данных.sql
Использование синтаксиса SOURCE на самом деле не является стандартным синтаксисом SQL, а является функцией, предоставляемой клиентом MySQL, например:
ИСТОЧНИК /tmp/имя_базы_данных.sql;
Здесь вам нужно указать абсолютный путь к файлу, и это должен быть файл, на чтение которого пользователь, работающий с mysqld (например, никто), не имеет разрешения.
2. mysqlhotcopy
2.1 Резервное копирование
mysqlhotcopy — это программа на языке PERL, первоначально написанная Тимом Бансом. Он использует LOCK TABLES, FLUSH TABLES и cp или scp для быстрого резервного копирования базы данных. Это самый быстрый способ резервного копирования базы данных или отдельной таблицы, но он может работать только на той машине, где расположены файлы базы данных (включая файлы определения таблицы данных, файлы данных и файлы индекса). mysqlhotcopy можно использовать только для резервного копирования MyISAM и будет работать только в Unix-подобных системах и системах NetWare.
mysqlhotcopy поддерживает копирование нескольких баз данных одновременно, а также поддерживает регулярные выражения. Вот несколько примеров:
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp (изменить каталог базы данных db_name
Скопировать в /tmp
Вниз)
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
имя_базы_базы_1 ... имя_базы_данных_n /tmp
root#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
имя_базы_данных./regex/ /tmp
Пожалуйста, обратитесь к руководству для более подробного использования или вызовите следующую команду, чтобы просмотреть справку по mysqlhotcopy:
perldoc /usr/local/mysql/bin/mysqlhotcopy
Обратите внимание: если вы хотите использовать mysqlhotcopy, у вас должны быть разрешения SELECT и RELOAD (для выполнения FLUSH TABLES), а также разрешение на чтение каталога datadir/db_name.
2.2 Восстановление
Mysqlhotcopy создает резервную копию всего каталога базы данных. При использовании его можно скопировать непосредственно в каталог данных, указанный mysqld (здесь это /usr/local/mysql/data/). как в следующем примере:
root#cp -rf имя_базы_данных /usr/local/mysql/data/
root#chown -R никто:никто /usr/local/mysql/data/ (измените владельца каталога db_name на mysqld
работающий пользователь)
3. Резервное копирование синтаксиса SQL
3.1 Резервное копирование
Синтаксис BACKUP TABLE на самом деле аналогичен принципу работы mysqlhotcopy. Они одновременно блокируют таблицу и затем копируют файл данных. С его помощью можно добиться онлайн-резервного копирования, но эффект не идеален, поэтому не рекомендуется. Он копирует только файлы структуры таблицы и файлы данных, но не копирует индексные файлы одновременно, поэтому восстановление происходит медленнее.
пример:
НАЗАД ТАБЛИЦА имя_таблицы TO '/tmp/db_name/';
Обратите внимание, что у вас должно быть разрешение FILE для выполнения этого SQL, а каталог /tmp/db_name/ должен быть доступен для записи пользователю mysqld. Экспортированный файл не может перезаписать существующий файл, чтобы избежать проблем с безопасностью.
SELECT INTO OUTFILE экспортирует данные в обычный текстовый файл. Вы можете настроить интервал полей, чтобы облегчить обработку этих данных.
пример:
ВЫБРАТЬ * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
Обратите внимание, что у вас должно быть разрешение FILE для выполнения этого SQL, а файл /tmp/db_name/tbl_name.txt должен быть доступен для записи пользователю mysqld. Экспортированный файл не может перезаписать существующий файл, чтобы избежать проблем с безопасностью.
3.2 Чтобы восстановить файлы, резервные копии которых созданы с помощью метода BACKUP TABLE, вы можете запустить инструкцию RESTORE TABLE для восстановления таблицы данных.
пример:
ВОССТАНОВИТЬ ТАБЛИЦУ ИЗ '/tmp/db_name/';
Требования к разрешениям аналогичны описанным выше.
Для файлов, резервные копии которых созданы с помощью метода SELECT INTO OUTFILE, вы можете запустить оператор LOAD DATA INFILE, чтобы восстановить таблицу данных.
пример:
ЗАГРУЗИТЬ ИНФАЙЛ ДАННЫХ '/tmp/db_name/tbl_name.txt' В ТАБЛИЦУ tbl_name;
Требования к разрешениям аналогичны описанным выше. Перед импортом данных таблица данных уже должна существовать. Если вы беспокоитесь о дублировании данных, вы можете добавить ключевое слово REPLACE, чтобы заменить существующие записи, или использовать ключевое слово IGNORE, чтобы игнорировать их.
4. Включить двоичный журнал (binlog)
Метод использования binlog относительно более гибок, избавляет от беспокойства и усилий, а также может поддерживать инкрементное резервное копирование.
Mysqld необходимо перезапустить, когда binlog включен. Сначала закройте mysqld, откройте my.cnf и добавьте следующие строки:
идентификатор сервера=1
log-bin = бинлог
журнал-bin-индекс = binlog.index
Затем запустите mysqld. Во время операции будут созданы файлы Binlog.000001 и binlog.index. Первый файл представляет собой mysqld, записывающий все операции обновления данных, а второй файл представляет собой индекс всех журналов binlog, который невозможно легко удалить. Пожалуйста, обратитесь к руководству для получения информации о binlog.
Когда вам нужно выполнить резервное копирование, вы можете сначала выполнить оператор SQL, чтобы позволить mysqld прекратить запись в текущий бинлог, а затем напрямую создать резервную копию файла. Таким образом можно достичь цели инкрементного резервного копирования:
ОЧИСТИТЬ ЖУРНАЛЫ. Если вы выполняете резервное копирование подчиненного сервера в системе репликации, вам также следует создать резервную копию файлов master.info и Relay-log.info.
Резервную копию файла binlog можно просмотреть с помощью инструмента mysqlbinlog, предоставляемого MySQL, например:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
Этот инструмент позволяет отображать все операторы SQL в указанной базе данных, а также может ограничивать временной диапазон, что весьма удобно. Подробности см. в руководстве.
При восстановлении вы можете использовать операторы, подобные следующим:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr имя_базы_данных
Используйте операторы SQL, выводимые mysqlbinlog, непосредственно в качестве входных данных для его выполнения.
Если у вас есть простаивающая машина, вы можете использовать этот метод для ее резервного копирования. Поскольку требования к производительности подчиненных машин относительно низкие, затраты на инкрементное резервное копирование могут быть низкими, а часть нагрузки по запросам данных может быть разделена. Почему бы и нет?
5. Прямое резервное копирование файлов данных. По сравнению с предыдущими методами резервное копирование файлов данных является наиболее прямым, быстрым и удобным. Недостаток заключается в том, что инкрементальное резервное копирование практически невозможно. Чтобы обеспечить согласованность данных, перед резервным копированием файла необходимо выполнить следующую инструкцию SQL:
FLUSH TABLES With READ LOCK, то есть сбрасывает все данные из памяти на диск и блокирует таблицу данных, чтобы гарантировать, что в процессе копирования не будут записаны новые данные. Данные, резервные копии которых созданы этим методом, также очень легко восстановить: достаточно скопировать их обратно в исходный каталог базы данных.
Обратите внимание, что для таблиц типа Innodb вам также необходимо создать резервную копию файлов журнала, то есть файлов ib_logfile*. Потому что, если таблица Innodb повреждена, вы можете положиться на эти файлы журналов для восстановления.
6. Стратегия резервного копирования. Для систем со средним объемом бизнеса стратегия резервного копирования может быть определена следующим образом: полное резервное копирование в первый раз, инкрементное резервное копирование один раз в день, полное резервное копирование один раз в неделю и т. д. Для важных и загруженных систем вам может потребоваться полное резервное копирование один раз в день, инкрементальное резервное копирование раз в час или даже чаще. Чтобы обеспечить оперативное резервное копирование и инкрементальное резервное копирование, не влияя на онлайн-бизнес, лучший способ — использовать механизм репликации «главный-подчиненный» (репликация) для создания резервных копий на подчиненной машине.
7. Обслуживание данных и аварийное восстановление. Для администратора базы данных (я еще им не являюсь, ха-ха) одной из наиболее важных задач является обеспечение безопасного, стабильного и высокоскоростного использования таблиц данных. Поэтому ваши таблицы данных необходимо регулярно поддерживать. Полезен следующий оператор SQL:
ПРОВЕРЬТЕ ТАБЛИЦУ или ВОССТАНОВИТЕ ТАБЛИЦУ, проверьте или поддерживайте таблицы MyISAM.
ОПТИМИЗИРОВАТЬ ТАБЛИЦУ, оптимизировать таблицу MyISAM
ANALYZE TABLE, анализ таблицы MyISAM. Конечно, все приведенные выше команды можно выполнить с помощью инструмента myisamchk, и они не будут здесь подробно описаны.
Таблицы Innodb можно дефрагментировать и повысить скорость индексации, выполнив следующие инструкции:
ALTER TABLE имя_таблицы ENGINE = Innodb;
На самом деле это NULL-операция. На первый взгляд она ничего не делает, но на самом деле она переупорядочивает фрагменты.
Часто используемые таблицы MyISAM можно восстановить с помощью методов, упомянутых выше. Если индекс поврежден, вы можете использовать инструмент myisamchk для его восстановления. Для таблиц Innodb это не так просто, поскольку все таблицы хранятся в одном табличном пространстве. Однако в Innodb имеется механизм проверки, называемый нечеткой контрольной точкой. Пока файл журнала сохраняется, ошибки можно исправлять на основе файла журнала. Вы можете добавить следующие параметры в файл my.cnf, чтобы mysqld автоматически проверял файл журнала при запуске:
innodb_force_recovery = 4
Информацию об этом параметре смотрите в руководстве.
8. Подведите итоги резервного копирования данных и определите подходящую стратегию резервного копирования. Это малая часть того, что делает администратор базы данных. Вначале все сложно.