How To Backup database in different version of MySQL

Move and backup database in a new host with different version of MySQL

When I run the backed up sql file with phpmyadmin in the new database on the new web host, I get the following error:

-- Table structure for table `ropix`
--
CREATE TABLE `ropix` (
`id` tinyint( 10 ) NOT NULL AUTO_INCREMENT ,
`mask` varchar( 255 ) NOT NULL default '',
`type` varchar( 255 ) NOT NULL default '',
`status` tinyint( 2 ) NOT NULL default '0',
PRIMARY KEY ( `id` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =1

MySQL said:
#1064 - 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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=1' at line 25

I guess it is a problem with different mysql versions on the two hosts?
moving from MySQL 4.0.1 to 4.1.1

The "ENGINE = MyISAM" needs to be change to "TYPE=MyISAM", and most of the charset info can be discarded.

Compare the structure/syntax of the backup to the database.mysql that comes with the base database install, and look for areas where the syntax differs.

I usually clean my db backups using a text editor wordpad (notepad running slowly to open big file sql). And, if a db fails to restore cleanly, just drop any tables that were created, re-edit the .sql file, and try again.

Posted in: