my.cnf 常用配置

1[mysqld]
2lower_case_table_names = 1
3datadir = /var/lib/mysql
4socket = /var/lib/mysql/mysql.sock
5
6symbolic-links= 0
7
8log-error= /var/log/mysqld.log
9pid-file= /var/run/mysqld/mysqld.pid
10
11user = mysql
12port = 3306
13
14server-id = 1
15
16
17
18# 忘记root 密码，可以使用
19#skip-grant-tables
20
21# 最大连接数
22max_connections = 512
23
24
25#默认使用InnoDB存储引擎
26default_storage_engine = InnoDB
27# 推荐为物理内存 80%
28innodb_buffer_pool_size = 13000M
29
30# 设置 log 文件大小 1G 常规
31innodb_log_file_size = 1024M
32
33# ON 为开启慢查询日志，off表示关闭慢查询日志，用于优化SQL语句
34slow_query_log = ON
35#指定慢查询日志文件
36slow_query_log_file = /var/log/3306/itpuxdb-query.err
37# 指定多少秒返回查询的结果为慢查询
38long_query_time = 5
39# sql 默认，解决 group by 问题
40sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
41

解决低版本 Navicat 访问mysql8 密码问题

参考 https://blog.csdn.net/yuepeng_csdn/article/details/102503908
如果Navicat 用不了, 那就修改加密规则
alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; flush privileges;

mysql 启动和重启

1mysq ：启动服务：systemctl start mysqld.service
2
3重启 systemctl restart mysqld.service

MySQL 8.0 Public Key Retrieval is not allowed

连接加上 allowPublicKeyRetrieval=true 参考 https://blog.csdn.net/u013360850/article/details/80373604

创建用户，并只赋予表的权限

1创建用户并赋予权限 对应的表 权限，只能一张一张的表来 , 不支持通配符  
2IDENTIFIED WITH mysql_native_password BY 可以让低版本的Navicat可以连接mysql  
3
4
5create user 'sso' IDENTIFIED WITH mysql_native_password BY  'ssss';
6
7GRANT ALL PRIVILEGES ON  auth_controller.sso_framework  TO 'sso'@'%' WITH GRANT OPTION;
8
9GRANT ALL PRIVILEGES ON  auth_controller.sso_user_role TO 'sso'@'%' WITH GRANT OPTION;
10
11GRANT ALL PRIVILEGES ON  auth_controller.sso_auth_info TO 'sso'@'%' WITH GRANT OPTION;
12
13GRANT ALL PRIVILEGES ON  auth_controller.sso_user_framework TO 'sso'@'%' WITH GRANT OPTION;
14
15GRANT ALL PRIVILEGES ON  auth_controller.sso_application_user TO 'sso'@'%' WITH GRANT OPTION;
16
17GRANT ALL PRIVILEGES ON  auth_controller.sso_application_role TO 'sso'@'%' WITH GRANT OPTION;
18
19flush privileges;
20

创建只读用户，并赋予任何IP可以访问

1create user 'reader'  IDENTIFIED WITH mysql_native_password BY 'rea@Drrr';
2
3GRANT SELECT ON  *.*  TO 'reader'@'%' WITH GRANT OPTION;
4
5flush privileges;

生产环境 配置创建用户原则

不要使用 root 用户， 因为不够安全。就让 root 默认 只可以localhost 访问可以了
因此 每次 要连生产的数据库的，就建一个对应的用户好了,配置合理的权限即可。

全量备份

参考 https://www.cnblogs.com/guoweilf/p/12053173.html
参考 https://blog.csdn.net/csdnhsh/article/details/94228436

1#!/bin/bash
2# 全量备份
3[ ! -e /backup ]&& mkdir /backup
4day=`date +%F`
5user=root
6pass=YONGhong@258
7#innobackupex --user=$user --password=$pass  /backup/allbak${day} --no-timestamp -S /tmp/mysql.sock #适用于2.4版本
8xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=$user --password=$pass --backup --target-dir=/backup/allbak${day} #适用于8.0版本
9mysql -u$user -p$pass -e "flush logs"
10
11# 删除前30天的备份
12find /backup/ -mtime +30 -name "allbak*" -exec rm -rf {} \;