mysql的主从复制、读写分离--负载均衡

1、什么是mysql的主从复制

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。

MySQL 默认采用异步复制方式，从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。

2、主从复制优点

数据热备：主库故障后，可切换到从数据库继续工作，一定程度上实现高可用。

读写分离：让主库负责写，从库负责读，使数据层能支持更大的并发；在报表统计等业务形态中尤其重要。比如后台生成报表的多表查询sql语句非常的慢，导致锁表，影响前台实时服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台实时业务的响应速度。

架构扩展：业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

3、主从复制原理

一句话概括就是把主服务器上的 binlog日志复制到从服务器上再执行一遍，具体过程：

(1)master必须启用二进制日志，二进制binlog日志中记录了每一次数据变化事件

(2)slave会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开启一个I/O Thread请求master二进制日志中的事件

(3)master为slave的I/O Thread启动一个dump线程(二进制日志转储线程)，检查自己二进制日志中的事件，跟对方请求的位置对比后，向其发送二进制事件

(4)slave接收到master发送过来的数据把它放置到中继日志(Relay log，也是二进制)文件中；并记录该次请求到master的具体哪一个二进制日志文件内部的哪一个位置

(5)slave将启动一个sql Thread从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致；并记录relay log中该次执行的位置信息

(6)最后I/O Thread和SQL Thread将进入睡眠状态，等待下一次被唤醒

---

mysql主从复制： 

server1中：

停掉nginx、开启mysqld、并且吧mysqld给server2传输过去

nginx -s stop   #停掉nginx
cd 
/etc/init.d/mysqld  start
scp -rp /usr/local/mysql/ server2:/usr/local/
scp /etc/init.d/mysqld server2:/etc/init.d/
scp /etc/my.cnf server2:/etc/
vim /etc/my.cnf
  添加：log-bin=mysql-bin
       server-id=1
/etc/init.d/mysqld restart

 server2中：停掉httpd、配置mysqld

systemctl stop httpd
groupadd -g 1001 mysql
useradd -u 1001 -g mysql -M -d /data/mysql -s /sbin/nologin mysql
id mysql
vim ~/.bash_profile
  第10行：PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
source ~/.bash_profile
which mysql
mkdir -p /data/mysql
chown mysql.mysql /data/mysql/
ps ax    #一定要查看进程，kill掉之前的进程之后在初始化
mysqld --initialize --user=mysql   #重新初始化的时候要把/data/mysql/目录下的东西清空
/etc/init.d/mysqld  start   #还要启动mysqld
cd /data/mysql
cat mysqld.log | grep password
chmod 755 mysqld
mysql_secure_installation
mysql -pwestos
vim /etc/my.cnf
  添加：collation-server=utf8mb4_general_ci
       server-id=2
/etc/init.d/mysqld restart

 

 server1:

mysql -pwestos
-->CREATE USER 'repl'@'%' IDENTIFIED BY 'westos';
-->GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-->SHOW MASTER STATUS;

 server2:

mysql -pwestos
--> stop slave;
-->CHANGE MASTER TO MASTER_HOST='172.25.70.1', MASTER_USER='repl', MASTER_PASSWORD='westos', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=987;
--> start slave;
-->show slave status\G;

 

 说明：以上步骤没有做锁表和库的同步步骤，因为再做此实验时主机和备机的库时一样的；
假如主机中比备机多了一个westos库，要将westos库同步过去的话：
server1主机中：

mysqldump -pwestos westos > dump.sql
vim dump.sql   #查看刚导入的库
scp dump.sql server2:  #把刚导入的库的文件给备机server2传输过去【注意绝对路径】

server2备机中：

cd ~
vim dump.sql
mysqladmin -pwestos create westos
mysql -pwestos westos < dump.sql

mysql -pwestos
-->show databases;#可以看到从主机中同步过来的westos数据库
-->use westos
-->select * from user_tb;

server1主机中：

mysql -pwestos
-->use westos
-->insert into user_tb values ('user1','123');
-->insert into user_tb values ('user2','123');

 在server1和2主从关系搭建好之后：可以在server1主机中创建一个库，在server2查看同步消息

 

---

mysql主从复制的优化：
server1主机中： 

vim /etc/my.cnf
  添加：gtid_mode=ON
       enforce-gtid-consistency=ON
/etc/init.d/mysqld restart

 server2备机中：

