Как правильно писать лог в базу данных (mySQL)

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

Лучший лог - это тектовые файлы. Даже если mysql станет колом, то вы не потеряете ни одной строки из лога.

Затем по крону (раз в несколько минут) перебрасывайте тхт в базу mysql, возможно при этой переброске делать обработку или какое-то агрегирование данных, чтобы уменьшить объем хранимого лога.

А как избежать потери данных в текстовом файле, при переносе. В базе данных-то можно использовать транзкции, хотя это жутко медленно и по-моему не верно для таких объемов данных. А вот как избежать потери данных в файловой системе?

Считал файл в память для дальнейшего переноса в базу. Обнулил его. Что если запись происходит между событиям "считал в память" и "обнулил"?

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

Использовать нормальные винты и универсальную кодировку.

Можно периодически архивировать и пересылать на др сервер/мыло.

Используйте 2 таблички. Первая - ваш лог. Вторая (назовем ее temp_log) - временный лог, куда пишите/дополняете записи в реальном времени. Потом по крону переносите/агрегируете из нее данные которые старше 2-3-10-20 секунд, зависит от ваших требований. Крон будет примерно таким: удалили старые записи из лога, достали N записей из temp_log, вставили группой в лог, удалили их из temp_log. Айдишники можете также копировать, чтобы они были сквозными у этих двух таблиц. Операции с temp_log будут быстрыми, т.к. там всегда будет мало данных, а операции с основным логом будут происходить синхронно по крону, поэтому блокировка там не страшна.

Также можно использовать nosql решение вместо mysql, к примеру mongo, она вроде как раcсчитана на большие объемы данных.

Идею с файлами не разделяю, зачем писать велосипед обработки файлов, если можно использовать готовые решения :)

Файлы - не готовые решения?

А как, интересно, посмотреть логи почему БД рухнула или тупо при не хватке памяти/потоков на запись 🍿

Для логов лучше использовать nosql решения.

В Вашем случае мы бы просто не удаляли строки. Пишется и пишется. Набрался миллион записей - пишите в новую таблицу. Пошел 11 миллион - почистили первую из таблиц вообще на фиг. 10 таблиц Вас не утянут, 100 кстати тоже (если захотите по 100к записей побить).

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

Для логов удобно использовать файл + привязку сервиса, например PaperTrail. Если хотите логировать что-то относительно кода (ошибки, пойманные исключения) - Rollbar.

Если файл-> БД то просто чистите файл _после_ успешной записи, и ничего не потеряется. Можно хранить в памяти (редис, мемкеш) и оттуда писать раз в N времени

Я вот сначала пытался придумать какое-то решение наподобие как говорит MrPacman, а потом мне стало больше нравится решение наподобие как говорит edogs.

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

Небольшой минус в том, что у меня на самом деле разные строки логов имеют разный "срок хранения", придется хранить больше. Но старые таблицы уже потом можно шерстить на предмет стирания ненужных строк отдельным процессом. Ну и id будут не сквозные, но это не особо проблема, т.к. под id можно понимать суффикс таблицы+id из таблицы.

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

Используя Kibana можно смотреть за логами в реалтайм:

Есть и более сложные решения, как Собираем и анализируем логи с помощью Lumberjack+Logstash+Elasticsearch+RabbitMQ

PS: "Видео-презенташка" Сбор и анализ логов и метрик распределенного приложения с помощью Elasticsearch/Logstash/Kibana, кому интересно