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

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

phpMyAdmin (PMA) - это программный продукт для работы с базами данных со множеством возможностей, которые подробно представлены в официальной документации проекта.

В данном разделе мы опишем наиболее популярные операции, совершаемые при помощи phpMyAdmin.

Зайти в PMA можно тремя способами:

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

• Как пользователь базы данных по ссылке phpMyAdmin, расположенной под именем нужной базы данных в разделе панели управления "Базы данных MySQL". При входе нужно ввести пароль для выбранной базы данных.

• Как общий пользователь с доступом ко всем базам данных аккаунта по ссылке phpMyAdmin в разделе панели управления "Базы данных MySQL", если включен переключатель "Полный доступ".

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

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

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

Экспортирование базы данных - это создание резервной копии (дампа) базы данных на локальном компьютере.

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

1. В области слева выберите необходимую базу данных (должен раскрыться список таблиц базы данных).

2. Перейдите на закладку "Импорт" в меню PMA.

3. Загрузите импортируемый файл (дамп базы данных), нажав на кнопку "Выберите файл" и указав файл на локальном компьютере.

4. Выберите кодировку, формат и проверьте другие настройки импорта.

5. Нажмите кнопку "Вперед" и осуществите импорт дампа в базу данных.

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

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

1. В области слева выберите необходимую базу данных (должен раскрыться список таблиц базы данных).

2. Перейдите на закладку "Экспорт" в меню PMA.

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 'имя_базы'