MYSQL数据库跨服务器实时同步更新实践----文献阅读（污水管网水质预测）

摘要

本周一是通过学习数据库MYSQL 的底层结构知识，以实现数据库的实时同步更新。但要求需要在不同的服务器上实现，此次实现利用虚拟机搭配环境，配置系统网络服务，构建同一个局域网下的多个服务器IP。再通过本机windows的mysql 数据库作为主库，以虚拟上的lunxi系统的mysql数据库作为从库。

二是阅读了关于城市管道水质预测的文献，该论文从多源数据融合预测城市污水管网水质的深度学习算法，主要的创新点就在发现多源数据与城市排水管网水质指数数据之间存在着重要的映射关系，将环境指标，社会指标，水量指标，水质指标等融合成多源数据，并利用在线测得的前11个指标的数值，作为输入数据值；而不容易测试的后五个BOD5、COD、NH4-N、TN和TP值，作为输出值，这样做节约时间与成本，提高预测精度。该论文的模型框架设计较为简单与常见，只是应用MLR、传统机器学习模型（多层感知器，MLP）和三种深度学习模型RNN、LSTM和GRU；然后分别训练对比评估参数，发现GRU的性能最佳。

一. MYSQL 数据库主从复制，实时同步的再现

1. 1 先下载虚拟机（硬件）与lunxi系统（centos7）

步骤：

1. 下载虚拟机：下载方法与步骤
2. 下载centos7 DVD版 镜像

如果版本与centos7 不匹配，则需要升级安装：VM16pro

装系统：在“虚拟电脑”里装centos: 编辑虚拟机设置 -CD/DVD中 指定 centos的文件位置 - 开启：新建虚拟机

关于vmware中 计算机名字：

虚拟机名：bigdata001
计算机名：bigdata001
网络中的名字：bigdata001
用户名：root
密码：root
默认超级管理员：root/自己设置

3. 磁盘布局：
   /boot：系统启动时 需要的内存 200M
   /：系统的可用磁盘大小 10240M
   swap:交换内存 ，建议大小 和内存一致 2048M
4. 查看计算机名：hostname
   修改计算机名：/etc/sysconfig/network
5. 配置网关（分配网络）
   

6.配置网络服务

7.防火墙：关闭
centos:
service iptables stop 关闭防火墙
chkconfig iptables off 禁止开机自启
查看防火墙状态service iptables status
windows:
网络-右键 属性-关闭防火墙
8. 自动同步时间：
设置时间同步，为防止多个节点之间通信时，时间不同步，造成服务拒绝的现象。
安装用于同步时间的 ntp插件 ：
yum -y install ntp ntpdate
ntpdate cn.pool.ntp.org
将系统时间写入硬盘时间
hwclock --systoch
9. windows真实机 通过SecureCRT 操作centos
下载SecureCRT
通过SecureCRT 对centos进行文件的上传下载：yum install lrzsz
上传：切换到当前目录 cd /tmp， 直接用鼠标拖拽,Zmodem…
10. 集群（3台） ：完整克隆
bigdata01 -> bigdata02/bigdata03

克隆完毕后：
	a.将从计算机 的内存适当降低
	
	b.修改从计算中 唯一性 的配置（ip、uuid、hostname映射）
		在克隆后的bigdata02/bigdata03中
		
		打开：vi /etc/sysconfig/network-scripts/ifcfg-ens33
		
	修改ip地址：
		删除uuid/hwaddr(在重启时，自动生成)
		删除文件 rm -r  /etc/udev/rules.d/70-persistent-net.rules 输入yes

		计算机名：vi /etc/sysconfig/network
		  更改名字：hostnamectl set-hostname bigdata002
		  
		
	c.更改映射
	  
	在centos:
		vi /etc/hosts,
		追加：
		192.168.2.128 bigdata01
		192.168.2.129 bigdata02
		192.168.2.130 bigdata03

	在本机windows:
		C:\Windows\System32\drivers\etc\hosts
		追加：
		192.168.2.128 bigdata01
		192.168.2.129 bigdata02
		192.168.2.130 bigdata03
		
		重启命令：
		systemctl restart network 

