CentOS_mysql&mariadb

知本知至

已于 2023-01-28 12:15:52 修改

阅读量399

点赞数

分类专栏： Linux 文章标签： mysql centos 数据库

于 2021-09-21 19:44:59 首次发布

本文链接：https://blog.csdn.net/qq_43652666/article/details/120404404

版权

Linux 专栏收录该内容

58 篇文章 1 订阅

订阅专栏

mysql10.3.28

MariaDB [(none)]> select version();
+-----------------+
| version()       |
+-----------------+
| 10.3.28-MariaDB |
+-----------------+
1 row in set (0.000 sec)

调用存储过程创建表并插入数据

(root@localhost) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| testlog           |
| toc               |
| user              |
+-------------------+
9 rows in set (0.01 sec)

(root@localhost) [hellodb]> select * from testlog;
Empty set (0.00 sec)

(root@localhost) [hellodb]> call sp_testlog;
Query OK, 1 row affected (52.53 sec)

(root@localhost) [hellodb]> truncate table testlog;
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [hellodb]> select * from testlog;
Empty set (0.00 sec)

(root@localhost) [hellodb]> begin;
Query OK, 0 rows affected (0.00 sec)
--使用事务插入数据
(root@localhost) [hellodb]> call sp_testlog;commit;select count(*) from testlog;
Query OK, 1 row affected (2.51 sec)

Query OK, 0 rows affected (0.10 sec)

+----------+
| count(*) |
+----------+
|   100000 |
+----------+
1 row in set (0.02 sec)

mysql8.0

修改创建的数据库的字符集

(root@localhost) [(none)]> create database db2;
Query OK, 1 row affected (0.00 sec)
(root@localhost) [(none)]> show create database db2;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                               |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(root@localhost) [(none)]> alter database db2 character set utf8 COLLATE utf8_bin;
Query OK, 1 row affected, 2 warnings (0.01 sec)

(root@localhost) [(none)]> show create database db2;
+----------+------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                  |
+----------+------------------------------------------------------------------------------------------------------------------+
| db2      | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

合并重复的年龄

(root@localhost) [hellodb]> select distinct age from students;
+-----+
| age |
+-----+
|  22 |
|  53 |
|  32 |
|  26 |
|  46 |
|  19 |
|  17 |
|  20 |
|  23 |
|  33 |
|  21 |
|  25 |
|  18 |
|  27 |
| 100 |
+-----+
15 rows in set (0.00 sec)

查询不为空的行

(root@localhost) [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

(root@localhost) [hellodb]> select * from students where classid is not null;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
+-------+---------------+-----+--------+---------+-----------+
23 rows in set (0.00 sec)

主从复制

查看主节点上创建的账号是否同步到slave节点

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| test             | %         |
| backm            | 10.0.0.%  |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql5.5.68

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21    |
+-----------+
1 row in set (0.00 sec)

安全初始化

[root@localhost ~]#mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

修改mysql默认字符集

查看默认使用的字符集

[root@localhost ~]#mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

修改服务端和客户端使用的字符集为utf8mb4
配置文件更改

[root@localhost ~]#sed -i '/^\[mysqld\]/a\character-set-server=utf8mb4' /etc/my.cnf.d/server.cnf
[root@localhost ~]#sed -i '/^\[client\]/a\default-character-set=utf8mb4' /etc/my.cnf.d/client.cnf
[root@localhost ~]#systemctl restart mariadb

登录数据库
验证默认字符类型是否被修改

[root@localhost ~]#mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

开启事件调度器event_scheduler

查看是否开启

MariaDB [hellodb]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+
1 row in set (0.00 sec)
/* 开启事件调度器 */
MariaDB [hellodb]> set global event_scheduler=ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+
1 row in set (0.00 sec)

mysql5.7

download&install

配置yum源

[root@centos7 ~]#cat  /etc/yum.repos.d/mysql-community.repo
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-connectors-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-tools-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.6-community]
name=MySQL 5.6 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.6-community-el7-$basearch/
enabled=0
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-5.7-community]
name=MySQL 5.7 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-$basearch/
enabled=1
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[mysql-8.0-community]
name=MySQL 8.0 Community Server
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-$basearch/
enabled=0
gpgcheck=1
gpgkey=https://repo.mysql.com/RPM-GPG-KEY-mysql

[root@centos7 ~]#yum -y install mysql-community-server

5.7版本安装好之后需要临时密码

如果遇到yum安装mysql提示密钥错误，重新导入密钥

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
[root@tomcat yum.repos.d]#yum install ca-certificates -y

启动报如下错误

2022-06-28T11:07:14.621450Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-28T11:07:14.622297Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.38) starting as process 4103 ...
2022-06-28T11:07:14.624198Z 0 [Note] InnoDB: PUNCH HOLE support available
2022-06-28T11:07:14.624227Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-06-28T11:07:14.624231Z 0 [Note] InnoDB: Uses event mutexes
2022-06-28T11:07:14.624233Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2022-06-28T11:07:14.624238Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-06-28T11:07:14.624240Z 0 [Note] InnoDB: Using Linux native AIO
2022-06-28T11:07:14.624383Z 0 [Note] InnoDB: Number of pools: 1
2022-06-28T11:07:14.624469Z 0 [Note] InnoDB: Using CPU crc32 instructions
2022-06-28T11:07:14.625268Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-06-28T11:07:14.629806Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-06-28T11:07:14.631080Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-06-28T11:07:14.642598Z 0 [ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4800!
2022-06-28 19:07:14 0x7f7c7d06d780  InnoDB: Assertion failure in thread 140172650272640 in file ut0ut.cc line 921
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
11:07:14 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0
connection_count=0
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68197 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/sbin/mysqld(_Z6fil_ioRK9IORequestbRK9page_id_tRK11page_size_tmmPvS8_+0x208)[0x13b7538]
/usr/sbin/mysqld(_Z13buf_read_pageRK9page_id_tRK11page_size_t+0xe4)[0x13658a4]
/usr/sbin/mysqld(_Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb+0x42d)[0x1334c9d]
/usr/sbin/mysqld(_Z31trx_rseg_get_n_undo_tablespacesPm+0x15d)[0x12d2c5d]
information that should help you find out what is causing the crash.

同样也是因为之前安装的有其他版本的mysql未清理干净，执行rm -rf /var/lib/mysql/*清理之后即可

日志中找不到临时密码

如果CentOS7安装完mysqld日志中没有临时密码
原因：可能是因为之前安装过MySQL未清理干净

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# rm -rf /var/lib/mysql
[root@localhost ~]# systemctl start mysqld

可以看到日志中已经显示了temporay password

[root@localhost ~]# tail -f /var/log/mysqld.log

2021-09-21T11:36:33.966768Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2021-09-21T11:36:58.617188Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-21T11:36:58.767944Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-09-21T11:36:58.794371Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-09-21T11:36:58.850035Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3b169d0d-1ad0-11ec-8ccf-000c29804c29.
2021-09-21T11:36:58.850762Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-09-21T11:36:59.297606Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-09-21T11:36:59.297627Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-09-21T11:36:59.298102Z 0 [Warning] CA certificate ca.pem is self signed.

2021-09-21T11:36:59.454565Z 1 [Note] A temporary password is generated for root@localhost: d*)aH/Usj3ty

登录成功
执行status命令报错提示：初次登录必须先修改密码

[root@localhost ~]# mysql -uroot -p'd*)aH/Usj3ty'
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 2
Server version: 5.7.35

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> status
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

修改mysql登录密码

mysql> alter user root@'localhost' identified by 'Kktb@126';
Query OK, 0 rows affected (0.01 sec)

报错

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysqld
2021-09-21T11:06:53.923342Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-09-21T11:06:53.924642Z 0 [Note] mysqld (mysqld 5.7.35) starting as process 2344 ...
2021-09-21T11:06:53.925948Z 0 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2021-09-21T11:06:53.925965Z 0 [ERROR] Aborting

2021-09-21T11:06:53.925978Z 0 [Note] Binlog end
2021-09-21T11:06:53.926011Z 0 [Note] mysqld: Shutdown complete

mysql5.5

MariaDB [(none)]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.68-MariaDB |
+----------------+
1 row in set (0.00 sec)

编译安装mysql5.6.51

安装软件包libevent-devel时报错

[root@centos7 mysql-5.6.51]#yum install libevent-devel
Transaction check error:
  file /usr/include/event2/event-config.h conflicts between attempted installs of libevent-devel-2.0.21-4.el7.i686 and libevent-devel-2.0.21-4.el7.x86_64

Error Summary
-------------

提示有冲突，忽略

cmake完成提示

-- Generating done
CMake Warning:
  Manually-specified variables were not used by the project:

    MYSQL_USER
    WITHOUT_MROONGA_STORAGE_ENGINE
    WITH_READLINE


-- Build files have been written to: /usr/local/src/mysql-5.6.51

找不到.sock文件

[root@centos7 ~]#mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

编译参数指定了.sock文件所在目录，但是和系统默认寻找的不在同一个目录所以就找不到

[root@centos7 ~]#mysql -S /data/mysql/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.51 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>

方法二：
在配置文件/etc/my.cnf中指定sock的文件路径

[root@centos7 ~]#vim /etc/my.cnf
socket = /data/mysql/mysql.sock

重启服务

[root@centos7 ~]#service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!

[root@centos7 ~]#mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.51 Source distribution

Copyright (c) 2000, 2021, 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.

问题记录

mysql8.0更改root用户密码为简单密码

mysql8.0更改简单密码提示Your password does not satisfy the current policy requirements
修改的密码不符合当前策略要求

mysql> set password for root@'localhost'='123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

查看当前密码策略

mysql>  SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

修改MySQL密码策略等级

mysql> SET GLOBAL validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL validate_password.length = 6;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL validate_password.number_count = 0;
Query OK, 0 rows affected (0.00 sec)

重新设置密码

mysql> set password for root@'localhost'='123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@tomcat yum.repos.d]#mysql -uroot -p'123456'
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 11
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, 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>

mysql8.0中validate_password组件用于测试密码并提高安全性。

在分配作为明文值提供的密码的SQL语句中，组件根据当前密码策略检查密码，若密码弱即不符合设定策略则返回错误。

validate_password安装后会执行三个级别口令检查：LOW,MEDIUM,STRONG,默认是MEDIUM

LOW策略仅测试密码长度，密码长度至少为8位
MEDIUM策略至少包含1个数字字符，1个小写字符，1个大写字符和1个特殊字符
STRONG添加条件，长度为4或更长的密码字符串不得与字典文件中的单词匹配(如果已指定)。要指定字典文件

更改MEDIUM策略

mysql> SET GLOBAL validate_password.number_count = 0;
mysql> SET GLOBAL validate_password.mixed_case_count = 0;
mysql> SET GLOBAL validate_password.special_char_count = 0;

参考：MySQL8.0官方手册

mysql5.7 修改密码策略

和mysql8.0整体一样，变量名不同

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_number_count=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set password for root@'localhost'='123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@localhost yum.repos.d]#mysql -uroot -p'123456'
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 5
Server version: 5.7.40 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mariadb非正常关闭

更改完my.cnf之后未重启MySQL服务，直接重启了机器，重启过程中，机器一直黑屏，直接手动点了关闭电源。

开机之后，重新连接MySQL时候发现socket文件一直存在

[root@localhost ~]#/data/mysql/3306/bin/mysqld start
MySQL is running...
[root@localhost ~]#mysql -uroot -p -S /data/mysql/3306/socket/mysql.sock
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysql/3306/socket/mysql.sock' (111)

端口未运行

[root@localhost ~]#ss -ntl 
State      Recv-Q Send-Q                                            Local Address:Port                                                           Peer Address:Port              
LISTEN     0      128                                                           *:22                                                                        *:*                  
LISTEN     0      100                                                   127.0.0.1:25                                                                        *:*                  
LISTEN     0      128                                                        [::]:22                                                                     [::]:*                  
LISTEN     0      100                                                       [::1]:25                                                                     [::]:*

日志记录

[root@localhost ~]#tail -f /data/mysql/3306/log/mysql.log 

Event queue status:
Element count   : 0
Data locked     : NO
Attempting lock : NO
LLA             : init_queue:141
LUA             : init_queue:151
WOC             : NO
Next activation : never
2022-12-10 17:19:33 0 [Note] /usr/sbin/mariadbd (initiated by: unknown): Normal shutdown

日志显示正常关机

使用命令停止数据库报错

[root@localhost ~]#/data/mysql/3306/bin/mysqld stop
Stoping MySQL...
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/data/mysql/3306/socket/mysql.sock' (111)'
Check that mysqld is running and that the socket: '/data/mysql/3306/socket/mysql.sock' exists!

进程中也没有MySQL

[root@localhost ~]#ps -aux|grep mysql 
root       1531  0.0  0.0 112808   964 pts/0    S+   17:25   0:00 grep --color=auto mysql

解决

[root@localhost ~]#rm -rf /data/mysql/3306/socket/mysql.sock 
[root@localhost ~]#/data/mysql/3306/bin/mysqld start
Starting MySQL....
[root@localhost ~]#ss -ntl 
State      Recv-Q Send-Q                                            Local Address:Port                                                           Peer Address:Port              
LISTEN     0      128                                                           *:22                                                                        *:*                  
LISTEN     0      100                                                   127.0.0.1:25                                                                        *:*                  
LISTEN     0      128                                                        [::]:22                                                                     [::]:*                  
LISTEN     0      100                                                       [::1]:25                                                                     [::]:*                  
LISTEN     0      80                                                         [::]:3306                                                                   [::]:*      

[root@localhost ~]#mysql -uroot -p -S /data/mysql/3306/socket/mysql.sock
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| recruitment        |

mariadb回收权限

授权之后发现授权过多，

MariaDB [(none)]> show grants for 'recruitment'@'%';
+---------------------------------------------------------------------------------------------------------------------------------------+
| Grants for recruitment@%                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `recruitment`@`%` IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

# 取消授权
MariaDB [(none)]> revoke all privileges ON *.* from 'recruitment'@'%';
Query OK, 0 rows affected (0.002 sec)

# 删除用户
MariaDB [(none)]> select user,host from mysql.user;
+-------------+-----------------------+
| User        | Host                  |
+-------------+-----------------------+
| recruitment | %                     |
| root        | 10.0.%                |
|             | localhost             |
| mariadb.sys | localhost             |
| mysql       | localhost             |
| root        | localhost             |
|             | localhost.localdomain |
+-------------+-----------------------+
7 rows in set (0.001 sec)

MariaDB [(none)]> drop user 'recruitment'@'%';
Query OK, 0 rows affected (0.001 sec)

# 重新创建用户&授权
MariaDB [(none)]> select user,host from mysql.user;
+-------------+-----------------------+
| User        | Host                  |
+-------------+-----------------------+
| root        | 10.0.%                |
|             | localhost             |
| mariadb.sys | localhost             |
| mysql       | localhost             |
| root        | localhost             |
|             | localhost.localdomain |
+-------------+-----------------------+
6 rows in set (0.001 sec)
MariaDB [(none)]> create user 'recruitment'@'%' identified by '123456';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> grant all privileges on recruitment.* to 'recruitment'@'%' identified by '123456' with grant option;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)