Links

Работа с Базой Данных

Общая информация о phpMyAdmin

phpMyAdmin (PMA) - это программный продукт для работы с базами данных со множеством возможностей, которые подробно представлены в официальной документации проекта.
В данном разделе мы опишем наиболее популярные операции, совершаемые при помощи phpMyAdmin.
Зайти в PMA можно тремя способами:
  • Как пользователь базы данных по ссылке вида servername.timeweb.ru/pma/, где servername - имя сервера, на котором расположен ваш аккаунт. Посмотреть имя сервера можно на главной странице панели управления в блоке "Информация о сервере". Для авторизации понадобится указать имя пользователя базы данных (совпадает с названием базы данных) и пароль доступа к БД.
  • Как пользователь базы данных по ссылке phpMyAdmin, расположенной под именем нужной базы данных в разделе панели управления "Базы данных MySQL". При входе нужно ввести пароль для выбранной базы данных.
  • Как общий пользователь с доступом ко всем базам данных аккаунта по ссылке phpMyAdmin в разделе панели управления "Базы данных MySQL", если включен переключатель "Полный доступ".

Экспорт и импорт

Самые популярные действия с базами данных на хостинге - это импорт и экспорт.
Импортирование базы данных - это загрузка сохраненной на локальном компьютере базы данных на сервер.
Экспортирование базы данных - это создание резервной копии (дампа) базы данных на локальном компьютере.
Чтобы осуществить импорт базы данных, необходимо выполнить следующие действия:
  1. 1.
    В области слева выберите необходимую базу данных (должен раскрыться список таблиц базы данных).
  2. 2.
    Перейдите на закладку "Импорт" в меню PMA.
  3. 3.
    Загрузите импортируемый файл (дамп базы данных), нажав на кнопку "Выберите файл" и указав файл на локальном компьютере.
  4. 4.
    Выберите кодировку, формат и проверьте другие настройки импорта.
  5. 5.
    Нажмите кнопку "Вперед" и осуществите импорт дампа в базу данных.
При загрузке дампов баз данных размером более 1000 Мб могут наблюдаться проблемы. В случае, если ваш дамп имеет больший размер, вы можете выполнить импорт через командную строку с помощью подключения по SSH.
Чтобы осуществить быстрый экспорт базы данных, выполните следующее:
  1. 1.
    В области слева выберите необходимую базу данных (должен раскрыться список таблиц базы данных).
  2. 2.
    Перейдите на закладку "Экспорт" в меню PMA.
  3. 3.
    Нажмите кнопку "Вперед", и файл дампа базы данных сохранится на локальный компьютер.

Выполнение SQL-запросов

Для выполнения SQL-запроса выберите базу данных из меню слева и перейдите на вкладку "SQL". В поле запроса введите команду и нажмите "Вперед" для ее выполнения.

Определение текущей кодировки

Чтобы узнать кодировку базы данных, выполните следующий SQL-запрос:
SHOW VARIABLES LIKE "character\_set\_database";

Изменение кодировки базы данных

Чтобы изменить кодировку базы данных, выполните SQL-запрос ниже, заменив "cp1251" на нужную вам кодировку:
ALTER DATABASE имя_базы DEFAULT CHARACTER SET cp1251
Обратите внимание, что данный SQL-запрос не изменит кодировку таблиц базы данных!

Изменение кодировки таблиц

Для смены кодировки таблиц используйте запросы ниже, заменив 'cp1251' на нужную вам кодировку:
ALTER TABLE имя_таблицы CONVERT TO CHARACTER SET cp1251ALTER TABLE имя_таблицы DEFAULT CHARACTER SET cp1251

Размер всех баз данных или таблиц

