I would like my CartoDB data to be updated, using external data (mysql database, I run and manage locally using php scripts), so that the CartDB table gets updated with the most recent data, whenever the map gets loaded. Is that even possible?
Another solution could be the following: I have a service running that needs to show a map of some data, it's python instead of PHP but the solution is the same.
Very time the data is modified I write to a queue the data to be inserted in cartodb which is sent to cartodb every minute. I use django ORM which helps with signals but it could be done using database procedures for example.
To send the data to CartoDB I use SQL API (http://developers.cartodb.com/documentation/sql-api.html) which allows to send a bunch of inserts in a single query. You can read this blog post on how to do it fast:
The only method for tables sync that CartoDB supports right now is by HTTP urls as sync tables.
Another option is using PostgreSQL Foreign Data Wrappers. Actually a PostgreSQL FDW could live withing a CartoDB installation without any problem but you would need to install it in the server manually since CartoDB "doesn't know" about FDW. It would not be data sync but just remote queries.
It would be great developing a MySQL connector as part of the synchronizer module: https://github.com/CartoDB/cartodb/tree/master/services/synchronizer
I'm adding this for future questions related to this.
There are connectors for PostGres,MySql,... to interact with your tables. eg insert, create,..