Путь до файла с базой в формате id

Обновлено: 07.07.2024

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

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

Существует два способа импорта/экспорта:

через веб-интерфейс PhpMyAdmin;
подключившись к консоли MySQL локально или через SSH.

Подробнее о подключении через протокол SSH можно узнать здесь.

Все описанные действия с MySQL схожи по выполнению на любых операционных системах Linux.

Экспорт базы данных с помощью PhpMyAdmin

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

Выбрав необходимую базу данных, нужно перейти в раздел «Экспорт».

Для быстрого экспорта подойдёт вариант «Быстрый – отображать минимум настроек», при котором используются оптимальные параметры переноса. Чтобы открыть список дополнительных опций, нужно выбрать метод «Обычный – отображать все возможные настройки».

Во вкладке «Формат» рекомендуется выставить расширение SQL (.sql) для экспортируемого файла. Но также можно использовать и другие форматы, такие как CSV (.csv) и XML (.xml).

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

Экспорт базы данных MySQL через консоль

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

Переменные в приведенном примере:

«Login» – имя пользователя MySQL.
«Password» – пароль пользователя MySQL.
«db_name» – наименование базы данных.
«db_name.sql» – конечный файл.

MySQLdump позволяет экспортировать базу данных с удаленного сервера (если хосту разрешено подключение):

*Вместо «XXX.XXX.XXX.XXX» указывается актуальный IP-адрес сервера.

Импорт базы данных с помощью PhpMyAdmin

Для начала необходимо авторизоваться в интерфейсе PhpMyAdmin и выбрать базу данных в левом верхнем углу. После этого стоит перейти в раздел «Импорт».

Теперь нужно нажать «Выбрать файл» и в открывшемся проводнике выбрать SQL-файл (принимаются также архивы Gzip, Bzip2, Zip). В разделе «Формат» отмечается расширение используемого файла.

Все остальные параметры изменять необязательно. Завершить импорт можно нажатием кнопки «Вперёд»

Импорт базы данных MySQL через консоль (замена существующей)

Для начала необходимо авторизоваться в командной строке MySQL:

*Вместо «Login» и «Password» вводятся действительные данные пользователя MySQL.

Теперь выбирается существующая база данных, с которой планируется дальнейшая работа:

*Вместо «db_name» вводится наименование существующей БД.

Остаётся выполнить импорт таблиц следующей командой:

*Вместо «db_name.sql» вводится путь до файла SQL (предварительно загружается на сервер).

Импорт и создание базы данных MySQL через консоль

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

Для начала необходимо авторизоваться в консоли MySQL:

*Вместо «Login» и «Password» вводятся действительные данные пользователя MySQL.

Далее нужно создать новую базу данных:

*Вместо «new_database» вводится собственное наименование базы данных.

На экране появится следующий вывод:

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

Файлы базы MySQL/MariaDB данных хранятся на жёстком диске. По умолчанию это папка data внутри директории исполнимых файлов MySQL (в Windows) или в директории /var/lib/mysql/ (Linux).

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

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

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

Определение пути без подключения к серверу СУБД

В Windows

В ОС Windows для получения пути до базы данных введите команду:

В Linux

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

Переменная @@datadir;

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

И затем в консоли MySQL/MariaDB выполните:

Как видно на скриншоте, путь до файлов базы данных на сервере, где введена команда, c:\Server\data\DB\data\.

Ещё один вариант выполнения данной команды:

Как узнать настройки директорий в MySQL/MariaDB

Чтобы показать информацию о всех настройках директорий СУБД сервера, выполнит команду:

Пример вывода в Windows для MariaDB:

Чтобы вывести только datadir выполните команду:

Как узнать путь до базы данных MySQL/MariaDB в скрипте

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

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

Также не забывайте менять пользователя root на имя вашего пользователя.

В поисках пути отношения

Вы можете увидеть путь до таблицы используя:

но что насчет обратного процесса, получения названия объекта из пути до него? Существует функция pg_filenode_relation, которая кажется подходящей для этого… но чтобы ее использовать, необходимо быть подключенным к конкретной базе данных, к которой относится этот файл, что подразумевает знание этой связи.

Структура пути до файла

Вот каким образом можно определить путь до таблиц и баз данных в современной версии PostgreSQL. (Более старые версии используют другой формат, про который можно почитать здесь).

Имеется 3 основных варианта пути:

Для файлов в табличном пространстве по умолчанию, base/database_oid/filenode id for the relation
Для файлов из прочих табличных пространств: pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id отношения
Для общих отношений: global/filenode id отношения

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

