0

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?

Improve this question
3
  • 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
    Commented Jun 17, 2015 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
    Commented Jun 18, 2015 at 4:31
  • With a good index, you might not notice any improvement. With "summary tables", it might be fast regardless.
    – Rick James
    Commented Jun 18, 2015 at 22:29

3 Answers 3

Reset to default
0

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.

Improve this answer
2
  • So by your answer, How can I use the new records which are saved on the master database?
    – shgnInc
    Commented Jun 17, 2015 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
    Commented Jun 17, 2015 at 11:23
0

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

CREATE VIEW DATA_VIEW AS
SELECT c1, c2
FROM  table1
WHERE c2='some value'
WITH CHECK OPTION;

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

Improve this answer
0

Anyone looking at this in 2023 can have a separate analytics database with tables that are populated with views defined as per the reporting requirements (like records within a given timepoint) then include the database in the list of databases to be replicated in the mysql config file.

Improve this answer
2
  • You could improve your answer by explaining this a little bit and citing some references perhaps.
    – Rohit Gupta
    Commented Feb 22, 2023 at 12:50
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    Commented Feb 22, 2023 at 12:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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