Progress28.ru

IT Новости
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Phpmyadmin sql dump

Генерируем «правильный» SQL дамп

В процессе разработки с использованием MySQL часто приходится делать дамп базы данных для сохранения ее в репозиторий (деплоя на сервер и т.д.).
Существуют разные клиенты для работы с MySQL:
— MySQL Front
— PHPMyAdmin
— Aqua Data Studio
— EMS SQL manager
и так далее.

Проблема

В каждом из перечисленных существует функция экспорта схемы базы и её данных в файл. Попросту говоря — создания дампа БД. Но вот незадача! Каждый из иструментов генерирует SQL код со своим форматированием. К примеру, некоторые даже не вставляют ENGINE=MyISAM DEFAULT CHARSET=… в выражении CREATE TABLE, то же самое и с DROP TABLE IF EXISTS. Одним словом, наблюдается эффект «лебедь, рак и щука».

Затруднения возникают, если в команде несколько разработчиков — вероятнее всего каждый из них привык пользоваться каким-то одним инструментом. Попытки заставить девелоперов применять какой-то определенный клиент могут привести к священным войнам («я к этому привык, я не хочу другое. а это вообще отстой!»). Возможно, кому то проблема покажется надуманной, но в моей практике такое было. Да и что делать, если люди работают на разных операционных системах? SQL менеджеры у них тоже будут разные.

Итак, нужен способ генерации дампа в каком то унифицированном формате. Ожидаемая выгода:

  • возможность легко сравнивать изменения sql файлов в репозитории (если ревизии в одном формате, это проще. Не так ли?)
  • возможность дальнейшего парсинга SQL кода (например, в инсталляторе приложения)
  • автоматизация сего действия
  • независимость от ОС
  • указание кодировки
  • возможно что-то еще:)

Решение

Отказаться от каких либо сторонних инструментов для генерации дампа.
В дистрибутиве MySQL существует набор полезных утилит, в том числе и известная многим mysqldump. Ниже можно увидеть BAT-файл, который используя данную утилиту генерирует правильный SQL дамп.
Правильный — в данном контексте означает «соответствующий оговоренному формату». В любом случае, не хочу подвергать сомнению корректность работы родной утилиты от MySQL 🙂

@echo off
rem author afi
echo =========================================
echo SQL generator
echo Output files :
echo scheme.sql — Scheme of database
echo data.sql — Data for database
echo =========================================

rem по умолчанию UTF-8
set ENCODING=utf8

IF «%1» == «» goto ERROR
IF «%2» == «» goto ERROR
IF «%3» == «» goto ERROR
IF «%4» == «» goto ERROR
IF NOT «%5» == «» (
set ENCODING=»%5″
)

:get
echo Generating scheme for DB: %1
mysqldump —host=%1 —password=%4 -u %3 —disable-keys —add-drop-table —default-character-set=%ENCODING% —no-data —result-file=scheme.sql %2

echo Generating data for DB: %1
mysqldump —host=%1 —password=%4 -u %3 —disable-keys —default-character-set=%ENCODING% —no-create-info —extended-insert=false —result-file=data.sql %2
goto END

:ERROR
echo Please, define parameters. Example:
echo gensql.bat host_name database_name mysql_user mysql_password [encoding]
goto END

Теперь все заботы сводятся к запуску скрипта с параметрами:
gensql.bat host_name database_name mysql_user mysql_password [encoding]

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

В результате, получаем 2 файла:
scheme.sql — содержит схему БД, т.е. скрипт создания таблиц, ограничений, вьюшек и пр.
data.sql — содержит дамп данных

Сделать bash-вариант скрипта, думаю не составит труда тем, кому он может понадобиться.

Дамп базы MySQL

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

Для создания резервной копии зайдите в интерфейс PHPMyAdmin.

  • Слева на странице выберите нужную базу данных.
  • Перейдите на вкладку Экспорт.

  • При необходимости измените настройки экспорта и нажмите кнопку Вперед в нижней части страницы.

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

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

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

Для восстановления базы зайдите в интерфейс PHPMyAdmin и выполните следующие действия:

  • В левой колонке выберите новую базу данных.
  • Откройте вкладку Импорт.
  • Нажмите на кнопку Выберите файл и укажите файл дампа, сохраненный на вашем компьютере.
  • Задайте кодировку файла (обычно она совпадает с кодировкой сайта).
  • При необходимости измените настройки импорта и нажмите кнопку Вперед, дождитесь окончания импорта.

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

