It’s a convoluted situation to describe. Mysql has a great graphical entity relationship diagram modeller called workbench. One problem that it has always had is that the designers assumed that you had control of the schema names on your physical databases. In a variety of hosting situations schema names are unique and determined by the environment so maintaining the database form a constantly modifying model was always a very manual process.
I have written a script map-schema for mysql-proxy so that the proxy can sit between workbench and a physical database (Or set of physical databases) and map the schma names backwords and forwards. It isn’t actually necessary to do the mapping for all situations and it isn’t necessary for the workbench to see the correct schema name back in all queries for it to be able to create and execute valid alter statements on the physical database in order to bring it into sync. Alarmingly the workbench has a passion for deleting the foreign schema from the physical database so the proxy ignores all drop schema statements.
In the simplest configuration a mysql-proxy is run on the mysql server machine, listening on port 4040 for mysql workbench. The mysql workbench uses a connection profile with a default database schema of the physical database it normally connects to. The port number in the connection profile is modified to 4040. The proxy is started and the databse synchronization is performed.
I have put the script itself on GitHub so you can install it from there. For a UNIX environment:
cd mkdir local cd local git clone email@example.com:obrun/map-schema.git
UPDATE THE START FILE
The schema name known to the workbench model is placed in the configuration of the proxy by editing the ‘start’ script
Replace my_model_schema in the line
with the name you gave to your model schema.
If you don’t know your model’s name for the physical schema open your model in workbench. Go to the tab labelled ‘MySQL Model’ and look at the grey word under the Physical Schemata tab on the right of the page. This is the name of the physical schema known to your model. By default it is ‘mydb’.
mysql-proxy needs to be installed either as root or in a user directory if that is what you need to do in your environment. If you are able to install the package as root or have an administrator install it then follow the instructions from Oracle. Then come back and install the script as above.