MySql主从集群搭建

一、主从复制原理

1.Master主数据库在事务提交时，会把数据变更记录在二进制日志文件Binlog当中。

2.slave从数据库的IO线程读取主库的Binlog文件，写入到从库的中继日志文件Relay Log。

3.slave读取Relay log文件，将改变反映到自己的数据中。

二、集群搭建

1. 虚拟机准备

准备至少两个服务器

2. mysql安装（主从都要操作）

（1）官网下载安装包

（2）解压到mysql

（3）安装mysql

（4）启动mysql

命令：systemctl start mysqld

查看mysql状态：

进入mysql: 

mysql -u root -p

查看默认密码：默认密码随机生成，图中红线标出。

设置密码相关限制：

set global validate_password.policy = 0;

set global validate_password.length = 4;

更改密码：

ALTER  USER  'root'@'localhost'  IDENTIFIED BY '1234';

创建一个可以让其他主机访问的用户：

create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '1234';

给他分配权限：

grant all on *.* to 'root'@'%';

使用Navicat连接两台服务器mysql

3.主库配置

1. 编辑主库配置：vim /etc/my.cnf

添加图片中最后两行

server-id=1 配置id号，唯一

read-only=0 可以读也可以写 =1只能读。

2. 重启mysql :systemctl restart mysqld

3.登录mysql,创建远程连接账号，并授予主从复制权限

创建用户：CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456' ;

授权主从复制：GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

4.查看主节点binlog日志文件状态

4.从库配置

1. 编辑从库配置：vim /etc/my.cnf

2. 重启mysql :systemctl restart mysqld

3.连接主库配置

进入mysql根据主库ip以及主库建立的用户信息执行以下命令

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.150.128', SOURCE_USER='itcast',SOURCE_PASSWORD='Root@123456', SOURCE_LOG_FILE='binlog.000004',SOURCE_LOG_POS=663;

4.开启从库

5.查看从库状态

三、测试

1.查看数据库

主库：

从库：

2. 在主库 192.168.200.200 上创建数据库、表，并插入数据

sql:

四、性能测试

1.测试工具下载

2.创建测试数据库

CREATE DATABASE test_db;

3.读写测试：

sysbench \
  --db-driver=mysql \
  --mysql-host=192.168.150.128 \         // ip地址
  --mysql-user=root \                              // 用户名
  --mysql-password=1234 \                    // 密码    
  --mysql-db=test_db \                            // 测试数据库名
  oltp_read_write \                                  //测试读写混合负载
  --tables=10 \                                        // 10个表
  --table-size=100000 \                          // 测试表的大小
  --threads=16 \                                      // 测试线程数
  --time=60 \                                           // 测试总时间
  --report-interval=10 \                            // 每 10 秒打印一次报告    
  run

4.测试结果：

thds: 并发线程数，在您的测试中设置为16。
tps: 每秒事务数（Transactions Per Second）。例如，在第40秒时，tps: 226.75 表示每秒钟处理了约227个事务。
qps: 每秒查询数（Queries Per Second）。例如，在第40秒时，qps: 4529.93 表示每秒钟执行了大约4530次查询。
r/w/o: 分别表示读（read）、写（write）和其他（other）操作的数量。例如，在第40秒时，r/w/o: 3170.32/906.11/453.50 表示每秒有约3170次读取操作、906次写入操作和454次其他操作。
lat (ms,95%): 95%的请求在该毫秒数内完成。例如，在第40秒时，lat (ms,95%): 118.92 表示95%的请求在118.92毫秒内完成。
err/s: 每秒错误数量。所有时间段均为 0.00，表示没有发生错误。
reconn/s: 每秒重新连接的数量。所有时间段均为 0.00，表示没有重新连接的情况。
SQL 统计
queries performed:
read: 总共执行了458,710次读查询。
write: 总共执行了131,060次写查询。
other: 其他类型的查询（例如 DDL 操作）总共执行了65,530次。
total: 总共执行了655,300次查询。
transactions: 总共处理了32,765个事务，平均每秒处理545.27个事务（TPS）。
queries: 总共执行了655,300次查询，平均每秒执行10,905.36次查询（QPS）。
ignored errors: 忽略的错误数为0，表示在整个测试过程中没有出现任何错误。
reconnects: 重新连接次数为0，表明在整个测试期间没有发生数据库连接丢失的情况。
一般统计
total time: 整个测试运行时间为60.0887秒。
total number of events: 总共发生的事件数为32,765，这与事务数相同，每个事件对应一个事务。
延迟（Latency）
min: 最小延迟为6.45毫秒。
avg: 平均延迟为29.32毫秒。
max: 最大延迟为316.60毫秒，可能是因为某些操作遇到了资源瓶颈或锁等待等情况。
95th percentile: 95%的请求在45.79毫秒内完成，这意味着绝大多数请求的响应时间都在这个范围内。
sum: 所有请求的总延迟时间为960,744.48毫秒。
线程公平性
events (avg/stddev): 每个线程平均处理了2,047.8125个事件，标准差为26.39，表明各线程之间的负载分配相对均匀。
execution time (avg/stddev): 每个线程的平均执行时间为60.0465秒，标准差为0.03秒，说明所有线程的执行时间非常接近，系统负载均衡良好。

5.修改参数继续测试（32线程）

sysbench \
  --db-driver=mysql \
  --mysql-host=192.168.150.128 \
  --mysql-user=root \
  --mysql-password=1234 \
  --mysql-db=test_db \
  oltp_read_write \
  --tables=10 \
  --table-size=100000 \
  --threads=32 \
  --time=60 \
  --report-interval=10 \
  run

6.只读测试

sysbench \
  --db-driver=mysql \
  --mysql-host=192.168.150.129 \
  --mysql-user=root \
  --mysql-password=1234 \
  --mysql-db=test_db \
  oltp_read_only \
  --tables=10 \
  --table-size=100000 \
  --threads=32 \
  --time=60 \
  run

7.只写测试

sysbench \
  --db-driver=mysql \
  --mysql-host=192.168.150.128 \
  --mysql-user=root \
  --mysql-password=1234 \
  --mysql-db=test_db \
  oltp_write_only \
  --tables=10 \
  --table-size=100000 \
  --threads=32 \
  --time=60 \
  run