58、主从复制数据库+读写分离

mysql的主从复制和读写分离（面试问原理）

mysql的主从复制和读写分离：

主从复制面试必问主从复制的原理。

读写分离，MHA

一、主从复制

1.1、主从复制的模式：

1、mysql的默认模式：

异步模式：主库在更新完事务之后，会立即把结果返回给从服务器，并不关心从库是否接收到，以及从库是否处理成功。

网络问题可能没有同步，或者是其他因素导致同步失败。

快效率高。

2、全同步模式：

主库在更新完事务之后，立即把结果返回到从库，所有的从库执行完毕之后才能继续下一个同步，安全，性能受到影响。

3、半同步复制

介乎异步和全同步之间，主库更新完事务之后，也是同步到从库，同步完成后有一个等待时间。

等待时间是一个tcp/ip的往返时间。5ms左右。

既在一定程度上保证了效率，也在一定程度上保证了数据的完整性。

在这里插入图片描述

主从复制的延迟怎么解决：

1、网络问题：防火墙的原因。

2、硬件设备问题，cpu内存和磁盘出了问题。

3、配置文件写错了。

配置文件当中设置的方式提高数据的安全性。

数据库的引擎要是innodb

1.2、双一设置：(银行面试)

innodb_flush_log_at_trx_commit=1

##每次提交都会刷新事务日志，确保事务的持久性。但是会影响性能。

sync_binlog=1

##每次提交事务，将二进制日志的内容保存到磁盘，确保日志的持久性，提高了安全性。

性能化设置：

sync_binlog=0

##数据在内存中，不保存，重启没了。

sync_binlog=20

最多提交几次事务会进行磁盘刷新，日志内容保存到磁盘。

innodb_flush_log_at_trx_commit=2

##每次更新保存在内存中，不进行刷新。

innnodb_buffer_pool_size

控制innodb缓冲池的大小，增大可以提高数据库的性能，但是占用的是系统内存，配置的时候要注意合理化时间。

主从复制如何实现：

实现是基于mysql的二进制文件，根据主库的二进制文件的标志位，实现主和从的同步。

主从服务之间，服务器的时间要同步。

架构：

三台服务器

192.168.168.11 mysql 8.0 主

192.168.168.12 mysql 8.0 从

192.168.168.13mysql 8.0 从

看指标

在这里插入图片描述

Slave_IO_Running: Yes

从库和主机的读写是否正常。

Slave_SQL_Running: Yes

slave的mysql状态是否正常。

1.3、配置主从数据库

在这里插入图片描述

主库、从库：systemctl stop firewalld
setenforce 0yum -y install ntpdate##安装工具date##查看时间ntpdate ntp.aliyun.com获取阿里云的时间主数据库vim /etc/my.cnflog-bin=master-bin
binlog_format=MIXED
log-slave-updates=true
##允许从服务器从主库复制数据时可以写入从库自己的二进制日志当中。systemctl restart mysqld从数据库2vim /etc/my.cnfserver-id = 2
relay-log=relay-log-bin##从服务器 获取二进制日志的开头，开启从库的二进制日志relay-log-index=slave-relay-bin.index##二进制日志的索引文件的名称relay_log_recovery=1##配置从服务器在启动时是否执行二进制日志的恢复操作（和主库同步），1开启开启。systemctl restart mysqld从数据库3vim /etc/my.cnfserver-id = 3
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
relay_log_recovery=1systemctl restart mysqld主1从库2、3mysql -u root -p123456mysql> CREATE USER 'myslave'@'192.168.168.%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.02 sec)mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.168.%';
Query OK, 0 rows affected (0.00 sec)mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)主库1：show master status;
##查看主库的状态，从库通过主库的信息，方便与主库建立连接从库2、3：CHANGE master to master_host='192.168.168.11',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=857;
##与主库创建连接stop slave;start slave;
##开启从库show slave status\G;
##查看从库的状态`Slave_IO_Running: Yes`##从库和主机的读写是否正常。`Slave_SQL_Running: Yes`##slave的mysql状态是否正常。

二、读写分离

读写分离：主从的架构当中，主库负责写，从库负责读。

在这里插入图片描述

2.1、读写分离的方式：

1、代码开发人员纯靠代码完成，涉及到数据库的

二次开发。性能好，不需要额外的硬件设备

2、中间层代理代理服务器。在客户端和主从架构之间有一个代理服务器。代理服务器收到客户端的请求之后，通过客户端的sql语句来进行判断，读转到从，写转到主。

Amoeba：读写分离最常见的客户端代理软件。java代码开发的一个软件。

Master 服务器：192.168.168.11 mysql8.0
Slave1 服务器：192.168.168.12 mysql8.0
Slave2 服务器：192.168.168.13 mysql8.0
Amoeba 服务器：192.168.168.23 jdk1.6、Amoeba 代理服务器
客户端服务器：192.168.168.24 mysql maridb 客户端

----------------------搭建 MySQL读写分离--------------------------------

----Amoeba服务器配置----
##安装 Java 环境##
因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
//按yes，按entermv jdk1.6.0_14/ /usr/local/jdk1.6vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/binsource /etc/profile
java -version##安装 Amoeba软件##
mkdir /usr/local/amoeba
tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop说明安装成功##配置 Amoeba读写分离，两个 Slave 读负载均衡##
#先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问主库1、slave2、3CREATE USER 'amoeba'@'192.168.168.%' IDENTIFIED WITH mysql_native_password BY '123456';GRANT REPLICATION SLAVE ON *.* TO 'amoeba'@'192.168.168.%';GRANT ALL PRIVILEGES ON *.* TO 'amoeba'@'192.168.168.%';flush privileges;#再回到amoeba服务器配置amoeba服务：
cd /usr/local/amoeba/conf/cp amoeba.xml amoeba.xml.bak
vim amoeba.xml									#修改amoeba配置文件
--30行--
<property name="user">amoeba</property>--32行-- 
<property name="password">123456</property>--115行--
<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								#修改数据库配置文件
--23行--注释掉  作用：默认进入test库 以防mysql中没有test库时，会报错
<!-- <property name="schema">test</property> -->--26--修改
<property name="user">amoeba</property>--28-30--去掉注释
<property name="password">123456</property>--45--修改，设置主服务器的名Master
<dbServer name="master"  parent="abstractServer">--48--修改，设置主服务器的地址
<property name="ipAddress">192.168.233.21</property>--52--修改，设置从服务器的名slave1
<dbServer name="slave1"  parent="abstractServer">--55--修改，设置从服务器1的地址
<property name="ipAddress">192.168.233.22</property>--58--复制上面6行粘贴，设置从服务器2的名slave2和地址
<dbServer name="slave2"  parent="abstractServer">
<property name="ipAddress">192.168.233.23</property>--65行--修改
<dbServer name="slaves" virtual="true">--71行--修改
<property name="poolNames">slave1,slave2</property>/usr/local/amoeba/bin/amoeba start &					#启动Amoeba软件，按ctrl+c 返回netstat -anpt | grep java							#查看8066端口是否开启，默认端口为TCP 8066
netstat -anpt | grep java
tcp6       0      0 127.0.0.1:60543         :::*                    LISTEN      13477/java          
tcp6       0      0 :::8066                 :::*                    LISTEN      13477/java          
tcp6       0      0 192.168.168.23:54400    192.168.168.13:3306     ESTABLISHED 13477/java          
tcp6       0      0 192.168.168.23:40406    192.168.168.11:3306     ESTABLISHED 13477/java          
tcp6       0      0 192.168.168.23:34946    192.168.168.12:3306     ESTABLISHED 13477/java          ----测试读写分离 ----
yum install -y mariadb-server mariadb
systemctl start mariadb.service#在主从服务器上开启查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log在客户端服务器上测试：
mysql -u amoeba -p123456 -h 192.168.233.10 -P8066		
//通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器在主服务器上：
use db_test;
create table test (id int(10),name varchar(10),address varchar(20));在两台从服务器上：
stop slave;											#关闭同步
use db_test;
//在slave1上：
insert into test values('1','zhangsan','this_is_slave1');//在slave2上：
insert into test values('2','lisi','this_is_slave2');//在主服务器上：
insert into test values('3','wangwu','this_is_master');//在客户端服务器上：
use db_test;
select * from test;		//客户端会分别向slave1和slave2读取数据，显示的只有在两个从服务器上添加的数据，没有在主服务器上添加的数据insert into test values('4','qianqi','this_is_client');		//只有主服务器上有此数据//在两个从服务器上执行 start slave; 即可实现同步在主服务器上添加的数据
start slave;

