I have a big database which contain a table with about 26 million records. Now i need to make some reports on it from a special period of time.

So how to make a replication from that table from a special time, for example, just show replicate the records from 2015-01-01 till now?

  • Why? Replication is designed to keep Slave in sync with Master. Use a WHERE clause to limit the out what rows are fetched. – Rick James Jun 17 '15 at 16:03
  • @RickJames, Because I need the reports as my IVR system is working as online, and do not need all of the table's records in my reports. So if i can decrease the range of the records, I can make better speed on my reports. Isn't it? – shgnInc Jun 18 '15 at 4:31
  • With a good index, you might not notice any improvement. With "summary tables", it might be fast regardless. – Rick James Jun 18 '15 at 22:29

I suggest using mysqldump instead of replication.

mysqldump <other-params> --where="date >= '2015-01-01'" db table > dump.sql

That way you have dump file which can be reused. Import will take some time, but setup is much simpler - you can import it anywhere and do what you need.

  • So by your answer, How can I use the new records which are saved on the master database? – shgnInc Jun 17 '15 at 11:02
  • Ah, with this you would have to re-import new data each time (only last few days depending on how often) if you needed them. If it is not one-time use, then just add proper indexes with date column for the report where needed. – jkavalik Jun 17 '15 at 11:23

U can use VIEW statement,so that you can replicate the records into a view table.

SELECT c1, c2
FROM  table1
WHERE c2='some value'

The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.