How To Control Mysql Replication

jiay

How To Control Mysql Replication ZT
1. Environment
  
  In 192.168.1.202

  Database: RMC_BILLING
  DB Account: root
  DB Password:


  In 192.168.1.204
  Database: RMC_BILLING
  DB Account: root
  DB Password:

  We need to replicate the following tables from 192.168.1.204 to 192.168.1.202.
  users
  plan
  profile
  promotion_plan
  roles
  service_settings
  credit_cards
  currency_exchange
  country
  conference_plan

2. Steps

  Because we need replicate the data from 204 to 202, So, Mysql server in 204 is master, and the 202 is slave.
 
  a. Ssh to 204, Open /etc/mysql/my.cnf mysql configuration file. Make sure the following two lines is uncommented.

           server-id               = 1
           log_bin                 = /var/log/mysql/mysql-bin.log

  b. Ssh to 202, Open /etc/mysql/my.cnf mysql configuration file. Make sure it has following lines.

        server-id=2
        log-slave-updates
        read-only=1
        replicate-do-db=RMC_BILLING
        replicate-do-table=RMC_BILLING.users
        replicate-do-table=RMC_BILLING.plan
        replicate-do-table=RMC_BILLING.profile
        replicate-do-table=RMC_BILLING.promotion_plan
        replicate-do-table=RMC_BILLING.roles
        replicate-do-table=RMC_BILLING.service_settings
        replicate-do-table=RMC_BILLING.credit_cards
        replicate-do-table=RMC_BILLING.currency_exchange
        replicate-do-table=RMC_BILLING.country
        replicate-do-table=RMC_BILLING.conference_plan
        relay-log-purge=1

  c. Connect to master mysql server, Create a mysql user for replication.
 
        mysql> grant replication slave on *.* to 'rep'@'%' identified by 'rep';

  d. Lock the tables on master mysql server, So we can dump the master server's data and import to slave server. Make master and slave has the same data.
 
        mysql> flush tables with read lock;

  e. Show master status, and record the data.

        mysql> show master status;
        +------------------+----------+--------------+------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +------------------+----------+--------------+------------------+
        | mysql-bin.000002 |      228 |              |                  |
        +------------------+----------+--------------+------------------+
        1 row in set (0.00 sec)
 
  f. Connect to slave server and run the following commands. Notice, the data of MASTER_LOG_FILE and MASTER_LOG_POS come from step 6.

        mysql> CHANGE MASTER TO
            -> MASTER_HOST='192.168.1.204',
            -> MASTER_USER='rep',
            -> MASTER_PASSWORD='rep',
            -> MASTER_LOG_FILE='mysql-bin.000002',
            -> MASTER_LOG_POS=228;
        Query OK, 0 rows affected (0.01 sec)   

  g. Start the slave thread.

        mysql> start slave;

  h. OK, the replication is completed, Don't forget to unlock the master's tables.

        mysql> unlock tables;



3. Start/Stop slave thread by Java code.

We can start/stop the slave thread by java code(actually by JDBC). First create a user with all privileges.

mysql> GRANT ALL PRIVILEGES ON *.* TO slave@"%"  IDENTIFIED BY 'slave';

OK, Now, We have a user which use to start/stop slave thread. The following code is how to start/stop slave thread.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class Slave {

    /**
     * @param args
     */
    public static void main(String[] args) {
        String connString = "jdbc:mysql://192.168.1.202:3306/RMC_BILLING";
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            Connection connection = DriverManager.getConnection(connString, "slave", "slave");
            PreparedStatement ps = connection.prepareStatement("start slave");
            //The next line is used to stop the slave.
            //PreparedStatement ps = connection.prepareStatement("stop slave");
            int executeUpdate = ps.executeUpdate();
            System.out.println(executeUpdate);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}


OK, That's all you need to do.