Mysql的Replication读写分离

Mysql的Replication原理：
    Mysql的Replication是一个异步的复制过程，从一个Mysql instace（我们称之为Master）复制到另一个Mysql instance（我们称之Slave）。在Master与Slave之间的实现整个复制过程主要由三个线程来完成，其中两个线程Sql线程和IO线程）在Slave端，另外一个线程（IO 线程）在Master端。   
    要实现MySQL的Replication ，首先必须打开Master 端的Binary Log（mysqlbin.xxxxxx）功能，否则无法实现。因为整个复制过程实际上就是Slave从Master端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。打开MySQL的Binary Log可以通过在启动MySQL Server的过程中使用“—log-bin” 参数选项，或者在my.cnf配置文件中的mysqld 参数组（[mysqld]标识后的参数部分）增加“log-bin”参数项。
    Mysql具体Replication过程，可以参见《MySQL性能优化与架构设计》一书的第13.2节。

Mysql的安装：
    CentOS5.5 + Mysql5.0.22 + IP 192.168.1.200 (Master)
    CentOS5.5 + Mysql5.0.22 + IP 192.168.1.241 (Slave)
   
    # groupadd mysql
    # useradd -g mysql mysql

    # tar –zvxf mysql-standard-5.0.22-linux-i686-glibc23.tar.gz
    # mv mysql-standard-5.0.22-linux-i686-glibc23 /usr/local/mysql

    # cd /usr/local/mysql
    # chown –R root /usr/local/mysql
    # chgrp –R mysql /usr/local/mysql
    # chown –R mysql /usr/local/mysql/data
    # cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
    # /usr/local/mysql/scripts/mysql_install_db --user=mysql
    # /usr/local/mysql/bin/mysqld_safe &

    # ps –ef |grep mysql 看看mysql是否启动

Mysql的Replication相关设置：

    启动mysql数据库
    # /usr/local/mysql/bin/mysqld_safe --user=mysql &
    设置读锁
    mysql> flush tables with read lock;
    解锁
    mysql> unlock tables;
    查看log-bin日志文件名和偏移量
    mysql> show master status;

    Master中的设置：

    1、建立Replication账号
    mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.241' IDENTIFIED BY 'repl';
    建立Slave从Master复制日志时使用的账号。
   
    2、修改my.cnf设置(/etc/my.cnf)
    server-id  = 1
    log-bin    = mysql-bin
    设置主服务器的Id，并开启log-bin二进制日志文件。
   
    #binlog-do-db=需要备份的数据库名，可写多行
    #binlog-ignore-db=不需要备份的数据库名，可写多行
      
    3、重启mysql

    Slave中的设置：
   
    1、修改my.cnf设置(/etc/my.cnf)

    修改服务Id:
    server-id  = 1
    修改为
    server-id  = 2

    设置Master信息
    master-host     =   192.168.1.200
    master-user     =   repl
    master-password =   repl
    master-port     =  3306

    #replicate-do-db=test 需要备份的数据库名
    #replicate-ignore-db=mysql 忽略的数据库
    #master-connect-retry=60 如果从服务器发现主服务器断掉，重新连接的时间差(秒)

    2、重启mysql
   
    3、在mysql配置表中设置Master信息：
    注：如果在my.cnf中未设置Master信息，也可以在mysql提示符下输入如下语句进行设置：
    mysql> change master to
    -> master_host=’192.168.1.200′ 
    -> master_user=’repl’ 
    -> master_password=’repl’ 
    -> master_log_file=’mysql-bin.000001′ 
    -> master_log_pos=106;

    4、启动slave中的Replication
    mysql> start slave;


Replication设置检查：
   
    查看启动的slave进程：
    mysql> show processlist\G;
    *************************** 2. row *************************** 
    Id: 2 
    User: system user 
    Host: 
    db: NULL 
    Command: Connect 
    Time: 2579 
    State: Has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
    *************************** 3. row *************************** 
    注：Info: NULL表示slave已经连接上master，开始接受并执行日志

    查看slave进程状态：
    mysql> show slave status;
    Slave_IO_Running: Yes 
    Slave_SQL_Running: Yes
    检查Slave_IO_Running与Slave_SQL_Running的值，如果均为Yes，说明配置成功。


Replication设置测试：

    在Master服务器上
    mysql> create database repl_test;
    mysql> use repl_test;
    mysql> create table member(id int, username varchar(16), password varchar(32));

    在Slave服务器上
    mysql> show databases;
    mysql> use repl_test;
    mysql> show tables member;

Replication常见问题：

    1、由于各种原因，导致主从数据不一致，在负载低的时候，进行手动同步.

    在主服务器上执行
    mysql>flush tables with read lock; 
    Query OK,rows affected (0.01 sec) 
    mysql>show master status; 
    +——————+———-+————–+——————+ 
    | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
    +——————+———-+————–+——————+ 
    | mysql-bin.0000011 | 260| | | 
    +——————+———-+————–+——————+

    在从服务器上执行
    先得到当前主服务器的二进制文件名和偏移量,执行命令使从服务器与主服务器同步
    mysql>select master_pos_wait(‘mysql-bin.0000011′,’260′); 
    +————————————————–+ 
    | master_pos_wait(‘mysql-bin.0000011′,’260′) | 
    +————————————————–+ 
    | 0 | 
    +————————————————–+ 
    1 row in set (0.01 sec)

    同步完成后，在主服务器上执行解锁
    mysql>unlock tables;

    2、切换主从服务器
    当主服务器出现故障时，可将从服务器当主服务器来使用.

    保证所有从数据库都已经执行了relay log中的全部更新，在从服务器中执行stop slave io_thread,用show processlist检查，查看状态是否是Has read all relay log,表示更新完成.

    mysql>stop slave io_thread; 
    Query OK,0 affected (0.00 sec) 
    mysql>show processlist\G; 
    *************************** 2. row *************************** 
    Id: 2 
    User: system user 
    Host: 
    db: NULL 
    Command: Connect 
    Time: 4757 
    State: Has read all relay log; waiting for the slave I/O thread to update it 
    Info: NULL

    在从服务器上执行stop slave，reset master命令，重置成主数据库

    mysql>stop slave; 
    Query OK,0 affected (0.00 sec) 
    mysql>reset master; 
    Query OK,0 affected (0.00 sec)

    删除新的主服务器数据库目录中的master.info和relay-log.info文件，否则下次重启时还会按照从服务器来启动.

参考文章：http://database.51cto.com/art/201009/223213.htm