存档

文章标签 ‘mysql’

Mycat 读写分离、主从切换、分库分表的操作记录(转)

2022年1月1日 没有评论

在系统开发中,数据库是非常重要的一个点。除了程序的本身的优化,如:SQL语句优化、代码优化,数据库的处理本身优化也是非常重要的。主从、热备、分表分库等都是系统发展迟早会遇到的技术问题问题。Mycat是一个广受好评的数据库中间件,已经在很多产品上进行使用了。下面就针对Mycat的基础知识和应用做一总结性梳理,这些内容有的是从网上收集的,有的是自己做的测试验证信息,如有错误,烦请谅解和指出!

一、MyCat简单介绍
MyCat是一个开源的分布式数据库系统,是一个实现了MySQL协议的服务器,前端用户可以把它看作是一个数据库代理(类似于Mysql Proxy),用MySQL客户端工具和命令行访问,而其后端可以用MySQL原生协议与多个MySQL服务器通信,也可以用JDBC协议与大多数主流数据库服务器通信,其核心功能是分表分库,即将一个大表水平分割为N个小表,存储在后端MySQL服务器里或者其他数据库里。

MyCat发展到目前的版本,已经不是一个单纯的MySQL代理了,它的后端可以支持MySQL、SQL Server、Oracle、DB2、PostgreSQL等主流数据库,也支持MongoDB这种新型NoSQL方式的存储,未来还会支持更多类型的存储。而在最终用户看来,无论是那种存储方式,在MyCat里,都是一个传统的数据库表,支持标准的SQL语句进行数据的操作,这样一来,对前端业务系统来说,可以大幅降低开发难度,提升开发速度。

Mycat可以简单概括为
-  一个彻底开源的,面向企业应用开发的大数据库集群
-  支持事务、ACID、可以替代MySQL的加强版数据库
-  一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群
-  一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
-  结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
-  一个新颖的数据库中间件产品

Mycat关键特性
-  支持SQL92标准
-  遵守Mysql原生协议,跨语言,跨平台,跨数据库的通用中间件代理
-  基于心跳的自动故障切换,支持读写分离,支持MySQL主从,以及galera cluster集群
-  支持Galera for MySQL集群,Percona Cluster或者MariaDB cluster
-  基于Nio实现,有效管理线程,高并发问题
-  支持数据的多片自动路由与聚合,支持sum,count,max等常用的聚合函数,支持跨库分页
-  支持单库内部任意join,支持跨库2表join,甚至基于caltlet的多表join
-  支持通过全局表,ER关系的分片策略,实现了高效的多表join查询
-  支持多租户方案
-  支持分布式事务(弱xa)
-  支持全局序列号,解决分布式下的主键生成问题
-  分片规则丰富,插件化开发,易于扩展
-  强大的web,命令行监控
-  支持前端作为mysq通用代理,后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉
-  支持密码加密
-  支持服务降级
-  支持IP白名单
-  支持SQL黑名单、sql注入攻击拦截
-  支持分表(1.6)
-  集群基于ZooKeeper管理,在线升级,扩容,智能优化,大数据处理(2.0开发版)

二、为什么要用MyCat
这里要先搞清楚Mycat和MySQL的区别(Mycat的核心作用)。我们可以把上层看作是对下层的抽象,例如操作系统是对各类计算机硬件的抽象。那么我们什么时候需要抽象?假如只有一种硬件的时候,我们需要开发一个操作系统吗?再比如一个项目只需要一个人完成的时候不需要leader,但是当需要几十人完成时,就应该有一个管理者,发挥沟通协调等作用,而这个管理者对于他的上层来说就是对项目组的抽象。

同样的,当我们的应用只需要一台数据库服务器的时候我们并不需要Mycat,而如果你需要分库甚至分表,这时候应用要面对很多个数据库的时候,这个时候就需要对数据库层做一个抽象,来管理这些数据库,而最上面的应用只需要面对一个数据库层的抽象或者说数据库中间件就好了,这就是Mycat的核心作用。所以可以这样理解:数据库是对底层存储文件的抽象,而Mycat是对数据库的抽象。

三、Mycat工作原理
Mycat的原理并不复杂,复杂的是代码。Mycat的原理中最重要的一个动词是“拦截”,它拦截了用户发送过来的SQL语句,首先对SQL语句做了一些特定的分析:如分
片分析、路由分析、读写分离分析、缓存分析等,然后将此SQL发往后端的真实数据库,并将返回的结果做适当的处理,最终再返回给用户。

上述图片里,Orders表被分为三个分片datanode(简称dn),这三个分片是分布在两台MySQL Server上(DataHost),即datanode=database@datahost方式,因此你可以用一台到N台服务器来分片,分片规则为(sharding rule)典型的字符串枚举分片规则,一个规则的定义是分片字段(sharding column)+分片函数(rule function),这里的分片字段为prov而分片函数为字符串枚举方式。

当Mycat收到一个SQL时,会先解析这个SQL,查找涉及到的表,然后看此表的定义,如果有分片规则,则获取到SQL里分片字段的值,并匹配分片函数,得到该SQL对应的分片列表,然后将SQL发往这些分片去执行,最后收集和处理所有分片返回的结果数据,并输出到客户端。以select * from Orders where prov=?语句为例,查到prov=wuhan,按照分片函数,wuhan返回dn1,于是SQL就发给了MySQL1,去取DB1上的查询结果,并返回给用户。

如果上述SQL改为select * from Orders where prov in (‘wuhan’,‘beijing’),那么,SQL就会发给MySQL1与MySQL2去执行,然后结果集合并后输出给用户。但通常业务中我们的SQL会有Order By 以及Limit翻页语法,此时就涉及到结果集在Mycat端的二次处理,这部分的代码也比较复杂,而最复杂的则属两个表的Jion问题,为此,Mycat提出了创新性的ER分片、全局表、HBT(Human Brain Tech)人工智能的Catlet、以及结合Storm/Spark引擎等十八般武艺的解决办法,从而成为目前业界最强大的方案,这就是开源的力量!

四、Mycat应用场景
Mycat发展到现在,适用的场景已经很丰富,而且不断有新用户给出新的创新性的方案,以下是几个典型的应用场景:
-   单纯的读写分离,此时配置最为简单,支持读写分离,主从切换;
-   分表分库,对于超过1000万的表进行分片,最大支持1000亿的单表分片;
-   多租户应用,每个应用一个库,但应用程序只连接Mycat,从而不改造程序本身,实现多租户化;
-   报表系统,借助于Mycat的分表能力,处理大规模报表的统计;
-   替代Hbase,分析大数据;
-   作为海量数据实时查询的一种简单有效方案,比如100亿条频繁查询的记录需要在3秒内查询出来结果,除了基于主键的查询,还可能存在范围查询或其他属性查询,此时Mycat可能是最简单有效的选择;
-   Mycat长期路线图;
-   强化分布式数据库中间件的方面的功能,使之具备丰富的插件、强大的数据库智能优化功能、全面的系统监控能力、以及方便的数据运维工具,实现在线数据扩容、迁移等高级功能;
-   进一步挺进大数据计算领域,深度结合Spark Stream和Storm等分布式实时流引擎,能够完成快速的巨表关联、排序、分组聚合等 OLAP方向的能力,并集成一些热门常用的实时分析算法,让工程师以及DBA们更容易用Mycat实现一些高级数据分析处理功能。
-   不断强化Mycat开源社区的技术水平,吸引更多的IT技术专家,使得Mycat社区成为中国的Apache,并将Mycat推到Apache
基金会,成为国内顶尖开源项目,最终能够让一部分志愿者成为专职的Mycat开发者,荣耀跟实力一起提升。

五、Mycat不适合的应用场景
-  设计使用Mycat时有非分片字段查询,请慎重使用Mycat,可以考虑放弃!
-  设计使用Mycat时有分页排序,请慎重使用Mycat,可以考虑放弃!
-  设计使用Mycat时如果要进行表JOIN操作,要确保两个表的关联字段具有相同的数据分布,否则请慎重使用Mycat,可以考虑放弃!
-  设计使用Mycat时如果有分布式事务,得先看是否得保证事务得强一致性,否则请慎重使用Mycat,可以考虑放弃!

需要注意:  在生产环境中, Mycat节点最好使用双节点, 即双机热备环境, 防止Mycat这一层出现单点故障. 可以使用的高可用集群方式有:  Keepalived+Mycat+MysqlKeepalived+LVS+Mycat+MysqlKeepalived+Haproxy+Mycat+Mysql

六、利用MyCAT实现MySQL的读写分离、主从切换、分库分表的操作记录

Mycat实现Mysql主从复制,其中写操作在master主节点上执行,包括insert,delete,update 语句操作;读操作在slave节点上执行,只有select语句操作,其他操作均由主master的二进制文件决定;MyCat支持双主多从,多主多从情况需要配置多个writeHost兄弟节点,多个readHost节点即可!

Mycat的架构其实很好理解,Mycat是数据库代理中间件,Mycat后面就是物理数据库。和Web服务器的Nginx类似。对于使用者来说,访问的都是Mycat,不会接触到后端的数据库。如下案例是做一个主从、读写分离,简单分库分表的示例。结构如下图:

服务器信息如下(实验环境,关闭机器的iptables防火墙和selinux):

服务器主机名       ip                  说明
Mycat-node       192.168.10.210      mycat服务器,连接数据库时,连接此服务器
Mysql-node1      192.168.10.205      物理数据库1,真正存储数据的数据库,这里为Master主数据库
Mysql-node2      192.168.10.206      物理数据库2,真正存储数据的数据库,这里为Slave主数据库

三台机器分布修改主机名,并做hosts绑定
# vim /etc/hosts
192.168.10.205 Mysql-node1
192.168.10.206 Mysql-node1
192.168.10.210 Mycat-node

为方便做实验,关闭三台机器的iptables防火墙和selinux

Mycat作为主数据库中间件,肯定是与代码弱关联的,所以代码是不用修改的,使用Mycat后,连接数据库是不变的,默认端口是8066。连接方式和普通数据库一样,比如:jdbc:mysql://192.168.10.210:8066/

1)Mysql安装及主从复制部署(Mysql-node1和Mysql-node2两台机器上操作)

a)安装Mysql,安装过程要在两个Mysql节点机器上都要操作。
安装过程参考:http://www.cnblogs.com/kevingrace/p/6109679.html
这里省去安装过程~~

b)Mysql主从复制(两个节点的mysql登录用户名和密码都是root/123456)
参考:http://www.cnblogs.com/kevingrace/p/6256603.html

====================================================
首先是Mysql-node1主节点配置操作
[root@Mysql-node1 ~]# cp /usr/local/mysql/my.cnf /usr/local/mysql/my.cnf.bak
[root@Mysql-node1 ~]# vim /usr/local/mysql/my.cnf        #在[mysqld]区域添加下面内容
......
[mysqld]
server-id=1
log-bin=mysql-bin
#binlog-do-db=kevin         #需要同步的数据库。如果是多个同步库,就以此格式另写几行即可。如果不指明对某个具体库同步,就去掉此行,表示同步所有库(除了ignore忽略的库)
binlog-ignore-db = mysql,information_schema
sync_binlog = 1
binlog_checksum = none
binlog_format = mixed   

重启mysql服务
[root@Mysql-node1 ~]# /etc/init.d/mysql restart

登录mysql,授予slave从机复制权限
mysql> grant replication slave,replication client on *.* to slave@'192.168.10.206' identified by "slave@123";
Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)

授权之后,要保证192.168.10.206这台slave节点机器能使用上面的权限信息登录到本机的mysql

将数据库锁住,仅仅允许读,以保证数据一致性;
mysql> FLUSH TABLES WITH READ LOCK;                 #注意,锁定后,如果自己同步对方数据,同步前一定要记得先解锁!
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.05 sec)

查看主节点的master复制信息
mysql> show master status;
+------------------+----------+--------------+--------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------+-------------------+
| mysql-bin.000003 |     1349 |              | mysql,information_schema |                   |
+------------------+----------+--------------+--------------------------+-------------------+
1 row in set (0.00 sec)

====================================================
接着是slave从节点操作
[root@Mysql-node2 ~]# cp /usr/local/mysql/my.cnf /usr/local/mysql/my.cnf.bak
[root@Mysql-node2 ~]# vim /usr/local/mysql/my.cnf
.......
[mysqld]
.......
server-id=2
log-bin=mysql-bin
#replicate-do-db=kevin    #需要同步的数据库名。如果不指明同步哪些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。
replicate-ignore-db=mysql
slave-skip-errors = all 

重启mysql服务
[root@Mysql-node2 ~]# /etc/init.d/mysql restart

登录slave节点的mysql,进行主从同步设置
mysql> stop slave;
Query OK, 0 rows affected (0.09 sec)

