mysql之主从复制和读写分离

一、主从复制

1、定义

主mysql上的数据（新增或修改库、表里的数据）都会同步到从mysql上

2、mysql的主从复制模式（面试题）

（1）异步复制（常用）：默认的复制模式。客户端提交一个事务，主mysql会立即把结果返回给从服务器，主mysql不在意从mysql是否已接收并处理（只管发送，不在意结果）

问题：主一旦崩溃，主mysql的事务可能没有传到从mysql上，此时强行将从提升为主，可能新的主mysql上数据不完整（较少见，工作环境全是异步复制）

（2）全同步复制（少用）：主库执行完成一个事务之后，所有的从库也都执行该事务后才会返回客户端

缺点：因为需要等待所有从库全部执行完成，性能降低。适用于对数据一致性和完整性要求很高的场景

（3）半同步复制（少用）：介于异步复制和全同步复制之间。主库执行完一个客户端提交的事务之后，至少等待一个从库接收并处理完成之后才返回给客户端。适用于电视的网络中

优点：在一定程度上提高了数据安全性

缺点：有一定的延迟。延迟时间一般是一个TCP/IP的往返时间round-trip time[RTT]（从发送到接收的时间，单位：ms）

3、主从复制的原理（重点）

（1）Master的更新事件(update、insert、delete)会按照顺序写入二进制日志bin-log中。当Slave连接到Master的后,Master机器会为Slave开启binlog dump线程,该线程会去读取bin-log日志

（2）Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。

（3）Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行

4、Slave_IO_Runnin:NO该怎么办？（重点）

（1）网络问题

（2）myql.cnf配置文件错误

（3）绑定主从同步配置CHANGE master to master_host='192.168.233.21',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;

要么文件名错误，要么位置偏移量错误

（4）防火墙和安全机制问题（工作中没有此问题）

5、mysql实现读写分离的方式（重点）

amoeba、mycat

6、如何查看主从同步是否成功？（重点）

show slave status\G

slave_IO_Running和slave_sql_running都是yes

Slave_IO_Running: Yes #负责与主机的IO通信

Slave_SQL_Running: Yes #负责自己的slave mysql进程

并且在主库创建一个库或表，查看主库、从库是否同步

7、show slave status\G能看到的信息有哪些？（重点）

1. IO和sql的线程状态信息
2. master服务器的IP地址、端口和事务开始的位置
3. 最近一次的错误信息和错误位置
4. 最近一次的IO报错信息
5. 最近一次的sql报错信息

8、主从复制延迟问题（重点）

（1）网络延迟

（2）主从硬件设备（CPU主频率、内存IO性能、硬件IO性能）

（3）使用了同步复制，而不是异步复制

如何解决？

（1）硬件方面：主库一般不需要改动，从库硬件配置要升级，提升随机写的性能（硬盘可换成固态、升级CPU的核数、扩展内存。尽量使用物理机，不要用云服务器）

（2）网络方面：主从服务器均在一个局域网内，尽量避免跨网段和跨机房

（3）架构方面：做读写分离，主服务器负责写，从库负责读，降低从库的压力

（4）mysql配置方面：从配置文件的角度实现性能最大化

1）追求安全性

①innodb_flush_log_at_trx_commit=1

每次事务提交时都会刷新日志，确保持久性。1是最高级别的数据安全性，但是会影响性能，默认是1（生产环境）

innodb_flush_log_at_trx_commit=0事务提交时不会立刻刷新，二十每秒刷新一次，可以提高性能，但是发生故障会导致数据丢失（安全性降低）

innodb_flush_log_at_trx_commit=2事务提交时日志不会写入硬而是写入缓存，也不会进行刷新，提升一定的安全性和性能，但对内存要求较高

②sync_binlog=1默认是1。每次提交事务后，直接把二进制日志刷新到磁盘，确保日志的持久性，占用较高性能，但安全性高

sync_binlog=0将二进制日志写入到缓存，也不会刷新日志，发生故障会丢失数据，同时对内存要求高

sync_binlog=N（N代表自定义）。每几个事务执行一次刷新到磁盘。提高性能，但一旦崩溃，数据会大量丢失

2）追求性能化

①sync_binlog=0

②innodb_flush_log_at_trx_commit=2

③logs-slaves-updates=0从库的更新不会写入二进制日志（断掉从库。不建议）

④innodb_buffer_pol_size 500G

设置存储引擎的缓冲池大小。设置的数值越高，可以提高innodb的性能。更多的数据和索引都可以缓存在内存中，减少磁盘的访问次数，对系统内存要求较高

二、读写分离

1、必须在主从复制的基础上实现读写分离

2、定义

所有的写入操作都在主库，从库只负责读select。如果有更新，是从主库复制到从库的

3、用什么方式实现mysql读写分离？

amoeba、mycat

三、实验

mysql之主从复制实验

实验思路：

实验条件：

mysql1——主服务器——20.0.0.13

mysql2——从服务器1——20.0.0.23

mysql3——从服务器2——20.0.0.33

实验步骤：

1、主、从服务器的时间同步

安装时间同步工具

2、修改时间同步配置文件

（1）主服务器——mysql1

fudge 127.127.0.0 stratum 8从本地获取时间源同步，不从网络获取

数字越小，时间精确度越高。设置fudge 8（时间层级是8，最高是15）

此0是网段的第3位数，网段的第3位数是什么，这里就是什么

重启ntp服务

（1）从服务器1——mysql2

①开启时间同步

②时间同步到主服务器

③创建定时任务【每隔半小时主从服务器时间同步一次】

（3）从服务器2——mysql3

①开启时间同步

②时间同步到主服务器

③创建定时任务【每隔半小时主从服务器时间同步一次】

④开启同步命令工具，查看当前时间

3、主服务器mysql1

（1）修改mysql的配置文件

（2）进入数据库，给从服务器赋予复制权限【从库可以访问主库】

注：每操作一步数据库，这个偏移量的值都会发生变化，因此查看状态后，数据库最好就不要继续再操作任何内容了

4、从服务器1mysql2

（1）修改mysql配置文件

relay-log=relay-log-bin:

指定了从服务器上中继日志的基本文件名。在这个例子中，

中继日志的文件名将以 "relay-log-bin" 开头。

relay-log-index=slave-relay-bin.index:

指定了中继日志索引文件的名称。中继日志索引文件用于记录中继日志文件的顺序和位置

在这个例子中，索引文件名为 "slave-relay-bin.index"

relay_log_recovery=1:

用于配置从服务器在启动时是否执行中继日志的恢复操作。设置为 1 表示启用中继日志的恢复，通常在从服务器出现异常或崩溃后重启时使用。这有助于确保从服务器能够从主服务器的二进制日志中正确地读取和应用中继日志，以保持数据一致性

（2）进入数据库，配置主从同步

5、从服务器2mysql3

（1）修改配置文件vim /etc/my.cnf

（2）进入数据库，配置主从同步

Slave_IO_Running: Yes #负责与主机的IO通信

Slave_SQL_Running: Yes #负责自己的slave mysql进程

6、验证主从服务器同步

（1）创建库是否同步

（2）创建表是否同步

（3）更新表的内容验证是否同步

（4）从服务器更新，主服务器是否会同步更新？

不会。主从复制是单向的，只能从主服务器复制到从服务器

mysql之amoeba实现读写分离实验

实验思路：基于主从复制的基础上才能实现读写分离

实验条件：

mysql1——主服务器——20.0.0.13——mysql服务

mysql2——从服务器1——20.0.0.23——mysql服务

mysql3——从服务器2——20.0.0.33——mysql服务

test1——读写分离服务器——20.0.0.10——jdk1.6、Amoeba   

test2——客户端——20.0.0.20

实验步骤：

先做主从复制实验

再做读写分离实验

读写分离调度器test1

1、安装java环境

因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用

./jdk-6u14-linux-x64.bin执行java环境

（1）重命名（为了后面配置环境简单）

（2）修改java配置文件vim /etc/profile

2、安装amoeba服务

3、配置 Amoeba读写分离，两个 Slave 读负载均衡

（1）先在主、从服务器的mysql上开放权限给 Amoeba 访问

（2）再回到amoeba服务器配置amoeba服务

①修改用户信息配置文件

②修改数据库信息配置文件

③启动amoeba

按Ctrl+C返回

④查看8066端口是否开启

4、在客户端测试读写分离

（1）安装mariadb服务

（2）在主从服务器上开启查询日志

（3）通过amoeba服务器代理访问mysql ，在通过客户端连接mysql后写入的数据只有主服务会记录，然后同步给从--从服务器

（4）在客户端服务器上测试

①第一次查看表

②第二次查看表

结论：查数据库，访问的是从服务器，并且会轮询从服务器实现select

③客户端插入表记录

mysql之mycat实现读写分离实验

实验思路：基于主从复制的基础上才能实现读写分离

实验条件：

mysql1——主服务器——20.0.0.13——mysql服务

mysql2——从服务器1——20.0.0.23——mysql服务

mysql3——从服务器2——20.0.0.33——mysql服务

test3——读写分离服务器——20.0.0.30——jdk1.6、mycat  

test2——客户端——20.0.0.20

实验步骤：

1、安装java环境

2、安装mycat工具

wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

3、创建/apps文件夹，并解压mycat包至/apps下

4、设置变量环境

5、启动mycat，查看日志文件，最后可以看到启动成功

6、客户端安装mariadb

yum install -y mariadb-server mariadb

systemctl start mariadb.service

netstat -antp | grep 3306

7、客户端连接数据库

这里密码初始为123456   需要加端口

8、修改mycat配置文件

去掉44行行注释，对应的在51行行末注释，删除50行行末注释，5 * 60 * 1000L; //连接空>    闲检查

#修改45行端口号为3306

9、主服务器授权

10、客户端连接调度器测试

11、客户端测试读写分离

（1）在主从服务器上都打开通用日志

第一次查询

结论：读实现轮询

插入数据

结论：读写分离

mysql之主主复制实验

实验思路：第二台服务器不再作为从，而是两台都是作为主数据库

实验条件：

mysql1——主服务器1——20.0.0.13——从服务器2

mysql2——主服务器2——20.0.0.23——从服务器1

实验步骤：

1、两台主服务器均安装mysql服务

2、主服务器1mysql1

（1）修改配置文件 vim /etc/my.cnf

（2）赋予从数据库所有权限

3、从服务器1mysql2

（1）修改配置文件vim /etc/my.cnf

（2）绑定同步主服务器1数据库

4、配置主服务器2mysql2

5、配置从服务器2mysql1

绑定同步主服务器2的数据库

6、测试主从同步复制

（1）测试第一台服务器

查看从服务器是否同步数据

（2）测试第二台服务器

查看从服务器是否同步数据

结论：mysql实现完全同步。主服务器的更新同步到从服务器，从服务器的更新也能同步到主服务器