At 21:45 +0000 10/18/03, Martín Lahittette wrote:
>Yes, you are right. I am upgrading to MySQL 4.0,
>but I can not do it if I do not have a backup of
>my current database. If something goes wrong, I
>need to be sure that I am able to restore the
>database to its current state. Maybe I need two
>types of backup, one to move my databases to
>version 4.0 (it is solved with mysqldump and SET
>FOREIGN_KEY_CHECKS) and other to recover mu
>current database if something goes wrong when
>upgrading.
>Martín
It's going to be true that you need a backup whether you upgrade or not, right?
The alternative strategy is to know the foreign key dependencies, dump each
of the affected tables into a separate file, then reload each of the files
in the proper order.
>
>>>Thank you very much Paul, that is exactly what
>>>I need, but FOREIGN_KEY_CHECKS = 0 is a
>>>feature of MySQL/InnoDB-3.23.52 and
>>>unfortunatelly my current version is 3.23.51.
>>>I can not upgrade to version 4.0 if I do not
>>>have a reliable backup of my current version.
>>>Do you have any other sugestion?
>>
>>I don't understand. You say (below) that you are upgrading to *4.0*.
>>The SET FOREIGN_KEY_CHECKS statement isn't something you need when
>>you're dumping your database, it's something you use when reloading
>>the dump file.
>>
>>- Use mysqldump to dump your 3.23 databases
>>- Upgrade to 4.0
>>- Reload the dump file into 4.0, using SET_FOREIGN_KEY_CHECKS.
>>
>>>Martín
>>>
>>>>At 0:15 +0000 10/18/03, MartÌn Lahittette wrote:
>>>>>Hi,
>>>>>I want to backup a MySQL 3.23 database to
>>>>>upgrade it to MySQL 4.0. My tables are
>>>>>InnoDB and they have foreign keys. I would
>>>>>like to know how to backup it, because it
>>>>>seems that neither mysqldump nor
>>>>>mysqlhotcopy can be easily used.
>>>>>
>>>>>The restore script created by mysqldump
>>>>>contains the 'create table' (and data
>>>>>insert) instructions unsorted. So, when I
>>>>>run the script it fails because it many
>>>>>times tries to create tables referencing
>>>>>other tables that have not already been
>>>>>created.
>>>>
>>>>Before loading the dump file, turn off foreign key checking. Invoke
>>>>mysql, then issue these statements:
>>>>
>>>>mysql> SET FOREIGN_KEY_CHECKS = 0;
>>>>mysql> SOURCE your_dump_file;
>>>>
>>>>>
>>>>>On the other hand, the MySQL manual says
>>>>>(4.8.7 mysqlhotcopy) that mysqlhotcopy can
>>>>>only be used with MyISAM and ISAM tables.
>>>>>
>>>>>Thanks in advance
>>>>>Martín
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