mysql> change  master to master_host='192.168.10.205',master_user='slave',master_password='slave@123',master_log_file='mysql-bin.000003',master_log_pos=1349;
Query OK, 0 rows affected, 2 warnings (0.21 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.205
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1349
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          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: 1349
              Relay_Log_Space: 456
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 747977ea-8fba-11e8-86c0-525400b19c93
             Master_Info_File: /data/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> 

=========================================================
通过上面的信息,可知主从复制环境已经OK(Slave_IO_Running和Slave_SQL_Running状态均为YES),下面验证下主从复制是否正常?

在Mysql-node1主节点上操作
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.06 sec)

mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.34 sec)

在Mysql-node2从节点上查看(保证从节点上查看slave状态时,Slave_IO_Running和Slave_SQL_Running状态均为YES,这样就能保证主从复制在进行中)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kevin              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use kevin;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

接着在Mysql-node1主节点插入数据
mysql> insert into kevin.haha values(1,"wangshibo"),(2,"linan"),(3,"zhangminmin");
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

然后再在MYsql-node2从节点查看,如下发现已经同步过来了!
mysql> use kevin;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
+----+-------------+
3 rows in set (0.00 sec)

由此可见,Mysql的主从复制已经实现!

2)Mycat中间件安装、配置和测试(Mycat-node节点机器上操作)
2.1)Mycat安装

建议下载1.6-RELEASE 版本,毕竟是比较稳定的版本。
下载官网地址:http://dl.mycat.io/

[root@Mycat-node ~]# cd /usr/local/src/
[root@Mycat-node src]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@Mycat-node src]# tar -zvxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
[root@Mycat-node src]# mv mycat /data/
[root@Mycat-node src]# ls /data/mycat/
bin  catlet  conf  lib  logs  version.txt

mycat安装完成后,目录如下:
bin	    mycat命令,启动、重启、停止等
catlet	catlet为Mycat的一个扩展功能
conf	Mycat 配置信息,重点关注
lib	    Mycat引用的jar包,Mycat是java开发的
logs	日志文件,包括Mycat启动的日志和运行的日志。

Mycat的配置文件都在conf目录里面,这里介绍几个常用的文件:
server.xml	 Mycat的配置文件,设置账号、参数等
schema.xml	 Mycat对应的物理数据库和数据库表的配置
rule.xml	 Mycat分片(分库分表)规则

[root@Mycat-node src]# cd /data/mycat/conf
[root@Mycat-node conf]# ll server.xml
-rwxrwxrwx. 1 root root 3740 Jul 25 12:19 server.xml
[root@Mycat-node conf]# ll schema.xml
-rwxrwxrwx. 1 root root 4667 Jul 31 02:54 schema.xml

2.2)Mycat相关配置
server.xml文件其实跟读写分离策略关系不大,但是需要用此文件来配置连接MyCat的用户及权限等,因此在这里简单说明。

[root@Mycat-node conf]# cp server.xml server.xml.bak
[root@Mycat-node conf]# vim server.xml
.......
        <user name="bobo">
                <property name="password">bo@123</property>
                <property name="schemas">mycat/property>

                <!-- 表级 DML 权限设置 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>

        <!--                         #注意,由于这里只定义了一个标签,所以把多余的都注释了。如果这个打开,也需要将TESTDB库改为和上面一样的mycat库名。
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>
       -->
</mycat:server>
.......

重点关注上面这段配置,其他默认即可。
=======================================
参数           说明
user	      用户配置节点
name	      登录的用户名,也就是连接Mycat的用户名。
password      登录的密码,也就是连接Mycat的密码
schemas	      数据库名,这里会和schema.xml中的配置关联,多个用逗号分开,例如需要这个用户需要管理两个数据库db1,db2,则配置db1,dbs
privileges    配置用户针对表的增删改查的权限
readOnly      mycat逻辑库所具有的权限。true为只读,false为读写都有,默认为false。
=======================================
我这里配置了一个账号boo,密码为bo@123,逻辑数据库为mycat,这些信息都可以自己随意定义,读写权限都有,没有针对表做任何特殊的权限。
注意:
- server.xml文件里登录mycat的用户名和密码可以任意定义,这个账号和密码是为客户机登录mycat时使用的账号信息。
- 逻辑库名(如上面的mycat,也就是登录mycat后显示的库名,切换这个库之后,显示的就是代理的真实mysql数据库的表)要在schema.xml里面也定义,否则会导致mycat服务启动失败!
- 这里只定义了一个标签,所以把多余的都注释了。如果定义多个标签,即设置多个连接mycat的用户名和密码,那么就需要在schema.xml文件中定义多个对应的库!

schema.xml是最主要的配置项,此文件关联mysql读写分离策略!读写分离、分库分表策略、分片节点都是在此文件中配置的!
MyCat作为中间件,它只是一个代理,本身并不进行数据存储,需要连接后端的MySQL物理服务器,此文件就是用来连接MySQL服务器的!

schemaxml文件中配置的参数解释

参数            说明
schema	       数据库设置,此数据库为逻辑数据库,name与server.xml中schema对应
dataNode       分片信息,也就是分库相关配置
dataHost       物理数据库,真正存储数据的数据库

配置说明

name属性唯一标识dataHost标签,供上层的标签使用。
maxCon属性指定每个读写实例连接池的最大连接。也就是说,标签内嵌套的
writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最大连接数。
minCon属性指定每个读写实例连接池的最小连接,初始化连接池的大小。

每个节点的属性逐一说明

schema:
属性	           说明
name	           逻辑数据库名,与server.xml中的schema对应
checkSQLschema	   数据库前缀相关设置,建议看文档,这里暂时设为folse
sqlMaxLimit	   select 时默认的limit,避免查询全表

table

属性	           说明
name	           表名,物理数据库中表名
dataNode	   表存储到哪些节点,多个节点用逗号分隔。节点为下文dataNode设置的name
primaryKey	   主键字段名,自动生成主键时需要设置
autoIncrement	   是否自增
rule	           分片规则名,具体规则下文rule详细介绍

dataNode

属性	           说明
name	           节点名,与table中dataNode对应
datahost	   物理数据库名,与datahost中name对应
database	   物理数据库中数据库名

dataHost

属性	           说明
name	           物理数据库名,与dataNode中dataHost对应
balance	           均衡负载的方式
writeType	   写入方式
dbType	           数据库类型
heartbeat	   心跳检测语句,注意语句结尾的分号要加

schema.xml文件中有三点需要注意:balance=”1″,writeType=”0″ ,switchType=”1″
schema.xml中的balance的取值决定了负载均衡对非事务内的读操作的处理。balance 属性负载均衡类型,目前的取值有 4 种:

balance="0":      不开启读写分离机制,所有读操作都发送到当前可用的writeHost 上,即读请求仅发送到writeHost上。

balance="1":      读请求随机分发到当前writeHost对应的readHost和standby的writeHost上。即全部的readHost与stand by writeHost 参与
                   select 语句的负载均衡,简单的说,当双主双从模式(M1 ->S1 , M2->S2,并且 M1 与 M2 互为主备),正常情况下, M2,S1,
                   S2 都参与 select 语句的负载均衡

balance="2":      读请求随机分发到当前dataHost内所有的writeHost和readHost上。即所有读操作都随机的在writeHost、 readhost 上分发。

balance="3":      读请求随机分发到当前writeHost对应的readHost上。即所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,
                   writerHost 不负担读压力,注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。

writeType 属性,负载均衡类型,目前的取值有 3 种

writeType="0"   所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
writeType="1"   所有写操作都随机的发送到配置的 writeHost。
writeType="2"   没实现。

对于事务内的SQL默认走写节点

以 /*balance*/ 开头,可以指定SQL使用特定负载均衡方案。例如在大环境开启读写分离的情况下,特定强一致性的SQL查询需求;
slaveThreshold:近似的主从延迟时间(秒)Seconds_Behind_Master < slaveThreshold ,读请求才会分发到该Slave,确保读到的数据相对较新。

schema.xml中的writeType的取值决定了负载均衡对写操作的处理:
writeType="0":所有的写操作都发送到配置文件中的第一个write host。(第一个write host故障切换到第二个后,即使之后修复了仍然维持第二个为写库)。推荐取0值,不建议修改.

主从切换(双主failover):switchType 属性

如果细心观察schem.xml文件的话,会发现有一个参数:switchType,如下配置:
 <dataHost name="237_15" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native"switchType="1"  slaveThreshold="100">

参数解读
switchType="-1":  不自动切换
switchType="1":   默认值,自动切换
switchType="2":   基于MySQL主从同步的状态来决定是否切换。需修改heartbeat语句(即心跳语句):show slave status
switchType="3":   基于Mysql Galera Cluster(集群多节点复制)的切换机制。需修改heartbeat语句(即心跳语句):show status like 'wsrep%'

dbType属性

指定后端连接的数据库类型,目前支持二进制的mysql协议,还有其他使用JDBC连接的数据库。例如:mongodb、oracle、spark等。

dbDriver属性指定连接后端数据库使用的

Driver,目前可选的值有native和JDBC。

使用native的话,因为这个值执行的是二进制的mysql协议,所以可以使用mysql和maridb。
其他类型的数据库则需要使用JDBC驱动来支持。从1.6版本开始支持postgresql的native原始协议。

如果使用JDBC的话需要将符合JDBC 4标准的驱动JAR包放到MYCAT\lib目录下,并检查驱动JAR包中包括如下目录结构的文件:
META-INF\services\java.sql.Driver。在这个文件内写上具体的Driver类名,例如:com.mysql.jdbc.Driver。

heartbeat标签

这个标签内指明用于和后端数据库进行心跳检查的语句。例如,MYSQL可以使用select user(),Oracle可以使用select 1 from dual等。
这个标签还有一个connectionInitSql属性,主要是当使用Oracla数据库时,需要执行的初始化SQL

语句就这个放到这里面来。例如:altersession set nls_date_format='yyyy-mm-dd hh24:mi:ss'

1.4主从切换的语句必须是:showslave status

writeHost标签、readHost标签

这两个标签都指定后端数据库的相关配置给mycat,用于实例化后端连接池。

唯一不同的是:writeHost指定写实例、readHost指定读实例,组着这些读写实例来满足系统的要求。

在一个dataHost内可以定义多个writeHost和readHost。但是,如果writeHost指定的后端数据库宕机,那么这个writeHost绑定的所有readHost都将不可用。
另一方面,由于这个writeHost宕机系统会自动的检测到,并切换到备用的writeHost上去。

应用场景1—>Mycat读写分离(负载均衡)、主从自动切换

目前有大量Mycat的生产实践案例是属于简单的读写分离类型的,此案例主要用到Mycat的以下特性:
- 读写分离支持
- 高可用

大多数读写分离的案例是同时支持高可用性的,即Mycat+MySQL主从复制的集群,并开启Mycat的读写分离功能,这种场景需求下,Mycat是最为简单并且功能最为
丰富的一类Proxy,正常情况下,配置文件也最为简单,不用每个表配置,只需要在schema.xml中的元素上增加dataNode=“defaultDN”属性,并配置此dataNode
对应的真实物理数据库的database,然后dataHost开启读写分离功能即可。

修改mycat的schema.xml:
balance为1:让全部的readHost及备用的writeHost参与select的负载均衡。
switchType为2:基于MySQL主从同步的状态决定是否切换。
heartbeat:主从切换的心跳语句必须为show slave status。

仅仅进行读写分离的schema.xml配置(备份原来的schema.xml文件,清空,直接复制下面内容):不想要自动切换功能,即MySQL写节点宕机后不自动切换到备用节点:

