Mysql залить дамп через консоль

Обновлено: 02.07.2024

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

Сегодня я решил опубликовать свои шпаргалки, на самые часто встречающиеся ошибки при работе с MySQL.

Работа с бекапами

Делаем бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump --no-data - u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Создаём бекап и сразу его архивируем
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

Заливаем архив бекапа в базу
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

-Q оборачивает имена обратными кавычками
-c делает полную вставку, включая имена колонок
-e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Для просмотра списка баз данных можно использовать команду:
mysqlshow -u USER -pPASSWORD

А так же можно посмотреть список таблиц базы:
mysqlshow -u USER -pPASSWORD DATABASE

Для таблиц InnoDB надо добавлять --single-transaction, это гарантирует целостность данных бекапа.
Для таблиц MyISAN это не актуально, ибо они не поддерживают транзакционность.

Общие факты

Полезно под каждую базу на боевом сервере создавать своего пользователя
Кодировка базы может быть любой, если она UTF8
В большинстве случаев лучше использовать движок InnoDB
В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli
Новую копию MySQL всегда можно настроить и оптимизировать
Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов
ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

Работа с данными

Числа

На 32-битных системах практически нет смысла ставить для типа INTEGER свойство UNSIGNED, так как такие большие числа в php не поддерживаются.
На 64-битных системах, php поддерживает большие числа, вплоть до MySQL BIGINT со знаком.
Связанные таблицы («Foreign keys») должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично
Для хранения булевых значений, нужно использовать TINYINT(1)
А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99

Строки

В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов
Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь «LONGTEXT»
Самая правильная кодировка для вашей БД UTF8

DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку
Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов, например:
SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

Перечисления

Для перечислений правильно использовать тип ENUM
Правильно пишется так: ENUM('мама', 'мыла', 'раму')
Можно ставить значение по-умолчанию, как и для любой строки
В базе поле с перечислением хранится как число, поэтому скорость работы — потрясающе высокая
Количество перечислений

Отладка

Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf
А потом оптимизировать запросы через EXPLAIN
И наблюдать за запросами удобно через программу mytop

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

Доброго времени суток, коллеги 🙂

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

Я уже написал статьи о том, как производить основные действия с данными MySQL через консоль иm делать бэкап базы MySQL, а также экспорт хранимой в ней информации.

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

Но, перед тем, как мы приступим к обзору способов и инструментов, пару слов о том, что такое импорт базы данных MySQL, каким он бывает и как его лучше всего делать?

Импорт базы данных MySQL: что и зачем?

Импорт, как и экспорт БД MySQL, бывает двух видов информации, хранящейся в базе:

структуры базы, её таблиц и хранимых в них данных (в простонародье именуемых дампом БД);
просто данных, хранящихся в таблице либо собранных с помощью SELECT запросов.

В данной статье будут рассмотрены оба варианта.

Для восстановления из дампа MySQL базы данных с её структурой и всей хранимой информацией, как уже было сказано, нужен файл дампа БД, который представляет из себя текстовый файл с любым расширением (предварительно может быть запакован в архив для уменьшения размера), содержащий SQL команды для создания самой базы и таблиц, а также наполнения их информацией.

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

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

Для этих целей подойдёт и обычный txt файл, данные в котором будут разделены, либо файлы, создаваемые в специальных табличных редакторах (Microsoft Office Excel, OpenOffice и т.д.), имеющих отличное расширение: xls, csv, odt и др.

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

Добавление данных в MySQL: инструменты

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

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

Консоль сервера и командная строка MySQL;
Скрипты, написанные на языках программирования, позволяющие делать запись данных в MySQL с помощью языковых средств;
Готовые программы, предоставляющие визуальный интерфейс для работы с БД (тот же самый phpMyAdmin, MySQL WorkBench, MySQL Manager и др.).

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

Так или иначе, во главе всего лежит консоль, а остальные инструменты, по сути, являются её эмуляторами.

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

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

Думаю, все, кто хоть раз пытался загрузить дамп в MySQL БД большого размера через phpMyAdmin, понимают, о чём я говорю.

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

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