测试结果：

主1从库2、3打开查询日志

tail -f /usr/local/mysql/data/mysql_general.log

insert into test values(‘3’,‘wangwu’,‘this_is_master’);

在这里插入图片描述

select * from test;

在这里插入图片描述

三、面试题

1、主从同步复制原理

主从同步复制原理主要涉及到数据库系统中的数据复制和同步机制，以确保数据在主服务器（主节点）和从服务器（从节点）之间保持一致。以下以MySQL和Redis为例，详细阐述主从同步复制的原理。

一、MySQL主从同步复制原理

MySQL的主从同步复制是其自带的功能，通过二进制日志（binlog）实现数据的复制和同步。具体原理如下：

binlog记录变更

：
- 当MySQL数据库执行数据的增、删、改操作时，这些事件会被记录到二进制日志（binlog）中。
- binlog是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
日志发送

：
- binlog中的日志内容会被发送到每台从服务器上。
从服务器接收并应用

：
- 在从服务器上有两个线程：I/O线程和SQL线程。
- I/O线程负责接收主服务器发送的binlog内容，并将其写入到中继日志（relay log）文件中。
- SQL线程读取relay log文件内容，对数据更新进行重放，最终保证主从数据库的一致性。
同步方式

：
- MySQL主从复制支持多种同步方式，包括异步复制、半同步复制和全同步复制。
- 异步复制：主服务器不需要等待从服务器确认即可提交事务，提供较好的性能但数据一致性稍差。
- 半同步复制：主服务器需要等待至少一个从服务器确认接收到事务的日志内容后才提交事务，提高数据一致性。
- 全同步复制：主服务器需要等待所有从服务器都确认接收到事务的日志内容后才提交事务，数据一致性最高但性能影响最大。

2、读写分离你们使用什么方式？

中间层代理代理服务器。在客户端和主从架构之间有一个代理服务器。代理服务器收到客户端的请求之后，通过客户端的sql语句来进行判断，读转到从，写转到主。

Amoeba：读写分离最常见的客户端代理软件。java代码开发的一个软件。 jdk1.6依赖环境。

3、如何查看主从同步状态是否成功

进入从的数据库，show slave status\G;

4、如果I/O不是yes呢，你如何排查？

查看配置文件是否有问题，查看mysql的uuid是否一致，若一致，进行修改。

show variables like "%uuid%";
##查看本机的uuid
select uuid();
##随机生成uuid
show variables like 'datadir';
##查看uuid配置文件的位置

5、show slave status能看到哪些信息（比较重要）

SHOW SLAVE STATUS命令在MySQL中用于查看主从复制（或主备复制）的状态信息。执行此命令后，会返回一系列与复制相关的属性和值，这些信息对于监控复制状态、诊断问题以及确保数据一致性至关重要。以下是一些通过SHOW SLAVE STATUS命令可以看到的关键信息：

Slave_IO_State

：
- 表示复制线程（IO Thread）的状态。例如，“Waiting for master to send event”表示IO线程正在等待主服务器发送事件。
Slave_IO_Running

：
- 表示复制IO线程是否正在运行。如果值为“Yes”，则表示线程正在运行；如果为“No”，则表示线程已停止。
Slave_SQL_Running

：
- 表示复制SQL线程是否正在运行。如果值为“Yes”，则表示线程正在运行；如果为“No”，则表示线程已停止。
Seconds_Behind_Master