[root@Mycat-node conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="haha">
        </schema>

<dataNode name="haha" dataHost="Mycat-node" database="kevin" />

    <dataHost name="Mycat-node" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <writeHost host="Mysql-node1" url="192.168.10.205:3306" user="root" password="123456">
             <readHost host="Mysql-node2" url="192.168.10.206:3306" user="root" password="123456">
             </readHost>
        </writeHost>
    </dataHost>

</mycat:schema>

实现主从自动切换的schema.xml配置:即MySQL写节点宕机后自动切换到备用节点(也就是把从机也配置成writeHosts):

[root@Mycat-node conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="haha">
        </schema>

<dataNode name="haha" dataHost="Mycat-node" database="kevin" />

    <dataHost name="Mycat-node" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <writeHost host="Mysql-node1" url="192.168.10.205:3306" user="root" password="123456">
             <readHost host="Mysql-node2" url="192.168.10.206:3306" user="root" password="123456">
             </readHost>
        </writeHost>
        <writeHost host="Mysql-node2" url="192.168.10.206:3306" user="root" password="123456">
        </writeHost>
    </dataHost>

</mycat:schema>

上面配置中,balance改为1,表示读写分离。
以上配置达到的效果就是192.168.10.205为主库,192.168.10.206为从库。
注意:要保证192.168.10.205和192.168.10.206机器能使用root/123456权限成功登录mysql数据库。同时,也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库!!这很重要,否则会导致登录mycat后,对库和表操作失败!

一主一从结构是最简单的配置。
MyCat支持双主多从,如果有N个主,那么就配置N个writeHost兄弟节点;如果有M个从节点,那么就配置M个readHost节点即可。
也可以有多台MySQL服务器,或者SQL Server、Oracle等,配置多个dataHost节点就可以。

需要注意的是:
Mycat主从分离只是在读的时候做了处理,写入数据的时候,只会写入到writehost,需要通过mycat的主从复制将数据复制到readhost!这个问题需要弄明白!!
如果没有提前做mysql主从复制,会发现Mycat读写分离配置后,数据写入writehost后,readhost一直没有数据!因为Mycat就没有实现主从复制的功能,毕竟数据库本身自带的这个功能才是最高效稳定的。

特别注意:

1)本案例采用的一主一从模式的两个mysql实例,并且针对单一的数据库名进行测试;大多数mycat使用场景都是在多主多从模式并针对多个库进行的。
2)要想登录Mycat后看到mysql的真实数据库的表内容,需要在schema.xml文件中指明database,其中dataNote和dataHost名称可以自定义,database名称要是mysql上的库名。
3)如果针对的是mysql的多个库,比如mysql的真实库名是kevin、grace、shanshan,那么schema.xml文件里应该指明多个库名,如:
       <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100" dataNode="haha,heihei,hengheng">
       </schema>
   <dataNode name="haha" dataHost="Mycat-node" database="kevin" />
   <dataNode name="heihei" dataHost="Mycat-node" database="grace" />
   <dataNode name="hengheng" dataHost="Mycat-node" database="shanshan" />
   ........
4)主从自动切换配置后,第一个writeHost故障后,会自动切换到第二个,第二个故障后自动切换到第三个;
   如果当是1主3从模式,可以把第1个从节点配置为writeHost 2,第2个和第3个从节点则配置为writeHost 1的readHost;

Mycat服务启动

[root@Mycat-node ~]# cd /data/mycat/bin/
[root@Mycat-node bin]#
[root@Mycat-node bin]# ./mycat start      #开启
[root@Mycat-node bin]# ./mycat stop       #关闭
[root@Mycat-node bin]# ./mycat restart    #重启
[root@Mycat-node bin]# ./mycat status     #查看启动状态
[root@Mycat-node bin]# ./mycat console    #前台运行
[root@Mycat-node bin]# ./mycat pause      #暂停 

mycat启动后,执行命令不成功,可能实际上配置有错误,导致后面的命令没有很好的执行。
如果在启动时发现异常,在logs目录中查看日志。
[root@Mycat-node ~]# cd /data/mycat/logs/
[root@Mycat-node logs]# ls
2018-07  mycat.log  mycat.pid  wrapper.log

- wrapper.log  为程序启动的日志,启动时的问题看这个
- mycat.log    为脚本执行时的日志,SQL脚本执行报错后的具体错误内容,查看这个文件。mycat.log是最新的错误日志,历史日志会根据时间生成目录保存。

[root@Mycat-node conf]# cd /data/mycat/bin/
[root@Mycat-node bin]# ./mycat start
Starting Mycat-server...
[root@Mycat-node bin]# ps -ef|grep cat
root      5693     1  0 11:28 ?        00:00:00 /data/mycat/bin/./wrapper-linux-x86-64 /data/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/data/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root      5695  5693 58 11:28 ?        00:00:01 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/zookeeper-3.4.6.jar:lib/velocity-1.7.jar:lib/disruptor-3.3.4.jar:lib/hamcrest-library-1.3.jar:lib/curator-client-2.11.0.jar:lib/mysql-binlog-connector-java-0.4.1.jar:lib/curator-framework-2.11.0.jar:lib/wrapper.jar:lib/log4j-1.2.17.jar:lib/kryo-2.10.jar:lib/objenesis-1.2.jar:lib/log4j-core-2.5.jar:lib/log4j-1.2-api-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/leveldb-api-0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/netty-3.7.0.Final.jar:lib/sequoiadb-driver-1.12.jar:lib/libwrapper-linux-x86-64.so:lib/curator-recipes-2.11.0.jar:lib/dom4j-1.6.1.jar:lib/commons-lang-2.6.jar:lib/guava-19.0.jar:lib/commons-collections-3.2.1.jar:lib/reflectasm-1.03.jar:lib/slf4j-api-1.6.1.jar:lib/joda-time-2.9.3.jar:lib/jline-0.9.94.jar:lib/libwrapper-linux-x86-32.so:lib/leveldb-0.7.jar:lib/Mycat-server-1.6-RELEASE.jar:lib/jsr305-2.0.3.jar:lib/libwrapper-linux-ppc-64.so:lib/univocity-parsers-2.2.1.jar:lib/hamcrest-core-1.3.jar:lib/log4j-api-2.5.jar:lib/asm-4.0.jar:lib/mapdb-1.0.7.jar:lib/fastjson-1.2.12.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar -Dwrapper.key=ZcN0KgylpD8RMkUx -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=5693 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.MycatStartup start
root      5729 24581  0 11:29 pts/0    00:00:00 grep cat

Mycat服务端口默认是8066
[root@Mycat-node bin]# lsof -i:8066
COMMAND  PID USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
java    5695 root   79u  IPv6 37036522      0t0  TCP *:8066 (LISTEN)[root@Mycat-node conf]# lsof -i:9066

Mycat还有一个管理端口,默认是9066
[root@Mycat-node conf]# lsof -i:9066
COMMAND  PID USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
java    5695 root   75u  IPv6 37036520      0t0  TCP *:9066 (LISTEN)

在客户机远程登录Mycat登录mysql(将mycat启动起来后,远程连接,默认端口是8066,逻辑库名和账号密码就是在schema.xml里配置的信息。代码里用jdbc方式连接)

Mycat带来的最大好处就是:
使用是完全不用修改原有代码的,在mycat通过命令启动后,你只需要将数据库连接切换到Mycat的地址就可以了。
如下面就可以进行连接了(注意使用server.xml文件中定义的用户名和密码连接mycat)
登录mycat的前提是:两台mysql机器要授权mycat服务器使用root/123456成功登录这两个mysql。
[root@client-server ~]# mysql -h192.168.10.210 -P8066 -ubobo -pbo@123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+----------+
| DATABASE |
+----------+
| mycat    |
+----------+
1 row in set (0.01 sec)

mysql> use mycat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
+----+-------------+
3 rows in set (0.00 sec)

mysql>

 Mycat读写分离测试

将mycat的日志输出级别改完debug(默认是info级别),在conf/log4j2.xml里配置,然后去查询去添加数据在/logs/mycat.log日志文件里查看sql被路由到了
哪个服务器上

特别注意:查询语句不要加事务,否则读操作会被分发到写服务器上。

[root@Mycat-node ~]# vim /data/mycat/conf/log4j2.xml
......
把
<asyncRoot level="info" includeLocation="true">
改成
<asyncRoot level="debug" includeLocation="true">

重启mycat服务
[root@Mycat-node ~]# /data/mycat/bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@Mycat-node ~]# lsof -i:8066
COMMAND   PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    18955 root   79u  IPv6 3812293      0t0  TCP *:8066 (LISTEN)

登录mycat
[root@client-server ~]# mysql -h192.168.10.210 -P8066 -ubobo -pbo@123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+----------+
| DATABASE |
+----------+
| mycat    |
+----------+
1 row in set (0.00 sec)

mysql> use mycat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
+----+-------------+
3 rows in set (0.00 sec)

mysql> 

通过mycat写入一条数据或读数据,查看sql被路由到了代理的哪个mysql服务器上了:
mysql> insert into kevin.haha values(4,"lihuan");
Query OK, 1 row affected (0.31 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
|  4 | lihuan      |
+----+-------------+
4 rows in set (0.01 sec)

mysql> 

然后查看mycat.log的debug日志,观察mysql的sql被路由到了哪个服务器上了(下面日志中的192.168.10.203是远程客户机的ip地址)。
[root@Mycat-node ~]# tail -f /data/mycat/logs/mycat.log
.......
2018-07-31 08:50:12.616 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:110)) - ServerConnection
[id=2, schema=mycat, host=192.168.10.203, user=bobo,txIsolation=3, autocommit=true, schema=mycat]insert into kevin.haha values(4,"lihuan"),
route={
   1 -> haha{insert into kevin.haha values(4,"lihuan")}
.......
.......
2018-07-31 08:50:12.617 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:448)) -
con need syn ,total syn cmd 1 commands SET names latin1;schema change:false con:MySQLConnection [id=6, lastTime=1532998212617, user=root,
schema=kevin, old shema=kevin, borrowed=true, fromSlaveDB=false, threadId=55, charset=latin1, txIsolation=3, autocommit=true, attachment=
haha{insert into kevin.haha values(4,"lihuan")}, respHandler=SingleNodeHandler [node=haha{insert into kevin.haha values(4,"lihuan")}, packetId=0],
host=192.168.10.205, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
.......
.......
2018-07-31 08:50:14.358 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341)) -
release connection MySQLConnection [id=12, lastTime=1532998214353, user=root, schema=kevin, old shema=kevin, borrowed=true, fromSlaveDB=true,
threadId=29, charset=latin1, txIsolation=3, autocommit=true, attachment=haha{select * from haha}, respHandler=SingleNodeHandler [node=haha
{select * from haha}, packetId=8], host=192.168.10.206, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

从日志中可以看出,"insert into ...."插入语句路由到了192.168.10.205的主机上了,"select * ..."的查询语句路由到了192.168.10.206的从机上了。
这就实现了mysql的读写分离!!

 Mycat主从自动切换测试(采用上面自动切换的schema.xml配置)

首先关闭主机192.168.10.205的mysql服务,此时从机192.168.10.206的mysql已经失去了和主机192.168.10.205的mysql主从关系。
[root@Mysql-node1 ~]# /etc/init.d/mysql stop
Shutting down MySQL............ SUCCESS!
[root@Mysql-node1 ~]# lsof -i:3306
[root@Mysql-node1 ~]# 

[root@Mysql-node2 ~]# mysql -p123456
........
mysql> show slave status \G;
*************************** 1. row ***************************
.......
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
.......
                Last_IO_Error: error reconnecting to master 'slave@192.168.10.205:3306' - retry-time: 60  retries: 1

然后客户机登录mycat进行读写操作
[root@client-server ~]# mysql -h192.168.10.210 -P8066 -ubobo -pbo@123
......
mysql> use mycat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
|  4 | lihuan      |
+----+-------------+
4 rows in set (0.00 sec)

mysql> insert into kevin.haha values(5,"gengmei");
Query OK, 1 row affected (0.00 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
|  4 | lihuan      |
|  5 | gengmei     |
+----+-------------+
5 rows in set (0.00 sec)

同时查看mycat.log的debug日志,观察读写操作都被路由到哪里了:
[root@Mycat-node ~]# tail -f /data/mycat/logs/mycat.log
.......
2018-07-31 09:10:19.261  INFO [$_NIOConnector] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:508)) - close connection,reason:java.net.
ConnectException: Connection refused ,MySQLConnection [id=0, lastTime=1532999419254, user=root, schema=kevin, old shema=kevin, borrowed=false, fromSlaveDB=false,
threadId=0, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.168.10.205, port=3306, statusSync=null, writeQueue=0,
modifiedSQLExecuted=false]
2018-07-31 09:10:19.261  INFO [$_NIOConnector] (io.mycat.sqlengine.SQLJob.connectionError(SQLJob.java:114)) - can't get connection for sql :show slave status
.......
2018-07-31 09:06:20.139 DEBUG [$_NIOREACTOR-1-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341)) -
release connection MySQLConnection [id=17, lastTime=1532999180134, user=root, schema=kevin, old shema=kevin, borrowed=true, fromSlaveDB=false,
threadId=46, charset=latin1, txIsolation=3, autocommit=true, attachment=haha{insert into kevin.haha values(5,"gengmei")}, respHandler=SingleNodeHandler
[node=haha{insert into kevin.haha values(5,"gengmei")}, packetId=1], host=192.168.10.206, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]
......
2018-07-31 09:06:21.727 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:448)) - con need syn ,total
syn cmd 1 commands SET names latin1;schema change:false con:MySQLConnection [id=16, lastTime=1532999181727, user=root, schema=kevin, old shema=kevin, borrowed
=true, fromSlaveDB=false, threadId=48, charset=latin1, txIsolation=3, autocommit=true, attachment=haha{select * from haha}, respHandler=SingleNodeHandler [node=
haha{select * from haha}, packetId=0], host=192.168.10.206, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

从上面的日志中可以看出,主机192.168.10.205已经断开从mycat连接,insert写语句和select读语句都被路由分配到了从机192.168.10.206的mysql上了,
这就看出来已经实现了mysql主从启动切换了!!!

登录从机192.168.10.206的mysql,发现新插入的数据已经写进去了
[root@Mysql-node2 ~]# mysql -p123456
.......
mysql> use kevin;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from kevin.haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
|  4 | lihuan      |
|  5 | gengmei     |
+----+-------------+
5 rows in set (0.00 sec)

mysql>

Mycat分表分库的原理
mycat里面通过定义路由规则来实现分片表(路由规则里面会定义分片字段,以及分片算法)。分片算法有多种,你所说的hash是其中一种,还有取模、按范围分片等等。在mycat里面,会对所有传递的sql语句做路由处理(路由处理的依据就是表是否分片,如果分片,那么需要依据分片字段和对应的分片算法来判断sql应该传递到哪一个、或者哪几个、又或者全部节点去执行)

Mycat适用于哪些场景?
数据量大到单机hold不住,而又不希望调整架构切换为NoSQL数据库,这个场景下可以考虑适用mycat。当然,使用前也应该做规划,哪些表需要分片等等。另外mycat对跨库join的支持不是很好,在使用mycat的时候要注意规避这种场景。

应用场景2—>MYcat分库分表配置及测试:

接着上面的操作继续:

启动主机192.168.10.205的mysql
[root@Mysql-node1 ~]# /etc/init.d/mysql start
Starting MySQL... SUCCESS!
[root@Mysql-node1 ~]# lsof -i:3306
COMMAND   PID  USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mysqld  11645 mysql   17u  IPv4 10457025      0t0  TCP *:mysql (LISTEN)
mysqld  11645 mysql   39u  IPv4 10457749      0t0  TCP Mysql-node1:mysql->Mycat-node:39252 (ESTABLISHED)
mysqld  11645 mysql   40u  IPv4 10458099      0t0  TCP Mysql-node1:mysql->Mysql-node1:57270 (ESTABLISHED)

登录从机192.168.10.206的mysql,重启slave的主从同步关系
[root@Mysql-node2 ~]# mysql -p123456
......
mysql> show slave status \G;
......
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
......
                Last_IO_Error: error reconnecting to master 'slave@192.168.10.205:3306' - retry-time: 60  retries: 81
......

mysql> stop slave;
Query OK, 0 rows affected (0.08 sec)

mysql> start slave;
Query OK, 0 rows affected (0.04 sec)

mysql> show slave status \G;
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....

在主机192.168.10.205的mysql上再创建一个数据库grace
现在需要将haha表放在kevin库里,将heihei表拆分放到kevin和grace库了。

操作如下(在主机mysql里操作,从机mysql自动会同步过来):
登录grace库里创建heihei表,同时在kevin库里也创建heihei表。

特别需要注意的是:
- 分表的表在创建时一定要创建主键,否则在mycat端写入数据时会报错主键冲突!!
- 分表的表要在两个库上都要创建。

mysql> CREATE DATABASE grace CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> use grace;
Database changed

mysql> CREATE TABLE heihei (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> city varchar(50) NOT NULL,
    -> PRIMARY KEY (id)
    -> )AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.23 sec)

