Question

Table Replacation in MySQL

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


Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

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.

Want to learn more? Join the DigitalOcean Community!

Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in Q&A, subscribe to topics of interest, and get courses and tools that will help you grow as a developer and scale your project or business.

(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.

server-id=YOUR_SERVER_ID_GOES_HERE

On the master, add this to the my.cnf as well:

log-bin=/var/lib/mysql-bin.log
expire-logs-days=7
sync-binlog=1

Next, on the master (after restarting MySQL, changing DB_NAME and TABLE_NAME to the database and table names):

BASH> mysqldump -p DB_NAME TABLE_NAME --master-data > slave.sql

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:

MySQL> GRANT REPLICATION CLIENT ON DB_NAME.TABLE_NAME TO 'USER'@'HOST' IDENTIFIED BY 'PASSWORD';

Next, run this on each slave:

MySQL> RESET SLAVE;

Then run this on each slave (changing HOST_IP, USER_NAME, USER_PASS)

MySQL> CHANGE MASTER TO MASTER_HOST='HOST_IP',MASTER_USER='USER_NAME',MASTER_PASSWORD='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)

BASH>  mysql -u root -p -D DB_NAME_GOES_HERE < slave.sql

Finally run this:

MySQL> START SLAVE;

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:

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

If either shows “No”, there’s an error, so look for the lines that look like this, and paste their output:

                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error: