MYSQL主从复制和读写分离

Mysql复制特点（异步复制）：

主节点中一个用户请求一个写操作时，主节点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成再响应用户。主节点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主服务，可以使用很多软件来检查主从数据是否一致。

复制原理：

Mysql中有一种日志叫做binlog（二进制日志）。这个日志会记录下所有修改了数据库的SQL语句（insert,update,delete,ALTER TABLE,grant等等）。 
主从复制的原理其实就是把主服务器上的Ｂinlog复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

复制过程：

1. 主节点必须启用二进制日志，记录任何修改数据库数据的事件。

2. 主节点：binlog线程，记录所有改变数据库数据的操作，放进master的binlog中

3. 从节点：I/O线程，在使用start slave后，拉取master上的binlog放到自己的relaylog中。

4. 从节点：sql线程，执行relaylog中的语句。

主从复制配置过程：

主节点：

1. 启用二进制日志。

2. 为当前节点设置一个全局唯一的server_id。

3. 创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

1. 启动中继日志。

2. 为当前节点设置一个全局唯一的server_id。

3. 使用有复制权限的用户账号连接至主节点，并启动复制线程。

配置演示：

1：编辑主节点配置文件 

      # vim /etc/my.cnf

2：启用二进制日志文件

添加以下内容：

•   log-bin = mysql-bin

•   server-id    = 1

•   innodb_file_per_table =ON

• # skip_name_resolve=ON

• 

• 
  

3：启动mysql服务，并连接mysql 

# mysql -uroot -p

MySQL [(none)]> use mysql;

4：查看日志信息 

1:)查看二进制日志是否开启 

MySQL [mysql]>show global variables like '%log%';

2:)查看主节点二进制日志列表 

MySQL [mysql]> show master logs;

 

3:)查看主节点的server id 
MySQL [mysql]> show variables like "%server%";

 

5：在主节点上创建有复制权限的用户。REPLIACTION SLAVE ，REOPLIATION CLIENT 

MySQL [mysql]>grant replication slave,replication client on *.* to 'wang'@'192.168.6.155' identified by 'redhat';

6：刷新 

MySQL [mysql]> flush privileges;

 
主服务器配置完成。 

注意：确保主服务器的iptables没有阻断3306的访问端口（firewalld）。

7：编辑从节点配置文件 

[root@RS2 var]# vim /etc/my.cnf

8：开启中继日志

• 添加：relay-log=relay-log

• 添加：log-bin=mysql-bin

• 添加：server-id=2

  
  

   

• 
  9:启动从节点mysql服务，并登陆mysql 

• 
  

• 
  10:查看从节点日志信息 

• 
  1:)查看中继日志是否开启 

• MySQL [mysql]> show global variables like '%log%';
  

• 
  

• 
  2:)产看server 相关信息 

      MySQL [mysql]> show global variables like '%server%';

• 
  

•  

• 
  11：在从节点配置访问主节点的参数信息 

• 
  添加主节点主机，访问主节点的用户名及密码，主节点二进制文件信息。 

      注意：主节点的二进制文件一定要是二进制列表中的最后一个二进制文件。 

• 
  

MySQL[mysql]>CHANGE MASTER TO MASTER_HOST='192.168.6.147',MASTER_USER='wang',MASTER_PASSWORD='redhat', MASTER_LOG_FILE='mysql-bin.000012',MASTER_LOG_POS=214;1

12：查看从节点的状态信息 

因为没有启动 从节点的复制线程，IO线程 和 SQL 线程都为NO. 
MySQL [mysql]> show slave status\G;

13：启动复制线程 

START SLAVE 可以指定线程类型：IO_THREAD ,SQL_THREAD, 如果不指定，则两个都启动。 

MySQL [mysql]> start slave;

 

14:再次查看从节点状态信息 

至此，mysql的主—-从复制配置完成。 

15：测试 

1：）在主节点创建数据库，并查看主节点二进制日志信息 

 

2：）在从节点查找二进制日志信息，并查看mydb数据库是否复制成功 

 

读写分离

proxysql实现mysql读写分离,proxysql类似haproxy七层代理路由功能且支持MySQL 协议的的数据库代理，用户请求发向proxysql,如果是写请求发往主节点;读请求发下从节点组中;以此实现读写分离。双主或多主模型是无须实现读写分离，仅需要负载均衡：haproxy, nginx, lvs等。

主从复制架构中应注意的问题：

从节点要设置某些限定使得它不能进行写操作,才能保证复制当中的数据一致。

1：限制从服务器为只读 

在从服务器上设置： 
read_only = ON,但是此限制对拥有SUPER权限 的用户均无效。 
阻止所有用户： 
mysq>FLUSH TABLES WITH READ LOCK;