mysql> insert into grace.heihei values(1,"beijing");
Query OK, 1 row affected (0.03 sec)

mysql> show tables;
+-----------------+
| Tables_in_grace |
+-----------------+
| heihei          |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from grace.heihei;
+----+---------+
| id | name    |
+----+---------+
|  1 | beijing |
+----+---------+
1 row in set (0.00 sec)

mysql> use kevin;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
+-----------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE heihei (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> city varchar(50) NOT NULL,
    -> PRIMARY KEY (id)
    -> )AUTO_INCREMENT= 1 ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.23 sec)

mysql> show tables;
+-----------------+
| Tables_in_kevin |
+-----------------+
| haha            |
| heihei          |
+-----------------+
2 rows in set (0.00 sec)

mysql> insert into kevin.heihei values(1,"shanghai");
Query OK, 1 row affected (0.13 sec)

mysql> select * from kevin.heihei;
+----+----------+
| id | name     |
+----+----------+
|  1 | shanghai |
+----+----------+
1 row in set (0.00 sec)

上面在从机的mysql里操作后,从机的mysql会自动把数据同步过来!

登录mycat机器192.168.10.210机器,分别设置server.xml文件、rule.xml文件、schema.xml文件
server.xml文件在上面已经设置过了,这里就不用修改了:
[root@Mycat-node ~]# vim /data/mycat/conf/server.xml
......
<!-- mycat的服务端口默认为8066,管理端口默认为9066 -->
         <property name="serverPort">8066</property> <property name="managerPort">9066</property>
.....
<!-- 任意设置登陆 mycat 的用户名,密码,数据库  -->
        <user name="bobo">
                <property name="password">bo@123</property>
                <property name="schemas">mycat</property>

                <!-- 表级 DML 权限设置 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>

         <!--
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>
       -->
</mycat:server>

分库分表配置还涉及到rule.xml文件,配置如下(备份文件,清空,直接复制下面内容):
[root@Mycat-node conf]# cp rule.xml rule.xml.bak
[root@Mycat-node conf]# vim rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
    - you may not use this file except in compliance with the License. - You
    may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
    - - Unless required by applicable law or agreed to in writing, software -
    distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
    License for the specific language governing permissions and - limitations
    under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">

    <tableRule name="mycat-rule">                <!-- heihei表分片的规则名,这里定义为mycat-rule,这个需要在schema.xml文件中引用 -->
        <rule>
            <columns>id</columns>                  <!--heihei表的分片列 -->
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>

    <function name="mod-long">
        <!-- how many data nodes -->
        <property name="count">2</property>        <!-- count值与分片个数相同,这里heihei表分片到kevin库和grace库里,共2份。 -->
    </function>
</mycat:rule>

接着配置schema.xml文件(server.xml文件配置在已经已经完成),分库分表配置如下
(要将rule.xml里定义的分片模型写进去,由于这里的heihei表配置了主键,所以primaryKey="id"这个也写进去,其他情况看表结构,也可以不写):
[root@Mycat-node conf]# cp schema.xml schema.xml.old
[root@Mycat-node conf]# vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100">
        <table name="haha" primaryKey="id"  dataNode="kevin_db" />
        <table name="heihei" primaryKey="id" dataNode="kevin_db,grace_db" rule="mycat-rule" />
        </schema>

<dataNode name="kevin_db" dataHost="Mycat-node" database="kevin" />
<dataNode name="grace_db" dataHost="Mycat-node" database="grace" />

    <dataHost name="Mycat-node" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <writeHost host="Mysql-node1" url="192.168.10.205:3306" user="root" password="123456">
             <readHost host="Mysql-node2" url="192.168.10.206:3306" user="root" password="123456">
             </readHost>
        </writeHost>
        <writeHost host="Mysql-node1" url="192.168.10.206:3306" user="root" password="123456">
        </writeHost>
    </dataHost>

</mycat:schema>

重启mycat服务
[root@Mycat-node conf]# /data/mycat/bin/mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@Mycat-node conf]# lsof -i:8066
COMMAND PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    971 root   79u  IPv6 4024290      0t0  TCP *:8066 (LISTEN)
[root@Mycat-node conf]# lsof -i:9066
COMMAND PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
java    971 root   75u  IPv6 4024288      0t0  TCP *:9066 (LISTEN)

在客户机远程登录Mycat
[root@client-server ~]# mysql -h192.168.10.210 -P8066 -ubobo -pbo@123
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+----------+
| DATABASE |
+----------+
| mycat    |
+----------+
1 row in set (0.01 sec)

mysql> use mycat;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables in mycat |
+-----------------+
| haha            |
| heihei          |
+-----------------+
2 rows in set (0.01 sec)

mysql> select * from haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
|  4 | lihuan      |
+----+-------------+
4 rows in set (0.12 sec)

mysql> select * from heihei;
+----+----------+
| id | city     |
+----+----------+
|  1 | beijing  |
|  1 | shanghai |
+----+----------+
2 rows in set (0.04 sec)

mysql> 

分别在mycat里往heihei和grace.heihei表里插入一些数据
mysql> insert into haha values(10,"wangbiao");
insert into haha values(11,"zhangcaiyi");Query OK, 1 row affected (0.06 sec)

mysql> insert into haha values(11,"zhangcaiyi");
Query OK, 1 row affected (0.06 sec)

mysql> insert into heihei(id,city) values(20,"chognqing");
Query OK, 1 row affected (0.09 sec)

mysql> insert into heihei(id,city) values(22,"xianggang");
insert into heihei(id,city) values(28,"chengdu");Query OK, 1 row affected (0.02 sec)

mysql> insert into heihei(id,city) values(23,"huoqiu");
Query OK, 1 row affected (0.02 sec)

mysql> insert into heihei(id,city) values(24,"haikou");
Query OK, 1 row affected (0.03 sec)

mysql> insert into heihei(id,city) values(25,"anqing");
Query OK, 1 row affected (0.03 sec)

mysql> insert into heihei(id,city) values(26,"tianqing");
Query OK, 1 row affected (0.02 sec)

mysql> insert into heihei(id,city) values(27,"hangzhou");
Query OK, 1 row affected (0.04 sec)

mysql> insert into heihei(id,city) values(28,"chengdu");
Query OK, 1 row affected (0.26 sec)

特别注意:
1)在配置了sharding分片策略之后(如heihei表),mycat里分片的表做插入数据时,即使插入所有字段的数据,也一定要在表名后面写明插入数据的字段名称,
   否则插入数据会报错:ERROR 1064 (HY000): partition table, insert must provide ColumnList
2)没有配置sharding分片策略的表(如haha表),插入所有字段的数据时,表名后面不需要写明字段名称,默认就是所有字段插入数据,如上面的haha表。

登录主机和从机,查看从mycat端写入的数据
mysql> select * from kevin.haha;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | wangshibo   |
|  2 | linan       |
|  3 | zhangminmin |
|  4 | lihuan      |
| 10 | wangbiao    |
| 11 | zhangcaiyi  |
+----+-------------+
6 rows in set (0.00 sec)

mysql> select * from grace.heihei;
+----+-----------+
| id | city      |
+----+-----------+
|  1 | beijing   |
| 21 | guangzhou |
| 23 | huoqiu    |
| 25 | anqing    |
| 27 | hangzhou  |
+----+-----------+
5 rows in set (0.00 sec)

mysql> select * from kevin.heihei;
+----+-----------+
| id | city      |
+----+-----------+
|  1 | shanghai  |
| 20 | chognqing |
| 22 | xianggang |
| 24 | haikou    |
| 26 | tianqing  |
| 28 | chengdu   |
+----+-----------+
6 rows in set (0.00 sec)

mysql>

从上面可以看出:
在mycat里往做了sharding分片策略的heihei表里写入的数据,已经分片到kevin和grace两个库里了,即成功实现了分库分表功能!

查看mycat的debug日志,可以观察到mysql读写分离和分库分表情况
[root@Mycat-node logs]# tail -f mycat.log

这里需要注意:
-  查询语句不要加事务,否则读操作会被分发到写服务器上。
-  主从复制是mysql自己实现的,mycat只是代理插件,它本身不能实现主从复制,只能实现了读写分离、主从切换、分库分表功能。

为了提升查询的性能,有人创新的设计了一种MySQL主从复制的模式,主节点为InnoDB引擎,读节点为MyISAM引擎,经过实践,发现查询性能提升不少。

此外,为了减少主从复制的时延,也建议采用MySQL 5.6+的版本,用GTID同步复制方式减少复制的时延,可以将一个Database中的表,根据写频率的不同,
分割成几个Database,用Mycat虚拟为一个Database,这样就满足了多库并发复制的优势,需要注意的是,要将有Join关系的表放在同一个库中。

对于某些表,要求不能有复制时延,则可以考虑这些表放到Gluster集群里,消除同步复制的时延问题,前提是这些表的修改操作并不很频繁,需要做性能测试,
以确保能满足业务高峰。

总结一下,Mycat做读写分离和高可用,可能的方案很灵活,只有你没想到的,没有做不到的。

=================Mycat常见问题及注意点==================
1)Mycat自动切换需要人工处理么?
Mycat通过心跳检测,自主切换数据库,保证高可用性,无须手动切换。

2)Mycat支持集群么?
目前Mycat没有实现对多Mycat集群的支持,可以暂时使用haproxy来做负载,或者统计硬件负载。

3)Mycat目前有生产案例了么?
目前Mycat初步统计大概600家公司使用。

4)Mycat稳定性与Cobar如何?
目前Mycat稳定性优于Cobar,而且一直在更新,Cobar已经停止维护,可以放心使用。

5)Mycat除了Mysql还支持哪些数据库?
mongodb、oracle、sqlserver 、hive 、db2 、 postgresql。

