Mac to Windows Database issue

Question

I've been developing a Drupal site for over a year on my MacBook. The production server that the Drupal site will run on is Windows Server 2008 R2. As I try and load the SQL file from my MacBook, I'm getting this error from my MySQL client: "This file contains mixed linebreaks. They have been converted to Windows linebreaks." Then the import crashes. Can anyone share some tips with me on how to sucessfully migrate the Drupal database to Windows w/o carrying over assorted character/encoding problems?

Also-because the Drupal URL of the site on Windows is something other than "localhost", I end up having to edit the exported SQL file(s) either in TextMate (on Mac) or in Notepad++ (on Windows) to change the URLs. Could this also be contributing to the problem? I have also noticed in Sequel Pro that in the "field_data_body" table that there are hard returns everywhere. Is this normal?

Sorry if this is a dumb question, but I'm at my wits end and on a tight deadline. Thanks so much,

Jason

--Added on 2/9/14--

Here's some additional details related to my original question.

1. First, I realized that the Mac version of my database contains Windows line breaks (\r\n) and not OSX line breaks (\n). If my production MySQL server runs on Windows, should I even bother to try and convert the incorrect line breaks on my mac development site?
2. Importing using Backup & Migrate doesn't usually work from my Mac to my Windows server because the Windows server usually crashes. So I have to start the site off on Windows by importing the schema and data files separately. Schema imports fine, but the data file import encounters SQL errors from these issues: apostrophies in block of text (i.e., Drupal's, site's); brackets (i.e., 'Your membership request was approved for '[og_membership:group:label]' in an insert statement), anyplace where % or @ occurs.

I've tried putting a backslash before the offending characters in some tables, but don't want to do this for the whole data file. Is there something I'm doing wrong when exporting SQL files on my Mac that is causing these problems?

One thing I just remembered is that I had to uninstall and then reinstall MAMP on my Mac two weeks ago. Before this, I never encountered these type of import errors, on my Mac at least. I checked my MySQL config. file and the only difference is that I declared InnoDB as the default storage engine. I did not have this in my previous MAMP installation.

Thanks for any assistance.

Jason

Answer 1

First, I would suggest using the Backup and Migrate module to move Drupal databases between hosts. That may solve your line breaks issues, and YMMV, but loading Drupal dbs via other MySQL methods can cause other issues.

Second, have you tried setting the $base_url config variable on your production server? You shouldn't have to change URLs in the SQL files. It's a best practice to use relative paths in content. There are modules/filters that will convert them to absolute paths on the fly.

Cheers!