Контроль версий структуры базы данных MySQL

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

Существует несколько способов держать актуальной структуру таблиц во всех окружениях разработки. Например, можно писать вручную изменяющие SQL-запросы в отдельный файл, использовать схемы баз данных или программы для автоматизации сборки проектов, вроде Maven или Phing.

Если используется СУБД MySQL задачу можно решить с помощью проксирования запросов.

MySQL Proxy – программа, отслеживающая протокол общения между клиентом и сервером MySQL. С ее помощью можно анализировать и изменять все пересылаемые по нему данные. Спектр применения MySQL Proxy широк: от сбора статистики до балансировки нагрузки. Программа также поддерживает скрипты на языке Lua, предоставляя разработчику простой API – этой возможностью мы воспользуемся для решения задачи протоколирования изменений в структуре базы данных.

Для примера рассмотрим ситуацию, когда копии БД существует на компьютере разработчика, development и production сервере.

MySQL Proxy нужно запустить только на компьютере разработчика.

mysql-proxy --daemon --proxy-backend-addresses=localhost:3306 --proxy-lua-script /home/user//lua/sample.lua

Аргумент --proxy-backend-addresses указывает на адрес и порт локального MySQL сервера, а --daemon позволит запустить процесс в фоновом режиме.

По умолчанию MySQL Proxy использует порт 4040 для принятия входящих соединений, поэтому инструменты разработчика следует настроить на использование именно этого порта. Само веб-приложение, если оно не изменяет структуру базы данных, может продолжать использовать порт MySQL, минуя MySQL Proxy.

Непосредственно решением поставленной задачи займется скрипт Lua, приведенный ниже. Он будет отслеживать все запросы к базе данных и записывать интересующие нас в лог. В качестве примера возьмем отработку событий создания, изменения и удаления объектов базы данных.

local tokenizer = require("proxy.tokenizer")

--[[ 

Функция, которая будет вызвана MySQL proxy при поступлении запроса

Подробнее:

http://dev.mysql.com/doc/refman/5.1/en/mysql-proxy-scripting.html

--]]

function read_query(packet)
	
	-- Пакет должен быть запросом
	if packet:byte() ~= proxy.COM_QUERY then
		return
	end
	
	-- Получение текста SQL-запроса
	
	local query = packet:sub(2)
	
	--[[
	
	Вызов tokenize разберет строку на синтаксические составляющие. 
	Это позволит создавать гибкие конструкции условий для записи в лог. 
	
	Например можно протоколировать создание учетных записей пользователей
	в таблице users, но игнорировать запись других данных.
	
	Подробнее:
	
	http://forge.mysql.com/wiki/ProxyCookbook#Figure:_Finding_the_first_word
	
	--]]
	
	local token_name = tokenizer.tokenize(query)[1].token_name
	
	-- Если встречается конструкция, изменяющая структуру БД

	-- Список констант для команд SQL: 
	-- http://forge.mysql.com/wiki/ProxyCookbook#Appendix_B:_Tokenizer_Constants

	if token_name == 'TK_SQL_CREATE' or token_name == 'TK_SQL_ALTER' or token_name == 'TK_SQL_DROP' then
		-- Запишем ее в лог
		local log = assert(io.open('/home/user/projects/test/log.sql','a'))
		log:write(query .. 'n')
		log:flush()
		log:close()
	end
	
end

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

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

Автор: bashtannik