There may be another way to do this out there, but when I needed it, I couldn’t find an easy free way to do it, so I made one.

You first need to create your new tables in your new MySQL DB and make sure you have¬†allow_url_fopen on in htaccess or in your PHP config. I use PDO so you may have to modify the code if you don’t.

Step 1: The CF file

I made one of these for each table I wanted to move. Map the jSON key to the new column names in your new DB.

<cfquery datasource="yourDSN" name="qName">
 select * from table order by id
 </cfquery>
 [
 <cfoutput query="qName">
 {
 "id":"#URLEncodedFormat(id)#",
 "col1":"#URLEncodedFormat(col1)#",
 "col2":"#URLEncodedFormat(col2)#"
 }
 <cfif currentrow neq recordcount>,</cfif>
 </cfoutput>
 ]

 Step 2: The PHP file

You can run this in your browser using https://yourserver/import.php?records=yourCFfilenameonyourremoteserver

function get_web_page($url)
{
	$options = array( 'http' => array(
			'user_agent' => 'spider', // who am i
			'max_redirects' => 30, // stop after 10 redirects
			'timeout' => 220, // timeout on response
		) );
	$context = stream_context_create( $options );
	$page = @file_get_contents( $url, false, $context );

	$result = array( );
	if ( $page != false )
		$result['content'] = $page;
	else if ( !isset( $http_response_header ) )
			return null; // Bad url, timeout

		// Save the header
		$result['header'] = $http_response_header;

	// Get the *last* HTTP status code
	$nLines = count( $http_response_header );
	for ( $i = $nLines-1; $i >= 0; $i-- )
	{
		$line = $http_response_header[$i];
		if ( strncasecmp( "HTTP", $line, 4 ) == 0 )
		{
			$response = explode( ' ', $line );
			$result['http_code'] = $response[1];
			break;
		}
	}

	return $result;
}

error_reporting(E_ALL);
ini_set("display_errors", 1);
ini_set('memory_limit', '128M');

require_once('db.php');

$records = $_GET['records'];

$inserts = get_web_page("https://pathtoyourserverwithcfandsql/".$records.".cfm");

print_r($inserts);

if ($inserts['http_code'] == 200)
{
	$inserts = $inserts['content'];
}

if (json_decode($inserts,true) == true)
{

	$insertsR = json_decode($inserts,true);

	foreach ($insertsR as $items)
	{
		$cols = array();
		$vals = array();

		foreach ($items as $key => $value)
		{
			$cols[] = $key;
			$vals[] = urldecode($value);
		}

		$colsI = implode(',',$cols);

		$questions = '';

		$valsC = count($vals);

		for ($i=1; $i <= $valsC; $i++)
		{
			$questions .= ($i < $valsC) ? '?,' : '?'; 		} 		print_r($cols); 		print_r($vals); 		echo "insert ignore into $records (".implode(',',$cols).") values ($questions)"; 		print_r($vals); 		$statement = $db->prepare("insert ignore into $records (".implode(',',$cols).") values ($questions)");
		$statement->execute($vals);

	}

}

else
{
	echo 'no bueno';
}