Replicating part of database

Hi

I'm in need of creating reporting database. What I have to do it is to
replicate only half of tables to next database so reports could query
only that second database. Unfortunately there is too many inserts in
existing database so simple master/slave solution won't give me much. I
was wondering if there is a way to replicate just few tables? Can I use
for it mysql replication or do I have to create manually scripts to do
that?
I expect that reporting servers is a typical way to report off of
transactual systems, hope some of you had experience with similar
problem.

Thanks in advance for any tips
Radek

Re: Replicating part of database

Radek wrote:
> Hi
>
> I'm in need of creating reporting database. What I have to do it is to
> replicate only half of tables to next database so reports could query
> only that second database. Unfortunately there is too many inserts in
> existing database so simple master/slave solution won't give me much. I
> was wondering if there is a way to replicate just few tables? Can I use
> for it mysql replication or do I have to create manually scripts to do
> that?
> I expect that reporting servers is a typical way to report off of
> transactual systems, hope some of you had experience with similar
> problem.
>
> Thanks in advance for any tips
> Radek
>

Yes, you can do that. Check out the MySQL manual for the my.cnf
settings for:

replicate-do-db
replicate-do-table
replicate-wild-do-table

Using these filtering options, you can have only certain tables
replicate to your slave. Then, you can run your reports off of the
slave tables.

A great book about setting up complex replication is Jeremy Zawodny's
High Performance MySQL.

-Steven

Re: Replicating part of database

Thanks for a quick answer! My only concern here is that the tables I
want to replicate are created (summarized) from tables (huge 50GB+
containing logs) I do not want to replicate. I'm wondering if MySQL
will do the work in this situation?

Thanks for a book tip. I will check it out.

Cheers
Radek

Steven Musumeche wrote:
> Radek wrote:
> > Hi
> >
> > I'm in need of creating reporting database. What I have to do it is to
> > replicate only half of tables to next database so reports could query
> > only that second database. Unfortunately there is too many inserts in
> > existing database so simple master/slave solution won't give me much. I
> > was wondering if there is a way to replicate just few tables? Can I use
> > for it mysql replication or do I have to create manually scripts to do
> > that?
> > I expect that reporting servers is a typical way to report off of
> > transactual systems, hope some of you had experience with similar
> > problem.
> >
> > Thanks in advance for any tips
> > Radek
> >
>
> Yes, you can do that. Check out the MySQL manual for the my.cnf
> settings for:
>
> replicate-do-db
> replicate-do-table
> replicate-wild-do-table
>
> Using these filtering options, you can have only certain tables
> replicate to your slave. Then, you can run your reports off of the
> slave tables.
>
> A great book about setting up complex replication is Jeremy Zawodny's
> High Performance MySQL.
>
> -Steven

Re: Replicating part of database

Yes, you can do that. You would use MySQL's filtering options
(replicate-ignore-table) to ignore the log tables. All other tables
would replicate, but anything defined in your replicate-ignore-table
filters will not replicate.

Good luck,
Steven

Radek wrote:
> Thanks for a quick answer! My only concern here is that the tables I
> want to replicate are created (summarized) from tables (huge 50GB+
> containing logs) I do not want to replicate. I'm wondering if MySQL
> will do the work in this situation?
>
> Thanks for a book tip. I will check it out.
>
> Cheers
> Radek
>
> Steven Musumeche wrote:
>> Radek wrote:
>>> Hi
>>>
>>> I'm in need of creating reporting database. What I have to do it is to
>>> replicate only half of tables to next database so reports could query
>>> only that second database. Unfortunately there is too many inserts in
>>> existing database so simple master/slave solution won't give me much. I
>>> was wondering if there is a way to replicate just few tables? Can I use
>>> for it mysql replication or do I have to create manually scripts to do
>>> that?
>>> I expect that reporting servers is a typical way to report off of
>>> transactual systems, hope some of you had experience with similar
>>> problem.
>>>
>>> Thanks in advance for any tips
>>> Radek
>>>
>> Yes, you can do that. Check out the MySQL manual for the my.cnf
>> settings for:
>>
>> replicate-do-db
>> replicate-do-table
>> replicate-wild-do-table
>>
>> Using these filtering options, you can have only certain tables
>> replicate to your slave. Then, you can run your reports off of the
>> slave tables.
>>
>> A great book about setting up complex replication is Jeremy Zawodny's
>> High Performance MySQL.
>>
>> -Steven
>

