Question

Table Replacation in MySQL

Posted January 31, 2015 2k views

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.

×
1 answer

(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 DBNAME and TABLENAME 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 DBNAME, TABLENAME, 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 HOSTIP, USERNAME, 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:
Submit an Answer