Set up mysql replication and control slave thread

1. Environment

  
  In 192.168.1.202

  Database: BILLING
  DB Account: root
  DB Password:


  In 192.168.1.204
  Database: 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

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=BILLING
        replicate-do-table=BILLING.users
        replicate-do-table=BILLING.plan
        replicate-do-table=BILLING.profile
        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();
        }
    }

}


在项目中有这样的需求， 就是想控制定期的slave thread。 在网上找到了http://forums.mysql.com/read.php?26,43705,43705#msg-43705  上面有两种方式。 不过都比较复杂。 后面不知道怎么就突然想到了 是不是可以用jdbc去做。 然后马上写了个Main跑了一下， 果然可以。

 

参考： http://www.ningoo.net/html/2007/mysql_replication_configuration.html

如果有DBA，写个shell更好些

这里面是有介绍用shell配合corn来做。  但是现在项目需要在代码中控制。