Linux基础——MySQL（四）主从

概述
应用 场景	从服务器作为主服务器的实时数据备份 主服务器实现读写分离，从服务器实现负载均衡 把多个从服务器更具业务重要性进行拆分
创业型公司架构部署分析	互联网80%读，20%写 负载均衡器承受三个web连接，数据库要承受五百个连接且要支持后续增长，所以建立从数据库，主同步数据到从，两相数据一致。由于互联网80%都是读，20%是写，所以进行读写分离操作，减轻主服务器负担。Web写找mysql主库，主库将变化的语句记录到bin-log日志中，bin-log日志同步到从数据库进行执行，达到双方数据一致。所以使用负载均衡器指向两个从数据库，从从数据库读取数据，减轻主数据库负担。 NFS对web服务器进行挂载，放静态数据；同时也要保证数据安全，所以需要备份服务器。此时也就是全网备份了。（数据库的备份是在从服务器上进行的，也就是温备份，在表锁定的情况下进行备份，此时用户不可写。）  另部署一个管理服务器，安装Zabbix监控工具。  从库不够用，可以再加，但是最多不能超过五台，否则会拖累主库。此时就可以添加数据库缓存（后端缓存，如：redis）。当web要写新数据，会对MySQL主库和数据库缓存双写，读的时候先调取缓存中的内容；如果缓存中没有内容，那么就会从从库中读取，然后再写入数据库缓存，最后返还web。所以在数据库缓存中存储的都是热数据，也就是经常被用户访问的数据。降低了数据库的读压力。（类似于CPU从内存中读取数据，内存从硬盘中读数据。） 开发在工作中需要制作绘图统计方面服务，所以一般会再启用一台从库。这个库不是用来读数据的，而是用来做开发分析，测试的。 另起一个服务器用来增量和全量备份，启用bin-log日志。 所以五台数据库，最多三个用来读，一个给开发做测试，一个用来做全备，增备。 MHA是数据库的备库，一旦主库宕机，他就会启用。所以一般为二主五从。
主从复制原理	MySQL的主从复制不是实时同步的，是异步同步的，所以会产生数据延迟。 实时同步：从数据到数据 异步同步：经过以此或多次中转 修改两台服务器的server_id，让两台服务器区分主从； 主库打开二进制日志（log-bin=mysql-bin），启动I/O线程，这个线程记录二进制日志。一旦产生了改变数据的SQL语句，I/O线程就会 把这些SQL语句记录到二进制日志中。 从库打开中继日志（relay-log=relay-bin），生成I/O线程和SQL线程。I/O线程监控主库的二进制日志，一旦主库的二进制日志发生变化，从库的I/O线程就会找主库的I/O线程，让主库的I/O线程将数据复制给它，并将其写入从库的中继日志中。从库的SQL线程通过对中继日志的解析，将可执行的SQL语句解析并执行到从库中。 主库创建主从复制账号：grant replication slave on *.* to…。要验证想要连接的从库。 从库记录主库的IP，端口，二进制日志的位置，二进制日志的名字，主从复制账号和密码，这六个条件。 激活从库 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小时主从架构不断开。
数据 安全	假如主库数据，因此和从库不一致，那么只需要找个凌晨时间，将主库锁表，全备导出，导入到从库即可汇入主从数据的一致性。所以在某些企业，数据并非最重要的，最重要的是要保证架构的安全。
主从延迟解决
数据库只读
从变主