Restore Database

RAH104 · 07-05-2008, 07:36 PM

Hello,

I am currently attempting to restore a very large MySQL database (1.4GB).

[root@server]# mysql --password=xxx --user=root vb < backup.sql
ERROR 1060 (42S21) at line 5116: Duplicate column name 'quoteid'

Is there anyway of forcing the database to restore? Basically it takes like an hour to get to this error where it halts and doesn't restore the rest of the database.

Thanks.

Matir · 07-05-2008, 08:26 PM

No, as you can't have two columns with the same name in the same table. I would look at the file and see why two columns of the same name are being created. How was the backup created? Exact same version of MySQL?

RAH104 · 07-05-2008, 08:40 PM

Both servers are running MySQL 5. Backup was made with mysqldump. What is the best way of correcting this? Editing the file in nano I presume would take forever for it to load.

trickykid · 07-05-2008, 09:49 PM

Quote:

Originally Posted by RAH104

Both servers are running MySQL 5. Backup was made with mysqldump. What is the best way of correcting this? Editing the file in nano I presume would take forever for it to load.

Yeah, loading a 1.4GB file then trying to edit and save is not something you want to do, not even with vi or any editor.

The way I would approach this is to either edit the file by using sed, might take just as long but could work, I've done this with mysql dumps over 10GB once, knowing the line number helps quicken the process. Or you could try using the command split to split up the file into smaller files. Find the line that has the duplicate column name, edit or remove, finish loading to restore.