2:如何保证主从复制时的事物安全？ 

1：在主节点设置参数 

sync_binlog=1： Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃时，就可能导致bin-log最后的语句丢失。可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。 

如果用到innode 存储引擎: innodb_flush_logs_at_trx_commit=ON（刷写日志：在事务提交时，要将内存中跟事务相关的数据立即刷写到事务日志中去。） 

innodb_support_xa=ON (分布式事务：基于它来做两段式提交功能) 

sync_master_info=1：每次给从节点dump一些事件信息之后，主节点的master info 信息会立即同步到磁盘上。让从服务器中的 master_info 及时更新。

2：在每个slave节点 

skip_slave_start =ON (跳过自动启动，使用手动启动。) 

relay_log也会在内从中先缓存，然后在同步到relay_log中去，可以使用下面参数使其立即同步。 

sync_relay_log =1 ，默认为10000，即每10000次sync_relay_log事件会刷新到磁盘。为0则表示不刷新，交由OS的cache控制。 

sync_relay_log_info=1每间隔多少事务刷新relay-log.info，如果是table（innodb）设置无效，每个事务都会更新

注： 在从节点中 master.info是记录在主节点复制位置的文件。 

relylog_info: 本地将来至于主节点的哪一个二进制文件中position并且保存文本地哪一个中继日志中的哪一个postion. 从节点启动时也需要根据relay-log.info定位本地relay-log.

主从复制分析

问题1：master的写操作，slaves被动的进行一样的操作，保持数据一致性，那么slave是否可以主动的进行写操作？

假设slave可以主动的进行写操作，slave又无法通知master，这样就导致了master和slave数据不一致了。因此slave不应该进行写操作，至少是slave上涉及到复制的数据库不可以写。实际上，这里已经揭示了读写分离的概念。

问题2：主从复制中，可以有N个slave,可是这些slave又不能进行写操作，要他们干嘛？

可以实现数据备份。

类似于高可用的功能，一旦master挂了，可以让slave顶上去，同时slave提升为master。

异地容灾，比如master在北京，地震挂了，那么在上海的slave还可以继续。

主要用于实现scale out,分担负载,可以将读的任务分散到slaves上。

【很可能的情况是，一个系统的读操作远远多于写操作，因此写操作发向master，读操作发向slaves进行操作】

问题3：主从复制中有master,slave1,slave2,...等等这么多MYSQL数据库，那比如一个JAVA WEB应用到底应该连接哪个数据库?

当 然，我们在应用程序中可以这样，insert/delete/update这些更新数据库的操作，用connection(for master)进行操作，select用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select，例如简单的轮循算法。

这样的话，相当于应用程序完成了SQL语句的路由，而且与MYSQL的主从复制架构非常关联，一旦master挂了，某些slave挂了，那么应用程序就要修改了。能不能让应用程序与MYSQL的主从复制架构没有什么太多关系呢？可以看下面的图：

 

找一个组件，application program只需要与它打交道，用它来完成MYSQL的代理，实现SQL语句的路由。

mysql proxy并不负责，怎么从众多的slaves挑一个？可以交给另一个组件(比如haproxy)来完成。

这就是所谓的MYSQL READ WRITE SPLITE，MYSQL的读写分离。

问题4：如果mysql proxy , direct , master他们中的某些挂了怎么办？

总统一般都会弄个副总统，以防不测。同样的，可以给这些关键的节点来个备份。

问题5：当master的二进制日志每产生一个事件，都需要发往slave，如果我们有N个slave,那是发N次，还是只发一次？

如果只发一次，发给了slave-1，那slave-2,slave-3,...它们怎么办？

显 然，应该发N次。实际上，在MYSQL master内部，维护N个线程，每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作，还的维护N个线程，负担会很重。可 以这样，slave-1是master的从，slave-1又是slave-2,slave-3,...的主，同时slave-1不再负责select。 slave-1将master的复制线程的负担，转移到自己的身上。这就是所谓的多级复制的概念。

问题6：当一个select发往mysql proxy，可能这次由slave-2响应，下次由slave-3响应，这样的话，就无法利用查询缓存了。

应该找一个共享式的缓存，比如memcache来解决。将slave-2,slave-3,...这些查询的结果都缓存至mamcache中。

问题7：随着应用的日益增长，读操作很多，我们可以扩展slave，但是如果master满足不了写操作了，怎么办呢？

scale on ?更好的服务器？ 没有最好的，只有更好的，太贵了。。。

scale out ? 主从复制架构已经满足不了。

可以分库【垂直拆分】，分表【水平拆分】。

转载于:https://blog.51cto.com/13689359/2159181