Mysql – Problems migrate database from 4 to 5.5 thesql

migrationMySQL

I have a server with 4.0 mysql version innodb engine.

I need to migrate to other server with 5.5 version (linux) ok

I dump the full database using mysqldump, when i try to restore on the 5.5 server it has a problem, because the file generate in the 4.0 set the variable on the sql scritp TYPE=INNODB and mysql 5.5 accepts only ENGINE=INNODB format on the time table creation and it abort the process.

I search on google, call any friends and nothing! the sql file has 16 gigabytes. Is not possible edit with an file editor.

error exemple: 

CREATE TABLE tabadi ( 
nAdicao char(3) default '0', 
nSeqAdic char(3) NOT NULL default '', 
cFabricante varchar(60) NOT NULL default '', 
vDescDi varchar(15) NOT NULL default '', 
xPed int(6) NOT NULL default '0', 
nItemPed int(6) NOT NULL default '0', 
nDI varchar(12) NOT NULL default '' 
) TYPE=InnoDB

ERROR 1064 (42000) at line 19: 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 'TYPE=InnoDB' at line 9

Ps: I did try to change TYPE TO ENGINE and its solved the problem.

Can anybody help me? Thanks a lot.

Best Answer

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.

Related Question