MySQLdump скачать, работа с mysqldump, примеры


Содержание

Дамп и восстановление базы данных MySQL

Дамп и восстановление базы данных MySQL довольно просто и удобно делать удаленно через SSH или прямо через консоль сервера. Удаленно, это можно делать используя программы Putty/Kitty. Также указанные ниже примеры Вы можете выполнять и на Windows запустив командную строку ‘cmd‘. Ниже приведены примеры о том, как создавать дампы базы данных MySQL и затем восстанавливать их при необходимости, например для Вашего сайта, интернет-магазина или какого либо другого проекта.

Создание дампа базы данных MySQL

Для того, чтоб выполнять данные команды, подключитесь удаленно к Вашему серверу через SSH используя одну из перечисленных выше программ. После подключения и авторизации к серверу/хостингу, Вы можете вводить приведенные ниже команды.

В приведенном выше примере, для создания бекапа используется утилита mysqldump, которая входит в состав mysql. Далее указываются параметры для создания бекапа базы данных, которые разберем подробнее:

  • -u – параметр указывает логин, который будет использоваться для подключения к базе данных. В примере мы используем логин root, который нужно указать в этом параметре без пробела! В результате у нас это выглядит как -uroot
  • -p – параметр указывает что нужно ввести пароль для указанного логина. Мы его оставили пустым, в результате чего пароль нужно будет ввести после нажатия “Enter” при выполнении команды. Тем не менее, можно указать пароль сразу же здесь, как и в параметре логина, без пробела после -p, однако этот способ не является безопасным, так как консоль сохраняет Ваши команды в лог файл и если Вы его регулярно не очищаете, то он может быть просмотрен злоумышленником.
  • your_base – вместо этой строки в примере, вам необходимо указать реальное имя Вашей базы данных, для которой Вы создаете бекап.
  • > – оператор который показывает направление действия, т.е. как бы указывает, что вы собираетесь сделать запись из базы в файл.
  • dump_file.sql – это название Вашего файла .slq в которую нужно сохранить Вашу базу данных. Он указывается через пробел после оператора ‘>’. Вы можете задать любое другое имя. Например, чтобы в имени система автоматически вставила текущее время, достаточно указать строку вида:

Внимание! Если Вы указываете только имя файла, то он будет сохранен в той же директории, относительно которой Вы выполняете данную команду. Т.е. если Вы видите в строке приглашения ввода команд что-то вроде [root@dvs home]#, где root@dvs это логин и имя сервера, то файл будет создан в директории /home. Чтобы изменить сохранение файла по другому пути, укажите вместо имени полный путь для сохранения файла, например: /var/www/backup/dump_file.sql.

  • Во втором примере, вместо оператора ‘>‘ используется оператор ‘|‘, который указывает на необходимость выполнения дополнительной команды gzip c параметром ‘-c‘ которая позволяет сразу же запаковать дамп в архив, а только затем сохранить его в файл вида ‘2014-11-15.gz‘, о чем сообщает оператор ‘>‘.
  • Параметр –no-data позволяет создать дамп только структуры базы данных без самих данных. В некоторых случаях довольно полезно, когда данные не нужны.
  • Параметры –default-character-set=utf8 и –extended-insert=FALSE. Первый позволяет Вам явно указать кодировку, которая используется этой базой данных, тем самым избежать сохранение базы в неверной кодировке Вместо utf8 можно указать любую другую кодировку, например cp1251 . Второй параметр позволяет указать, что при экспорте для каждой записи необходимо создать отдельную команду INSERT. В некоторых случаях это может потребоваться при частичном восстановлении данных из дампа.
  • Восстановление базы данных из файла дампа MySQL

    Теперь рассмотрим с Вами обратный процесс восстановления базы данных из файла дампа. Данное действие выполняется при помощи программы mysql. Рассмотрим сразу же пример.

    mysqldump — бекап/дамп данных в Mysql

    Mysqldump — это одна из утилит, входящих в пакет с клиентскими программами mysql-client. Используется для создания дампа одной или нескольких баз данных, отдельных таблиц или только их структуры с целью резервирования нужной информации и дальнейшего его восстановления в будущем. Дамп содержит в себе набор SQL-команд, которые выполняются последовательно при разворачивании.

    При запуске mysqldump в качестве аргумента передается название базы данных либо ее определенные таблицы или перечисляются несколько баз с помощью ключа «—databases -B» либо все «—all-databases -A». Также можно указывать дополнительные опции, наиболее полезными из которых являются:
    —quick -q – дамп непосредственно направляется на stdout (стандартный вывод — экран), не используя буфер;
    —opt – соответствует заданию опций —quick —add-drop-table —add-locks —extended-insert —lock-tables, которые максимально ускоряют создание дампа;
    —add-drop-table – в полученном дампе перед SQL-командами создания добавляется команда удаления таблицы (drop table);
    —add-locks – добавляются строки для блокирования «LOCK TABLES» и разблокирования «UNLOCK TABLE» таблиц при их создании чтобы не получить какие-либо противоречия в результате;
    —extended-insert -e – используется определенный синтаксис SQL-команды INSERT для более быстрого разворачивания данных;
    —lock-tables -l – заблокировать все таблицы перед стартом дампа, что благоприятно влияет на паралельные вставки при типе таблиц MyISAM;
    —all -a – включить все MySQL-специфичные параметры SQL-команды CREATE;
    —complete-insert -с – используется в команде INSERT задание имен столбца;
    —force -f – не останавливаться, если получаем SQL-ошибку;
    —no-data -d – для получения дампа только структуры таблиц;
    —no-create-db -n – ‘CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;’ не будет помещена в вывод;
    —no-create-info -t – не писать информацию для создания таблицы (CREATE TABLE);
    —password=password -ppassword, -P 3306 —port=3306, -S /var/lib/mysql/mysql.sock —socket= /tmp/mysql.sock, -u user —user=user – указать пароль, порт или сокет, пользователь для подключения к mysql-серверу;

    В самом обычном и стандартном случае используется:

    mysqldump —opt database > backup_database.sql – для создания дампа БД database;
    mysql database —add-drop-database Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE. —add-drop-table Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE. —add-locks Добавляет оператор LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL). —all-databases, -A Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера. —allow-keywords Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца. —comments, -i Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL. —compact Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным —comments. —compatible=name Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми. —complete-insert, -c Используется полная форма оператора INSERT (с именами столбцов). —create-options Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры. —databases, -B Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп. —delayed Использовать команду INSERT DELAYED при вставке строк. —delete-master-logs На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «—master-data». —disable-keys, -K Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных. —extended-insert, -e Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода). —flush-logs, -F Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. —force, -f Продолжать даже если в процессе создания дампа произошла ошибка. —hex-blob Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0×616263. —ignore-table=db_name.tbl_name Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров. —insert-ignore Добавляет ключевое слово IGNORE в оператор INSERT. —lock-all-tables, -x Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных. —lock-tables, -l Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп. —no-autocommit Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных. —no-create-db, -n Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров —databases и —all-databases. —no-data, -d Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных. —opt Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: —quick —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —set-charset. Начиная с MySQL 4.1, параметр —opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров —skip-opt —order-by-primary Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу. —port, -P Номер TCP порта, используемого для подключения к хосту. —protocol= Параметр позволяет задать протокол подключения к серверу. —quick, -q Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память. —quote-names, -Q Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию. —replace Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3. —result-file=/path/to/file, -r /path/to/file Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>. —routines, -R Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2. —single-transaction Параметр создает дамп в виде одной транзакции. —skip-comments Данный параметр позволяет подавить вывод в дамп дополнительной информации. —socket=/path/to/socket, -S /path/to/socket Файл сокета для подсоединения к localhost. —tab=/path/, -T /path/ При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров —fields-xxx и —lines-xxx. —tables Перекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц. —triggers Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers. —events, -E Создается дамп событий. Смотрите MySQL Event Scheduler или встроенный диспетчер событий в MySQL. —tz-utc при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=’+00:00′, который позволит обмениваться дампа в различных временных зонах. —verbose, -v Расширенный режим вывода. Вывод более детальной информации о работе программы. —version, -V Вывести информацию о версии программы. —where=’where-condition’, -w ‘where-condition’ Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. —xml, -X Представляет дамп базы данных в виде XML. —first-slave, -x Блокирует все таблицы во всех базах данных. —debug=. -# Отслеживать прохождение программы (для отладки). —help

    Вывести справочную информацию и выйти из программы.

    Еще пару слов о бекапе в MySQL

    mysqlhotcopy для MyISAM

    Для быстрого резервирования БД с типом таблиц ISAM и MyISAM можно использовать «mysqlhotcopy», которая скопирует файлы *.frm, *.MYD и *.MYI:

    Для InnoDB не подойдет данный способ, потому что при этом типе не обязательно все файлы будут храниться в директории базы данных.

    xtrabackup для InnoDB

    Для InnoDB есть xtrabackup, рекомендую посмотреть! UPD: XtraBackup — резервное копирование для innoDB

    Бин-лог и репликации

    Для репликации «mysqldump» не предназначена, для этого есть бин-лог (—log-bin):

    Для полной репликации необходимо вести бин-лог с самого начала работы БД, то есть еще до создания структур и данных.

    Читайте другие интересные статьи

    Понравилась статья, расскажи о ней друзьям, нажми кнопку!

    Создание дампа в mysql для нескольких таблиц. MySQLdump: скачать, работа с mysqldump, примеры. Примеры использования mysqldump

    Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как mysqldump.

    mysqldump — утилита позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

    Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt

    Файл можно не создавать, MySQL создаст его сам.

    mysql -u root -p -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

    распаковывать такой архив можно командой:

    Чтобы знать, за какое число был сделан бэкап, можно написать такую команду:

    mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date «+%Y-%m-%d»`.gz

    а если приходится делать дампы с разными кодировками, то удобно пользоваться переменными:

    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. Запуская периодически

    /каталог-с-архивами -name «*.gz» -mtime +7 -exec rm -f <> \;

    Тем самым Вы будете удалять архивы, которые «старше» семи дней.

    Идея статьи, создать качественное руководство по установке и работе с программой mysqldump. Применяя, множество примеров и собрав все полезные опции и параметры по работе с этой утилитой. Все в одну статью сложно вместить, поэтому все будет кратко и по сути. Если кто-то, чего-то недопонимает, обращаемся в комментарии к статье.

    Разделы:

    Что такое mysqldump?

    MySQLdump – это серверное приложение, которое позволяет делать резервное копирование (далее дамп) баз данных и сохранять их в отдельном файле. При этом можно осуществлять гибкие настройки дампа: несколько или все базы данных, архивация в gzip, добавление команд lock, drop и многое другое. Также возможнен обратный импорт резервных копий БД. Осуществлять можно с помощью PHP, но это неприемлемо для больших проектов, которые имеют большой вес данных.

    Эта программа очень полезна при реализации экспорта и импорта данных с БД. Она может быть стандартно установленной на вашем хостинге (точнее mysql сервере). Но для того, чтобы отточить мастерство работы с mysqldump и научится устанавливать, можно поставить ее на denwer. Что мы сейчас и сделаем.

    Скачать mysqldump

    Вы всегда можете скачать программу mysqldump у нас на сайте, при этом скачивание приложения совершенно бесплатно. Скачайте mysqldump по прямой ссылке ниже.

    Как установить mysqldump?

    Устанавливать будем на локальный сервер Denwer. Установить приложение легко и просто, для этого следуйте ниже предоставленным инструкциям и скриншотам.

    1.Копируем файл mysqldump.exe в папку с денвером:

    D:\WebServers\usr\local\mysql5\bin\
    При этом у вас может быть немного другое название папки mysql5, например mysql-5.1 или немного видоизмененное. Поэтому, для большей ясности, ниже предоставлен скриншот:

    2.Запускаем денвер

    Наверняка вы и сами знаете как запустить Denwer.

    3.Запускаем консоль:

    Пуск->Выполнить-> cmd. exe или в ОС Windows 7: Пуск->Поиск->Вводим cmd. exe-> Enter , как показано на скриншоте:

    4.Тестируем:

    С помощью команд в консоле, переходим на виртуальных диск денвера (у меня W:\) и в папку с приложением mysqldump. Для подтверждения выполнения команды жмем Enter.

    W: — заходим на виртуальный диск денвера

    cd usr\ local\ mysql5\ bin – заходим в папку с приложением

    mysqldump -uroot имя_вашей_бд>имя_файла.sql – тестируем, делаем дамп произвольной базы данных в файл, который сохранится в папку bin.

    У меня установка выполнена успешно, надеюсь у вас тоже. В папке bin находим файл резервной копии БД. Чтобы научится более широко использовать программу, читайте следующие разделы статьи.

    Начало работы: экспорт и импорт БД

    Экспорт базы данных

    Мы уже установили приложение и научились пользоваться консолем. Тестовый дамп мы уже делали. Теперь сделаем простой дамп базы данных в нужный нам каталог. Для этого я создал ранее используемую БД под именем “test”. Она находится на локальном сервере denwer. Ниже предоставлены пошаговые команды консоля для дампа бд test в нужную папку и нужный файл.

    W: cdusr\local\mysql5\bin mysqldump -uroot test>D:\test\easydump.sql


    На скриншоте ниже показан дамповый файл в папке тест:

    Экспорт выполнен успешно. Теперь попробуем импортировать этот файл обратно на наш сервер.

    Импорт базы данных

    Для импорта БД, очищаем БД в phpmyadmin, и пользуемся следующей командой в cmd.exe:

    Mysql -uroot test /path/to/file/dump.sql

    -u или —user=. — имя пользователя

    -h или —host=. — удаленный хост (для локального хоста можно опустить этот параметр)

    -p или —password — запросить пароль

    database — имя экспортируемой базы данных

    /path/to/file/dump.sql — путь и файл для дампа

    Делаем дамп нескольких баз данных, для этого используем атрибут —databases или сокращенно –B, смотрите на примере ниже:

    Mysqldump -uroot -h82.82.82.82 -p -B database1 database2 database3 > databases.sql

    Если вы желаете создать дамп всех баз данных, необходимо использовать параметр –all-databases или –А в сокращенном виде, смотрим пример:

    Mysqldump -uroot -h82.82.82.82 -p -A > all-databases.sql

    Создаем структуру базы без данных

    Для этого необходимо использовать параметр —no-data как показано на примере ниже:

    Mysqldump —no-data — uUSER -pPASSWORD DATABASE > /path/to/file/schema.sql

    Создаем дамп только одной или нескольких таблиц БД

    Создаем дамп и архивируем его в gzip

    Создаем дамп с указанием даты в имени файла

    Используем дополнительные атрибуты

    Как уже упоминалось раньше, эти атрибуты уменьшают итоговый размер файла и ускоряют процесс резервного копирования. А точнее:

    -Q оборачивает имена обратными кавычками

    -c делает полную вставку, включая имена колонок

    -e делает расширенную вставку.

    Данная утилита позволяет получить дамп («моментальный снимок»») содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

    Если же резервная копия создается на сервере, то вместо описываемой утилиты следует использовать mysqlhotcopy . См.раздел See section 4.8.6 mysqlhotcopy , Копирование баз данных и таблиц MySQL .

    Shell> mysqldump database или mysqldump —databases DB1 или mysqldump —all-databases

    Если не указывать имена таблиц или использовать параметры —databases или —all-databases , то будет получен дамп базы данных в целом (соответственно — всех баз данных).

    Перечень опций, поддерживаемых вашей конкретной версией утилиты mysqldump , можно получить, выполнив команду mysqldump —help .

    Следует иметь в виду, что утилита mysqldump , используемая без опций —quick или —opt , перед тем, как сделать дамп результата выборки информации, загрузит весь результат в память. Это может создать проблемы при получении дампа большой базы данных.

    Учтите, что не следует применять параметры —opt или -e , если вы собираетесь использовать для получения дампа новую копию программы mysqldump , а затем воспроизводить его на очень старом MySQL-сервере.

    Утилита mysqldump поддерживает следующие опции:

    Add-locks Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL). —add-drop-table Добавить команду DROP TABLE перед каждой командой CREATE TABLE . -A, —all-databases Произвести дамп всех баз данных. Аналогично опции —databases с указанием всех баз данных. -a, —all Включить все опции создания объектов, специфичные для MySQL. —allow-keywords Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца. -c, —complete-insert Использовать полные команды INSERT (с именами столбцов). -C, —compress Использовать сжатие всей информации между клиентом и сервером, если они оба поддерживают сжатие. -B, —databases Выполнить дамп нескольких баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных. —delayed Использовать команду INSERT DELAYED при вставке строк. -e, —extended-insert Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода). -#, —debug[=option_string] Отслеживать прохождение программы (для отладки). —help Вывести справочную информацию и выйти из программы. —fields-terminated-by=. —fields-enclosed-by=. —fields-optionally-enclosed-by=. —fields-escaped-by=. —lines-terminated-by=. Эти опции используются совместно с параметром -T и имеют то же самое значение, что и соответствующие операторы для LOAD DATA INFILE . См. раздел See section 6.4.9 Синтаксис оператора LOAD DATA INFILE . -F, —flush-logs Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. -f, —force, Продолжать даже при получении ошибки SQL при выполнении дампа таблицы. -h, —host=.. Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию — localhost . -l, —lock-tables. Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL , чтобы разрешить параллельные записи для MyISAM -таблиц. Следует отметить, что при выполнении дампа совокупности баз данных опция —lock-tables блокирует таблицы каждой базы по отдельности. Таким образом, использование этого параметра не гарантирует, что таблицы будут логически непротиворечивы в пределах этих баз данных. В различных базах данных при выполнении дампа таблицы могут находиться в совершенно разных состояниях. -K, —disable-keys Добавляет выражение /*!40000 ALTER TABLE tb_name DISABLE KEYS */; и /*!40000 ALTER TABLE tb_name ENABLE KEYS */; в выводе результата. Это ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются после внесения всех данных. -n, —no-create-db В выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; будет отсутствовать. Данная строка будет добавлена в любом случае при использовании опций —databases или —all-databases . -t, —no-create-info Не записывать информацию о создании таблицы (команда CREATE TABLE). -d, —no-data Не записывать информацию из строк таблицы. Это очень полезно для получения дампа структуры таблицы! —opt То же, что и —quick —add-drop-table —add-locks —extended-insert —lock-tables . Должно дать наиболее быстрый дамп для чтения на MySQL-сервере. -pyour_pass, —password[=your_pass] Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqldump предложит ввести пароль. -P port_num, —port=port_num Номер порта TCP/IP, используемого для подключения к хосту (применяется при подсоединении к хостам, отличным от localhost , для которого используются сокеты Unix). -q, —quick Выводить дамп непосредственно на стандартный вывод stdout без буферизации запроса. Для этого используется функция mysql_use_result() . -Q, —quote-names Взять в кавычки имена таблиц и столбцов без символов «» . -r, —result-file=. Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS, так как она предотвращает преобразование символа новой строки «\n» в последовательность «\n\r» (новая строка + возврат каретки). —single-transaction Данная опция выдает SQL-команду BEGIN перед выполнением дампа данных с сервера. Наиболее часто используется с InnoDB -таблицамии и уровнем изоляции транзакций READ_COMMITTED , так как именно в этом режиме можно получить дамп с непротиворечивым состоянием базы данных после выполнения команды BEGIN без блокирования каких-либо приложений. Используя эту опцию, необходимо помнить, что при выполнении дампа только транзакционные таблицы будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM — или HEAP -таблицы при использовании данной опции могут все же изменить свое состояние. Опция —single-transaction добавлена в версии 4.0.2. Она является взаимоисключающей по отношению к опции —lock-tables , так как команда LOCK TABLES прерывает выполнение предыдущей транзакции. -S /path/to/socket, —socket=/path/to/socket Файл сокета для подсоединения к localhost (значение хоста по умолчанию). —tables Перекрывает параметр —databases (-B). -T, —tab=path-to-some-directory Для каждой заданной таблицы создает файл a `table_name.sql» , содержащий SQL CREATE команды для создания таблицы, и файл `table_name.txt» с данными таблицы. Файл `.txt» имеет формат в соответствии с параметрами —fields-xxx и —lines—xxx . Примечание : Этот параметр работает только при условии, что утилита mysqldump запущена на том же компьютере, что и демон mysqld , причем пользователь/группа, запустившие данный поток mysqld (обычно это пользователь mysql и группа mysql), должны иметь право создавать/записывать файл по указанному адресу. -u user_name, —user=user_name Имя пользователя MySQL-сервера, используемое при подключении к серверу. Значением по умолчанию является имя пользователя Unix. -O var=option, —set-variable var=option Установить значения переменных. Доступные для использования переменные перечислены ниже. -v, —verbose Расширенный режим вывода. Вывод более детальной информации о работе программы. -V, —version Вывести информацию о версии и выйти из программы. -w, —where=»where-condition» Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. «—where=user=»jimf»» «-wuserid>1» «-wuser > backup-file.sql

    Mysql -e «source /patch-to-backup/backup-file.sql» database

    Данная утилита достаточно часто используется и для переноса информации из базы данных на другой MySQL-сервер:

    Mysqldump —opt database | mysql —host=remote-host -C database

    Вполне возможно получить дамп нескольких баз данных с помощью одной команды:

    Mysqldump —databases database1 > my_databases.sql

    Если необходим дамп всех баз данных, можно использовать:

    Дампы баз данных MySql – mysqldump

    Общее описание программы mysqldump

    Для работы с дампами баз данных MySql существует клиентская программа mysqldump, изначально написанная Игорем Романенко (Igor Romanenko). Mysqldump входит в состав дистрибутивов всех клонов MySql. Полная документация – http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html, а здесь только краткая шпаргалка по использованию основных функций.

    Программа mysqldump позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL сервер (не обязательно MySQL сервер).

    Существует три основных способов использования mysqldump: создать набор из одной или более таблиц, набор из одного или более полных баз данных, или весь сервер MySQL, как показано здесь:

    Например, сделать дамп базы DATABASE:

    • -u или -–user=… – имя пользователя
    • -h или –host=… – удаленный хост (для локального хоста можно опустить этот параметр)
    • -p или –password – запросить пароль
    • database – имя базы данных
    • damp.sql – файл для дампа

    Залить базу из файла дампа:

    Основные опции программы mysqldump:

    Добавляет оператор DROP DATABASE перед каждым оператором CREATE DATABASE.

    Добавляет оператор DROP TABLE перед каждым оператором CREATE TABLE.

    Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.

    Используется полная форма оператора INSERT (с именами столбцов).

    Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.

    Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.

    Использовать команду INSERT DELAYED при вставке строк.

    Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. Очень рекомендуется использовать.

    Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров –databases и –all-databases.

    Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа только структуры базы данных без самих данных.

    Номер TCP порта, используемого для подключения к хосту. Если порт нестандартный, то использовать обязательно

    Добавляет ключевое слово REPLACE в оператор INSERT.

    Параметр направляет дамп в файл file. Имеет смысл только в Windows. В Linux системах лучше перенаправить результат в файл при помощи последовательностей > и >>.

    Файл сокета для подсоединения к localhost. Это для любителей использовать нестандартные настройки.

    Прерывает действие параметра –databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.

    Расширенный режим вывода. Вывод детальной информации о работе программы.

    Вывести информацию о версии программы.

    Выполнить дамп только для выбранных записей. Кавычки обязательны.

    Вывести справочную информацию и выйти из программы.

    Примеры использования программы mysqldump

    Создание только структуры базы без данных

    Создание дампа только нескольких таблиц

    Создание дампа и архивирование его

    Создание архивного дампа с указанием его даты

    Добавить комментарий Отменить ответ

    Для отправки комментария вам необходимо авторизоваться.

    MySQLdump: скачать, работа с mysqldump, примеры

    Оставьте комментарий 6,950

    Данная утилита позволяет получить дамп («моментальный снимок»») содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

    Если же резервная копия создается на сервере, то вместо описываемой утилиты следует использовать mysqlhotcopy . См.раздел See section 4.8.6 mysqlhotcopy , Копирование баз данных и таблиц MySQL .

    Shell> mysqldump database или mysqldump —databases DB1 или mysqldump —all-databases

    Если не указывать имена таблиц или использовать параметры —databases или —all-databases , то будет получен дамп базы данных в целом (соответственно — всех баз данных).


    Перечень опций, поддерживаемых вашей конкретной версией утилиты mysqldump , можно получить, выполнив команду mysqldump —help .

    Следует иметь в виду, что утилита mysqldump , используемая без опций —quick или —opt , перед тем, как сделать дамп результата выборки информации, загрузит весь результат в память. Это может создать проблемы при получении дампа большой базы данных.

    Учтите, что не следует применять параметры —opt или -e , если вы собираетесь использовать для получения дампа новую копию программы mysqldump , а затем воспроизводить его на очень старом MySQL-сервере.

    Утилита mysqldump поддерживает следующие опции:

    Add-locks Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL). —add-drop-table Добавить команду DROP TABLE перед каждой командой CREATE TABLE . -A, —all-databases Произвести дамп всех баз данных. Аналогично опции —databases с указанием всех баз данных. -a, —all Включить все опции создания объектов, специфичные для MySQL. —allow-keywords Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца. -c, —complete-insert Использовать полные команды INSERT (с именами столбцов). -C, —compress Использовать сжатие всей информации между клиентом и сервером, если они оба поддерживают сжатие. -B, —databases Выполнить дамп нескольких баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных. —delayed Использовать команду INSERT DELAYED при вставке строк. -e, —extended-insert Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода). -#, —debug[=option_string] Отслеживать прохождение программы (для отладки). —help Вывести справочную информацию и выйти из программы. —fields-terminated-by=. —fields-enclosed-by=. —fields-optionally-enclosed-by=. —fields-escaped-by=. —lines-terminated-by=. Эти опции используются совместно с параметром -T и имеют то же самое значение, что и соответствующие операторы для LOAD DATA INFILE . См. раздел See section 6.4.9 Синтаксис оператора LOAD DATA INFILE . -F, —flush-logs Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. -f, —force, Продолжать даже при получении ошибки SQL при выполнении дампа таблицы. -h, —host=.. Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию — localhost . -l, —lock-tables. Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL , чтобы разрешить параллельные записи для MyISAM -таблиц. Следует отметить, что при выполнении дампа совокупности баз данных опция —lock-tables блокирует таблицы каждой базы по отдельности. Таким образом, использование этого параметра не гарантирует, что таблицы будут логически непротиворечивы в пределах этих баз данных. В различных базах данных при выполнении дампа таблицы могут находиться в совершенно разных состояниях. -K, —disable-keys Добавляет выражение /*!40000 ALTER TABLE tb_name DISABLE KEYS */; и /*!40000 ALTER TABLE tb_name ENABLE KEYS */; в выводе результата. Это ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются после внесения всех данных. -n, —no-create-db В выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; будет отсутствовать. Данная строка будет добавлена в любом случае при использовании опций —databases или —all-databases . -t, —no-create-info Не записывать информацию о создании таблицы (команда CREATE TABLE). -d, —no-data Не записывать информацию из строк таблицы. Это очень полезно для получения дампа структуры таблицы! —opt То же, что и —quick —add-drop-table —add-locks —extended-insert —lock-tables . Должно дать наиболее быстрый дамп для чтения на MySQL-сервере. -pyour_pass, —password[=your_pass] Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqldump предложит ввести пароль. -P port_num, —port=port_num Номер порта TCP/IP, используемого для подключения к хосту (применяется при подсоединении к хостам, отличным от localhost , для которого используются сокеты Unix). -q, —quick Выводить дамп непосредственно на стандартный вывод stdout без буферизации запроса. Для этого используется функция mysql_use_result() . -Q, —quote-names Взять в кавычки имена таблиц и столбцов без символов «» . -r, —result-file=. Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS, так как она предотвращает преобразование символа новой строки «\n» в последовательность «\n\r» (новая строка + возврат каретки). —single-transaction Данная опция выдает SQL-команду BEGIN перед выполнением дампа данных с сервера. Наиболее часто используется с InnoDB -таблицамии и уровнем изоляции транзакций READ_COMMITTED , так как именно в этом режиме можно получить дамп с непротиворечивым состоянием базы данных после выполнения команды BEGIN без блокирования каких-либо приложений. Используя эту опцию, необходимо помнить, что при выполнении дампа только транзакционные таблицы будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM — или HEAP -таблицы при использовании данной опции могут все же изменить свое состояние. Опция —single-transaction добавлена в версии 4.0.2. Она является взаимоисключающей по отношению к опции —lock-tables , так как команда LOCK TABLES прерывает выполнение предыдущей транзакции. -S /path/to/socket, —socket=/path/to/socket Файл сокета для подсоединения к localhost (значение хоста по умолчанию). —tables Перекрывает параметр —databases (-B). -T, —tab=path-to-some-directory Для каждой заданной таблицы создает файл a `table_name.sql» , содержащий SQL CREATE команды для создания таблицы, и файл `table_name.txt» с данными таблицы. Файл `.txt» имеет формат в соответствии с параметрами —fields-xxx и —lines—xxx . Примечание : Этот параметр работает только при условии, что утилита mysqldump запущена на том же компьютере, что и демон mysqld , причем пользователь/группа, запустившие данный поток mysqld (обычно это пользователь mysql и группа mysql), должны иметь право создавать/записывать файл по указанному адресу. -u user_name, —user=user_name Имя пользователя MySQL-сервера, используемое при подключении к серверу. Значением по умолчанию является имя пользователя Unix. -O var=option, —set-variable var=option Установить значения переменных. Доступные для использования переменные перечислены ниже. -v, —verbose Расширенный режим вывода. Вывод более детальной информации о работе программы. -V, —version Вывести информацию о версии и выйти из программы. -w, —where=»where-condition» Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. «—where=user=»jimf»» «-wuserid>1» «-wuser > backup-file.sql

    Mysql -e «source /patch-to-backup/backup-file.sql» database

    Данная утилита достаточно часто используется и для переноса информации из базы данных на другой MySQL-сервер:

    Mysqldump —opt database | mysql —host=remote-host -C database

    Вполне возможно получить дамп нескольких баз данных с помощью одной команды:

    Mysqldump —databases database1 > my_databases.sql

    Если необходим дамп всех баз данных, можно использовать:

    Mysqldump —all-databases > all_databases.sql

    Дамп и восстановление базы данных MySQL довольно просто и удобно делать удаленно через SSH или прямо через консоль сервера. Удаленно, это можно делать используя программы Putty/Kitty. Также указанные ниже примеры Вы можете выполнять и на Windows запустив командную строку ‘cmd ‘. Ниже приведены примеры о том, как создавать дампы базы данных MySQL и затем восстанавливать их при необходимости, например для Вашего сайта, интернет-магазина или какого либо другого проекта.

    Создание дампа базы данных MySQL

    Для того, чтоб выполнять данные команды, подключитесь удаленно к Вашему серверу через SSH используя одну из перечисленных выше программ. После подключения и авторизации к серверу/хостингу, Вы можете вводить приведенные ниже команды.

    # Бекап одной базы данных в файл dump_file.sql mysqldump -uroot -p your_base > dump_file.sql # На windows дамп лучше всего создавать немного другой командой, которая предотвращает # случайное затирание строк дампа из за конвертации символов перевода строки «\r\n» в «\n» mysqldump -uroot -p your_base -r dump_file_utf8.sql # Если Вам нужен бекап только отдельных таблиц, а не всей базы данных # (указываем наименования таблиц через пробел после названия базы данных) mysqldump -uroot -p your_base TABLE1 TABLE2 TABLE3 > dump_file.sql # Если нужно создать бекап только структуры базы данных без самих данных mysqldump -uroot -p —no-data your_base > dump_file.sql # Бекап всех баз данных в файл текущая_дата.gz mysqldump -uroot -p —all_databases | gzip -c > «date «+%Y-%m-%d»».gz # Бекап, где для каждой записи создается отдельный INSERT # и с явным указанием кодировки базы данных UTF-8 mysqldump -uroot -p —default-character-set=utf8 your_base —extended-insert=FALSE | gzip -c > «date «+%Y-%m-%d»».gz

    # Бекап одной базы данных в файл dump_file.sql

    mysqldump — uroot — p your_base > dump_file . sql

    # На windows дамп лучше всего создавать немного другой командой, которая предотвращает

    # случайное затирание строк дампа из за конвертации символов перевода строки «\r\n» в «\n»

    mysqldump — uroot — p your_base — r dump_file_utf8 . sql

    # Если Вам нужен бекап только отдельных таблиц, а не всей базы данных

    # (указываем наименования таблиц через пробел после названия базы данных)

    mysqldump — uroot — p your_base TABLE1 TABLE2 TABLE3 > dump_file . sql

    # Если нужно создать бекап только структуры базы данных без самих данных

    mysqldump — uroot — p — no — data your_base > dump_file . sql

    # Бекап всех баз данных в файл текущая_дата.gz

    mysqldump — uroot — p — all_databases | gzip — c > «date «+%Y-%m-%d»» . gz

    # Бекап, где для каждой записи создается отдельный INSERT

    # и с явным указанием кодировки базы данных UTF-8

    mysqldump — uroot — p — default — character — set = utf8 your_base — extended — insert = FALSE | gzip — c > «date «+%Y-%m-%d»» . gz

    В приведенном выше примере, для создания бекапа используется утилита mysqldump , которая входит в состав mysql. Далее указываются параметры для создания бекапа базы данных, которые разберем подробнее:

    • -u – параметр указывает логин, который будет использоваться для подключения к базе данных. В примере мы используем логин root, который нужно указать в этом параметре без пробела! В результате у нас это выглядит как -uroot
    • -p – параметр указывает что нужно ввести пароль для указанного логина. Мы его оставили пустым, в результате чего пароль нужно будет ввести после нажатия “Enter” при выполнении команды. Тем не менее, можно указать пароль сразу же здесь, как и в параметре логина, без пробела после -p, однако этот способ не является безопасным, так как консоль сохраняет Ваши команды в лог файл и если Вы его регулярно не очищаете, то он может быть просмотрен злоумышленником.
    • your_base – вместо этой строки в примере, вам необходимо указать реальное имя Вашей базы данных, для которой Вы создаете бекап.
    • > – оператор который показывает направление действия, т.е. как бы указывает, что вы собираетесь сделать запись из базы в файл.
    • dump_file.sql – это название Вашего файла.slq в которую нужно сохранить Вашу базу данных. Он указывается через пробел после оператора ‘>’. Вы можете задать любое другое имя. Например, чтобы в имени система автоматически вставила текущее время, достаточно указать строку вида:

    после этой строки в примере указывается расширение файла ‘.gz ‘. В результате будет создан файл вида ‘2014-11-15.gz ‘.

    Внимание! Если Вы указываете только имя файла, то он будет сохранен в той же директории, относительно которой Вы выполняете данную команду. Т.е. если Вы видите в строке приглашения ввода команд что-то вроде # , где [email protected] это логин и имя сервера, то файл будет создан в директории /home . Чтобы изменить сохранение файла по другому пути, укажите вместо имени полный путь для сохранения файла, например: /var/www/backup/dump_file.sql .

  • Во втором примере, вместо оператора ‘> ‘ используется оператор ‘| ‘, который указывает на необходимость выполнения дополнительной команды gzip c параметром ‘-c ‘ которая позволяет сразу же запаковать дамп в архив, а только затем сохранить его в файл вида ‘2014-11-15.gz ‘, о чем сообщает оператор ‘> ‘.
  • Параметр –no-data позволяет создать дамп только структуры базы данных без самих данных. В некоторых случаях довольно полезно, когда данные не нужны.
  • Параметры –default-character-set=utf8 и –extended-insert=FALSE . Первый позволяет Вам явно указать кодировку, которая используется этой базой данных, тем самым избежать сохранение базы в неверной кодировке Вместо utf8 можно указать любую другую кодировку, например cp1251 . Второй параметр позволяет указать, что при экспорте для каждой записи необходимо создать отдельную команду INSERT. В некоторых случаях это может потребоваться при частичном восстановлении данных из дампа.

    Восстановление базы данных из файла дампа MySQL

    Теперь рассмотрим с Вами обратный процесс восстановления базы данных из файла дампа. Данное действие выполняется при помощи программы mysql. Рассмотрим сразу же пример.

    Утилита mysqldump служит для резервирования и восстановления резервных копий (часто называются «дампами») баз данных и таблиц MySQL. Утилита предназначена для командной строки и работает под операционными системами Windows, FreeBSD и некоторыми другими. Mysqldump является свободнораспространяемым ПО, не требующим обязательной оплаты.

    Для локального копирования баз данных MySQL с типом таблиц ISAM и MyISAM лучше использовать утилиту, написанную на Perl: mysqlhotcopy . Работает она намного быстрее, чем mysqldump, так как копирует файлы баз данных *.frm, *.myd и *.myi целиком, предварительно заблокировав таблицы. Также, для корректной работы mysqlhotcopy необходимо наличие Unix-утилит: cp или scp. Для таблиц типа InnoDB mysqlhotcopy не подойдет, так как не все файлы таблиц InnoDB хранятся в стандартной директории БД MySQL.

    Примеры использования mysqldump

    Основной синтаксис mysqldump таков:

    mysqldump [ OPTIONS] database [ tables]
    mysqldump [ OPTIONS] — databases [ OPTIONS] DB1 [ DB2 DB3. ]
    mysqldump [ OPTIONS] — all- databases [ OPTIONS]

    Не указывая имена таблиц или используя параметры —databases или —all-databases, будет получен дамп всех баз данных находящихся в MySQL.

    Записать дамп БД с удаленного сервера MySQL с адресом 127.0.0.2 на локальный компьютер в файл dump_db.sql:

    mysqldump — uroot — h127.0.0.2 — p db_name > dump_db.sql

    Восстановить базу MySQL db_name на удаленном сервере по адресу 127.0.0.2 с дампа, расположенного на локальном компьютере, в файле dump_db.sql:

    mysql — uroot — h127.0.0.2 — p db_name dump_db.sql

    Выборка не более 50 записей (напремер для тестирования не нужна полная копия БД):

    Скопировать базы данных MySQL на удаленный компьютер:

    mysqldump — opt database | mysql — host= remote- host — C database

    Скопировать только структуру базы данных MySQL:

    mysqldump — uroot — hh127.0.0.2 — p — no- data my_dbname > my_db_structure.sql

    Утилита mysqldump, используемая без опций —opt или —quick, перед тем, как сделать дамп результата выборки информации, запишет весь результат своей работы в память. Это может вызвать проблемы при получении дампа большой базы данных. Также следует учесть, что нет необходимости применять параметры —opt или -e, если предполагается использовать для получения дампа новую копию программы mysqldump, а затем воспроизводить его на очень старом MySQL-сервере.

    Все параметры утилиты mysqldump можно просмотреть, запустив ее так:

    Стандартный перечень параметров mysqldump с кратким описанием опубликован ниже:

    —add-drop-database
    Добавляет DROP DATABASE перед каждым оператором CREATE DATABASE.

    —add-drop-table
    Добавляет DROP TABLE перед каждым оператором CREATE TABLE.

    —add-locks
    Добавляет LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL).

    —all-databases, -A
    Сохраняет все таблицы из всех баз данных, которые находятся под управлением текущего сервера.

    —allow-keywords
    Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца.

    —comments, -i
    Данный параметр позволяет добавить в дамп дополнительную информацию, такую, как версия mysqldump, версия MySQL, имя хоста, на котором расположен сервер MySQL.

    —compact
    Данный параметр требует от mysqldump создать дамп, используя как можно более компактный формат. Параметр является противоположным —comments.

    —compatible=name
    Параметр генерирует вывод, который совместим с другими СУБД или более старыми версиями MySQL. Вместо ключевого слова name можно использовать: «ansi», «mysql323», «mysql40», «postgresql», «oracle», «mssql», «db2», «maxdb», «no_key_options», «no_table_options», «no_field_options». Можно использовать несколько значений, разделив их запятыми.

    —complete-insert, -c
    Используется полная форма оператора INSERT (с именами столбцов).

    —create-options
    Добавляет дополнительную информацию в операторы CREATE TABLE. Это может быть тип таблицы, начальное значение AUTO_INCREMENT и другие параметры.

    —databases, -B
    Параметр позволяет указать имена нескольких баз данных, для которых необходимо создать дамп.

    —delayed
    Использовать команду INSERT DELAYED при вставке строк.

    —delete-master-logs
    На главном сервере репликации автоматически удаляются бинарные логи (logbin) после того, как дамп был успешно создан при помощи mysqldump. Этот параметр автоматически включает параметр «—master-data».

    Для каждой таблицы, окружает оператор INSERT выражениями /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; и /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; в выводе результата дампа. Это ускорит загрузку данных на сервер для таблиц типа MyISAM, так как индексы создаются после внесения всех данных.

    —extended-insert, -e
    Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода).

    —flush-logs, -F
    Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа.

    —force, -f
    Продолжать даже если в процессе создания дампа произошла ошибка.

    —hex-blob
    Параметр позволяет представить бинарные данные в полях типа BINARY, VARBINARY, BLOB и BIT в шестнадцатеричном формате. Так последовательность «abc» будет заменена на 0×616263.

    —ignore-table=db_name.tbl_name
    Позволяет игнорировать таблицу tbl_name базы данных db_name при создании дампа. Если из дампа необходимо исключить несколько таблиц, необходимо использовать несколько параметров «—ignore-table», указывая по одной таблице в каждом из параметров.

    —insert-ignore
    Добавляет ключевое слово IGNORE в оператор INSERT.

    —lock-all-tables, -x
    Указание этого параметра приводит к блокировке всех таблиц во всех базах данных на время создания полного дампа всех баз данных.

    —lock-tables, -l
    Указание этого параметра приводит к блокировке таблиц базы данных, для которой создается дамп.

    —no-autocommit
    Включает все операторы INSERT, относящиеся к одной таблице, в одну транзакцию, что приводит к увеличению скорости загрузки данных.


    —no-create-db, -n
    Подавляет создание в дампе операторов CREATE DATABASE, которые автоматически добавляются при использовании параметров —databases и —all-databases.

    —no-data, -d
    Подавляет создание операторов INSERT в дампе, что может быть полезно при создании дампа структуры базы данных без самих данных.

    —opt
    Параметр предназначен для оптимизации скорости резервирования данных и является сокращением, включающим следующие опции: —quick —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —set-charset. Начиная с MySQL 4.1, параметр —opt используется по умолчанию, т.е. все вышеперечисленные параметры включаются по умолчанию, даже если они не указываются. Для того чтобы исключить такое поведение, необходимо воспользоваться параметров —skip-opt

    —order-by-primary
    Указание параметра приводит к тому. что каждая таблица сортируется по первичному ключу или первому уникальному индексу.

    —port, -P
    Номер TCP порта, используемого для подключения к хосту.

    —protocol=
    Параметр позволяет задать протокол подключения к серверу.

    —quick, -q
    Позволяет начать формирование дампа, не дожидаясь полной загрузки данных с сервера и экономя тем самым память.

    —quote-names, -Q
    Помещает имена баз данных, таблиц и столбцов в обратные апострофы `. Начиная с MySQL 4.1, данный параметр включен по умолчанию.

    —replace
    Добавляет ключевое слово REPLACE в оператор INSERT. Данный параметр впервые появился в MySQL 5.1.3.

    —result-file=/path/to/file, -r /path/to/file
    Параметр направляет дамп в файл file. Этот параметр особенно удобен в Windows, без использования командной строки. когда можно перенаправить результат в файл при помощи последовательностей > и >>.

    —routines, -R
    Данный параметр создает дамп хранимых процедур и функций. Доступен с MySQL 5.1.2.

    —single-transaction
    Параметр создает дамп в виде одной транзакции.

    —skip-comments
    Данный параметр позволяет подавить вывод в дамп дополнительной информации.

    —socket=/path/to/socket, -S /path/to/socket
    Файл сокета для подсоединения к localhost.

    —tab=/path/, -T /path/
    При использовании этого параметра в каталоге path для каждой таблицы создаются два отдельных файла: tbl_name.sql, содержащий оператор CREATE TABLE, и tbl_name.txt, который содержит данные таблиц, разделенные символом табуляции. Формат данных может быть переопределен явно с помощью параметров —fields-xxx и —lines-xxx.

    —tables
    Перекрывает действия параметра —databases (-B). Все аргументы, следующие за этим параметром, трактуются как имена таблиц.

    —triggers
    Создается дамп триггеров. Этот параметр включен по умолчанию. для его отключения следует использовать параметр —skip-triggers.

    —tz-utc
    при использовании данного параметра в дамп будет добавлен оператор вида SET TIME_ZONE=»+00:00″, который позволит обмениваться дампа в различных временных зонах.

    —verbose, -v
    Расширенный режим вывода. Вывод более детальной информации о работе программы.

    —version, -V
    Вывести информацию о версии программы.

    —where=»where-condition», -w «where-condition»
    Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны: «—where=user=»test»» «-wuserid>1» «-wuserid

    —xml, -X
    Представляет дамп базы данных в виде XML.

    —first-slave, -x
    Блокирует все таблицы во всех базах данных.

    —debug=. -#
    Отслеживать прохождение программы (для отладки).

    —help
    Выводится справка и завершается работа программы.

    Mysqldump — документация и примеры.

    Данная статья может оказаться полезной, если у вас есть веб-сайт, который использует систему управления контентом (Joomla, WordPress, OpenCart и т.д.), и вы решили перенести его на другой сервер . Для этого требуется не только перенос файлов сайта, но и перенос . Перед тем, как приступить к переносу веб-сайта, необходимо подобрать хороший и заказать его, чтобы получить доступ к новому хостинговому аккаунту. Мы, например, предлагаем отдельный , Joomla и PrestaShop. Если вы хотите перенести сайт на новое имя и вам нужно , не торопитесь. Наш план виртуального хостинга S4 предоставит вам домен и в подарок. Если для вашего сайта недостаточно виртуального хостинга, вы всегда можете заказать VPS или .

    Для переноса базы данных необходимо сначала создать ее дамп, то есть разместить содержимое в отдельный sql-файл. Делается это в меню phpMyAdmin на хостинге, откуда вы переносите сайт. Зайдите в phpMyAdmin, выделите слева базу данных, которую необходимо перенести, и нажмите на кнопку «Экспорт » в верхнем меню.

    Откроется диалоговое окно, в котором обязательно выберите тип базы SQL, после чего нажмите кнопку ОК . Дождитесь пока файл базы данных скачается на ваш компьютер.

    Теперь этот файл необходимо корректно разместить на нашем хостинге. Для этого необходимо сначала создание базы данных MySQL в cPanel аккаунте, добавление к ней пользователя и назначение ему привилегии. Ниже по порядку расписано, как создать базу данных в MySQL.

    Зайдите в cPanel и найдите раздел «Базы данных », выберите «».

    Откроется новое окно. На шаге 1 введите имя базы. Обратите внимание на то, что имя БД всегда будет с префиксом, первая половина имени будет содержать в себе имя cPanel аккаунта.

    После ввода имени нажмите на кнопку «Следующий шаг ».

    На шаге 2 необходимо создать пользователя MySQL к базе и задать пароль для него. После ввода всех данных нажмите кнопку «Создать пользователя ».

    При настройке привилегий созданного пользователя выберите опцию «ВСЕ ПРАВА » и переходите к следующему шагу.

    Если в предыдущих шагах все было сделано правильно, мастер баз данных оповестит вас об успешном завершении создания БД на хостинге.

    Созданную БД вы теперь сможете найти в списке доступных баз MySQL в одноименном меню контрольной панели.

    Теперь для восстановления базы данных MySQL импортируем в созданную базу содержимое сохраненного со старого хостинга файла с расширением .sql . Для этого на главной странице cPanel выберите пункт меню “phpMyAdmin ” в том же разделе “Базы данных ”. В открывшемся окне в меню слева выберите только что созданную БД и нажмите на вкладку «Импорт » в верхнем меню.

    Нажмите кнопку «Выберите файл », в диалоговом окне выберите ранее сохраненный sql-файл и загрузите его на хостинг. Убедитесь, что выбран тип кодировки utf-8. После нажатия кнопки ОК дождитесь сообщения об успешном импорте базы данных.

    На этом все действия, необходимые для переноса базы данных на другой , выполнены.

    Приветствую вас, друзья! ��

    Сегодня я решил продолжить разговор о работе с MySQL в консоли и уделить внимание процедуре экспорта базы данных MySQL.

    В статье я расскажу о том, как сделать дамп базы MySQL, а также производить выгрузку данных из MySQL в Excel файл и csv формат.

    Мы рассмотрим различные варианты выборки информации из : создание дампа одной и нескольких БД, экспорте данных из отдельных таблиц и результатов произвольных SELECT запросов.

    А также поговорим о том, как сделать вывод данных из MySQL базы в консоли сервера и командной строке MySQL.

    В данной статье я не буду рассказывать о том, как экспортировать данные с помощью phpMyAdmin и других визуальных инструментов.

    Во-первых, потому что по данной теме уже достаточно материала в сети. Причём качественного материала, который я не горю желанием копипастить.

    А, во-вторых, я уже вкратце сам рассматривал процесс вывода информации из MySQL базы в SQL файл в одной из своих статей, где рассказывал об .

    Так что, если вы не являетесь профессиональным разработчиком или системным администратором, которым может быть полезна информация о работе с консолью, и вы пришли лишь за инструкцией по экспорту базы в phpMyAdmin, то можете ограничиться чтением информации по ссылке выше.

    Хочу, чтобы вы поняли меня правильно: я ни в коем случае не хочу вас обидеть, а просто желаю, чтобы вы потратили своё время с максимальной пользой для дела и получили то, что искали.

    На этом вступительная часть окончена и мы переходим к обзору консольных команд для создания дампа базы MySQL, которые я решил рассортировать по объёму сохраняемых данных: начиная от экспорта всей БД, заканчивая отдельными таблицами и результатами произвольных запросов.

    Создание дампа базы 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 и экспорт данных

    Для создания дампа определённых таблиц 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 базы с помощью запросов

    О том, как сделать дамп базы данных 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 в Excel и csv файлы

    Я не зря объединил информацию о выводе информации из 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 — напишите в комментариях, пожалуйста, о результатах вашей работы. Думаю, информация будет интересна всем.

    Экспорт данных из 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 сервер данным образом.

    Здесь есть два способа решения проблемы:

    • Изменить параметры запуска MySQL сервера
    • Изменить путь к конечному файлу экспорта 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 сборки сервера.

    Вам, кстати, если захотите изменить путь к буферному каталогу обмена файлами, нужно будет сделать то же самое.

    В моём случае в конфиге данная переменная уже существовала, только в закомментированном виде:

    Если у вас её не будет, то пропишите её с нуля в секции (по крайней мере, у меня она располагалась там).

    Я её раскомментил и решил использовать в том виде, в каком она была прописана. Т.е. при экспорте данных из 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

    MySQLdump: скачать, работа с mysqldump, примеры

    На днях пытался сделать дамп базы MySQL, но по какой-то причине, мой phpMyAdmin отказался это делать. Как это сделать без него, читайте в заметке.

    Суть такова, зайдя в phpMyAdmin в раздел Экспорт у меня почему-то не отработал файл export.php. Нажав на кнопку ОК я получил fatal error

    Fatal error: Uncaught Error: Class ‘PMA\libraries\Util’ not found in C:\Server\data\htdocs\phpMyAdmin\export.php:168 Stack trace: #0

    thrown in C:\Server\data\htdocs\phpMyAdmin\export.php on line 168

    Как бы я не пытался сохранить дамп базы, либо в файл, либо просто вывести его на экран, всегда выбрасывалась ошибка, что не найден класс Util.

    В общем разбираться времени не было, надо было срочно делать дамп. Гугл помог найти очень простой и быстрый способ.

    Использование mysqldump

    Чтобы сделать дамп базы в MySQL из-под Windows, откройте командную строку в режиме Администратора. Сделать это легко: откройте поиск Windows, пропишите cmd и в поисковом списке на строке Командная строка нажмите правой кнопкой мыши и выберите Запустить от имени администратора. На рисунке ниже все показано наглядно.

    В открывшимся терминале следует прописать следующую команду:

    C:\Server\bin\mysql-5.7\bin\mysqldump.exe -h127.0.0.1 -u -p > backup.sql

    Коротко: -h — это хост, -u — логин пользователя от MySQL, -p — пароль пользователя от MySQL, DB_NAME — название базы данных, дамп которой нам надо сделать, backup.sql — название файла с дампом базы.

    Обратите внимание Параметр -p намерено указан пустым. Иначе у вас не получится ввести эту команду. И еще — путь к файлу mysqldump.exe у вас будет свой.

    Наглядно это будет вот так:

    После ввода этой команды, MySQL тут же запросит пароль указанного пользователя.

    Вводите пароль и все готово.

    Если Вы не указывали путь для backup.sql то по-умолчанию он будет находиться по адресу: C:\Windows\System32\backup.sql

    В файле вы увидите привычный всем дамп базы. Чтобы задать нужный путь для файла с бэкапом, нужно просто его писать. Что-то вроде такого:

    Дамп MySQL базы данных

    Несколько полезных приемов, которые позволят вам значительно ускорить работу с резервным копированием и восстановлением баз данных. Конечно дамп MySQL можно делать и с помощью стороннего ПО, того же phpMyAdmin, но встроенными утилитами самого сервера БД это удобнее и быстрее.

    Делаем резервную копию (он же дамп, он же бекап)

    Все данные одной базы

    USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

    Все данные нескольких определенных баз

    Добавляем параметр —databases или -B

    USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

    Данные всех баз

    Указываем ключ —all-databases или -A .

    USER – логин пользователя, PASSWORD – пароль. Обратите внимание, что между -p и PASSWORD нет пробела.

    Дамп только структуры базы данных MySQL

    За это это отвечает параметр —no-data или -d .

    Дамп определенных таблиц

    Сразу архивируем дамп MySQL

    Добавляем дату и время к дампу

    Восстанавливаем дамп MySQL

    Заливаем все данные

    Заливаем данные в конкретную базу

    Заливаем данные из заархивированного дампа

    Распространенные ошибки, возникающие при создании дампа MySQL

    Ошибка связана с тем, что не хватает времени для выполнения операции. Для исправления в конфигурационном файле /etc/my.cnf в секции [mysqldump] увеличьте значение параметров wait_timeout=600 и interactive_timeout=600 . Значение времени подберите под ваши условия.

    Ошибка связана с тем, что объем вносимых данных превышает разрешенный. Решается 2-я способами:

    1. добавить параметр —max_allowed_packet=128M к нашим командам
    2. Добавить параметр max_allowed_packet=128M в секцию [mysqldump] конфигурационного файла /etc/my.cnf

    Соответственно, размер данных указываем тот, который нам необходим.

    Данная ошибка может быть связана, как с недостаточным значение параметра wait_timeout , так и с маленьким размером max_allowed_packet . Решения собственно уже приведены.

    Читайте также

    Не смотря на то, что Microsoft делает все возможное, что бы Skype стал как можно…

    Оценить размер папки в Linux (занимаемого места) можно с помощью стандартной консольной команды du. Давайте…

    90% книг написанных про Linux начинают знакомство с командной строкой с cat. По статистике (на…

    Делаем дамп (бэкап) базы данных, используя утилиту mysqldump

    Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как mysqldump.

    mysqldump — утилита позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

    mysqldump -u root -p -f name_database > C:\mydb_backup_name_database.txt

    Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt

    Файл можно не создавать, MySQL создаст его сам.

    mysql -u root -p -f name_database Pwd , то пример выгладит так:

    mysqldump -u root -p Pwd -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 -p Pwd -f —default-character-set= cp1251 DBNAME | gzip -c > 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 -p Pwd -f —default-character-set=$DBCHARACTER $DBNAME | bzip2 -c > sql.$DBNAME.`date «+%Y-%m-%d»`.bz2


    Если Вы хотите автоматизировать удаление старых архивов, попробуйте воспользоваться cron и командой find, которая обычно есть в unix. Запуская периодически

    /каталог-с-архивами -name «*.gz» -mtime +7 -exec rm -f <> \;

    Тем самым Вы будете удалять архивы, которые «старше» семи дней.

    MySQLdump: скачать, работа с mysqldump, примеры

    Экспорт и импорт данных в MySQL обычно требуется при переносе информации из одной базы данных MySQL в другую и для осуществления резервного копирования.
    Резервное копирование данных носит чисто технологический характер. Это означает, что в случае какого-либо программного или аппаратного сбоя оборудования, будет возможность восстановить актуальные данные.
    Единственный способ быть уверенным в 100% восстановлении данных — самостоятельно выполнять регулярное резервное копирование информации. Для этого необходимо воспользоваться утилитой mysqldump, которая доступна через unix shell.

    Пример команд для экспорта и импорта базы данных:

    mysqldump -u имя_пользователя -p -h имя_сервера_БД имя_базы > dump.sql

    Данные будут сохранены в файле dump.sql. Далее (в случае необходимости) восстанавливаем данные из резервной копии (дампа) так:

    mysql -u имя_пользователя -p -h имя_сервера_БД имя_базы Запускаемые указанным образом утилиты для работы с MySQL будут запрашивать пароль к базе данных. Нужно вводить пароль соответствующего пользователя БД.

    По умолчанию система считает, что данные в базе хранятся в кодировке Windows-1251. Если ваши данные хранятся в другой кодировке, то после создания резервной копии (дампа) для последующего корректного восстановления данных необходимо открыть созданный файл текстовым редактором и исправить в нём строчку:

    /*!40101 SET NAMES cp1251 */;

    Например, для кодировки UTF8 нужно указать:

    /*!40101 SET NAMES utf8 */

    Дополнительные опции утилиты mysqldump:

    —add-drop-table — опция, которая добавляет команду DROP TABLE перед созданием таблиц. Перед восстановлением таблиц из дампа, таблицы с таким же именем в рабочей базе данных будут удалены и пересозданы из резервной копии. Рекомендуется использовать для предотвращения возможных ошибок после восстановления;
    —add-locks — опция, которая добавляет команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы. Применяется для ускорения доступа к MySQL;
    —quote-names — опция, сообщающая утилите о необходимости ставить кавычки для названий таблиц и столбцов. Рекомендуется к использованию для MySQL версий, младше 4.1.1. В старших версиях она активирована по умолчанию.
    Опции —quick и —opt рекомендуется использовать, если база данных MySQL слишком большая для того, чтобы целиком поместиться в памяти.
    При этом утилита mysqldump выдает ошибку:

    mysqldump: Out of memory (Needed XXXXX bytes)
    mysqldump: Got error: 2008: MySQL client
    run out of memory when retrieving data from server

    В итоге строчка для создания копии базы данных получается следующей:

    mysqldump —opt -u имя_пользователя -p -h имя_сервера_БД —add-drop-table имя_базы > dump.sql
    mysqldump —quick -u имя_пользователя -p -h имя_сервера_БД —add-drop-table имя_базы > dump.sql

    Для совместимости дампа, сделанного на стороннем сервере мы рекомендуем создавать дамп базы данных с ключом: либо такой:

    —set-variable max_allowed_packet=2M
    -O max_allowed_packet=2M

    Если во время импорта вы получите ошибку вида

    mysqldump: Error 2020:Got packet bigger than ‘max_allowed_packet’bytes when dumping table `some_table_name ` at row: 2

    значит в вашей базе данных присутствуют данные, которые невозможно разделить на отдельные части, каждая не больше двух мегабайт.
    Как правило, такое случается, если базу данных MySQL используют для хранения содержимого различных файлов, например, картинок или музыки, не размещая их непосредственно в файловой системе.

    Если на вашем сервере установлена версия MySQL 4.0.x, то для корректного переноса данных со сторонних серверов необходимо при экспорте базы данных использовать ключ:

    —compatible=mysql40

    Если решение вопроса найти не удалось, Вы можете отправить нам заявку:

    АВТОМИР

    Приветствую вас, друзья! ��

    Сегодня я решил продолжить разговор о работе с MySQL в консоли и уделить внимание процедуре экспорта базы данных MySQL.

    В статье я расскажу о том, как сделать дамп базы MySQL, а также производить выгрузку данных из MySQL в Excel файл и csv формат.

    Мы рассмотрим различные варианты выборки информации из : создание дампа одной и нескольких БД, экспорте данных из отдельных таблиц и результатов произвольных SELECT запросов.

    А также поговорим о том, как сделать вывод данных из MySQL базы в консоли сервера и командной строке MySQL.

    В данной статье я не буду рассказывать о том, как экспортировать данные с помощью phpMyAdmin и других визуальных инструментов.

    Во-первых, потому что по данной теме уже достаточно материала в сети. Причём качественного материала, который я не горю желанием копипастить.

    А, во-вторых, я уже вкратце сам рассматривал процесс вывода информации из MySQL базы в SQL файл в одной из своих статей, где рассказывал об .

    Так что, если вы не являетесь профессиональным разработчиком или системным администратором, которым может быть полезна информация о работе с консолью, и вы пришли лишь за инструкцией по экспорту базы в phpMyAdmin, то можете ограничиться чтением информации по ссылке выше.

    Хочу, чтобы вы поняли меня правильно: я ни в коем случае не хочу вас обидеть, а просто желаю, чтобы вы потратили своё время с максимальной пользой для дела и получили то, что искали.

    На этом вступительная часть окончена и мы переходим к обзору консольных команд для создания дампа базы MySQL, которые я решил рассортировать по объёму сохраняемых данных: начиная от экспорта всей БД, заканчивая отдельными таблицами и результатами произвольных запросов.

    Создание дампа базы 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 и экспорт данных

    Для создания дампа определённых таблиц 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 базы с помощью запросов

    О том, как сделать дамп базы данных 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 в Excel и csv файлы

    Я не зря объединил информацию о выводе информации из 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 — напишите в комментариях, пожалуйста, о результатах вашей работы. Думаю, информация будет интересна всем.

    Экспорт данных из 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 сервер данным образом.

    Здесь есть два способа решения проблемы:

    • Изменить параметры запуска MySQL сервера
    • Изменить путь к конечному файлу экспорта 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 сборки сервера.

    Вам, кстати, если захотите изменить путь к буферному каталогу обмена файлами, нужно будет сделать то же самое.

    В моём случае в конфиге данная переменная уже существовала, только в закомментированном виде:

    Если у вас её не будет, то пропишите её с нуля в секции (по крайней мере, у меня она располагалась там).

    Я её раскомментил и решил использовать в том виде, в каком она была прописана. Т.е. при экспорте данных из 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

    Данная утилита позволяет получить дамп («моментальный снимок»») содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

    Если же резервная копия создается на сервере, то вместо описываемой утилиты следует использовать mysqlhotcopy . См.раздел See section 4.8.6 mysqlhotcopy , Копирование баз данных и таблиц MySQL .

    Shell> mysqldump database или mysqldump —databases DB1 или mysqldump —all-databases


    Если не указывать имена таблиц или использовать параметры —databases или —all-databases , то будет получен дамп базы данных в целом (соответственно — всех баз данных).

    Перечень опций, поддерживаемых вашей конкретной версией утилиты mysqldump , можно получить, выполнив команду mysqldump —help .

    Следует иметь в виду, что утилита mysqldump , используемая без опций —quick или —opt , перед тем, как сделать дамп результата выборки информации, загрузит весь результат в память. Это может создать проблемы при получении дампа большой базы данных.

    Учтите, что не следует применять параметры —opt или -e , если вы собираетесь использовать для получения дампа новую копию программы mysqldump , а затем воспроизводить его на очень старом MySQL-сервере.

    Утилита mysqldump поддерживает следующие опции:

    Add-locks Добавить команды LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы (для ускорения доступа к MySQL). —add-drop-table Добавить команду DROP TABLE перед каждой командой CREATE TABLE . -A, —all-databases Произвести дамп всех баз данных. Аналогично опции —databases с указанием всех баз данных. -a, —all Включить все опции создания объектов, специфичные для MySQL. —allow-keywords Разрешить создавать имена столбцов, которые совпадают с ключевыми словами. Отсутствие конфликтов обеспечивается прибавлением имени таблицы в качестве префикса к имени каждого столбца. -c, —complete-insert Использовать полные команды INSERT (с именами столбцов). -C, —compress Использовать сжатие всей информации между клиентом и сервером, если они оба поддерживают сжатие. -B, —databases Выполнить дамп нескольких баз данных. Обратите внимание на разницу в использовании: в этом случае таблицы не указываются. Все имена аргументов рассматриваются как имена баз данных. Оператор USE db_name; включается в вывод перед каждой новой базой данных. —delayed Использовать команду INSERT DELAYED при вставке строк. -e, —extended-insert Использовать команду INSERT с новым многострочным синтаксисом (повышает компактность и быстродействие операторов ввода). -#, —debug[=option_string] Отслеживать прохождение программы (для отладки). —help Вывести справочную информацию и выйти из программы. —fields-terminated-by=. —fields-enclosed-by=. —fields-optionally-enclosed-by=. —fields-escaped-by=. —lines-terminated-by=. Эти опции используются совместно с параметром -T и имеют то же самое значение, что и соответствующие операторы для LOAD DATA INFILE . См. раздел See section 6.4.9 Синтаксис оператора LOAD DATA INFILE . -F, —flush-logs Записать на диск данные системного журнала из буфера MySQL-сервера перед началом выполнения дампа. -f, —force, Продолжать даже при получении ошибки SQL при выполнении дампа таблицы. -h, —host=.. Выполнить дамп данных MySQL сервера на указанном хосте. Значение хоста по умолчанию — localhost . -l, —lock-tables. Заблокировать все таблицы перед началом выполнения дампа. Таблицы блокируются оператором READ LOCAL , чтобы разрешить параллельные записи для MyISAM -таблиц. Следует отметить, что при выполнении дампа совокупности баз данных опция —lock-tables блокирует таблицы каждой базы по отдельности. Таким образом, использование этого параметра не гарантирует, что таблицы будут логически непротиворечивы в пределах этих баз данных. В различных базах данных при выполнении дампа таблицы могут находиться в совершенно разных состояниях. -K, —disable-keys Добавляет выражение /*!40000 ALTER TABLE tb_name DISABLE KEYS */; и /*!40000 ALTER TABLE tb_name ENABLE KEYS */; в выводе результата. Это ускорит загрузку данных на сервер MySQL 4.0, так как индексы создаются после внесения всех данных. -n, —no-create-db В выводе результата выражение CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name; будет отсутствовать. Данная строка будет добавлена в любом случае при использовании опций —databases или —all-databases . -t, —no-create-info Не записывать информацию о создании таблицы (команда CREATE TABLE). -d, —no-data Не записывать информацию из строк таблицы. Это очень полезно для получения дампа структуры таблицы! —opt То же, что и —quick —add-drop-table —add-locks —extended-insert —lock-tables . Должно дать наиболее быстрый дамп для чтения на MySQL-сервере. -pyour_pass, —password[=your_pass] Используемый пароль при подключении к серверу. Если аргумент =your_pass не введен, mysqldump предложит ввести пароль. -P port_num, —port=port_num Номер порта TCP/IP, используемого для подключения к хосту (применяется при подсоединении к хостам, отличным от localhost , для которого используются сокеты Unix). -q, —quick Выводить дамп непосредственно на стандартный вывод stdout без буферизации запроса. Для этого используется функция mysql_use_result() . -Q, —quote-names Взять в кавычки имена таблиц и столбцов без символов «» . -r, —result-file=. Прямой вывод указанного файла. Этот опцию следует использовать в MS DOS, так как она предотвращает преобразование символа новой строки «\n» в последовательность «\n\r» (новая строка + возврат каретки). —single-transaction Данная опция выдает SQL-команду BEGIN перед выполнением дампа данных с сервера. Наиболее часто используется с InnoDB -таблицамии и уровнем изоляции транзакций READ_COMMITTED , так как именно в этом режиме можно получить дамп с непротиворечивым состоянием базы данных после выполнения команды BEGIN без блокирования каких-либо приложений. Используя эту опцию, необходимо помнить, что при выполнении дампа только транзакционные таблицы будут находиться в непротиворечивом состоянии, т.е. некоторые MyISAM — или HEAP -таблицы при использовании данной опции могут все же изменить свое состояние. Опция —single-transaction добавлена в версии 4.0.2. Она является взаимоисключающей по отношению к опции —lock-tables , так как команда LOCK TABLES прерывает выполнение предыдущей транзакции. -S /path/to/socket, —socket=/path/to/socket Файл сокета для подсоединения к localhost (значение хоста по умолчанию). —tables Перекрывает параметр —databases (-B). -T, —tab=path-to-some-directory Для каждой заданной таблицы создает файл a `table_name.sql» , содержащий SQL CREATE команды для создания таблицы, и файл `table_name.txt» с данными таблицы. Файл `.txt» имеет формат в соответствии с параметрами —fields-xxx и —lines—xxx . Примечание : Этот параметр работает только при условии, что утилита mysqldump запущена на том же компьютере, что и демон mysqld , причем пользователь/группа, запустившие данный поток mysqld (обычно это пользователь mysql и группа mysql), должны иметь право создавать/записывать файл по указанному адресу. -u user_name, —user=user_name Имя пользователя MySQL-сервера, используемое при подключении к серверу. Значением по умолчанию является имя пользователя Unix. -O var=option, —set-variable var=option Установить значения переменных. Доступные для использования переменные перечислены ниже. -v, —verbose Расширенный режим вывода. Вывод более детальной информации о работе программы. -V, —version Вывести информацию о версии и выйти из программы. -w, —where=»where-condition» Выполнить дамп только выбранных записей. Обратите внимание, что кавычки обязательны. «—where=user=»jimf»» «-wuserid>1» «-wuser > backup-file.sql

    Mysql -e «source /patch-to-backup/backup-file.sql» database

    Данная утилита достаточно часто используется и для переноса информации из базы данных на другой MySQL-сервер:

    Mysqldump —opt database | mysql —host=remote-host -C database

    Вполне возможно получить дамп нескольких баз данных с помощью одной команды:

    Mysqldump —databases database1 > my_databases.sql

    Если необходим дамп всех баз данных, можно использовать:

    Mysqldump —all-databases > all_databases.sql

    Дамп и восстановление базы данных MySQL довольно просто и удобно делать удаленно через SSH или прямо через консоль сервера. Удаленно, это можно делать используя программы Putty/Kitty. Также указанные ниже примеры Вы можете выполнять и на Windows запустив командную строку ‘cmd ‘. Ниже приведены примеры о том, как создавать дампы базы данных MySQL и затем восстанавливать их при необходимости, например для Вашего сайта, интернет-магазина или какого либо другого проекта.

    Создание дампа базы данных MySQL

    Для того, чтоб выполнять данные команды, подключитесь удаленно к Вашему серверу через SSH используя одну из перечисленных выше программ. После подключения и авторизации к серверу/хостингу, Вы можете вводить приведенные ниже команды.

    # Бекап одной базы данных в файл dump_file.sql mysqldump -uroot -p your_base > dump_file.sql # На windows дамп лучше всего создавать немного другой командой, которая предотвращает # случайное затирание строк дампа из за конвертации символов перевода строки «\r\n» в «\n» mysqldump -uroot -p your_base -r dump_file_utf8.sql # Если Вам нужен бекап только отдельных таблиц, а не всей базы данных # (указываем наименования таблиц через пробел после названия базы данных) mysqldump -uroot -p your_base TABLE1 TABLE2 TABLE3 > dump_file.sql # Если нужно создать бекап только структуры базы данных без самих данных mysqldump -uroot -p —no-data your_base > dump_file.sql # Бекап всех баз данных в файл текущая_дата.gz mysqldump -uroot -p —all_databases | gzip -c > «date «+%Y-%m-%d»».gz # Бекап, где для каждой записи создается отдельный INSERT # и с явным указанием кодировки базы данных UTF-8 mysqldump -uroot -p —default-character-set=utf8 your_base —extended-insert=FALSE | gzip -c > «date «+%Y-%m-%d»».gz

    # Бекап одной базы данных в файл dump_file.sql

    mysqldump — uroot — p your_base > dump_file . sql

    # На windows дамп лучше всего создавать немного другой командой, которая предотвращает

    # случайное затирание строк дампа из за конвертации символов перевода строки «\r\n» в «\n»

    mysqldump — uroot — p your_base — r dump_file_utf8 . sql

    # Если Вам нужен бекап только отдельных таблиц, а не всей базы данных

    # (указываем наименования таблиц через пробел после названия базы данных)

    mysqldump — uroot — p your_base TABLE1 TABLE2 TABLE3 > dump_file . sql

    # Если нужно создать бекап только структуры базы данных без самих данных

    mysqldump — uroot — p — no — data your_base > dump_file . sql

    # Бекап всех баз данных в файл текущая_дата.gz

    mysqldump — uroot — p — all_databases | gzip — c > «date «+%Y-%m-%d»» . gz

    # Бекап, где для каждой записи создается отдельный INSERT

    # и с явным указанием кодировки базы данных UTF-8

    mysqldump — uroot — p — default — character — set = utf8 your_base — extended — insert = FALSE | gzip — c > «date «+%Y-%m-%d»» . gz

    В приведенном выше примере, для создания бекапа используется утилита mysqldump , которая входит в состав mysql. Далее указываются параметры для создания бекапа базы данных, которые разберем подробнее:

    • -u – параметр указывает логин, который будет использоваться для подключения к базе данных. В примере мы используем логин root, который нужно указать в этом параметре без пробела! В результате у нас это выглядит как -uroot
    • -p – параметр указывает что нужно ввести пароль для указанного логина. Мы его оставили пустым, в результате чего пароль нужно будет ввести после нажатия “Enter” при выполнении команды. Тем не менее, можно указать пароль сразу же здесь, как и в параметре логина, без пробела после -p, однако этот способ не является безопасным, так как консоль сохраняет Ваши команды в лог файл и если Вы его регулярно не очищаете, то он может быть просмотрен злоумышленником.
    • your_base – вместо этой строки в примере, вам необходимо указать реальное имя Вашей базы данных, для которой Вы создаете бекап.
    • > – оператор который показывает направление действия, т.е. как бы указывает, что вы собираетесь сделать запись из базы в файл.
    • dump_file.sql – это название Вашего файла.slq в которую нужно сохранить Вашу базу данных. Он указывается через пробел после оператора ‘>’. Вы можете задать любое другое имя. Например, чтобы в имени система автоматически вставила текущее время, достаточно указать строку вида:

    после этой строки в примере указывается расширение файла ‘.gz ‘. В результате будет создан файл вида ‘2014-11-15.gz ‘.

    Внимание! Если Вы указываете только имя файла, то он будет сохранен в той же директории, относительно которой Вы выполняете данную команду. Т.е. если Вы видите в строке приглашения ввода команд что-то вроде # , где [email protected] это логин и имя сервера, то файл будет создан в директории /home . Чтобы изменить сохранение файла по другому пути, укажите вместо имени полный путь для сохранения файла, например: /var/www/backup/dump_file.sql .

  • Во втором примере, вместо оператора ‘> ‘ используется оператор ‘| ‘, который указывает на необходимость выполнения дополнительной команды gzip c параметром ‘-c ‘ которая позволяет сразу же запаковать дамп в архив, а только затем сохранить его в файл вида ‘2014-11-15.gz ‘, о чем сообщает оператор ‘> ‘.
  • Параметр –no-data позволяет создать дамп только структуры базы данных без самих данных. В некоторых случаях довольно полезно, когда данные не нужны.
  • Параметры –default-character-set=utf8 и –extended-insert=FALSE . Первый позволяет Вам явно указать кодировку, которая используется этой базой данных, тем самым избежать сохранение базы в неверной кодировке Вместо utf8 можно указать любую другую кодировку, например cp1251 . Второй параметр позволяет указать, что при экспорте для каждой записи необходимо создать отдельную команду INSERT. В некоторых случаях это может потребоваться при частичном восстановлении данных из дампа.

    Восстановление базы данных из файла дампа MySQL

    Теперь рассмотрим с Вами обратный процесс восстановления базы данных из файла дампа. Данное действие выполняется при помощи программы mysql. Рассмотрим сразу же пример.

    Данная статья может оказаться полезной, если у вас есть веб-сайт, который использует систему управления контентом (Joomla, WordPress, OpenCart и т.д.), и вы решили перенести его на другой сервер . Для этого требуется не только перенос файлов сайта, но и перенос . Перед тем, как приступить к переносу веб-сайта, необходимо подобрать хороший и заказать его, чтобы получить доступ к новому хостинговому аккаунту. Мы, например, предлагаем отдельный , Joomla и PrestaShop. Если вы хотите перенести сайт на новое имя и вам нужно , не торопитесь. Наш план виртуального хостинга S4 предоставит вам домен и в подарок. Если для вашего сайта недостаточно виртуального хостинга, вы всегда можете заказать VPS или .

    Для переноса базы данных необходимо сначала создать ее дамп, то есть разместить содержимое в отдельный sql-файл. Делается это в меню phpMyAdmin на хостинге, откуда вы переносите сайт. Зайдите в phpMyAdmin, выделите слева базу данных, которую необходимо перенести, и нажмите на кнопку «Экспорт » в верхнем меню.

    Откроется диалоговое окно, в котором обязательно выберите тип базы SQL, после чего нажмите кнопку ОК . Дождитесь пока файл базы данных скачается на ваш компьютер.

    Теперь этот файл необходимо корректно разместить на нашем хостинге. Для этого необходимо сначала создание базы данных MySQL в cPanel аккаунте, добавление к ней пользователя и назначение ему привилегии. Ниже по порядку расписано, как создать базу данных в MySQL.

    Зайдите в cPanel и найдите раздел «Базы данных », выберите «».

    Откроется новое окно. На шаге 1 введите имя базы. Обратите внимание на то, что имя БД всегда будет с префиксом, первая половина имени будет содержать в себе имя cPanel аккаунта.

    После ввода имени нажмите на кнопку «Следующий шаг ».

    На шаге 2 необходимо создать пользователя MySQL к базе и задать пароль для него. После ввода всех данных нажмите кнопку «Создать пользователя ».

    При настройке привилегий созданного пользователя выберите опцию «ВСЕ ПРАВА » и переходите к следующему шагу.

    Если в предыдущих шагах все было сделано правильно, мастер баз данных оповестит вас об успешном завершении создания БД на хостинге.

    Созданную БД вы теперь сможете найти в списке доступных баз MySQL в одноименном меню контрольной панели.

    Теперь для восстановления базы данных MySQL импортируем в созданную базу содержимое сохраненного со старого хостинга файла с расширением .sql . Для этого на главной странице cPanel выберите пункт меню “phpMyAdmin ” в том же разделе “Базы данных ”. В открывшемся окне в меню слева выберите только что созданную БД и нажмите на вкладку «Импорт » в верхнем меню.

    Нажмите кнопку «Выберите файл », в диалоговом окне выберите ранее сохраненный sql-файл и загрузите его на хостинг. Убедитесь, что выбран тип кодировки utf-8. После нажатия кнопки ОК дождитесь сообщения об успешном импорте базы данных.

    На этом все действия, необходимые для переноса базы данных на другой , выполнены.

    Делать дамп (бэкап) базы данных очень важно. Поэтому я сделал некоторые комментарии с примерами к такой отличной утилите, как mysqldump.

    mysqldump — утилита позволяет получить дамп содержимого базы данных или совокупности баз для создания резервной копии или пересылки данных на другой SQL-сервер баз данных (не обязательно MySQL-сервер). Дамп будет содержать набор команд SQL для создания и/или заполнения таблиц.

    Этой командой мы делаем бэкап базы данных под именем name_database на диск С в файл mydb_backup_name_database.txt

    Файл можно не создавать, MySQL создаст его сам.

    mysql -u root -p -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

    распаковывать такой архив можно командой:

    Чтобы знать, за какое число был сделан бэкап, можно написать такую команду:

    mysqldump -uLOGIN -PPORT -hHOST -pPASS DBNAME | gzip -c > `date «+%Y-%m-%d»`.gz

    а если приходится делать дампы с разными кодировками, то удобно пользоваться переменными:

    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. Запуская периодически

    /каталог-с-архивами -name «*.gz» -mtime +7 -exec rm -f <> \;

    Тем самым Вы будете удалять архивы, которые «старше» семи дней.

    какой то “куцый” обзор… как будто спешили куда то

    Илон Маск рекомендует:  Что такое код ocicolumnisnull
  • Понравилась статья? Поделиться с друзьями:
    Кодинг, CSS и SQL