How to restore a MySQL 4.1 backup on MySQL 4.0

Introduction

I was recently approached with a question relating to restoring a MySQL database from a backup. The restore process was failing with a restore error. Being busy at the time I requested a copy of the backup file to look at when time was convenient. The backup was of a database related to a web hosting site that had been taken during various moves between hosts (not my hosts !)

The Problem

The different hosts were running at different MySQL releases !

The main problem was simply one of compatibility in the Data Definition Statements (SQL's CREATE TABLE and other similar statements). With the introduction of MySQL 4.1 there had been the concept of multiple character sets. MySQL 4.0 simply didn't understand the additional options on the CREATE TABLE portion of the backup file, then any resulting INSERTS were failing as the table had not been created.

The Solution

Fortunately MySQL performs it's backup by essentially producing a large SQL script that is a reverse engineered copy of the database. The backup is simply executed by the MySQL server and will step by step build the database tables and insert the data.

As the backup file is a simple SQL script file rather than a binary dump, then resolution of this particular problem was simple.

The particular issue was the addition of the CHARSET, this simply wasn't supported by the 4.0 server the database was being restored onto.

DROP TABLE IF EXISTS `table_name`;
CREATE TABLE `table_name` (
  `record_id` int(11) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`record_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

A quick search & replace of

ENGINE=MyISAM DEFAULT CHARSET=latin1;

with

ENGINE=MyISAM;

and bingo, the file loads

Conclusion

MySQL is complex database system, it is essential that you spend a bit of time getting to understand the basics of how the system operates. Most issues can be resolved with a simple understanding of what goes on underneath the GUI.

About the Author

Alex Monaghan is a Certified MySQL Professional and was the 2nd UK listed person on the MySQL Listing of Certified MySQL Professionals (there are a number of UK listings now). Alex runs an IT consultancy Monaghan Consultants Ltd and Web Hosting via DIY Hosting

This article is published at Monaghan Consultants MySQL Tips page and may not be republished without permission.