vim /etc/my.cnf
  添加：gtid_mode=ON
       enforce-gtid-consistency=ON
/etc/init.d/mysqld restart
mysql -pwestos
-->stop slave;
-->change master to master_host='172.25.70.1', master_user='repl', master_password='westos', master_auto_position=1;   #使用gtid
-->start slave;
-->show slave status\G;
   -->Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

 server1主机中：

mysql -pwestos
--> use westos
--> insert into user_tb values ('user3','123');  #给表插入信息

 server2备机中：

mysql -pwestos
> show databases;   #查看所有的库
> use westos  进入westos库中
> select * from user_tb;   #看user_tb表中信息。可以看到主机中的表信息已经同步了

---

 mysql半同步复制【io线程的优化】
server1主机中：

mysql -pwestos
--> install plugin rpl_semi_sync_master soname 'semisync_master.so';
--> select plugin_name, plugin_status
 -> from information_schema.plugins
 -> where plugin_name like '%semi%';
--> set global rpl_semi_sync_master_enabled = 1;
vim /etc/my.cnf
  添加：rpl_semi_sync_master_enabled = 1
/etc/init.d/mysqld restart
mysql -pwestos
--> show variables like 'rpl%';

 

 server2备机中：

mysql -pwestos
--> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
--> set global rpl_semi_sync_slave_enabled = 1;
vim /etc/my.cnf
  添加：rpl_semi_sync_slave_enabled = 1
/etc/init.d/mysqld restart  #执行数据库重启命令后，再看'rpl%'的status会直接时ON的状态，但不执行的话需要关闭在开启之后'rpl%'的status才会是ON状态
【stop slave io_thread #关闭slave的io。start slave io_thread #开启slave的io】
mysql -pwestos
--> show variables like 'rpl%';
--> show status like 'rpl%';

 server1主机中：

mysql -pwestos
--> show status like 'rpl%'; #此时是ON的状态
--> use westos;
--> insert into user_tb values ('user4','123');

 server2备机中：

mysql -pwestos
--> select * from westos.user_tb; #可以同步到刚才建立的表里信息
--> stop slave io_thread;

 server1主机中：

mysql -pwestos
--> insert into user_tb values ('user7','123'); #会等待10秒，此时是异步
--> show status like 'rpl%'; 

 server2备机中：

mysql -pwestos
--> start slave io_thread;

server1主机中：

mysql -pwestos
--> insert into user_tb values ('user8','123');
--> show status like 'rpl%';

 server2备机中：

mysql -pwestos
--> select * from westos.user_tb; #可以同步到刚才建立的表里信息

---

 mysql延迟：
server2备机中：

mysql -pwestos
--> show processlist;
> STOP SLAVE SQL_THREAD;
> change master to master_delay = 30;
> START SLAVE SQL_THREAD;

server1主机中：

mysql -pwestos
--> insert into user_tb values ('user9','123');

server2备机中：

mysql -pwestos
> show slave status\G;
> show slave status\G;  #一致刷这个命令，等到30秒完了可以看到备机中有刚才主机中建立的user5的信息了
> select * from westos.user_tb;

 

 

---

mysql多主模式

server2（1的备机，3的主机）机中：

vim /etc/my.cnf
  添加：slave_parallel_workers=16
      slave_parallel-type=LOGICAL_CLOCK
      master_info_repository=TABLE
      relay_log_info_repository=TABLE
      relay_log_recovery=ON
/etc/init.d/mysqld restart 
mysql -pwestos
--> show processlist;
cd /usr/local/
ls
scp -rp /usr/local/mysql/ server3:/usr/local/
scp /etc/init.d/mysqld server3:/etc/init.d/
scp /etc/my.cnf server3:/etc/

 

server3（2的备机中）：

groupadd -g 1001 mysql
useradd -u 1001 -g mysql -M -d /data/mysql -s /sbin/nologin mysql
id mysql
vim ~/.bash_profile
source ~/.bash_profile
which mysql
mkdir -p /data/mysql
chown mysql.mysql /data/mysql/
mysqld --initialize --user=mysql
cd /data/mysql
cat mysqld.log | grep password
chmod 755 mysqld
mysql_secure_installation
mysql -pwestos
vim /etc/my.cnf
  改为：server-id=3
  注释掉：#rpl_semi_sync_slave_enabled = 1
        #slave_parallel_workers=16
        #slave_parallel-type=LOGICAL_CLOCK
        #master_info_repository=TABLE
        #relay_log_info_repository=TABLE
        #relay_log_recovery=ON
