We have a production server and a dev server. We have found that backups are nearly impossible on the production server because of the query volume we experience. So, we're looking at setting up replication with our dev server being the slave. This is ideal because we can afford to lock the tables on that server and additionally it will be nice to have up to date data for the developers. Now, the issues. The production server can't really be taken down or locked at this point, at least not easily. We have a high query volume and fairly large 30+ GB innodb tables. Both servers are running all innodb and are also both on mysql 5.1. What can we do to sync the data initially to get replication started? I've tried a few options, but so far, none have worked.
views:
140answers:
1Your question seems to indicate that you have a single production server with no redundancy and no plans for how to deal with a failure of the primary database server. If this assumption is true, then there are two paths you could take to set up replication.
Continue down the path of setting up replication with the architecture as is. If you choose this path, then more information would be needed to know what is the best approach. For example, are binary logs enabled? Without the details, I believe the best advice I can give is to look into the Percona XtraBackup tool.
The second option is to update your production database architecture to include redundancy and a workable fail-over plan. This would give you a more robust environment and the ability to setup additional replication easily. I would highly recommend this option since you will eventually have an issue with the production database server which will result in an unplanned outage.