I store game data inside a MySQL database. The game data can be modified via web-based admin page written in PHP. Is there a nice way I could automatically track these changes inside an SQL file, so that I could track it using git and easily push the changes to development/staging/production server?

For example, when I create a level, an insert statement will be generated:

INSERT INTO `levels` ...;

Then, when I edit the level in the future, an update statement will be generated instead:

UPDATE `levels` SET ... WHERE `id` = ...;

These files are then checked into git. When the time comes, I will push these changes from the local repo to the dev repo. The dev server keeps track of the last sql file it has executed. Upon receiving the changes, the server will execute the new sql files sequentially.

Or maybe there's a better way? I just want to have my data inside git.

You're talking not about dynamic data, but static data. There's no known way to automatically track these changes, but personally I just script everything on disk, track it with Git, and then execute against the database server(s). You can even automatically execute the scripts against test and production servers at various stages if you trust your scripting enough.

Making your changes with a Web application isn't much different. You'll just have to generate the scripts from the Web application based on what capabilities you programmed into the Web site, invoke Git to track them, and invoke a MySQL client to execute them against the database. Simple enough to do. Just make sure you add sufficient error handling and wrap database operations in transactions that can be backed out if something goes wrong (perhaps also automate database backups if dynamic data matters).

I like to track "indexed" SQL scripts that if run in order will create a database and bring it up-to-date (structure and data). E.g.,

* ~/src/projectX/
`--* .git/
   `--* (...)
`--* db/
   `--* changelog/
      `--* 0000-CreateDatabase/
         `--* (...initial script(s) to create the database...)
      `--* 0001-SomeChange/
         `--* (...scripts to make a chance to the original structure and data...)
      `--* 0002-SingleScriptToInvokeSomeChange.sql
      `--* (...)
   `--* fn/
      `--* (...current scripts to [maybe] drop and create each SQL function...)
   `--* sp/
      `--* (...likewise for stored procedures...)
   `--* tbl/
      `--* (...likewise for tables, except no dropping for safety...)
   `--* tgr/
      `--* (...likewise for triggers...)
   `--* vw/
      `--* (...likewise for views...)

It's easy enough to write a program to automatically execute the db/changelog/ in order. If it also tracks its progress somewhere for each server then it should be possible to automatically continue with new scripts.

When my scripts mess with dynamic data I am obviously very careful to preserve it and usually those are run by hand instead of automatically, rolled back initially, and the results verified prior to committing them.

bambams@test-chamber-1:~/src/projectX (master -> origin/master)$ push-db -c master --commit --force testing
testing e3b552 => 11dfa2
backup written to /var/backup/projectX/db/testing/20140725111105121.bak
log file is /var/log/build/projectX/db/testing/push-20140725111105121.log
transaction started
0042-AddWaterLevels/0000-level92-water-castle.sql
0042-AddWaterLevels/0001-level93-water-maze.sql
0042-AddWaterLevels/0002-level94-water-tower.sql
transaction committed

Thank you for the feedbacks.

@bamccaig: Yep, this is for static data. We are actually already using a similar solution to yours for the schematic changes (though we use timestamp instead of index, but it probably doesn't matter anyway.) For the schematic changes, we just simply write the scripts by hand. But it's not possible for the data, because the people who will be making the changes aren't programmers, so scripting by hand is out of the window. That's why I thought of generating the sql, because at least they know how to do commit files into git.

@The Master: I was thinking of some sort of logging system that keep tracks of the changes to the data, but that seems like a lot of work to be done, and time is against me right now.

@Edgar: Not sure about that. I haven't explored the "advanced" features of MySQL yet (I only know tables ) I'll take a look on triggers.

Another idea I had is to just take the dump of the static data, track it in git, and from time to time, generate a dump from the development database, "diff" it (not sure how...) with the latest version in git, and then check in those changes.

You might do a bit of exploring here :
http://www.mysqltutorial.org/mysql-triggers.aspx

Basically it lets you do stuff whenever an insert delete or update occurs, thus allowing you to synchronize or possibly run tasks.

Triggers are the way to go, though for such an advanced use of SQL I'd normally recommend PostgreSQL since that supports the SQL standard and advanced uses of SQL like this better, and also has a procedural language that makes triggers even more powerful.

MySQL has binary logging which can be used for realtime replication and point-in-time restoration.

It also has plain text query logging.

Triggers sound like a bad choice here.