Работа с дампом из командной строки

Подключение к хостингу по SSH позволяет работать с базами данных удаленно, без необходимости загрузки дампов на ваш компьютер. Работа с дампами баз данных размером более 64 МБ возможна только из командной строки.

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

mysqldump -y -f -q —default-character-set=binary —create-options —single-transaction —skip-extended-insert —add-drop-table -h dbhost -u dbuser -pdbpassword dbname > dump .sql

Читать еще:  Count sql php

dbhost — адрес сервера баз данных,
dbuser — имя MySQL-пользователя,
dbpassword — пароль MySQL-пользователя (пишется слитно с параметром -p),
dbname — название базы данных,
dump.sql — название дампа базы данных. Дамп будет создан в текущем каталоге.

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

mysql -h dbhost -u dbuser -pdbpassword dbname

Мы рекомендуем предварительно создать новую базу данных и выполнить в нее импорт дампа. Дамп базы данных должен быть загружен на хостинг (см. статью FTP — загрузка файлов на сервер)

После того как база данных будет импортирована, измените в файлах конфигурации сайта название старой базы на новую, проверьте работоспособность.

Что такое дамп базы данных MySQL и как его сделать?

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

Следовательно, умение делать дамп базы данных MySQL немаловажно для любого веб-разработчика. Что это такое? Это файл, содержащий инструкции на языке SQL, за счет которых создается точная копия вашей БД как по содержанию, так и по структуре. Для каких ситуаций вам стоит сделать дамп базы MySQL:

  • Перенос БД на другой сервер

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

  • Резервное копирование базы данных

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

Как сделать дамп базы MySQL?

Существуют различные способы создания дампа, и далее мы рассмотрим основные варианты:

  • Делаем дамп при помощи консоли MySQL

В этом случае для создания дампа БД используется командная строка или консоль MySQL, где нужно ввести команду mysqldump -uuser -ppass db_name > file_to_save. При этом user — это имя пользователя БД с достаточными правами для создания дампа, pass — пароль от базы данных, db_name — имя нужной БД, а вместо file_to_save необходимо указать имя файла, куда будет сохраняться дамп.

После правильно введенной команды в указанном месте появится файл с расширением .sql, который и является дампом базы данных. Этот способ наиболее универсален и популярен среди пользователей Unix-систем, например, Ubuntu, если вдруг потребуется перенос MySQL на другой сервер. В том же случае, если вы не знаете консольных команд, вам потребуется дополнительное программное обеспечение.

  • Делаем дамп базы данных при помощи phpMyAdmin

Если вы предпочитаете использовать дополнительное программное обеспечение, то можно сделать дамп базы данных средствами PHP при помощи, например, phpMyAdmin. Для этого выполните следующие действия:

  1. Войдите в phpMyAdmin.
  2. Выберите нужную базу данных из общего списка.
  3. Авторизуйтесь в выбранной БД.
  4. После авторизации в левой колонке будет сама база данных и служебная информация, которая к ней относится. Теперь вам нужно повторно выбрать вашу БД.
  5. Перейдите во вкладку «Экспорт», после чего включите некоторые настройки, а именно:

