3

Each night we run mysqldump from production and dump it to another schema to use as a test or development server, on the same server. As the databases grow it is taking much of the night to finish. I'm trying to think of a way to speed this up. I thought since I am running binlog I thought maybe once a week do the Mysqldump and then the rest of the week just up date the other schema from the binlogs. Of course all this needs to be scripted if it is possible.

Also, is it faster to a mysqldump and import from one schema to another or is it faster to use an update from one schema to another to update all of the tables?

Is it possible to have a master/master on the same instance of mysql?

Jeff Viola
  • 31
  • 2

1 Answers1

1

Did you look into MySQL replication? That's exactly what it does. Reads binlog from the master and executes it on the slave.

There are some limitations, for instance it assumes that you don't do writes to your slaves for data integrity sake.

Master-master replication is technically possible if you partition your data so same pieces of it not changed on different servers. Not sure you can do this about the same instance.

alex-p
  • 46
  • 3
  • Yes, I've used replication before. I need the schema name to be different. Can you replicate to an instance of MySQL on the same server with a different Schema name? – Jeff Viola Nov 01 '12 at 14:35