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;
?>
Comments
>>
Remember to edit the file and set the correct password (optionally hostname and username) near the beginning of mysql-aifix. And thanks for this!
>>
Just thought I would let anyone else looking here know that as of MySQL 5.0.19 the mysql-aifix script is no longer required. :)
>>
this is what i was looking for.
>>
THANK YOU!!!! this is perfect! you are a godsend.
>>
Te felicito, estuve buscando algo asi durante muchas horas hasta que al fin lo encontre, necesitaba pasar una base mysql5 a mysql4 y no lo lograba hacer correctamente.
>>
David's message, through Google Translate:
I congratulate you, I was looking for something for so many hours until finally I found it, I needed to spend a mysql5 to mysql4 and not managed properly.
>>
Thanks a lot, as David, I've spent a lot of time trying to solve this issue until discover your script. Again, THANKS A LOT! ... y felices fiestas!
Re: Compatibility between mySQL 4 and 5
really... thank you! I find it really usefull.
Re: Compatibility between mySQL 4 and 5
If mysql-aifix script is no longer required, how can we get the auto-increment to dump with mysqldump?
>>
Hello,
I found this useful after the import was done. Update your Permissions so that the same access is allowed
GRANT ALL ON DBNAME.* TO 'DBUSERNAME'@'%' IDENTIFIED BY 'DBPASSWORD';
replace: dbname, dbusername, dbpassword.
New Comment