Migrate Table data from MSSQL to MySQL with MssqlMysqlMigrator
This gem puts puts together my code to migrate the data from a MSSQL to a MySQL server. Its was done to move an old rails project from windows to linux.
It is NOT using Rails Active Record. This is because i want to move the data and avoid all overhead od interpreting or validating them. I consider this part of the actuall business logic. Also this frees this gem from all dependency on the actuall rails app, removing of course also the dependency on rails or any particular ruby/rails version combination.
Importing data to mysql is done via CVS file import. Again, this is for efficency reasons. Letting MySQL itself load the data from disk in bulk mode give you access to the most efficient code path there is. You simply can't load SQL data toa database faster and better than the database itself.
Most simple usage to transfer all talbles would be:
$ mssql-mysql-migrator transfer source_db destination_db
The database account are found in 'database.yml' config file. See database.yml.template (like rails, but it goes into Sequel, not ActiveRecord). Overload default config lookup with --database_config=your-config-file.yml.
Documentation for now is limited, please read the source and exploit the code for whatever usage you might have in mind. Over time, when actually demanded, documentation, and error handling for this gem might improve.
A Full blown example like my actuall data transfer would look like:
$ mssql-mysql-migrator
--loglevel=info # defines verbosity(error, warn, info, debug)
--debug # shows callstack in case of error
--outdir=$(pwd)/tmp # where to put the CSV files
--exclude-tables=schema_info,schema_migrations # not transfered
transfer ac-express-live test # source and target DB
More usage examples
checking the character encoding
Character encoding, when you have to think about it, tends to easily turn into a nightmare. A triple error in an encoding chain might make it all look nice and right, when it is not. The encoding is also a parameter in the database condig file but to help you tinkering with various setting there is the encoding test where you can check multiple encoding option from the command line. See my example:
$ mssql-mysql-migrator encoding-test database:table_name:column 'id = 31' iso-8859-1,cp1252,latin1
With this command you read exactly one value from a database in all the encoding you list (comma seperated, no blanks) at the end. Easy way check what you get from you db connection depending on the encoding setting.
list tables
yeah, easy:
$ mssql-mysql-migrator list-tables ac-express-live
Will help you to specify which tables you actually have and need to copy.
Installation
Add this line to your application's Gemfile:
gem 'mssql_mysql_migrator'
And then execute:
$ bundle
Or install it yourself as:
$ gem install mssql_mysql_migrator
Development
After checking out the repo, run bin/setup
to install dependencies. Then, run rake rspec
to run the tests. You can also run bin/console
for an interactive prompt that will allow you to experiment.
To install this gem onto your local machine, run bundle exec rake install
. To release a new version, update the version number in version.rb
, and then run bundle exec rake release
, which will create a git tag for the version, push git commits and tags, and push the .gem
file to rubygems.org.
Contributing
Bug reports and pull requests are welcome on GitHub at https://github.com/[USERNAME]/mssql_mysql_migrator. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.
License
The gem is available as open source under the terms of the MIT License.