Centos下使用yum安装Mysql8（Mysql5.7）以及常见的配置和使用

Myth丶恋晨 2024-03-30 15:18 10阅读 0赞

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

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

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

[root@myw ~]# whereis mysql
    mysql: /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/)][https_dev.mysql.com_downloads_repo_yum]找到对应的版本信息

![100801f86af444efb50c64d25563b566.png][]

![33bea6cbb735452a9f836c8dceca8bdd.png][]

![808068ef1cb5409a9a1c6ba5af3cd25d.png][]

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

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

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

![3bcfad3adb6445c38cdaf98ba69969dc.png][]

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

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

既然得到了这个链接，那么可以单独访问[Index of /232905][Index of _232905]

我们可以看到

![5ffbd05c23fc4680a0f67896bb8f8eda.png][]

妥妥的一大笔资源在这里，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 wget
    Loaded plugins: fastestmirror
    Determining fastest mirrors
     * base: 100.125.0.40
     * extras: 100.125.0.40
     * updates: 100.125.0.40
    base                                                                                                                                                                                                       | 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 k
    Installed size: 2.0 M
    Downloading packages:
    wget-1.14-18.el7_6.1.x86_64.rpm                                                                                                                                                                            | 547 kB  00:00:00     
    Running transaction check
    Running transaction test
    Transaction test succeeded
    Running 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.rpm
    Resolving repo.mysql.com (repo.mysql.com)... 184.30.152.230
    Connecting to repo.mysql.com (repo.mysql.com)|184.30.152.230|:443... connected.
    HTTP request sent, awaiting response... 200 OK
    Length: 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.rpm
    warning: mysql80-community-release-el7-7.noarch.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
    Preparing...                          ################################# [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-devel
    Loaded plugins: fastestmirror
    Loading mirror speeds from cached hostfile
     * base: 100.125.0.40
     * extras: 100.125.0.40
     * updates: 100.125.0.40
    mysql-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_9                            libselinux-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 3306
    tcp6       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.sock
    unix  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/mysqld
    
    Jan 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 3306
    tcp6       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

![ed91fef6889544899f3dd257a929241b.png][]