Обратите внимание, я употребил формулировку "filenode id отношения", а не "oid отношения". Это связано с тем, что PostgreSQL имеет карту relfilenode в файле с именем pg_relfilenode.map для каждой базы данных/табличного пространства. Имена файлов таблиц вовсе не обязательно совпадают с их oid'ами из pg_class, и они могут измениться после запуска VACUUM FULL, TRUNCATE и прочих. К примеру:

Итак. Как превратить этот путь обратно в имя отношения?

Oid'ы базы данных и filenode ids отношения

Предположим, Вы получили ошибку из начала этой статьи. Ее можно разбить на несколько частей:

base: в табличном пространстве по умолчанию
16396: в базе данных с oid'ом 16396
3720450 filenode id для таблицы с oid'ом 3720450

Определение базы данных по oid

Во-первых, необходимо подключиться к любой базе данных в этом PostgreSQl процессе и выполнить:

(или любой другой oid базы, который вы имеете). Это вернет Вам имя базы данных.

После этого необходимо подключиться к этой базе.

Обратное преобразование relfilenodes на 9.4 версии

Если Вы используете версию 9.4, или более свежую, то для Вас следующая часть проста:

(0 означает «табличное пространство по умолчанию»)

Эта функция выполняет обратное преобразование relfilenode за вас. Таким образом, она просто покажет Вам имя таблицы. Для него не будет показана связь с какой-то схемой, если полученное имя таблицы принадлежит текущему search_path; Можно использовать SET search_path = ''; перед выполнением функции, для того, чтобы был указан путь вплоть до схемы.

Вы должны быть подключены к правильной базе данных, или будет получен неправильный ответ, либо вообще ответ не будет получен.

Обратное преобразование relfilenodes на 9.3 версии

Если вы используете версию 9.3, или старее, необходимо подключиться к базе данных, в которой находится таблица и выполнить следующий запрос к pg_class:

(или любой другой полученный relfilenode id таблицы).

Это расскажет Вам о том, к какой таблице относится эта ошибка.

Нет результатов?

Что ж, обычно это помогает.

Relfilenode также может быть нулем, это в свою очередь означает, что файл расположен посредством pg_relfilenode.map. Это является типичным сценарием для общих и некоторых системных каталогов, их индексов, TOAST таблиц и т.д. К примеру, это могут быть pg_database, pg_class и pg_proc.

Что насчет схемы?

Вы обратили внимание, что схема (пространство имен) не фигурирует в пути?

В PostgreSQL схемы является только пространством имен внутри базы данных. Они не имеют никакого влияния на то, где физически хранятся таблицы на диске.

Другие пути табличных пространств

Недавний случай, с которым я столкнулся, был следующей ошибкой:

Это не табличное пространство по умолчанию, так как путь начинается с pg_tblspc.

Сам процесс нахождения таблицы на самом деле тот же. Можно проигнорировать pg_tblspc/nnn/PG_n.n_nnnnnn/ часть и сфокусироваться сразу на database_oid/relation_oid, как описано выше для случаев с табличным пространством по умолчанию. Для этого стоит понимать что означает путь.

Таким образом текст ошибки разбивается на следующие части:

pg_tblspc: это не табличное пространство по умолчанию
16709: это табличное пространство с oid'ом 16709
PG_9.3_201306121: используется PostgreSQL 9.3 с версией каталога 201306121.
16499: база данных с oid'ом 16499
19401 таблица с relfilenode id 19401

Так что насчет части с табличным пространством?

pg_tblspc является директорией в директории данных PostgreSQL, которая содержит в себе символьные ссылки ко всем положениям табличных пространств (или на NTFS, точки соединения для них). Каждая символьная ссылка названа в честь oid табличного пространства. Именно так PostgreSQL находит табличные пространства. SQL команды к табличным пространствам оперируют этими ссылками.

Oid относится к pg_tablespace записи для табличного пространства, как видно из:

Внутри директории табличного пространства, имеется еще одна директория, имеющая название, соответствующее версии PostgreSQL. Оно статично для этой версии и единственное применение этому — это множественный доступ нескольких PostgreSQL процессов к одному табличному пространству, например, во время pg_upgrade. Как правило, имеется только одна запись.

В целом же, структура та же как и для base/ путей — сначала oid базы данных, потом oid отношения.

Глобальные (общие) таблицы

Имеется еще третья категория ошибок, в случае если Вы ее наблюдаете, то Вы определенно в беде. PostgreSQL имеет общие каталоги — таблицы, которые имеют одинаковое содержимое в каждой базе данных. Они обитают в специальном табличном пространстве с relfilenode id 16709.

Пути к ним начинаются с global вместо base и у них отсутствует компонент с oid'ом базы данных.