6)Mycat如何配置字符集?
在配置文件server.xml配置,默认配置为utf8。

7)Mycat后台管理监控如何使用?
9066端口可以用JDBC方式执行命令,在界面上进行管理维护,也可以通过命令行查看命令行操作。
命令行操作是:mysql -h127.0.0.1 -utest -ptest -P9066 登陆,然后执行相应命令。

8)Mycat主键插入后应用如何获取?
获得自增主键,插入记录后执行select last_insert_id()获取。

9)Mycat运行sql时经常阻塞或卡死是什么原因?
如果出现执行sql语句长时间未返回,或卡死,请检查是否是虚机下运行或cpu为单核。如果仍旧无法解决,可以暂时跳过,目前有些环境阻塞卡死原因未知。

10)Mycat中,旧系统数据如何迁移到Mycat中?
旧数据迁移目前可以手工导入,在mycat中提取配置好分配规则及后端分片数据库,然后通过dump或loaddata方式导入,后续Mycat就做旧数据自动数据迁移工具。

11)Mycat如何对旧分片数据迁移或扩容,支持自动扩容么?
目前除了一致性hash规则分片外其他数据迁移比较困难,目前暂时可以手工迁移,未提供自动迁移方案,具体迁移方案情况Mycat权威指南对应章节。

12)Mycat支持批量插入吗?
目前Mycat1.3.0.3以后支持多values的批量插入,如insert into(xxx) values(xxx),(xxx) 。

13)Mycat支持多表Join吗?
Mycat目前支持2个表Join,后续会支持多表Join,具体Join请看Mycat权威指南对应章节。

14)Mycat 启动报主机不存在的问题?
需要添加ip跟主机的映射。

15)Mycat连接会报无效数据源(Invalid datasource)?
例如报错:mysql> select * from company;
ERROR 3009 (HY000): java.lang.IllegalArgumentException: Invalid DataSource:0
这类错误最常见是一些配置问题例如schema.xml中的dataNode的配置和实际不符合,请先仔细检查配置项,确保配置没有问题。
如果不是配置问题,分析具体日志看出错原因,常见的有:
- 如果是应用连:在某些版本的Mysql驱动下连接Mycat会报错,可升级最新的驱动包试下。
- 如果是服务端控制台连,确认mysql是否开启远程连接权限,或防火墙是否设置正确,或者数据库database是否配置,或用户名密码是否正确。

16)Mycat支持的或者不支持的语句有哪些?
insert into,复杂子查询,3表及其以上跨库join等不支持。

17)MycatJDBC连接报 PacketTooBigException异常
检查mysqljdbc驱动的版本,在使用mycat1.3和mycat1.4版本情况下,不要使用jdbc5.1.37和38版本的驱动,会出现如下异常报错:
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (60 > -1). You can change this value on the server by setting the max_allowed_packet’ variable。
建议使用jdbc5.1.35或者36的版本。

18)Mycat中文乱码的问题
答:如果在使用mycat出现中文插入或者查询出现乱码,请检查三个环节的字符集设置:
a)客户端环节(应用程序、mysql命令或图形终端工具)连接mycat字符集
b)mycat连接数据库的字符集
c)数据库(mysql,oracle)字符集。这三个环节的字符集如果配置一致,则不会出现中文乱码,其中尤其需要注意的是客户端连接mycat时使用的连接字符集,
通常的中文乱码问题一般都由此处设置不当引出。其中mycat内部默认使用utf8字符集,在最初启动连接数据库时,mycat会默认使用utf8去连接数据库,当客户
端真正连接mycat访问数据库时,mycat会使用客户端连接使用的字符集修改它连接数据库的字符集,在mycat环境的管理9066端口,可以通过”show @@backend”命令
查看后端数据库的连接字符集,通过show @@connection命令查看前端客户端的连接字符集。客户端的连接可以通过指定字符集编码或者发送SET命令指定连接mycat
时connection使用的字符集,常见客户端连接指定字符集写法如下:
-   jdbcUrl=jdbc:mysql://localhost:8066/databaseName? characterEncoding=iso_1
-   SET character_set_client = utf8;用来指定解析客户端传递数据的编码
SET character_set_results = utf8;用来指定数据库内部处理时使用的编码
SET character_set_connection = utf8;用来指定数据返回给客户端的编码方式
-   mysql –utest –ptest –P8066 –default-character-set=gbk

19)Mycat无法登陆Access denied
Mycat正常安装配置完成,登陆mycat出现以下错误:
[mysql@master ~]$ mysql -utest -ptest -P8066
ERROR 1045 (28000): Access denied for user ‘test’@’localhost’ (using password: YES)
请检查在schema.xml中的相关dataHost的mysql主机的登陆权限,一般都是因为配置的mysql的用户登陆权限不符合,mysql用户权限管理不熟悉的请自己度娘。
只有一种情况例外,mycat和mysql主机都部署在同一台设备,其中主机localhost的权限配置正确,使用-hlocalhost能正确登陆mysql但是无法登陆mycat的情况,
请使用-h127.0.0.1登陆,或者本地网络实际地址,不要使用-hlocalhost,很多使用者反馈此问题,原因未明。

20)Mycat的分片数据插入报异常IndexOutofBoundException
在一些配置了分片策略的表进行数据插入时报错,常见的报错信息如下:java.lang.IndexOutOfBoundsException:Index:4,size:3
这类报错通常由于分片策略配置不对引起,请仔细检查并理解分片策略的配置,例如:使用固定分片hash算法,PartitionByLong策略,如果schema.xml里面设置
的分片数量dataNode和rule.xml配置的partitionCount 分片个数不一致,尤其是出现分片数量dataNode小于partitionCount数量的情况,插入数据就可能会报错。
很多使用者都没有仔细理解文档中对分片策略的说明,用默认rule.xml配置的值,没有和自己实际使用环境进行参数核实就进行分片策略使用造成这类问题居多。

21)Mycat ER分片子表数据插入报错
一般都是插入子表时出现不能找到父节点的报错。报错信息如: [Err] 1064 – can’t find (root) parent sharding node for sql:。
此类ER表的插入操作不能做为一个事务进行数据提交,如果父子表在一个事务中进行提交,显然在事务没有提交前子表是无法查到父表的数据的,因此就无法确定
sharding node。如果是ER关系的表在插入数据时不能在同一个事务中提交数据,只能分开提交。

22)Mycat最大内存无法调整至4G以上
mycat1.4的JVM使用最大内存调整如果超过4G大小,不能使用wrapper.java.maxmemory参数,需要使用wrapper.java.additional的写法,注意将
wrapper.java.maxmemory参数注释,例如增加最大内存至8G:wrapper.java.additional.10=-Xmx8G。

23)Mycat使用过程中报错怎么办
记住无论什么时候遇到报错,如果不能第一时间理解报错的原因,首先就去看日志,无论是启动(wrapper.log)还是运行过程中(mycat.log),请相信良好的
日志是编程查错的终极必杀技。日志如果记录信息不够,可以调整conf/log4j.xml中的level级别至debug,所有的详细信息均会记录。另外如果在群里面提问,
尽量将环境配置信息和报错日志提供清楚,这样别人才能快速帮你定位问题。

Mysql INNODB存储引擎表损坏修复方法(转)

2017年12月10日 没有评论

设你正在运行使用InnoDB表格的MySQL,糟糕的硬件设备,驱动程序错误,内核错误,不幸的电源故障或某些罕见的MySQL错误使你的InnoDB表空间被损坏了。
在这种情况下,InnoDB的一般会出现这样的输出:

InnoDB: Database page corruption on disk ora failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
080703 23:46:16 InnoDB: Page dump in ascii and hex (16384bytes):
… 这里省略很多二进制和十六进制编码…
080703 23:46:16 InnoDB: Page checksum 587461377,prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form storedchecksum 772331632
InnoDB: Page lsn 24 1487506025, low 4 bytes of lsn at page end1487506025
InnoDB: Page number (if stored to page already) 7,
InnoDB: Database page corruption on disk or a failed

mysqldump导出库时报错如下:
“mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `; at row:6880″
或是操作对应表时,也会报错。这时数据库会重启。
当时想到的是在修复之前保证数据库正常,不是这么异常的无休止的重启。

使用innodb_force_recovery =1,正如你所看到的,即使日志文件中有校验失败的记录,但CHECK TABLE还是说表格是正确的。
这意味着你不能太依赖CHECKTABLE在InnoDB上执行的结果。
所以就修改了配置文件的一个参数:innodb_force_recovery
innodb_force_recovery影响整个InnoDB存储引擎的恢复状况。默认为0,表示当需要恢复时执行所有的,
innodb_force_recovery可以设置为1-6,大的数字包含前面所有数字的影响。
当设置参数值大于0后,可以对表进行select,create,drop操作,但insert,update或者delete这类操作是不允许的。
1(SRV_FORCE_IGNORE_CORRUPT):忽略检查到的corrupt页。
2(SRV_FORCE_NO_BACKGROUND):阻止主线程的运行,如主线程需要执行full purge操作,会导致crash。
3(SRV_FORCE_NO_TRX_UNDO):不执行事务回滚操作。
4(SRV_FORCE_NO_IBUF_MERGE):不执行插入缓冲的合并操作。
5(SRV_FORCE_NO_UNDO_LOG_SCAN):不查看重做日志,InnoDB存储引擎会将未提交的事务视为已提交。
6(SRV_FORCE_NO_LOG_REDO):不执行前滚的操作。
因为错误日志里面提示出现了坏页,导致数据库崩溃,所以这里把innodb_force_recovery 设置为1,忽略检查到的坏页。
重启数据库之后,找到错误信息出现的表

因为后者可以通过使用OPTIMIZE TABLE命令来修复,但这和更难以恢复的表格目录(table dictionary)被破坏的情况来说要好一些。

操作步骤:

修改my.cnf 配置文件,添加innodb_force_recovery = 1运行InnoDB,然后重启mysql 。

1,建议一个备份表,T_MinuteStore_BAK,使用MYISAM存储引擎,建表语句如下:

CREATE TABLE `T_MinuteStore_BAK` (
`T_MinuteStore_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘行标识’,
`siteID` int(11) DEFAULT NULL COMMENT ‘站点标识’,
`genTime` datetime DEFAULT NULL COMMENT ‘监测时间’,
`itemID` int(11) DEFAULT NULL COMMENT ‘监测项目’,
`value` varchar(255) DEFAULT NULL COMMENT ‘监测值’,
`flag` varchar(255) DEFAULT NULL COMMENT ‘异常标志’,
`filterFlag` bit(1) DEFAULT NULL COMMENT ‘过滤标志’,
PRIMARY KEY (`T_MinuteStore_ID`)
) ENGINE=MYISAM AUTO_INCREMENT=188995 DEFAULT CHARSET=utf8 COMMENT=’监测分钟数据–5分钟一组数据’;

2,把T_MinuteStore表中的数据导入到新表T_MinuteStore_BAK.

insert into T_MinuteStore_BAK select * from T_MinuteStore;

提示报错: 2013 (HY000): Lost connection TO MySQL server during query
你可能想对数据表进行扫描直到第一个被损坏的行,然后从MyISAM表中得到结果?
不幸的是,运行之后的T_MinuteStore_BAK表格是只有一部份数据。
这里查看T_MinuteStore_BAK表中,主键 T_MinuteStore_ID 从最小值到160861,可以看出是被顺序插入的数据。
那么现在就好办了,说明从16081之后,有损坏的数据行。
下面作下插入数据测试,看看大约有多少行数据损坏。
insert into T_MinuteStore_BAK select * from T_MinuteStore where T_MinuteStore_ID>161000;
执行命令后,还是报错。
最后测试;
161500160861
此主键之间的数据,有损坏,其它数据恢复正常。总计100万行数据,只丢了几百行数据,完全可以承受。
3, 删除掉原表: drop table T_MinuteStore;注释掉innodb_force_recovery 之后,重启Mysql。
4,重命名 T_MinuteStore_BAK:

rename table T_MinuteStore_BAK to T_MinuteStore;
5,最后将表T_MinuteStore修改回存储引擎:

alter table T_MinuteStore engine = innodb;

最后将数据库数据导出,测试导出成功,未发现问题。备份好数据,然后更换好硬盘,重装好Mysql ,将数据导入。
测试程序,一切正常。

分类: 解决方案 标签: , ,

解决在phpmyadmin中执行sql语句出现的错误:Unknown storage engine ‘InnoDB’

2016年10月3日 没有评论

问题:phpmyadmin报错——Unknown storage engine ‘InnoDB’

解决方法:解决方法:
1.关闭MySQL数据库
   2.修改my.ini文件,把skip-innodb这行注释掉
   3.打开MySQL数据库
当然把innodb改成MyISAM也行

原因:没有开启MySQL InnoDB存储引擎。