[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 -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 8
    Server version: 8.0.31
    
    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> \s
    ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
    mysql> 
    mysql> \s
    ERROR 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:        8
    Current database:    
    Current user:        root@localhost
    SSL:            Not in use
    Current pager:        stdout
    Using outfile:        ''
    Using delimiter:    ;
    Server version:        8.0.31
    Protocol version:    10
    Connection:        Localhost via UNIX socket
    Server characterset:    utf8mb4
    Db     characterset:    utf8mb4
    Client characterset:    utf8mb4
    Conn.  characterset:    utf8mb4
    UNIX socket:        /var/lib/mysql/mysql.sock
    Binary data as:        Hexadecimal
    Uptime:            9 min 3 sec
    
    Threads: 2  Questions: 8  Slow queries: 0  Opens: 129  Flush tables: 3  Open tables: 48  Queries per second avg: 0.014
    --------------
    
    mysql>

我们在/etc/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_password
    
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

配置文件中，有一个 disable\_log\_bin参数 ，这个参数一般需要启用，这样可以减少磁盘的日志输入，降低使用的资源，这样使用查询指令

可以看到log\_bin的值是OFF

mysql> show variables like "%bin%";
    +------------------------------------------------+----------------------+
    | Variable_name                                  | Value                |
    +------------------------------------------------+----------------------+
    | bind_address                                   | *                    |
    | binlog_cache_size                              | 32768                |
    | binlog_checksum                                | CRC32                |
    | binlog_direct_non_transactional_updates        | OFF                  |
    | binlog_encryption                              | OFF                  |
    | binlog_error_action                            | ABORT_SERVER         |
    | binlog_expire_logs_auto_purge                  | ON                   |
    | binlog_expire_logs_seconds                     | 2592000              |
    | binlog_format                                  | ROW                  |
    | binlog_group_commit_sync_delay                 | 0                    |
    | binlog_group_commit_sync_no_delay_count        | 0                    |
    | binlog_gtid_simple_recovery                    | ON                   |
    | binlog_max_flush_queue_time                    | 0                    |
    | binlog_order_commits                           | ON                   |
    | binlog_rotate_encryption_master_key_at_startup | OFF                  |
    | binlog_row_event_max_size                      | 8192                 |
    | binlog_row_image                               | FULL                 |
    | binlog_row_metadata                            | MINIMAL              |
    | binlog_row_value_options                       |                      |
    | binlog_rows_query_log_events                   | OFF                  |
    | binlog_stmt_cache_size                         | 32768                |
    | binlog_transaction_compression                 | OFF                  |
    | binlog_transaction_compression_level_zstd      | 3                    |
    | binlog_transaction_dependency_history_size     | 25000                |
    | binlog_transaction_dependency_tracking         | COMMIT_ORDER         |
    | innodb_api_enable_binlog                       | OFF                  |
    | log_bin                                        | OFF                  |
    | log_bin_basename                               |                      |
    | log_bin_index                                  |                      |
    | log_bin_trust_function_creators                | OFF                  |
    | log_bin_use_v1_row_events                      | OFF                  |
    | log_statements_unsafe_for_binlog               | ON                   |
    | max_binlog_cache_size                          | 18446744073709547520 |
    | max_binlog_size                                | 1073741824           |
    | max_binlog_stmt_cache_size                     | 18446744073709547520 |
    | mysqlx_bind_address                            | *                    |
    | sql_log_bin                                    | ON                   |
    | sync_binlog                                    | 1                    |
    +------------------------------------------------+----------------------+
    38 rows in set (0.00 sec)
    
    mysql>

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

[root@myw ~]# whereis mysql
    mysql: /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 mysql
    mysql-community-libs-8.0.31-1.el7.x86_64
    mysql-community-devel-8.0.31-1.el7.x86_64
    mysql80-community-release-el7-7.noarch
    mysql-community-common-8.0.31-1.el7.x86_64
    mysql-community-client-8.0.31-1.el7.x86_64
    mysql-community-icu-data-files-8.0.31-1.el7.x86_64
    mysql-community-server-8.0.31-1.el7.x86_64
    mysql-community-libs-compat-8.0.31-1.el7.x86_64
    mysql-community-client-plugins-8.0.31-1.el7.x86_64
    [root@myw ~]#

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

查询mysql的版本

[root@myw ~]# mysql -V
    mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
    [root@myw ~]# mysql --version
    mysql  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默认存数据的位置

![768b7e21be78422997a4c927cf887144.png][]

## mysql8的卸载操作 ##

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

[root@myw ~]# rpm -qa|grep -i mysql
    mysql-community-libs-8.0.31-1.el7.x86_64
    mysql-community-devel-8.0.31-1.el7.x86_64
    mysql80-community-release-el7-7.noarch
    mysql-community-common-8.0.31-1.el7.x86_64
    mysql-community-client-8.0.31-1.el7.x86_64
    mysql-community-icu-data-files-8.0.31-1.el7.x86_64
    mysql-community-server-8.0.31-1.el7.x86_64
    mysql-community-libs-compat-8.0.31-1.el7.x86_64
    mysql-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 mysql
    mysql-community-libs-8.0.31-1.el7.x86_64
    mysql80-community-release-el7-7.noarch
    mysql-community-common-8.0.31-1.el7.x86_64
    mysql-community-icu-data-files-8.0.31-1.el7.x86_64
    mysql-community-libs-compat-8.0.31-1.el7.x86_64
    mysql-community-client-plugins-8.0.31-1.el7.x86_64
    [root@myw ~]#

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

yum remove XXX
    
    rpm -ev --nodeps XXXXXXXXXXX

[root@myw ~]# rpm -ev --nodeps mysql-community-libs-8.0.31-1.el7.x86_64
    Preparing 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
    //   省略，选择y
    Installed size: 10 M
    Is this ok [y/N]: y
    Downloading packages:
    Running transaction check
    Running transaction test
    Transaction test succeeded
    Running 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 mysql
    mysql-community-icu-data-files-8.0.31-1.el7.x86_64
    mysql-community-libs-compat-8.0.31-1.el7.x86_64
    mysql-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 mysql
    mysql: /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 ~]#

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

![0304a899c4bd4ec18f20ea7d6341f4be.png][]

圈出来的都是需要删除的，其中数据存储的位置/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 mysql
    mysql: /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.rpm
    
    rpm -ivh mysql57-community-release-el7-10.noarch.rpm
    
    yum -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-mysql
    
    
    The 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

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

![7deaff6b14824b9aabac71a57f8953cf.png][]

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

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

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

[root@myw ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.40
    
    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> 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 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> 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 wrapper
    
    Connection id:        4
    Current database:    mysql
    Current user:        root@localhost
    SSL:            Not in use
    Current pager:        stdout
    Using outfile:        ''
    Using delimiter:    ;
    Server version:        5.7.40
    Protocol version:    10
    Connection:        Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    utf8
    Conn.  characterset:    utf8
    UNIX socket:        /var/lib/mysql/mysql.sock
    Uptime:            23 min 25 sec
    
    Threads: 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 = 2M
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    character-set-client-handshake = FALSE
    character-set-server=utf8mb4
    collation-server = utf8mb4_unicode_ci
    init_connect='SET NAMES utf8mb4'
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
    tmp_table_size = 256M
    max_heap_table_size = 256M
    
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

修改重启mysql5.7后(utf8mb4)

[root@myw ~]# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    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> \s
    --------------
    mysql  Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using  EditLine wrapper
    
    Connection id:        2
    Current database:    
    Current user:        root@localhost
    SSL:            Not in use
    Current pager:        stdout
    Using outfile:        ''
    Using delimiter:    ;
    Server version:        5.7.40 MySQL Community Server (GPL)
    Protocol version:    10
    Connection:        Localhost via UNIX socket
    Server characterset:    utf8mb4
    Db     characterset:    utf8mb4
    Client characterset:    utf8mb4
    Conn.  characterset:    utf8mb4
    UNIX socket:        /var/lib/mysql/mysql.sock
    Uptime:            55 sec
    
    Threads: 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 changed
    mysql> 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 changed
    Query 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: 该用户的登陆密码,按照密码的策略，一般大小写和特殊字符长度大于8
    
    CREATE 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_password
    
    ALTER 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, 所的权限则使用ALL
    databasename: 数据库名
    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.TABLES
    group by 
        table_schema
    order 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.TABLES
    order 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.TABLES
    where 
        table_schema = 'test'
    group by 
        table_schema
    order 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.TABLES
    where 
        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.TABLES
    where 
        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 表名  account
    mysqldump -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.sql
    
    mysqldump -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_TRX
    
    kill XXXXX   
    
    XXXXX这个值是trx_mysql_thread_id

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

select * from information_schema.processlist;

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

[https_dev.mysql.com_downloads_repo_yum]: https://dev.mysql.com/downloads/repo/yum/%29
[100801f86af444efb50c64d25563b566.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bdc331ee0ad241ae9fb4cb9fdd83c540.png
[33bea6cbb735452a9f836c8dceca8bdd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/92dde12fe1fd4d97bdccf9cfe924ba95.png
[808068ef1cb5409a9a1c6ba5af3cd25d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/676e03784c844adba12250f05ba7026b.png
[3bcfad3adb6445c38cdaf98ba69969dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/26e8e87478d1435ba8c47838d4021ee5.png
[Index of _232905]: http://repo.mysql.com/
[5ffbd05c23fc4680a0f67896bb8f8eda.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c20c7297a2f043f2a5cc2f184d1c6f86.png
[ed91fef6889544899f3dd257a929241b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/6172690daea3480db19e7d0d4dc945f1.png
[768b7e21be78422997a4c927cf887144.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/567622aaacd3411790e5c85922cde612.png
[0304a899c4bd4ec18f20ea7d6341f4be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/efd438079af346a3acf008f218401d3b.png
[7deaff6b14824b9aabac71a57f8953cf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/ea966c8cd1e2416e86b3da04744af6d3.png