— Добавить DROP TABLE/VIEW/PROCEDURE/FUNCTION/EVENT

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

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

  • Получаете готовый к использованию дамп БД. Если все было правильно сделано, то у вас будет архив, внутри которого находится файл с расширением .sql — дампом нужной вам базы данных.
  • Теперь вы знаете, как сделать дамп MySQL, если вам вдруг потребуется перенос базы на другой сервер или возникнет любая другая ситуация, когда может пригодиться резервная копия БД. Конечно, существует еще достаточно много способов создания дампа, помимо описанных выше. Но даже владение самыми простыми вариантами и регулярное применение их на практике позволит вам более уверенно чувствовать себя в любой непредвиденной ситуации, ведь достаточно будет просто развернуть дамп MySQL и быстро восстановить все базы данных.

    Если вы решились купить dedicated server в нашей компании, то Вам выдается 100 ГБ на удаленном сервере бекапов, куда можно настроить автоматическое резервное копирование сайтов и баз данных. В таком случае, у вас всегда будет свежий дамп MySQL.

    Читать еще:  Php trim string

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

    Восстановление базы данных и пароля root в MySQL

    Восстановления базы из дампа

    Через phpMyAdmin

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

    1. Открыть phpMyAdmin и выбрать требуемую БД.
    2. Затем перейти по ссылке «Импорт» – она находится в главном меню.
    3. В ней нужно найти и открыть раздел «Импортируемый файл», где нужно указать источник бэкапа базы.
    4. Подтвердить операцию нажатием «Вперед».
    5. Теперь остается только перезагрузить сервер MySQL.

    Как увеличить объем импортируемых баз данных

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

    Чтобы обойти дефолтные ограничения phpMyAdmin, нужно увеличить размер разрешенных к загрузке файлов. Это можно сделать как в настройках самой программы, так и на стороне сайта/сервера.

    Во втором случае (в файлах php.ini / .htaccess ) потребуется увеличить значения по умолчанию ряда опций, влияющих на загрузку:

    • upload_max_filesize («максимальный размер загружаемого файла»). Первоначальное значение: «2Mб».
    • post_max_size («максимальный размер POST-запросов»). Значение параметра должно быть больше, чем у «upload_max_filesize».
    • max_execution_time («время исполнения скрипта»). Чтобы снять ограничения с параметра, ему нужно присвоить значение «0».
    • max_input_time («время обработки входящих запросов»).

    Способы увеличения лимитов на исполнение php-скриптов

    1. В настройках конфигурационного файла phpMyAdmin ( config.inc.php ). В файл нужно добавить строки:

    После чего добавить туда же переменную, снимающую ограничения со времени исполнения скриптов (после загрузки базы данных ее лучше убрать):

    1. В пользовательском файле сайта ( php.ini ), где хранятся настройки исполнения php-скриптов. Файл «php.ini» можно найти, если открыть в браузере ранее добавленный (в корень сайта) php-файл. Например, ввести запрос вида «https://mysitename.com/myphpinfo.php», где «mysitename.com» — имя сайта, а «myphpinfo.php» — название php-файла. В открывшемся окне нужно найти параметры «Loaded Configuration File» или «Configuration File (php.ini) Path», где и будет указан путь к «php.ini».

    Добавляем в конце файла строки:

    1. В конфигурационном файле сервера ( .htaccess ), отвечающем, в том числе, за настройку обработки файлов на определенном сайте. Чтобы изменения сработали для всех файлов сайта, «.htaccess» должен обязательно находится в его корневой папке.

    Добавляем в файл строки:

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

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

    2. Далее следует загрузить файл дампа SQL с помощью команды «mysql»:

    3. В случае, когда дамп был сделан до того, поможет следующая команда:

    Следовательно, этими командами можно осуществить восстановление базы данных MySQL без особых трудностей.

    Восстановление баз данных с помощью таблиц

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

    Форматы таблиц

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

    Другой популярный формат таблиц — InnoDB. Он обладает более высоким быстродействием, снабжен функцией автоматического восстановления и устойчив к сбоям. Однако, для его ручного ремонта потребуется приложить некоторые усилия.

    Общий принцип восстановления

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

    Перед тем, как ее запустить, можно попытаться получить результат при помощи команды select … into out file . В большинстве случаев она дает возможность сохранить информацию, не прибегая к дополнительным операциям.

    Однако, если select … into out file не сработала (например, помешали незаконченные процессы), чтобы восстановить базу из файлов .frm, остается прибегнуть к расширенному параметру innodb_force_recovery.

    Читать еще:  Php sql connect

    1. Сначала нужно прописать в конфигурационном файле MySQL опцию innodb_force_recovery.

    Расположение конфигурационного файла может отличаться в зависимости от операционной системы. Искать его нужно по следующим путям, согласно указанному приоритету — сверху вниз.

    1. %WINDIR%my.ini (%WINDIR%my.cnf)
    2. C:my.ini (C:my.cnf)
    3. BASEDIRmy.ini (BASEDIRmy.cnf)

    «WINDIR» — папка установки Windows. Обычно, путь к ней выглядит так: C:WINDOWS .
    «BASEDIR» — папка, где установлена база MySQL. Например, для MySQL 8.0 полный путь к ней выглядит так: C:PROGRAMDIRMySQLMySQL 8.0 Server (где «PROGRAMDIR» — папка c программами Windows, обычно это Program Files).

    В дистрибутивах Linux:

    «SYSCONFDIR» — подкаталог, который использовался для компилирования MySQL (по умолчанию — etc ).

    В файле предусмотрена возможность установить для innodb_force_recovery несколько параметров. По умолчанию опция будет иметь вид «innodb_force_recovery = 0». Если установить другие числа (от 1 до 3) можно не только восстановить данные таблицы MySQL, но и процессы, незавершенные из-за аварийной остановки.

    2. После открытия my.cnf или my.ini необходимо найти в нем блок [mysqld] , куда и нужно прописать innodb_force_recovery. Пример:

    Чтобы применить данный параметр, следует перезапустить сервер MySQL.

    Восстановить структуру таблицы MySQL подобным образом можно только в случае, если имеются сохраненные копии файлов данных, журнала InnoDB и таблиц .frm InnoDB, а также конфигурационного файла my.cnf или my.ini.

    Замена значений параметра

    Главное, чего нужно придерживаться во время работы с innodb_force_recovery – последовательно менять значения параметров от 1 до 3 и перезапускать после этого сервер.

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

    • 1 (SRV_FORCE_IGNORE_CORRUPT) — позволит серверу запуститься даже в случае обнаружения поврежденной таблицы.
    • 2 (SRV_FORCE_NO_BACKGROUND) — предотвращает запуск основного процесса и других процессов очистки. Иными словами, если случится сбой во время операции очистки фоновых процессов он будет предотвращен благодаря этому значению.
    • 3 (SRV_FORCE_NO_TRX_UNDO) — не происходит откат транзакций по завершению восстановления после сбоя.
    • 4 (SRV_FORCE_NO_IBUF_MERGE) — предотвращает операции объединения вставленных данных из буфера, при этом не собираются данные статистики. Устанавливается режим InnoDB «только для чтения».
    • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN) — не учитываются логи отмены (undo logs) при запуске базы данных. InnoDB учитывает даже незавершенные операции как выполненные. Устанавливается режим InnoDB «только для чтения».
    • 6 (SRV_FORCE_NO_LOG_REDO) — отключение ведения логов отката изменений (redo logs) в связи с восстановлением. Оставляет таблицы в неактуальном состоянии, что в свою очередь может внести больше повреждений в Б-деревья и другие структуры базы данных. Устанавливается режим InnoDB «только для чтения».

    Пользоваться значениями 4, 5 и 6 не стоит тем, кто не имеет достаточного опыта работы с MySQL таблицами. С их применением в несколько раз возрастает вероятность полной потери информации.

    Восстановление пароля

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

    Использование init-file

    Во время запуска MySQL есть возможность сообщить сервису о файле, в котором находятся исполняемые команды SQL. Его адрес следует указать с помощью параметра «init-file».

    1. В первую очередь необходимо создать файл «init-file»:

    2. Далее нужно добавить в файл следующую строку:

    3. Далее следует отключить сервис, если он работает:

    4. Затем можно запустить свой файл:

    5. Остается подождать немного, пока все будет работать, как надо, и далее остановить данный процесс. В терминале будет отображен вывод «started as proccess» и PID (номер-идентификатор) процесса. Последний как раз и нужно выключить. К примеру*:

    * Значение PID приведено для примера. Следует заменить его на актуальное.

    6. Теперь можно запустить MySQL стандартным способом и попробовать авторизоваться с помощью нового пароля:

    Использование skip-grant-tables

    Помимо — init-file можно выполнить сброс пароля с использованием другого параметра –skip-grant-tables . Если запустить с ним сервис, будет пропущена загрузка данных пользователей, что позволяет войти без необходимости вводить логин и пароль.

    1. Здесь также сначала требуется отключить базу данных:

    2. Дальше нужно запустить вручную MySQL следующей командой:

    3. Теперь можно открыть консоль для работы с MySQL:

    4. Поскольку загрузка была осуществлена без привилегий пользователей, таблицы с ними теперь нужно подгрузить:

    5. На этой стадии можно менять пароль пользователя root:

    6. Можно закрывать консоль управления:

    7. Остается выключить сервис*, как и в приведенном выше способе:

    * Значение PID приведено для примера. Следует заменить его на актуальное.

    8. И, наконец, запустить MySQL в стандартном режиме работы:

    9. После этого появится возможность авторизации с помощью нового пароля:

    Начни экономить на хостинге сейчас — 14 дней бесплатно!

    Ссылка на основную публикацию
    Adblock
    detector