Skip Navigation Links
Exit Print View
| Oracle Solaris Cluster Geographic Edition System Administration Guide Oracle Solaris Cluster 4.1

Search Scope:

- This Document
- Entire Library

### How to Configure the MySQL Replication

Before You Begin

Before you can configure the replication, you must decide which cluster will contain the master database at the first start.

1. Prevent the startup of the slave threads.

   On the master 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 slave 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 >/mysql-data-directory/my.cnf
       cl2-node1 # clresource disable mys-rs
       cl2-node1 # clresource enable 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.
   3. This example assumes that the MySQL database on the primary cluster listens to the socket `/tmp/nyc.sock`:

          cl1-node1:/ # /usr/local/mysql/bin/mysql -S /tmp/nyc.sock -uroot -proot
          mysql> use mysql
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl1-node1' identified by 'repl';
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl1-node2' identified by 'repl';
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl2-node3' identified by 'repl';
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl2-node4' identified by 'repl';

   4. This example assumes that the MySQL database on the secondary cluster listens to the socket `/tmp/sfo.sock`:

          cl2-node3:/ # /usr/local/mysql/bin/mysql -S /tmp/sfo.sock -uroot -proot
          mysql> use mysql
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl1-node1' identified by 'repl';
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl1-node2' identified by 'repl';
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl2-node3' identified by 'repl';
          mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'cl2-node4' identified by 'repl';

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

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 slave, issue the following commands:

          mysql> change master to master_host='nyc',

              -> 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='sfo',

              -> 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. c. 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. e. Stop the slave, and exit the MySQL client.

          mysql> stop slave;
          mysql> exit;

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

          mysql> start slave;
          mysql> exit;

### Configuring the MySQL Application Resource Group

At a minimum, you must create a resource group that contains a logical host resource. You must leave the resource group in an unmanaged state.

For example, on cluster `nyc`, you would issue the following commands:

    cl1-node1 # clresourcegroup create usa-rg
    cl1-node1 # clresourcegroup set -p Auto_start_on_new_cluster=false  usa-rg
    cl1-node1 # clresourcegroup set -p RG_Affinities=+++nyc-rg  usa-rg
    cl1-node1 # clreslogicalhostname create -g usa-rg usa
    cl1-node1 # clresource enable usa

On cluster `sfo`, you would issue the following commands:

    cl2-node1 # clresourcegroup create usa-rg
    cl2-node1 # clresourcegroup set -p Auto_start_on_new_cluster=false  usa-rg
    cl2-node1 # clresourcegroup set -p RG_Affinities=+++sfo-rg  usa-rg
    cl2-node1 # clreslogicalhostname create -g usa-rg usa
    cl2-node1 # clresource enable usa

Copyright © 2004, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |