阿里云服务器部署（3）---安装mysql

系统: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