/etc/init.d/mysqld start

注意：以上启动不成功进入/data/mysql/目录执行rm -rf * 之后再次初始化

server1中：

mysqldump -pwestos westos > dump.sql
scp dump.sql server3:

server3（2的备机中）：

cd ～
mysqladmin -pwestos create westos
mysql -pwestos westos < dump.sql
mysql -pwestos
--> use westos
--> select * from user_tb;  #同步过来2上的库里的信息了

server2（1的备机，3的主机）机中：

vim /etc/my.cnf
  添加：log-bin=mysql-bin
       log-slave-updates=ON
/etc/init.d/mysqld restart 
mysql -pwestos
--> grant replication slave on *.* to repl@'%' identified by 'westos';
--> flush privileges;
--> select * from westos.user_tb;

server3（2的备机中）：

mysql -pwestos

--> change master to master_host='172.25.70.2', master_user='repl', master_password='westos', master_auto_position=1;
--> start slave;
--> show slave status\G;

server1中：

mysql -pwestos
> use westos
> insert into user_tb values ('user10','123');

#过30秒后2和3都会同步到刚才在westos数据库中建立的user_tb表里的信息，因为上一个实验做了30秒的延迟。

---

组复制：（一组最少三个成员，最多九个成员）
先清除之前的数据库；因为组复制对数据库的校验非常高

server1中：

 注意：引导程序只由一台服务器完成即可，这里server1引导了，所以接下来的server2和server3就不需要这一步了。

 server2:

清除之前的数据，在修改密码时必须一致

 

 server1中查看

 server3同理:  清除之前的数据

  server1中查看：三台机子都是online状态

 server2、server3 。在server2和3中查看刚才server1的消息已经同步过来了

 server2中写入数据（可以直接同步到server3和server1中）

 server1和3中：

  server3中写入数据（可以直接同步到server2和server1中）

  server1和2中：

 server3：停掉mysqld服务

 server1中只有server1和2是online状态

 server2中：可以同步过来刚server1中写的表数据

 server3：开启server3中的mysqld服务

  server1中：三台机子都是online状态

 server3：开启server3之后还能同步到刚才关掉服务时server1写的表数据

---

 读写分离--负载均衡
server1中：

mysql -pwestos
--> show variables like 'slow%';
--> set global slow_query_log=ON;
--> show variables like 'long%';
--> show variables like 'slow%';
--> select sleep(10);;

 

 sleep 等待了十秒开启虚拟机server4；
在server4中：

lftp 172.25.254.250;
~> cd pub/docs/mysql/
～> get mysql-router-community-8.0.21-1.el7.x86_64.rpm
~> exit
rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm  #安装rpm包
cd /etc/mysqlrouter/
vim mysqlrouter.conf 
  给最后添加：
  [routing:ro]   #只读
  bind_address = 0.0.0.0
  bind_port = 7001          #端口号
  destinations = 172.25.11.2:3306,172.25.11.3:3306
  routing_strategy = round-robin  #轮循

  [routing:rw]    #读写
  bind_address = 0.0.0.0
  bind_port = 7002
  destinations = 172.25.11.1:3306,172.25.11.2:3306
  routing_strategy = first-available  #从前往后找，先找1在找2
systemctl start mysqlrouter.service   #开启服务
netstat -antlp  #查看刚才配置文件的两个端口是否启动

server1中：

mysql -pwestos
--> grant all on test.* to dmq@'%' identified by 'westos';
--> flush privileges;

真机中：

mysql -h 172.25.11.4 -P 7001 -u dmq -p test #7001是只读
--> show tables;
--> select * from t1;

server2：
yum install -y lsof
lsof -i :3306  #查看mysql的端口号连接哪一台服务器

 server3：
yum install -y lsof
lsof -i :3306

 
说明：用真机执行mysql -h 172.25.11.4 -P 7001 -u dmq -p test。在server2或server3中用lsof -i :3306命令查看，会轮流在2或3上，实现负载均衡 

server1：
yum install -y lsof
lsof -i :3306

 真机中：
mysql -h 172.25.11.4 -P 7002 -u dmq -p test #7002是只读

说明：用真机执行mysql -h 172.25.11.4 -P 7002 -u dmq -p test。在server1或server2中用lsof -i :3306命令查看，会轮流在1或2上，实现负载均衡