Martin, Shut down your 3.23 server. Make a binary copy of the data. Install 4.0 Upgrade your privileges with the 4.0 script. Start 4.0 server.
That's it, right? Worked for me... When doing backups I always do a full recursive backup of the entire mysql data directory. This way I know that I'm safe if something happens. master logs, relay logs and .info files. That should make me feel safe, right? I've never done a backup with mysqldump. Database is about 10 GB on disk so I figure it will be fairly large with a regular mysqldump. /Mike ----- Original Message ----- From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Martín Lahittette" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Saturday, October 18, 2003 11:59 PM Subject: Re: Backup database with foreign keys 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
