Posts Tagged Database
Recently, I was asked to work on a project using a MySQL backend after many years of not having worked with the product. I was amazed when I saw that some of the more common tools to work with MySQL, such as MySQL Workbench or Toad, had still not developed an easy way to move objects and/or data between mySQL databases such as we have become accustomed to in SQL Server Management Studio. Obviously, this greatly complicated my work and caused me to have to work with data in a production environment more often, which obviously I wasn’t comfortable with.
So like most developers, my brain began to think about how to solve this problem. You could of course, go through each object, get the CREATE script, put it in a giant file, and run it that way. That would be time consuming, bulky and difficult to maintain should there be changes to the database objects. And of course, this did nothing for the issue of data migration.
I looked at other products. MySQLDump, a free tool is fine. Of course, it was more complicated then I would have liked. I just want to move the objects and data and not have a high learning curve of a new product. Just get it done already. Others were available that you had to pay for. Being the cheap bastard that I am, that option didn’t appeal to me either.
So I wrote MySQLMove. This started out as a quick and dirty option to get objects and data moved to another database on another server and gradually moved to including things like a report, the option to only migrate objects, and actually put a user interface on top instead of just running a script. The script was fine for me but it may not be for other developers.
Let’s go into some things you need to know or generally be aware of.
First, some MySQL developers might wonder why I did not use INFILE to import data. The reason is for many such developers, they are coding against a shared hosting environment and often the administrators in such environment have disabled this option. Even using the LOCAL keyword presented problems relating to security. However, in future releases (but see below) I am planning on a interface modification that will allow the end user to indicate that INFILE is available for use and it will operate accordingly.
The engine now uses INFILE and LOCAL keywords. It is also now lightening fast!
Please make sure you have created the database on the destination server. I know that comes from the “duh” department but you would be surprised…. If you are asking yourself the reason why I don’t do it for you, it is because I drop each object on the destination individually as it is being imported. But more importantly, often each database has a specific username and password designated for access. An idea submitted for future versions (but see below) is that we collect all databases under a given username and password and allow multiple databases to be migrated at one time. That day is not here yet but it intrigues me.
Another “duh” is it is important to make sure the destination database doesn’t have any users writing changes.
Next…. it is important that if you are importing from or to a Linux based environment, that you are absolutely certain of the case of your database, its objects and server name. Windows is much more forgiving then Linux is in this regard. If you are importing from Linux, be prepared for windows to make all objects lower case, regardless of how you created them in Linux based servers. I have also seen some versions of MySQL run on Windows that were also sticklers for such. My best advice to you is to make all objects lower case. Both Linux and Windows based MySQL servers can handle such and if you are often moving between the two systems you will save yourself a lot of trouble.
Next, you will see an option in a checkbox to only import MySQL objects.
If you have the time and the data available to you in text or csv format, I suggest you check this. Why? While this engine can and will move data, it takes longer then a manual import would. The way it is built is all of your insert statements for the data are collected as one statement and inserted collectively. It was about 20 percent faster then inserting a row at a time and also if it should bomb, I can rollback the transaction so that you are not left with a table with partial data. That said, if you are one of those folks who build 300+ column tables, (by the way who hurt you as a child to do that to yourself? jk) it is going to take awhile. But rest assured, it will get there. This was tested against a table with 80000 rows and 30 columns. So it is pretty robust.
As mentioned above I now use INFILE and LOCAL keyword. The above text is no longer a concern.
Next, it is important you let the code finish. Check that, it is imperative. If you are impatient, switch to decaf and try some breathing exercises.
Finally, MySQLMove will attempt to export database objects as follows: tables, stored procedures, functions and triggers. Not every single one of all those objects can be migrated using this tool. One issue that has commonly arisen is when the code uses a PREPARE statement along with a string for use as the command. Future versions (but see below) will probably rectify this. Should something not be exportable to the destination, at the end of the process a report will appear showing what objects weren’t able to be moved. In my testing, it is a rare occurrence. Also with triggers, remember the “defined user” is copied straight from the trigger to the destination. You will need to make sure that user exists on the destination database as well. Oh and by the way, triggers are migrated last. Otherwise each insert might cause that trigger to fire. And yeah that would not be fun.
Finally, I would love to continue developing this tool. There are so many things I could and would love to do with it. Better reports, table optimization, code optimization, object selection etc. Unfortunately that costs me time, which in translation for those that live under a rock, this means it costs me money. If you find the tool useful and would like to continue to see it developed, please don’t be a cheap bastard like me and make a donation here using my email address – firstname.lastname@example.org .
I would be extremely grateful. I know the honor system is putting yourself out there isn’t real effective but it gives those of you who would like to say thank you and keep going with this a chance to do so.
So on to the download…. You have two options….
One if you are a person who already has the MySql.Data dll already installed on your system (make sure it is version 126.96.36.199 and you have at least .NET runtime v4.0.30319) you can download just the bin folder. If you don’t or aren’t sure, get the install package zip file called “MySqlMoveSetup”. If you need the .NET Framework you can download it here.
Both the bin folder zip file and the installer zip file are located here on my OneDrive.
Please do send me an email at email@example.com with any bug reports, suggestions or praise. All are accepted.
Have a great day!