系统:ubuntu 20.04
mysql:8.0
1.安装MySQL
sudo apt-get install mysql-server
2.查看MySQL状态
service mysql status
3.设置密码
mysql -u root -p
use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by 'xxxx';
flush privileges;
exit;
4.重启MySQL服务并查看状态
service mysql restart
5.新建表
create database emqx;
use emqx;
CREATE TABLE data_file_name(id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, xxx VARCHAR(255) NOT NULL, time VARCHAR(255) NOT NULL , fileName VARCHAR(255) NOT NULL);
6.查看表数据类型是否符合要求
desc data_file_name;
7.编写Redis转存MySQL脚本
# coding=utf-8
import os
import time
import redis
import pymysql
import warnings
warnings.filterwarnings("ignore")
class RedisToMysql:
def __init__(self):
try:
pool = redis.ConnectionPool(host='localhost', port=6379, db=0, password='xxxx')
self.redis_cli = redis.Redis(connection_pool=pool)
self.conn = pymysql.connect(host='localhost', port=3306, db='xxxx', user='root', password='xxxx')
self.cur = self.conn.cursor()
self.sql = "insert into data_file_name (sn,time,fileName) values ('{}','{}','{}')"
self.logRootPath = os.getcwd() + "/Log/"
self.logPath = ''
self.check_log_data()
except Exception as e:
print('__init__Error:' + str(e))
def check_log_data(self):
try:
CurrentData = time.strftime("%Y-%m-%d", time.localtime())
self.logPath = self.logRootPath + CurrentData + '.txt'
if not os.path.exists(self.logPath):
file = open(self.logPath, 'w')
file.write("")
file.close()
except Exception as e:
print('check_log_data_Error:' + str(e))
def main(self):
count = 0
while True:
# 获取redis中所有SN
logInfo = ''
CurrentDatatime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
names = self.redis_cli.keys()
for name in names:
# 根据SN获取对应的所有数据并进行排序
sn = name.decode()
# print(f"SN:{sn}")
# logInfo += CurrentDatatime + " " + "[Log]" + " " + f"SN:{sn}" + '\n'
dataSet = self.redis_cli.hgetall(name)
dataSort = sorted(dataSet.items(), key=lambda dataSet: dataSet[0], reverse=False)
saveBinFile = b''
dataFileName = []
deleteFileName = []
packageIndex = []
for data in dataSort:
cont = data[1]
contTitle = ''.join(['%02X' % b for b in cont[:10]])
timeStr = contTitle[14:16] + contTitle[12:14] + contTitle[10:12] + contTitle[8:10] + contTitle[6:8] + contTitle[4:6] + contTitle[2:4] + contTitle[:2]
unixTime = int(timeStr, 16)
# print(f"unixTime:{unixTime}")
# logInfo += CurrentDatatime + " " + "[Log]" + " " + f"unixTime:{unixTime}" + '\n'
sum = int(contTitle[16:18], 16)
# print(f"sum:{sum}")
# logInfo += CurrentDatatime + " " + "[Log]" + " " + f"sum:{sum}" + '\n'
cur = int(contTitle[18:20], 16)
# print(f"cur:{cur}")
# logInfo += CurrentDatatime + " " + "[Log]" + " " + f"cur:{cur}" + '\n'
# 第0个包重置变量
if not cur:
saveBinFile = b''
dataFileName = []
packageIndex = []
deleteFileName = []
dataFileName.append(data[0])
packageIndex.append(cur)
saveBinFile += cont[10:]
else:
if packageIndex[-1] != cur:
dataFileName.append(data[0])
packageIndex.append(cur)
saveBinFile += cont[10:]
deleteFileName.append(data[0])
# 对完整的数据进行存储并删除源文件
if cur == sum - 1 and len(dataFileName) == sum and packageIndex == [i for i in range(sum)]:
# print(f"len:{len(saveBinFile)}")
# logInfo += CurrentDatatime + " " + "[Log]" + " " + f"len:{len(saveBinFile)}" + '\n'
# 存储bin文件
with open(os.getcwd() + r'/results/' + str(sn) + str(unixTime) + '.bin', 'wb') as f:
f.write(saveBinFile)
with open(r'/home/esp32/share/' + str(sn) + str(unixTime) + '.bin', 'wb') as f:
f.write(saveBinFile)
print(f"save {sn}{unixTime}.bin success")
logInfo += CurrentDatatime + " " + "[Log]" + " " + f"save {sn}{unixTime}.bin success" + '\n'
# 存储Mysql
self.conn.ping(reconnect=True)
self.cur.execute(self.sql.format(sn, unixTime, str(sn) + str(unixTime) + '.bin'))
self.conn.commit()
print(f"save data to Mysql success")
logInfo += CurrentDatatime + " " + "[Log]" + " " + f"save data to Mysql success" + '\n'
# 删除redis中的数据
for dfname in deleteFileName:
res = self.redis_cli.hdel(name, dfname)
if res:
print(f'delete {name} {dfname} Success')
logInfo += CurrentDatatime + " " + "[Log]" + " " + f'delete {name} {dfname} Success' + '\n'
else:
print(f'delete {name} {dfname} Failed')
logInfo += CurrentDatatime + " " + "[Log]" + " " + f'delete {name} {dfname} Failed' + '\n'
print(f"delete Redis data success")
logInfo += CurrentDatatime + " " + "[Log]" + " " + f"delete Redis data success" + '\n'
# 根据日期分存log
if count < 3600:
count += 1
else:
count = 0
self.check_log_data()
# 如果logInfo有数值则写入Log文档
if logInfo:
with open(self.logPath, 'a') as f:
f.write(logInfo)
time.sleep(1)
if __name__ == '__main__':
rtm = RedisToMysql()
rtm.main()
8.安装python相关库
sudo pip3 install redis
sudo pip3 install pymysql
9. 生成相关文件夹
mkdir xxx
10.后台运行脚本
nohup python3 -u main.py > test.out 2>&1 &
11.查看脚本运行状态
ps -ef | grep python3
10---.开始可以先用指令运行脚本确定没有错误后再后台运行脚本
sudo python3 main.py