在MySql上实现Replication(Master 与 Slave 数据同步) 一

 

1： 首先确定Master和Slave的数据库版本，Master数据库的版本不能高于Slave数据的版本。

这里我是使用MySql 5.0.27 作为Master数据库，MySql 6.0.3（alpha）作为Slave进行测试。

 

2：首先在Master数据库的配置文件my.ini (windows)里添加log-bin 和 server-id 两项

  eg: [mysqld]

     log-bin = mysql-bin  //使用的二进制日志文件名

     server-id = 1  //服务器编号

   在Slave数据库的配置文件my.ini里添加server-id 项

   eg:[mysqld]

        server-id = 2

(这里需要理解的是Slave本身也是一个独立的服务器，它作为‘从数据库’是从它通过‘主服务器’日志更新数据角度上理解的。可以把 server-id 想象成为IP地址：这些ID标识了整个同步组合中的每个服务器。如果没有指定 server-id 的值，如果也没定义 master-host，那么它的值就为1，否则为2。注意，如果没有设定 server-id，那么master就会拒绝所有的slave连接，同时slave也会拒绝连接到master上。)

 

3：修改配置后启动Master数据服务。在Master数据库上建立一个用户，用于Slave数据连接以便同步数据。一般来说Slave数据只用于同步数据，所以我们在建立这个用户时只授予它REPLICATION SLAVE 权限。

  eg: GRANT REPLICATION SLAVE ON *.* TO ‘slaver’@’%’ IDENTIFIED BY ‘slaver’;

 

