煮酒与君饮

这个屌丝很懒，什么也没留下！

热门标签

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

作者：煮酒与君饮 | 2024-08-09 23:47:51

踩

yum安装mysql

记录一下在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/)找到对应的版本信息

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

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

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

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

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

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

我们可以看到

妥妥的一大笔资源在这里，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


[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参数，这个参数一般需要启用，这样可以减少磁盘的日志输入，降低使用的资源，这样使用查询指令


mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.02 sec)
 
mysql>

可以看到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默认存数据的位置

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 ~]#

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

圈出来的都是需要删除的，其中数据存储的位置/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

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

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

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://www.wpsshop.cn/w/煮酒与君饮/article/detail/955678