高可用Mysql架构_主从复制、双主热备、双主双从、读写分离（Mycat中间件）、分库分表架构（Mycat中间件）的演变

「爱情、让人受尽委屈。」 2021-10-19 05:28 321阅读 0赞

【Mysql主从复制】  
解决的问题  
数据分布：比如一共150台机器，分别往电信、网通、移动各放50台，这样无论在哪个网络访问都很快。其次按照地域，比如国内国外，北方南方，这样地域性访问解决了。  
负载均衡：Mysql读写分离，读写分开了，解决了部分服务器的压力，均衡分开。  
数据备份：比如100台机器，实际数据是一样的，这样可以说每台机器都是数据备份。  
高可用性和容错性：1台机器挂掉了无所谓，因为还有99台机器。  
实现原理：  
Mysql支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或者多个其他服务器充当从服务器  
Mysql复制基于主服务器在二进制日志中跟踪所有对数据库的更改等操作。其实就是存储的SQL语句，只不过被二进制化。  
每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存和更新。比如主服务器执行了插入操作，那么从服务器你就会把这个指令拿过来，在它自己的服务器执行插入操作。  
实现步骤：  
Master将改变日志记录到二进制日志中（binary log）, 再然后Slave重做中继日志中的事件，将改变反应它自己的数据，也就是执行一遍日志。

流程图解说：

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTEzMDAxNDgtMTI0MDI3MjgxLnBuZw][]

首先，如果主机有数据改变，会写到主机的bin-log日志中，然后从机会监听主机的bin-log日志，这时候会读主机的bin-log，然后写入自己（从机）的relay-log（中继日志）中，然后再从中继日志中读出来执行SQL事件。这样就会执行了主机的SQL操作。

开始搭建主从复制

192.168.9.164 主服务器，读操作在164  
192.168.9.165 从服务器，写操作在165  
因为主需要开启bin-log，从服务器需要监听主服务器的bin-log，那么首先去主服务器开启bin-log。

先编辑主服务器164的Mysql配置文件

#vim /etc/my.cnf  //对照下面三行
    log_bin = mysql-bin #是开启的
    binlog_format = mixed #日志文件名称
    Server-id = 164 #以为server-id是惟一的，用ip最合适。

先去mysql数据的存放目录，如果不知道的情况下，可以在/etc/my.cnf中查看 datadir的位置datadir = /data/mysql

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxMTUzNTc2OTItOTU3ODI3NTA1LnBuZw][]

看到这些文件，就是mysql的binlog文件，说明bin-log已经开启，重启Mysql服务。

然后进入主服务器客户端，执行命令查看主服务器状态

MySQL [(none)]> show master status;

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxMTUzMzgxMzEtMTI0Mzk0NDgzNC5wbmc][]

然后配置从服务器：

同样编辑/etc/my.cnf文件，把server-id修改一下，暂时把两个选项注释掉，还需要加一个relay-log（中继日志）

#vim /etc/my.cnf
    #log_bin = mysql-bin  
    #binlog_format = mixed 
    relay_log = mysql-relay-bin
    Server-id = 165  #以为server-id是惟一的，用ip最合适。

配置为重启Mysql。

下面就需要让从服务器监听主服务器的bin-log日志文件，这样需要做的话就是主服务器允许授权。先去主服务器授权，回到164主服务器mysql客户端执行命令授权，

MySQL [(none)]> grant replication slave on *.* to slave@192.168.9.165 identified by '123456';
    MySQL [(none)]> flush privileges; //刷新权限

这句话的意思是，允许slave这个用户通过密码123456在192.168.9.165这台服务器上访问164这台master。这时候就可以允许165监听164的bin-log了。

进入从服务器，先确定从服务器是否能ping的通主服务器。然后进入从服务器客户端，设定要监听的机器。进入从服务器mysql客户端习惯性执行 stop slave 命令。

然后准备在从服务器mysql客户端执行授权命令，but，在执行之前先看看主服务器的状态

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxMjAwMDc5OTgtODU4OTQzMTM1LnBuZw][]

