Mysql集群

一、安装Mysql

若是CentOS7.6，需要运行 rpm -qa | grep mari，查询mariadb相关安装包，因为CentOS 7.6会自带类MySQL数据库，会和mysql冲突，所以有安装包则删除：

rpm -e --nodeps mariadb-libs  # 删除

CentOS7环境之RPM方式离线安装MySQL5.7
官网下载Mysql：https://downloads.mysql.com/archives/community/

上传至服务器指定位置

# 在当前目录下（mysql）下创建一个 mysql-5.7 文件夹
mkdir mysql-5.7.42
# 解压安装包到该目录下
tar -xvf mysql-5.7.42-1.el7.x86_64.rpm-bundle.tar -C mysql-5.7.42

解压完成之后可以切换到 mysql-5.7 目录下查看解压后的文件
可以看到解压后的文件都是 rpm 文件，所以需要用到rpm包资源管理器相关的指令安装这些 rpm 的安装包
在安装执行 rpm 安装包之前先下载一些插件，因为 mysql 里面有些 rpm 的安装依赖于该插件。

yum install openssl-devel
# 和
yum -y install libaio perl net-tools

安装完该插件之后，依次执行以下命令安装这些 rpm 包

启动mysql

systemctl start mysqld  # 启动mysql服务
systemctl status mysqld  # 查看mysql服务状态
systemctl enable mysqld  #设置开机启动
systemctl daemon-reload  # 刷新所有修改过的配置文件

MySQL会自动为root生成密码，执行下面的命令可看到密码
grep “password”/var/log/mysqld.log （根目录执行）
拿到密码之后登陆mysql -u root -p

# 执行如下语句更改root用户密码：
ALTER USER USER() IDENTIFIED BY '999999';
#设置 validate_password_policy 的全局参数为 LOW，并将密码长度设置为你想要的长度：
mysql> set global validate_password_policy=LOW;
mysql> set global validate_password_length=9;
#最后执行刷新MySQL的系统权限命令：
FLUSH PRIVILEGES; 

现在会发现远程连接不上mysql

# 直接将root权限修改为可以通过远程访问(但不推荐)
mysql> use mysql;
mysql> UPDATE user SET Host='%' WHERE User='root';
mysql> flush privileges;

添加防火墙

# 打开防火墙                   
systemctl start firewalld；
#放开3306端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent    
#重新加载配                                             
firewall-cmd --reload 
# 查看防火墙所以开放端口
firewall-cmd --zone=public --list-ports

mysql配置文件路径 /etc/my.cnf

[mysqld]
port=3307         #设置端口号

二、开启BIN-LOG

binlog基本概念
binlog基本概念二进制日志(binnary log)以事件形式记录了对MySQL数据库执行更改的所有操作。 binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE、DROP等）以及表数据修改（INSERT、UPDATE、DELETE、TRUNCATE等）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。
登录MySQL后，查看binlog状态sql如下：
show variables like ‘%log_bin%’;
若未开启则需要开启
编辑配置文件配置开启binlog 放在mysqld下
编辑配置文件 vim /etc/my.cnf

#设置唯一id
server-id=1
##开启bin-log，产生的bin-log文件名即为bin-log.*
log-bin=mysql-bin
#指定bin-log为row类别，其他两种是statement、mixed
binlog_format=row
#对全部数据库开启则注释掉binlog-do-db即可
#对指定的数据库开启bin-log，这里是对food数据库开启bin-log服务
binlog-do-db=food

配置完成后退出，然后重启我们的mysql服务
查看是否开启bin_log日志
mysql命令: show variables like ‘log_%’

三、数据恢复

自行模拟删除数据

查看我们当前使用的binlog
show master status;
查看日志列表
show master logs;
查看binlog日志信息包括位置
show variables like 'log_%';

当需要恢复数据时，为了防止恢复数据后影响最新业务，需要执行flush logs，产生一个新的binlog文件，此时旧的binlog文件不会再有写入；
进入到我们的binlog日志位置 /var/lib/mysql

下面具体通过mysql-bin.000001来进行数据恢复
恢复时需要在binlog中找到两个位置：

• 数据恢复的起始位置
• 数据恢复的结束位置

2.1 mysqlbinlog

只能正向回滚，不适用
在服务器内执行：

通过mysqlbinlog将binlog转为sql，以方便查询具体位置
mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-bin.000001>backuptmp.sql
查看生成的backuptmp.sql，最终确定需要恢复的起始位置为的行数
执行恢复数据
mysqlbinlog -v /var/lib/mysql/mysql-bin.000005 --start-position=299 --stop-position=1305 | mysql -uroot -p999999
如果执行提示se you are using a --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output.
则表示个参数指向了一个语句中间的事件。部分语句的事件尚未被写入输出。

说明如何定位起止行数：
并不是定位delete，drop的数据位置，而是寻找你create或Write的数据
举例：一条数据的inset如何定位

2.2 binlog2.sql

推荐使用
只针对于mysql5.6 5.7，必须开启binlog日志
优势：可反编译 直接回滚,可筛选库筛选表非常的方便
官网https://github.com/danfengcao/binlog2sql