Общие каталоги не отмечены relfilenode в pg_class. То есть Вы не сможете посмотреть, к примеру, pg_database из pg_class. pg_filenode_relation возвращает null, независимо от того, вызывать ли его с oid'ом табличного пространства по умолчанию, или же с oid'ом глобального табличного пространства 1664.

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

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

Имея дело с повреждениями

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

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

Как работает Liquibase

databasechangelog хранит когда какие скрипты запускались, (а databasechangeloglock ставит блокировки, если несколько экземпляров приложения запускаются на одной базе одновременно)

В итоге получается, что при запуске приложения на пустой базе, она заполнится (таблицами и данными, которые есть в скриптах) и накатятся все изменения (которые есть в скриптах). А при запуске приложения на заполненной базе последней версии, скрипты не накатятся. При запуске же приложения на частично устаревшей базе, накатятся только те скрипты, которые еще не выполнялись. Отлично, теперь не надо помнить, на какой базе уже выполнены скрипты, а на какой нет. Устраняется путаница.

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

Перенос существующей базы данных в Liquibase-формат

Это можно сделать с помощью maven-плагина, но мы рассмотрим, как сгенерировать файл со скриптами (у нас он будет называться master.yaml) из командной строки. Для этого нужно установить на компьютер Liquibase.

Затем в любой папке компьютера создаем файл liquibase.properties с:

параметрами доступа к базе,
путем до драйвера,
именем выходного файла (например, master.yaml)
названием папки с данными базы.

Например, для PostgreSQL-базы можно положить драйвер postgresql-42.2.14.jar прямо в ту же папку, где находится файл liquibase.properties. А в файле liquibase.properties написать:

Затем из командной строки выполним:

Пример сгенерированного скрипта

Пусть в нашем приложении есть единственная сущность Animal:

которой соответствует таблица animal в базе. В таблице две строки.

После запуска команды:

содержимое master.yaml будет таким:

Тут три changeSet (каждому соответствует свой идентификатор):

создание таблицы animal
заполнение ее данными из файла data/animal.csv
создание sequence (для генерации идентификаторов id, так как в сущности @Animal мы используем strategy= GenerationType.SEQUENCE)

Таким образом всё, что есть в базе, перенеслось в набор changeSet-ов.

Теперь внесем в Spring Boot приложение зависимость Liquibase, настроим и ее на запуск полученных changeSet-ов. Так при запуске Spring Boot приложения на пустой базе она будет заполняться таблицами и данными с нуля.

Заполнение базы из Liquibase-файлов с приложением Spring Boot

Мы слегка переделаем master.yaml, отделив создание таблицы и последовательности от создания данных. Это нужно для того, чтобы в будущем было проще дополнять changeSet-ы со своими изменениями.

Maven-зависимость

Во-первых, надо добавить в Spring Boot приложение Maven-зависимость:

Настройки в application.yml

Во-вторых, чтобы библиотека Liquibase выполняла невыполненные скрипты при запуске приложения, в application.yml необходимо ее включить (enabled=true):

Структура папок

По пути db/changelog/1.0/ сейчас лежит один файл 2020-20-02-create-tables.yaml.

В него мы скопируем два сгенерированных changeSet-а из трех: а именно, создание таблицы и создание последовательности. Таким образом, содержимое файла такое:

Файл initial-data.yaml отвечает за загрузку данных. В него скопирован третий changeSet:

Обратите внимание, что копию мы немного поменяли. Тут добавлено свойство relativeToChangelogFile: true. Оно означает, что путь data/animal.csv до файла с данными берется относительно самого файла initial-data.yaml.

Запуск Spring Boot приложения

Теперь приложение можно запускать на новой пустой базе. (Предварительно нужно только создать саму базу с нужным именем и прописать к ней spring.datasource настройки в application.yml).

При первом запуске Spring Boot приложения Liquibase заполнит базу, а также создаст две служебные таблицы Liquibase. В них сохранится сам факт запуска скриптов с данными идентификаторами. Поэтому при втором запуске Spring Boot приложения на этой базе Liquibase не будет повторно выполнять скрипты.

Как отделить скрипты для тестовой базы

Этот changeSet выполнится только в тестовой базе.

Откуда взялось значение test? Оно прописывается в файле application.yaml, в данном случае в том, что находится в папке тестов test:

При этом контекст dev прописан в основном application.yaml папки main:

Теперь наше Spring Boot приложение с maven-зависимостью Liquibase, включенной настройкой spring.liquibase.enabled=true и размещенными в ресурсах changeSet-ами можно запускать на пустой базе. При первом запуске база будет заполнена, при последующих запусках останется такой же.

Исходный код примера есть на GitHub: там Spring Boot приложение с готовыми Liquibase-скриптами.

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