关于innodb引擎的资料:
事务型数据库的首选引擎,支持ACID事务,支持行级锁定。InnoDB是为处理巨大数据量时的最大性能设计。InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,即使在文件尺寸被限制为2GB的操作系统上。InnoDB默认地被包含在MySQL二进制分发中。Windows Essentials installer使InnoDB成为Windows上MySQL的默认表。

  简介   InnoDB 给 MySQL 提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)、多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行级锁(locking on row level),提供与 Oracle 类似的不加锁读取(non-locking read in SELECTs)。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要,因为在InnoDB中行级锁定适合非常小的空间。InnoDB也支持FOREIGN KEY强制。在SQL查询中,你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来,甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation),因为 InnoDB 的行级锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。   在技术上,InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

MySQL

InnoDB 把数据和索引存放在表空间里,可能包含多个文件,这与其它的不一样,举例来说,在 MyISAM 中,表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小,可也可以每个表使用各自独立的表空间,只需要启用选项 innodb_file_per_table。   在 MySQL 的源代码中,从 3.23.34a 开始包含 InnoDB 表引擎,并在 MySQL -Max 的二进制版本中激活。

分类: Linux, 解决方案 标签: ,

利用mysql二进制日志进行mysql数据库的恢复(转)

2016年9月4日 没有评论

对于需要确保mysql数据库完整性的网站,mysql的二进制日志至关重要。比如你做了mysql数据库的主从备份,你认为这应该能确保mysql从数据库是最新的内容了,这的确没错,但有一种情况,比如你误删除了数据,或者被黑客入侵而清除了数据库,这时mysql从服务器也会跟主数据库同步,这是你不想看到的,但数据确实没了。但是如果你每天进行了数据库的完整备份,而且mysql的二进制日志还在的话,这将有可能恢复最新的数据。下面介绍方法。

1、开启二进制日志
在使用二进制日志恢复数据库时,请确保你已经开启二进制日志,检查my.cnf文件是否存在log-bin=mysql-bin。

2、定期完整备份
你还需要间隔一定的时间做数据库的完整备份,只有这样你才有可能快速的恢复最新数据。

3、查看备份时间
查看你备份的时间,如dedecmsv57gbksp1.sql,取最后一行:– Dump completed on 2012-11-08 11:11:48。

4、导出二进制日志
暂时关闭网站,防止新数据写入,导出二进制日志:

mysqlbinlog mysql-bin.000001 -d dedecmsv57gbksp1 –start-datetime=”2012-11-08 11:11:48″ >binlog.sql

5、调整binlog.sql
编辑binlog.sql,删除误操作的语句。

6、导入数据
重建数据库dedecmsv57gbksp1,导入dedecmsv57gbksp1.sql,接着导入binlog.sql。

MySQL删除数据库时的错误error dropping database

2016年1月8日 没有评论

由于在1月2号更新了下数据库,却出现了数据库已存在的提示信息。于是就进入命令行窗口drop database hkvps,可是却得到了ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./hkvps’, errno: 39)的错误信息。

解决方法:删除/var/lib/mysql/hkvps下面的所有文件,不要删除hkvps本身。然后再通过命令行drop database就可以了。

分类: 解决方案 标签: ,

mysql更改tmp路径

2016年1月5日 没有评论

因为mysql经常要把文件定入tmp目录,而tmp的目录为/tmp,为了提高性能,所以考虑更改tmpdir目录为/run/shm,即使用内存来存储。

1、打开/etc/mysql/my.cnf
更改tmpdir = /tmp为

tmpdir = /run/shm
2、更改/etc/apparmor.d/usr.sbin.mysqld添加:

