mysql主从复制，读写分离

一、  Mysql主服务器Master配置

1、修改主服务器配置:

   #vi /etc/my.cnf

      

         binlog-do-db=db1         #根据具体要复制的库进行添加

         binlog-do-db=db2

         binlog-do-db=db3

         binlog-ignore-db = mysql   #可以不写

 

         log-bin=mysql-bin         #启用二进制日志

server-id=1              #服务器唯一ID

 

2、重启MySQL

         Service mysql restart

 

3、建立帐户并授权slave:

   #/usr/local/mysql/bin/mysql -uroot -p123456 –h127.0.0.1

                  

   mysql>GRANT FILE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';

   mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'slave'@'%' IDENTIFIED BY '123456';

 

   #一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.137.20，加强安全。

 

   刷新权限

   mysql> FLUSH PRIVILEGES;

 

   查看mysql现在有哪些用户

   mysql>select user,host from mysql.user;

 

4、登录主服务器的mysql，查询master的状态

   mysql> show master status;

         +------------------+----------+--------------+------------------+

    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

    +------------------+----------+--------------+------------------+

    | mysql-bin.000010 |      106 | db1,db2,db3  | mysql            |

    +------------------+----------+--------------+------------------+

    Master 重启后会修改mysql-bin（序号加1）

 

 

 

 

二、  MySQL从服务器Slave 配置

1、修改从服务器配置:

   #vi /etc/my.cnf

 

         replicate-do-db=db1        #跟主服务器Master中的内容相同

         replicate-do-db=db2

         replicate-do-db=db3

         replicate-ignore-db=mysql    #可以不写

        

        

         server-id=2                 #每个Slave的server-id必须不同

         #master-host=192.168.137.11  #如果Slave启动失败，注释掉该内容，从Slave的Mysql手动添加

         #master-user=slave

         #master-password=123456

         #master-port=3306

         #master-connect-retry=60

         #log-slave-updates

         skip-slave-start  #防止复制随着mysql启动而自动启动。即slave端的mysql服务重启后需手动来启动主从复制（slave start），最好加上，slave端数据库服务重启后手动启动slave比较安全

 

2、重启MySQL

         Service mysql restart

 

3、登录mysql并停止slave服务

         mysql -uroot –p123456 –h127.0.0.1

         mysql> stop slave;

 

         设置与master服务器相关的配置参数

         mysql>change master to master_host='192.168.137.11', master_user='slave', master_password='123456',MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=106;

        

注意：Master重启后slave 要修改MASTER_LOG_FILE，106无单引号。

 

启动从服务器复制功能

      Mysql>start slave;   

 

         ERROR 1201 (HY000):Could not initialize master info structure的问题

         解决方案是：运行命令 stop slave;

成功执行后继续运行 reset slave;

查看server_id:

Mysql> SHOW VARIABLES LIKE 'server_id';

 

4、检查从服务器复制功能状态

         mysql> show slave status\G

以下两个参数必须为YES：

      Slave_IO_Running: Yes

      Slave_SQL_Running: Yes

 

返回如下：

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 10.1.176.158

                  Master_User: backup

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000010

          Read_Master_Log_Pos: 106

               Relay_Log_File: bogon-relay-bin.000002

                Relay_Log_Pos: 251

        Relay_Master_Log_File: mysql-bin.000010

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: db1,db2,db3

          Replicate_Ignore_DB: mysql

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 106

              Relay_Log_Space: 406

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

 

三、  数据库中间件Mycat配置

跳过第1部分，看第二部分，配置Mycat的schema.xml文件

1、不使用Mycat托管MySQL主从服务器，简单使用如下配置

    <dataNode name="dn1" dataHost="localhost1" database="db1" />

    <dataNode name="dn2" dataHost="localhost1" database="db2" />

    <dataNode name="dn3" dataHost="localhost1" database="db3" />  

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"

       writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

       <heartbeat>select user()</heartbeat>

       <writeHost host="hostM" url="192.168.11:3306" user="root"

           password="123456">

       </writeHost>

    </dataHost>

 

2、Mycat 1.4 支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：

    <dataNode name="dn1" dataHost="localhost1" database="db1" />

    <dataNode name="dn2" dataHost="localhost1" database="db2" />

    <dataNode name="dn3" dataHost="localhost1" database="db3" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

       writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">

       <heartbeat>show slave status</heartbeat>

       <writeHost host="hostM" url"10.1.176.158:3306" user="root"

           password="123456">

           <readHost host="hostS" url="10.1.176.78:3306" user="root"

           password="123456" />

       </writeHost>s

    </dataHost>

 

(1)      设置 balance="1"与writeType="0"

Balance参数设置：

1. balance=“0”, 所有读操作都发送到当前可用的writeHost上。

2. balance=“1”，所有读操作都随机的发送到readHost。

3. balance=“2”，所有读操作都随机的在writeHost、readhost上分发

WriteType参数设置：

1. writeType=“0”, 所有写操作都发送到可用的writeHost上。

2. writeType=“1”，所有写操作都随机的发送到readHost。

3. writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。

 “readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”

(2)      设置 switchType="2" 与slaveThreshold="100"

“Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“

四、  测试读写分离与主从复制测试

 

主从复制测试：

通过mycat 创建表并插入字段

mysql>  explain create table company(id int not null primary key,name varchar(100));

+-----------+---------------------------------------------------------------------+

| DATA_NODE | SQL                                                                 |

+-----------+---------------------------------------------------------------------+

| dn1       | create table company(id int not null primary key,name varchar(100)) |

| dn2       | create table company(id int not null primary key,name varchar(100)) |

| dn3       | create table company(id int not null primary key,name varchar(100)) |

+-----------+---------------------------------------------------------------------+

3 rows in set (0.32 sec)

 

mysql> create table company(id int not null primary key,name varchar(100));

Query OK, 0 rows affected (0.26 sec)

 

mysql> insert into company(id,name) values(1,'alibaba');

Query OK, 3 rows affected (0.15 sec)

 

在Master和Slave端查看数据是否同步。

读写分离测试：

设置mycat的log4j.xml文件，日志模式为debug，通过select查询观察日志中是否通过已配置的的读节点执行。

还可以在Master和Slave中插入不一样的数据，看mycat读取到的数据。