再检查是否ping通：ping www.baidu.com 查看是否能连接上Windows的网

11. SSH免密钥登陆

私钥：密钥留在本机
公钥: 密钥发给本机
bigdata01:
生成密钥：ssh-keygen -t rsa   (密钥存放位置：/root/.ssh ，  id_rsa:私钥     id_rsa.pub)
发送私钥（本机）： ssh-copy-id localhost    ， 需要输入bigdata01节点(root)的密码
发送公 钥（其他计算机）：ssh-copy-id bigdata02， 需要输入bigdata02节点(root)的密码
测试免密钥登陆：
	ssh localhost
	ssh bigdata02
	
用上述方法，给每2台计算机 设置免密钥

1.2 在lunxi 系统安装rpm 版mysql 5.5.55

步骤：

1. 先去官网下载安装报包到本地
   使用SecureCRT 对centos进行文件的上传下载：yum install lrzsz

2. 使用命令安装：rpm -ivh rpm文件名（服务端+客服端）
   （若发生冲突，需要先卸载对赢得mysql -lib 文件）

3. 安装时需要修改密码

4. 验证MYSQL:
   mysqladmin --version

5. 启动: service mysql start
   关闭：service mysql stop
   重启：service mysql restart

6. 继续更改配置mysql配置文件，编码问题（详见视频，有点繁琐）

1.3 在Windows 系统中安装新的MYSQL5.55

步骤：按道理 先应该彻底删除之前的MYSQL文件及注册文件（本机采用两个端口号安装了两个mysql ,但只不同时启动）

1. 去下载Windows版mysql安装包（不叙述了就）
2. 在navicat 中测试连接是否成功（注意更改端口号与从库lunxi 一致）

1.4 主从复制的原理与实践

实现主从同步的原理：

1.master将改变的数 记录在本地的 二进制日志中（binary log);该过程：二进制事件
2.slave 将master 的binary log 拷贝自己的relay log 中继日志文件）中
3.中继日志文件事件，将数据读取到自己的数据库中
4. MYSQL 主从复制 是异步的，串行的，有延迟的（master:slave=1:n）

实战将windows - bigdata001实现主从同步，实时同步（跨服务器，跨系统）

	windows主机（IP：192.168.2.2，mysql 用户名：root，密码：root）
	bigdata001：从机（IP：192.168.2.128，mysql 用户名：root，密码：root）

1.关闭防火墙
centos7：
systemctl stop firewalld
systemctl disable firewalld
Windows：去网络中关闭即可
2. 允许远程访问操作——（在centos 和Windows中分别操作）：
grant all privileges on . to ‘root’@’%’ identified by ‘root’ with grant option ;
flush privileges;

3.配置master(windows) my.ini
[mysqld]
#id
server-id=1
#二进制日志文件
log-bin=“D:/Program Files/MySQL/MySQL Server 5.5/data/mysql-bin”
错误记录日志文件
log-error=“D:/Program Files/MySQL/MySQL Server 5.5/data/mysql-error”
#主从同步时 忽略的数据库
binlog-ignore-db=mysql
#可以指定需要同步的数据库
binlog-do-db=test
保存，重启即可。

4. 处理mysql权限：
开放(windows/centos)远程访问MySql的权限：（授权）
登录Mysql管理员账户：在主机中授权哪些机子是从机
grant all privileges on . to ‘root’@‘192.168.2.%’ identified by ‘root’ with grant option ;
flush privileges;
本地的mysql可以被： 192.168.2.% ，root/root 访问

5. 查看主机状态：show master status;
   记录：（ mysql-bin.000001 | 107) 文件位置（配置从库要用）

6. 配置slave(centos)
进入my.cnf : vi /etc/my.cnf 修改命令
[mysqld]
server-id=2（待会可以设置成全局变量 set global server_id=2;）
log-bin=mysql-bin
replicate-do-db=test

windwos/centos需要在配置完毕后 重启

7. 设置主从关系 ：
slave ->master
显示master的位置
通过以下命令查看master的信息：
（windows）show master status;
File: mysql-bin.000001
Position:107

