my.cnf 常用配置

[mysqld]
lower_case_table_names = 1
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

symbolic-links= 0

log-error= /var/log/mysqld.log
pid-file= /var/run/mysqld/mysqld.pid

user = mysql
port = 3306

server-id = 1



# 忘记root 密码，可以使用
#skip-grant-tables

# 最大连接数
max_connections = 512


#默认使用InnoDB存储引擎
default_storage_engine = InnoDB
# 推荐为物理内存 80%
innodb_buffer_pool_size = 13000M

# 设置 log 文件大小 1G 常规
innodb_log_file_size = 1024M

# ON 为开启慢查询日志，off表示关闭慢查询日志，用于优化SQL语句
slow_query_log = ON
#指定慢查询日志文件
slow_query_log_file = /var/log/3306/itpuxdb-query.err
# 指定多少秒返回查询的结果为慢查询
long_query_time = 5
# sql 默认，解决 group by 问题
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

解决低版本 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 启动和重启

mysq ：启动服务：systemctl start mysqld.service

重启 systemctl restart mysqld.service

MySQL 8.0 Public Key Retrieval is not allowed

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

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

创建用户并赋予权限 对应的表 权限，只能一张一张的表来 , 不支持通配符  
IDENTIFIED WITH mysql_native_password BY 可以让低版本的Navicat可以连接mysql  


create user 'sso' IDENTIFIED WITH mysql_native_password BY  'ssss';

GRANT ALL PRIVILEGES ON  auth_controller.sso_framework  TO 'sso'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON  auth_controller.sso_user_role TO 'sso'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON  auth_controller.sso_auth_info TO 'sso'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON  auth_controller.sso_user_framework TO 'sso'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON  auth_controller.sso_application_user TO 'sso'@'%' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON  auth_controller.sso_application_role TO 'sso'@'%' WITH GRANT OPTION;

flush privileges;

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

create user 'reader'  IDENTIFIED WITH mysql_native_password BY 'rea@Drrr';

GRANT SELECT ON  *.*  TO 'reader'@'%' WITH GRANT OPTION;

flush privileges;

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

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

全量备份

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

#!/bin/bash
# 全量备份
[ ! -e /backup ]&& mkdir /backup
day=`date +%F`
user=root
pass=YONGhong@258
#innobackupex --user=$user --password=$pass  /backup/allbak${day} --no-timestamp -S /tmp/mysql.sock #适用于2.4版本
xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=$user --password=$pass --backup --target-dir=/backup/allbak${day} #适用于8.0版本
mysql -u$user -p$pass -e "flush logs"

# 删除前30天的备份
find /backup/ -mtime +30 -name "allbak*" -exec rm -rf {} \;