Быстрое восстановление дампа базы mysql. Дамп и восстановление базы данных MySQL
Странно, но в интернете найти эту информацию довольно непросто. Куча левых всяких рекомендаций. А на самом деле всё просто. Восстановить дамп можно командой:
# mysql -u -p < mysql_dump.sql.
Вместо подставьте имя пользователя, я лично восстановление делаю от рута. А в mysql_dump,sql, собственно, дамп сделанный mysqldump утилитой. Опция -p указывает на авторизацию с паролем, после нажатия ентера у вас спросят пароль. Вот и всё, собственно. Сделать дамп можно командой:
# mysqldump -u root -p -f database_name > file_with_dump.sql
Здесь параметр -f database_name задаёт имя базы, которую сохраняем. Если надо всё — заменяем на —all-databases. Эта опция укажет сохранять все базы на сервере. Чтобы не вводить каждый раз пароль, можно указать его прямо в команде (это надо, если вы делаете дамп по крону из скрипта), впрочем, с точки зрения безопасности тут возникают вопросы, особенно если пароль рутовый для мускуля. Вот если бы мускуль понимал хотя бы md5 пароли… Впрочем не факт, что не понимает, просто может я не знаю:)
Автор
Алекс Разгибалов
Сумасшедший мужчина, неопределённого возраста, наслаждающийся манией преследования. Паталогически недоверчив, эгоистичен, авторитарен. Вторичные диагнозы - программист и поц. Владеет английским языком на уровне около хренового разговорного. Также знаком с некоторыми другими языками. Интересуется всем и вся, за счёт чего в любой области знания являются поверхностными, неглубокими. Характер невыдержанный. Крепость - 55 градусов.
./mysql -u
2018-12-04T00:00Z
2018-12-11T00:00Z
Когда мы создаем файл дампа с mysqldump , то он содержит большой скрипт SQL для воссоздания содержимого базы данных. Поэтому мы восстанавливаем его с помощью запуска клиентской командной строки MySQL:
Mysql -uroot -p
(где root - наше имя администратора для MySQL), и после подключения к базе данных нам нужны команды для создания базы данных и чтения файла:
Create database new_db; use new_db; \. dumpfile.sql
Детали будут меняться в зависимости от того, какие параметры были использованы при создании файла дампа.
2018-12-18T00:00Z
Вам просто нужно запустить это:
mysql -p -u < db_backup.dump
Если дамп содержит несколько баз данных, вы должны опустить имя базы данных:
mysql -p -u < db_backup.dump
Чтобы запустить эти команды, откройте командную строку (в Windows) и cd в каталог, в котором mysql.exe исполняемый файл mysql.exe (возможно, вам придется немного поразмыслить над ним, это будет зависеть от того, как вы установили mysql, то есть автономный или как часть пакета, такого как WAMP). Когда вы находитесь в этом каталоге, вы должны просто ввести команду.
2018-12-25T00:00Z
Это должно быть так просто, как запустить это:
Mysql -u
Если дамп имеет одну базу данных, вам может потребоваться добавить строку вверху файла:
USE
Если это дамп многих баз данных, операторы использования уже там.
Чтобы запустить эти команды, откройте командную строку (в Windows) и cd в каталог, в котором mysql.exe исполняемый файл mysql.exe (возможно, вам придется немного поразмыслить над ним, это будет зависеть от того, как вы установили mysql, то есть автономный или как часть пакета, такого как WAMP). Как только вы попадете в этот каталог, вы сможете просто набрать команду, как я ее выше.
2019-01-01T00:00Z
Команда One-liner для восстановления сгенерированного SQL из mysqldump
Mysql -u 2019-01-08T00:00Z Используя файл дампа 200 Мбайт, созданный в Linux для восстановления в Windows с mysql 5.5, у меня был больше успеха с Source file.sql
подход из командной строки mysql, чем с помощью Mysql < file.sql
подход в командной строке, вызвавший некоторую ошибку 2006 «сервер ушел» (на окна) Как ни странно, служба, созданная во время (mysql) install, относится к файлу my.ini, которого не было. Я скопировал «большой» пример файла в my.ini, который я уже модифицировал с рекомендуемым увеличением. Мои ценности Max_allowed_packet = 64M
interactive_timeout = 250
wait_timeout = 250 2019-01-15T00:00Z В качестве конкретного примера предыдущего ответа: Мне нужно было восстановить резервную копию, чтобы я мог импортировать / перенести ее в SQL Server. Я установил только MySql, но не зарегистрировал его как услугу или не добавил на свой путь, поскольку мне не нужно его запускать. Я использовал Windows Explorer, чтобы поместить мой файл дампа в C: \ code \ dump.sql. Затем откройте MySql из пункта меню «Пуск». Создал БД, затем выполнил команду источника с полным путем следующим образом: Mysql> create database temp
mysql> use temp
mysql> source c:\code\dump.sql Приветствую вас, друзья! 🙂 Сегодня я решил продолжить разговор о работе с MySQL в консоли и уделить внимание процедуре экспорта базы данных MySQL. В статье я расскажу о том, как сделать дамп базы MySQL, а также производить выгрузку данных из MySQL в Excel файл и csv формат. Мы рассмотрим различные варианты выборки информации из : создание дампа одной и нескольких БД, экспорте данных из отдельных таблиц и результатов произвольных SELECT
запросов. А также поговорим о том, как сделать вывод данных из MySQL базы в консоли сервера и командной строке MySQL. В данной статье я не буду рассказывать о том, как экспортировать данные с помощью phpMyAdmin и других визуальных инструментов. Во-первых, потому что по данной теме уже достаточно материала в сети. Причём качественного материала, который я не горю желанием копипастить. А, во-вторых, я уже вкратце сам рассматривал процесс вывода информации из MySQL базы в SQL файл в одной из своих статей, где рассказывал об . Так что, если вы не являетесь профессиональным разработчиком или системным администратором, которым может быть полезна информация о работе с консолью, и вы пришли лишь за инструкцией по экспорту базы в phpMyAdmin, то можете ограничиться чтением информации по ссылке выше. Хочу, чтобы вы поняли меня правильно: я ни в коем случае не хочу вас обидеть, а просто желаю, чтобы вы потратили своё время с максимальной пользой для дела и получили то, что искали. На этом вступительная часть окончена и мы переходим к обзору консольных команд для создания дампа базы MySQL, которые я решил рассортировать по объёму сохраняемых данных: начиная от экспорта всей БД, заканчивая отдельными таблицами и результатами произвольных запросов. Хочу в самом начале сделать небольшое уточнение. Дамп базы
— это файл с набором SQL команд, который при запуске позволяет создавать базы данных и таблицы, а также наполнять их информацией. Дамп нужен тем, кто хочет скачать базу данных MySQL с целью её копирования на другой сервер или в рамках существующего. Также, если кто-то не в курсе, бэкап базы данных MySQL — это, по сути, её дамп, сделанный в определённый период времени, который позволяет восстановить структуру и данные базы при необходимости. Экспорт данных
— это просто извлечение информации из таблиц в текстовом виде для дальнейшей работы с текстовом или графическом редакторах. Следовательно, команды для данных действий будут немного разные. Для создания дампа БД у MySQL есть встроенная утилита под названием mysqldump
, которую нужно использовать за пределами командной строки MySQL в консоли сервера или другой оболочке. Итак, для самого простого и распространённого варианта — экспорта данных конкретной БД в консоли MySQL для переноса её на другой сервер или внутреннего копирования нужно выполнить следующую команду: Mysqldump -u имя_пользователя -p имя_базы_данных > путь_и_имя_файла_дампа
Данная утилита может создавать дампы базы MySQL только в виде файлов с SQL командами, поэтому какое бы вы расширение для своего файла не выбрали — в любом случае его содержимое будет одинаковым. И не забудьте перед экспортом информации из MySQL проверить права на запись у директории, в которой он будет располагаться, чтобы создание файла было возможно. Если вдруг вам нужно будет сделать дамп со всеми базами данных на сервере, то используйте следующий вариант команды: Mysqldump -u имя_пользователя -p --all-databases > путь_и_имя_файла_дампа
Для создания дампа лишь для нескольких конкретных баз данных вам понадобится вызов mysqldump со следующими параметрами: Mysqldump -u имя_пользователя -p --databases имя_базы_данных1, имя_базы_данных2, ... > путь_и_имя_файла_дампа
В итоге, в каждом случае вы получите дамп базы MySQL, содержащий команды для создания структуры содержащихся таблиц (поля, их типы, индексы, ключи и т.д.), а также операции наполнения их данными. Данный вариант годится только для восстановления и копирования баз данных целиком. О том, как делать бэкапы определённых таблиц MySQL и получать их данные в читаемом виде, речь пойдёт дальше. Для создания дампа определённых таблиц MySQL базы данных нам понадобится всё та же утилита mysqldump
, вызываемая со следующими параметрами: Mysqldump -u имя_пользователя -p имя_базы_данных имя_таблицы1, имя_таблицы2, ... > путь_и_имя_файла_дампа
Ещё при вызове mysqldump можно указывать требуемые таблицы в качестве значения параметра —tables
, при использовании которого параметр —databases
будет игнорироваться: Mysqldump -u имя_пользователя -p --databases имя_базы_данных1, имя_базы_данных2 --tables имя_таблицы1, имя_таблицы2, ... > путь_и_имя_файла_дампа
Приведённый пример выведет на экран следующую ошибку: Mysqldump: Got error: 1049: Unknown database "имя_базы_данных1," when selecting the database
Как видите, будет использоваться только самая последняя БД из списка указанных. В принципе, такое поведение вполне логично, т.к. во всех БД указанных таблиц может не оказаться. Хорошо, дамп таблиц MySQL базы данных мы получили. Его можно использовать для их восстановления или копирования вместе со структурой. А что, если нужно получить просто хранимую в них информацию и, желательно, в читаемом виде, чтобы можно было её отправить менеджеру и просмотреть в обычном текстовом или табличном редакторе? У MySQL есть средства и для этого. Достичь задуманного нам поможет вариант вызова утилиты mysql
из консоли с определёнными параметрами: Mysql -u имя_пользователя -p имя_базы_данных -e "SELECT * FROM имя_таблицы"
Данная команда позволит нам выполнить запрос к требуемой БД и вывести результат в консоль, не заходя в командную строку MySQL. Ну, а для того, чтобы не выводить данные в консоль, а записать их в файл, нужно дополнить команду следующим образом: Mysql -u имя_пользователя -p -e "SELECT * FROM имя_таблицы" > путь_и_имя_файла
Благодаря данным конструкциям мы можем не только получить данные, хранящиеся во всех полях таблицы, но и в конкретных. Для этого достаточно вместо символа wildcards (*) прописать через запятую требуемые. В результате у нас на выходе получится обычный текстовый файл, который будет содержать названия полей в виде шапки и информацию по ним для всех записей. Его можно открыть в обычном текстовом редакторе, независимо от того, какое разрешение вы ему зададите при создании. Если же захотите экспортировать данные из MySQL базы в xls или csv формате, чтобы полученный файл корректно отображался в табличных редакторах, то о том, как это сделать будет рассказано немного позже 🙂 О том, как сделать дамп базы данных MySQL — одной и нескольких, а также отдельных их таблиц, мы поговорили. Но иногда на практике бывают случаи, когда нужно экспортировать набор данных, не ограниченный одной таблицей. Либо нужно выбрать лишь некоторые данные из таблицы. Особенно часто с таким сталкиваются разработчики корпоративных проектов, когда менеджеры просят их предоставить всякие статистические данные. Либо когда нужно сделать бэкап определённой части таблицы для её быстрого восстановления. Для бэкапа нам понадобится всё та же утилита mysqldump
, которую нужно будет вызвать в таком виде: Mysqldump -u имя_пользователя -p имя_базы_данных имя_таблицы --where "уточняющий_запрос" > путь_и_имя_файла_дампа
В результате мы получим файл с SQL командами для создания таблицы со всей его структурой, которая после создания будет наполняться информацией, выбираемой с помощью уточняющего запроса. Если нам будет нужно просто получить данные, хранящиеся в одной или нескольких таблицах, то нам потребуется модификация команды, используемой в предыдущем случае при выборке всех данных таблицы, только с некоторыми уточнениями: Mysql -u имя_пользователя -p -e "SELECT * FROM имя_таблицы WHERE уточняющий_запрос" > путь_и_имя_файла
Как вы понимаете, помимо различных уточнений, указываемых в запросе с помощью директивы WHERE
, можно использовать и прочие SQL конструкции: JOIN
, UNION
и т.д. Статистику собрать получится какую угодно 🙂 То же самое действие возможно произвести также при работе в командной строке MySQL с помощью следующей команды: SELECT * FROM таблица_базы_данных WHERE уточняющий_запрос INTO OUTFILE "путь_и_имя_файла";
Данная команда как раз и предназначена для создания файлов с результатами выборки. Причём, результаты можно не только экспортировать в файлы, но и записывать в переменные, а также выводимые данные можно форматировать различными способами. Если перечисленное — ваш случай, то с полным списком параметров и вариантов вызова данной команды вы можете ознакомиться здесь — https://dev.mysql.com/doc/refman/5.7/en/select-into.html В завершение своего краткого экскурса по mysqldump хочу привести вариант вызова команды со списком параметров для создания оптимизированного дампа базы MySQL и таблиц, восстановление БД и отдельных таблиц из которого будет занимать меньше времени, чем при обычном вызове: Mysqldump -u имя_пользователя -h хост_или_IP_сервера_MySQL -p --no-autocommit --opt имя_базы_данных > путь_и_имя_файла_дампа;
Ради эксперимента я использовал данный вариант для того, чтобы сделать дамп базы MySQL размером в 143 Мб. Последующее восстановление заняло 59 секунд времени против 1 минуты и 3 секунд, когда БД восстанавливалась из дампа, сделанного вызовом mysqldump без специальных параметров. Согласен, что это мелочь. Но это только в случае данного объёма данных. Если использовать данную методику при создании дампа размером более 1Гб, то разница будет более существенной. Если вы столкнётесь с такой ситуацией, то не забудьте ещё предварительно запаковать дамп базы MySQL в архив. Лучше всего tar.gz. Тогда восстановление займёт ещё меньше времени. Я не зря объединил информацию о выводе информации из MySQL в эти два формата в одном блоке, т.к. они очень похожи, используются примерно одинаково (для структурирования информации в виде таблиц) и команды для экспорта будут вызываться одни и те же. Как известно, единственным существенным различием между данными форматами является то, что расширение xls и xlsx имеют файлы, создаваемые в программе Microsoft Office Excel, которая работает только под Windows, а csv файлы являются более универсальными и операции с ними возможны во многих редакторах. Это не значит, что xls нигде, кроме Microsoft Office Excel, не откроется. Тот же OpenOffice подтверждает обратное. Но для такой возможности данная поддержка должна присутствовать в программном продукте. csv же файлы доступны для чтения даже в обыкновенном текстовом редакторе типа «Блокнот», только такая форма будет не совсем читабельной. Начну с того, что экспортировать в xls или csv можно только результаты SQL запросов, работать с которыми мы с вами научились ранее, т.к. всю базу данных в один файл будет невозможно вывести за одну операцию. Во-первых, это не оптимально, т.к. такой файл вряд ли откроется при больших объёмах хранящейся в БД информации. А, во-вторых, непонятно, как разбивать внутри файла информацию по таблицам и полям. Нет, сделать это, конечно, возможно, но вряд ли одной командой и вообще вряд ли кто-то будет таким заниматься в консоли. Думаю, что для данных целей понадобится специальное ПО или, как минимум, скрипт. Если вы вдруг знаете, как можно экспортировать информацию из всей MySQL БД сразу в один или несколько xls файлов в консоли, то напишите об этом в комментариях. Думаю, почитать об этом будет полезно многим. Итак, если мы говорим о том, как сделать экспорт данных из MySQL в xls и csv, то сделать это можно прямо в консоли сервера через утилиту mysql
либо в , работой с которой я знакомил вас в предыдущей своей статье. Начнём по порядку. Экспортировать данные из MySQL базы данных в csv и xls форматы прямо в консоли сервера можно следующими командами. На Linux
системах: Mysql -u имя_пользователя -d имя_базы_данных -p -e "SELECT * FROM таблица_БД;" | sed "s/"/\"/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > путь_и_имя_файла.csv
В принципе, при крайней необходимости можете сделать данной командой и экспорт данных MySQL в Excel файл. Но я, если честно, на практике данным не занимался и что выйдет в итоге — без понятия, т.к. работаю сейчас под Windows. Если будете пользоваться данной командой под Linux — напишите в комментариях, пожалуйста, о результатах вашей работы. Думаю, информация будет интересна всем. На Windows
: Экспорт данных из MySQL таблиц в csv приведённой выше командой в данном случае, к сожалению, не удастся, т.к. у Windows, в отличие от Linux, нет встроенной консольной команды для работы с потоками, какой является sed в Linux. Установить её, конечно, можно, но слишком много хлопот. Ещё, как вариант, можете использовать CygWin
— эмулятор консоли Linux для Windows систем. Хорошо, если она у вас уже установлена. В противном случае экспорт данных из БД MySQL выбранным способом принесёт нам слишком много хлопот. Зато извлечение информации в xls файл прост как 5 копеек 🙂 Запустить его очень просто следующим способом, который я опробовал лично: Mysql -u имя_пользователя -d имя_базы_данных -p -e "SELECT * FROM таблица_БД;" > путь_и_имя_файла.xls
Открылся данный файл в Microsoft Office Excel вообще без всяких проблем. Единственное, при открытии на экран вывелось сообщение с предупреждением о том, что действительный формат открываемого файла отличается от указываемого его расширения. Но при подтверждении действия документ открылся без трудностей — вся информация была разбита по ячейкам в том виде, в каком она хранилась в самой таблице. Не знаю, возможно, при произведении каких-либо специфических действий в Microsoft Office Excel и возникнут проблемы в дальнейшем, я так глубоко не копался. При обычном просмотре данных, по крайней мере, я ничего необычного не встретил. Если у вас в процессе использования экспортированного из MySQL xls файла возникнут какие-то проблемы либо в данной программе, либо в других, то дайте мне знать, пожалуйста, в комментариях. Описанным выше путём можно экспортировать содержимое БД MySQL и в csv файл, в принципе. Но тогда данные из разных полей таблицы будут записаны скопом, без разделителей, что может плохо отображаться в различных программах для работы с таблицами, в которых обычно с csv файлами и работают. OpenOffice, кстати, всё равно 🙂 Он автоматически разграничил информацию, полученную способом, которым мы экспортировали содержимое базы MySQL в xls. Не знаю, как он это делает — но рекомендую пользоваться 🙂 Ну, а тот же самый Microsoft Office Excel отобразил всю информацию, соответствующую одной записи в таблице, записав её в одну ячейку без каких-либо разделителей. Думаю, что подобным образом поступят и другие редакторы таблиц. Поэтому при экспорте базы данных MySQL в csv файлы нужно делать это, разделяя информацию специальными символами, воспринимаемыми редакторами. И тут я плавно подошёл ко второму способу экспорта MySQL данных в csv и xls, который заключается в использовании командной строки MySQL. Итак, для того, чтобы экспортировать данные MySQL в csv файл данным способом, нам нужна следующая команда: SELECT * FROM таблица_базы_данных
INTO OUTFILE "путь_и_имя_файла.csv"
FIELDS TERMINATED BY "," ENCLOSED BY """
LINES TERMINATED BY "\n";
В результате её выполнения вы получите csv файл по пути, прописанному вами при вызове, который будет корректно открываться в большинстве современных табличных редакторов. На всякий случай напоминаю, что запускать данную команду нужно только после соединения с базой данных MySQL. Данная команда также отлично подходит и для экспорта данных MySQL в xls файл для корректного отображения в Microsoft Office Excel. Только в этом случае разделители нам не нужны, т.к. они будут мешать в разбиении информации по ячейкам: SELECT * FROM таблица_базы_данных INTO OUTFILE "путь_и_имя_файла.xls";
Однако, на практике не всё так просто, как я описал. Во время выполнения команды вы можете столкнутся со следующей ошибкой в консоли, препятствующей выполнению экспорта: ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Она вызвана тем, что ваш MySQL сервер был запущен с опцией —secure-file-priv
. Лично я столкнулся с данной проблемой из-за того, что для работы в консоли пользуюсь дистрибутивом MySQL, входящим в комплект WAMP OpenServer, который, в свою очередь запускает MySQL сервер данным образом. Здесь есть два способа решения проблемы: Первый способ мне показался слишком сложным, т.к. пришлось бы копаться в конфигурации OpenServer, которая не мною была написана со всеми вытекающими из этого обстоятельствами 🙂 Поэтому я решил пойти вторым путём. Если вы столкнулись с подобной проблемой — повторяйте за мной. Сперва нужно зайти в командную строку MySQL и выполнить одну из следующих команд: SHOW VARIABLES LIKE "secure_file_priv";
SELECT @@GLOBAL.secure_file_priv;
Результатом выполнения обеих будет значение глобальной переменной MySQL secure_file_priv
, которая содержит путь к каталогу, через который могут осуществляться операции экспорта и импорта данных MySQL (в будущем ссылка на статью об импорте данных). Т.е. при использовании команд LOAD DATA
и SELECT … INTO OUTFILE
экспортируемые и импортируемые файлы могут располагаться только внутри данного каталога. В моём случае у данной переменной было вообще установлено значение NULL
, т.к. я, как уже говорил, пользуюсь для работы в консоли утилитами MySQL из дистрибутива, входящего в OpenServer. Данное значение указывало на то, что операции экспорта данных MySQL и импорта с помощью указанных команд были вообще закрыты. Как потом выяснилось, это распространённая ситуация в случае использования коробочных WAMP и MAMP серверов. К сожалению, привычными методами изменения значений глобальных переменных MySQL в моём случае воспользоваться не удалось: SET имя_переменной = значение;
В результате я увидел в консоли лишь следующую ошибку: ERROR 1238 (HY000) at line 1: Variable "secure_file_priv" is a read only variable.
В итоге, чтобы изменить значение переменной secure_file_priv
и открыть операции экспорта и импорта, мне потребовалось зайти в файл конфигурации MySQL mysql.ini, который расположен в корневой директории дистрибутива MySQL, или к нему можно получить доступ иным способом, если MySQL входит в комплект вашего WAMP/LAMP/MAMP сборки сервера. Вам, кстати, если захотите изменить путь к буферному каталогу обмена файлами, нужно будет сделать то же самое. В моём случае в конфиге данная переменная уже существовала, только в закомментированном виде: Secure-file-priv = "%dprogdir%\\userdata\\temp"
Если у вас её не будет, то пропишите её с нуля в секции
(по крайней мере, у меня она располагалась там). Я её раскомментил и решил использовать в том виде, в каком она была прописана. Т.е. при экспорте данных из MySQL и их импорте обратно файлы у меня теперь будут храниться в каталоге c:\openserver\userdata\temp\
. После изменения конфига (любого, кстати), не забывайте перезапускать ваш сервер либо отдельную службу, настройки которой вы правили, если есть такая возможность, чтобы изменения вступили в силу! Для уверенности, после перезапуска MySQL сервера ещё раз выводим на экран переменную secure_file_priv
и копируем её значение в буфер обмена. А теперь нам нужно вызвать команду, что и в начале, только перед названием файла, в который будет сохраняться информация из БД MySQL, прописать путь, хранимый в изменяемой нами переменной в следующем виде: SELECT * FROM таблица_базы_данных INTO OUTFILE "значение_secure_file_priv\имя_файла.csv";
После этого экспорт данных из MySQL в моём случае заработал. Важный момент! Если вы работаете с MySQL под Windows, то не забывайте при указании пути к файлу поменять «\» на «/», иначе ошибка с —secure-file-priv
всё равно продолжит выводиться. На этом статья о том, как сделать дамп базы MySQL и её таблиц, а также сделать вывод данных из MySQL таблиц в различные форматы, подходит к концу. Пишите в комментариях свои отзывы и делитесь со всеми своими вариантами скриптов, которыми вы пользуетесь на практике чаще всего. Если статья вам понравилась — вы можете поблагодарить автора репостом статьи в социальные сети или финансово с помощью формы ниже, чтобы была возможность элементарной оплаты хостинга. Всем удачи и до новых встреч! 🙂 P.S.
: если вам нужен сайт либо необходимо внести правки на существующий, но для этого нет времени и желания, могу предложить свои услуги. Более 5 лет опыта
профессиональной разработки сайтов. Работа с PHP
Большую часть времени, вы можете легко управлять своей базы данных с веб-инструментов, например через PHPMyAdmin. К сожалению, бывают случаи, когда необходимо восстановить большое количество данных. Это не может быть достигнуто через PHPMyAdmin из-за пределов РНР (либо максимальное время работы скрипта не позволяет или невозможно загрузить большой архив с БД). Если вы используете MySQL на Linux или MS Windows, вы можете использовать командную строку для выполнения большинства задач. Если вы используете MS Windows, убедитесь, что путь к исполняемым файлам командной строки входит в системном пути. В противном случае, вам придется ввести полный путь к каждой команды. MySQL предоставляет утилиту под названием «mysqldump.» В принципе, этот инструмент создает плоский файл, содержащий инструкции MySQL, чтобы восстановить базу данных. Вот несколько примеров использования mysqldump: Это создает файл, содержащий все операторы MySQL для создания таблиц и восстановление данных в существующую базу данных. Если целевая база данных содержит таблицы с одинаковыми именами, они будут перезаписаны. Если вы хотите существующие таблицы, которые будут отброшены и заново использованы, то используйте опцию » add-drop-table»:
# mysqldump --add-drop-table -u username -p DB_name > your_file.sql
Кроме того, вы можете выбрать это в качестве альтернативы:
# mysqldump --add-drop-databases -u username -p DB_name > your_file.sql
Вы можете сделать копию нескольких баз данных в один файл, используя опцию базы данных:
# mysqldump -u username -p --databases your_database1 your_database2 your_database3 > your_file.sql
Создание резервной копии всех баз данных может быть достигнуто с помощью опции все-базы данных:
# mysqldump -u username -p --all-databases > your_file.sql
Если в базе данных есть столбцы InnoDB, вам нужно будет отключить ссылочную целостность при восстановлении данных. К сожалению, это не может быть сделано с помощью утилиты mysqldump. Для этого нужно открыть сам файл и добавить: SET FOREIGN_KEY_CHECKS = 0;
Если вы используете mysqldump в оболочке Linux, вы можете сжать через Gzip, чтобы сжать файл дампа (при условии, что вы установили gzip):
# mysqldump -u username -p your_database_name | gzip -c your_file.sql.gz
Чтобы восстановить резервную копию, созданную с mysqldump, вам нужно будет использовать команду MySQL. Если ваш файл дампа MySQL не содержит «Создать базу данных» о, вы можете использовать следующую команду:
# mysql -u username -p your_database_name < your_file.sql
Но, если это выполнится, использовать ту же команду без указания имени базы данных:
# mysql -u username -p < your_file.sql
Более подробную информацию о mysqldumpможно найти на официальном сайте MySQL. Наверяка вам не раз приходилось импортировать дампы базы данных. И не смотря на всю тривиальность этой задачи, часто возникают вопросы, а иногда и проблемы связанные с этим. Ниже приведен весь код, который необходим, чтобы быстро применить дамп базы из командной строки. SET NAMES utf8;
DROP DATABASE `mydb`;
CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8;
USE `mydb`;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET autocommit=0;
SET foreign_key_checks=0;
SET unique_checks=0;
SOURCE /path/to/db_dump.sql;
COMMIT;
SET autocommit=1;
SET foreign_key_checks=1;
SET unique_checks=1;
Эти команды позволят вам быстро импортировать дамп базы данных и избежать проблем. Причем некоторые из этих проблем не решают даже распространенные mysql-клиенты, такие как phpMyAdmin и другие. Попробуем разобрать данный код по порядку. SET NAMES utf8;
говорит, что обмен данными будет происходить в кодировке UTF-8 и именно в ней находится исходный файл дампа базы и именно её будет иметь конечная база данных. DROP DATABASE `mydb`;
и CREATE DATABASE `mydb` DEFAULT CHARACTER SET utf8;
выполняют уничтожение
базы данных и её последующее создание с кодировкой по умолчанию UTF-8. Понятное дело, что это сотрет все таблицы и данные в них, но это и даст нам уверенность в том, что полученная БД будет полностью соответствовать данным в файле дампа. USE `mydb`;
указывает, что надо использовать именно эту базу данных для всех команд из файла дампа (проверьте файл дампа на наличие этой директивы с указанием другой базы, дабы избежать конфуза). SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
отключает увеличение счетчика авто-инкремента при записи в поле авто-инкремента значения 0. По умолчанию, при записи в такое поле значения NULL или 0 оно автоматически инкрементируется. И хотя иметь значение 0 в авто-инкрементном поле - не лучшая практика, иногда все-таки случается что в дампе есть такие строки (я встретился с подобным в дампе от magento). Стоит также заменить, что mysqldump с некоторых пор включает данную директиву по умолчанию в своих дампах. SET autocommit=0;
перед заливкой дампа и COMMIT;
после него - ускоряют выполнение INSERT"ов для InnoDB таблиц, поскольку при включенном режиме авто-коммита логи сохраняются на диск при каждом INSERT"е. Может помочь при накатывании больших дампов. SET foreign_key_checks=0;
наиболее очевидная опция, без которой большая часть дампов вообще не могла бы быть применена. Отключает проверки внешних ключей, что позволяет записывать данные в любом порядке и в том числе с циклическими ссылками по внешним ключам. Эта опция также ускоряет импорт дампа. SET unique_checks=0;
отключает проверки на уникальность для вторичных ключей, что опять таки может ускорить процесс импорта. Ну и наконец, основная команда, ради которой все это было задумано - SOURCE /path/to/db_dump.sql;
непосредственно импортирует дамп базы mysql из указанного файла. Путь до файла может быть абсолютными, либо относительным от той директории, откуда вы зашли в консоль mysql. А после выполнения импорта - восстанавливаем значение флагов, хотя этого можно и не делать, ибо команда SET
в mysql по умолчанию изменяет значения переменных только в контексте текущей сессии.Создание дампа базы MySQL через консоль
Делаем дамп таблицы MySQL и экспорт данных
Создание бэкапов и вывод данных из MySQL базы с помощью запросов
Экспорт данных из MySQL в Excel и csv файлы
Использование MySQL с командной строки
Как сделать резервную копию базы данных MySQL
Создание простого резервной копии базы данных
# mysqldump -u username -p DB_name > your_file.sql
Резервное копирование нескольких баз данных
Резервное копирование таблиц InnoDB
… И добавить следующее в конец файла:
SET FOREIGN_KEY_CHECKS = 1;
Сжатие MySQL Dump файлов
Восстановление из резервной копии MySQL