P&G小站

# Day03 MySQL 权限启动日志

# 本节关键词

  • GRANT 权限 ON 权限级别 TO 用户;
  • 启动、连接、关闭
  • 版本多实例
  • 多种日志配置

# 用户权限角色

# 权限用户的属性
GRANT 权限 ON 权限级别 TO 用户;

# 权限级别
*.*               : 全库级别  -> 管理员         存储位置：mysql.user
test.*            : 单库级别  -> 业务层面       存储位置：mysql.db
test.t1           : 单表级别                   存储位置：mysql.tables_priv
select(id, name)  : 列级别 如敏感字段           存储位置：mysql.columns_priv

# 角色
权限的集合

# 可以授权的权限功能
show privileges;

# 管理员 test@localhost all 权限
create user test@localhost identified with mysql_native_password by '123';
grant all on *.* to test@localhost with grant option;
show grants for test@localhost;
select * from mysql.user where user='test'\G;

# 开发 dev_user@localhost
create user dev_user@localhost identified with mysql_native_password by '123';
grant create, update, delete, insert on dev_db.* to dev_user@localhost;
show grants for dev_user@localhost;
select * from mysql.db where user='dev_user'\G;

# 主从复制 repl_user@'10.0.0.%'
create user repl_user@'10.0.0.%' identified with mysql_native_password by '123';
grant replication slave, replication client on *.* to repl_user@'10.0.0.%';
show grants for repl_user@'10.0.0.%';

# 两种查看权限
show grants for 用户名@'白名单'
select * from mysql.user | mysql.db

# 回收权限
revoke delete on dev_db.* from dev_user@localhost;

# 角色 8.0新特性 不能登录
create role dev_role@localhost;
grant create, update, delete, insert on dev_db.* to dev_role@localhost;

create user dev_user1@localhost identified with mysql_native_password by '123';
create user dev_user2@localhost identified with mysql_native_password by '123';
grant dev_role@localhost to dev_user1@localhost, dev_user2@localhost;

select * from mysql.role_edges;

# 连接mysql

# socket
mysql -uroot -p [-S /tmp/mysql.sock]

# TCP/IP
mysql -h localhost -P3306 -uroot -p

show processlist;

# 支持 ssl 方式连接
mysql_ssl_rsa_setup
ls -al /data/3306/data

mysql -udev_user -p123 -h10.0.0.51 --ssl-cert=/data/3306/data/client-cert.pem --ssl-key=/data/3306/data/client-key.pem

# 配置文件

# 配置读取顺序
[root@ni-ning data]# mysqld --help --verbose | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
  • 标签 另外可以命令启动时手动加入
server端：
    [server]
    [mysqld]
    [mysql_safe]
client端(不影响远程)
    [client]
    [mysql]
    [mysqldump]
  • 模板
vim /etc/my.cnf

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
server_id=51
port=3306

[mysql]
socket=/tmp/mysql.sock
  • 手动指定默认文件
mysqld_safe --defaults-file=/opt/mysql.cnf &

# 启动流程

命令：  systemctl start mysqld
命令：  /etc/init.d/mysqld start
脚本：  /usr/local/mysql/support-files/mysql.server       内部调用 mysqld_safe
脚本：  /usr/local/mysql/bin/mysqld_safe                  内部调用 mysqld
主程序：/usr/local/mysql/bin/mysqld                       可手动启动：mysqld &

mysqld_safe
    - 记录日志文件
    - 监控mysqld状态，如果进程出现问题，会尝试拉起mysqld
mysqld
    - 调试时可以用
    - 关闭得借助外部方法

mysqld_safe、mysqld
    - 可以启动时，手工加参数

systemctl、/etc/init.d/mysqld
    - start
    - restart
    - stop
    - status

# 关闭方式

systemctl stop mysqld
/etc/init.d/mysqld stop
mysql -uroot -S /tmp/mysql.sock shutdown

# 版本多实例

  • 常见需求规划
# 3台机器 9 个实例
机器1： 主  从  从
机器2： 从  主  从
机器3： 从  从  主

# 1台机器 不同版本
  • 同版本多实例
# 配置 /data/3307/data /data/3308/data 可挂载不同磁盘
INS1 /etc/mysql.cnf

INS2 /etc/mysql3307.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
server_id=52
port=3307


INS3 /etc/mysql3308.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
server_id=53
port=3308

# 初始化
mysqld --defaults-file=/etc/mysql3307.cnf --initialize-insecure
mysqld --defaults-file=/etc/mysql3308.cnf --initialize-insecure

# 启动
mysqld_safe --defaults-file=/etc/mysql3307.cnf &
mysqld_safe --defaults-file=/etc/mysql3308.cnf &
  • 不同版本多实例
# 配置
basedir=/usr/local/mysql
basedir=/usr/local/mysql56
basedir=/usr/local/mysql57

# 5.7初始化
/usr/local/mysql57/bin/mysql --defaults-file=/etc/my3357.cnf --initailize-insecure
# 5.6初始化
/usr/local/mysql56/scripts/mysql_install_db --defaults-file=/etc/my3357.cnf

# 5.7启动
/usr/local/mysql57/bin/mysql_safe --defaults-file=/etc/my3357.cnf &
# 5.6启动
/usr/local/mysql56/bin/mysql_safe --defaults-file=/etc/my3357.cnf &

# 日志相关

  • 日志分类
log_error       : 错误日志
log_bin         : 二进制日志
slow_query_log  : 慢日志
general_log     : 普通日志
  • log_error
默认打开
    位置 datadir=/data/3306/data/HOSTNAME.err
控制参数
    vim /etc/my.cnf
    log_error=/data/3306/log/mysql-err.log
    日志文件和数据文件分开存储，记得权限 chown -R mysql.mysql /data
错误日志
    - 启动故障
    - 主从故障
    - 死锁
    - 数据库卡主，堆栈信息
    - mysqld & 可以调试信息打印到屏幕
查看日志参数
    show variables like '%log_error%';
日志参数级别
    log_error_verbosity 1 错误信息；2 错误信息和告警信息； 3：错误信息、告警信息和通知信息
    set global log_error_verbosity=3;
  • log_bin
二进制日志
    - 记录MySQL发生过的修改的操作日志，除了show、select，修改过都会记录binlog
    - 数据修复、主从、SQL问题排查、审计(工具my2sql)
配置binlog
    - 8.0默认开启，5.7需手动开启
    - 默认 datadir=/data/3306/data/binlog.000001
    - 建议日志文件和数据文件分开存储
    - 配置
        server_id=51
        log_bin=/data/3306/log/binlog
  • slow_query_log
慢日志
    默认没有打开
    记录数据库运行期间，执行较慢的SQL
配置
    slow_query_log=1
    long_query_time=0.5
    slow_query_log_file=/data/3306/slow_log

    log_queries_not_using_indexes=1  -- 没用索引的语句也会记录下来
    log_throttle_queries_not_using_indexes=1000  -- 最近1000条
  • general_log
普通日志
    文本格式记录MySQL运行期间，所有的操作日志，可以做问题诊断和调试
配置
    general_log=1
    general_log_file=/data/3306/log/general_log

# 升级

  • 不管哪种方式升级，都应该先做了冷备
  • 写+读架构 -> 新节点
# REPLACE
1. 安装新版本软件
2. 关闭原数据库业务(挂维护业) 避免脏数据
    set global innodb_fast_shutdown=0;
    show variables like '%innodb_fast_shutdown%';
    做冷备
3. 使用新版本软件 "挂" 旧版本数据 --skip-grant-tables  --skip-networking

4. 升级：只是升级系统表，升级时间和数据量继无关

5. 正常启动
6. 验证即可


# 相关操作
## 关闭连接会话
show processlist;
select concat('kill ', id, ";") from information_schema.processlist;
## 刷新脏数据
set global innodb_fast_shutdown=0;
## 关闭数据库
shutdown;
上次更新: 1/30/2023, 12:27:29 PM

← Day04 SQL 定义库与表 →