史上最全mysql的复制和读写分离

13544870125

　　mysql的复制和mysql的读写分离从来就不是一个简单的话题，今天笔者就详细来记录一下我学习的mysql。
　　mysql日至类型有：二进制日志，事务日志，错误日志，一般查询日志，中继日志，慢查询日志。
　　二进制日志一般放在数据目录，一般文件名叫mysql-bin.xxxx，当数据大到我们设定的最大大小的时候就会滚动，还有类似flush  ，logs，服务器重启都会引起滚动，在mysql下删除日志一般我们不实用rm来删除，而是在mysql命令下使用PURGE来清除
　　二进制记录的格式有基于语句的，有基于行的，有混合的。除了mysql-bin-xxx外还有一些mysql-bin-index的文件，这是msyql二进制日志文件的缩影
　　文件。想查看当前mysql使用的二进制文件我们在mysql>下使用SHOW MASTER STATUS;
　　SHOW查看mysql二进制日志中的相关内容，mysql> SHOW BINLOG EVENTS IN "file";
　　查看当前mysql上存在二进制日志文件的列表。mysql> SHOW BINARY LOGS;
　　一个日志一般会记录：产生时间(timestamp);  位置(position） 时间本身(offset)，
　　二进制日志记录日志并不完善，所以如果不备份数据库，仅仅靠二进制文件恢复数据可能不完善。所以最好的办法就是把数据库备份一边
　　由于主服务器允许并行执行，例如拥有多颗CPU就能同时执行多个写操作，但是向二进制日志中写入的话只允许一个一个写入，所以向从服务器传输的时候也只能一个一个传输，所以从服务器会比主管服务器慢，甚至有可能慢很多。
　　如果MYSQL的复制是同步的话，用户就需要等待主服务器上的二进制文件完全传输到从服务器的时候才能执行下一个操作，而这时不科学的，所以MYSQL上的复制都是异步的，半同步就是只要数据发送到从服务器的tcp/ip的缓存中就OK了，但是对于MYSQL来说这个不实用，因为mysql还需要将他们保存到中继日志中去，一个一个去读的。所以半同步和异步是差不多的，所以mysql一般使用异步。mysql和drbd最大的不同就是mysql支持一主多从。
　　看下图一，就是mysql的基本原理图

　　在一主多从的模式下，只要有一个从服务器返回以及保存成功的信息，我们就不再去管其他的从服务器，这种方式我们叫做半同步。
　　mysql在5.5之前是不支持半同步的，
　　从服务器的所有数据都是从主服务器上复制过来的，因为从服务器是备份用的，所以我们需要保存和主服务器一模一样的数据，如果从服务器的数据和主服务器的数据不一样，我们就失去了从服务器的意义，所以我们不容许从服务器写操作和向主服务器传送东西。
　　热备份很复杂，而且容易产生数据不一致的情况，有了从服务器我们可以在从服务器上停止Mysql的服务，然后进行冷备份，这样安全多了。
　　从服务器不需要二进制日志，没什么作用，还拖延从服务器的速度
　　mysql还有多级辅助
　　为了让我们的读写速度加快，我们可以给他们分配任务，只在主服务器上写，只在从服务器上读。但是那么多从服务器呢，我们在在那个上边读呢，简单，负载均衡器。在前边找一个MYsql路由，如果我们想发起Mysql读写的时候，我i们需要联系这个Mysql代理，它可以判断我们执行是什么操作，读的话就联系从服务器，写的话就联系主服务器，这就叫做Mysql的读写分离。如果我们的读操作非常多，那样我们怎么能尽可能的让我们的读速度变快呢，我们怎么才能知道我的读操作是那个从服务器响应我们呢？简单，添加一个LVS就行了。在前端添加一个directory就行了

　　如果directory坏掉了怎么办？MYSQL代理坏掉怎么办？好办，都做成高可用，如下图三 为了加快速度，我们可以加上个共享缓存，无论那个服务器的查询都可以暂时缓存到memcnche，以后无论那个服务器查询先到 memcnche查询。
　　为了增加我们的读写速度我们添加了很多的从服务器，但是这么多从服务器难道每次我们都要从主服务器启动一个线程把日志从二进制日志发送到从服务器的中继日志么?这就会产生大量的IO，那样速度还会快么？基于这样的情况，就产生了多级复制的办法
　　多级复制，就是从从服务器中选出一个专门负责中转主服务器的日志发总给别的从服务器。
　　PHP不需要和Mysql发生关系，发生关系的是PHP语言开发的程序。
　　我们可以让开发语言，我们在配置时候自己来解决读写的程序，只不过开发程序比较麻烦。
　　如果既不想让前端程序变得复杂，又不想提供读写分离，这就是主主模式，但是我们并不建议使用。
　　如图四，黑线代表我们开始的时候的顺序，产生的数据，我们保存在二进制日志里，二进制日志通过传送到另一个服务器中的中继日志中，中继日志在通过本地服务器读出保存在数据库，并储存在二进制日志中，然后开始红线的部分，和这样一样的进程，复制到第一个进程中。但是这样大家发现没有，这样会陷入一个循环中，尴尬，那我们需要做的就是给进程添加一个编号，这样我们不再本地运行相同ID的server，这样就不会再二进制日志中发送了。
　　双主模型，其实无法实现平均负载写操作的，因为两个节点上的数据必须一模一样，不然会出错的。双主模型还容易产生爽肤数据不一致导致崩溃的情况。
　　对于MYSQL来说一个主服务器可以带多个从服务器，但是一个从不能带多个主，如果各个主服务器都写入不同的数据，从服务器完全复制过来会到时数据不一致或者冲突之类的。
　　配置MySQL复制基本步骤：
　　简单的下载解压就不说了，我们直接从配置开始把。
　　一，先配置主服务器
　　复制配置文件到/etc/my.cof
　　复制脚本到/etc/rc.d/in.d/mysqld
　　chkconfig -add mysqld
　　vim /etc/my.cnf
　　我们所有的和复制有关的配置都在mysqld这里边
　　我们需要把log-bin=master-bin这一项启动起来
　　2、选择一个惟一server-id =1
　　server-id = {0-2^32}
　　innodb_file_per_table = 1
　　datedir = /mydata/data
　　然后service mysqld start
　　然后把该文件配置到从服务器上去
　　3、创建具有复制权限的用户
　　REPLICATION SLAVE
　　REPLICATION CLIENT
　　二、配置从服务器
　　1、启用中继日志

　　添加relay-log =>
　　添加relay-log-index =>　　2、选择一个惟一的server-id = (一定不能等于主服务器)
　　server-id = {0-2^32}
　　3、连接至主服务器，并开始复制数据；
　　mysql> CHANGER MASTER TO MASTER_HOST='',MASTER_PORT='',MASTER_LOG_FILE='',MASTER_LOG_FIEL_POS='',MASTER_USER='',MASTER_PASSWORD='';
　　mysql>SHOW SLAVE STATUS\G(查看当前状态
　　)
　　mysql> START SLAVE;（启动服务）
　　mysql> START SLAVE IO_Thread; （YES为正常）
　　mysql> START SLAVE SQL_Thread; （YES为正常）
　　复制线程：
　　master: dump
　　slave: IO_Thread, SQL_Thread
　　怎么把从服务器设置成只读
　　在配置文件中添加
　　read-only = YES 重启服务有效
　　但是对于拥有管理员身份的人无效
　　sync-binlog = ON
　　在主服务器上设定，用于事务安全；
　　所有的从服务器上的信息都会被保存在/mydata/daya/node2.magedu.com.err下边
　　如何让mysql实现半同步呢，这需要一个插件
　　这个插件在 /usr/local/mysql/lib/plugin里边有两个文件 semisync_slave.so 和semisync_maste.so。一主一从
　　设置半同步步骤：
　　在Master和Slave的mysql命令行运行如下代码：
　　# On Master
　　mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
　　mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
　　mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
　　设置是否启用和超时时间
　　# On Slave
　　mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
　　mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
　　mysql> STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
　　在Master和Slave的my.cnf中编辑：
　　# On Master
　　[mysqld]
　　rpl_semi_sync_master_enabled=1
　　rpl_semi_sync_master_timeout=1000 # 1 second
　　# On Slave
　　[mysqld]
　　rpl_semi_sync_slave_enabled=1
　　# 也可通过设置全局变量的方式来设置,如下：
　　set global rpl_semi_sync_master_enabled=1
　　# 取消加载插件
　　mysql> UNINSTALL PLUGIN rpl_semi_sync_master;
　　设置主-主复制：
　　1、在两台服务器上各自建立一个具有复制权限的用户；
　　2、修改配置文件：
　　# 主服务器上
　　[mysqld]
　　server-id = 10
　　log-bin = mysql-bin

　　relay-log =>
　　relay-log-index =>　　auto-increment-increment = 2
　　auto-increment-offset = 1
　　# 从服务器上
　　[mysqld]
　　server-id = 20
　　log-bin = mysql-bin

　　relay-log =>
　　relay-log-index =>　　auto-increment-increment = 2
　　auto-increment-offset = 2
　　3、如果此时两台服务器均为新建立，且无其它写入操作，各服务器只需记录当前自己二进制日志文件及事件位置，以之作为另外的服务器复制起始位置即可
　　server1|mysql> SHOW MASTER STATUS\G
　　************************** 1. row ***************************
　　File: mysql-bin.000001
　　Position: 710
　　Binlog_Do_DB:
　　Binlog_Ignore_DB:
　　1 row in set (0.00 sec)
　　server2|mysql> SHOW MASTER STATUS\G
　　mysql> SHOW MASTER STATUS\G
　　*************************** 1. row ***************************
　　File: mysql-bin.000003
　　Position: 811
　　Binlog_Do_DB:
　　Binlog_Ignore_DB:
　　1 row in set (0.00 sec)
　　4、各服务器接下来指定对另一台服务器为自己的主服务器即可：
　　server1|mysql> CHANGE MASTER TO ...,MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=811
　　server2|mysql> CHANGE MASTER TO ...,MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=710