配置Mysql datadir目录为virtio-9p文件系统（三）

接上文

自己在配置过程中遇到的问题总结在这里，日后自己翻看

问题总结：

问题1：Unit mysql.service is masked.

解决办法：systemctl unmask mysql.service

问题2：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

解决办法：在执行初始化过程时产生随机的密码是sr.KsrRYo3ro，先修改密码

 mysqladmin -u root -p password 123456 修改密码为123456

 

在红色箭头处填写原始密码sr.KsrRYo3ro

然后在登录Mysql如命令如下

查看数据库datadir存储路径

SHOW VARIABLES LIKE 'datadir';

 

 

问题3：ERROR 2003 (HY000):

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.122.24:3306' (113

解决办法：

需要关闭防火墙

Systemctl stop Firewalld && iptables -F

root@amd-srv1:/data/wy/myubuntu# mysql --host=192.168.122.159 --port=3306 -uroot -p123456

问题4：ERROR 1130 (HY000):

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1130 (HY000): Host '192.168.122.1' is not allowed to connect to this MySQL server

解决办法：

登录mysql-server执行以下命令

[root@localhost ~]#  mysql -uroot -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 8.0.26 Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update user set host = '%' where user = 'root';

ERROR 1046 (3D000): No database selected

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set host = '%' where user = 'root';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

参考链接：https://blog.csdn.net/qq_42257666/article/details/123746508

问题5、max_prepared_stmt_count不够大

FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"

FATAL: `thread_init' function failed: /usr/share/sysbench/oltp_common.lua:284: SQL API error

FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"

解决办法：

登录mysql容器，改变max_prepared_stmt_count值

 

问题6：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

解决办法：

找到mysql真正使用的mysqld.sock

$ln -s /data/mysql/mysql.sock /var/run/mysqld/mysqld.sock

问题7：ERROR 1698 (28000): Access denied for user 'root'@'localhost'

# mysql -h127.0.0.1 -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

解决办法：

出现ERROR 1698 (28000): Access denied for user ‘root‘@‘localhost‘ 的解决方法_码农研究僧的博客-CSDN博客

选择数据库，use mysql;

并且查询数据库的用户表格
其root默认的方式位auth_socket

 

多次执行mysql> flush privileges;

退出

问题8: Error:9 (Bad file descriptor)

 

暂时没找到原因

问题9:ERROR 1130 (HY000):

ERROR 1130 (HY000): Host '192.168.122.1' is not allowed to connect to this MySQL server

使用mysql远程登录命令mysql –uroot –h192.168.122.87 –p时，报错MySQL ERROR 1130 (HY000): Host 'XXXX' is not allowed to connect to this MySQL server

问题原因：mysql服务器出于安全考虑，默认只允许本地登录数据库服务器。

解决办法：登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%"，然后重启mysql这样就允许所有的远程机器进行访问了。

步骤1：修改mysql表

mysql -u root -p123456

mysql>use mysql;

mysql>update user set host = '%' where user = 'root';

mysql>flush privileges;

mysql>select host, user from user;

mysql>quit

步骤2：重启mysql

问题10：ERROR 1064 (42000): 

解决办法：修改mysql的密码时遇到问题ERROR 1064 (42000): You have an error in your SQL syntax； check the manual that corre_吹头发丶的博客-CSDN博客_mysql修改密码报错1064

参考链接：

dpkg说明_linbounconstraint的博客-CSDN博客_dpkg-source

Mysql-8.0.29-源码编译安装_阳光九叶草LXGZXJ的博客-CSDN博客

Ubuntu20.04下编译安装Mysql8.0 - 简书

mysql-server/Docs at 8.0 · mysql/mysql-server · GitHub

MySQL :: MySQL 8.0 Reference Manual :: 2.9.4 Installing MySQL Using a Standard Source Distribution