С помощью запроса ниже можно получить сводную таблицу всех баз данных с указанием размера каждой из них (в мегабайтах):
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;
Вывод выглядит следующим образом:
Иногда может быть полезно узнать размер всех таблиц во всех базах и отсортировать их по объему:
SELECT table_schema as `Database`, table_name AS `Table`,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`FROM information_schema.TABLESORDER BY (data_length + index_length) DESC
Пример вывода:
Для вывода подробной информации обо всех таблицах конкретной базы выберите ее в phpMyAdmin в списке слева и выполните запрос:
SHOW TABLE STATUS
Здесь вы можете просмотреть, например, размер файла индекса для каждой таблицы (в байтах) - столбец index_length.
Похожим образом можно получить информацию о конкретной таблице выбранной базы:
SHOW TABLE STATUS LIKE 'имя_таблицы'
Тогда в выводе будет представлена только интересующая вас таблица.

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

Основные опции

kill - завершить запросы, подходящие под заданные параметры;
print - вывести запросы, подходящие под заданные параметры;
busy-time - длительность запроса;
interval - как часто проверять наличие запросов, подходящих под заданные параметры (если interval не задан, значением по умолчанию будет половина busy-time);
run-time - как долго работать pt-kill (если run-time не задан, pt-kill после запуска будет работать постоянно).

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

  • Завершить все запросы к базе с длительностью более 60 секунд:
  • Вывести все запросы длительностью более 60 секунд (не завершая их):
pt-kill --busy-time 60 --kill --print --user 'база_данных' --password 'пароль_базы_данных'
pt-kill --busy-time 60 --print --user 'база_данных' --password 'пароль_базы_данных'
  • Проверять наличие запросов, находящихся в состоянии ожидания, каждые 10 секунд и завершать их:
pt-kill --match-command Sleep --kill --victims all --interval 10 --user 'база_данных' --password 'пароль_базы_данных'
  • Логировать все запросы длительностью более 60 секунд в файл logs.txt:
pt-kill --busy-time 60 --print --user 'база_данных' --password 'пароль_базы_данных' >> /путь/к/файлу/logs.txt
  • Запустить pt-kill на 300 секунд и каждые 10 секунд завершать запросы длительностью более 60 секунд:
pt-kill --busy-time 60 --kill --interval 10 --run-time 300 --user 'база_данных' --password 'пароль_базы_данных'

Запуск pt-kill по cron

Утилиту pt-kill можно также запускать по расписанию с помощью cron.
Для этого необходимо:
1) Создать файл скрипта с произвольным именем (например, pt-kill.sh) и сохранить его в необходимой директории аккаунта.
2) Добавить в файл нужную команду, например:
pt-kill --busy-time 300 --kill --interval 5 --run-time 30 --user 'база_данных' --password 'пароль_базы_данных' >> /путь/к/файлу/killed_queries.log
3) Добавить задачу cron, указав периодичность выполнения и путь до файла:
*/10 * * * * /bin/bash /home/u/user/pt-kill.sh # вместо u и user укажите первую букву логина и сам логин
Таким образом, pt-kill будет запускаться каждые 10 минут на 30 секунд, в течение этого времени каждые 5 секунд будет проверять и завершать запросы длительностью более 300 секунд, а также вести лог "убитых" запросов в файл killed_queries.log.
Задание также можно добавить через планировщик Crontab в ПУ:

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

Между ключами (-u и -p) и следующими за ними параметрами 'базаданных' и 'парольбазы_данных' не должно быть символа пробела.
Для восстановления базы данных из дампа (при условии, что дамп не был сжат и имеет расширение .sql) в SSH-клиенте необходимо выполнить команду:
mysql -u'имя_пользователя' -p 'имя_базы' < имя_дампа.sql
Для дампа с расширением .sql.zip необходимо выполнить команду:
unzip -p имя_дампа.sql.zip | mysql -u'имя_пользователя' -p 'имя_базы'
Для дампа с расширением .sql.gz:
zcat имя_дампа.sql.gz | mysql -u'имя_пользователя' -p 'имя_базы'

Возможные ошибки

Процесс прерывается из-за ограничений на хостинге

иногда бывает, что на хостинге установлены ограничения на использование процессами процессоров сервера, в связи с чем распаковка тяжелых архивов может завершаться принудительно. В таких случаях (за исключением дампов с расширением .sql.zip) вы можете использовать команду pv, например:
cat имя_дампа.sql | pv -L 2M | mysql -u'имя_пользователя' -p 'имя_базы'

ERROR #1273: Unknown collation

При импорте может наблюдаться подобная ошибка:
cat имядампа.sql | mysql -u'базаданных' базаданных -p'парольбазы_данных' ERROR 1273 (HY000) at line 29: Unknown collation: 'utf8mb4_unicode_520_ci'
В этом случае, оставив исходный файл без изменений, создайте копию дампа, заменив в ней строки, отвечающие за Collation и кодировку:
cat имя_дампа.sql | sed 's/utf8mb4_unicode_520_ci/utf8_unicode_ci/g' | sed 's/utf8mb4/utf8/g' > имя_дампа_new.sql
После чего осуществите импорт измененного дампа стандартным способом.

ERROR #1062: Duplicate entry

Также может встречаться следующая ошибка:
ERROR 1062 (23000) at line 53: Duplicate entry '1' for key 'PRIMARY'
Для ее исправления можно использовать команду:
cat имя_дампа.sql | sed 's/INSERT/REPLACE/' > имя_дампа_new.sql

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

Для создания дампа (резервной копии) необходимо выполнить следующую команду:
mysqldump -u'имя_пользователя' -p 'имя_базы' > имя_дампа.sql

Проверка базы данных на наличие ошибок

mysqlcheck -c -u'имя_пользователя' -p 'имя_базы'

Восстановление базы данных при наличии ошибок

mysqlcheck -r -u'имя_пользователя' -p 'имя_базы'