How to Configure the Number of Replicas in MySQL

In MySQL you can set up a database replication (replica) from one server to another. This is a direct sync that mirrors all actions on each server. Therefore, it is unlike a backup, because delete commands will be mirrored on each as well. The number of replicas you set up is up to you. Each replication is configured compared to an initial database, therefore you can repeat this process. This is useful for security or up-time, where one server may go down at some stage.

Instructions

Master

1. Log-in to your server via the secure shell (SSH). Use the details provided to you by your host.

2. Type "vi /etc/mysql/my.cnf" and press "Enter." Locate the "#skip-networking" and "#bind-address" lines and remove the hashtags from the start. Add the following lines to the file:

   log-bin = /var/log/mysql/mysql-bin.log

   binlog-do-db=exampledb

   server-id=1

   Press "Esc," then type ":x" to exit and save from vi.

3. Type "/etc/init.d/mysql restart" and press "Enter." Log-in by typing "mysql -u root -p" and pressing "Enter," then type your password and pressing "Enter."

4. Type the following command into mysql:

   GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';

   FLUSH PRIVILEGES;

   Replacing "some_password" with a password.

5. Type "USE databasename;" (entering your own database name) and press "Enter."

   Type "FLUSH TABLES WITH READ LOCK;" and press "Enter," then type "SHOW MASTER STATUS;" and press "Enter."

6. Make a note of the displayed text, then type "quit;" and press "Enter."

Slave

1. Type "mysqldump -u root -p PASSWORD -opt databasename > databasename.sql" (replacing PASSWORD and databasename with appropriate tags) and press "Enter." This dumps the database to your server.

2. Log-in to your second server which will act as the slave. Create a database that uses the same name as your original.

3. Type "mysql -u root -p PASSWORD < /databasename.sql" (replacing each parameter appropriately). Press "Enter."

4. Type "vi /etc/mysql/my.cnf" and press "Enter." Add the following lines, replacing each parameter for your own server:

   server-id=2

   master-host=192.168.0.100

   master-user=slave_user

   master-password=secret

   master-connect-retry=60

   replicate-do-db=databasename

   Press "Esc," then ":x" to save and exit. Type "/etc/init.d/mysql restart" and press "Enter."

5. Type "mysql -u root -p" and press "Enter." Type your password and press "Enter." Then type "SLAVE STOP;" and press "Enter."

6. Type "CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;" (replacing all parameters appropriately) and press "Enter."

7. Type "START SLAVE;" and press "Enter." Finally, type "quit;" and press "Enter." To increase the number of replicas, repeat this process as many times as you wish.

References

• HowToForge: How To Set Up Database Replication In MySQL