再入 MySQL 的门

[Bpazy]

用 MySQL 很久了，记录一些知识点。比如创建新用户，mysqld_exporter 创建方法等等

[Bpazy]

root 用户无法给新用户授权

新创建了一个数据库，发现用 root 用户连上之后，无法给新创建的用户授权，提示 access denied for user 'root'@' ' to database 'behappy_prod'。

调查后发现是 root 用户没有 grant 权限:

mysql> select host,user,grant_priv,super_priv from user;
+-----------+---------------+------------+------------+
| host      | user          | grant_priv | super_priv |
+-----------+---------------+------------+------------+
| localhost | mysql.session | N          | Y          |
| localhost | mysql.sys     | N          | N          |
| %         | root          | N          | Y          |
| %         | test          | N          | N          |
+-----------+---------------+------------+------------+

那么加上权限即可:

update mysql.user set Grant_priv='Y',Super_priv='Y' where user = 'root' and host = '%';
flush privileges;

注意需要重启 MySQL 服务: sudo systemctl restart mysql

[Bpazy]

索引、字段类型和长度、新建表的话容量、数据库抖动、sql性能

[Bpazy]

mysql 8.0 允许 root 外部访问

1. /etc/mysql/mysql.conf.d/mysqld.cnf 中将 bind-address 改为 0.0.0.0
2. 登录到 mysql: sudo mysql
3. 创建新的 root 用户（因为存在的是 'root@localhost'，它只是本地访问）:

CREATE USER 'root'@'%' IDENTIFIED BY '123';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; 

[Bpazy]

安装 mysqld_exporter

首先创建 exporter 用户：

$ mysql -u root -p

mysql> CREATE USER 'exporter'@'%' IDENTIFIED BY 'XXXXXXXX' WITH MAX_USER_CONNECTIONS 3;
Query OK, 0 rows affected (0.10 sec)

mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';
Query OK, 0 rows affected (0.10 sec)

k8s 配置:

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    name: mysqld-exporter
  name: mysqld-exporter
  namespace: monitoring  
  annotations:
    reloader.stakater.com/auto: "true"
spec:
  replicas: 1
  selector:
    matchLabels:
      name: mysqld-exporter
  template:
    metadata:
      labels:
        name: mysqld-exporter
    spec:
      containers:
      - image: prom/mysqld-exporter
        name: mysqld-exporter
        ports:
        - containerPort: 9104
          name: http
        resources:
          limits:
            cpu: 200m
            memory: 200Mi
          requests:
            cpu: 100m
            memory: 100Mi
        volumeMounts:
        - name: secret-volume
          mountPath: /.my.cnf
          subPath: my.conf
          readOnly: true
      volumes:
      - name: secret-volume
        secret:
          secretName: mysqld-exporter
---
apiVersion: v1
kind: Service
metadata:
  name: mysqld-exporter
  namespace: monitoring
spec:
  type: ClusterIP
  clusterIP: 10.43.112.32
  selector:
    name: mysqld-exporter
  ports:
  - port: 9104
    targetPort: 9104

记得先创建 secret:

apiVersion: v1
kind: Secret
metadata:
  name: mysqld-exporter
  namespace: monitoring
type: Opaque
data:
  my.conf: |
    W2NsaWVudF0NCnVzZXIgPSBleHBvcnRlcg0KcGFzc3dvcmQgPSB4eHh4eHh4DQpbY2xpZW50LnNlcnZlcnNdDQp1c2VyID0gZXhwb3J0ZXINCnBhc3N3b3JkID0geHh4eHh4eA==

其中的那段 base64 decode 之后是这样:

[client]
user = exporter
password = xxxxxxx
[client.servers]
user = exporter
password = xxxxxxx

最后配置 prometheus:

# 省略了其他配置
scrape_configs:
    - job_name: mysql
      scrape_interval: 30s
      params:
        auth_module: [client.servers]
      static_configs:
        - targets:
          - 192.168.31.31:3306
      relabel_configs:
        - source_labels: [__address__]
          target_label: __param_target
        - source_labels: [__param_target]
          target_label: instance
        - target_label: __address__
          replacement: mysqld-exporter:9104

[Bpazy]

修改连接数

1、查看最大连接数：
输入SQL语句show variables like '%max_connections%';

2、修改最大连接数：
方法一：修改配置文件。（推荐方法一）

进入制MySQL安装目录 打开MySQL配置文件 my.ini 或 my.cnf查找 max_connections=100 修改为 max_connections=1000 服务里重起MySQL即可。

在 ubuntu 中文件路径: /etc/mysql/mysql.conf.d/mysqld.cnf

方法二：命令行修改。（不推荐方法二）

命令行登录MySQL后。

设置新的MySQL最大连接数为200：MySQL> set global max_connections=200