记录一下在Centos7.x下面使用yum方式安装Mysql8(mysql5.7)关系型数据库

安装之前一般需要先确定centos7.x服务器里是否已经安装，未安装或者刚初始化的centos7.x服务器最好安装，原来已经有的要升级的话一定要对系统原有mysql 或mariadb卸载干净，以免影响后续的安装

安装之前查看下有没有mysql,mariadb相关的东西存在

[root@myw ~]# whereis mysqlmysql: /usr/lib64/mysql /usr/share/mysql[root@myw ~]# find / -name 'mysql*'/etc/selinux/targeted/active/modules/100/mysql/etc/my.cnf.d/mysql-clients.cnf/usr/lib/firewalld/services/mysql.xml/usr/share/qemu-kvm/qemu-ga/fsfreeze-hook.d/mysql-flush.sh.sample/usr/share/man/man5/mysql_table.5.gz/usr/share/mysql/usr/lib64/mysql/usr/lib64/mysql/plugin/mysql_clear_passWord.so[root@myw ~]# [root@myw ~]# rpm -qa|grep mariadb[root@myw ~]# 

查看了这些查出来的目录和文件，不影响安装mysql8

mysql8的安装操作

使用yum方式安装mysql的最新版mysql8

首先在mysql的官网找到安装源，使用yum方式(https://dev.mysql.com/downloads/repo/yum/)找到对应的版本信息

个人使用的是常见的QQ浏览器，

方式一，可以先把rpm包下载下来，然后通过ftp等工具上传到centos7.x服务器的/root目录下面

方式二，复制链接地址，使用wget方式直接把rpm包下载到centos7.x里，默认下载目录就是/root里

方式二得到的下载链接如下

https://repo.mysql.com//mysql80-commUnity-release-el7-7.noarch.rpm

既然得到了这个链接，那么可以单独访问http://repo.mysql.com/

我们可以看到

妥妥的一大笔资源在这里，mysql老版本和新版本都在这儿

这里安装mysql8 那么可以使用

wget Https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm

如果运行发现不可用，可能没有wget的软件包 先使用yum安装wget

yum -y install wget

[root@myw ~]# yum -y install wgetLoaded plugins: fastestmirrorDetermining fastest mirrors * base: 100.125.0.40 * extras: 100.125.0.40 * updates: 100.125.0.40base   | 3.6 kB  00:00:00     extras | 2.9 kB  00:00:00     updates| 2.9 kB  00:00:00     (1/2): extras/7/x86_64/primary_db  | 249 kB  00:00:00     ******************************// 有很多信息，不太需要关注******************************Total download size: 547 kInstalled size: 2.0 MDownloading packages:wget-1.14-18.el7_6.1.x86_64.rpm    | 547 kB  00:00:00     Running transaction checkRunning transaction testTransaction test succeededRunning transaction  Installing : wget-1.14-18.el7_6.1.x86_64            1/1   Verifying  : wget-1.14-18.el7_6.1.x86_64            1/1 Installed:  wget.x86_64 0:1.14-18.el7_6.1                           Complete![root@myw ~]# 

[root@myw ~]# wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm--2023-01-06 09:58:40--  https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpmResolving repo.mysql.com (repo.mysql.com)... 184.30.152.230Connecting to repo.mysql.com (repo.mysql.com)|184.30.152.230|:443... connected.HTTP request sent, awaiting response... 200 OKLength: 11196 (11K) [application/x-redhat-package-manager]Saving to: ?.ysql80-community-release-el7-7.noarch.rpm?100%[========================================================================================================================================================================================>] 11,196      --.-K/s   in 0s      2023-01-06 09:58:41 (250 MB/s) - ?.ysql80-community-release-el7-7.noarch.rpm?.saved [11196/11196][root@myw ~]# 

然后执行rpm方式

rpm -ivh mysql80-community-release-el7-7.noarch.rpm

[root@myw ~]# rpm -ivh mysql80-community-release-el7-7.noarch.rpmwarning: mysql80-community-release-el7-7.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEYPreparing...                          ################################# [100%]Updating / installing...   1:mysql80-community-release-el7-7  ################################# [100%][root@myw ~]# 

再执行

yum -y install mysql mysql-server mysql-devel或者yum -y install mysql-server

[root@myw ~]# yum -y install mysql mysql-server mysql-develLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: 100.125.0.40 * extras: 100.125.0.40 * updates: 100.125.0.40mysql-connectors-community         | 2.6 kB  00:00:00     mysql-tools-community              | 2.6 kB  00:00:00     mysql80-community                  | 2.6 kB  00:00:00     (1/3): mysql-connectors-community/x86_64/primary_db            |  93 kB  00:00:01     (2/3): mysql-tools-community/x86_64/primary_db                 |  89 kB  00:00:01     (3/3): mysql80-community/x86_64/primary_db                     | 222 kB  00:00:01     Resolving Dependencies--> Running transaction check---> Package mysql-community-client.x86_64 0:8.0.31-1.el7 will be installed--> Processing Dependency: mysql-community-client-plugins = 8.0.31-1.el7 for package: mysql-community-client-8.0.31-1.el7.x86_64--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.31-1.el7.x86_64---> Package mysql-community-devel.x86_64 0:8.0.31-1.el7 will be installed***********//省略***********Installed:  mysql-community-client.x86_64 0:8.0.31-1.el7           mysql-community-devel.x86_64 0:8.0.31-1.el7           mysql-community-libs.x86_64 0:8.0.31-1.el7           mysql-community-libs-compat.x86_64 0:8.0.31-1.el7            mysql-community-server.x86_64 0:8.0.31-1.el7          Dependency Installed:  keyutils-libs-devel.x86_64 0:1.5.8-3.el7                     krb5-devel.x86_64 0:1.15.1-51.el7_9                  libaio.x86_64 0:0.3.109-13.el7   libcom_err-devel.x86_64 0:1.42.9-19.el7          libkadm5.x86_64 0:1.15.1-51.el7_9libselinux-devel.x86_64 0:2.5-15.el7                 libsepol-devel.x86_64 0:2.5-10.el7                           libverto-devel.x86_64 0:0.2.5-4.el7              mysql-community-client-plugins.x86_64 0:8.0.31-1.el7         mysql-community-common.x86_64 0:8.0.31-1.el7         mysql-community-icu-data-files.x86_64 0:8.0.31-1.el7         openssl-devel.x86_64 1:1.0.2k-22.el7_9           pcre-devel.x86_64 0:8.32-17.el7  zlib-devel.x86_64 0:1.2.7-19.el7_9                  Dependency Updated:  krb5-libs.x86_64 0:1.15.1-51.el7_9                       openssl.x86_64 1:1.0.2k-22.el7_9                       openssl-libs.x86_64 1:1.0.2k-22.el7_9                       zlib.x86_64 0:1.2.7-19.el7_9                      Replaced:  mariadb-libs.x86_64 1:5.5.68-1.el7                      Complete![root@myw ~]# 

常见mysql服务的操作(启动，停止，重启，加入开机自启动)

[root@myw ~]# systemctl daemon-reload[root@myw ~]# systemctl start mysqld.service[root@myw ~]# netstat -lnp|grep 3306tcp6       0      0 :::33060                :::*                    LISTEN      8203/mysqld         tcp6       0      0 :::3306                 :::*                    LISTEN      8203/mysqld  unix  2      [ ACC ]     STREAM     LISTENING     28463    8203/mysqld          /var/run/mysqld/mysqlx.sockunix  2      [ ACC ]     STREAM     LISTENING     28467    8203/mysqld          /var/lib/mysql/mysql.sock[root@myw ~]# systemctl status mysqld.service?.mysqld.service - MySQL Server   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)   Active: active (running) since Fri 2023-01-06 10:12:55 CST; 52s aGo     Docs: man:mysqld(8)           http://dev.mysql.com/doc/refman/en/using-systemd.html  Process: 8120 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 8203 (mysqld)   Status: "Server is operational"   CGroup: /system.slice/mysqld.service           ?..8203 /usr/sbin/mysqldJan 06 10:12:37 myw systemd[1]: Starting MySQL Server...Jan 06 10:12:55 myw systemd[1]: Started MySQL Server.[root@myw ~]# systemctl restart mysqld.service[root@myw ~]# netstat -lnp|grep 3306tcp6       0      0 :::33060                :::*                    LISTEN      8299/mysqld         tcp6       0      0 :::3306                 :::*                    LISTEN      8299/mysqld         [root@myw ~]# systemctl stop mysqld.service[root@myw ~]# netstat -lnp|grep 3306[root@myw ~]# systemctl start mysqld.service[root@myw ~]# systemctl enable mysqld.service[root@myw ~]# 

端口：3306 用于 MySQL Classic 协议（服务器端口选项，非常常用）

端口：33060 用于 MySQL X 协议（服务器 mysqlx_port 选项,一般不用，不管他）

 // 重新加载systemctl daemon-reload // 启动systemctl start mysqld.service // 重启systemctl restart mysqld.service // 停止systemctl stop mysqld.service // 查看启动状态systemctl status mysqld.service  // 加入开机启动systemctl enable mysqld.service // 取消开机启动systemctl disable mysqld.service 

centos7.x已经安装好了mysql8 还需要对mysql8进行数据库root密码修改和常见的配置

这里安装的mysql8需要去日志目录找到mysqld.log里找临时密码

/var/log/mysqld.log

[System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.31) initializing of server in progress as process 8154[System] [MY-013576] [InnoDB] InnoDB initialization has started.[System] [MY-013577] [InnoDB] InnoDB initialization has ended.[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: MQ4UOM&fdenM[System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 8203[System] [MY-013576] [InnoDB] InnoDB initialization has started.[System] [MY-013577] [InnoDB] InnoDB initialization has ended.[Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.[System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.[System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, Socket: /var/run/mysqld/mysqlx.sock[System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: mq4UOM&fdenM临时密码：mq4UOM&fdenM

使用临时密码登录后改密码

mysql8的默认密码策略要求密码必须是大小写字母数字特殊字母的组合，至少8位，有些时候为了简单，可以修改密码策略，实际个人觉得不影响使用，密码复杂点儿好，字符集需要utf8mb4，这里发现默认就是utf8mb4 可以说省了配置

ALTER USER 'root'@'localhost' IDENTIFIED BY 'myW.MyyhTw147258';

[root@jpmlg4syqgfe22ch ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.31Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \sERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.mysql> mysql> \sERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'myW.MyyhTw147258';Query OK, 0 rows affected (0.00 sec)mysql> \s--------------mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)Connection id:        8Current database:    Current user:        root@localhostSSL:            Not in useCurrent pager:        stdoutUsing outfile:        ''Using delimiter:    ;Server version:        8.0.31Protocol version:    10Connection:        Localhost via UNIX socketServer characterset:    utf8mb4Db     characterset:    utf8mb4Client characterset:    utf8mb4Conn.  characterset:    utf8mb4UNIX socket:        /var/lib/mysql/mysql.sockBinary data as:        HexadecimalUptime:            9 min 3 secThreads: 2  Questions: 8  Slow queries: 0  Opens: 129  Flush tables: 3  Open tables: 48  Queries per second avg: 0.014--------------mysql> 

我们在/ect/my.cnf里查看到的配置信息是如此的，可以根据需求调整

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove the leading "# " to disable binary logging# Binary logging captures changes between backups and is enabled by# default. It's default setting is log_bin=binlog# disable_log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2M## Remove leading # to revert to previous value for default_authentication_plugin,# this will increase compatibility with older clients. For background, see:# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin# default-authentication-plugin=mysql_native_passworddatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

到这里mysql就安装完成可以使用了，于是回过头来查询下

[root@myw ~]# whereis mysqlmysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql /usr/share/man/man1/mysql.1.gz[root@myw ~]# find / -name 'mysql*'/etc/ld.so.conf.d/mysql-x86_64.conf/etc/systemd/system/multi-user.target.wants/mysqld.service/etc/selinux/targeted/active/modules/100/mysql/etc/logrotate.d/mysql/etc/yum.repos.d/mysql-community.repo/etc/yum.repos.d/mysql-community-debuginfo.repo/etc/yum.repos.d/mysql-community-source.repo/sys/fs/cgroup/systemd/system.slice/mysqld.service/root/mysql80-community-release-el7-7.noarch.rpm/var/lib/yum/repos/x86_64/7/mysql80-community/var/lib/yum/repos/x86_64/7/mysql-connectors-community/var/lib/yum/repos/x86_64/7/mysql-tools-community/var/lib/mysql-keyring/var/lib/mysql-files/var/lib/mysql/var/lib/mysql/mysql.sock.lock/var/lib/mysql/mysql.sock/var/lib/mysql/mysql.ibd/var/lib/mysql/mysql/var/log/mysqld.log/var/cache/yum/x86_64/7/mysql80-community/var/cache/yum/x86_64/7/mysql-connectors-community/var/cache/yum/x86_64/7/mysql-tools-community/run/mysqld/run/mysqld/mysqld.pid/run/mysqld/mysqlx.sock/run/mysqld/mysqlx.sock.lock/usr/lib/systemd/system/mysqld.service/usr/lib/systemd/system/mysqld@.service/usr/lib/tmpfiles.d/mysql.conf/usr/lib/firewalld/services/mysql.xml/usr/share/aclocal/mysql.m4/usr/share/qemu-kvm/qemu-ga/fsfreeze-hook.d/mysql-flush.sh.sample/usr/share/mysql-8.0/usr/share/mysql-8.0/mysql-log-rotate/usr/share/man/man1/mysqldump.1.gz/usr/share/man/man1/mysql_upgrade.1.gz/usr/share/man/man1/mysqladmin.1.gz/usr/share/man/man1/mysqlpump.1.gz/usr/share/man/man1/mysqlimport.1.gz/usr/share/man/man1/mysqlman.1.gz/usr/share/man/man1/mysqlshow.1.gz/usr/share/man/man1/mysqlcheck.1.gz/usr/share/man/man1/mysqlslap.1.gz/usr/share/man/man1/mysql_config.1.gz/usr/share/man/man1/mysql_config_editor.1.gz/usr/share/man/man1/mysqldumpslow.1.gz/usr/share/man/man1/mysql_ssl_rsa_setup.1.gz/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz/usr/share/man/man1/mysqlbinlog.1.gz/usr/share/man/man1/mysql_secure_installation.1.gz/usr/share/man/man1/mysql.1.gz/usr/share/man/man8/mysqld.8.gz/usr/share/man/man5/mysql_table.5.gz/usr/share/doc/mysql-community-libs-compat-8.0.31/usr/share/doc/mysql-community-icu-data-files-8.0.31/usr/share/doc/mysql-community-common-8.0.31/usr/share/doc/mysql-community-client-plugins-8.0.31/usr/share/doc/mysql-community-server-8.0.31/usr/share/doc/mysql-community-client-8.0.31/usr/share/doc/mysql-community-devel-8.0.31/usr/share/doc/mysql-community-libs-8.0.31/usr/sbin/mysqld/usr/lib64/pkGConfig/mysqlclient.pc/usr/lib64/mysql/usr/lib64/mysql/plugin/mysql_no_login.so/usr/lib64/mysql/plugin/mysql_clone.so/usr/include/mysql/usr/include/mysql/mysql_com.h/usr/include/mysql/mysql_version.h/usr/include/mysql/mysqlx_version.h/usr/include/mysql/mysql_time.h/usr/include/mysql/mysqld_error.h/usr/include/mysql/mysqlx_error.h/usr/include/mysql/mysqlx_ername.h/usr/include/mysql/mysql.h/usr/include/mysql/mysql/usr/bin/mysql_migrate_keyring/usr/bin/mysql_upgrade/usr/bin/mysqlshow/usr/bin/mysql_config-64/usr/bin/mysqlpump/usr/bin/mysql_secure_installation/usr/bin/mysql_config/usr/bin/mysqlslap/usr/bin/mysqlimport/usr/bin/mysqlcheck/usr/bin/mysqlbinlog/usr/bin/mysql_tzinfo_to_sql/usr/bin/mysql_config_editor/usr/bin/mysql_ssl_rsa_setup/usr/bin/mysqld_pre_systemd/usr/bin/mysqldumpslow/usr/bin/mysqldump/usr/bin/mysql/usr/bin/mysqladmin[root@myw ~]#[root@myw ~]# rpm -qa|grep -i mysqlmysql-community-libs-8.0.31-1.el7.x86_64mysql-community-devel-8.0.31-1.el7.x86_64mysql80-community-release-el7-7.noarchmysql-community-common-8.0.31-1.el7.x86_64mysql-community-client-8.0.31-1.el7.x86_64mysql-community-icu-data-files-8.0.31-1.el7.x86_64mysql-community-server-8.0.31-1.el7.x86_64mysql-community-libs-compat-8.0.31-1.el7.x86_64mysql-community-client-plugins-8.0.31-1.el7.x86_64[root@myw ~]#  

可以对比看到增加了不少东西，如果要卸载mysql来重新安装升级版本，这样要移除的文件太多了，操作起来麻烦不说，还不一定能卸载干净，所以建议重新选择版本相近的系统，或者重装系统，以免卸载后重装出现未知的错误问题而无法解决，可是有些条件下必须卸载，那就只有走卸载的方式了。

查询mysql的版本

[root@myw ~]# mysql -Vmysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)[root@myw ~]# mysql --versionmysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)[root@myw ~]#

create database myw_test

[root@myw ~]# find / -name 'myw_test'/var/lib/mysql/myw_test[root@myw ~]# 

通过创建一个数据库方式 再去找这个数据库的名字就能找到mysql默认存数据的位置

mysql8的卸载操作

卸载mysql8不一定能完全卸载干净的，只能说尝试卸载干净吧

[root@myw ~]# rpm -qa|grep -i mysqlmysql-community-libs-8.0.31-1.el7.x86_64mysql-community-devel-8.0.31-1.el7.x86_64mysql80-community-release-el7-7.noarchmysql-community-common-8.0.31-1.el7.x86_64mysql-community-client-8.0.31-1.el7.x86_64mysql-community-icu-data-files-8.0.31-1.el7.x86_64mysql-community-server-8.0.31-1.el7.x86_64mysql-community-libs-compat-8.0.31-1.el7.x86_64mysql-community-client-plugins-8.0.31-1.el7.x86_64[root@myw ~]# yum -y remove mysql mysql-server mysql-devel// 省略了Removed:  mysql-community-client.x86_64 0:8.0.31-1.el7   mysql-community-devel.x86_64 0:8.0.31-1.el7   mysql-community-server.x86_64 0:8.0.31-1.el7  Complete![root@myw ~]# rpm -qa|grep -i mysqlmysql-community-libs-8.0.31-1.el7.x86_64mysql80-community-release-el7-7.noarchmysql-community-common-8.0.31-1.el7.x86_64mysql-community-icu-data-files-8.0.31-1.el7.x86_64mysql-community-libs-compat-8.0.31-1.el7.x86_64mysql-community-client-plugins-8.0.31-1.el7.x86_64[root@myw ~]# 

可以看到上面的操作只是卸载了3个主要的，可以使用2个指令来删除

yum remove XXXrpm -ev --nodeps XXXXXXXXXXX

[root@myw ~]# rpm -ev --nodeps mysql-community-libs-8.0.31-1.el7.x86_64Preparing packages...mysql-community-libs-8.0.31-1.el7.x86_64[root@myw ~]# rpm -qa | grep mysql[root@myw ~]# yum remove mysql-community-common-8.0.31-1.el7.x86_64//   省略，选择yInstalled size: 10 MIs this ok [y/N]: yDownloading packages:Running transaction checkRunning transaction testTransaction test succeededRunning transaction  Erasing    : mysql-community-common-8.0.31-1.el7.x86_64                         1/1   Verifying  : mysql-community-common-8.0.31-1.el7.x86_64                         1/1 Removed:  mysql-community-common.x86_64 0:8.0.31-1.el7            Complete![root@myw ~]# rpm -qa | grep mysqlmysql-community-icu-data-files-8.0.31-1.el7.x86_64mysql-community-libs-compat-8.0.31-1.el7.x86_64mysql-community-client-plugins-8.0.31-1.el7.x86_64[root@myw ~]# // 建议使用 rpm -ev --nodeps XXXXX

如此再执行查询

[root@myw ~]# rpm -qa | grep mysql[root@myw ~]# whereis mysqlmysql: /usr/lib64/mysql[root@myw ~]# find / -name 'mysql*'/etc/selinux/targeted/active/modules/100/mysql/root/mysql80-community-release-el7-7.noarch.rpm/var/lib/yum/repos/x86_64/7/mysql80-community/var/lib/yum/repos/x86_64/7/mysql-connectors-community/var/lib/yum/repos/x86_64/7/mysql-tools-community/var/lib/mysql/var/lib/mysql/mysql.ibd/var/lib/mysql/mysql/var/log/mysqld.log/var/cache/yum/x86_64/7/mysql80-community/var/cache/yum/x86_64/7/mysql-connectors-community/var/cache/yum/x86_64/7/mysql-tools-community/usr/lib/firewalld/services/mysql.xml/usr/share/qemu-kvm/qemu-ga/fsfreeze-hook.d/mysql-flush.sh.sample/usr/share/mysql-8.0/usr/share/man/man5/mysql_table.5.gz/usr/lib64/mysql[root@myw ~]# find / -name 'mysql'/etc/selinux/targeted/active/modules/100/mysql/var/lib/mysql/var/lib/mysql/mysql/usr/lib64/mysql[root@myw ~]# 

可以看到查询的东西少了不少，但其中还是有些东西需要删除的

圈出来的都是需要删除的，其中数据存储的位置/var/lib/mysql需要备份慎重删除的，这里是放数据的

[root@myw ~]# rm -rf /root/mysql80-community-release-el7-7.noarch.rpm[root@myw ~]# rm -rf /var/lib/yum/repos/x86_64/7/mysql*[root@myw ~]# rm -rf /var/lib/mysql[root@myw ~]# rm -rf /var/cache/yum/x86_64/7/mysql*[root@myw ~]# rm -rf /var/log/mysqld.log[root@myw ~]# rm -rf /usr/lib/firewalld/services/mysql.xml[root@myw ~]# rm -rf /usr/share/mysql-8.0[root@myw ~]# rm -rf /usr/share/man/man5/mysql_table.5.gz[root@myw ~]# find / -name 'mysql'/etc/selinux/targeted/active/modules/100/mysql/usr/lib64/mysql[root@myw ~]# find / -name 'mysql*'/etc/selinux/targeted/active/modules/100/mysql/usr/share/qemu-kvm/qemu-ga/fsfreeze-hook.d/mysql-flush.sh.sample/usr/lib64/mysql[root@myw ~]# whereis mysqlmysql: /usr/lib64/mysql[root@myw ~]# 

还有一个my.cnf配置文件，目前执行了上面的操作 my.cnf文件已经消失不见了(/etc/my.cnf)，那么如此算是卸载干净了，如果文件还存在，需要删除，以免后面新安装mysql或者mariadb影响到

rm -rf /etc/my.cnf

mysql5.7的安装操作(简写，很少用了)

wget https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpmrpm -ivh mysql57-community-release-el7-10.noarch.rpmyum -y install mysql-server mysql-server mysql-devel

在执行yum -y install mysql-server mysql-server mysql-devel时，报出密匙过期的错误

[root@myw ~]# yum -y install mysql-server mysql-devel// 省略了  Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysqlThe GPG keys listed for the "MySQL 5.7 Community Server" repository are already installed but they are not correct for this package.Check that the correct key URLs are configured for this repository. Failing package is: mysql-community-client-5.7.40-1.el7.x86_64 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql[root@myw ~]#

那么需要更新密匙后重新安装

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

实际上这个更新密匙来自这里儿(下图片)

执行那条语句后，重新安装，就能完成安装

yum -y install mysql-server mysql-server mysql-devel

mysql5.7也是有临时密码的，在/var/log/mysqld.log里面

[root@myw ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.40Copyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'myW.MyyhTw147258';Query OK, 0 rows affected (0.00 sec)mysql> select Host, User, plugin, authentication_string, password_lifetime, password_last_changed from user;ERROR 1046 (3D000): No database selectedmysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select Host, User, plugin, authentication_string, password_lifetime, password_last_changed from user;+-----------+---------------+-----------------------+-------------------------------------------+-------------------+-----------------------+| Host      | User          | plugin                | authentication_string                     | password_lifetime | password_last_changed |+-----------+---------------+-----------------------+-------------------------------------------+-------------------+-----------------------+| localhost | root          | mysql_native_password | *2F9E1755F3CDF48D3A31BFEB08C74DC824CD1EC4 |              NULL | 2023-01-06 18:25:50   || localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |              NULL | 2023-01-06 18:04:10   || localhost | mysql.sys     | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |              NULL | 2023-01-06 18:04:10   |+-----------+---------------+-----------------------+-------------------------------------------+-------------------+-----------------------+3 rows in set (0.00 sec)mysql> \s--------------mysql  Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using  EditLine wrapperConnection id:        4Current database:    mysqlCurrent user:        root@localhostSSL:            Not in useCurrent pager:        stdoutUsing outfile:        ''Using delimiter:    ;Server version:        5.7.40Protocol version:    10Connection:        Localhost via UNIX socketServer characterset:    latin1Db     characterset:    latin1Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:        /var/lib/mysql/mysql.sockUptime:            23 min 25 secThreads: 1  Questions: 43  Slow queries: 0  Opens: 137  Flush tables: 1  Open tables: 130  Queries per second avg: 0.030--------------mysql> 

可以看到，mysql5.7的默认plugin是mysql_native_password 这个和mysql8不一样，得注意，还有一点就是mysql5.7的编码是utf8和latin1，这在开发中是需要重新配置的，在/etc/my.cnf里面配置

# For advice on how to change settings please see# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[client]default-character-set=utf8mb4[mysql]default-character-set=utf8mb4[mysqld]## Remove leading # and set to the amount of RAM for the most important data# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.# innodb_buffer_pool_size = 128M## Remove leading # to turn on a very important data integrity option: logging# changes to the binary log between backups.# log_bin## Remove leading # to set options mainly useful for reporting servers.# The server defaults are faster for transactions and fast SELECTs.# Adjust sizes as needed, experiment to find the optimal values.# join_buffer_size = 128M# sort_buffer_size = 2M# read_rnd_buffer_size = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockcharacter-set-client-handshake = FALSEcharacter-set-server=utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES tmp_table_size = 256Mmax_heap_table_size = 256Mlog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

修改重启mysql5.7后(utf8mb4)

[root@myw ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \s--------------mysql  Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using  EditLine wrapperConnection id:        2Current database:    Current user:        root@localhostSSL:            Not in useCurrent pager:        stdoutUsing outfile:        ''Using delimiter:    ;Server version:        5.7.40 MySQL Community Server (GPL)Protocol version:    10Connection:        Localhost via UNIX socketServer characterset:    utf8mb4Db     characterset:    utf8mb4Client characterset:    utf8mb4Conn.  characterset:    utf8mb4UNIX socket:        /var/lib/mysql/mysql.sockUptime:            55 secThreads: 1  Questions: 5  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.090--------------mysql> 

mysql8（mysql5.7）的常见不常用sql

记录，mysql数据库都是需要远程访问的，特别是大家一起开发的时候（本地数据库的效果反而很差），那么我们需要配置远程访问

先看看mysql的user表

mysql> use mysql;Database changedmysql> select Host, User, plugin, authentication_string, password_lifetime, password_last_changed from user;+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| Host      | User             | plugin                | authentication_string                      | password_lifetime | password_last_changed |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | root             | caching_sha2_password | $A$005$l    5{fEm?d?Y-jYFeJj.lUv3eAFlEstmd8AKtJW.foBc8b9OwGHRQXn/hi0 |              NULL | 2023-01-06 10:32:46   |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+4 rows in set (0.00 sec)

root账号的host是localhost,那么仅仅支持本地访问，而且加密方式是caching_sha2_password(mysql8)，一般情况下root账户在开发或者生产最好不要远程能够访问到，容易出事故，如果我就是需要远程访问而且还能有所有权限，那么可以新建一个账户来解决

mysql> CREATE USER 'sysroot'@'%' IDENTIFIED BY 'myW.123456';Query OK, 0 rows affected (0.01 sec)mysql> GRANT ALL ON *.* TO 'sysroot'@'%';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> select Host, User, plugin, authentication_string, password_lifetime, password_last_changed from user;+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| Host      | User             | plugin                | authentication_string                      | password_lifetime | password_last_changed |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| %         | sysroot          | caching_sha2_password | $A$005$u3WO"ME1BpbhvvAQPHnrrhNnvi8NUQXUr0vi643HwtmS4PWWYfPB |              NULL | 2023-01-06 10:55:12   || localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | root             | caching_sha2_password | $A$005$l    5{fEm?d?Y-jYFeJj.lUv3eAFlEstmd8AKtJW.foBc8b9OwGHRQXn/hi0 |              NULL | 2023-01-06 10:32:46   |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+5 rows in set (0.00 sec)mysql> 

可以看到新增的sysroot用户host是% 加密方式是caching_sha2_password(mysql5.7没有)，这个时候用Navicat或者其他工具连接，如果说出现连接不成功，有可能是密码的加密模式不支持caching_sha2_password，还是使用的mysql_native_password方式，那么可以重新设置一下加密方式并且更新密码

mysql> ALTER USER 'sysroot'@'%' IDENTIFIED WITH mysql_native_password BY 'myW.123456';Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql>  select Host, User, plugin, authentication_string, password_lifetime, password_last_changed from user;+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| Host      | User             | plugin                | authentication_string                      | password_lifetime | password_last_changed |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| %         | sysroot          | mysql_native_password | *F44ED6676E63CA5264A14380FAAF279B31482776  |              NULL | 2023-01-06 10:59:59   || localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | root             | caching_sha2_password | $A$005$l    5{fEm?d?Y-jYFeJj.lUv3eAFlEstmd8AKtJW.foBc8b9OwGHRQXn/hi0 |              NULL | 2023-01-06 10:32:46   |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+5 rows in set (0.00 sec)mysql> 

可以看到plugin的密码的加密方式变了。

假如我不需要这个用户了，那么可以把这个用户删掉

DROP USER 'XXXXX'@'XXX';

例子,创建一个把他删除

mysql> CREATE USER 'myroot'@'%' IDENTIFIED BY 'myW.123456';Database changedQuery OK, 0 rows affected (0.01 sec)mysql> GRANT ALL ON *.* TO 'myroot'@'%';Query OK, 0 rows affected (0.01 sec)mysql> select Host, User, plugin, authentication_string, password_lifetime, password_last_changed from user;+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| Host      | User             | plugin                | authentication_string                      | password_lifetime | password_last_changed |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| %         | myroot           | caching_sha2_password | $A$005$|XdSN%Rj^%v9&.aa7oKHsz1/WwvfhJnXM6l.WHnJqqCM9e0RANZCbx/aSsgD |              NULL | 2023-01-06 11:05:23   || %         | sysroot          | caching_sha2_password | $A$005$7]{|qn&%;NZ@%b,D^z9.mBn942HLnoa.Y30HmjsioHjT2jyGsXtMjMbq14D4 |              NULL | 2023-01-06 11:03:07   || localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | root             | caching_sha2_password | $A$005$l    5{fEm?d?Y-jYFeJj.lUv3eAFlEstmd8AKtJW.foBc8b9OwGHRQXn/hi0 |              NULL | 2023-01-06 10:32:46   |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+6 rows in set (0.00 sec)mysql> DROP USER 'myroot'@'%';Query OK, 0 rows affected (0.01 sec)mysql>  select Host, User, plugin, authentication_string, password_lifetime, password_last_changed from user;+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| Host      | User             | plugin                | authentication_string                      | password_lifetime | password_last_changed |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+| %         | sysroot          | caching_sha2_password | $A$005$7]{|qn&%;NZ@%b,D^z9.mBn942HLnoa.Y30HmjsioHjT2jyGsXtMjMbq14D4 |              NULL | 2023-01-06 11:03:07   || localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.session    | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | mysql.sys        | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |              NULL | 2023-01-06 10:12:51   || localhost | root             | caching_sha2_password | $A$005$l    5{fEm?d?Y-jYFeJj.lUv3eAFlEstmd8AKtJW.foBc8b9OwGHRQXn/hi0 |              NULL | 2023-01-06 10:32:46   |+-----------+------------------+-----------------------+------------------------------------------------------------------------+-------------------+-----------------------+5 rows in set (0.00 sec)mysql> 

可以看到mysql的存储数据的地址 在 var/lib/mysql下面(这个平时很少用，万一出问题很重要)

记录一下mysql账号的操作，创建用户 ，更改密码，授权用户, 取消授权，刷新权限，删除用户

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password'username: 你要创建的用户名字(非中文) host: 指定该用户登录主机范围(本地用户localhost, 任意主机登陆,使用通配符%)password: 该用户的登陆密码,按照密码的策略，一般大小写和特殊字符长度大于8CREATE USER 'mywroot'@'localhost' IDENTIFIED BY 'myW.MyyhTw147258'CREATE USER 'mywroot'@'%' IDENTIFIED BY 'myW.MyyhTw147258'

更改密码

ALTER USER 'username'@'host' IDENTIFIED WITH (plugin) BY 'password';plugin: 密码方式  caching_sha2_password  mysql_native_passwordALTER USER 'mywroot'@'%' IDENTIFIED WITH mysql_native_password BY 'myW.123456'ALTER USER 'mywroot'@'%' IDENTIFIED WITH caching_sha2_password BY 'myW.123456'(mysql8)

授权用户

GRANT (privileges) ON (databasename.tablename) TO 'username'@'host'GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION(该方式创建的用户可以给用户授权)privileges: 用户的操作权限 SELECT, INSERT, UPDATE, 所的权限则使用ALLdatabasename: 数据库名tablename: 表名所有数据库和表的相应操作权限则可用*表示 *.*GRANT SELECT, INSERT, UPDATE ON mywdata.account TO 'mywroot'@'%'GRANT ALL ON *.* TO 'mywroot'@'%'查看授权信息SHOW GRANTS FOR 'mywroot'@'%'

取消授权

REVOKE (privileges) ON (databasename.tablename) FROM 'username'@'host'REVOKE SELECT, INSERT ON *.* FROM 'mywroot'@'%'

刷新权限

flush privileges;更新或者修改或者删除用户权限后都刷新

删除用户

DROP USER 'username'@'host'

记录一下查询mysql表容量大小的查询sql(用的机会少，但每次用都去查还不如自己记录)

查询存在的数据库容量大小select     table_schema as 'database_name', sum(table_rows) as 'record_number', sum(truncate(data_length/1024/1024, 2)) as 'data_size(MB)', sum(truncate(index_length/1024/1024, 2)) as 'index size(MB)'from     infORMation_schema.TABLESgroup by     table_schemaorder by     sum(data_length) desc, sum(index_length) desc查询存在数据库的各表容量大小select     table_schema as 'database_name', table_name as 'table_name', table_rows as 'record_number', truncate(data_length/1024/1024, 2) as 'data_size(MB)', truncate(index_length/1024/1024, 2) as 'index size(MB)'from     information_schema.TABLESorder by     data_length desc, index_length desc查询指定的数据库容量大小select     table_schema as 'database_name', sum(table_rows) as 'record_number', sum(truncate(data_length/1024/1024, 2)) as 'data_size(MB)', sum(truncate(index_length/1024/1024, 2)) as 'index size(MB)'from     information_schema.TABLESwhere     table_schema = 'test'group by     table_schemaorder by     sum(data_length) desc, sum(index_length) desc查询指定的数据库各表容量大小select     table_schema as 'database_name', table_name as 'table_name', table_rows as 'record_number', truncate(data_length/1024/1024, 2) as 'data_size(MB)', truncate(index_length/1024/1024, 2) as 'index size(MB)'from     information_schema.TABLESwhere     table_schema = 'test'    order by     data_length desc, index_length desc查询指定的数据库-指定表容量大小select     table_schema as 'database_name', table_name as 'table_name', table_rows as 'record_number', truncate(data_length/1024/1024, 2) as 'data_size(MB)', truncate(index_length/1024/1024, 2) as 'index size(MB)'from     information_schema.TABLESwhere     table_schema = 'test' and table_name = 'test.account'order by     data_length desc, index_length desc

记录一下查询mysql 在centos7.x下面备份

备份数据库 myw_test mysqldump -usysroot -p myw_test > /home/myw_test-20230101.sql备份数据库 myw_test 表名  accountmysqldump -usysroot -p myw_test account > /home/myw_test_account-20230101.sql按照指定id范围备份数据库mysqldump -usysroot -p myw_test account -w "account.user_id < 1920" > /home/myw_test_account-20230101.sqlmysqldump -usysroot -p myw_test account -w "account.user_id > 12 and account.user_id < 1920" > /home/myw_test_account-20230101.sql

记录一下查询正在运行的线程以及锁等待，死锁的情况

SELECT * FROM information_schema.INNODB_TRXkill XXXXX   XXXXX这个值是trx_mysql_thread_id

在执行结果中如果看到有等待的或者直接锁死成死锁了，直接杀掉

select * from information_schema.processlist;

显示用户正在运行的线程，这里特殊点儿（除了超级的 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程）

来源地址：https://blog.csdn.net/myyhtw/article/details/128573673