salve：指定master (slave指定master作为自己的老大)
（bigdata001,mysql命令）

change master to
master_host=‘192.168.2.2’,
master_user=‘root’,
master_password=‘root’,
master_port=3306,
master_log_file=‘mysql-bin.000001’,
master_log_pos=107;

若报错：先停止之前的同步：stop slave;

9. 开启slave （bigdata001,mysql命令）:
start slave ;

10. 验证主从是否成功执行：
show slave status \G
必须保证以下两个为yes：
Slave_IO_Running:
Slave_SQL_Running:

如果有问题:
1.查看日志：Last_IO_Errno(1539)/Last_SQL_Errno 
	本次提示：master和slave有相同的server-id 
2.查看日志：Last_IO_Errno(2003)/Last_SQL_Errno 
本次再次提示端口号或者授权访问受阻

11.再次尝试：
stop slave ;
start slave;

12. 在navicat 中验证是否同步：
    在主数据库（Windows mysql中）的test库中新建表success:
    

1.5 总结

mysql 数据库的主从复制，实时同步数据终于实现了，在虚拟机服务器，与搭配的windows 中，同一个局网中，使用上述的二进制日志与mysql数据库的配置功能可以不需要借助第三软件实现。

本周文献阅读

二. A deep learning algorithm for multi-source data fusion to predict water quality of urban sewer networks

（多源数据融合预测城市污水管网水质的深度学习算法）

作者：Yiqi Jiang, Chaolin Li,, Lu Sun, Dong Guo, Yituo Zhang, Wenhui Wang
单位：中国深圳哈尔滨工业大学土木与环境工程学院
中国哈尔滨工业大学城市水资源与环境国家重点实验室

本文摘要

城市排水管网点源污染是一个难以监测和控制的难题。为了解决这一问题，在下水道水质评价和预测中必须跟踪关键水质指标。然而，其中一些重要的化学指标（如生物需氧量（BOD5）、化学需氧量（COD）、氨氮（NH4-N）、总氮（TN）和总磷（TP））需要大量的时间和精力进行测量，这将对污水管网中的预测产生不利影响。现有的统计方法和机器学习算法不能有效地解决检测时间问题或提供有限的精度。此外，这些方法中缺乏考虑的各种因素导致预测性能不令人满意。很少有研究考虑城市多源数据对下水道网络水质预测的影响，同时发展统计方法或机器学习算法。为了解决这个问题，我们提出了一种基于多源数据融合的深度学习方法。该方法综合考虑以下指标对排水水质进行分析和预测：环境指标（如面积和直径）；社会指标（如人口）；水量指标（如饮用水供应、污水流量、水流速度和液位）；以及易于监控的水质标准指标（如pH值、温度和电导率）。为了验证这种方法的有效性，我们在中国南方的一个城市进行了一个案例研究。通过与线性方法（多元线性回归，MLR）和传统学习算法（多层感知，MLP）的比较，发现包含递归神经网络（RNN）、长短时记忆（LSTM）和选通递归单元（GRU）的深度学习算法具有良好的预测性能，其中GRU对水质化学指标的预测能力较强，学习曲线较快。结果表明，GRU 的 R2 比 RNN 和 LSTM 高 0.82%–5.07%，R2 比传统机器学习算法高 9.13%–15.03%，R2 比线性方法高 37.26%–43.38%。

问题引入

1. 污水管网水质的评估和预测必须跟踪关键水质指标，如pH值、电导率、温度、生物需氧量（BOD5）、化学需氧量（COD）、铵离子氮（NH4-N）、总氮（TN）、总磷（TP）等。其中一些指标，如pH值和电导率，可以通过在线监测设备实时检测。然而，收集和测量排水网络的一些关键水质指标，如BOD5、COD、NH4-N、TN和TP，需要大量的时间和精力。采样和测试相关的时间需求对于污水管网的水质预测和管理是不利的。
2. 深度学习在水文学领域有很多应用，但在城市排水管网水质预测中的应用非常有限。此外，考虑到许多可能影响城市污水管网水质的因素，如人口、排水管网服务区面积、居民生活习惯、管径大小和城市饮用水供应，预测复杂城市排水管网的水质是一项非常具有挑战性的任务。
3. 提出了一种基于多源数据融合的深度学习算法对城市污水管网水质进行预测。
   本研究的目的：
   1.城市多源数据与城市排水管网水质指数数据之间存在着重要的映射关系。
   2.与线性方法和传统的机器学习算法相比，基于深度学习的方法为城市排水管网提供了更准确的水质预测结果。

