How to Configure the MySQL Replication

Before You Begin

Determine which cluster will contain the master database at the first start.

1. Prevent the startup of the slave threads.

   On the primary cluster at the node where the MySQL database is active, add the skip-slave-start keyword to the my.cnf file. For example:

   cl1-node1# echo skip-slave-start >> /mysql-data-directory/my.cnf

2. Prevent non-root modifications.

   On the secondary cluster at the node where the MySQL database is active, add the read-only=true directive to the my.cnf file, and restart your database.

   For example:

   cl2-node1# echo read-only=true >> S/mysql-data-directory/my.cnf
   cl2-node1# clresource disable paris-mys-rs
   cl2-node1# clresource enable paris-mys-rs

3. Create the replication user on both databases.
   1. On each cluster, pick the node where the MySQL database is active, and connect as an administrative user who can at least create users.
   2. Create the replication user, and stay connected.

      ---

      Note -  Be sure to create the replication with permissions to connect from any node.

      ---

      • On the primary cluster, which listens to the socket /tmp/paris.sock, issue the following commands:

        cl1-node1:/ # /usr/local/mysql/bin/mysql -S /tmp/paris.sock -uroot -proot
        mysql> use mysql
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-paris-1' identified by 'repl';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-paris-2' identified by 'repl';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-newyork-1' identified by 'repl';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-newyork-2' identified by 'repl';

      • On the secondary cluster, which listens to the socket /tmp/newyork.sock:

        cl2-node3:/ # /usr/local/mysql/bin/mysql -S /tmp/newyork.sock -uroot -proot
        mysql> use mysql
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-paris-1' identified by 'repl';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-paris-2' identified by 'repl';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-newyork-1' identified by 'repl';
        mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'phys-newyork-2' identified by 'repl';

4. Establish the replication between the secondary and primary clusters.
   1. On the primary cluster, issue the following on the MySQL client:

      mysql> FLUSH TABLES WITH READ LOCK;
      mysql> show master status;
      | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB  |
      
      | bin-log.000002 |     1424 |              | sc3_test_database |
      
      1 row in set (0.03 sec)
      
      mysql> unlock tables;

      Note the values for file and position. In the preceding example, they are bin-log.000002 and 1424, respectively.

   2. On the MySQL client on the secondary cluster, issue the following commands:

      mysql> change master to master_host='cluster-paris',
      
      -> master_user='repl',
      
      -> master_password='repl',
      
      -> master_log_file='bin-log.000002',
      
      -> master_log_pos=1424;
      
      Query OK, 0 rows affected (0.04 sec)
      
      mysql> start slave;
      
      Query OK, 0 rows affected (0.03 sec)

   3. Check the slave status.

      mysql> show slave status\G

   4. "Waiting for master to sent event" at Slave_IO_State check for the following message:

      Slave_IO_Running: Yes
      
      Slave_SQL_Running: Yes

   5. Stop the slave.

      mysql> stop slave;

5. Configure the reverse replication to prepare the two clusters for a role swap.
   1. On the secondary cluster, issue the following commands:

      mysql> FLUSH TABLES WITH READ LOCK;
      Query OK, 0 rows affected (0.01 sec)
      mysql> show master status;
      
      | File           | Position | Binlog_Do_DB | Binlog_Ignore_DB  |
      
      | bin-log.000020 |     1162 |              | sc3_test_database |
      
      1 row in set (0.00 sec)
      
      mysql> unlock tables;

      Note the values for file and position. In the preceding example, they are bin-log.000020 and 1162, respectively.

   2. On the MySQL client on the primary cluster, issue the following commands:

      mysql> change master to master_host='cluster-newyork',
      
      -> master_user='repl',
      
      -> master_password='repl',
      
      -> master_log_file='bin-log.000020',
      
      -> master_log_pos=1162;
      
      mysql> start slave;
      
      Query OK, 0 rows affected (0.03 sec)

   3. Check the slave status.

      mysql> show slave status\G

   4. "Waiting for master to sent event" at Slave_IO_State check for the following message:

      Slave_IO_Running: Yes
      
      Slave_SQL_Running: Yes

   5. Stop the slave, and exit the MySQL client.

      mysql> stop slave;
      mysql> exit;

   6. On the MySQL client on the secondary cluster, start the slave, and exit the client.

      mysql> start slave;
      mysql> exit;