Надеюсь, что я замотивировал вас делать импорт базы данных MySQL через консоль (причём, как её структуры, так и отдельно данных).

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

Как восстановить MySQL базу из дампа через консоль?

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

с помощью команды в командной строке MySQL;
в самой консоли сервера.

Начнём по порядку.

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

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

После того, как вы сделаете указанное, вводим в MySQL Shell следующую команду:

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

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

В Linux это можно сделать следующим образом:

В Windows стандартной утилиты для распаковки архива в консоли нет, поэтому её нужно будет установить дополнительно.

Как видите, импорт дампа MySQL через консоль – операция весьма простая, которая выполняется одной командой. Так что для выполнения данной процедуры не обязательно быть разработчиком.

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

Кстати, описанными способами возможен также и импорт таблицы MySQL, а не целой базы. В этом случае в заливаемом вами дампе должны присутствовать операции её создания и наполнения данными.

Загрузка данных в MySQL базу из файла в консоли

О восстановлении БД MySQL из дампа в консоли мы поговорили. Теперь самое время разобраться с тем, как аналогичным образом можно импортировать данные из файлов, в том числе из xls и csv в MySQL базу.

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

Снова начнём обзор по порядку.

Итак, для импорта файла в MySQL командной строке мы снова запускаем её и переходим на БД, в которую будут загружаться данные.

А далее прописываем в консоли следующую SQL команду:

О том, как узнать её значение и изменить его, подробно написано в статье об экспорте базы данных MySQL.

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

Данная утилита является аналогом SQL команды LOAD DATA INFILE, только для командной строки. Но, спрашивается, почему тогда среди параметров её вызова не указана таблица, в которую будут загружаться данные из файла?

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

Т.е. если вы захотите сделать импорт из Excel таблицы в MySQL таблицу users, то ваш файл должен называться users.xls.

Расширение у импортируемого файла, как уже говорилось, может быть любым.

С помощью mysqlimport также можно загружать сразу несколько файлов xls или csv в MySQL. Чтобы данные попали по назначению, названия файлов и таблиц БД, как и в предыдущем примере, также должны совпадать.

Естественно, что в своих примерах я не рассмотрел полный список параметров mysqlimport, т.к. некоторые из них весьма специфичны и на практике используются крайне редко.

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

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

У самих команд импорта баз данных MySQL таких опций, к сожалению, нет.

Единственное, для увеличения скорости при загрузке большого дампа БД можете использовать следующую фишку.

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

2. Прописываем в начале файла следующие строки:

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

3. В конце файла прописываем обратные действия:

Кстати, данные команды помогут не только ускорить процесс импорта, но и сделать его возможным.

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

Т.е. выполняется поиск в БД таблицы с таким же именем, как и у импортируемой, и если таковая найдена, то она удаляется и создаётся заново.

И если вдруг существующая таблица будет связана внешними ключами с другими, то вся загрузка провалится.

Поэтому отключение проверки существования внешних ключей и прочих – это ещё и отличная гарантия успешного выполнения процесса импорта базы данных MySQL.

Особенности импорта csv в MySQL БД и других файлов

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

Причём, в отличие от предыдущей ситуации, в данном случае прописать директивы в файл не получится, т.к. SQL команды в нём не будут восприниматься и выполняться.

В предыдущей статье об экспорте базы MySQL я уже упоминал о том, как это сделать с помощью следующей операции в командной строке MySQL:

Однако, там я не упомянул, что системная переменная MySQL FOREIGN_KEY_CHECKS имеет два значение: глобальное и сессионное (для текущей сессии).

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

Сессионное значение системной переменной MySQL устанавливается только на время сеанса работы пользователя с сервером MySQL. Сеанс или сессия начинается при подключении клиента к серверу, при котором ему присваивается уникальный connection id, и заканчивается при отключении от сервера, которое может произойти в любой момент (например, по таймауту).

Почему я об этом решил вспомнить?

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

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

В приведённых командах переменная явно помечается как сессионная.

А, поскольку, я выполнял загрузку csv файла в MySQL таблицу через консоль сервера, без прямого подключения к серверу MySQL, то сеанс и не был создан, в рамках которого работало бы моё сессионное значение переменной.

В итоге я установил глобальное значение FOREIGN_KEY_CHECKS, и импорт успешно выполнился.

Сделать это можно одним из перечисленных способов:

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

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

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

Файлы резервных копий, созданные утилитой mysqldump, в основном представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может создавать файлы в форматах CSV и XML.

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

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

Синтаксис команды Mysqldump

Прежде чем перейти к использованию команды mysqldump, давайте начнем с обзора основного синтаксиса.

Выражения утилиты mysqldump имеют следующую форму:

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

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

Например, чтобы создать резервную копию базы database_name с именем database_name используя пользовательский root и сохранить ее в файл с именем database_name.sql вы должны выполнить следующую команду:

Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.

Если вы вошли в систему как тот же пользователь, которого используете для экспорта, и этот пользователь не требует пароля, вы можете опустить параметры -u и -p :

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

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

Приведенная выше команда создаст файл дампа, содержащий обе базы данных.

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

Используйте параметр --all-databases для резервного копирования всех баз данных MySQL:

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

Резервное копирование всех баз данных MySQL в отдельные файлы

Утилита mysqldump не предоставляет возможности резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла FOR bash :

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

Создание сжатой резервной копии базы данных MySQL

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

Создать резервную копию с отметкой времени

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

Приведенная выше команда создаст файл в следующем формате database_name-20180617.sql

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

Вы можете восстановить дамп MySQL с помощью инструмента mysql . Общий синтаксис команды следующий:

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

В следующем примере первая команда создаст базу данных с именем database_name а затем импортирует в нее дамп database_name.sql :

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

Если вы создали резервную копию всех своих баз данных с помощью параметра -all-databases и хотите восстановить одну базу данных из файла резервной копии, содержащего несколько баз данных, используйте параметр --one-database как показано ниже:

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

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

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

Автоматизируйте резервное копирование с помощью Cron

Автоматизировать процесс резервного копирования баз данных так же просто, как создать задание cron, которое будет запускать команду mysqldump в указанное время.

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

Создайте файл с именем .my.cnf в домашнем каталоге пользователя:

Скопируйте и вставьте следующий текст в файл .my.cnf.

Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.

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

Создайте каталог для хранения резервных копий:

Откройте ваш пользовательский файл crontab:

Добавьте следующее задание cron, которое будет создавать резервную копию базы данных с именем mydb каждый день в 3 часа ночи:

Не забудьте заменить username своим фактическим именем пользователя. Мы также избегаем знаков процента ( % ), потому что они имеют особое значение в crontab.

Вы также можете создать другое задание cron для удаления любых резервных копий старше 30 дней:

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

Выводы

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

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

Вы также можете проверить руководство о том, как сбросить пароль root MySQL, если вы его забыли.

Если у вас есть какие-либо вопросы или отзывы, не стесняйтесь оставлять комментарии.

Аноним, Какая стандартная тема?
Light и Dark Стандартная тёмная тема. Есть возможность переключить на светлую.

thx

На 6Gb базу таким образом развернул. Всё отлично, автору всего самого доброго и хорошего)

Аноним, Программа, действительно, говно. Видео, снятое айфоном, неизбежно сохраняет в худшем качестве, хоть на максимум все доступные бегунки выверни. При этом файл будет весить в десятки (. ) раз больше, а качество говно! Полностью согласен сохраняет в говно и размером в разы больше. Как так можно испохабить бочку меда ложкой дегтя

Аноним, Здравствуйте, подскажите почему камера не записывает данные при отсутствии сеети. Карта памяти установлена в камере?

Здравствуйте, подскажите почему камера не записывает данные при отсутствии сеети.

ты спамер, клоун

Аноним, Не помогло. Перепробовал всё. Один фиг запрашивает логин и пароль. Любые введи - сработает

спасибо в mi 12 в разделе безопасность

Какая стандартная тема?
Light и Dark

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