这里由154变成了602，因为刚才在主服务器执行过命令。

MySQL [(none)]> change master to
        -> master_host='192.168.9.164',
        -> master_port=3306,
        -> master_user='slave',
        -> master_password='123456',
        -> master_log_file='mysql-bin.000001',
        -> master_log_pos=450;
    
    MySQL [(none)]> start slave;  //OK没问题的话，开启监听。

MySQL [(none)]> show slave status\G; //查看状态

*************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.9.164
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000005
              Read_Master_Log_Pos: 450
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               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: 629
                  Relay_Log_Space: 527
                  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: d8d23a1f-341f-11e8-8102-000c29f3aa69
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               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
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

然后在主服务器做相应的Mysql增删改，同样再查看一下从服务器的数据，都发生了相应的变化~

检查主从复制通信状态

Slave\_IO\_State \#从站的当前状态   
Slave\_IO\_Running： Yes \#读取主程序二进制日志的I/O线程是否正在运行   
Slave\_SQL\_Running： Yes \#执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样   
Seconds\_Behind\_Master \#是否为0，0就是已经同步了

必须都是 Yes

如果不是原因主要有以下 4 个方面：

1、网络不通   
2、密码不对   
3、MASTER\_LOG\_POS 不对 ps   
4、mysql 的 auto.cnf server-uuid 一样（可能你是复制的mysql）

$ find / -name 'auto.cnf'
    $ cat /data/mysql/auto.cnf
    [auto]
    server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照这个16进制格式，修改server-uuid，重启mysql即可

OK，这样主从复制的架构就配置完成了。但是别着急，接着往下看。

**现有架构问题**

现有架构问题有一个Master，可以扩展多个Slave，如果Master挂掉，那么影响现有的架构，如果Master一旦挂掉，写数据不知道往哪里写了，Slave也会失去监听的Master。

**解决问题**  
给现有的Master再去备份另外的Master，这可以实现心跳感应，其中一个Master挂掉，切换到另外一个Master。

【Mysql双主热备】

流程图解说：

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTMyMjAzNzctMTA1Njk2NjAwMy5wbmc][]

现在要做的是让Slave也作为Master，首先给Slave也开启bin-log，也就是做主机的话，必须开启bin-log。而且要开始log\_slave\_updates，这个配置代表的是，当我们的relay\_log发生改变的时候，它会通过log\_slave\_updates写入到从机的bin-log日志中，也就是说实现了Slave中的bin-log和Master的bin-log日志同步，保证数据一致。然后再让Master监听Slave的bin-log，这样的话必须Master也必须开启relay-log，让它作为Slave的从，这样Master和Slave互为主从。这样我们往其中任意一台数据库写数据，另外一个数据也会发生相应改变，其中一个挂掉，我们另外一个机器可以作为主机提供服务，最终达到双主热备。

达到的架构图：

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTMzMDQyMDMtMjAxMTM1MzE1Mi5wbmc][]

两个Master，双主。然后各自的Master都有各自的Slave集群，其中一个Master挂掉，另一个Master可以提供服务，并且下面还有很多Slave，然后可以将挂掉的Master下面的Slave切换到正常的Mater上。

开始搭建双主热备

现在要做的是把Slave作为主，需要开启Slave的bin-log，编辑从机的配置文件

#vim /etc/my.cnf   //按照下面的配置
    log_bin = mysql-bin 
    binlog_format = mixed 
    relay_log = mysql-relay-bin
    log_slave_updates = 1 #表示将relay通过这样的配置写入到bin-log中，主挂掉，从立马作为主上位。
    Server-id = 165 #以为server-id是惟一的，用ip最合适。

配置完之后重启从机的Mysql服务，这样从就有作为主的能力。  
接下来给主机授权，Slave要让主监听从的bin-log，刚才是在主给从授权，现在是在从给主授权

grant replication slave on *.* to master@192.168.9.164 identified by 'wt000000';