Re: Replicating part of database

Hey Steven

Thanks for help.
Looks like it is not so simple anyway:
http://forum.mysqlperformanceblog.com/s?t=rview&goto=9

cheers
Radek

Steven Musumeche wrote:
> Yes, you can do that. You would use MySQL's filtering options
> (replicate-ignore-table) to ignore the log tables. All other tables
> would replicate, but anything defined in your replicate-ignore-table
> filters will not replicate.
>
> Good luck,
> Steven
>
> Radek wrote:
> > Thanks for a quick answer! My only concern here is that the tables I
> > want to replicate are created (summarized) from tables (huge 50GB+
> > containing logs) I do not want to replicate. I'm wondering if MySQL
> > will do the work in this situation?
> >
> > Thanks for a book tip. I will check it out.
> >
> > Cheers
> > Radek
> >
> > Steven Musumeche wrote:
> >> Radek wrote:
> >>> Hi
> >>>
> >>> I'm in need of creating reporting database. What I have to do it is to
> >>> replicate only half of tables to next database so reports could query
> >>> only that second database. Unfortunately there is too many inserts in
> >>> existing database so simple master/slave solution won't give me much. I
> >>> was wondering if there is a way to replicate just few tables? Can I use
> >>> for it mysql replication or do I have to create manually scripts to do
> >>> that?
> >>> I expect that reporting servers is a typical way to report off of
> >>> transactual systems, hope some of you had experience with similar
> >>> problem.
> >>>
> >>> Thanks in advance for any tips
> >>> Radek
> >>>
> >> Yes, you can do that. Check out the MySQL manual for the my.cnf
> >> settings for:
> >>
> >> replicate-do-db
> >> replicate-do-table
> >> replicate-wild-do-table
> >>
> >> Using these filtering options, you can have only certain tables
> >> replicate to your slave. Then, you can run your reports off of the
> >> slave tables.
> >>
> >> A great book about setting up complex replication is Jeremy Zawodny's
> >> High Performance MySQL.
> >>
> >> -Steven
> >

Re: Replicating part of database

Do you want to create the summary tables on the master and then have
them replicate to the slave? If so, then you won't have a problem
unless you are using a SELECT INSERT statement.

-Steven

Radek wrote:
> Hey Steven
>
> Thanks for help.
> Looks like it is not so simple anyway:
> http://forum.mysqlperformanceblog.com/s?t=rview&goto=9
>
> cheers
> Radek
>
> Steven Musumeche wrote:
>> Yes, you can do that. You would use MySQL's filtering options
>> (replicate-ignore-table) to ignore the log tables. All other tables
>> would replicate, but anything defined in your replicate-ignore-table
>> filters will not replicate.
>>
>> Good luck,
>> Steven
>>
>> Radek wrote:
>>> Thanks for a quick answer! My only concern here is that the tables I
>>> want to replicate are created (summarized) from tables (huge 50GB+
>>> containing logs) I do not want to replicate. I'm wondering if MySQL
>>> will do the work in this situation?
>>>
>>> Thanks for a book tip. I will check it out.
>>>
>>> Cheers
>>> Radek
>>>
>>> Steven Musumeche wrote:
>>>> Radek wrote:
>>>>> Hi
>>>>>
>>>>> I'm in need of creating reporting database. What I have to do it is to
>>>>> replicate only half of tables to next database so reports could query
>>>>> only that second database. Unfortunately there is too many inserts in
>>>>> existing database so simple master/slave solution won't give me much. I
>>>>> was wondering if there is a way to replicate just few tables? Can I use
>>>>> for it mysql replication or do I have to create manually scripts to do
>>>>> that?
>>>>> I expect that reporting servers is a typical way to report off of
>>>>> transactual systems, hope some of you had experience with similar
>>>>> problem.
>>>>>
>>>>> Thanks in advance for any tips
>>>>> Radek
>>>>>
>>>> Yes, you can do that. Check out the MySQL manual for the my.cnf
>>>> settings for:
>>>>
>>>> replicate-do-db
>>>> replicate-do-table
>>>> replicate-wild-do-table
>>>>
>>>> Using these filtering options, you can have only certain tables
>>>> replicate to your slave. Then, you can run your reports off of the
>>>> slave tables.
>>>>
>>>> A great book about setting up complex replication is Jeremy Zawodny's
>>>> High Performance MySQL.
>>>>
>>>> -Steven
>