/run/shm/* rw
3、重启mysql和apparmor

1
2
/etc/init.d/apparmor restart
/etc/init.d/mysql restart
分类: 解决方案 标签: ,

MySQL优化经验 key_buffer_size,query_cache_size,table_cache(转)

2015年12月10日 没有评论

同时在线访问量继续增大 对于1G内存的服务器明显感觉到吃力严重时甚至每天都会死机 或者时不时的服务器卡一下 这个问题曾经困扰了我半个多月MySQL使用是很具伸缩性的算法,因此你通常能用很少的内存运行或给MySQL更多的被存以得到更好的性能。

安装好mysql后,配制文件应该在/usr/local/mysql/share/mysql目录中,配制文件有几个,有my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf,不同的流量的网站和不同配制的服务器环境,当然需要有不同的配制文件了。

一般的情况下,my-medium.cnf这个配制文件就能满足我们的大多需要;一般我们会把配置文件拷贝到/etc/my.cnf 只需要修改这个配置文件就可以了,使用mysqladmin variables extended-status –u root –p 可以看到目前的参数,有3个配置参数是最重要的,即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只对MyISAM表起作用,

key_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点 这个数字是远远不够的,通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql,以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天

key_buffer_size – 128M
key_read_requests – 650759289
key_reads – 79112

比例接近1:8000 健康状况非常好

另外一个估计key_buffer_size的办法 把你网站数据库的每个表的索引所占空间大小加起来看看以此服务器为例:比较大的几个表索引加起来大概125M 这个数字会随着表变大而变大。

从4.0.1开始,MySQL提供了查询缓冲机制。使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。

通过调节以下几个参数可以知道query_cache_size设置得是否合理

Qcache inserts
Qcache hits
Qcache lowmem prunes
Qcache free blocks
Qcache total blocks

Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲

我设置:

query_cache_size = 32M
query_cache_type= 1

得到如下状态值:

Qcache queries in cache 12737 表明目前缓存的条数
Qcache inserts 20649006
Qcache hits 79060095  看来重复查询率还挺高的
Qcache lowmem prunes 617913 有这么多次出现缓存过低的情况
Qcache not cached 189896   
Qcache free memory 18573912  目前剩余缓存空间
Qcache free blocks 5328 这个数字似乎有点大 碎片不少
Qcache total blocks 30953

如果内存允许32M应该要往上加点

table_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。

对于有1G内存的机器,推荐值是128-256。

笔者设置table_cache = 256

得到以下状态:

Open tables 256
Opened tables 9046

虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,已经运行了20天,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。如果运行了6个小时就出现上述值 那就要考虑增大table_cache。

如果你不需要记录2进制log 就把这个功能关掉,注意关掉以后就不能恢复出问题前的数据了,需要您手动备份,二进制日志包含所有更新数据的语句,其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外,如果做同步复制( Replication )的话,也需要使用二进制日志传送修改情况。

log_bin指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字引,每次启动服务时,都会重新生成一个新的二进制文件。此外,使用log-bin-index可以指定索引文件;使用binlog-do-db可以指定记录的数据库;使用binlog-ignore-db可以指定不记录的数据库。注意的是:binlog-do-db和binlog-ignore-db一次只指定一个数据库,指定多个数据库需要多个语句。而且,MySQL会将所有的数据库名称改成小写,在指定数据库时必须全部使用小写名字,否则不会起作用。

关掉这个功能只需要在他前面加上#号

#log-bin

开启慢查询日志( slow query log ) 慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询,如果需要,还可以记录不使用索引的记录。下面是一个慢查询日志的例子:

开启慢查询日志,需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。

log_slow_queries指定日志文件,如果不提供文件名,MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值,缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数,它指示记录不使用索引的查询。笔者设置long_query_time=10

笔者设置:

sort_buffer_size = 1M
max_connections=120
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
interactive_timeout=120
thread_concurrency = 4

参数说明:

back_log

要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求,这就起作用,然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接,你需要增加它,换句话说,这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 Unix listen(2)系统调用的手册页应该有更多的细节。检查你的OS文档找出这个变量的最大值。试图设定back_log高于你的操作系统的限制将是无效的。

max_connections

并发连接数目最大,120 超过这个值就会自动恢复,出了问题能自动解决

thread_cache

没找到具体说明,不过设置为32后 20天才创建了400多个线程 而以前一天就创建了上千个线程 所以还是有用的

thread_concurrency

#设置为你的cpu数目x2,例如,只有一个cpu,那么thread_concurrency=2
#有2个cpu,那么thread_concurrency=4
skip-innodb
#去掉innodb支持

代码:

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M – 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the “–help” option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
#socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
myisam_sort_buffer_size = 1M
max_connections=120
#addnew config
wait_timeout =120
back_log=100
read_buffer_size = 1M
thread_cache=32
skip-innodb
skip-bdb
skip-name-resolve
join_buffer_size=512k
query_cache_size = 32M
interactive_timeout=120
long_query_time=10
log_slow_queries= /usr/local/mysql4/logs/slow_query.log
query_cache_type= 1
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 4

#end new config
# Don’t listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the “enable-named-pipe” option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
#log-bin

# required unique id between 1 and 2^32 – 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) –
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master’s port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables’ values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 – 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave – required
#master-host =
#
# The username the slave will use for authentication when connecting
# to the master – required
#master-user =
#
# The password the slave will authenticate with when connecting to
# the master – required
#master-password =
#
# The port the master is listening on.
# optional – defaults to 3306
#master-port =
#
# binary logging – not required for slaves, but recommended
#log-bin

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 – 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

补充

优化table_cachetable_cache指定表高速缓存的大小。每当MySQL访问一个表时,如果在表缓冲区中还有空间,该表就被打开并放入其中,这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)。注意,不能盲目地把table_cache设置成很大的值。如果设置得太高,可能会造成文件描述符不足,从而造成性能不稳定或者连接失败。对于有1G内存的机器,推荐值是128-256。

案例1:该案例来自一个不是特别繁忙的服务器table_cache – 512open_tables – 103opened_tables – 1273uptime – 4021421 (measured in seconds)该案例中table_cache似乎设置得太高了。在峰值时间,打开表的数目比table_cache要少得多。

案例2:该案例来自一台开发服务器。table_cache – 64open_tables – 64opened-tables – 431uptime – 1662790 (measured in seconds)虽然open_tables已经等于table_cache,但是相对于服务器运行时间来说,opened_tables的值也非常低。因此,增加table_cache的值应该用处不大。案例3:该案例来自一个upderperforming的服务器table_cache – 64open_tables – 64opened_tables – 22423uptime – 19538该案例中table_cache设置得太低了。虽然运行时间不到6小时,open_tables达到了最大值,opened_tables的值也非常高。这样就需要增加table_cache的值。优化key_buffer_sizekey_buffer_size指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)。

案例1:健康状况key_buffer_size – 402649088 (384M)key_read_requests – 597579931key_reads – 56188案例2:警报状态key_buffer_size – 16777216 (16M)key_read_requests – 597579931key_reads – 53832731案例1中比例低于1:10000,是健康的情况;案例2中比例达到1:11,警报已经拉响。

mysql服务性能优化—my.cnf配置说明详解(16G内存)

2015年12月3日 没有评论

MYSQL服务器my.cnf配置文档详解
硬件:内存16G

[client]
port = 3306
socket = /data/3306/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
open_files_limit = 10240

back_log = 600
#在MYSQL暂时停止响应新请求之前,短时间内的多少个请求可以被存在堆栈中。如果系统在短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的监听队列的大小。默认值50。

max_connections = 3000
#MySQL允许最大的进程连接数,如果经常出现Too Many Connections的错误提示,则需要增大此值。

max_connect_errors = 6000
#设置每个主机的连接请求异常中断的最大次数,当超过该次数,MYSQL服务器将禁止host的连接请求,直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。

table_cache = 614
#指示表调整缓冲区大小。# table_cache 参数设置表高速缓存的数目。每个连接进来,都会至少打开一个表缓存。#因此, table_cache 的大小应与 max_connections 的设置有关。例如,对于 200 个#并行运行的连接,应该让表的缓存至少有 200 × N ,这里 N 是应用可以执行的查询#的一个联接中表的最大数量。此外,还需要为临时表和文件保留一些额外的文件描述符。
# 当 Mysql 访问一个表时,如果该表在缓存中已经被打开,则可以直接访问缓存;如果#还没有被缓存,但是在 Mysql 表缓冲区中还有空间,那么这个表就被打开并放入表缓#冲区;如果表缓存满了,则会按照一定的规则将当前未用的表释放,或者临时扩大表缓存来存放,使用表缓存的好处是可以更快速地访问表中的内容。执行 flush tables 会#清空缓存的内容。一般来说,可以通过查看数据库运行峰值时间的状态值 Open_tables #和 Opened_tables ,判断是否需要增加 table_cache 的值(其中 open_tables 是当#前打开的表的数量, Opened_tables 则是已经打开的表的数量)。即如果open_tables接近table_cache的时候,并且Opened_tables这个值在逐步增加,那就要考虑增加这个#值的大小了。还有就是Table_locks_waited比较高的时候,也需要增加table_cache。

external-locking = FALSE
#使用–skip-external-locking MySQL选项以避免外部锁定。该选项默认开启

max_allowed_packet = 32M
#设置在网络传输中一次消息传输量的最大值。系统默认值 为1MB,最大值是1GB,必须设置1024的倍数。

sort_buffer_size = 2M
# Sort_Buffer_Size 是一个connection级参数,在每个connection(session)第一次需要使用这个buffer的时候,一次性分配设置的内存。
#Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。例如:500个连接将会消耗 500*sort_buffer_size(8M)=4G内存
#Sort_Buffer_Size 超过2KB的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
#技术导读 http://blog.webshuo.com/2011/02/16/mysql-sort_buffer_size/
#dev-doc: http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html
#explain select*from table where order limit;出现filesort
#属重点优化参数

join_buffer_size = 2M
#用于表间关联缓存的大小,和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

thread_cache_size = 300
# 服务器线程缓存这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过比较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作用。设置规则如下:1GB 内存配置为8,2GB配置为16,3GB配置为32,4GB或更高内存,可配置更大。

thread_concurrency = 8
# 设置thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那么thread_concurrency的应该为4; 2个双核的cpu, thread_concurrency的值应为8
#属重点优化参数

query_cache_size = 64M
## 对于使用MySQL的用户,对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中,这个参数也是一个重要的优化参数。但随着发展,这个参数也爆露出来一些问题。机器的内存越来越大,人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下 query_cache_size的工作原理:一个SELECT查询在DB中工作后,DB会把该语句缓存下来,当同样的一个SQL再次来到DB里调用时,DB在该表没发生变化的情况下把结果从缓存中返回给Client。这里有一个关建点,就是DB在利用Query_cache工作时,要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时,Query_cache里的数据又怎么处理呢?首先要把Query_cache和该表相关的语句全部置为失效,然后在写入更新。那么如果Query_cache非常大,该表的查询结构又比较多,查询语句失效也慢,一个更新或是Insert就会很慢,这样看到的就是Update或是Insert怎么这么慢了。所以在数据库写入量或是更新量也比较大的系统,该参数不适合分配过大。而且在高并发,写入量大的系统,建议把该功能禁掉。
#重点优化参数(主库 增删改-MyISAM)

query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小,缺省为1M

query_cache_min_res_unit = 2k
#默认是4KB,设置值大对大数据查询有好处,但如果你的查询都是小数据查询,就容易造成内存碎片和浪费
#查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
#如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。
#查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
#查询缓存利用率在25%以下的话说明query_cache_size设置的过大,可适当减小;查询缓存利用率在80%以上而且Qcache_lowmem_prunes > 50的话说明query_cache_size可能有点小,要不就是碎片太多。
#查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

default-storage-engine = MyISAM
#default_table_type = InnoDB

thread_stack = 192K
#设置MYSQL每个线程的堆栈大小,默认值足够大,可满足普通操作。可设置范围为128K至4GB,默认为192KB。

transaction_isolation = READ-COMMITTED
# 设定默认的事务隔离级别.可用的级别如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# 1.READ UNCOMMITTED-读未提交2.READ COMMITTE-读已提交3.REPEATABLE READ -可重复读4.SERIALIZABLE -串行

tmp_table_size = 256M
# tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。如果超过该值,则会将临时表写入磁盘。
max_heap_table_size = 256M
long_query_time = 2
log_long_format
log-slow-queries=/data/3306/slow-log.log
#log-bin = /data/3306/mysql-bin
log-bin
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 512M

expire_logs_days = 7
key_buffer_size = 2048M
#批定用于索引的缓冲区大小,增加它可以得到更好的索引处理性能,对于内存在4GB左右的服务器来说,该参数可设置为256MB或384MB。

read_buffer_size = 1M
# MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且你认为频繁扫描进行得太慢,可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样,该参数对应的分配内存也是每个连接独享。

read_rnd_buffer_size = 16M
# MySql的随机读(查询操作)缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySql会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。

bulk_insert_buffer_size = 64M
#批量插入数据缓存大小,可以有效提高插入效率,默认为8M

myisam_sort_buffer_size = 128M
# MyISAM表发生变化时重新排序所需的缓冲

myisam_max_sort_file_size = 10G
# MySQL重建索引时所允许的最大临时文件的大小 (当 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
# 如果文件大小比此值更大,索引会通过键值缓冲创建(更慢)

myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
# 如果一个表拥有超过一个索引, MyISAM 可以通过并行排序使用超过一个线程去修复他们.
# 这对于拥有多个CPU以及大量内存情况的用户,是一个很好的选择.

myisam_recover
#自动检查和修复没有适当关闭的 MyISAM 表
skip-name-resolve
lower_case_table_names = 1

server-id = 1

innodb_additional_mem_pool_size = 16M
#这个参数用来设置 InnoDB 存储的数据目录信息和其它内部数据结构的内存池大小,类似于Oracle的library cache。这不是一个强制参数,可以被突破。

innodb_buffer_pool_size = 2048M
# 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大了

innodb_data_file_path = ibdata1:1024M:autoextend
#表空间文件 重要数据

innodb_file_io_threads = 4
#文件IO的线程数,一般为 4,但是在 Windows 下,可以设置得较大。

innodb_thread_concurrency = 8
#服务器有几个CPU就设置为几,建议用默认设置,一般为8.

innodb_flush_log_at_trx_commit = 2
# 如果将此参数设置为1,将在每次提交事务后将日志写入磁盘。为提供性能,可以设置为0或2,但要承担在发生故障时丢失数据的风险。设置为0表示事务日志写入日志文件,而日志文件每秒刷新到磁盘一次。设置为2表示事务日志将在提交时写入日志,但日志文件每次刷新到磁盘一次。

innodb_log_buffer_size = 16M
#此参数确定些日志文件所用的内存大小,以M为单位。缓冲区更大能提高性能,但意外的故障将会丢失数据.MySQL开发人员建议设置为1-8M之间

innodb_log_file_size = 128M
#此参数确定数据日志文件的大小,以M为单位,更大的设置可以提高性能,但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3
#为提高性能,MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3M

innodb_max_dirty_pages_pct = 90
#推荐阅读 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html
# Buffer_Pool中Dirty_Page所占的数量,直接影响InnoDB的关闭时间。参数innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率,而且幸运的是innodb_max_dirty_pages_pct是可以动态改变的。所以,在关闭InnoDB之前先将innodb_max_dirty_pages_pct调小,强制数据块Flush一段时间,则能够大大缩短 MySQL关闭的时间。

innodb_lock_wait_timeout = 120
# InnoDB 有其内置的死锁检测机制,能导致未完成的事务回滚。但是,如果结合InnoDB使用MyISAM的lock tables 语句或第三方事务引擎,则InnoDB无法识别死锁。为消除这种可能性,可以将innodb_lock_wait_timeout设置为一个整数值,指示 MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)

innodb_file_per_table = 0
#独享表空间(关闭)

[mysqldump]
quick
max_allowed_packet = 32M

[mysqld_safe]
log-error=/data/3306/mysql_oldboy.err
pid-file=/data/3306/mysqld.pid

#补充
#wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右的内存服务器来说,可以将其设置为5-10。
#skip_networking
#开启该选可以彻底关闭MYSQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MYSQL数据库服务器的,则不要开启该选项,否则将无法正常连接。

#log-queries-not-using-indexes
将没有使用索引的查询也记录下来

双机高可用、负载均衡、MySQL(读写分离、主从自动切换)架构设计(转)

2015年8月5日 没有评论

架构简介

前几天网友来信说帮忙实现这样一个架构:只有两台机器,需要实现其中一台死机之后另一台能接管这台机器的服务,并且在两台机器正常服务时,两台机器都能用上。于是设计了如下的架构。

此架构主要是由keepalived实现双机高可用,维护了一个外网VIP,一个内网VIP。正常情况时,外网VIP和内网VIP都绑定在server1服务器,web请求发送到server1的nginx,nginx对于静态资源请求就直接在本机检索并返回,对于php的动态请求,则负载均衡到server1和server2。对于SQL请求,会将此类请求发送到Atlas MySQL中间件,Atlas接收到请求之后,把涉及写操作的请求发送到内网VIP,读请求操作发送到mysql从,这样就实现了读写分离。

当主服务器server1宕机时,keepalived检测到后,立即把外网VIP和内网VIP绑定到server2,并把server2的mysql切换成主库。此时由于外网VIP已经转移到了server2,web请求将发送给server2的nginx。nginx检测到server1宕机,不再把请求转发到server1的php-fpm。之后的sql请求照常发送给本地的atlas,atlas把写操作发送给内网VIP,读操作发送给mysql从,由于内网VIP已经绑定到server2了,server2的mysql同时接受写操作和读操作。

当主服务器server1恢复后,server1的mysql自动设置为从,与server2的mysql主同步。keepalived不抢占server2的VIP,继续正常服务。

架构要求

要实现此架构,需要三个条件:
1、服务器可以设置内网IP,并且设置的内网IP互通;
2、服务器可以随意绑定IDC分配给我们使用的外网IP,即外网IP没有绑定MAC地址;
3、MySQL服务器支持GTID,即MySQL-5.6.5以上版本。

环境说明

server1
eth0: 10.96.153.110(对外IP)
eth1: 192.168.1.100(对内IP)
server2
eth0: 10.96.153.114(对外IP)
eth1: 192.168.1.101(对内IP)
系统都是CentOS-6。

对外VIP: 10.96.153.239
对内VIP: 192.168.1.150

hosts设置

/etc/hosts:
192.168.1.100 server1
192.168.1.101 server2

Nginx PHP MySQL Memcached安装

这几个软件的安装推荐使用EZHTTP来完成。

解决session共享问题

php默认的session存储是在/tmp目录下,现在我们是用两台服务器作php请求的负载,这样会造成session分布在两台服务器的/tmp目录下,导致依赖于session的功能不正常。我们可以使用memcached来解决此问题。
上一步我们已经安装好了memcached,现在只需要配置php.ini来使用memcached,配置如下,打开php.ini配置文件,修改为如下两行的值:
session.save_handler = memcache
session.save_path = “tcp://192.168.1.100:11211,tcp://192.168.1.101:11211″
之后重启php-fpm生效。

Nginx配置

Server1配置
http {
[...]
upstream php-server {
server 192.168.1.101:9000;
server 127.0.0.1:9000;
keepalive 100;
}
[...]
server {
[...]
location ~ \.php$ {
fastcgi_pass php-server;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
[...]
}
[...]
}

Server2配置
http {
[...]
upstream php-server {
server 192.168.1.100:9000;
server 127.0.0.1:9000;
keepalive 100;
}
[...]
server {
[...]
location ~ \.php$ {
fastcgi_pass php-server;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
[...]
}
[...]
}
这两个配置主要的作用是设置php请求的负载均衡。

MySQL配置

mysql util安装
我们需要安装mysql util里的主从配置工具来实现主从切换。
cd /tmp
wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.3.tar.gz
tar xzf mysql-utilities-1.5.3.tar.gz
cd mysql-utilities-1.5.3
python setup.py build
python setup.py install

mysql my.cnf配置
server1:
[mysql]
[...]
protocol=tcp
[...]
[...]
[mysqld]
[...]
# BINARY LOGGING #
log-bin = /usr/local/mysql/data/mysql-bin
expire-logs-days = 14
binlog-format= row
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency =true
master-info-repository=TABLE
relay-log-info-repository=TABLE
server-id=1
report-host=server1
report-port=3306
[...]
server2:
[mysql]
[...]
protocol=tcp
[...]
[mysqld]
[...]
# BINARY LOGGING #
log-bin = /usr/local/mysql/data/mysql-bin
expire-logs-days = 14
binlog-format= row
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency =true
master-info-repository=TABLE
relay-log-info-repository=TABLE
server-id=2
report-host=server2
report-port=3306
[...]
这两个配置主要是设置了binlog和启用gtid-mode,并且需要设置不同的server-id和report-host。

开放root帐号远程权限
我们需要在两台mysql服务器设置root帐号远程访问权限。
mysql> grant all on *.* to ‘root’@’192.168.1.%’ identified by ‘Xp29at5F37′ with grant option;
mysql> grant all on *.* to ‘root’@'server1′ identified by ‘Xp29at5F37′ with grant option;
mysql> grant all on *.* to ‘root’@'server2′ identified by ‘Xp29at5F37′ with grant option;
mysql> flush privileges;

设置mysql主从
在任意一台执行如下命令:
mysqlreplicate –master=root:Xp29at5F37@server1:3306 –slave=root:Xp29at5F37@server2:3306 –rpl-user=rpl:o67DhtaW
# master on server1: … connected.
# slave on server2: … connected.
# Checking for binary logging on master…
# Setting up replication…
# …done.

显示主从关系
mysqlrplshow –master=root:Xp29at5F37@server1 –discover-slaves-login=root:Xp29at5F37
# master on server1: … connected.
# Finding slaves for master: server1:3306
# Replication Topology Graph
server1:3306 (MASTER)
|
+— server2:3306 – (SLAVE)

检查主从状态
mysqlrplcheck –master=root:Xp29at5F37@server1 –slave=root:Xp29at5F37@server2
# master on server1: … connected.
# slave on server2: … connected.
Test Description Status
—————————————————————————
Checking for binary logging on master [pass]
Are there binlog exceptions? [pass]
Replication user exists? [pass]
Checking server_id values [pass]
Checking server_uuid values [pass]
Is slave connected to master? [pass]
Check master information file [pass]
Checking InnoDB compatibility [pass]
Checking storage engines compatibility [pass]
Checking lower_case_table_names settings [pass]
Checking slave delay (seconds behind master) [pass]
# …done.

Keepalived配置

keepalived安装(两台都装)
yum -y install keepalived
chkconfig keepalived on

keepalived配置(server1)
vi /etc/keepalived/keepalived.conf
vrrp_sync_group VG_1 {
group {
inside_network
outside_network
}
}

vrrp_instance inside_network {
state BACKUP
interface eth1
virtual_router_id 51
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
192.168.1.150/24
}
nopreempt
notify /data/sh/mysqlfailover-server1.sh
}

vrrp_instance outside_network {
state BACKUP
interface eth0
virtual_router_id 50
priority 101
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
10.96.153.239/24
}
nopreempt
}

keepalived配置(server2)
vrrp_sync_group VG_1 {
group {
inside_network
outside_network
}
}

vrrp_instance inside_network {
state BACKUP
interface eth1
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
192.168.1.150
}
notify /data/sh/mysqlfailover-server2.sh
}

vrrp_instance outside_network {
state BACKUP
interface eth0
virtual_router_id 50
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 3489
}
virtual_ipaddress {
10.96.153.239/24
}
}
此keepalived配置需要注意的是:
1、两台server的state都设置为backup,server1增加nopreempt配置,并且server1 priority比server2高,这样用来实现当server1从宕机恢复时,不抢占VIP;
2、server1设置notify /data/sh/mysqlfailover-server1.sh,server2设置notify /data/sh/mysqlfailover-server2.sh,作用是自动切换主从
/data/sh/mysqlfailover-server1.sh脚本内容:
#!/bin/bash

sleep 10
state=$3
result=`mysql -h127.0.0.1 -P3308 -uroot -pXp29at5F37 -e ‘show slave status;’`
[[ "$result" == "" ]] && mysqlState=”master” || mysqlState=”slave”

if [[ "$state" == "MASTER" ]];then
if [[ "$mysqlState" == "slave" ]];then
mysqlrpladmin –slave=root:Xp29at5F37@server1:3308 failover
fi

elif [[ "$state" == "BACKUP" ]];then
if [[ "$mysqlState" == "master" ]];then
mysqlreplicate –master=root:Xp29at5F37@server2:3308 –slave=root:Xp29at5F37@server1:3308 –rpl-user=rpl:o67DhtaW
fi
fi

sed -i ‘s/proxy-read-only-backend-addresses.*/proxy-read-only-backend-addresses = 192.168.1.150:3308/’ /usr/local/mysql-proxy/conf/my.cnf
mysql -h127.0.0.1 -P2345 -uuser -ppwd -e “REMOVE BACKEND 2;”
/data/sh/mysqlfailover-server2.sh脚本内容:
#!/bin/bash

