Table Replacation in MySQL

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