Replicating part of database

[Radek]

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

[Steven Musumeche]

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

[Radek]

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

[Steven Musumeche]

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

[Radek]

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

[Steven Musumeche]

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