# 安装工具
yum -y install git python-pip 

# 克隆binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
用pip命令来下载binlog2sql所需要的“requirements.txt”这个文本
pip install -r requirements.txt

# 安装位置
cd /mydata/binlog2sql/binlog2sql
# 执行回滚 配置在官网查看
# 可根据mysqlbinlog找到删除的偏移量 根据偏移量去执行反编译sql，若不知偏移量也可直接全部反编译
python binlog2sql.py -h127.0.0.1 -P3307 -uroot -p999999 -dfood -t jjjfood_order -B --flashback  --start-file=mysql-bin.000012 > rollback.sql --start-position=3270 --stop-position=4335

若误删整表，该如何操作
针对于delete from 表 删除表中所有数据

#根据时间去查看是否为当时删除的数据
python binlog2sql.py -h127.0.0.1 -P3307 -uroot -p999999 -dfood -t jjjfood_center_menu  --start-file='mysql-bin.000013'   --start-datetime='2024-4-17 00:00:00'   --stop-datetime='2024-04-17 16:51:58' 
#根据时间进行反编译执行sql
python binlog2sql.py -h127.0.0.1 -P3307 -uroot -p999999 -dfood -t jjjfood_center_menu  --start-file='mysql-bin.000012'   -B > rollback.sql --start-datetime='2024-4-17 00:00:00'   --stop-datetime='2024-04-17 16:51:58' 

针对于DROP删除
1.被drop删除之后，binlog2无法查询和反编译到增删改语句
2.执行flush logs

python binlog2sql.py -h127.0.0.1 -P3307 -uroot -p999999 -dfood -t jjjfood_product_unit --start-file='mysql-bin.000016'  > rollback.sql --start-datetime='2024-4-17 00:00:00'   --stop-datetime='2024-04-17 16:51:58'

3.从最初的binlog日志开始找create 表语句/或者是备份的表结构在数据库中添加(有表结构才会进行编译)
4.在flush logs之前所有的binlog日志文件执行上述命令，讲sql copy 一次执行恢复数据

四、主从复制

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的
主从复制是为了读写分离，减轻服务器压力，起到负载均衡的作用。

Amoeba (变形虫)。由陈思儒开发，其曾就职于阿里巴巴。该程序用Java语言进行开发,阿里巴巴将其用于生产环境。它不支持事务和存储过程。
实现过程
1、客户端访问代理服务器
2、代理服务器写入到主服务器
3、主服务器将增删改写入自己二进制日志
4、从服务器将主服务器的二进制日志同步至自己中继日志
5、从服务器重放中继日志到数据库中
6、客户端读，则代理服务器直接访问从服务器
7、降低负载，起到负载均衡作用

4.1 主库Mysql配置

首先配置主库，开启binlog日志

vim /etc/my.cnf
server-id = 1
log-bin=master-bin							#添加，主服务器开启二进制日志
log-slave-updates=true						#添加，允许从服务器更新二进制日志

systemctl restart mysqld

执行下面SQL，记录下结果中File和**Position（偏移量）**的值，用于后续复制开始的位置

show master status;

创建数据同步的用户并授权

GRANT REPLICATION SLAVE ON *.* to 'copyUser'@'%' identified by 'Root@123456';

上面SQL的作用是创建一个用户,并且给用户授予REPLICATION SLAVE权限。常用于建立复制时所需要用到的用户权限，也就是slave必须被master授权具有该权限的用户，才能通过该用户复制

4.2 从库Mysql配置

修改Mysql数据库的配置文件/etc/my.cnf 后重启mysql

vim /etc/my.cnf
server-id = 2						#修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						#添加，开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index		#添加，定义中继日志文件的位置和名称

systemctl restart mysqld

登录Mysql数据库，设置主库地址及同步位置

change master to master_host='ip',master_port=3307,master_user='copyUser',master_password='Root@123456',master_log_file='mysql-bin.000008',master_log_pos=154;
 
start slave;

# 如果配置错了停止掉重新配置即可
stop slave;

参数说明
A. master_host : 主库的IP地址 ，master_port：指定端口
B. master_user : 访问主库进行主从复制的用户名(上面在主库创建的)
C. master_password : 访问主库进行主从复制的用户名对应的密码
D. master_log_file : 从哪个日志文件开始同步(上述查询master状态中展示的有)
E. master_log_pos : 从指定日志文件的哪个位置开始同步(上述查询master状态中展示的有)
查看从数据库的状态

show slave status\G;	  #查看 Slave 状态
//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
Slave_IO_Running: Yes	  #负责与主机的io通信
Slave_SQL_Running: Yes    #负责自己的slave mysql进程

状态信息中的 Slave_IO_running 和 Slave_SQL_running 可以看出主从同步是否就绪，如果这两个参数全为Yes，表示主从同步已经配置完成。** **
注意：一定要保证主库数据为空或从库必须有当前主库数据否则删除/修改时主从复制会失败

五、读写分离

5.1 Amoeba服务器配置

下载地址：https://sourceforge.net/projects/amoeba/files/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz/download
因为Amoeba是基于jdk1.5版本开发的，所以官方推荐使用1.5或者1.6版本，高版本不建议使用。

yum install -y java-1.8.0-openjdk-devel.x86_64
java -version

配置环境变量 AMOEBA_HOME 变形虫的安装路径

vim /etc/profile

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk   # Java安装路径
export PATH=$JAVA_HOME/bin:$PATH
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export AMOEBA_HOME=/mydata/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile	

安装 Amoeba软件

mkdir /mydata/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /mydata/amoeba/
chmod -R 755 /mydata/amoeba/
# 开启amoeba
/mydata/amoeba/bin/amoeba
# 如显示amoeba start|stop 说明安装成功

Amoeba启动错误：
The stack size specified is too small, Specify at least 160k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.
解决方法：
打开bin目录下 的amoeba启动文件
# vim amoeba
DEFAULT_OPTS=“-server -Xms256m -Xmx256m -Xss128k”
修改为：
DEFAULT_OPTS=“-server -Xms256m -Xmx256m -Xss256k”

5.2 在主从服务器的mysql上授权

先在Master、Slave1 的mysql上开放权限给 Amoeba 访问

grant all on *.* to test@'ip.%' identified by 'tnpXL%^Kbrj^8bRX'; 

5.3 配置 Amoeba读写分离

amoeba代理服务器配置amoeba服务

cd /mydata/amoeba/conf/

cp amoeba.xml amoeba.xml.bak
vim amoeba.xml		#修改amoeba配置文件

<property name="user">amoeba</property>
<property name="password">amoeba</property>
#---------115修改 设置默认地址为master-----------------------------
<property name="defaultPool">master</property>
#---------117去掉注释–------------------------
<property name="writePool">master</property>
<property name="readPool">slaves</property>

cp dbServers.xml dbServers.xml.bak


vim dbServers.xml
# 文件中的port是mysql集群的端口号 需保持一致
注释作用：默认进入test库 以防mysql中没有test库时，会报错
<!-- mysql schema
<property name="schema">test</property>
-->

<!-- mysql user -->
<property name="user">test</property>
<property name="password">tnpXL%^Kbrj^8bRX</property>

#---------设置主服务器的名Master------------------
<dbServer name="master"  parent="abstractServer">
<property name="ipAddress">ip</property>

#---------设置从服务器的名slave1-----------------
<dbServer name="slave1"  parent="abstractServer">
<property name="ipAddress">ip</property>

#---------复制上面设置从服务器2的名slave2和地址---
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">ip</property>

#---------修改后的65或66修改-------------------------------------
<dbServer name="slaves" virtual="true">
#---------71修改----------------------------------------
<property name="poolNames">slave1,slave2</property>

/mydata/amoeba/bin/amoeba start&	 #启动Amoeba软件，按ctrl+c 返回
netstat -anpt | grep java	     #查看8066端口是否开启，默认端口为TCP 8066

# 打开防火墙                   
systemctl start firewalld
firewall-cmd --zone=public --add-port=8066/tcp --permanent    
#重新加载配                                             
firewall-cmd --reload 
# 查看防火墙所以开放端口
firewall-cmd --zone=public --list-ports

验证是否成功 云服务器切记开放安全组后，启动amoeba

客户端连接amoeba或使用本地连接amoeba
mysql -u amoeba -pamoeba -h ip -P8066

Java连接mysql若报错请切换版本至<mysql.version>5.1.24</mysql.version>
经测试amoeba确实不支持事务，请根据情况使用，当然也可以通过多数据源的方式去实现读写分离(较麻烦)

总结
mysql主从复制原理
slave节点与主节点进行连接，建立主从关系，并把从哪开始同步，及哪个日志文件都一并发送到master
master将修改的数据保存到binlog中
master开启binlog dump线程，将binlog日志推送到连接的slave中
slave接受到推送的binlog，slave开启IO线程将数据写到中继日志(relay log)中
slave同时还会开启一个SQL线程，对比中继日志中新增的内容，并且解析SQL，回放数据到从数据库中

面试题：
1.如何查看主从同步状态是否成功
show slave status \G
show master status\G
2.如果I/O和sql不是yes呢，你是如何排查的
先看last errno有几个错误 有什么错误等等都有信息显示
网络不通
my.cnf配置有问题
密码、file文件名、pos偏移量不对
防火墙没有关闭
3.show slave status \G能看到哪些信息（比较重要的）
io状态 两个线程的yes
Last_Errno,Last_Error**
slave的SQL线程读取日志参数的的错误数量和错误消息。错误数量为0并且消息为空字符串表示没有错误；
如果Last_Error值不是空值，它也会在从属服务器的错误日志中作为消息显示。
4.主从复制慢（延迟）有哪些可能
从库硬件比主库差，导致复制延迟
主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。升级更高版本的mysql
可以支持多线程复制
慢SQL语句过多
网络延迟
master负载：主库读写压力大，导致复制延迟，架构的前端要加buffer及缓存层
slave负载：一般的做法是，使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器，只作为备份用，不进行其他任何操作.
5.mysql主从同步读写分离的三个账号
主从同步（数据库互相授权的账号）
amoeba（客户端访问amoeba）
amoeba（amoeba访问数据库集群）