mysql 主从复制 以及 读写分离

主从复制

1. 配置master

1. 关闭mysql服务(net stop mysql), 然后 在配置文件my.ini  中加入

    # Binary Logging.
    # log-bin

    log-bin=master-bin
    log-bin-index=master-bin.index
    server-id=1

2.  重启mysql服务(net start mysql),  然后 创建 复制用户, 并授予 复制 权限:

    create user repl_user;

    grant replication slave on *.* to repl_user identified by 'repl_user';

2. 配置slave

1. 关闭mysql服务(net stop mysql), 然后 在配置文件my.ini  中加入

    server-id=2
    relay-log-index=slave-relay-bin.index
    relay-log=slave-relay-bin

2. 重启mysql服务(net start mysql),  并连接至 master

   change master to master_host='your-host-name',master_port=3306,master_user='repl_user',master_password='repl_user';

3. 启动 slave

   start slave;

3. 测试主从复制

在 master上创建数据库以及创建测试表

    create database repl_test;

    create table test(id int not null primary key auto_increment,name varchar(20));

在slave 上查看 含有的数据库(show databases),即可看到 reple_test 数据库，同时在reple_test数据库中也含有 test表, 说明 配置成功！

读写分离

基于以上的主从复制，现在我们可以来实现 读写分离 了，首先需要下载 mysql-proxy, 当然也可以用 ameoba, 但因为考虑到其不支持事务，所以暂时用mysql-proxy

1. 解压 mysql-proxy-0.8.4-win32-x86 到 {home}目录

2. 在 mysql-proxy-0.8.4-win32-x86 目录下 创建logs/mysql-proxy.log

3. 在 mysql-proxy-0.8.4-win32-x86 目录下 创建 mysql-proxy.ini文件,其文件内容如下

  [mysql-proxy]
  daemon=true
  proxy-skip-profiling=true
  keepalive = true
  log-level=debug
  log-file=logs/mysql-proxy.log
  proxy-lua-script=share/doc/mysql-proxy/rw-splitting.lua
  proxy-address=localhost:4040
  proxy-backend-addresses = 192.168.51.106:3306
  proxy-read-only-backend-addresses = 192.168.51.110:3306

4. 启动mysql-proxy(如果需要将其添加到环境变量中),

      mysql-proxy --defaults-file="{home}\mysql-proxy-0.8.4-win32-x86\mysql-proxy.ini"

5. 在master 和 slave上分配权限给新建用户proxy

   GRANT ALL privileges ON *.* TO 'proxy'@'yaogaolin-PC' IDENTIFIED BY 'proxy';

6. 测试

  1. （注意：为方便测试，我们可以修改读写分离Lua脚本,Lua脚本默认最小4个最大8个以上的客户端连接才会实现读写分离（这是因为mysql-proxy会检测客户端连接, 当连接没有超过min_idle_connections预设值时，不会进行读写分离，即查询操作会发生到Master上），现改为最小1个最大2个（当多于2个连接才会进行读写分离，所以测的时候mysql -u proxy -p111111 -P4040 -h localhost，这个命令要第三次才能看到效果，这点很重要），我们修改{home}\share\doc\mysql-proxy\rw-splitting.lua脚本，改动内容如下所示：
    if not proxy.global.config.rwsplit then 
            proxy.global.config.rwsplit = { 
                    min_idle_connections = 1, 
                    max_idle_connections = 2, 
    
                    is_debug = false
            } 
    end

  2. 连接master,并在其下创建 repl_test数据库,以及test数据库：

    create database repl_test;

   create table test(id int not null primary key auto_increment, name varchar(20));

  insert into test(null,'gordon');

3. 观察slave, 直到主从复制成功，即在slave能查询到 test这张表的数据时 可以 关闭 slave,以 做测试：stop slave;

4. 启动cmd ,并 连接至 proxy : mysql -u proxy -P 4040 -h localhost -p(注意端口为 4040),然后再test表中插入数据：

   insert into test(null,'gordon2');

  并进行查询，可以看到test表中含有两条数据

5. 启动cmd, 并连接至 proxy, 同时查看test表中的数据，为1条, 说明配置成功！