my flat form is windows 2003, I use MySQL Server 5.1, how to replicate only selective table, from Main Server to Server 1, server 2 to server 3??, example: I have MasterDatabase which have Product table, I want to replicate Product Table to Server 1, Server 2 to Server 3, both Servers have the same database(MasterDatabase), can anyone give the steps for me; I am new in MySQL…
Thanks in advance
These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.
(BASH> means we’re on the BASH shell, not the MySQL console) (MySQL> means we’re in the MySQL console. You can also run these via PHPMyAdmin if you have that) (Neither one present means these are direct changes to the [mysqld] section of your my.cnf file)
First, set server-id in each servers my.cnf file. set it to a unique number for each in the [ mysqld ] section of the configuration. A good one is something like the IP without dots, so if it were “8.8.8.8”, the server-id would be “8888”. Also, remove all zeroes to trim down the server id size.
On the master, add this to the my.cnf as well:
Next, on the master (after restarting MySQL, changing DB_NAME and TABLE_NAME to the database and table names):
Copy the file above (slave.sql) to each slave. We will need it later.
On the master, run this (changing DB_NAME, TABLE_NAME, USER, HOST, & PASSWORD), one time per slave, changing the IP each time to each slaves IP address:
Next, run this on each slave:
Then run this on each slave (changing HOST_IP, USER_NAME, USER_PASS)
If the database exists, but the table doesn’t, that’s fine. If the table has data, drop the table before going any further (take a dump first of each copy, to ensure you don’t lose data). If the database doesn’t exist, create it.
Now, import the data we grabbed from earlier (slave.sql)
Finally run this:
Once you’re done with this, run this on each slave to make sure you have no issues:
MySQL> SHOW SLAVE_STATUS\G
Look for lines like this:
If either shows “No”, there’s an error, so look for the lines that look like this, and paste their output: