Как сохранить таблицу mysql в файл
Обновлено: 07.07.2024
Эта статья в первую очередь рассчитана на новичков в администрировании, для тех, кто хочет научиться самостоятельно производить импорт и экспорт баз данных. Зачем вам может это понадобиться? Допустим, вы хотите самостоятельно сделать бэкап, чтобы в дальнейшем при необходимости восстановить определенную версию базы данных. Или вам нужно сделать перенос сайта на другой сервер либо в другую среду разработки. В общем, причин может быть множество, поэтому понимание того, как сначала сделать, а потом импортировать резервную копию, лишним не будет.
Для того, чтобы выполнить все дальнейшие действия, у вас должны быть:
а) доступ к серверу на базе Linux, на котором работает MySQL/MariaDB;
б) название базы данных и данные доступа к ней.
Используем консоль
Экспорт
Для того, чтобы произвести экспорт, мы будем использовать утилиту mysqldump. При помощи нее осуществляется работа с текстовыми файлами базы данных. Итак, вы должны знать название базы данных, а также иметь доступ (логин и пароль) к аккаунту, который имеет, по крайней мере, доступ read only (только для чтения).
Для экспорта базы данных введите вот такую команду:
в которой нужно ввести имя пользователя с необходимым доступом, название нужной вам базы данных, а также data-dump.sql – файл в текущей директории, куда будут сохранены данные.
После ввода этой команды вы не увидите никакого вывода на экране, однако вы можете проверить содержимое файла data-dump.sql для того, чтобы убедиться, что теперь он является резервной копией вашей базы данных.
Содержимое файла должно выглядеть примерно так, как показано ниже. В документе будет указано название базы данных (в данном случае MySQL), ее название и другие данные.
Импорт
Для того, чтобы импортировать существующий файл в MySQL или MariaDB, вам нужно начать с создания новой базы данных. Именно в нее вы затем загрузите содержимое резервной копии.
Сначала подключитесь к базе данных в качестве root-пользователя (либо другого пользователя, который сможет создать новую базу данных):
После того, как вы подключились к консоли MySQL, создайте новую базу данных (в данном случае new_database):
После этого на экране появился следующий вывод:
Теперь для выхода из консоли MySQL нажмите CTRL+D. Далее переходите к самому импорту. Сделать это можно, введя вот такую команду:
Команда очень похожа на команду экспорта, вам нужно ввести имя пользователя, название новой базы данных, куда вы будете импортировать данные (в качестве примера new_database), и название самого файла, который вы собираетесь импортировать (data-dump.sql).
И тогда при всех последующих запросах в данном сеансе автоматически будет использоваться эта база данных. Данную установку можно изменить, использовав команду use с названием другой базы данных.
Что касается команды show, то она используется для того, чтобы посмотреть информацию о самих базах данных, о таблицах, столбцах, которые они содержат, а также о состоянии сервера.
Допустим, нам нужно посмотреть, список таблиц в базе. Для этого вводим:
Хотите увидеть список столбцов в какой-то определенной таблице? Используйте команду SHOW COLUMNS FROM и название нужно вам таблицы:
Статистику по работе сервера можно получить в ответ на команду:
Используем phpMyAdmin
Экспорт и импорт баз данных можно также делать через phpMyAdmin. В общем и целом, пожалуй, это даже более простой путь, чем использование консоли.
Экспорт
Зайдите в phpMyAdmin и выберите базу данных, с которых вы хотите работать.
Далее выберите вкладку «Экспорт» и, в зависимости от своих предпочтений, быстрый или обычный метод экспорта. Второй подойдет для тех, кто хочет самостоятельно выставить все настройки.
Импорт
Выполнить импорт базы данных тоже совсем несложно. Как и в предыдущем случае, в списке слева выберите нужную вам базу данных, а затем перейдите во вкладку «Импорт».
Импорт успешно завершён, выполнено 32 запроса.
В списке слева вы можете выбрать базу данных, с которой работали, и посмотреть имеющиеся файлы, а также их содержимое (и изменить их).
Заключение
Выбор подходящего метода экспорта и импорта баз данных зависит только от вас и ваших предпочтений – кому-то проще работать в консоли, а для кого-то понятнее phpMyAdmin. Главное, нужно регулярно делать бэкапы, в том числе и ваших баз данных.
Кстати, полезную информацию о базах данных я также нашел в Справочном центре Timeweb.
Приветствую вас, друзья! 🙂
Сегодня я решил продолжить разговор о работе с MySQL в консоли и уделить внимание процедуре экспорта базы данных MySQL.
В статье я расскажу о том, как сделать дамп базы MySQL, а также производить выгрузку данных из MySQL в Excel файл и csv формат.
Мы рассмотрим различные варианты выборки информации из базы данных сайта: создание дампа одной и нескольких БД, экспорте данных из отдельных таблиц и результатов произвольных SELECT запросов.
А также поговорим о том, как сделать вывод данных из MySQL базы в консоли сервера и командной строке MySQL.
В данной статье я не буду рассказывать о том, как экспортировать данные с помощью phpMyAdmin и других визуальных инструментов.
Во-первых, потому что по данной теме уже достаточно материала в сети. Причём качественного материала, который я не горю желанием копипастить.
А, во-вторых, я уже вкратце сам рассматривал процесс вывода информации из MySQL базы в SQL файл в одной из своих статей, где рассказывал об установке WordPress на хостинг.
Так что, если вы не являетесь профессиональным разработчиком или системным администратором, которым может быть полезна информация о работе с консолью, и вы пришли лишь за инструкцией по экспорту базы в phpMyAdmin, то можете ограничиться чтением информации по ссылке выше.
Хочу, чтобы вы поняли меня правильно: я ни в коем случае не хочу вас обидеть, а просто желаю, чтобы вы потратили своё время с максимальной пользой для дела и получили то, что искали.
На этом вступительная часть окончена и мы переходим к обзору консольных команд для создания дампа базы MySQL, которые я решил рассортировать по объёму сохраняемых данных: начиная от экспорта всей БД, заканчивая отдельными таблицами и результатами произвольных запросов.
Создание дампа базы MySQL через консоль
Хочу в самом начале сделать небольшое уточнение.
Следовательно, команды для данных действий будут немного разные.
Для создания дампа БД у MySQL есть встроенная утилита под названием mysqldump, которую нужно использовать за пределами командной строки MySQL в консоли сервера или другой оболочке.
Если вдруг вам нужно будет сделать дамп со всеми базами данных на сервере, то используйте следующий вариант команды:
Для создания дампа лишь для нескольких конкретных баз данных вам понадобится вызов mysqldump со следующими параметрами:
В итоге, в каждом случае вы получите дамп базы MySQL, содержащий команды для создания структуры содержащихся таблиц (поля, их типы, индексы, ключи и т.д.), а также операции наполнения их данными.
Данный вариант годится только для восстановления и копирования баз данных целиком.
О том, как делать бэкапы определённых таблиц MySQL и получать их данные в читаемом виде, речь пойдёт дальше.
Делаем дамп таблицы MySQL и экспорт данных
Для создания дампа определённых таблиц MySQL базы данных нам понадобится всё та же утилита mysqldump, вызываемая со следующими параметрами:
Приведённый пример выведет на экран следующую ошибку:
Как видите, будет использоваться только самая последняя БД из списка указанных. В принципе, такое поведение вполне логично, т.к. во всех БД указанных таблиц может не оказаться.
Хорошо, дамп таблиц MySQL базы данных мы получили. Его можно использовать для их восстановления или копирования вместе со структурой.
Достичь задуманного нам поможет вариант вызова утилиты mysql из консоли с определёнными параметрами:
Данная команда позволит нам выполнить запрос к требуемой БД и вывести результат в консоль, не заходя в командную строку MySQL.
Ну, а для того, чтобы не выводить данные в консоль, а записать их в файл, нужно дополнить команду следующим образом:
Благодаря данным конструкциям мы можем не только получить данные, хранящиеся во всех полях таблицы, но и в конкретных. Для этого достаточно вместо символа wildcards (*) прописать через запятую требуемые.
В результате у нас на выходе получится обычный текстовый файл, который будет содержать названия полей в виде шапки и информацию по ним для всех записей. Его можно открыть в обычном текстовом редакторе, независимо от того, какое разрешение вы ему зададите при создании.
Если же захотите экспортировать данные из MySQL базы в xls или csv формате, чтобы полученный файл корректно отображался в табличных редакторах, то о том, как это сделать будет рассказано немного позже 🙂
Создание бэкапов и вывод данных из MySQL базы с помощью запросов
Особенно часто с таким сталкиваются разработчики корпоративных проектов, когда менеджеры просят их предоставить всякие статистические данные. Либо когда нужно сделать бэкап определённой части таблицы для её быстрого восстановления.
Для бэкапа нам понадобится всё та же утилита mysqldump, которую нужно будет вызвать в таком виде:
В результате мы получим файл с SQL командами для создания таблицы со всей его структурой, которая после создания будет наполняться информацией, выбираемой с помощью уточняющего запроса.
Если нам будет нужно просто получить данные, хранящиеся в одной или нескольких таблицах, то нам потребуется модификация команды, используемой в предыдущем случае при выборке всех данных таблицы, только с некоторыми уточнениями:
Как вы понимаете, помимо различных уточнений, указываемых в запросе с помощью директивы WHERE, можно использовать и прочие SQL конструкции: JOIN, UNION и т.д.
Статистику собрать получится какую угодно 🙂
То же самое действие возможно произвести также при работе в командной строке MySQL с помощью следующей команды:
Данная команда как раз и предназначена для создания файлов с результатами выборки. Причём, результаты можно не только экспортировать в файлы, но и записывать в переменные, а также выводимые данные можно форматировать различными способами.
Далее речь как раз пойдёт о корректном выводе данных MySQL в xls и csv форматы с помощью данной команды. А с musqldump в рамках данной статьи мы прощаемся.
В завершение своего краткого экскурса по mysqldump хочу привести вариант вызова команды со списком параметров для создания оптимизированного дампа базы MySQL и таблиц, восстановление БД и отдельных таблиц из которого будет занимать меньше времени, чем при обычном вызове:
Ради эксперимента я использовал данный вариант для того, чтобы сделать дамп базы MySQL размером в 143 Мб. Последующее восстановление заняло 59 секунд времени против 1 минуты и 3 секунд, когда БД восстанавливалась из дампа, сделанного вызовом mysqldump без специальных параметров.
Согласен, что это мелочь. Но это только в случае данного объёма данных. Если использовать данную методику при создании дампа размером более 1Гб, то разница будет более существенной.
Если вы столкнётесь с такой ситуацией, то не забудьте ещё предварительно запаковать дамп базы MySQL в архив. Лучше всего tar.gz. Тогда восстановление займёт ещё меньше времени.
Экспорт данных из MySQL в Excel и csv файлы
Я не зря объединил информацию о выводе информации из MySQL в эти два формата в одном блоке, т.к. они очень похожи, используются примерно одинаково (для структурирования информации в виде таблиц) и команды для экспорта будут вызываться одни и те же.
Как известно, единственным существенным различием между данными форматами является то, что расширение xls и xlsx имеют файлы, создаваемые в программе Microsoft Office Excel, которая работает только под Windows, а csv файлы являются более универсальными и операции с ними возможны во многих редакторах.
Это не значит, что xls нигде, кроме Microsoft Office Excel, не откроется. Тот же OpenOffice подтверждает обратное.
Начну с того, что экспортировать в xls или csv можно только результаты SQL запросов, работать с которыми мы с вами научились ранее, т.к. всю базу данных в один файл будет невозможно вывести за одну операцию.
Во-первых, это не оптимально, т.к. такой файл вряд ли откроется при больших объёмах хранящейся в БД информации. А, во-вторых, непонятно, как разбивать внутри файла информацию по таблицам и полям.
Нет, сделать это, конечно, возможно, но вряд ли одной командой и вообще вряд ли кто-то будет таким заниматься в консоли. Думаю, что для данных целей понадобится специальное ПО или, как минимум, скрипт.
Если вы вдруг знаете, как можно экспортировать информацию из всей MySQL БД сразу в один или несколько xls файлов в консоли, то напишите об этом в комментариях. Думаю, почитать об этом будет полезно многим.
Итак, если мы говорим о том, как сделать экспорт данных из MySQL в xls и csv, то сделать это можно прямо в консоли сервера через утилиту mysql либо в командной строке MySQL, работой с которой я знакомил вас в предыдущей своей статье.
Начнём по порядку.
Экспортировать данные из MySQL базы данных в csv и xls форматы прямо в консоли сервера можно следующими командами.
На Linux системах:
На Windows:
Экспорт данных из MySQL таблиц в csv приведённой выше командой в данном случае, к сожалению, не удастся, т.к. у Windows, в отличие от Linux, нет встроенной консольной команды для работы с потоками, какой является sed в Linux.
Хорошо, если она у вас уже установлена. В противном случае экспорт данных из БД MySQL выбранным способом принесёт нам слишком много хлопот.
Зато извлечение информации в xls файл прост как 5 копеек 🙂 Запустить его очень просто следующим способом, который я опробовал лично:
Не знаю, возможно, при произведении каких-либо специфических действий в Microsoft Office Excel и возникнут проблемы в дальнейшем, я так глубоко не копался. При обычном просмотре данных, по крайней мере, я ничего необычного не встретил.
Если у вас в процессе использования экспортированного из MySQL xls файла возникнут какие-то проблемы либо в данной программе, либо в других, то дайте мне знать, пожалуйста, в комментариях.
Описанным выше путём можно экспортировать содержимое БД MySQL и в csv файл, в принципе. Но тогда данные из разных полей таблицы будут записаны скопом, без разделителей, что может плохо отображаться в различных программах для работы с таблицами, в которых обычно с csv файлами и работают.
Ну, а тот же самый Microsoft Office Excel отобразил всю информацию, соответствующую одной записи в таблице, записав её в одну ячейку без каких-либо разделителей. Думаю, что подобным образом поступят и другие редакторы таблиц.
Поэтому при экспорте базы данных MySQL в csv файлы нужно делать это, разделяя информацию специальными символами, воспринимаемыми редакторами.
И тут я плавно подошёл ко второму способу экспорта MySQL данных в csv и xls, который заключается в использовании командной строки MySQL.
Итак, для того, чтобы экспортировать данные MySQL в csv файл данным способом, нам нужна следующая команда:
В результате её выполнения вы получите csv файл по пути, прописанному вами при вызове, который будет корректно открываться в большинстве современных табличных редакторов. На всякий случай напоминаю, что запускать данную команду нужно только после соединения с базой данных MySQL.
Данная команда также отлично подходит и для экспорта данных MySQL в xls файл для корректного отображения в Microsoft Office Excel. Только в этом случае разделители нам не нужны, т.к. они будут мешать в разбиении информации по ячейкам:
Однако, на практике не всё так просто, как я описал. Во время выполнения команды вы можете столкнутся со следующей ошибкой в консоли, препятствующей выполнению экспорта:
Здесь есть два способа решения проблемы:
- Изменить параметры запуска MySQL сервера
- Изменить путь к конечному файлу экспорта MySQL
Сперва нужно зайти в командную строку MySQL и выполнить одну из следующих команд:
Результатом выполнения обеих будет значение глобальной переменной MySQL secure_file_priv, которая содержит путь к каталогу, через который могут осуществляться операции экспорта и импорта данных MySQL (в будущем ссылка на статью об импорте данных).
В моём случае у данной переменной было вообще установлено значение NULL, т.к. я, как уже говорил, пользуюсь для работы в консоли утилитами MySQL из дистрибутива, входящего в OpenServer. Данное значение указывало на то, что операции экспорта данных MySQL и импорта с помощью указанных команд были вообще закрыты.
Как потом выяснилось, это распространённая ситуация в случае использования коробочных WAMP и MAMP серверов.
К сожалению, привычными методами изменения значений глобальных переменных MySQL в моём случае воспользоваться не удалось:
В результате я увидел в консоли лишь следующую ошибку:
В итоге, чтобы изменить значение переменной secure_file_priv и открыть операции экспорта и импорта, мне потребовалось зайти в файл конфигурации MySQL mysql.ini, который расположен в корневой директории дистрибутива MySQL, или к нему можно получить доступ иным способом, если MySQL входит в комплект вашего WAMP/LAMP/MAMP сборки сервера.
Вам, кстати, если захотите изменить путь к буферному каталогу обмена файлами, нужно будет сделать то же самое.
В моём случае в конфиге данная переменная уже существовала, только в закомментированном виде:
Если у вас её не будет, то пропишите её с нуля в секции [mysqld] (по крайней мере, у меня она располагалась там).
Я её раскомментил и решил использовать в том виде, в каком она была прописана. Т.е. при экспорте данных из MySQL и их импорте обратно файлы у меня теперь будут храниться в каталоге c:\openserver\userdata\temp\.
После изменения конфига (любого, кстати), не забывайте перезапускать ваш сервер либо отдельную службу, настройки которой вы правили, если есть такая возможность, чтобы изменения вступили в силу!
Для уверенности, после перезапуска MySQL сервера ещё раз выводим на экран переменную secure_file_priv и копируем её значение в буфер обмена.
А теперь нам нужно вызвать команду, что и в начале, только перед названием файла, в который будет сохраняться информация из БД MySQL, прописать путь, хранимый в изменяемой нами переменной в следующем виде:
После этого экспорт данных из MySQL в моём случае заработал.
На этом статья о том, как сделать дамп базы MySQL и её таблиц, а также сделать вывод данных из MySQL таблиц в различные форматы, подходит к концу. Пишите в комментариях свои отзывы и делитесь со всеми своими вариантами скриптов, которыми вы пользуетесь на практике чаще всего.
В этой статье будут показаны восемь способов экспорта строк из запроса T-SQL в текстовый файл. Мы покажем следующие параметры:
Требования
Вам потребуется SQL Server, установленный с SSIS и SQL Server Data Tools (SSDT).
Начало работы
Давайте рассмотрим каждый из способов мы можем экспортировать результаты запроса.
1. Показывать результаты в файл в SSMS
В первом варианте мы настроим SSMS для отображения результатов запроса в текстовый файл. Мы будем использовать следующий скрипт с именем myscript.sql:
В SQL Server Management Studio (SSMS) отображается следующий результат:
Если вы хотите сохранить результаты в текстовом файле, вы можете сделать это в SSMS. Перейдите в Инструменты> Параметры :
Выберите вариант Результат в файл:
Создайте запрос и выполните запрос. Будет отображена возможность указать имя и путь. Мы будем вызывать результаты в файле с именем Results.rpt:
Сохранены следующие результаты:
2. SQLCMD
Используйте следующую команду в cmd:
Команда использовала файл myquery.sql, созданный ранее, а вывод сохраняется в файле myoutput.txt:
3. PowerShell
Invoke-Sqlcmd вызовет сценарий myquery.sql, созданный в начале этой статьи, и сохранит результаты в файле с именем powershelloutput. .текст. Результаты будут следующими:
4. Импорт/ Мастер экспорта в SSMS
Вы откроете Мастер импорта и экспорта SQL Server:
Мы будем экспортировать из SQL Server в плоский файл. Выберите поставщик Microsoft OLE DB в качестве источника данных:
При необходимости укажите имя сервера и информацию о подключении:
В разделе «Назначение» выберите «Место назначения плоского файла» и нажмите «Обзор», чтобы указать имя и путь к файлу:
В этом примере имя плоского файла будет exportwizard.txt:
После того, как у вас есть имя файла и путь, нажмите следующий:
Выберите вариант« Написать запрос, чтобы указать данные для передачи »:
Сохраните значения по умолчанию:
Выберите «Выполнить немедленно» для экспорта данные немедленно:
Созданный файл будет похож на этот:
5. SSIS в SSDT
Вы также можете использовать SSIS в SSDT. Этот метод аналогичен мастеру импорта/экспорта в SSMS, поскольку SSMS вызывает SSIS для импорта и экспорта данных.
Перейдите в SSDT, затем перейдите в меню «Файл»> «Новый проект» и выберите «Проект служб интеграции»:
В обозревателе решений щелкните правой кнопкой мыши Пакеты служб SSIS и выберите Импорт служб SSIS. и мастер экспорта:
Следующие шаги такие же, как в SSMS, когда мы вызываем мастер импорта/экспорта, но в конце у вас нет возможности запустить его немедленно.
Чтобы запустить пакет, нажмите Пуск:
Пакет сгенерирует текстовый файл в формате CSV.
Для этого в SSDT перетащите задачу Script:
Дважды щелкните« Задача сценария »и нажмите кнопку« Изменить сценарий »:
В скрипте в области, где говорится, добавьте сюда свой код, добавьте следующий код:
В первой строке указывается запрос T-SQL:
Во второй строке указывается информация о соединении (имя SQL Server, имя базы данных и метод аутентификации):
В третьей строке указывается путь к текстовому файлу:
Мы будем использовать соединение SQL и запрос и откройте соединение:
Структура try используется для обработки исключений. Строка с «while (reader.Read ())» используется для чтения построчно результатов SQL-запроса. myFile.WriteLine запишет в текстовый файл всю информацию из SQL Server:
Наконец, мы выберем файл и соединение с SQL Server с Reader.Close и myFile.Close:
7. Службы отчетов
В SSDT перейдите в меню «Файл»> «Новый проект» и выберите «Мастер проекта сервера отчетов»:
Укажите соединение SQL Server и базы данных . В этом примере мы подключаемся к локальному SQL Server и базе данных AdventureWorks2016CTP3:
Выбрать Тип отчета в виде таблицы:
В «Дизайн таблицы» , нажмите Finish:
Нажмите Preview и в значке Save выберите CSV (через запятую), чтобы сохранить файл с расширением csv:
8. BCP
В следующем примере для экспорта запроса используется bcp результаты в файл с именем bcp.txt. -T используется для указания того, что мы используем надежное соединение (соединение Windows), а -c используется для выполнения операции типа данных:
Выводы
Есть много других способов экспортировать результаты. Однако эти параметры вдохновят вас на использование других.
В заключение, вот несколько советов о том, когда их использовать:
5. Мастер SSIS (почти такой же, как номер 4, но мы используем SSDT вместо SSMS для создания пакета. ). Он похож на 4, но его можно настроить, и вы можете создавать действительно сложные пакеты, интегрированные с веб-службами, отправлять почту, PowerShell и многое другое. Используйте его, если вам нужно сложное решение, требующее интеграции между несколькими инструментами.
Как экспортировать результаты запроса в CSV на SQL Server
В этом кратком руководстве я покажу вам 2 метода экспорта результатов запроса в CSV в SQL Server Management Studio:
- Быстрый метод для меньшего числа записей
- Полный метод для больших наборов данных
Я также продемонстрирую, как включать заголовки столбцов при экспорте результатов.
Метод 1. Быстрый способ экспорта результатов запроса в CSV на SQL Server
Для начала запустите запрос, чтобы получить результаты.
Например, я выполнил простой запрос и получил следующую таблицу с небольшим количеством записей:
Чтобы быстро экспортировать результаты запроса, выберите все записи в вашей таблице (например, по рис. измените любую ячейку сетки, а затем используйте комбинацию клавиш Ctrl + A ):
Откройте пустой файл CSV и вставьте результаты:
Вышеупомянутый метод может быть полезен для меньшего количества записей. Однако, если вы имеете дело с гораздо большими наборами данных, вы можете рассмотреть возможность использования второго метода ниже.
Метод 2: экспорт результатов запроса для больших наборов данных
Использование В том же примере вы можете экспортировать результаты запроса в файл CSV, щелкнув правой кнопкой мыши любую ячейку в сетке и выбрав « Сохранить результаты как… »
Затем введите имя для вашего CSV-файла (например, query_results), а затем нажмите
Ваш CSV-файл будет сохранен в указанном вами месте:
И если вы откроете файл CSV, вы увидите экспортированный результаты (без заголовков столбцов):
Вы можете выполнить следующие шаги, если вам нужно включить заголовки столбцов при экспорте файла CSV в SQL Server.
Как включить заголовки столбцов при экспорте запроса результаты в CSV в SQL Server
Чтобы включить заголовки столбцов, перейдите в Инструменты , а затем выберите Параметры…
Затем нажмите Результаты запроса >> SQL Server >> Результаты в сетку ng>:
Теперь вам нужно перезапустить сервер SQL, чтобы изменения вступили в силу. .
Затем повторно запустите запрос, чтобы получить результаты:
Щелкните правой кнопкой мыши любую ячейку в самой сетке и выберите« Сохранить результаты как… »
Введите имя для вашего CSV-файла и нажмите Сохранить :
Ваш новый CSV-файл будет теперь содержат заголовки столбцов, идущие вперед:
Базу данных MySQL можно скопировать, если временно выключить MySQL-сервер и просто скопировать файлы из папки /var/lib/mysql/db/. Если сервер не выключить, по очевидным причинам вероятна потеря и порча данных. Для больших нагруженных баз эта вероятность близка к 100%. Кроме того, при первом запуске с «грязной» копией базы данных MySQL-сервер начнет процесс проверки всей базы, который может затянуться на часы.
В большинстве «живых» проектов регулярное выключение сервера БД на длительное время неприемлемо. Для решения этой проблемы применяется трюк, основанный на снэпшотах файловой системы. Снэпшот — это что-то вроде «фотографии» файловой системы на определенный момент времени, сделанный без реального копирования данных (и потому быстро). Аналогичным образом работает «ленивое копирование» объектов во многих современных языках программирования.
Общая схема действий такова: блокируются все таблицы, сбрасывается файловый кэш БД, делается снэпшот файловой системы, разблокируются таблицы. После этого файлы спокойно копируются из снэпшота, после чего он уничтожается. «Блокирующая» часть такого процесса занимает время порядка секунд, что уже терпимо. В качестве расплаты на какое-то время, пока «жив» снэпшот, снижается производительность файловых операций, что в первую очередь бьет по скорости операций записи в базу.
Некоторые файловые системы, например, ZFS, поддерживают снятие снэпшотов нативно. Если вы не пользуетесь ZFS, но на вашем сервере стоит менеджер томов LVM, вы также сможете скопировать базу MySQL через снэпшот. Наконец, под *nix можно воспользоваться драйвером снэпшотов R1Soft Hot Copy, но этот способ не заработает в контейнере openvz (процесс бэкапа MySQL описан здесь).
Для баз MyISAM существует официальная бесплатная утилита mysqlhotcopy, которая «правильно» копирует файлы баз MyISAM без остановки сервера. Существует аналогичная утилита для InnoDB, но она платная, хотя и возможностей в ней больше.
Копирование файлов — самый быстрый способ перебросить базу данных целиком с одного сервера на другой.
2. Копирование через текстовые файлы
Для того, чтобы считать в бэкап данные из production-базы, необязательно дергать файлы. Можно выбрать данные запросом и сохранить их в текстовый файл. Для этого используется SQL-команда SELECT INTO OUTFILE и парная ей LOAD DATA INFILE. Выгрузка производится построчно (можно отобрать для сохранения только нужные строки, как в обычном SELECT). Структура таблиц нигде не указывается — об этом должен заботиться программист. Он также должен позаботиться о включении команд SELECT INTO OUTFILE в транзакцию, если это необходимо для обеспечения целостности данных. На практике SELECT INTO OUTFILE используется для частичного бэкапа очень больших таблиц, которые нельзя скопировать никаким другим образом.
В большинстве случаев намного более удобна созданная Игорем Романенко утилита mysqldump. Утилита mysqldump формирует файл, содержащий все SQL-команды, необходимые для полного восстановления БД на другом сервере. Отдельными опциями можно добиться совместимости этого файла с практически любой СУБД (не только MySQL), кроме того, существует возможность выгрузки данных в форматах CSV и XML. Для восстановления данных из таких форматов существует утилита mysqlimport.
Утилита mysqldump консольная. Существуют её надстройки и аналоги, позволяющие управлять бэкапом через веб-интерфейс, например, украинская тулза Sypex Dumper (их представитель zapimir есть на хабре).
Недостатки универсальных утилит бэкапа в текстовые файлы — это относительно невысокая скорость работы и отсутствие возможности делать инкрементные бэкапы.
3. Инкрементные бэкапы
Традиционно рекомендуют держать 10 бэкапов: по одному на каждый день недели, а также бэкапы двухнедельной, месячной и квартальной давности — это позволит достаточно глубоко откатиться в случае порчи каких-либо данных.
Храниться бэкапы должны точно не на том же диске, что и живая база, и не на том же сервере. На случай пожаров и прочих катаклизмов лучше всего арендовать пару юнитов в соседнем дата-центре.
Эти требования могут стать проблемой для больших баз. Прокачка бэкапа 100-гигабайтной базы по 100-мбитной сети займет часа три, на которые полностью забьет канал.
Частично решить эту проблему позволяют инкрементные бэкапы, когда полный бэкап делается, скажем, только по воскресеньям, а в остальные дни пишутся только данные, добавленные или измененные за прошедшие сутки. Сложность в том, как выявить эти самые «данные, изменившиеся за сутки».
Здесь практически вне конкуренции система Percona XtraBackup, которая содержит модифицированный движок InnoDB, анализирует двоичные логи MySQL и вытаскивает из них необходимую информацию. Почти такими же возможностями обладает платная InnoDB Hot Backup, упомянутая выше.
Общая проблема с любыми бэкапами в том, что они всегда отстают. В случае фатального сбоя основного сервера восстановить систему можно будет только с некоторым «откатом» по времени, что очень и очень разочарует её пользователей. Если в системе так или иначе были затронуты финансовые потоки, подобный «откат» может в прямом смысле влететь в копеечку.
4. Репликация
Репликация — это очень здорово, только использовать её нужно по назначению. Реплика — это полная копия базы, но это не резервная копия! Очевидно же, что если на мастере выполнить DROP TABLE или UPDATE users SET password=«Haha!», изменения будут тут же скопированы на слейв, и откатить их назад станет невозможно.
Репликацию можно совместить с бэкапом на уровне файлов базы, останавливая слейв, а не мастера.
Читайте также: