ubuntu 安装mysql8.0 并从5.7备份还原

https://blog.csdn.net/linzuo7096/article/details/151756514

修改配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf

//开放监听
vim /etc/mysql/mysql.conf.d/mysqld.cnf
将bind-address = 127.0.0.1注销​ 或改为0.0.0.0
查看SHOW VARIABLES LIKE 'bind_address';
注意/etc/mysql/mysql.conf.d/mysqld_bak.cnf类似的配置文件对监听有影响

//添加新账户
create user 'wflow'@'%' identified by 'wflowxxx';
grant all on wflow_prd.* to wflow@'%';
GRANT PROCESS ON *.* TO wflow@'%';

DBeaver JDBC URL 中添加参数 allowPublicKeyRetrieval=true

还原数据库时 添加字符集参数 –default-character-set=utf8

备份脚本backup.sh

#!/bin/bash
# MySQL备份脚本
USER="wflow"
PASS="wflowxxxx"
DBNAME="wflow_prd"
BACKUP_DIR="/mesdata/wflow_bak/backup"
DATE=$(date +%Y%m%d_%H%M%S)
FILE_NAME="$DBNAME-$DATE.sql"
FULL_PATH="$BACKUP_DIR/$FILE_NAME"

# 创建备份目录（如果不存在）
mkdir -p $BACKUP_DIR

# 执行备份命令
mysqldump -u$USER -p$PASS $DBNAME > $FULL_PATH

# 可选：压缩备份文件以节省空间
gzip $FULL_PATH

定时任务crontab -e

0 3 * * * /mesdata/wflow_bak/backup.sh
0 4 * * * find /mesdata/wflow_bak/backup -type f -name "*.sql" -mtime +10 -exec rm {} \;

重新初始化，初始化root密码可以在/var/log/mysql中看到，或者加–console

sudo mysqld --initialize --datadir=/mesdata/mysql --console
注意datadir目录需要将权限给mysql用户
chown mysql:mysql /mesdata/mysql

mysql大小写敏感问题

查看SHOW VARIABLES LIKE 'lower_case_table_names';
配置vim /etc/mysql/mysql.conf.d/mysqld.cnf 增加lower_case_table_names=1