Восстановление многотомного дампа mysql sql. Делаем дамп (бэкап) базы данных, используя утилиту mysqldump. Что такое дамп
./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 немаловажно для любого веб-разработчика. Что это такое? Это файл, содержащий инструкции на языке SQL, за счет которых создается точная копия вашей БД как по содержанию, так и по структуре. Для каких ситуаций вам стоит сделать дамп базы MySQL: Дамп может понадобиться, если вы запланировали перенос базы данных на другой хостинг. С его помощью вы сможете без каких-либо трудностей воссоздать точную копию старой базы данных. Виртуальный хостинг сайтов , на котором включено ежедневное резервное копирование. Регулярное резервное копирование данных сэкономит вам немало сил и нервов в случае непредвиденных ситуаций. Неудачный эксперимент с сайтом или крах сервера способен привести к потере всей базы, и лишь вовремя сделанный дамп поможет вам в этой ситуации. Естественно, при этом его нельзя хранить на одном сервере с основной базой, ведь в противном случае вы потеряете всю информацию без возможности ее восстановления. Существуют различные способы создания дампа, и далее мы рассмотрим основные варианты: В этом случае для создания дампа БД используется командная строка или консоль MySQL, где нужно ввести команду mysqldump -uuser -ppass db_name > file_to_save. При этом user - это имя пользователя БД с достаточными правами для создания дампа, pass - пароль от базы данных, db_name - имя нужной БД, а вместо file_to_save необходимо указать имя файла, куда будет сохраняться дамп. После правильно введенной команды в указанном месте появится файл с расширением.sql, который и является дампом базы данных. Этот способ наиболее универсален и популярен среди пользователей Unix-систем, например, Ubuntu, если вдруг потребуется перенос MySQL на другой сервер. В том же случае, если вы не знаете консольных команд, вам потребуется дополнительное программное обеспечение. Если вы предпочитаете использовать дополнительное программное обеспечение, то можно сделать дамп базы данных средствами PHP при помощи, например, phpMyAdmin. Для этого выполните следующие действия: Добавить DROP TABLE/VIEW/PROCEDURE/FUNCTION/EVENT Данная опция позволит вам в дальнейшем без опаски восстановить базу MySQL из дампа. Все таблицы, имеющие одинаковые имена, сначала будут удалены, то есть исполнится команда dpop, а затем заменятся на аналогичные из вашего дампа. В итоге вы обезопасите себя от создания дублей таблиц и как следствие, неправильной работы базы данных. Упаковать zip Включение этой опции обеспечит архивацию вашего дампа БД, и в итоге вы получите файл с расширением.zip. Это позволит вам быстро и корректно сохранить дамп даже при невысокой скорости соединения. Теперь вы знаете, как сделать дамп MySQL, если вам вдруг потребуется перенос базы на другой сервер или возникнет любая другая ситуация, когда может пригодиться резервная копия БД. Конечно, существует еще достаточно много способов создания дампа, помимо описанных выше. Но даже владение самыми простыми вариантами и регулярное применение их на практике позволит вам более уверенно чувствовать себя в любой непредвиденной ситуации, ведь достаточно будет просто развернуть дамп MySQL и быстро восстановить все базы данных. Если вы решились купить dedicated server в нашей компании, то Вам выдается 100 ГБ на удаленном сервере бекапов, куда можно настроить автоматическое резервное копирование сайтов и баз данных. В таком случае, у вас всегда будет свежий дамп MySQL. В том случае, когда у вас все же возникают сложности при создании дампа и смене сервера или хостинга, вы можете обратиться к сотрудникам техподдержки RigWEB, и они ответят на любые ваши вопросы в рамках своей компетенции. Если же вы по каким-то причинам не можете заниматься этим самостоятельно, наши специалисты бесплатно займутся переносом вашего сайта с другого хостинга на наш. Также мы обеспечим регулярное резервное копирование и своевременное предоставление актуальных копий ваших данных при необходимости. Пользуясь профессиональным хостингом RigWEB, вы можете быть уверены в максимальной защите своего проекта от любых неожиданностей! Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как mysqldump. mysqldump - утилита позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц. mysqldump
-u root -p -f name_database > Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt Файл можно не создавать, MySQL создаст его сам. mysql
-u root -p -f name_database < C:\mydb_backup_name_database.txt этой командой мы импортируем данные бэкапа из файла C:\mydb_backup_name_database.txt Примечание: -f, --force - опция, которая указывает продолжать даже при получении ошибки SQL, т.е. игнорировать ошибки. Например, если в таблице уже существует во всем идентичная строка. Чтобы пароль не запрашивался, нужно писать его сразу после -p, то есть без пробелов. Если пароль Pwd
, то пример выгладит так: mysqldump
-u root -pPwd
-f name_database > C:\mydb_backup_name_database.txt Если вы часто используете данную команду, то лучше сделать отдельного пользователя с необходимыми правами, дабы поменьше светить пароль root-а Рассмотрим более тонкие настройки mysqldump: --databases
позволяет сделать так, что mysqldump включит в сценарий восстановления команды CREATE DATABASE /*!33333 IF NOT EXISTS*/ DBNAME и USE DBNAME. Это позволит создавать рабочие базы "с нуля". То есть, без использования --databases подразумевается, что пользователь восстанавливает одну базу данных и явно указывает, куда нужно помещать восстанавливаемые данные. Если же backup создается с целью сделать полностью рабочую копию данных, например, на другом MySQL-сервере, то нужно использовать этот ключ; --all-databases
позволяет сделать копии всех баз данных, которые существуют на данном MySQL-сервере. Если же нужно сделать копии только некоторых баз, нужно просто указать их через пробел при вызове mysqldump из командной строки (см. выше); Ключ --help
. Программа mysqldump имеет множество версий. Посмотреть, какие возможности поддерживаются конкретно Вашей версией, можно с помощью этого ключа; --add-drop-table
- ключ, который заставит mysqldump добавлять в итоговый сценарий команду drop table перед созданием таблиц. Это позволит избежать некоторых ошибок при восстановлении базы из резервной копии. Конечно, нужно учитывать то, что таблицы, находящиеся в рабочей копии (если таблицы с таким же именем существуют в backup), перед восстановлением из резервной копии будут удалены из основной базы и пересозданы из backup; --no-data
. С помощью этого ключа можно быстро сделать копию структуры таблицы/баз без самих данных. Например, Вы создали сложную таблицу и хотели бы сохранить на будущее ее структуру, а сами данные, которые находятся в этой таблице, Вам в резервной копии не нужны; --result-file=...
- этот ключ можно использовать для перенаправления вывода в файл. Можно использовать обычное unix-перенаправление командой ">", а можно - вот этот ключ. Кому что нравится; Еще один очень полезный совет по использованию mysqldump в хостинговой среде. Как правило, при использовании хостинга на пользователя налагаются некоторые ограничения. Например, нельзя занять больше некоторого количества физической памяти (RAM, ОЗУ). mysqldump по умолчанию помещает все полученные от MySQL-сервера данные в память, а потом записывает все это на диск. Соответственно, если провайдер дает Вам занять, например, 30Мб памяти, а база, копию которой Вы делаете с помощью mysqldump, занимает 50Мб, конечно, тут возникнет ошибка - mysqldump не сможет отработать корректно и завершится аварийно, о чем Вам сообщит. Чтобы "заставить" mysqldump писать данные сразу на диск, а не хранить их, пусть даже и временно, в памяти, используйте ключ --quick
. Это решит проблему. Приведем еще пару полезных примеров:
mysqldump -u root -pPwd
-f --default-character-set=cp1251
DBNAME
| gzip -c > filename.txt
.gz распаковывать такой архив можно командой: gunzip filename.txt
.gz Чтобы знать, за какое число был сделан бэкап, можно написать такую команду: mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date "+%Y-%m-%d"`.gz а если приходится делать дампы с разными кодировками, то удобно пользоваться переменными: set DBCHARACTER
= utf8 set DBNAME
= breach mysqldump -u root -pPwd
-f --default-character-set=$DBCHARACTER
$DBNAME
| bzip2 -c > sql.$DBNAME
.`date "+%Y-%m-%d"`.bz2 Если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически find
~/каталог-с-архивами -name "*.gz" -mtime +7 -exec rm -f {} \; Тем самым Вы будете удалять архивы, которые "старше" семи дней. Приветствую вас, друзья! 🙂 Сегодня я решил продолжить разговор о работе с 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
Странно, но в интернете найти эту информацию довольно непросто. Куча левых всяких рекомендаций. А на самом деле всё просто. Восстановить дамп можно командой: # 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?
Создание дампа базы MySQL через консоль
Делаем дамп таблицы MySQL и экспорт данных
Создание бэкапов и вывод данных из MySQL базы с помощью запросов
Экспорт данных из MySQL в Excel и csv файлы
Автор
Алекс Разгибалов