Compatibility between mySQL 4 and 5

In general, I use mysqldump to export databases, and mysql to import them.

MySQL 4 to 5

No problems here.

1. Export the database on the system with MySQL 4:

mysqldump -p --opt dbname >dbname.sql

 

2. Copy the database to the other sytem, for example,

scp dbname.sql me@domain.com:/home/me

 

3. To import the database on the system with MySQL 5:

mysqladmin -p drop dbname

mysqladmin -p create dbname

mysql -p -D dbname <dbname.sql

 

MySQL 5 to 4

The issue here is 4 cannot directly import files dumped by 5. You must use the --compatible=mysql40 flag. However, that does not export the "autoincrement" option on tables that are autoincremented, so re-importing the data will fail.

I've assembled a script from various googled sources (unfortunately I didnt add those links in my comments). I called it "mysql-aifix", and it takes the dbname and creates a file called "dbname-fix.sql". After importing the database, also import this fix sql to ensure your autoincrement tables are autoincrementing.

1. Export the database on the system with MySQL 5:

mysqldump -p --opt --compatible=mysql40 dbname >dbname.sql

mysql-aifix dbname

 

2. Copy the database to the other sytem, for example,

scp dbname.sql me@domain.com:/home/me

scp dbname-fix.sql me@domain.com:/home/me

 

3. To import the database on the system with MySQL 4:

mysqladmin -p drop dbname

mysqladmin -p create dbname

mysql -p -D dbname <dbname.sql

mysql -p -D dbname <dbname-fix.sql

 

The msql-aifix Script

Paste this code into a file named mysql-aifix, and chmod +x it.

 

#!/usr/bin/php
<?php
$hostname = "localhost";
$username = "root";
$password = "secret";
if ($argc < 2) {
echo "Usage: mysql-aifix [dbname] \n";
echo "Adds autoincrement to mysqldump for database dbname \n";
echo "Outputs file dbname-fix.sql \n";
exit;
}

$dbName = $argv[1];
mysql_connect($hostname,$username,$password) or die("Can't create connection");
$res1 = mysql_query("SHOW TABLES FROM $dbName");
if (empty($res1)) die("Can't find database '" . $dbName . "'");
$i=0;
$fname = $dbName . "-fix.sql";
$fp = fopen ($fname, "w");
while($row1 = mysql_fetch_array($res1)){
$res2 = mysql_db_query($dbName, "SHOW CREATE TABLE $row1[0]");
while($row2 = mysql_fetch_array($res2)){
preg_match("/\s*(\W\w+\W)(.*auto_increment)/", $row2[1], $matches);
if($matches){
$i++;
$str = "ALTER TABLE ".$row2[0]." CHANGE ".$matches[1]."
".$matches[1]."".$matches[2].";\n";
fwrite($fp, $str);
}
}
mysql_free_result($res2);
}
fclose($fp);
mysql_free_result($res1);
mysql_close();
echo "Entries created: ".$i . " in file " . $fname;
?>