Overview
In the recent RC version of MSDeploy, we shipped the dbMySql provider. The dbMySql povider can be used to sync a MySql script to a database. It cannot be used for initial deployment of a MySql database to an empty database or dumping a MySql database to a script file like the dbFullSql provider. In the RC version, we have the capability to add custom providers that can perform certain specific tasks for you. I wrote a custom provider (dbFullMySql) with help from the MSDeploy dev team, that extends MSDeploy functionality and can approximately mimic the basic functionality of the MSDeploy SQL Database provider functionality, i.e.
- It can be used for initial deployment of a MySql database to a specified destination database.
- It can also be used sync a MySql database to an editable .sql file.
- When called with a sql source and a database connection string destination, it will internally call MSDeploy’s builtin dbMySql provider, hence it acts as a wrapper for dbMySql.
Download
You can download the sample source code, the visual studio project files and binary (CustomProvider.dll) for the dbFullMySql provider from here. This sample also supports custom provider settings and you can use this as an example to get started with writing your own msdeploy custom provider.
Installation
You can get it working with the RC installation of MSDeploy by following a couple of simple steps.
- GAC CustomProvider.dll using “gacutil.exe /if CustomProvider.dll”
- Run the following command from a command-line.
| reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\IIS Extensions\MSDeploy\1\Providers\dbFullMySql" /v type /t REG_SZ /d "CustomProvider.MySqlScriptProviderFactory, CustomProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=755393eb585dfba1" |
Now,if you run “mseploy.exe” from the command-line, you should see dbFullMySql in the list of available providers.
Working
Functionally, all dbFullMySql provider does to sync two databases is, internally uses mysqldump.exe to generate a script file from the source database connection string. It then passes this script file and the destination connection string to MSDeploy’s dbMySql provider to handle the rest of the sync. In case you specify a destination .sql file, instead of a connection string, dbFullMySql will just stop with the first part of the sync and dump the output of mysqldump.exe into the script file that you specified.
In the code, the default location of mysqldump.exe is assumed to be “%programfiles%\MySQL\MySQL Server 5.1\bin\mysqldump.exe”. In case you would like to specify a different location, you can use the mysqlDumpExecutablePath provider setting as shown in the example below.
Usage
You can use the dbFullMySql custom provider in the following ways
- Sync a source mysql database to a non-existent destination database
Example:
| msdeploy.exe -verb:sync -source:dbFullMySql="server=iis-msd124;database=gallery2;uid=root;pwd=iis" -dest:dbFullMySql="server=iis-msd125;database=gallery3;uid=root;pwd=iis" |
- Sync a source mysql database to a .sql file
Example:
| msdeploy.exe -verb:sync -source:dbFullMySql="server=iis-msd124;database=gallery2;uid=root;pwd=iis" -dest:dbFullMySql=c:\sql.sql |
- Sync a .sql file to a destination mysql database
Example:
| msdeploy.exe -verb:sync -source:dbFullMySql=c:\sql.sql
–dest:dbFullMySql="server=iis msd124;database=gallery3;uid=root;pwd=iis" |
- To specify a location for the mysqldump.exe, you can use the following command.
Example:
| msdeploy.exe -verb:sync
-source:dbFullMySql="server=iis-msd124;database=gallery2;uid=root;pwd=iis",mysqlDumpExecutablePath=c:\mysqldump.exe
-dest:dbFullMySql="server=iis-msd125;database=gallery3;uid=root;pwd=iis" |
Limitation
Currently, we can sync the dbFullMySql provider (or any custom provider) to an archive or a package since we allow overriding GetStream() method. However, you cannot sync back from an archive or a package back to your custom provider because GetStream() is not public in this release. If you still want to see how to implement syncing to an archive/package, you can check the CustomProvider.cs sample code and use the following example to see it working
| msdeploy.exe -verb:sync -source:dbFullMySql="Server=localhost;Database=gallery2;uid=root;pwd=iis"
-dest:archivedir=c:\archive |
| msdeploy.exe -verb:sync -source:dbFullMySql="Server=localhost;Database=gallery2;uid=root;pwd=iis"
-dest:package=c:\package.zip |
Comments