日常使用的一些小玩意之 mysql

我的MYSQL的日常使用

数据库是一个十分重要的部分，我们日常的几乎所有的开发都是针对数据库的操作，如果哪一天你的数据库宕机了或者被入侵了，那将是十分严重的事故。
本文简要记录一些数据库的日常使用的一些事情。

1. 数据库的密码设置，
2. 数据库的访问权限，
3. 数据库的日志记录
4. 数据库的错误日志
5. 数据库的常见优化
6. 常见的sql语句
7. 大量数据的分类统计（补充）
8. 简单实用的脚本

1.密码 不必多言，长一点复杂一点。

2.访问权限

	先看MySQL安装好之后的 mysql库 user表		
![在这里插入图片描述](https://img-blog.csdnimg.cn/a28ab5102fed483883cfe3d409ce2d7d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAZ2liYnN3dw==,size_20,color_FFFFFF,t_70,g_se,x_16)

这里针对用户设置了IP 访问，这个是必要的。当然 设置证书访问是更好的，因先决条件限制，这个不支持证书就暂时这么设置
附上sql语句如下

GRANT ALL PRIVILEGES ON . TO ‘account’@‘120.120.120.12’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
这里就是 把account 账户设置了IP限制，ip为 120.120.120.12 密码为 password
其中 ip 也可以设置为 120.%.%.% 来表示 %符号是通配符。

MySQL日志

mysql 配置文件
my.cnf一般位于 /etc/my.cnf

这里配置了 mysql 日志信息。
log-error = /home/mysql_logs/mysql_error.log
这里是数据文件，数据库挂了之后，有了这个就可以恢复数据。
datadir=/home/home/db
这里是MySQL程序目录
basedir = /usr/local/mysql
二进制日志，这个是主从库需要使用到的，
log-bin = /home/mysql_logs/mysql-bin
这里没有开启慢查询，因为没有很多复杂业务在这里，都是简单的，而且已经稳定下来就没有加了

错误日志

mysql 的错误日志还是要定期检查一下，能发现一些问题。
比如sql执行错误，账户密码登录错误。
如下：典型的猜我测试服密码了。这样的 直接关闭root的远程访问，限制为localhost就好了。

mysql常见优化

sql 语句层面上就核心就索引使用以及一些特定的查询条件会造成扫描全表的。要想办法过滤。
常见的 有
1. 模糊查询 like 全模糊是不可使用索引的，对于右模糊查询，即like ‘…%’，是会使用索引的；
左模糊like‘%…’无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’；
2. 条件中使用了null 值。而且在一些多方对接时，往往null值需要另外处理，比较不符号程序规范。
3. 查询条件有不等于 <> != 这样的，会引起扫描全表。
4. 使用not in 做为连接条件
5. 使用count（*）
6. 使用参数作为条件，如 select account from member where nickname = @name，
这样的语句是在编译时不确定参数，所以不能使用索引。

代码层面上的需要注意的，循环内尽量不要执行mysql，尤其是多层循环内。
查询需要限定数量 limit ，避免使用select *  , 要明确字段，不做列运算，如SELECT id WHERE age + 1 = 10 这样的 会直接扫描全表，or条件改为in条件,且in的值数量 一般限定200以内，
连续的值 比如 id是自增的，这样查询 1-100之间的 不要使用in  要使用BETWEEN  1 and 100，
函数和触发器的功能可以放到程序实现。
一次执行大量数据，插入，修改，删除时候要拆分执行。
sql语句尽量简单
开启慢查询，根据慢查询日志可以发现有问题的sql，针对性的优化。

综合上面的避免扫描全表的。就能满足大多数的业务需求。

常见的sql语句

 msql 一般就是增删改查，简单的略过。
思考栗子：
  一个公司 有多款游戏运行，需要查询某一个游戏的的玩家在指定游戏内的各个玩法的通关情况。
  这个可以自行设计结构考虑一下。

大量数据的统计查询

常见的就是运营运营系统的开发。需要统计一些，日活，留存，下单，广告...等各类活动的效果
如果是mysql存储数据的形式，可以考虑以下思路。
功能拆分两部分：实时数据，历史数据。
 先说历史数据，这个是基本固化了的数据，如需要日报表就每天执行一次日报表相关的数据运算。
  如果单日历史数据量很大，一条或者几条统计查询处理不了的，可以使用存储过程，游标循环，临时表这样，在业务空闲时执行（我的设置时凌晨4点-6点之间，在从库上分模块执行前一天的数据统计）。
 实时数据，可以时历史数据+当天新增的数据，当天新增数据可以直接放到redis内，比如注册人数，充值金额，订单数量，留存等等都可以实现基本实时。这个实际应用一般都比较复杂，而且一些老旧系统可能面临更多的问题。需要因地制宜，灵活运用。

简单mysql 脚本

放一个简单的mysql 备份脚本
这里时备份了MySQL下的名为mysql的数据库的脚本

  	#!/bin/bash
  	#file:/root/mysqlbackup
  	backupdir=/opt/mysqlbackup/
  	cd "$backupdir"
  	time=`date '+%Y%m%d %H:%M:%S'`
  	mysqldump -uroot -proot mysql  | gzip> ./test-"$time".sql.gz  
  	
  	function write_log()
  	
  	{
  	  now_time='['$(date +"%Y-%m-%d %H:%M:%S")']'
  	  echo ${now_time} $1 | tee -a ${log_file}
  	}
  	
  	
  	log_file='/opt/mysqlbkup/mysqlbkup.log'
  	#write_log '####################################################'
  	write_log "myhsql备份完成，文件名称为（不含大括号）{ test-$time.sql.gz }"
  	#write_log '####################################################'
  			

然后修改计划任务
crontab -e; 执行命令行

crontab cron restart / service cron restart 重启cron服务；
例如 ： */1 * * * * curl servername/home/test/test > /dev/null 2>&1

每分钟向数据库中写入一条数据，定时任务每分钟执行url就行。
不会直接输出错误的信息,这时候可以在每条定时脚本后面加入 ： > /dev/null 2>&1
计划任务的五个* 各自代表的含义
分 时 日 月 周

* * * * *

如

* */5 * * *     /opt/a.sh    // 每5个小时
*/5  5 * * * curl -s http://www.demo.com?s=Member/updatedata    //5点到5.55分每5分钟一次
*/5 9,18 * * * curl -s http://www.demo.com?s=Member/updatedata    //9点到18点 每5分钟一次

* 5 * * *     /opt/a.sh                             //5点执行一次
5 5 * * * curl -s http://www.demo.com?s=Member/updatedata   //5点5分执行一次

任务1-执行一个.sh
任务2-执行一个url访问

写一个python 脚本监听事件 watch.py

# -*- coding: utf-8 -*-
#!/usr/bin/env python
import os
import datetime
import pyinotify
import logging
import send_vx
import time 
pos = 0
#times = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
 
def printlog():
    global pos
    try:
            fd = open(r'/opt/mysqlbkup/mysqlbkup.log')
            if pos != 0:
                fd.seek(pos,0)
            while True:
                line = fd.readline()
                if line.strip():
                    print(line.strip())
		    times =' ok  '+ time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())+' backup finished  '
		    send_vx.send_wechat(times)
                pos = pos + len(line)
                if not line.strip():
                    break
            fd.close()
    except Exception,e:
        print(str(e))
