MSDeploy Sample Custom Provider to sync MySql databases(dbFullMySql).

Overview

In the 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.

1. It can be used for initial deployment of a MySql database to a specified destination database.
2. It can also be used sync a MySql database to an editable .sql file.
3. 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. 

In the RTW version of MSDeploy, the dbMySql provider has complete functionality on par with dbFullSql. You can use the custom provider in this blog as a starting point to write your very own custom providers. For production and enterpirse deployment, it is recommended that you use dbMySql provider that ships with Web Deploy RTW.

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.

You can download the RTW version of  the Microsoft Web Deployment tool from here

Installation

You can get it working with the RTW installation of MSDeploy by following a couple of simple steps.

1. Create a folder called Extensibility under “%programfiles%\IIS\Microsoft Web Deploy”. This folder will contain all your MSDeploy custom providers
2. Drop the dll into the newly created folder

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"

• Sync a local database to a remote destination

          Example:

msdeploy.exe -verb:sync -source:dbFullMySql="server=localhost;database=gallery2;uid=root;pwd=iis" -dest:dbFullMySql="server=localhost;database=gallery3;uid=root;pwd=iis",computername=iis-msd126,username=administrator, password=iis

• Sync a local database to a .sql file on a remote machine, etc..

          Example:

msdeploy.exe -verb:sync -source:dbFullMySql="server=localhost;database=g2;uid=root;pwd=iis" -dest:dbFullMySql=c:\sql.sql,computername=iismsd126,username=administrator,password=iis