MySQL Forums
Forum List  »  Replication

Initial setup of replication with large tables
Posted by: A B
Date: August 09, 2011 10:33PM

Hello,

I have been reading a number of articlaes on replication and have a question.

environment:
Windows 2008 64-bit
MySQL server v. 5.5.9

- One file per table
- Total DB size = approximately 700 GB
- Tables range in size up to 15 GB
- Active tables (i.e. being written to actively) size = up to about 3 GB.
- There are about 70 active tables.
- all other tables are monthly archives, which are only used for reporting (i.e. only reads).
- active tables keep 5 days worth of data and then records are moved to the latest monthly archive table. This is done by a process that runs each night.

Reading teh documents there seems two ways to set up the slave with the master's data:
1. do a DB dump of the master database
2. use the LOAD FROM MASTER command

There are issues with both of these:
1. a dump of the entire database takes days to restore so the data in the binary logs on the master will become stale. Not sure what to do about that.

2. the LOAD FROM MASTER option will also take days to get the data across and so will also have the same problem.

These are espacially true of the active tables.

Questions:
A. are my assumptions above correct?
B. How do others set up slaves with such table sizes?
C. is it possible to just load the monthly archives on the slaves from several database dumps of each monthly archive set, then load the active tables (hich only takes about 1 hour) and ensure that all tables then sync correctly between Master and Slave?
D. if C. is possible, do i just dump each monthly archive with the replication options set?


thanks for any assistance.


Is it possible to

Options: ReplyQuote


Subject
Views
Written By
Posted
Initial setup of replication with large tables
4220
A B
August 09, 2011 10:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.