sleep 10
state=$3
result=`mysql -h127.0.0.1 -P3308 -uroot -pXp29at5F37 -e ‘show slave status;’`
[[ "$result" == "" ]] && mysqlState=”master” || mysqlState=”slave”

if [[ "$state" == "MASTER" ]];then
if [[ "$mysqlState" == "slave" ]];then
mysqlrpladmin –slave=root:Xp29at5F37@server2:3308 failover
fi

elif [[ "$state" == "BACKUP" ]];then
if [[ "$mysqlState" == "master" ]];then
mysqlreplicate –master=root:Xp29at5F37@server1:3308 –slave=root:Xp29at5F37@server2:3308 –rpl-user=rpl:o67DhtaW
fi
fi

sed -i ‘s/proxy-read-only-backend-addresses.*/proxy-read-only-backend-addresses = 192.168.1.150:3308/’ /usr/local/mysql-proxy/conf/my.cnf
mysql -h127.0.0.1 -P2345 -uuser -ppwd -e “REMOVE BACKEND 2;”

Atlas设置

atlas安装
到这里下载最新版本,https://github.com/Qihoo360/Atlas/releases
cd /tmp
wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
rpm -i Atlas-2.2.1.el6.x86_64.rpm

atlas配置
cd /usr/local/mysql-proxy/conf
cp test.cnf my.cnf
vi my.cnf
调整如下参数,
proxy-backend-addresses = 192.168.1.150:3306
proxy-read-only-backend-addresses = 192.168.1.101:3306
pwds = root:qtyU1btXOo074Itvx0UR9Q==
event-threads = 8
注意:
proxy-backend-addresse设置为内网VIP
proxy-read-only-backend-addresses设置为server2的IP
root:qtyU1btXOo074Itvx0UR9Q==设置数据库的用户和密码,密码是通过/usr/local/mysql-proxy/bin/encrypt Xp29at5F37生成。
更详细参数解释请查看,Atlas配置详解。

启动atlas
/usr/local/mysql-proxy/bin/mysql-proxy –defaults-file=/usr/local/mysql-proxy/conf/my.cnf
之后程序里配置mysql就配置127.0.0.1:1234就好。

部署atlas自动维护脚本
在两台机器都部署此脚本,并添加定时任务(如每2分钟运行一次)我们把脚本放在/data/sh/auto_maintain_atlas.sh,脚本内容为:
#!/bin/bash

count=`mysql -N -h127.0.0.1 -P2345 -uuser -ppwd -e “select * from backends;” | wc -l`

if [[ "$count" == "1" ]];then
result=`mysql -hserver1 -P3308 -uroot -pXp29at5F37 -e ‘show slave status\G’`
if echo “$result” | grep Slave_IO_State;then
slaveIP=192.168.1.100
else
result=`mysql -hserver2 -P3308 -uroot -pXp29at5F37 -e ‘show slave status\G’`
slaveIP=192.168.1.101
fi

slaveIORunning=`echo “$result” | awk -F’:’ ‘/Slave_IO_Running:/{print $2}’`
slaveSQLRunning=`echo “$result” | awk -F’:’ ‘/Slave_SQL_Running:/{print $2}’`
SlaveSQLRunning_State=`echo “$result” | awk -F’:’ ‘/Slave_SQL_Running_State:/{print $2}’`
if [[ "$slaveIORunning" =~ "Yes" && "$slaveSQLRunning" =~ "Yes" && "$SlaveSQLRunning_State" =~ "Slave has read all relay log" ]];then
mysql -h127.0.0.1 -P2345 -uuser -ppwd -e “add slave ${slaveIP}:3308;”
fi
fi
为什么需要这个脚本呢?假设目前mysql主服务器在s1,s1宕机后,s2接管VIP,接着删除atlas中设置的slave backend,其mysql提升为主。过一段时间后,s1从宕机中恢复,这时候s1的mysql自动切换为从,接着删除atlas中设置的slave backend,开始连接s2的mysql主同步数据。到这个时候我们发现,已经不存在读写分离了,所有的sql都发送给了s2的mysql。auto_maintain_atlas.sh脚本就派上用场了,此脚本会定时的检查主从是否已经同步完成,如果完成就自动增加slave backend,这样读写分离又恢复了,完全不需要人工干预。

server1主宕机测试

测试keepalived是否工作正常
我们来模拟server1宕机。
在server1上执行shutdown关机命令。
此时我们登录server2,执行ip addr命令,输出如下:
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:81:9d:42 brd ff:ff:ff:ff:ff:ff
inet 10.96.153.114/24 brd 10.96.153.255 scope global eth0
inet 10.96.153.239/24 scope global secondary eth0
inet6 fe80::20c:29ff:fe81:9d42/64 scope link
valid_lft forever preferred_lft forever
3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:81:9d:4c brd ff:ff:ff:ff:ff:ff
inet 192.168.1.101/24 brd 192.168.1.255 scope global eth1
inet 192.168.1.150/32 scope global eth1
inet6 fe80::20c:29ff:fe81:9d4c/64 scope link
valid_lft forever preferred_lft forever
我们看到对外VIP 10.96.153.239和对内IP 192.168.1.150已经转移到server2了,证明keepalived运行正常。

测试是否自动切换了主从
登录server2的mysql服务器,执行show slave status;命令,如下:
mysql> show slave status\G
Empty set (0.00 sec)
我们发现从状态已经为空,证明已经切换为主了。

测试server1是否抢占VIP
为什么要测试这个呢?如果server1恢复之后抢占了VIP,而我们的Atlas里后端设置的是VIP,这样server1启动之后,sql的写操作就会向server1的mysql发送,而server1的mysql数据是旧于server2的,所以这样会造成数据不一致,这个是非常重要的测试。
我们先来启动server1,之后执行ip addr,输出如下:
1: lo: mtu 16436 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:f1:4f:4e brd ff:ff:ff:ff:ff:ff
inet 10.96.153.110/24 brd 10.96.153.255 scope global eth0
inet6 fe80::20c:29ff:fef1:4f4e/64 scope link
valid_lft forever preferred_lft forever
3: eth1: mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:f1:4f:58 brd ff:ff:ff:ff:ff:ff
inet 192.168.1.100/24 brd 192.168.1.255 scope global eth1
inet6 fe80::20c:29ff:fef1:4f58/64 scope link
valid_lft forever preferred_lft forever
我们看到,server1并没有抢占VIP,测试正常。不过另人郁闷的是,在虚拟机的环境并没有测试成功,不知道为什么。

测试server2的atlas是否已经删除slave backend
我们测试这个是为了保证atlas已经没有slave backend,也就是没有从库的设置了,否则当server1恢复时,有可能会把读请求发送给server1的mysql,造成读取了旧数据的问题。

[root@server1 ~]# mysql -h127.0.0.1 -P2345 -uuser -ppwd
mysql> select * from backends;
+————-+——————–+——-+——+
| backend_ndx | address | state | type |
+————-+——————–+——-+——+
| 1 | 192.168.1.150:3308 | up | rw |
+————-+——————–+——-+——+
1 rows in set (0.00 sec)
如果看到只有一个后端,证明运作正常。

测试server1 mysql是否设置为从
serve1恢复后,登录server1的mysql服务器,执行show slave status;命令,如下:

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Opening tables
Master_Host: server1
Master_User: rpl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 48405991
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: yes

测试是否自动恢复读写分离
server1恢复后一段时间,我们可以看是读写分离是否已经恢复。

[root@server1 ~]# mysql -h127.0.0.1 -P2345 -uuser -ppwd
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select * from backends;
+————-+——————–+——-+——+
| backend_ndx | address | state | type |
+————-+——————–+——-+——+
| 1 | 192.168.1.150:3308 | up | rw |
| 2 | 192.168.1.100:3308 | up | ro |
+————-+——————–+——-+——+
2 rows in set (0.00 sec)

我们看到server1已经被添加为slave backend了。这表示已经成功恢复读写分离。

XAMPP php版本历史

2015年1月18日 没有评论

XAMPP版本历史如图

http://code.stephenmorley.org/articles/xampp-version-history-apache-mysql-php/

XAMPP Apache MySQL PHP 5 PHP 4
1.8.1 2.4.3 5.5.27 5.4.7
1.8.0 2.4.2 5.5.25a 5.4.4
1.7.7 2.2.21 5.5.16 5.3.8
1.7.5 2.2.21 5.5.15 5.3.8
1.7.4 2.2.17 5.5.8 5.3.5
1.7.3 2.2.14 5.1.41 5.3.1
1.7.2 2.2.12 5.1.37 5.3.0
1.7.1 2.2.11 5.1.33 5.2.9
1.7.0 2.2.11 5.1.30 5.2.8
1.6.8 2.2.9 5.0.67 5.2.6 4.4.9
1.6.7 2.2.9 5.0.51b 5.2.6 4.4.8
1.6.6a 2.2.8 5.0.51a 5.2.5 4.4.8
1.6.6 2.2.8 5.0.51 5.2.5 4.4.8 (RC2)
1.6.5 2.2.6 5.0.51 5.2.5 4.4.7
1.6.4 2.2.6 5.0.45 5.2.4 4.4.7
1.6.3a 2.2.4 5.0.45 5.2.3 4.4.7
1.6.3 2.2.4 5.0.54 5.2.3 4.4.7
1.6.2 2.2.4 5.0.41 5.2.2 4.4.7
1.6.1 2.2.4 5.0.37 5.2.1 4.4.6
1.6.0a 2.2.4 5.0.33 5.2.1 4.4.5
1.6.0 2.2.3 5.0.33 5.2.1 4.4.5

分类: Linux 标签: , , ,