mysql 主从复制和读写分离

2018-09-18 本文已影响339人起个名忒难

centos 7 安装mysql ,并配置主从复制读写分离

安装mysql

1.准备两台虚拟机

ip地址： 192.168.0.101 ， 102.168.0.102
系统版本： centos 7
mysql : 8.0.12
nycat : 1.6.5

下载安装mysql

使用yum库进行安装，在https://dev.mysql.com/downloads/repo/yum/ 选择安装的版本

使用 wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm 进行下载
下载完成后，使用 yum localinstall platform-and-version-specific-package-name.rpm ，并将 platform-and-version-specific-package-name 替换为下载的RPM包
yum repolist enabled | grep "mysql.-community." 使用此命令查看yum存储库是否成功添加。
yum install mysql-community-server 使用此命令安装mysql,之后是漫长的等待时间。
安装完成之后，就是启动mysql 了，命令如下：systemctl start mysqld.service ，systemctl status mysqld.service 使用此命令查看运行状态，出现如下，表示mysql启动成功

mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: **active (running)** since 六 2018-09-15 22:05:45 CST; 20s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/enusingsystemd.html
  Process: 1335 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1403 (mysqld)
   Status: "SERVER_OPERATING"
   CGroup: /system.slice/mysqld.service
           └─1403 /usr/sbin/mysqld

使用此命令获取登陆的默认密码： grep "password" /var/log/mysqld.log
我们使用 mysql -u root -p 登陆
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'; 设置新密码
开启远程访问：
- use mysql
- update user set host = '%' where user = 'root';
- FLUSH PRIVILEGES;

至此，我们已经成功安装了mysql，并可以远程访问了

但是有一部还没有做，一般是不提供root用户的信息直接进行访问，需要提供一个单独的用户进行远程复制，可使用如下命令，进行用户的创建，并授权，这里我给了slave用户全部的权限，可以只给部分的权限.

mysql> create user 'slave'@'%' IDENTIFIED by '123456';
mysql> grant all PRIVILEGES on *.* to 'slave'@'%';
mysql> flush privileges

主从复制配置

实现主从复制的主要思路是，A主mysql开启二进制日志，B从mysql读取日志，并同步执行。一般都是主从同步，主主同步不推荐使用。

主从复制的方式：

基于日志
基于GTID（全局事务标识符）

主要说采用日志实现主从复制的方式。

主从复制的原理：

Master将数据改变的记录到二进制（binary log）中，也就是配置文件log-bin指定的文件名
slave 通过 i/o 线程读取master中binary log events 内容，并写入到slave的中继日志中
slave 重做中继日志事件，把中继日志的事件信息一条一条的在本地执行一次，完成数据库的本地存储，从而实现数据的复制操作。

配置Master主机文件，修改 /etc/my.cnf：

server-id=1 #值唯一
log-bin=master-bin #开启二进制日志文件
修改完配置后，使用systemctl restart mysqld.service 重启服务

配置slave主机文件，修改 /etc/my.cnf:

server-id=2
binlog_format=mixed #主从复制模式

修改完配置后，使用systemctl restart mysqld.service 重启服务

登陆mysql的命令行，输入：
mysql>
change master to
master_user='root',
master_password='123456',
master_host='192.168.0.101',
master_port=3306,
master_log_file='xxxx',
master_log_pos=xxx;

对上面的参数进行解释:

master_host: master主机ip
master_user : master用户
master_password:master密码
master_port:master端口号
master_log_file : #指定slave从哪个文件读取数据，可以在master服务器上，使用命令show master status 查看
master_log_pos : 从那个position 号开始读取

查看主从同步的状态命令

mysql> show slave status\G;

上面的配置完成后，开启主从同步模式，命令如下：
mysql>start slave ;

当使用 show slave status\G命令，主要看下面的两个参数：

Slave_IO_Running:yes
Slave_SQL_Runing:yes
两个参数的值都是yes就表示主从复制的模式是正常的。

如果遇到同步出错，可以重置主从复制设置：
mysql>reset slave ;
mysql> change ....

可使用如下命令，停止主从模式：
mysql>stop slave ;

总结：
上面所搭建的是单向的主从模式，也是使用的比较多的，而双向主从其实就是master和slave都开启日志功能，然后再master执行授权用户，再在master上进行change master操作。

主从同步延时问题调优：

网络延迟
master负载过高
slave负载过高
采用多台slave来分摊读请求，在单独配置一台slave做备份用。不进行其它任何的操作，已做到最大限度地实时请求。

读写分离

主要思路是使用mycat中间件，转发sql指令到mysql节点，mycat不负责数据库同步。 mycat原理中最重要的一个词是“拦截” ，它拦截了用户发送过来的SQL语句，首先对SQL语句做了一些特定的分析，如分片分析，路由分析，读写分离分析，缓存分析等，然后将此SQL发往后端的真实数据库，并将返回的结果做适当的处理，最终在返回给用户。

应用场景

单纯的读写分离，此时的配置最为简单，支持读写分离，主从切换
分库分表，对于超过1000万的表进行分片，最大支持1000亿的单表分片
多租户应用
报表系统，处理大规模的报表统计
替代hbase,分析大数据
作为海量数据实时查询的一种简单有效方案

mycat 安装：

下载安装，我就不在赘述了，很简单，找到tar包，解压就可以了。有一点需要注意的是，mycat运行需要jdk环境，并且版本要在1.7+。

配置：
需要配置三个文件，server.xml , rule.xml ,schema.xml

server.xml ,配置访问用户及权限

 //定义了两个用户，root和user为访问mycat的用户，并配置了密码，TESTDB为mycat的虚拟库，供上层使用，其中user用户为只读用户
 <user name="root" defaultAccount="true">
   <property name="password">123456</property>
   <property name="schemas">TESTDB</property>
 </user>
 <user name="user">
   <property name="password">user</property>
   <property name="schemas">TESTDB</property>
   <property name="readOnly">true</property>
 </user>

schema.xml 是主要的配置文件，它主要分为三个部分，schema ,dataNode ,dataHost 。

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
    <dataNode name="dn1" dataHost="localhost1" database="school" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
         <writeHost host="192.168.0.101" url="192.168.0.101:3306" user="root" password="123456">
            <readHost host="192.168.0.102" url="192.168.0.102:3306" user="root" password="123456" />
            </writeHost>
    </dataHost>

rule.xml : 这里没有用到数据分片的规则，所以rule.xml可以不配

启动：两种方式

mycat start
./startup_nowrap.sh

微信截图_20180918101350.png

出现如下信息，代表链接成功了。

问题：

Host '192.168.0.101' is blocked because of many connection errors

默认的max_connection_errors的值为10 ，这个值太低导致了数据库阻塞了，可以先使用命令 show global variables like '%max_connect_errors%'; 查看connect_errors的值，然后再使用命令 set global max_connect_errors=1000 来解决，当然也可以修改配置文件，my.cnf 中添加如下， max_connections=1000 , max_connect_errors = 1000 , wait_time=30 , 还有一种简单的方法，就是把基数清除掉 flush hosts , 但是不要忘了重启mycat

unknown charsetIndex : 255

微信截图_20180918095812.png

修改 conf/index_to_charset.properties ，在最后添加如下配置，255=utf8mb4 ，值为utf8mb4，是因为它是我mysql的默认字符集

client does not support authentication protocol request by server, consider upgrading mysql client

微信截图_20180918095945.png

use mysql ; alter USER 'root'@'%' identified with mysql_native_password by 'Huoyan@123'; FLUSH PRIVILEGES; 使用此命令修改密码。之所以出现这个问题，是因为新版本的mysql更改了密码的验证机制，使用以上命令，更改为旧密码的验证方式就可以了。当然你也可以降低mysql的版本。

至此， mysql的主从复制和读写分离我们已经搭建完成了。至于验证的步骤我就不在赘述了，感兴趣的可以自己去研究一下，有环境了就可以探索它的奥秘了。