(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 “22.214.171.124”, 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 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:
If either shows “No”, there’s an error, so look for the lines that look like this, and paste their output: