运维之道 | Mysql主从复制 + Mycat读写分离
1、什么是读写分离
读写分离，基本的原理是让主数据库处理事务性增、删、改操作（INSERT、UPDATE、DROP）,而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。
2、为什么要读写分离
在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性，还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。
因为数据库的“写”（写10000条数据到oracle可能需要几分钟）操作是比较耗时的；
但是数据库的“读”（从oracle读10000条数据可能需要几秒钟）操作是比较短时的；
所以读写分离，解决的是数据库的写入，影响了查询的效果。
3、主从复制、读写分离的基本设计
一台主、多台从，主提供写操作，从提供读操作。
一、安装配置JAVA - JDK环境
1、下载JDK安装包
下载Linux环境下的jdk1.8.0_231，请去JAVA官网中下载jdk的安装文件； 通过wget命令获取java压缩包[root@localhost java]# wget /otn/java/jdk/8u231-b11/5b13a193868b4bf28bcb45c792fce896/jdk-8u231-linux-x64.tar.gz?AuthParam=1574751387_e7715500e2e4d54917604014600ce583
2、对JAVA安装包进行解压
[root@localhost java]# tar -zxvf jdk-8u231-linux-x64.tar.gz
3、修改环境配置
[root@localhost java]# vi /etc/profile
# JAVAJAVA_HOME=/root/java/jdk1.8.0_231PATH=$JAVA_HOME/bin:$PATHCLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAME_HOME/lib/tools.jarexport JAVA_HOMEexport PATHexport CLASSPATH
[root@localhost ~]# source /etc/profile ///重启profile[root@localhost ~]# javac ///测试java环境是否配置成功用法: javac <options> <source files>其中, 可能的选项包括:-g生成所有调试信息-g:none不生成任何调试信息-g:{lines,vars,source}只生成某些调试信息-nowarn不生成任何警告-verbose 输出有关编译器正在执行的操作的消息-deprecation输出使用已过时的 API 的源位置...... 配置成功！！！
[root@localhost ~]# java -version ///查看java版本java version "1.8.0_231"Java(TM) SE Runtime Environment (build 1.8.0_231-b11)Java HotSpot(TM) 64-Bit Server VM (build 25.231-b11, mixed mode)
二、安装Mycat（基于java环境）
1、获取Mycat安装包
[root@localhost soft]# wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-1028204710-linux.tar.gz
2、解压Mycat安装包
[root@localhost soft]# tar -xzvf Mycat-server-1.6-RELEASE-1028204710-linux.tar.gz
3、进入到mycat文件的conf子文件夹修改server.xml文件
只需将配置的最后一部分密码修改即可，其他可按需求自行配置；[root@localhost conf]# vi server.xml
......<user name="user"><property name="password">123456</property><property name="schemas">TESTDB</property><property name="readOnly">true</property></user>......
4、修改conf文件中的 schema.xml文件
[root@localhost conf]# vi schema.xml
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/"><schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="villian"></schema><dataNode name="villian" 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><!-- can have multi write hosts --><writeHost host="hostM1" url="192.168.146.129:3306" user="root"password="123456"><!-- can have multi read hosts --><readHost host="hostS2" url="192.168.146.130:3306" user="root" password="123456" /></writeHost></dataHost></mycat:schema>
注释：
1、第五行的dataNode="villian"需与第七行的dataNode name="villian"对应；
2、第七行的database="school"为数据库读写分离库；
3、第八行的balance="0"需要开启为1，既balance="1"；
4、第十二行的writeHost host="hostM1" url="192.168.146.129:3306"为主服务器写入url；
5、第十四行的<readHost host="hostS2" url="192.168.146.130:3306"为从服务器独处的url；
5、启动mycat
[root@localhost bin]# pwd/root/java/mycat/bin///在mycat文件夹的bin里启动[root@localhost bin]# ./mycat start consoleStarting Mycat-server...
查看是否启动成功
[root@localhost bin]# ps -ef | grep mycatroot121591 0 16:53 ? 00:00:00 /root/java/mycat/bin/./wrapper-linux-x86-64 /root/java/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/root/java/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycatroot12161 12159 0 16:53 ?
[root@localhost bin]# netstat -ntlpActive Internet connections (only servers)Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp6 00 :::9066 :::*LISTEN12161/java tcp6 00 :::43902:::*LISTEN12161/javatcp6 00 :::1984 :::*LISTEN12161/javatcp6 00 :::8066 :::*LISTEN12161/java
三、搭建部署 Mysql 主从复制
1、准备两台服务器
主库服务器 192.168.146.130 master
从库服务器 192.168.146.129 slave
2、配置 master 主库服务器
a. 开启binlog
vi /etc/f[myslqd]server-id=1## 机器的唯一标识 ##bin-log=mysql-bin ## bin-log日志名称 ，开启bin-log ##binlog-format=ROW ## 二进制日志格式 ##datadir = /data/mysql## bin-log日志存放地点 ### binlog-do-db=school## 同步数据库名称（可不用） ##
b.重启 service mysqld restart
[root@localhost ~]# systemctl restart mysqld
c.进入mysql,查看binlog是否开启成功
[root@localhost ~]# cd /data/mysql[root@localhost mysql]# ll-rw-r-----. 1 mysql mysql178 10月 5 08:34 binlog.000017-rw-r-----. 1 mysql mysql178 10月 5 09:17 binlog.000018-rw-r-----. 1 mysql mysql178 10月 5 09:49 binlog.000019-rw-r-----. 1 mysql mysql155 10月 5 09:49 binlog.000020-rw-r-----. 1 mysql mysql320 10月 5 09:49 binlog.index
d.创建用户并授权
[root@localhost mysql]# mysql -u root -p#登录mysql#mysql> create user 'villian'@'192.168.146.130' identified by '123456'; ##创建用户##mysql> grant replication slave on *.* to 'villian'@'192.168.146.130'; ##用户授权##
3、配置 slave 从库服务器
a.开启binlog
vi /etc/f [myslqd]server-id=2 ## 机器的唯一标识 ##bin-log=mysql-bin## bin-log日志名称 ，开启bin-log ##binlog-format=ROW ## 二进制日志格式，跟主服务器一致 ##log-slave-updates=true## 中继日志执行之后，这些变化是否需要计入自己的binarylog。党你的从服务器需要作为另外一个服务器的主服务器的时候需要开启。就是双主互相备份，或者多主循环备份。#### ( ( server-id机器的唯一标识),后面两步是用来打开slave的relaylog功能的)
b.重启mysql
[root@localhost ~]# systemct restart mysqld
4、建立主从关系
a.打开主库服务器登录mysql （获取File 和 Position）
[root@localhost ~]# mysql -u root -p## 登录mysql ##mysql> show master status;## 查看master状态 ##+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |1356 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
b.打开从库服务器登录mysql
[root@localhost ~]# mysql -u root -p## 登录mysql ##mysql> show slave status\G; ## 查看状态 ##状态未开启时进行如下设置：mysql> change master to master_host='192.168.146.130'; ## 主节点 ##mysql> change master to master_port=3306; ## 主节点的端口号 ##mysql> change master to master_user='villian'; ## 账号 ##mysql> change master to master_password='123456';## 密码 ##mysql> change master to master_log_file='mysql-bin.000001'; ## show master status 对应上述主库的日志 ##mysql> change master to master_log_pos=1356; ## show master status 对应上述主库的pos ##
c.在从库服务器开启从节点
mysql> start slave； ## 开启从节点 ##mysql> show slave status\G;## 查看状态 ##下图红色框框必须为开启状态Slave_IO_Running ：负责与主机的io通信Slave_SQL_Running ：负责自己的slave mysql进程
其他错误：1.网络不通; 2.密码不对; 3.pos不对; 4.防火墙没关; 5.server-id冲突;6.主库中重新创建用户并授权；
1、若使用克隆虚拟机，必须修改mysql UUID，否则会显示 Slave_IO_Running: NO
2、主从复制实战视频讲解分析
3、master and slave have equal MySQL server ids
4、防火墙
以上都不行，主库中重新创建新用户，并授权，再重新配置一次信息即可
四、搭建部署 mycat 读写分离
1、通过主库登录mycat管理窗口
-h 192.168.146.130为本机IP-P8066为mycat数据端口，-P9066为mycat数据端口TESTDB是配置中间件mycat中设置的[root@localhost mysql]# /usr/local/mysql/bin/mysql -uroot -p -h 192.168.146.130 -P8066mysql> mysql> show databases;+----------+| DATABASE |+----------+| TESTDB |+----------+1 row in set (0.01 sec)
2、查看读写配置信息
mysql> show @@datasource;+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+| villian | hostM1 | mysql | 192.168.146.129 | 3306 | W |0 | 8 | 1000 | 1194 | 0 |0 || villian | hostS2 | mysql | 192.168.146.130 | 3306 | R |0 | 0 | 1000 | 0 | 0 |0 |+----------+--------+-------+-----------------+------+------+--------+------+------+---------+-----------+------------+2 rows in set (0.01 sec)
3、查看心跳信息
mysql> show @@heartbeat;+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+| hostM1 | mysql | 192.168.146.129 | 3306 | 1 |0 | idle | 0 | 1,1,1 | -11-26 20:07:39 | false || hostS2 | mysql | 192.168.146.130 | 3306 |-1 |0 | idle | 0 | 0,0,0 | -11-26 20:07:39 | false |+--------+-------+-----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+2 rows in set (0.00 sec)
4、读写分离测试
修改mycat日志[root@localhost conf]# pwd/root/java/mycat/conf[root@localhost conf]# vi log4j2.xml
将info改为debug
进入到mycat安装目录的bin目录下重启mycat中间件
[root@localhost mycat]# lsbin catlet conf lib logs version.txt[root@localhost mycat]# cd bin[root@localhost bin]# ./mycat restartStopping Mycat-server...Stopped Mycat-server.Starting Mycat-server...
在主库中查看数据
在mycat的日志文件中可查看记录tail -f wrapper.log
[root@localhost logs]# pwd/root/java/mycat/logs[root@localhost logs]# ls-11 mycat.log mycat.pid wrapper.log[root@localhost logs]# tail -f wrapper.log ///查看日志命令
mysql读写分离概念介绍
mysql读写分离视频教程
