mysql兼容性验证

最新推荐文章于 2024-05-10 12:07:41 发布

雪绒～

最新推荐文章于 2024-05-10 12:07:41 发布

阅读量845

点赞数

分类专栏：软件兼容性测试文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_45067241/article/details/129263491

版权

软件兼容性测试专栏收录该内容

16 篇文章 0 订阅

订阅专栏

MySQL是一个关系型数据库管理系统。

一、安装启动

安装mysql相关软件包
yum install mysql-server

启动mysql服务
systemctl start mysqld
systemctl status mysqld

mysql数据库启动失败问题汇总：

<问题1>、start mysqld显示失败，如下所示：

[root@bogon ~]# systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2023-02-28 15:44:06 CST; 5s ago
  Process: 3705 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)
  Process: 3690 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=1/FAILURE)
  Process: 3653 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
  Process: 3628 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
 Main PID: 3690 (code=exited, status=1/FAILURE)
   Status: "Data Dictionary upgrade from MySQL 5.7 in progress"

2月 28 15:44:06 bogon systemd[1]: Starting MySQL 8.0 database server...
2月 28 15:44:06 bogon systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURE
2月 28 15:44:06 bogon systemd[1]: mysqld.service: Failed with result 'exit-code'.
2月 28 15:44:06 bogon systemd[1]: Failed to start MySQL 8.0 database server.

查看日志/var/log/mysql/mysql.log

2023-02-28T07:13:19.872234Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-02-28T07:13:20.296742Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
2023-02-28T07:13:20.297433Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-02-28T07:13:20.297932Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-02-28T07:13:20.298852Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.21)  Source distribution.
2023-02-28T07:14:00.654090Z 0 [Warning] [MY-011037] [Server] The CYCLE timer is not available. WAIT events in the performance_schema will not be timed.
2023-02-28T07:14:00.753692Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.21) starting as process 133099
2023-02-28T07:14:00.778363Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-02-28T07:14:00.778536Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-02-28T07:14:00.880888Z 1 [ERROR] [MY-012530] [InnoDB] Unknown redo log format (103). Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/ upgrading-downgrading.html.

解决方法：
1、删除mysql数据库文件夹
rm -rf /var/lib/mysql
2、mysql数据库初始化
mysqld --initialize --console
3、修改/var/run/mysqld/权限
chown -R mysql:mysql /var/lib/mysql/
4、启动mysql服务
systemctl start mysqld
5、查看mysql数据库状态
systemctl status mysqld

<问题2>、Access denied for user ‘root’@‘localhost’ (using password: NO)错误

[root@bogon ～]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

【问题分析】：
出现Access denied的原因可能如下：
1、mysql的服务停止
2、用户的端口号或者ip导致
3、mysql的配置文件错误----/etc/my.cnf文件
4、root用户密码错误
【解决方法】：
1、修改/etc/my.cnf文件
在[mysqld]下添加一行，使其登录时跳过权限检查
skip-grant-tables
2、重启mysql服务器
systemctl stop mysqld
systemctl start mysqld
3、登录
[root@bogon bin]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.21 Source distribution

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>

二、测试

初始化数据库
mysql_secure_installation -D
会有如下一些提示，为root用户设置初始密码，譬如： PassW0rd!

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Please set the password for root here.

#设置 root 用户的初始密码，譬如 PassW0rd!
New password:

验证数据库最基本的增删改查功能。

从命令行登录到数据库

mysql -pPassW0rd!
展示数据库

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.21 Source distribution

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>
创建测试db

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql>
创建表

mysql> create table hola(bonj text);
Query OK, 0 rows affected (0.01 sec)

mysql>
插入数据

mysql> insert into test.hola(bonj) values(“Mundo”);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test.hola(bonj) values(“Lundo”);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test.hola(bonj) values(“测试”);
Query OK, 1 row affected (0.00 sec)
更新数据

mysql> update test.hola set bonj=‘Xundo’ where bonj=‘Mundo’;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>
查询数据