class MyEventHandler(pyinotify.ProcessEvent):
     
    #当文件被修改时调用函数
    def process_IN_MODIFY(self, event):
        try:
            printlog()
        except Exception,e:
            print(str(e))
 
def main():
    #输出前面的log
    printlog()
    # watch manager
    wm = pyinotify.WatchManager()
    wm.add_watch('/opt/mysqlbkup/mysqlbkup.log', pyinotify.ALL_EVENTS, rec=True)
    eh = MyEventHandler()
 
    # notifier
    notifier = pyinotify.Notifier(wm, eh)
    notifier.loop()
 
if __name__ == '__main__':
    main()

上面 调用 send_vx.send_wechat(times) 是企业微信的通知脚本
如下 :

# -*- coding: utf-8 -*-
# coding=utf-8
import json
import requests
import time
#企业唯一ID  企业微信ID 替换为自己的
corpid = "w********************"

#自定义应用密钥  替换为自己的
secret = "B***************************************************s"

#自定义应用编号 也替换为自己的
agentid = "***********2"

# 格式化成2021-11-20 11:45:39形式
times = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) 
def GetTokenFromServer(Corpid,Secret):                        
    access_token_url="https://qyapi.weixin.qq.com/cgi-bin/gettoken?corpid="+corpid+"&corpsecret="+secret
    r = requests.get(url=access_token_url)
    #print((r.json()['access_token']))
    return r.json()['access_token']

def send_wechat(str):
    access_token = GetTokenFromServer(corpid,secret)
    Url = "https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=%s" % access_token
    headers={
        "Content-Type": "application/json"
        }
    data={
        "touser": "* *******|ch**********3",    #消息接收人，多个用|分隔 如：  xxx|xxx|xxx
        #"toparty" : "1",    #如需要发送消息到整个部门的人，则使用该参数， 数字代表该部门 ID
        "agentid" : agentid,  #自定义应用编号
        "msgtype" : "text",    #发送类型
        "text": {
            "content": "updata finished ip =120.120.120.130 date = "+str    #内容
        }
    }
    data_dict = json.dumps(data, ensure_ascii=False).encode('utf-8')
    r = requests.post(url=Url, headers=headers, data=data_dict)
    print(r.text)


def send_wechat_error(str):
    access_token = GetTokenFromServer(corpid,secret)
    Url = "https://qyapi.weixin.qq.com/cgi-bin/message/send?access_token=%s" % access_token
    headers={
        "Content-Type": "application/json"
        }
    data={
        "touser": "Wa*******|ch**********",    #消息接收人，多个用|分隔 如：  xxx|xxx|xxx  这里是企业微信成员的企业微信id
        #"toparty" : "1",    #如需要发送消息到整个部门的人，则使用该参数， 数字代表该部门 ID
        "agentid" : agentid,  #自定义应用编号
        "msgtype" : "text",    #发送类型
        "text": {
            "content": "***important *** "+str    #内容
        }
    }
    data_dict = json.dumps(data, ensure_ascii=False).encode('utf-8')
    r = requests.post(url=Url, headers=headers, data=data_dict)
    print(r.text)   
#send_wechat()

linux
把 watch.py 执行一下 python ./watch.py & 开启监听，还可以改成其他多种形式的。代码细节有待优化。
这样子 一个功能简单的 自动备份通知到企业微信的小脚本就完成了。

end