Linux基础——MySQL（四）主从_有主从账号的概念

概述

应用

场景

1. 从服务器作为主服务器的实时数据备份
2. 主服务器实现读写分离，从服务器实现负载均衡
3. 把多个从服务器更具业务重要性进行拆分

创业型公司架构部署分析

互联网80%读，20%写

负载均衡器承受三个web连接，数据库要承受五百个连接且要支持后续增长，所以建立从数据库，主同步数据到从，两相数据一致。由于互联网80%都是读，20%是写，所以进行读写分离操作，减轻主服务器负担。Web写找mysql主库，主库将变化的语句记录到bin-log日志中，bin-log日志同步到从数据库进行执行，达到双方数据一致。所以使用负载均衡器指向两个从数据库，从从数据库读取数据，减轻主数据库负担。

NFS对web服务器进行挂载，放静态数据；同时也要保证数据安全，所以需要备份服务器。此时也就是全网备份了。（数据库的备份是在从服务器上进行的，也就是温备份，在表锁定的情况下进行备份，此时用户不可写。）

 另部署一个管理服务器，安装Zabbix监控工具。

 从库不够用，可以再加，但是最多不能超过五台，否则会拖累主库。此时就可以添加数据库缓存（后端缓存，如：redis）。当web要写新数据，会对MySQL主库和数据库缓存双写，读的时候先调取缓存中的内容；如果缓存中没有内容，那么就会从从库中读取，然后再写入数据库缓存，最后返还web。所以在数据库缓存中存储的都是热数据，也就是经常被用户访问的数据。降低了数据库的读压力。（类似于CPU从内存中读取数据，内存从硬盘中读数据。）

开发在工作中需要制作绘图统计方面服务，所以一般会再启用一台从库。这个库不是用来读数据的，而是用来做开发分析，测试的。

另起一个服务器用来增量和全量备份，启用bin-log日志。

所以五台数据库，最多三个用来读，一个给开发做测试，一个用来做全备，增备。

MHA是数据库的备库，一旦主库宕机，他就会启用。所以一般为二主五从。

主从复制原理

MySQL的主从复制不是实时同步的，是异步同步的，所以会产生数据延迟。

实时同步：从数据到数据

异步同步：经过以此或多次中转

1. 修改两台服务器的server_id，让两台服务器区分主从；
2. 主库打开二进制日志（log-bin=mysql-bin），启动I/O线程，这个线程记录二进制日志。一旦产生了改变数据的SQL语句，I/O线程就会 把这些SQL语句记录到二进制日志中。
3. 从库打开中继日志（relay-log=relay-bin），生成I/O线程和SQL线程。I/O线程监控主库的二进制日志，一旦主库的二进制日志发生变化，从库的I/O线程就会找主库的I/O线程，让主库的I/O线程将数据复制给它，并将其写入从库的中继日志中。从库的SQL线程通过对中继日志的解析，将可执行的SQL语句解析并执行到从库中。
4. 主库创建主从复制账号：grant replication slave on *.* to…。要验证想要连接的从库。
5. 从库记录主库的IP，端口，二进制日志的位置，二进制日志的名字，主从复制账号和密码，这六个条件。
6. 激活从库

master.info记录跟踪主从之间复制过的数据

relay-log.info记录中继日志到哪个文件，进行到什么位置。

部署过程

1.

需要两台机器一个作为主库，一个作为从库。两台机器必须都是源码按章MySQL，不能使用yum安装客户端，也不能克隆。

2.

[root@server ~]# vim /etc/my.cnf

//进入主库，开启log-bin日志，查看server-id

[root@server ~]# /etc/init.d/mysqld restart

//重启MySQL服务

[root@server ~]# ss -antup | grep 3306

//查看服务端口是否开启

[root@server ~]# ls /usr/local/mysql/data/

//查看二进制日志是否生成

3.

[root@client ~]# vim /etc/my.cnf

//进入客户机MySQL主配置文件，关闭二进制日志，设置server-id，手动添加relay-bin=relay-bin

[root@client ~]# /etc/init.d/mysqld restart

//重启数据库

删除/usr/local/mysql/data里面的二进制文件。

4.

[root@server ~]# mysql -uroot -pabc123,

//进入主库

mysql> grant replication slave on *.* to 'rep'@'%' identified by '123123';

//创建主从验证账号

replication slave类型具备近似root的supper权限，禁止写入挡不住supper权限。

mysql> flush table with read lock;

*解锁：mysql>unlock tables;

//对数据库锁表只读（不要关闭当前窗口，否则将解除锁表，也不要退出数据库）

mysql> show variables like '%timeout%';

//查看数据库的环境变量

数据库在锁表状态，任何对数据库的修改操作都不能执行。

5.

再打开一个主库的x-shell窗口

[root@server ~]# mysqldump -uroot -pabc123, --opt --all-databases | gzip -9 > /tmp/mysql_all.sql.gz

//对主库进行完全备份

[root@server ~]# rsync -avz /tmp/mysql_all.sql.gz root@192.168.200.6:~/

//使用rsync将完全备份推送到从服务器

（也可以使用scp命令远程复制过去）

