EntropySink
Technical & Scientific => Programming => Topic started by: ober on August 05, 2013, 10:22:17 PM
-
So for most of my clients I run a production database and a dev database so they can do all their testing on a dev site without fucking up live stuff. Normally it's totally fine if they're out of sync. But now I have one where there are many cron jobs changing and updating data on a daily basis and dev data doesn't really matter, so I'd like to just do a daily copy of the production database. I tried using:
mysqldump -h localhost -u xxxxxxxxx -pxxxxxxx db1 | mysql -h localhost -u xxxxxxxxxxxx -pxxxxxxxxxxx db2
And that works UNTIL I hit views. As soon as it hits a view (and I have a lot of them), it dies and stops copying. I need the views for the rest of the code to function, so it's not like I can just ignore them like I do in my backup mysqldump.
It seems like mysqldump has issues in general with views. I just want to copy the entire DB. Why is this so damn hard??
Anyone have any ideas here? Is there a better way to do this? I've been googling for like 30 min with no luck on my attempts.
-
Perhaps try dumping the schema and data separately? And do the data one without views.
-
Also curious why you use so many views. In all of my development I've stayed far away from views in the DB layer, handling that sort of thing in the application layer instead. And triggers for that matter too.
-
What dump parameters are you using? At the old job we had at least one view and I don't recall any problems restoring from it. But yeah, why so many views?
-
Can't you just re-run the CREATE VIEW commands after the base tables are copied?
-
I just did a local test and had no problems dumping views. They're all commented out in the SQL but they import just fine.
-
Was it a conditional comment? I swear half of a MySQL dump is conditional comments.
-
Yup. I really don't understand that but I haven't bothered to become a MySQL admin really either.
-
There are so many views partially because I find it easier to setup a view and then create an object with my DB abstraction library than to create custom JOIN queries all over the code. If it were a larger operation with more than me as the developer I may not do it this way, but it's a convenience.
Can't you just re-run the CREATE VIEW commands after the base tables are copied?
I suppose I could. But I was trying to do it with a quick and dirty cron statement. Then again I guess I could pipe in a call to import the views.
What dump parameters are you using? At the old job we had at least one view and I don't recall any problems restoring from it. But yeah, why so many views?
You see the statement in my original post. I wasn't using any parameters. I do have another cron that backsup the databases but I have to specifically ignore all the views individually (which is a pain in my ass). And it's not like I haven't done research on this. I've searched the motha-fuckin web and it hates me. I'm convinced that mysqldump is just deficient when it comes to views.
Was it a conditional comment? I swear half of a MySQL dump is conditional comments.
And maybe I just haven't found the right one yet.
-
As soon as it hits a view (and I have a lot of them), it dies and stops copying.
No error code?
-
This was the error:
mysqldump: Couldn't execute 'show create table `acctvaluelisting`': SHOW VIEW
command denied to user 'xxxxxxxxxx'@'localhost' for table 'acctvaluelisting'
But the user has all permissions. So I don't understand why it would say that.
-
Does the view reference a table in another database (not sure if that is even possible) that the user doesn't have access to?
-
Oh and what version of mysql?
-
No, all the views reference tables in this database.
Software version: 5.0.96-community
-
http://bugs.mysql.com/bug.php?id=22062 ?
-
That basically said I need the 'show view' permission, which I have already granted:
-
What does 'show grants' on the server actually show? (I've never really trusted cPanel).
-
Well... when I do that, the only user it shows is the cpanel user. So maybe I need to use that user in the cron job instead of my other user that I setup specifically for those 2 DBs.
-
'show grants' should show the permissions of the user you are connected as. You can do 'show grants for user@host' but when debugging I like to connect as the user in question (since the host part isn't always obvious)