4：在Master数据库上执行 FLUSH TABLES WITH READ LOCK; 命令以刷新数据并阻止对Master数据的写入操作。然后将Master数据的data目录复制一份覆盖Slave数据库的data目录，这样Master和Slaver就有了相同的数据库了。在复制时可能不需要同步 mysql 数据库，因为在slave上的权限表和master不一样。这时，复制的时候要排除它。同时不能包含任何`master.info~ 或 `relay-log.info` 文件。覆盖好后执行 UNLOCK TABLES; 释放锁定。

 

5：在Master数据库上执行SHOW MASTER STATUS; 查看当前Master数据库上的一些我们将要使用的信息：

 

File 表示 Master用于记录更新数据操作的日志文件，Position 表示当前日志的记录位置，这也是Slave 需要开始同步数据的位置。

 

6：启动Slave数据库 执行：（这点连接Master数据库所要的参数）

mysql> CHANGE MASTER TO

    ->  MASTER_HOST='127.0.0.1',  //Master服务器地址（我是在本机上安装两个数据库的）

    ->  MASTER_USER='slaver,  //Slave服务器更新时连接Master使用的用户名

    ->  MASTER_PASSWORD='slaver',  // Slave服务器更新时连接Master使用的密码

    ->  MASTER_LOG_FILE='mysql-bin.000004', //更新操作日志

    ->  MASTER_LOG_POS=837016;   //同步数据的开始位置

 

上面命令执行完毕后，执行START SLAVE; 命令启动数据更新。在Slave 数据库上执行:

SHOW SLAVE STATUS; 查看从数据跟主数据库的连接状态是否正常，如果显示的信息中

的 Slave-IO-Running 和 Slave_SQL_Running 值为 yes，表示用于数据同步的 io线程和sql操作线程已经成功启动。

 

7：到此已经建立Master和Slave数据库的同步了。你可以在Master数据库上更新一个表的数据，然后查看Slave数据库上对应表是否做了相应的更改。

注： slave开始同步后，就能在数据文件目录下找到2个文件 `master.info` 和`relay-log.info`。slave利用这2个文件来跟踪处理了多少master的二进制日志。master.info 记录了slave 连接master进行数据同步的参数，relay-log.info 记录了slave进行数据更新使用的中续日志的的信息。

Master 与 Slave 数据同步后对性能进行粗略的测试：

(1). 在Master数据库上建立一个新的数据库testdata，并建立表testdata。建立脚本如下

CREATE   DATABASE  `testdata`  /**/ /*!40100 DEFAULT CHARACTER SET utf8 */ ;

DROP   TABLE   IF   EXISTS  `testdata`.`testms`;
CREATE   TABLE   `testdata`.`testms` (
  `name`  text   NOT   NULL
) ENGINE = InnoDB  DEFAULT  CHARSET = utf8;

注意：由于上面我们建立的Master和Slave之间的数据同步是针对所有数据库的，所以当你在Master上建立数据库和表后，Slave 上自动也会建立对应的数据库和表，如果发现Slave上没有对应的数据库和表则必须检查是否Master和Slave是否正常同步数据。

 

(2). 测试代码如下（Java）：

分别调用两个不同的方法，进行两种类型的测试

package  com.ckcs.test;
/**/ /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import  java.sql.Connection;
import  java.sql.DriverManager;
import  java.sql.SQLException;
import  java.sql.Statement;
import  java.util.logging.Level;
import  java.util.logging.Logger;

/** */ /**
 *
 * @author Administrator
 */
public   class  TestMS  ... {

    /** *//**
     * 对master数据库进行数据插入和查询操作
     */
    private void testSingleDB() ...{
        try ...{
            //Master数据库
            final String singleUrl = "jdbc:mysql://localhost:3306/testdata?characterEncoding=utf-8&user=super&password=iamasuper006";
            Class.forName("com.mysql.jdbc.Driver");
            new Thread() ...{

                public void run() ...{
                    long start = System.currentTimeMillis();
                    try ...{
                        Connection updateCon = DriverManager.getConnection(singleUrl);
                        for (int i = 0; i < 1000; i++) ...{
                            Statement state = updateCon.createStatement();
                            //插入数据
                            state.execute("insert into testms values('wo de tian kong.')");
                            state.close();
                        }
                    } catch (SQLException ex) ...{
                        Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("更新用时： " + (System.currentTimeMillis() - start));
                }
            }.start();
            new Thread() ...{

                public void run() ...{
                    long start = System.currentTimeMillis();
                    try ...{
                        Connection queryCon = DriverManager.getConnection(singleUrl);
                        for (int j = 0; j < 100000; j++) ...{
                            Statement queryState = queryCon.createStatement();
                            //查询数据
                            queryState.execute("select * from testms");
                            queryState.close();
                        }
                    } catch (SQLException ex) ...{
                        Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("查询用时： " + (System.currentTimeMillis() - start));
                }
            }.start();


        } catch (Exception ex) ...{
            Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /** *//**
     * 对Master数据库进行数据的插入，对Slave数据库进行数据的查询
     */
    private void testMSDB() ...{
        try ...{
            //Master 数据库
            final String updateUrl = "jdbc:mysql://localhost:3306/testdata?characterEncoding=utf-8&user=super&password=iamasuper006";
            Class.forName("com.mysql.jdbc.Driver");
            new Thread() ...{

                public void run() ...{
                    long start = System.currentTimeMillis();
                    try ...{
                        Connection updateCon = DriverManager.getConnection(updateUrl);
                        for (int i = 0; i < 1000; i++) ...{
                            Statement updateState = updateCon.createStatement();
                            //插入数据
                            updateState.execute("insert into testms values('wo de tian kong.')");
                            updateState.close();
                        }
                    } catch (SQLException ex) ...{
                        Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("更新用时： " + (System.currentTimeMillis() - start));
                }
            }.start();
            //Slave数据库
            final String queryUrl = "jdbc:mysql://localhost:3307/testdata?characterEncoding=utf-8&user=super&password=iamasuper006";
            new Thread() ...{

                public void run() ...{
                    long start = System.currentTimeMillis();
                    try ...{
                        Connection queryCon = DriverManager.getConnection(queryUrl);
                        for (int j = 0; j < 100000; j++) ...{
                            Statement queryState = queryCon.createStatement();
                            //查询数据
                            queryState.execute("select * from testms");
                            queryState.close();
                        }
                    } catch (SQLException ex) ...{
                        Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("查询用时： " + (System.currentTimeMillis() - start));
                }
            }.start();
        } catch (Exception ex) ...{
            Logger.getLogger(TestMS.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void main(String[] args) ...{
        TestMS test = new TestMS();
        test.testMSDB();
    }
}

 

(3).测试所得数据：

（1）测试数据比较

注： 查询数据次数为 100000;， 插入数据次数为1000

测试 类型 测试次数 用时	对Master数据库进行插入和查询操作		对Master数据库进行插入操作，对Slave数据库进行查询操作
	插入	查询	插入	查询
第一次用时	63141	18172	66078	10656
第二次用时	67875	20109	68969	11860
第三次用时	65796	18265	65672	10906
平均用时	65604	18848	66906	11140

                                      （测试数据统计表）

虽然数据测试是很粗糙的，但确实反映出性能的一定改善。