Mysql – How to backup a database from MySQL 32-bit and restore it to MySQL 64-bit

MySQLwindows

I'm currently using MySQL version 5.0.16, on my local computer (which runs a 32-bit OS), for easier access while developing my program.

Now that I'm ready to deploy my program, I'd like to test the connection over a network, and use a 64-bit server to hold the database.

Up til now, I've only used the 32-bit version, and my backup 'process' would be to copy the database folder from C:\Program Files\MySQL\MySQL Server 5.0\data and paste it onto the same directory of another computer.

I'm not sure if that would work.

I tried using both MySQL Query Browser and Command Line Client to do the following:

mysqldump -u root -proot Isys-TMS(EEI1) > isystms.sql;

But both simply return this error:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysqldump -u root -proot Isys-TMS(EEI1) > isystms.sql' at line 1"

Which is not at all helpful. (Though I'm not sure if I did it right on the command line client, as opposed to Windows' own CMD)

I'm also assuming I can simply use this .sql output on the new 64-bit MySQL, so if that's not the case, what else can I do to solve this problem?

Thanks.

Notes:

I'm using Windows 7 32-bit Pro, with MySQL Server 5.0 installed in it.
I'm planning to move the database to a Windows Server 2008 64-bit, with MySQL Community Server 5.6

EDIT: I actually used mysqldump, not mysqldumb. Quick, someone make a dumb/dump joke.

Best Answer

This is no SQL code:

"mysqldump -u root -proot Isys-TMS(EEI1) > isystms.sql;"

It is a commandline program like mysql.exe. You seem to run this in Query Browser or mysql cmd - this is wrong!

You can create a backup in Query Browser with the tool itself: http://www.siteground.com/tutorials/php-mysql/mysql_gui_tools.htm

or by running the mysqldump commandline from C:\Program Files (x86)\MySQL\MySQL Server 4.1\bin as:

mysqldump -u root -proot Isys-TMS(EEI1) -r isystms.sql

Please read this too (avoid \n / \r\n conversion): http://dev.mysql.com/doc/refman/5.1/de/mysqldump.html

Restore: http://www.siteground.com/tutorials/php-mysql/mysql_gui_tools.htm#restore

Related Solutions

Mysql – Change root password of thesql database

Put the path in double quotes "....

Mysql – Problems migrate database from 4 to 5.5 thesql

This should be as simple as piping the output of mysqldump through a utility that can manipulate the data stream with a regular expression pattern match, like sed or perl.

mysqldump [options] | perl -pe 's/^\)\sTYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

For any line that begins with ) TYPE=InnoDB this will replace the starting portion of the line with the new string ) ENGINE=InnoDB and leave the rest of the line intact. It won't do anything to lines that don't match (they'll pass right through).

For quick and simple validation that this does what you want, use the --no-data option in mysqldump to dump only the table structures, not the data, and examine the resulting file. If it looks right, you can repeat the process without --no-data.

A similar expression in sed should be this:

mysqldump [options] | sed 's/^) TYPE=InnoDB/) ENGINE=InnoDB/' > dumpfile.sql

...however, I'm not as conversant in sed as I am in perl, so I'd recommend the first approach, although I tested both versions and they appear to work identically.

Both perl and sed should be already present on any *nix system, and both are freely downloadable for Windows. On Windows, you'll probably need " rather than ' surrounding the expressions.

Best Answer

Related Solutions

Mysql – Change root password of thesql database

Mysql – Problems migrate database from 4 to 5.5 thesql

Related Question