模型设计

很常见的研究框架：

首先，收集多源数据。然后进行数据预处理，主要用于将原始数据转换为可计算格式。数据处理后，数据集按4:1的比例分为训练集和测试集。根据输入和输出，线性方法（多元线性回归，MLR）、传统机器学习模型（多层感知器，MLP）和三种深度学习模型，包括递归神经网络（RNN）、长短时记忆（LSTM）和选通递归单元（GRU），用于预测下水道管网的水质。同时，对模型的性能进行了评价。

模型评估

本研究采用两个评估标准：相对均方根误差（RMSE）与拟合系数（R^2）。

实验数据

1.连续35天的水质数据，共有6个水质监测站点，在一个城区里，一天分五个时间段测量。

2.数据集包括 环境指标：土地面积（LA）、排水管直径（D），社会指标（人口）、水量指标4个（饮用水供应（DWS）、污水流量、流速（V）和液位）和水质指标9个（pH、温度（T）、电导率、悬浮固体（SS）、BOD5、COD、NH4-N、TN和TP）。我们调查和收集了监测点的环境和社会指标，并对污水管网的水质和水量数据进行了采样和测量，共有16个指标。

3. 将前28天当成训练数据集，后7天分成测试数据集。

在五种不同类型的模型（MLR、MLP、RNN、LSTM和GRU）。

输入：11个易于测量和获取的指标，如LA、D、人口、DWS、流量、V、液位、pH、T、电导率和SS，；
输出：5个指标，如BOD5、COD、NH4-N、TN和TP。

其中n 是指标数，m是每个指标的属性。

将提出的四种机器学习模型（MLP、RNN、LSTM、GRU）可以同时预测多个指标，即11个输入变量可以同时实现5个变量的输出。 这些模型会受到不同输入变量的各种范围的影响。 因此，MLP模型的输入变量矩阵Xi（等式（6））和RNN模型的输入变量矩阵Xt（等式（8）、（10）和（15））都设为[v’ 1, v’2, …, v’11] ∈V’n×m，输出矩阵均设为[v’12, v’13, v’14, v’15, v’16] ∈V 'n×m。

创新点

1. 深度学习在水文学邻域应用非常广泛，但在城市管网污水预测方面却应用受限。所以本研究 旨在发现 多源数据与城市排水管网水质指数数据之间存在着重要的映射关系。
2. 多源数据是指环境指标，社会指标，水量指标，最后是水质指标。
3. 将非常容易在线测得的前11个指标的数值，作为输入数据值；而不容易测试的后五个BOD5、COD、NH4-N、TN和TP值，作为输出值。该预测方法能够通过易于获取和测量的指标，高效、快速地预测水质。它可以解决与样品采集和化学分析相关的处理时间长和成本高的问题。
4. 将MLR与MLP，RNN，LSTM，GRU分别训练模型，分别进行评估对比，得出GRU的性能是最好的。
5. 实验数据拥有较为广泛的数据集，并采用了不同模型对比，使本研究具有一定的可靠性。

思考点

1. 深度学习模型的水质预测性能可能不仅取决于模型本身和数据集的数量，还取决于从用于训练学习模型的数据集中选择的输入指标。但在本研究中没有体现，需要探索每个输入指标对模型预测的贡献值（注意力机制），这样做可能会降低预测成本。
2. 在城市河流水域中，做水质预测指标时，我们可不可以将社会环境，社会指标，水量指标，等相关因素，加入训练样本中，以扩大训练样本特征，提高预测的精准度。
3. 本研究只是利用最原始基本的深度学习模型，我们可以进一步改进优化这些模型，看看效果是不是会更好。