授权完成之后，让主机监听从机  
编辑主机的配置文件，加上

# vim /etc/y.cnf   //按照下面的配置在主机加上
    relay_log = mysql-relay-bin
    log_slave_updates = 1 #同理

下面重启主的Mysql服务器。

接下来做的就是让之前的主来监听从了。  
进入从的Mysql终端，查看作为Master的状态，进入终端先执行reset master命令，再查看状态

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTUzMzIyMzItMTc4NjE3ODIyOC5wbmc][]

接下来就可以在192.168.9.164（之前的主机）上执行change master to命令了。

MySQL [(none)]> change master to 
        -> master_host='192.168.9.165',
        -> master_user='master',
        -> master_password='wt000000',
        -> master_log_file='mysql-bin.000001',
        -> master_log_pos=154;

此时，164就成了165的从了。然后执行命令

MySQL [(none)]> start slave;
    MySQL [(none)]> show slave status\G

MySQL [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.9.165
                      Master_User: master
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 154
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes   //ok
                Slave_SQL_Running: Yes  //ok
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               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: 154
                  Relay_Log_Space: 527
                  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: 165
                      Master_UUID: 6b831bf3-8ae7-11e7-a178-000c29cb5cbc
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               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
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

现在两台分别是对方的主，也是对方的从。检查一下状态，没问题~

然后分别在主和从修改数据，每次的修改发现，两个服务器都会发生相应的变化。OK~双主热备配置完成~只有从还不是很完善，往下面接着看。

要实现每个主服务器下面都有从服务器。

【双主双从】  
192.168.9.164 //原Master(一主)  
192.168.9.165 //原Slave(二主)  
192.168.9.166 //做为192.168.9.164的从  
192.168.9.167 //做为192.168.9.165的从

分别编辑两个新从机的配置文件  
编辑192.168.9.166的Mysql配置文件

#og_bin = mysql-bin 
    #binlog_format = mixed 
    relay_log = mysql-relay-bin
    Server-id = 166 #以为server-id是惟一的，用ip最合适。

编辑192.168.9.167的Mysql配置文件

#log_bin = mysql-bin 
    #binlog_format = mixed 
    relay_log = mysql-relay-bin
    Server-id = 167 #以为server-id是惟一的，用ip最合适。

接下来分别给两个新从机授权

先让192.168.9.164给192.168.9.166授权。

进入192.168.9.164的Mysql客户端授权。

MySQL \[hb\]> grant replication slave on \*.\* to slave166@192.168.9.166 identified by 'wt000000';

然后查看本机的Master状态

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNjM5MjA4MzQtMTIwNjg0MDE0LnBuZw][]

然后进入从机192.168.9.166,执行change master 命令

MySQL [(none)]> change master to
        -> master_host='192.168.9.164',
        -> master_user='slave166',
        -> master_password='wt000000',
        -> master_log_file='mysql-bin.000005',
        -> master_log_pos=1034;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)

MySQL [(none)]> start slave;
    MySQL [(none)]> show slave status\G

MySQL [(none)]> show slave status\G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.9.164
                      Master_User: slave166
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000005
              Read_Master_Log_Pos: 1034
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000005
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               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: 1034
                  Relay_Log_Space: 527
                  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: 164
                      Master_UUID: d8d23a1f-341f-11e8-8102-000c29f3aa69
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               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
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)

再让192.168.9.165给192.168.9.167授权。

这里就不重复说明了。。。。

OK，每个主服务器的从服务器也搭建完成了。。经过测试，操作其中一台主机的数据库，其他三个服务器的数据库数据也跟着发生相应的变化~

【补充】

至此我们mysql服务器的主从复制架构已经完成，但是我们现在的主从架构并不完善，因为我们的从服务上还可以进行数据库的写入操作，一旦用户把数据写入到从服务器的数据库内，然后从服务器从主服务器上同步数据库的时候，会造成数据的错乱，从而会造成数据的损坏，所以我们需要把从服务器设置成只读~如果加入了Mycat中间件，在主从架构中，如果主库宕机的情况下，从库也要立马做主库角色，那么这里最好也是关掉。方法如下：

注意：read-only = ON ，这项功能只对非管理员组以为的用户有效！

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNzMyNTU0NTctMTI4MjM1ODYwMC5wbmc][]

上图查看出没有开启只读。那么我们修改从服务器的配置文件my.cnf，加一行代码

read-only = ON

添加完成之后重启服务即可。

注意，mysql主从架构中，从服务器要比主服务器速度慢是正常的，因为主服务器是并行的，从服务器是队列逐行执行命令操作。（特别是在事务操作中，首先会把执行事务的操作放到缓存区，然后执行成功之后才会把日志写入主服务器日志，然后主服务器把日志发送给从服务器，从服务器中继日志存储后，再逐行读取操作从服务器），Mysql默认是异步的。

事务安全配置：

比如，在主服务器上，某一个事务已经提交了，这个事务提交相关的二进制日志应该写进日志文件，二进制日志有缓冲区，意味着事务提交有些事件在缓冲区没有写进二进制日志，万一这个时候主服务器崩溃了，从服务器得不到相关的事件，那么从服务器实现不了将事务完整的结束，所以在主服务器完成的事务，从服务器有可能完成不了，如果我们能让主服务器的事务一提交，那么立即写到从服务器中，不在缓冲区停留，那么就会降低主从不一致的可能性。那么在主服务器怎么配置呢？

SHOW VARIABLES LIKE 'log%';

找到sync\_binlog,为了事务安全，启动该选项

**-----------------------------------------------------------**

**要注意的是，在确定完从库之后，要保证从服务器和主服务器的数据一致性，需要在备份主数据库数据之前，先临时锁定主数据库，保证数据一致性。**

flush tables with read lock;

**另外要注意的是，如果涉及到函数或者存储过程的复制，需要在/etc/my.cnf中的\[mysqld\]段中增加配置log\_bin\_trust\_function\_creators=true,或者在客户端设置set\_log\_bin\_trust\_function\_creators =1**

【Mysql读写分离】

配置读写分离在这里用的是中间件Mycat，下面简单介绍。官方地址：http://www.mycat.io/

Mycat特性  
支持SQL92标准  
支持Mysql、Oracle、DB2、SQL Server、PostgreSQL等数据库的常见SQL语法  
遵循Mysql原生协议，跨语言、跨平台夸数据库通用中间件代理  
基于心跳检查自动故障切换、支持读写分离、支持主从复制、支持分库分表等

下面开始在新的服务器下载安装Mycat，这里的IP是192.168.9.168。

下载安装Mycat

# cd /usr/local/src
    # wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
    # tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz 
    # mv mycat/ /usr/local/mycat

一步步配置Mycat

进入conf目录下，查看一下文件里列表，需要配置的仅仅是这两个文件

![aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDcyMDEzMTA2MzktMTk5ODQxODM1Ny5wbmc][]

# vim server.xml  //编辑server.xml文件，按照如下配置
    
    
            <user name="root">
                    <property name="password">123456</property>
                    <property name="schemas">mydb</property>
                    
                    
                    
            </user>

# vim schema.xml   //对应下面的配置
    
    
    
      <schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
            </schema>
            
            
            
            <dataNode name="dn1" dataHost="localhost1" database="hb4" />
            <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
                              writeType="0" dbType="mysql" dbDriver="native" switchType="2"  slaveThreshold="100">
                    <heartbeat>show slave status</heartbeat>
                    
                    
    
                    
                    
                    <writeHost host="hostM1" url="192.168.9.164:3306" user="mycat"  password="123456" >
                         <readHost host="hostS1" url="192.168.9.166:3306" user="mycat" password="123456" />
                    </writeHost>
    
                    
                    <writeHost host="hostM2" url="192.168.9.165:3306" user="mycat"  password="123456" >
                         <readHost host="hostS2" url="192.168.9.167:3306" user="mycat" password="123456" />
                    </writeHost>
    
                    
            </dataHost>