：
- 表示从库与主库之间的延迟时间（以秒为单位）。如果值大于0，表示存在延迟；如果为0，则表示从库与主库同步是实时的；如果为NULL，则可能表示复制已经停止或未配置。
Master_Host, Master_User, Master_Port

：
- 分别表示主服务器的主机名、用户名和端口号，这些信息用于从库连接到主库。
Master_Log_File, Read_Master_Log_Pos

：
- 分别表示主服务器的二进制日志文件名和从库已经读取的二进制日志位置。这些信息有助于了解从库正在读取的主库日志信息。
Relay_Log_File, Relay_Log_Pos

：
- 分别表示从库的中继日志文件名和从库已经读取的中继日志位置。中继日志是从库用来存储从主库接收到的二进制日志事件的日志文件。
Relay_Master_Log_File

：
- 表示中继日志所记录的主服务器的二进制日志文件名，有助于确认中继日志与主库二进制日志的对应关系。
Last_Errno, Last_Error

：
- 分别表示上一次错误的错误代码和错误信息。这些信息对于诊断复制过程中的错误非常有用。
Connect_Retry

：
- 表示在发生连接错误后，重试连接的间隔时间（秒）。

执行SHOW SLAVE STATUS\G命令时，使用\G参数可以将结果以更易读的方式显示，每个属性占据一行，方便管理员阅读和解析。

6、主从复制慢（延迟）会有哪些可能？怎么解决？

硬件性能差异

：
- 主库和从库之间的硬件性能差异可能导致复制延迟。例如，从库的CPU、内存或存储性能较低，可能无法及时处理复制过来的数据。
网络延迟

：
- 网络带宽不足或网络延迟较高时，主库和从库之间的数据传输速度会受到影响，从而导致复制延迟。
主库负载过高

：
- 主库上的写操作过多，或者执行了大型事务，会导致主库的binlog产生速度加快，而从库可能无法及时接收和处理这些binlog。
从库负载过高

：
- 从库除了需要处理复制过来的数据外，还可能承担读操作，如果读操作过多，会占用从库的资源，导致复制处理速度变慢。
SQL执行效率低

：
- 从库上的SQL执行效率低，例如缺乏合适的索引、执行计划不合理等，会导致复制过程中的SQL执行时间延长。
复制策略选择不当

：
- MySQL默认的复制是异步的，虽然性能较好但数据一致性较差；全同步复制虽然数据一致性好但性能较差；半同步复制介于两者之间，但也可能因网络延迟等原因导致延迟。
数据库配置不当

：
- 数据库的某些配置参数设置不合理，如binlog格式、binlog缓冲区大小、innodb_flush_log_at_trx_commit等，可能影响复制性能。

解决方案

优化硬件性能

：
- 升级从库的硬件配置，如增加CPU核心数、提升内存容量、使用更快的存储设备等，以提高从库处理数据的能力。
优化网络环境

：
- 增加网络带宽、优化网络路由、减少网络延迟等，以提高主从库之间的数据传输速度。
减轻主库负载

：
- 对主库进行读写分离，将读操作分担到从库上；对主库上的大事务进行拆分，减少单个事务的执行时间。
合理分配从库资源

：
- 如果从库同时承担读操作和复制任务，需要合理分配资源，确保复制任务有足够的资源进行处理。
优化SQL执行效率

：
- 对从库上的SQL语句进行优化，如添加合适的索引、调整执行计划等，以提高SQL执行效率。
选择合适的复制策略

：
- 根据业务需求和数据一致性要求选择合适的复制策略。例如，对于实时性要求较高的系统，可以考虑使用半同步复制或全同步复制；对于实时性要求不高的系统，可以继续使用异步复制。
调整数据库配置

：
- 根据实际情况调整数据库的相关配置参数，如binlog格式、binlog缓冲区大小、innodb_flush_log_at_trx_commit等，以优化复制性能。
使用并行复制

：
- 在MySQL 5.6及更高版本中，支持并行复制功能。通过配置并行复制参数，可以允许多个从库线程同时处理binlog中的事件，从而提高复制效率。