Author Topic: Mysql Replication  (Read 6329 times)

ober

  • Ashton Shagger
  • Ass Wipe
  • Posts: 14305
  • Karma: +73/-790
  • mini-ober is taking over
    • Windy Hill Web Solutions
Mysql Replication
« 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.

hans

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3523
  • Karma: +46/-18
Re: Mysql Replication
« Reply #1 on: August 06, 2013, 11:19:01 AM »
Perhaps try dumping the schema and data separately? And do the data one without views.
This signature intentionally left blank.

hans

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3523
  • Karma: +46/-18
Re: Mysql Replication
« Reply #2 on: August 06, 2013, 11:22:17 AM »
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.
This signature intentionally left blank.

Mike

  • Jackass In Charge
  • Posts: 11248
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: Mysql Replication
« Reply #3 on: August 06, 2013, 11:35:20 AM »
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?

Perspective

  • badfish
  • Jackass In Charge
  • Posts: 4635
  • Karma: +64/-22
    • http://jeff.bagu.org
Re: Mysql Replication
« Reply #4 on: August 06, 2013, 11:36:06 AM »
Can't you just re-run the CREATE VIEW commands after the base tables are copied?

hans

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3523
  • Karma: +46/-18
Re: Mysql Replication
« Reply #5 on: August 06, 2013, 11:36:17 AM »
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.
This signature intentionally left blank.

Mike

  • Jackass In Charge
  • Posts: 11248
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: Mysql Replication
« Reply #6 on: August 06, 2013, 12:04:48 PM »
Was it a conditional comment?  I swear half of a MySQL dump is conditional comments.

hans

  • Guitar Addict
  • Jackass In Charge
  • Posts: 3523
  • Karma: +46/-18
Re: Mysql Replication
« Reply #7 on: August 06, 2013, 12:09:00 PM »
Yup. I really don't understand that but I haven't bothered to become a MySQL admin really either.
This signature intentionally left blank.

ober

  • Ashton Shagger
  • Ass Wipe
  • Posts: 14305
  • Karma: +73/-790
  • mini-ober is taking over
    • Windy Hill Web Solutions
Re: Mysql Replication
« Reply #8 on: August 06, 2013, 01:02:30 PM »
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.

Rob

  • New improved. Now with added something...
  • Jackass In Charge
  • Posts: 5959
  • Karma: +86/-149
  • Approaching 60 from the wrong damn direction...
Re: Mysql Replication
« Reply #9 on: August 07, 2013, 04:21:43 AM »
As soon as it hits a view (and I have a lot of them), it dies and stops copying.

No error code?

ober

  • Ashton Shagger
  • Ass Wipe
  • Posts: 14305
  • Karma: +73/-790
  • mini-ober is taking over
    • Windy Hill Web Solutions
Re: Mysql Replication
« Reply #10 on: August 07, 2013, 10:04:33 AM »
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.

Mike

  • Jackass In Charge
  • Posts: 11248
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: Mysql Replication
« Reply #11 on: August 07, 2013, 11:48:45 AM »
Does the view reference a table in another database (not sure if that is even possible) that the user doesn't have access to?

Mike

  • Jackass In Charge
  • Posts: 11248
  • Karma: +168/-32
  • Ex Asshole - a better and more caring person.
Re: Mysql Replication
« Reply #12 on: August 07, 2013, 11:49:08 AM »
Oh and what version of mysql?

ober

  • Ashton Shagger
  • Ass Wipe
  • Posts: 14305
  • Karma: +73/-790
  • mini-ober is taking over
    • Windy Hill Web Solutions
Re: Mysql Replication
« Reply #13 on: August 07, 2013, 12:42:18 PM »
No, all the views reference tables in this database.

Software version: 5.0.96-community

Rob

  • New improved. Now with added something...
  • Jackass In Charge
  • Posts: 5959
  • Karma: +86/-149
  • Approaching 60 from the wrong damn direction...
Re: Mysql Replication
« Reply #14 on: August 07, 2013, 04:17:54 PM »