Akina

Как бы вы поступили в подобной ситуации.

Да как обычно при хранении timeline. Первым делом настраиваем права и запрещаем обновление и удаление. Записи можно только добавлять. Вторым - вводим автоматически присваиваемое поле штампа времени создания версии заказа. И, собственно, всё.

Для получения состояния заказа на любой момент времени (актуальное состояние или в прошлом) используем один несложный запрос. То же - если нужно получить разницу между текущим и предыдущим состояниями. Оконные функции делают это на счёт "раз".

И никакие логи не нужны.

Лучше делать всё в одном запросе:

UPDATE users u1
CROSS JOIN users u2
SET u1.amount = u1.amount - $summa,
    u2.amount = u2.amount + $summa
WHERE u1.id = $client
  AND u2.id = $shop;

Во-первых, один запрос - проще. Пусть даже сам запрос и сложнее.

Во-вторых, если, например, в структуре таблицы имеется ограничение CHECK (amount >= 0), а сумма такова, что у клиента баланс уйдёт в минус - в таком случае ни одна из записей не будет изменена.

Мне нужно загнать всё это дело в MySQL. Какие наиболее оптимальные способы для этого есть?

Оптимальный - положить этот файл туда, куда может дотянуться MySQL. Импортировать, используя LOAD DATA INFILE, во временную таблицу. Несложным запросом распарсить в рабочие таблицы (как я понимаю, хоть весь файл и невалиден как JSON, но каждая отдельная строка файла есть JSON валидный). И прибить временную таблицу.

На всё про всё три запроса. Если очень хочется, можно их выполнить и через php, конечно. Но я бы затолкал их в хранимую процедуру (особенно если задача импорта обновлённых данных будет регулярная) и вызывал её - тогда вообще один запрос CALL proc_name;.

А можно и в один запрос уложиться, если использовать LOAD DATA INFILE с препроцессингом. Тогда и валидность JSON на строку неважна =- лишь бы формат данных в строке не плавал от одной строки к другой.

А гонять 18 гектар с диска в PHP, а потом от PHP к MySQL - ну несерьёзно.

PS. Загрузка с использованием LOAD DATA INFILE весьма нетребовательна к объёму оперативной памяти. И неважно, какого размера исходный файл.

И есть ли возможность у Mysql подключаться напрямую к другой базе на другом сервере?

Да, есть. The FEDERATED Storage Engine.

Как это сделать на PHP?

На первом сервере выполнить SELECT .. INTO OUTFILE.
Переместить полученный файл в место, доступное со второго сервера.
На втором сервере выполнить LOAD DATA INFILE.