Mysql how to replicate only one table

bibi92
bibi92 used Ask the Experts™
on
Hello,

I search a procedure to replicate only one table.

Thanks

Regards

bibi
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
from mysql.com:

--replicate-do-table=db_name.tbl_name

Tells the slave thread to restrict replication to the specified table. To specify more than one table, use this option multiple times, once for each table. This works for both cross-database updates and default database updates, in contrast to --replicate-do-db. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.

This option affects only statements that apply to tables. It does not affect statements that apply only to other database objects, such as stored routines. To filter statements operating on stored routines, use one or more of the --replicate-*-db options.

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-do-table
Distinguished Expert 2017

Commented:
The same way as above you can configure the master server to only bin-log transactions for a specific table.

Be careful changing the master to only log transactions for a specific table. It all depends on your setup, but I recently had to setup replication similar to what you are after, several complete databases and a few tables in some other databases.

It turned out a few scripts were setup to use a different database to which the tables were based in.

Similar to examples found in the MySQL manual.

USE db4;
UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;

http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

I had to setup replication like:

--replicate-do-db=db4
--replicate-wild-ignore-table=db4.%
--replicate-do-table=db1.table1

This was used the 'catch' extra queries that were using the wrong default database.

Just something else to consider.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Thanks, but where I have to configure this in master or slave ?

--replicate-do-db=db4
--replicate-wild-ignore-table=db4.%
--replicate-do-table=db1.table1
Thanks
bibi
In the my.cnf file on the replication server.

server-id          = ID OF REPLICATION SERVER
master-host     = IP ADDRESS OF THE LIVE SERVER
master-user     = REPLICATION USERNAME
master-password = REPLICATION USER PASSWORD

replicate-do-db=db4
replicate-do-table=db1.table1
replicate-wild-ignore-table=db4.%
If you want you can put them in the startup command instead.

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --replicate-do-db=db4 ... --user=mysql &

Without -- in the my.cnf
With -- in the startup command.

You will also need to create the user on the live server with replication permissions.

http://dev.mysql.com/doc/refman/5.0/en/replication-howto-repuser.html

Author

Commented:
Sorry, it's an error, I want to close with 500 for marklogan.

Author

Commented:
error : 500 points for marklogan
Allocate points correctly.

Thank you bibi92.

Author

Commented:
Thanks a lot bibi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial