CentOS7安装了MySQL8社区版后,Navicat连接正常,跑程序,起来后报错:找不到QRTZ_LOCK表,库里查,确实有这表,只不过是小写,大致定位到应该是数据库大小写配置问题。虽定位准确,但还是耗了很久的功夫。
1. 首先检查防火墙
检查防火墙是否开启,以及3306端口配置是否准确
2. MySQL8 忽略大小写配置lower_case_table_names = 1 (CentOS7)的具体操作
我的做法是:
(1)winscp修改mysql.conf,[mysqld]添加lower_case_table_names=1,保存
[mysqld]
lower_case_table_names=1
(2)删除/usr/local/mysql/data(建议先备份,因我没有历史数据,直接删除没有历史负担)因我是已经装完MySQL8以后在使用时提示找不到表才发现需要修改这个“大小写忽略配置”的,所以在处理时已经有 这个目录了。
(3)启动MySQL
systemctl start mysqld
systemctl stop mysqld
systemctl status mysqld.service#可以查看密码
(4)初始化数据库
mysqld --initialize
(5)修改权限,提升权限,外网访问
systemctl status mysqld.service#查看密码
用查看得到的密码登录后
登录数据库
mysql -u root -p
输入密码
mysql> use mysql;
查询host
mysql> select user,host from user;
4.创建host
如果没有"%"这个host值,就执行下面这两句:
mysql> update user set host='%' where user='root';
mysql> flush privileges;
5.授权用户
任意主机以用户root和密码mypwd连接到mysql服务器
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypwd' WITH GRANT OPTION;
mysql> flush privileges;
%代表任何IP,均可以使用root用户的密码来访问到该服务器上的MySQL数据库。比如Navicat里用root的账号密码访问
%修改为具体的则限定某个具体IP
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
mysql> flush privileges;
3.具体参考:
(2)https://bugs.mysql.com/bug.php?id=90695
In order to make this work in MySQL 8.0 and linux follow the steps bellow
0) Backup mysql schema before executing the following steps using
备份原有Schema。
mysqldump -h localhost -u root -p mysql > /home/username/dumps/mysqldump.sql
and then stop mysql using
sudo service mysql stop
- move or remove /var/lib/mysql directory. This will delete all databases!!!
因为是首次安装,没有历史负担,我直接给删掉了。建议先复制备份到其他位置
mv /var/lib/mysql /tmp/mysql
2)Create a new /var/lib/mysql directory and make mysql user as owner
mkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql
3)edit /etc/mysql/mysql.conf.d/mysqld.cnf and add the following line after [mysqld]
lower_case_table_names=1
4) Initialize mysql using the following
sudo systemctl start mysqld
- (Optional) Repeat
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql