469,953 Members | 2,392 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,953 developers. It's quick & easy.

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

Aug 10 '06 #1
5 4184
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
Aug 10 '06 #2
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
Aug 10 '06 #3
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
Aug 10 '06 #4
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
Aug 11 '06 #5
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
Aug 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Joe Powell | last post: by
reply views Thread by Brian E. Seppanen | last post: by
reply views Thread by nwiebe | last post: by
3 posts views Thread by jodyblau | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.