Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am having trouble getting mysql backups to run properly when their are views in the database. I think this might have something to do with needing a placeholder object for it. In any event I run this command:

mysqldump -u myuser -pmypassword mydatabase | mysql -u myuser -pmypassword -C mydatabase_Beta

The user has full privileges and I get this:

View mydatabase_beta.yadayada references invalid tables or columns or functions or definer/invoker or view lack rights to use them.

How can I back it up so that it restores all of my database properly? In the example I am restoring it to a different name but I do need to be able to restore a working copy. I think it is probably an additional mysqldump parameter or maybe hot copy would work better. Thoughts?

share|improve this question
    
Does the config you reference cause mysql to load the data to a separate instance? Does it help to save the dumpfile and then attempt to pipe it to mysql as a second step? Is there any difference in versions or supported storage engines between the two instances if there are two? –  Walter Mundt Dec 18 '09 at 21:37
    
I removed the -C, I do not need it, same issue though. Restoring to the exact same host/instance –  Codezy Dec 19 '09 at 6:27
    
Are you getting the error on the dump or the import? –  jgoldschrafe Dec 16 '10 at 3:31
add comment

3 Answers

Try doing it in two steps first:

mysqldump -uroot -ppassword --skip-extended-insert database.sql > database.sql

So it backs up to a file and then pipe that into the second command:

mysql -u myuser -pmypassword -C mydatabase_Beta < database.sql

If the reference error is in the mysqldump step either fix the views or drop it from the database. I can't imaging they're still used if they reference a non-existing table.

share|improve this answer
add comment

Shot in the dark here, but I don't think you need the "-C" in the second half of that statement. According to my copy of mysql "-C" defines the config file path to use.

Mark

share|improve this answer
    
Removed -C as I did not need it, still has issue with creating one of my views. –  Codezy Dec 19 '09 at 6:28
add comment

I think the answer might be to add -f:

--force, -f

Continue even if an SQL error occurs during a table dump.

One use for this option is to cause mysqldump to continue executing even when it encounters a view that has become invalid because the definition refers to a table that has been dropped. Without --force, mysqldump exits with an error message. With --force, mysqldump prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.