Поймал ошибку Mysql: The table is full

По дефолту при создании таблицы в mysql (до 5-й вроде бы версии) существует ограничение на количество записей, размер таблицы. Таблица MyISAM. Достигли размера больше 4Гб и поймали ошибку: The table is full
Сервак на FreeBSD, по идее ограничений на размер файла в 4Гб для ffs нет.

Смотрим.

mysql> show table status like 'new_posts';

Обращаем внимание на Max_data_length он у нас 4294967295.

Вносим изменения в свойства таблицы:

mysql> alter table new_posts max_rows = 1000000000 avg_row_length = 1038;
Query OK, 4131562 rows affected (1 hour 20 min 36.41 sec)
Records: 4131562  Duplicates: 0  Warnings: 0

Запрос займет некоторое продолжительное время.

Проверяем опять.

mysql> show table status like 'new_posts';

Получаем Max_data_length равным 1099511627775. По идее ошибка должна уйти.

По дефолту mysql ведет бинарный лог – все транзкции пишутся в файл, и в далнейшем мы имеем возможность восстановить состояние базы на определенный момент. Есть при этом неприятный момент – бинарный лог бстро занимает свободное прострнство на диске. При этом удалять руками файлы типа mysqld-bin.00000Х не стоит – mysql следит за состоянием бинарного лога.

Итак если лог заниимает слишком много места добавляем в /etc/my.cnf строку в секцию [mysqld]:

expire_logs_days=60

Псл этого перезапускаем сервер. Все логи старше 60 дней сервер потрёт.

Проверяем состояние переменной:

mysql> show variables like "%exp%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| expire_logs_days         | 60    |
| ft_query_expansion_limit | 20    |
+--------------------------+-------+

Если логи не потрелиcь (или если не хотим перезапускать сервис) – делаем:

mysql> flush logs;

Процесс резервного копирования разделяют на два типа – условно скажем – на физический и логический. Физический применим в таблицам типа myisam – когда мы можем просто заблокировать таблицы и скопировать файлы (MYD, MYI, frm). Этот тип выполняется к примеру скриптом mysqlhotcopy.

/usr/home/mysqlhotcopy.sh -u user -p pass --allowold --keepold --quiet database /path_to_back

Для «логического» бэкапа (бэкапа который генерирует логическую труктуру таблиц и запросы к базе, способные воссоздать таблицы) используется mysqldump.

mysqldump -uUSER -pPASS --databases base_name > /path_to_back/database.sql

Собственно для второго типа бэкапа есть скрипт autobackupmysql.sh – клон скрипта automysqlbackup.sh. Есть в портах, требует bash.

Поскольку в сети информации описывающей этот скрипт не нашел – пишем для себя.

Продолжить чтение →

Грохнул по нечаяности базу (говорили мне не используй phpmyadmin делай все руками) :)
Надо восстанавливать. В бэкапе – неверно настроенная кодировка – следовательно в таблицах – знаки вопроса вместо букв. Хорошо что бинарный лог не отключен, и имеется полный набор логов.
Продолжить чтение →

На заметку себе по вопросам оптимизации mysql. Актуально для myisam, mysql 4.0 (думаю актуально и для 5 ветки).
Общий принцип – смотрим счетчики в запросах типа

mysql> show status like "%table%";

И значение переменных сервера:

mysql> show variables like "%table%";

Сравниваем, думаем, читаем что за что отвечает.

После того как сделали выводы – идем в /etc/my.cnf правим переменные и перезапускаем сервер. Снова смотрим на вывод show status и делаем выводы. :)
Продолжить чтение →

Проверить включено ли кэширование запросов у вас на сервере можно запросом:

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

Продолжить чтение →

Иногда необходимо перенести только структуру базы данных mysql без данных.
Делается это просто (команда в одну строку):

#mysqldump -u use -p --databases base_name --add-drop-table 
--force --no-data > /home/userdir/base.sql

Ключевая директива mysqldump: --no-data
Получаем файл с описанием таблиц базы без данных.

ВНИМАНИЕ! Когда вы создали дамп базы и хотите на его основе сделать новую – проверьте нет ли упоминания имени старой базы в дампе (оператор drop database) – при попытке залить структуру в новую базу на той же площадке – потрете старую базу!

Если переносите базу простым копированием файлов базы Mysql из папки, к примеру /var/bd/mysq/basa и не перезапустили mysqld – есть вероятность получить такую ошибку.

Got error 127 from storage engine

Перезапускаем mysqld – всё должно работать.

Кстати после копирования обязательно проверяем права на папки и файлы – можно получить ошибку типа:

Can't find file: table.frm

Владельцем файла должен быть юзер под которым работает mysqld – например mysql.

Если при импорте дампа базы Mysql получаем в таблице мусор в виде знаков вопроса – проверяем в my.cnf наличие директив skip-character-set-client-handshake и настройки кодировки сервера в my.cnf.

Потому что может быть так:
Делаем дамп на одной машине, смотрим дамп – в дампе все нормально и русский текст читается (или не читается если смотрим в mc к примеру, но ясно что в дампе все в порядке – набор символов, но не знаки вопроса).
Переносим дамп, создаем базу импортируем наш перенесенный дамп. Делаем селект из любой таблицы и видим в качестве символов – знаки вопроса.

Дело в том что по дефолту у вашего сервера может быть не настроена кодировка по умолчанию и вы получите что-то вроде latin1_swedish_ci в таблицах.

Настраиваем my.cnf:

В раздел [mysqld] добавляем:

default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
init-connect="SET NAMES utf8"
skip-character-set-client-handshake

Теперь если к примеру скрипт установки модуля CMS выполняет запрос добавления таблицы в базу и не указывает явно кодировку (а это часто происходит поскольку модуль должен работать с локальной кодировкой) – то мы получаем таблицы с collation latin1_swedish_ci вместо необходимого нам utf8_general_ci и как следствие – знаки вопросов.

Вы уверены что ваш дамп базы который вы складываете в папку для бэкапов пишется в нужной для вас кодировке? Я тоже был уверен но один раз дамп оказался не в той кодировке в которой было нужно (просто не проверял). :) Вместо кирилицы – знаки вопросов. Продолжить чтение →