mysql兼容性验证

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 serverLoaded: 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 agoProcess: 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

Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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

Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

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> select * from test.hola;
±-------+
| bonj |
±-------+
| Mundo |
| Lundo |
| 测试 |
±-------+
3 rows in set (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> select * from test.hola;
±-------+
| bonj |
±-------+
| Xundo |
| Lundo |
| 测试 |
±-------+
3 rows in set (0.00 sec)

mysql>
查询数据

mysql> select * from test.hola;
±-------+
| bonj |
±-------+
| Xundo |
| Lundo |
| 测试 |
±-------+
3 rows in set (0.00 sec)

mysql>
删除数据

mysql> delete from test.hola where bonj=‘Xundo’;
Query OK, 1 row affected (0.00 sec)

mysql> select * from test.hola;
±-------+
| bonj |
±-------+
| Lundo |
| 测试 |
±-------+
2 rows in set (0.00 sec)

mysql>
退出 sql

SQL> exit