配置完成之后开启mycat服务

[root@localhost mycat]# bin/mycat start  //命令启动
    Starting Mycat-server...
    [root@localhost mycat]# bin/mycat status  //命令查看状态
    Mycat-server is running (3593).
    [root@localhost mycat]# netstat -tunlp | grep 8066  //查看端口
    tcp        0      0 :::8066                     :::*                        LISTEN      3595/java

授权配置

MySQL [hb4]> grant insert,update,delete,select on *.* to mycat@192.168.9.168 identified by '123456';   //在两台主服务器的Mysql终端分别执行

MySQL [hb4]> grant select on *.* to mycat@192.168.9.168 identified by '123456';  　　　　　　　　　　　　//在两台从服务器Mysql终端分别执行

确定启动成功，接下来通过项目开发机链接一下试试

# mysql -uroot -p123456 -h192.168.9.168 -P8066

MySQL [(none)]> show databases;
    +----------+
    | DATABASE |
    +----------+
    | mydb     |
    +----------+
    1 row in set (0.01 sec)

然后在之前的主机上操作数据表，然后通过此链接继续查看数据表，数据也发生了相应的变化。这样我们在项目中连接的Mysql地址端口做好更改，那么就连接到了mycat服务器了。然后分别在两台主服务器做写入测试；在开发机链接做读数据测试。再分别查看mycat目录下logs目录下的mycat.log文件，发现不同的读写分发到不同的服务器~最后试着挂掉一台服务器，重新做写操作，和查询操作，随时跟踪每台服务器的数据和mycat.log日志。发现，ok~没任何问题！

**【Mysql分库分表】**

如果数据量达到了亿级别，那么这样管理的话，压力会很大，解决这个问题可以把库里数据量比较大的表给单独分散，分散到其他库中，这样库承受的压力就会小很多了。拿一个表做演示

确定要分库的表

拿库里的hp\_user来演示，首先查看一下表结构。

MySQL [hb4]> show create table hp_user;
    
    | hp_user | CREATE TABLE `hp_user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL COMMENT '用户名',
      `nickname` varchar(40) DEFAULT NULL COMMENT '用户昵称',
      `password` varchar(40) DEFAULT NULL COMMENT '密码',
      `email` varchar(40) DEFAULT NULL COMMENT '邮箱',
      `avatar` varchar(150) DEFAULT NULL COMMENT '头像',
      `zip_avatar` varchar(150) DEFAULT NULL COMMENT '压缩头像',
      `birthday` int(11) DEFAULT '0' COMMENT '出生日期',
      `gender` tinyint(4) DEFAULT '1' COMMENT '性别, 1:男, 2:女',
      `slogan` varchar(255) NOT NULL DEFAULT '' COMMENT '标语',
      `level` tinyint(4) DEFAULT NULL COMMENT '等级',
      `total_score` int(11) DEFAULT '0' COMMENT '用户的积分总额',
      `score` int(11) NOT NULL DEFAULT '0' COMMENT '积分',
      `freeze_score` int(11) NOT NULL DEFAULT '0' COMMENT '购买商品时冻结积分',
      `point` int(11) DEFAULT '0' COMMENT '点数(预留)',
      `invite_code` char(6) DEFAULT NULL COMMENT '邀请码(自己的）',
      `invite_by` int(11) NOT NULL DEFAULT '0' COMMENT '被邀请id(user_info.id)',
      `status` tinyint(4) DEFAULT '1' COMMENT '用户状态, 1:可用, 0: 不可用',
      `create_time` int(11) DEFAULT NULL COMMENT '创建时间',
      `register_ip` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '注册ip',
      `login_time` int(11) DEFAULT NULL COMMENT '登录时间',
      `forbidden_time` int(11) DEFAULT NULL COMMENT '禁用时间',
      `operator_id` int(11) DEFAULT '0' COMMENT '操作人',
      `is_delete` tinyint(4) DEFAULT '0' COMMENT '是否删除',
      `reason` varchar(100) DEFAULT NULL COMMENT '审核不通过请备注原因',
      `is_examine` tinyint(4) DEFAULT '0' COMMENT '是否通过,1：否,0:是',
      `token` varchar(32) DEFAULT NULL COMMENT '用户token',
      `alipay_code` varchar(255) DEFAULT '' COMMENT '支付宝账户',
      `alipay_name` varchar(255) DEFAULT '' COMMENT '支付宝名字',
      `wx_id` char(50) NOT NULL DEFAULT '' COMMENT '微信oppenid',
      `level_time` varchar(20) NOT NULL COMMENT '成为等级时间',
      `assessment_time` varchar(20) NOT NULL COMMENT '上次考核时间',
      `level_type` int(11) NOT NULL COMMENT '等级状态1首次2不是首次',
      PRIMARY KEY (`id`),
      UNIQUE KEY `username` (`username`),
      KEY `invite_code` (`invite_code`),
      KEY `status` (`status`) USING BTREE,
      KEY `is_delete` (`is_delete`) USING BTREE,
      KEY `invite_by` (`invite_by`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表' |

创建要分的三个库的对应的表

MySQL [hb4]> create database user_db1;
    Query OK, 1 row affected (0.00 sec)
    
    MySQL [hb4]> create database user_db2;
    Query OK, 1 row affected (0.10 sec)
    
    MySQL [hb4]> create database user_db3;
    Query OK, 1 row affected (0.02 sec)

创建好库之后，分别use 三个库，复制建表语句，创建数据表，以后有数据需要往表里写了，那么做一个hash处理，做分配，将入库数据分散开，减小单库压力。

配置分库分表

#vim schemaxml  //对应好下面的配置
    
    <schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
     
            <table name="hp_user" primarykey="id" dataNode="user1,user2,user3" rule="sharding-by-murmur" />
     </schema>
     
      <dataNode name="user1" dataHost="localhost1" database="user_db1" />
      <dataNode name="user2" dataHost="localhost1" database="user_db2" />
      <dataNode name="user3" dataHost="localhost1" database="user_db3" />

这样配置完毕，但是在做分片数据之前，思考一下，插入数据的时候怎么能保证不发生冲突呢？也就是说，比如第一条数据进入user\_db1这个库，数据主键id从1开始，第二条的时候进入user\_db2，也是从1开始，将来查的时候肯定会查出多条id为1的，怎么避免呢？可以在插入的时候指定id，如果不指定主键id呢？  
mycat提供了一个自动生成主键id的配置解决这个问题。

找到conf下的rule.xml，每个tableRule就代表一个规则。  
找到sharding-by-murmur，一致性哈希。

# vim rulexml //对应配置
    
    <tableRule name="sharding-by-murmur">
          <rule>
                <columns>id</columns>  
                <algorithm>murmur</algorithm>
          </rule>
    </tableRule>

vim server.xml  //对应配置
    
    <property name="sequnceHandlerType">2</property>  //默认是2，改为0,0代表本地文件进行配置

# vim sequence_conf.properties   //
    
    #default global sequence    //这里是自带的说明
    GLOBAL.HISIDS=              //隐藏的
    GLOBAL.MINID=10001          //最小id
    GLOBAL.MAXID=20000          //最大id
    GLOBAL.CURID=10000          //当前id
    
    # self define sequence
    #配置自己的                  //这里是配置的user节点
    USER.HISIDS=
    USER.MINID=10001
    USER.MAXID=20000
    USER.CURID=10000

配置好重启mycat。

数据测试

执行SQL还是在项目服务器上，之前连接Mycat的服务器终端执行SQL

INSERT INTO `hp_user` (
        `id`,
        `username`,
        `nickname`,
        `password`,
        `email`,
        `avatar`,
        `zip_avatar`,
        `birthday`,
        `gender`,
        `slogan`,
        `level`,
        `total_score`,
        `score`,
        `freeze_score`,
        `point`,
        `invite_code`,
        `invite_by`,
        `status`,
        `create_time`,
        `register_ip`,
        `login_time`,
        `forbidden_time`,
        `operator_id`,
        `is_delete`,
        `reason`,
        `is_examine`,
        `token`,
        `alipay_code`,
        `alipay_name`,
        `wx_id`,
        `level_time`,
        `assessment_time`,
        `level_type`
    )
    VALUES
        (
            next value for MYCATSEQ_USER,   //这里一定要注意，是mycat的哈希规则
            '12323232323',
            '测试用户9',
            '14e1b600b1fd579f47433b88e8d85291',
            NULL,
            '[\"\\/upload\\/2017\\/03\\/29\\/2eqcj0jh10skfgtv.jpg\"]',
            '[\"\\/upload\\/2017\\/03\\/29\\/zip_2eqcj0jh10skfgtv.jpg\"]',
            '19910201',
            '1',
            'nonononononononononononononononono',
            '2',
            '111117',
            '111117',
            '0',
            '0',
            'PRNLT',
            '66',
            '1',
            '1483002221',
            '124.202.200.186',
            '1483002221',
            NULL,
            '0',
            '0',
            NULL,
            '0',
            '6b05171019a33823aa182364a1e643fa',
            '18798048650',
            '测试9',
            '',
            '1496723153',
            '',
            '0'
        );

写入4条数据，然后查看。

MySQL [mydb]> select id,username,nickname,password,email,alipay_name from hp_user;
    +-------+-------------+---------------+----------------------------------+-------+---+
    | id | username | nickname | password | email | alipay_name 　　　　　　　　|
    +-------+-------------+---------------+----------------------------------+-------+---+
    | 10006 | 13434343434 | 测试用户6 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 测试6 |
    | 10008 | 14455554444 | 测试用户8 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 测试8 |
    | 10007 | 16565656565 | 测试用户7 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 测试7 |
    | 10009 | 12323232323 | 测试用户9 | 14e1b600b1fd579f47433b88e8d85291 | NULL | 测试9 |
    +-------+-------------+---------------+----------------------------------+-------+----+

然后再在真实数据库服务器上（主库）查看真实数据

[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTEzMDAxNDgtMTI0MDI3MjgxLnBuZw]: /images/20211018/cbeb52de06c844878f9cdac2d9c09bc8.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxMTUzNTc2OTItOTU3ODI3NTA1LnBuZw]: /images/20211018/198dc06b34c84e1686afaea1cb5d5711.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxMTUzMzgxMzEtMTI0Mzk0NDgzNC5wbmc]: /images/20211018/595f7054110b4da6aa914f1a55a8abcf.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxMjAwMDc5OTgtODU4OTQzMTM1LnBuZw]: /images/20211018/a9bbd25722184da692e2f69bd9548bd0.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTMyMjAzNzctMTA1Njk2NjAwMy5wbmc]: /images/20211018/61d5760b5f1141b7951b4ec7565457de.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTMzMDQyMDMtMjAxMTM1MzE1Mi5wbmc]: /images/20211018/efa186d1d4d2409cb39732f1b24f6aa5.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNTUzMzIyMzItMTc4NjE3ODIyOC5wbmc]: /images/20211018/35152a8f30214d62aaf5742f27962025.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNjM5MjA4MzQtMTIwNjg0MDE0LnBuZw]: /images/20211018/3834d78cb940421c84d5191a727ab101.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDYxNzMyNTU0NTctMTI4MjM1ODYwMC5wbmc]: /images/20211018/45bab656b6264470b0e8f6aeb444bd22.png
[aHR0cHM6Ly9pbWFnZXMyMDE4LmNuYmxvZ3MuY29tL2Jsb2cvNjc2MzcyLzIwMTgwNC82NzYzNzItMjAxODA0MDcyMDEzMTA2MzktMTk5ODQxODM1Ny5wbmc]: /images/20211018/5ecde229a279471b9263ae3651a4effc.png