3

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?

7ochem
  • 280
  • 1
  • 3
  • 12
Codezy
  • 103
  • 1
  • 7
  • 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 06:27
  • Are you getting the error on the dump or the import? – jgoldschrafe Dec 16 '10 at 03:31

3 Answers3

2

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.

Scott Keck-Warren
  • 1,670
  • 1
  • 14
  • 23
1

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.

7ochem
  • 280
  • 1
  • 3
  • 12
Codezy
  • 103
  • 1
  • 7
0

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.

7ochem
  • 280
  • 1
  • 3
  • 12
mfarver
  • 2,576
  • 13
  • 16