I’ve spent several hours this past weekend working on a project for a client that uses a MySQL database backend. It involves converting thousands of records from XML files exported from the legacy data system. One of the things that’s been bugging me is the process that I have to go to every time I make changes to the the database or import more data into my local development copy in order to get those changes/data up to the staging server.
I am using a tool called EMS SQL Manager to manage the MySQL databases and the only way I have found to export data is to dump the entire database then open the .sql file in a text editor (but DEFINITELY not Notepad as the file is 500+ MB) and strip out the tables/data that I don’t want to update.
I spent some time in Google searching for a better way and found a blog post from March 2006 by Bojan Mihelac about how to use Ant and the command line tool mysqldump to automate the process of backing up and restoring databases. His post set me to thinking about how I could modify his posted code to be able to selectively export my tables using an Ant xml file from within Eclipse.
Here is the code I have so far (with some attributes removed for obvious reasons):
<project name="ProjectName" default="ExportUser"> <property name="backup_directory" value="some directory"/> <property name="local_database_host" value="localhost"/> <property name="local_database_username" value="username"/> <property name="local_database_password" value="password"/> <property name="local_database_name" value="dbName"/> <target name="ExportUser"> <exec dir="" executable="c:mysqlinmysqldump.exe"> <arg line="--host=${local_database_host}" /> <arg line="--user=${local_database_username}" /> <arg line="--password=${local_database_password}" /> <arg line="--add-drop-table" /> <arg line="--compact" /> <arg line="--result-file=${backup_directory}/tableName.sql" /> <arg line="${local_database_name}" /> <arg line="tableName" /> </exec> </target></project>Now, I don’t have this working quite right yet, but experimenting with this did give me an idea to make a batch file with a MySQL command line entry for each table in the database and that was enough to get me through the weekend.
Anyone out there have any thoughts or other examples of integrating Eclipse/Ant/MySQL?