注意：前提是从服务器要安装rsync或主服务器具有scp

6.

[root@client ~]# gzip -d mysql_all.sql.gz

//客户端解压全备文件

[root@client ~]# mysql -uroot -pabc123, < mysql_all.sql

//将备份文件导入到从库

[root@client ~]# mysql -uroot -pabc123, -e 'show databases;'

//查询数据是否与主库一致

此时两方数据一致

7.

mysql> show master status;

//查看主库二进制日志写到哪儿了

8.

[root@client ~]# mysql -uroot -pabc123,

//登录从库

mysql> CHANGE MASTER TO MASTER_HOST='192.168.200.4',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='123123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=248;

//MASTER_HOST=主库IP

  MASTER_POPT=主库端口，从库端口可以与主库不同

  MASTER_USER=主库创建的用于复制的用户

  MASTER_PASSWORD=主库创建的用于复制的用户的密码

  MASTER_LOG=在主库使用show master status查看到的二进制文件名称

  MASTER_LOG_POS=在主库使用show master status查看到的二进制文件偏移量

再打开一个从库的x-shell窗口

[root@client ~]# cd /usr/local/mysql/data/

//进入data目录

此时从库已经有记录主库与自己之间数据的master.info文件

9．

在从库的MySQL库中：

mysql> start slave;

//启动服务

mysql> show slave status\G;

//显示从库状态（\G竖着显示字段数据）

I/O=yes，SQL=yes表示主从搭建成功！

如果I/O线程不是yes，那么检查防火墙，selinux。

如果SQL线程为no，一般为在从库创建后，主库也创建一样的，这样从库的SQL线程就会显示为NO，表示从库已经不能再创建相同内容。删除从库上与主库不同的内容，然后关闭主从同步，再打开即可。

10.

测试：

在主库上解锁表或者直接关闭哪个窗口

mysql> drop database benet;

//主库删除benet库

mysql> show databases;

//从库查询不到benet库。

主从搭建成功！

命令

回顾

grant replication slave on *.* to '账号'@'地址' identified by '密码';    设置登录验证账号

relay-log=relay-bin       中继日志

flush table with read lock;  对数据库进行锁表只读

unlock table;   对数据库解除锁表只读

show variables like ‘%timeout%’;    查看数据库环境变量

show master status;    查看二进制日志写位置

\G    竖着显示字段数据

2021-1-21

MySQL主从复制读写分离

概念

Amoeba（变形虫）致力于MySQL的分布式数据库前端代理，主要为应用层访问MySQL时充当SQL路由功能，能够将相关的SQL路由到目标数据库。

特点：  负载均衡

        高可用性

        SQL过滤

        读写分离

        可并发请求多台数据库

例题

1.假如有开发想要申请数据库访问权限，根据项目安全，需要写入主库，读取

从库权限，如何设置账号？主从账号名字是否一致？密码是否一致？主从权限是否一致？

答： 全部一致。

2.如果备份的地方是从库，那么进行备份后，备份的也是从库的账号系统？如

果主库的从府库账号系统和从府库账号系统不一致，那么主库坏了，导入的却是从库的全备，主库的账户变成从库后该怎么办？

答：修改从库的配置文件，在mysql模块下加入一条语句：read_only=1，让空库永久性锁表只读，这样从库即便有锁权限，也不可写。

3.为什么主从复制账号，必须用replication slave类型？

答：因为replication slave类型具备近似root的supper权限，禁止写入挡不住supper权限。

4.如何防止开发意外写道从库配置文件？

答：修改从库的配置文件，在mysqld的模块下，加上read_only = 1让从库永久性锁表只读。

实验

主从环境：

mysql> drop database benet2;

//先在从库上删除benet2库；

mysql> drop database benet2;

//再在主库上删除benet2

mysql> show slave status\G;

//在从库：查看从库状态

因为从库先删除了benet2库，主库删除后，从库再执行一遍命令，没有benet2可删，所以SQL线程报错。

从库操作：

mysql> set global sql_slave_skip_counter=1;

//将sql线程同步指针向下移动一个，多次不同可以重复操作

mysql> stop slave;

//关闭从库

mysql> start slave;

//启用从库

mysql> show slave status\G;

//查看从库状态

工作中，编写脚本时，可以使用以下命令查看线程状态：

[root@client ~]# mysql -uroot -pabc123, -e 'show slave status\G;' | grep Slave_IO_Running

//过滤显示I/O线程

[root@client ~]# mysql -uroot -pabc123, -e 'show slave status\G;' | grep Slave_SQL_Running

//过滤显示SQL线程

如果这此项为no，那么检索Last_SQL_Errno：和Last_SQL_Error：这两项是否含有create、update、inster、drop、delete等字段，如果含有任何之一，那么就是创建数据冲突了，一律执行跳帧语句，然后关闭，开启从库连接。以此来维持24小时主从架构不断开。

数据

安全

假如主库数据，因此和从库不一致，那么只需要找个凌晨时间，将主库锁表，全备导出，导入到从库即可汇入主从数据的一致性。所以在某些企业，数据并非最重要的，最重要的是要保证架构的安全。

主从延迟解决

数据库只读

从变主