CentOS7系统使用rpm方式安装MySQL5.7

最新推荐文章于 2024-04-17 08:49:52 发布

weixin_47072624

最新推荐文章于 2024-04-17 08:49:52 发布

阅读量327

点赞数

分类专栏： linux 文章标签： linux

本文链接：https://blog.csdn.net/weixin_47072624/article/details/120653606

版权

linux 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

参考：https://blog.csdn.net/wudinaniya/article/details/81094578

1、首先去mysql官网下载rpm包，一个是server包一个是client包。官网下载地址：https://www.mysql.com/downloads/

这里用mysql community server里边的。这里使用的是5.7.x版本了，不使用Mysql8.x版本了。

我这里使用的是Centos7.x版本的64位操作系统，使用mysql5.7版本的数据库。查看linux操作系统版本和系统内核版本，如下所示：

1 [root@k8s-node3 ~]# cat /etc/redhat-release　　# 查看操作系统版本　　
2 CentOS Linux release 7.6.1810 (Core)
3 [root@k8s-node3 ~]# uname -r　　# 检查系统内核版本
4 3.10.0-957.el7.x86_64
5 [root@k8s-node3 ~]#
选择对应的Linux版本和x86/x64进行下载，可以选择 RPM Bundle，下载完记得解压 tar -xvf xxx.tar。

可以离线下载上传或者在线下载包，如下所示：

1 [root@k8s-node3 package]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
2 --2020-07-15 13:46:49-- https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-1.el7.x86_64.rpm-bundle.tar
3 Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
4 Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... failed: Connection refused.
5 [root@k8s-node3 package]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
6 --2020-07-15 13:47:14-- https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
7 Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
8 Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
9 HTTP request sent, awaiting response... 302 Found
10 Location: https://cdn.mysql.com//archives/mysql-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar [following]
11 --2020-07-15 13:47:16-- https://cdn.mysql.com//archives/mysql-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
12 Resolving cdn.mysql.com (cdn.mysql.com)... 104.85.161.42
13 Connecting to cdn.mysql.com (cdn.mysql.com)|104.85.161.42|:443... connected.
14 HTTP request sent, awaiting response... 200 OK
15 Length: 598026240 (570M) [application/x-tar]
16 Saving to: ‘mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar’
17
18 20% [=================> ] 125,263,632 2.17MB/s eta 4m 55s
也可以细化下载，下载须要的mysql组件，有4个：分别是 server、client、common、libs。

卸载旧版本的MySql，如果没有的话，则跳过即可，直接安装mysql。

1 [root@k8s-node3 package]# rpm -qa | grep mysql
2 [root@k8s-node3 package]#
如果有的话，将会列出旧版本MySql的组件列表，逐个删除掉旧的组件，使用命令rpm -e --nodeps {-file-name}进行移除操作，移除的时候可能会有依赖，要注意一定的顺序，{-file-name}是待删除的mysql的文件。我这里是没有，直接略过了。

2、使用 rpm 命令方式安装mysql组件，使用命令rpm -ivh {-file-name}进行安装操作。按照依赖关系依次安装rpm包依赖关系依次common→libs→client→server。如下所示：

备注：在ivh中， i-install安装、v-verbose进度条、h-hash哈希校验。

1 [root@k8s-node3 package]# rpm -ivh mysql-community-common-5.7.30-1.el7.x86_64.rpm
2 warning: mysql-community-common-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
3 Preparing... ################################# [100%]
4 file /usr/share/mysql/czech/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
5 file /usr/share/mysql/danish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
6 file /usr/share/mysql/dutch/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
7 file /usr/share/mysql/english/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
8 file /usr/share/mysql/estonian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
9 file /usr/share/mysql/french/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
10 file /usr/share/mysql/german/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
11 file /usr/share/mysql/greek/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
12 file /usr/share/mysql/hungarian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
13 file /usr/share/mysql/italian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
14 file /usr/share/mysql/japanese/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
15 file /usr/share/mysql/korean/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
16 file /usr/share/mysql/norwegian-ny/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
17 file /usr/share/mysql/norwegian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
18 file /usr/share/mysql/polish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
19 file /usr/share/mysql/portuguese/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
20 file /usr/share/mysql/romanian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
21 file /usr/share/mysql/russian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
22 file /usr/share/mysql/serbian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
23 file /usr/share/mysql/slovak/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
24 file /usr/share/mysql/spanish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
25 file /usr/share/mysql/swedish/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
26 file /usr/share/mysql/ukrainian/errmsg.sys from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
27 file /usr/share/mysql/charsets/Index.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
28 file /usr/share/mysql/charsets/armscii8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
29 file /usr/share/mysql/charsets/ascii.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
30 file /usr/share/mysql/charsets/cp1250.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
31 file /usr/share/mysql/charsets/cp1251.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
32 file /usr/share/mysql/charsets/cp1256.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
33 file /usr/share/mysql/charsets/cp1257.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
34 file /usr/share/mysql/charsets/cp850.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
35 file /usr/share/mysql/charsets/cp852.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
36 file /usr/share/mysql/charsets/cp866.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
37 file /usr/share/mysql/charsets/dec8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
38 file /usr/share/mysql/charsets/geostd8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
39 file /usr/share/mysql/charsets/greek.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
40 file /usr/share/mysql/charsets/hebrew.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
41 file /usr/share/mysql/charsets/hp8.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
42 file /usr/share/mysql/charsets/keybcs2.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
43 file /usr/share/mysql/charsets/koi8r.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
44 file /usr/share/mysql/charsets/koi8u.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
45 file /usr/share/mysql/charsets/latin1.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
46 file /usr/share/mysql/charsets/latin2.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
47 file /usr/share/mysql/charsets/latin5.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
48 file /usr/share/mysql/charsets/latin7.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
49 file /usr/share/mysql/charsets/macce.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
50 file /usr/share/mysql/charsets/macroman.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
51 file /usr/share/mysql/charsets/swe7.xml from install of mysql-community-common-5.7.30-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
52 [root@k8s-node3 package]# rpm -ivh mysql-community-libs-5.7.30-1.el7.x86_64.rpm
53 warning: mysql-community-libs-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
54 error: Failed dependencies:
55 mysql-community-common(x86-64) >= 5.7.9 is needed by mysql-community-libs-5.7.30-1.el7.x86_64
56 mariadb-libs is obsoleted by mysql-community-libs-5.7.30-1.el7.x86_64
57 [root@k8s-node3 package]# rpm -ivh mysql-community-server-5.7.30-1.el7.x86_64.rpm
58 warning: mysql-community-server-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
59 error: Failed dependencies:
60 mysql-community-client(x86-64) >= 5.7.9 is needed by mysql-community-server-5.7.30-1.el7.x86_64
61 mysql-community-common(x86-64) = 5.7.30-1.el7 is needed by mysql-community-server-5.7.30-1.el7.x86_64
62 [root@k8s-node3 package]# rpm -ivh mysql-community-client-5.7.30-1.el7.x86_64.rpm
63 warning: mysql-community-client-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
64 error: Failed dependencies:
65 mysql-community-libs(x86-64) >= 5.7.9 is needed by mysql-community-client-5.7.30-1.el7.x86_64
66 [root@k8s-node3 package]#
在安装 mysql-community-libs-5.7.30-1.el7.x86_64.rpm 时有可能会报错，这个是mysql依赖错误，解决方法是：清除yum里所有mysql依赖包。

1 [root@k8s-node3 package]# rpm -qa|grep mysql
2 [root@k8s-node3 package]# yum remove mysql-libs
3 Loaded plugins: fastestmirror, langpacks, product-id, search-disabled-repos, subscription-manager
4
5 This system is not registered with an entitlement server. You can use subscription-manager to register.
6
7 Resolving Dependencies
8 --> Running transaction check
9 ---> Package mariadb-libs.x86_64 1:5.5.60-1.el7_5 will be erased
10 --> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
11 --> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
12 --> Running transaction check
13 ---> Package postfix.x86_64 2:2.10.1-7.el7 will be erased
14 --> Processing Dependency: /usr/sbin/sendmail for package: redhat-lsb-core-4.1-27.el7.centos.1.x86_64
15 --> Restarting Dependency Resolution with new changes.
16 --> Running transaction check
17 ---> Package redhat-lsb-core.x86_64 0:4.1-27.el7.centos.1 will be erased
18 --> Finished Dependency Resolution
19 base/7/x86_64 | 3.6 kB 00:00:00
20 centos-gluster7/7/x86_64 | 3.0 kB 00:00:00
21 extras/7/x86_64 | 2.9 kB 00:00:00
22 extras/7/x86_64/primary_db | 205 kB 00:00:00
23 updates/7/x86_64 | 2.9 kB 00:00:00
24 updates/7/x86_64/primary_db | 3.0 MB 00:00:00
25
26 Dependencies Resolved
27
28 ================================================================================================================================
29 Package Arch Version Repository Size
30 ================================================================================================================================
31 Removing:
32 mariadb-libs x86_64 1:5.5.60-1.el7_5 @anaconda 4.4 M
33 Removing for dependencies:
34 postfix x86_64 2:2.10.1-7.el7 @anaconda 12 M
35 redhat-lsb-core x86_64 4.1-27.el7.centos.1 @base 45 k
36
37 Transaction Summary
38 ================================================================================================================================
39 Remove 1 Package (+2 Dependent packages)
40
41 Installed size: 17 M
42 Is this ok [y/N]: y
43 Downloading packages:
44 Running transaction check
45 Running transaction test
46 Transaction test succeeded
47 Running transaction
48 Erasing : redhat-lsb-core-4.1-27.el7.centos.1.x86_64 1/3
49 Erasing : 2:postfix-2.10.1-7.el7.x86_64 2/3
50 Erasing : 1:mariadb-libs-5.5.60-1.el7_5.x86_64 3/3
51 Verifying : redhat-lsb-core-4.1-27.el7.centos.1.x86_64 1/3
52 Verifying : 2:postfix-2.10.1-7.el7.x86_64 2/3
53 Verifying : 1:mariadb-libs-5.5.60-1.el7_5.x86_64 3/3
54
55 Removed:
56 mariadb-libs.x86_64 1:5.5.60-1.el7_5
57
58 Dependency Removed:
59 postfix.x86_64 2:2.10.1-7.el7 redhat-lsb-core.x86_64 0:4.1-27.el7.centos.1
60
61 Complete!
62 [root@k8s-node3 package]#
注意：有的系统可能不太一样，没有mysql-libs,而是mariadb-libs,此时要移除的则是mariadb-libs。

1 [root@k8s-node3 package]# rpm -qa|grep mariadb
2 [root@k8s-node3 package]# yum remove mariadb-libs
清除完yum里所有mysql的依赖包后，再次安装mysql组件就不会报错了，如下所示：

1 [root@k8s-node3 package]# ls
2 mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar mysql-community-libs-5.7.30-1.el7.x86_64.rpm
3 mysql-community-client-5.7.30-1.el7.x86_64.rpm mysql-community-server-5.7.30-1.el7.x86_64.rpm
4 mysql-community-common-5.7.30-1.el7.x86_64.rpm
5 [root@k8s-node3 package]# rpm -ivh mysql-community-server-5.7.30-1.el7.x86_64.rpm --nodeps --force
6 warning: mysql-community-server-5.7.30-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
7 Preparing... ################################# [100%]
8 Updating / installing...
9 1:mysql-community-server-5.7.30-1.e################################# [100%]
10 [root@k8s-node3 package]#
注意：执行 yum remove mysql-libs命令后，会自动删除掉 /etc/ 下的 my.cnf 文件，对于安装mysql组件，只有安装了 mysql-community-server-5.7.30-1.el7.x86_64.rpm 组件，才会在 /etc/下生成 my.cnf 文件和 my.cnf.d 文件夹。

1 [root@k8s-node3 package]# ls /etc/my.cnf
2 /etc/my.cnf
3 [root@k8s-node3 package]# ls /etc/my.cnf.d/
4 [root@k8s-node3 package]# ls /etc/my*
5 /etc/my.cnf
6
7 /etc/my.cnf.d:
8 [root@k8s-node3 package]#
在/var/lib/下可以看到以下三个文件夹mysql、mysql-files、mysql-keyring，如下所示：

1 [root@k8s-node3 package]# cd /var/lib/
2 [root@k8s-node3 lib]# ls
3 AccountsService color flatpak glusterd lldpad mysql-keyring polkit-1 rpm-state tpm yum
4 alsa colord fprint gssproxy logrotate net-snmp postfix rsyslog tuned
5 alternatives containers fwupd hyperv machines NetworkManager pulse samba udisks2
6 authconfig dbus fwupdate initramfs misc nfs rasdaemon selinux unbound
7 bluetooth dhclient games iscsi mlocate os-prober rhsm setroubleshoot upower
8 boltd dnsmasq gdm kubelet mysql PackageKit rpcbind stateless vmware
9 chrony docker geoclue libvirt mysql-files plymouth rpm systemd xkb
10 [root@k8s-node3 lib]# ls mysql mysql-
11 mysql-files/ mysql-keyring/
12 [root@k8s-node3 lib]# ls mysql mysql-
13 mysql-files/ mysql-keyring/
14 [root@k8s-node3 lib]# ls mysql mysql-files/ mysql-keyring/
15 mysql:
16
17 mysql-files/:
18
19 mysql-keyring/:
20 [root@k8s-node3 lib]#
在/var/log/ 下可以看到 mysqld.log 文件，如下所示：

1 [root@k8s-node3 lib]# cd /var/lo
2 local/ lock/ log/
3 [root@k8s-node3 lib]# cd /var/log/
4 [root@k8s-node3 log]# ls
5 anaconda cron libvirt qemu-ga spooler-20200714 vmware-vgauthsvc.log.0
6 audit cron-20200618 maillog rhsm swtpm vmware-vmsvc.log
7 boot.log cron-20200625 maillog-20200618 sa tallylog vmware-vmusr.log
8 boot.log-20200618 cron-20200628 maillog-20200625 samba tuned wpa_supplicant.log
9 boot.log-20200622 cron-20200714 maillog-20200628 secure vmware-network.1.log wtmp
10 boot.log-20200629 cups maillog-20200714 secure-20200618 vmware-network.2.log Xorg.0.log
11 boot.log-20200630 dmesg messages secure-20200625 vmware-network.3.log Xorg.0.log.old
12 boot.log-20200702 dmesg.old messages-20200618 secure-20200628 vmware-network.4.log Xorg.9.log
13 boot.log-20200703 firewalld messages-20200625 secure-20200714 vmware-network.5.log yum.log
14 boot.log-20200714 gdm messages-20200628 speech-dispatcher vmware-network.6.log
15 btmp gitlab messages-20200714 spooler vmware-network.7.log
16 btmp-20200702 glusterfs mysqld.log spooler-20200618 vmware-network.8.log
17 chrony grubby_prune_debug pluto spooler-20200625 vmware-network.9.log
18 containers lastlog ppp spooler-20200628 vmware-network.log
19 [root@k8s-node3 log]# ll mysqld.log
20 -rw-r----- 1 mysql mysql 0 Jul 15 14:17 mysqld.log
21 [root@k8s-node3 log]#
在/var/run/ 下可以看到 mysqld 目录，如下所示：

1 [root@k8s-node3 log]# cd /var/run/
2 [root@k8s-node3 run]# ls
3 abrt cups gitlab lock NetworkManager setrans udev
4 alsactl.pid dbus gluster log plymouth setroubleshoot udisks2
5 atd.pid dmeventd-client glusterd.pid lsm ppp sm-notify.pid user
6 auditd.pid dmeventd-server glusterd.socket lvm radvd spice-vdagentd utmp
7 avahi-daemon docker gssproxy.pid lvmetad.pid rhsm sshd.pid vmware
8 blkid docker.pid gssproxy.sock mcelog-client rpcbind sudo xtables.lock
9 console docker.sock initramfs mcelog.pid rpcbind.lock sysconfig
10 container-storage-setup ebtables.lock ksmtune.pid mdadm rpcbind.sock syslogd.pid
11 crond.pid faillock kubernetes mount runc systemd
12 cron.reboot flannel libvirt mysqld samba tmpfiles.d
13 cryptsetup gdm libvirtd.pid netreport sepermit tuned
14 [root@k8s-node3 run]# ls mysqld/
15 [root@k8s-node3 run]# ll mysqld/
16 total 0
17 [root@k8s-node3 run]#

3、登录并创建 mysql 密码，启动mysql，安装完后，使用命令 service mysqld start 或 systemctl start mysqld.service 启动MySQL服务，如果mysql服务无法启动，就重启一下系统。

1 [root@k8s-node3 ~]# systemctl start mysqld.service
2 [root@k8s-node3 ~]# systemctl status mysqld.service
3 ● mysqld.service - MySQL Server
4 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
5 Active: active (running) since Wed 2020-07-15 14:23:18 CST; 11s ago
6 Docs: man:mysqld(8)
7 http://dev.mysql.com/doc/refman/en/using-systemd.html
8 Process: 129166 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
9 Process: 129095 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
10 Main PID: 129169 (mysqld)
11 Tasks: 27
12 Memory: 259.3M
13 CGroup: /system.slice/mysqld.service
14 └─129169 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
15
16 Jul 15 14:23:12 k8s-node3 systemd[1]: Starting MySQL Server...
17 Jul 15 14:23:18 k8s-node3 systemd[1]: Started MySQL Server.
18 [root@k8s-node3 ~]#
启动、停止、查看状态命令如下所示：

1 systemctl start mysqld.service　　 # 此命令是启动mysql
2 systemctl status mysqld.service # 此命令是查看mysql状态
3 systemctl stop mysqld.service # 此命令是关闭mysql
查看mysql进程 ps -ef | grep mysql，查看3306端口 netstat -anop | grep 3306。

1 [root@k8s-node3 ~]# ps -ef | grep mysql
2 mysql 129169 1 0 14:23 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
3 root 129514 121741 0 14:25 pts/2 00:00:00 grep --color=auto mysql
4 [root@k8s-node3 ~]# netstat -nultp | grep 3306
5 tcp6 0 0 :::3306 :::* LISTEN 129169/mysqld
6 [root@k8s-node3 ~]#
登陆mysql修改root密码，由于MySQL5.7.4之前的版本中默认是没有密码的，登录后直接回车就可以进入数据库，进而进行设置密码等操作。其后版本对密码等安全相关操作进行了一些改变，在安装过程中，会在安装日志中生成一个临时密码。使用命令grep 'temporary password' /var/log/mysqld.log找到这个临时密码。

1 [root@k8s-node3 ~]# grep 'temporary password' /var/log/mysqld.log
2 2020-07-15T06:23:15.406927Z 1 [Note] A temporary password is generated for root@localhost: OUvw#frSq7iI
3 [root@k8s-node3 ~]# mysql -uroot -p
4 Enter password:
5 Welcome to the MySQL monitor. Commands end with ; or \g.
6 Your MySQL connection id is 2
7 Server version: 5.7.30
8
9 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
10
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14
15 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16
17 mysql> set password for root@localhost=password('123456');
18 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
19 mysql> set global validate_password_policy=0; # 此时，新密码长度大于等于8位才有效，否则报错
20 Query OK, 0 rows affected (0.00 sec)
21
22 mysql> set global validate_password_length=1; # 修改有效密码长度。
23 Query OK, 0 rows affected (0.00 sec)
24
25 mysql> set password for root@localhost=password('123456');
26 Query OK, 0 rows affected, 1 warning (0.01 sec)
27
28 mysql>
执行下面的命令set password for root@localhost=password('123456');修改MySql root密码，但是在5.6后,mysql内置密码增强机制,低强度密码会报错:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements。

1 第一步是，更改策略，设置 validate_password_policy=0;
2 mysql> set global validate_password_policy=0; # 此时，新密码长度大于等于8位才有效，否则报错
3
4 然后修改有效密码长度：
5 mysql> set global validate_password_length=1;
6 不管设置 validate_password_length=1,还是2,3,4 ,"使密码长度生效"这个参数的实际值都是4。超过4后设置是多少实际就是多少。
7
8 第二步就可以重新设置密码了，如下所示：
9 mysql> set password for root@localhost=password('123456');
10 Query OK, 0 rows affected, 1 warning (0.01 sec)
使用修改之后的密码就可以顺利登录，如下所示：

查看当前授予过的权限，如下所示：

1 [root@k8s-node3 ~]# mysql -uroot -p123456
2 mysql: [Warning] Using a password on the command line interface can be insecure.
3 Welcome to the MySQL monitor. Commands end with ; or \g.
4 Your MySQL connection id is 3
5 Server version: 5.7.30 MySQL Community Server (GPL)
6
7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
8
9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15 mysql> use mysql;
16 Reading table information for completion of table and column names
17 You can turn off this feature to get a quicker startup with -A
18
19 Database changed
20 mysql> select user,host from user;
21 +---------------+-----------+
22 | user | host |
23 +---------------+-----------+
24 | mysql.session | localhost |
25 | mysql.sys | localhost |
26 | root | localhost |
27 +---------------+-----------+
28 3 rows in set (0.00 sec)
29
30 mysql> grant all privileges on *.* to root@'%' identified by '123456'; # 授予root用户远程访问权限。
31 Query OK, 0 rows affected, 1 warning (0.00 sec)
32
33 mysql> flush privileges; # 刷新权限，使设置生效。
34 Query OK, 0 rows affected (0.00 sec)
35
36 mysql>
现在可以远程链接服务器上面的mysql数据库，如下所示：

4、验证mysql安装成功，如下所示：

1 [root@k8s-node3 ~]# mysqladmin --version
2 mysqladmin Ver 8.42 Distrib 5.7.30, for Linux on x86_64
3 [root@k8s-node3 ~]#

查看mysql检查是否开机自启动，服务前面存在*的是开机自启动的，没有*的是开机不自启动的，如下所示：

1 [root@k8s-node3 ~]# ntsysv
2
3 ntsysv 1.7.4 - (C) 2000-2001 Red Hat, Inc.
4
5
6 ─┐
7 │
8 What services should be automatically started? │
9 │
10 [*] mdmonitor.service ↑ │
11 [*] microcode.service ? │
12 [*] multipathd.service ? │
13 [*] mysqld.service ? │
14 [ ] ndctl-monitor.service ? │
15 [ ] netcf-transaction.service ? │
16 [ ] nfs-blkmap.service ? │
17 [ ] nfs-rquotad.service ? │
18 [ ] nfs-server.service ? │
19 [ ] nfs.service ↓ │
20 │
21 ┌────┐ ┌────────┐ │
22 │ Ok │ │ Cancel │ │
23 └────┘ └────────┘ │
24 │
25 │ │
26 └──────────────────────────────────────────────────┘
27
28
29 Press <F1> for more information on a service.

mysql的核心目录，如下所示：

1 1）、/var/lib/mysql，mysql的安装目录。
2 2）、/usr/share/mysql/，mysql的配置文件。
3 3）、/usr/bin/，mysql的命令目录。
4
5
6 [root@k8s-node3 ~]# ls /var/lib/mysql
7 auto.cnf client-cert.pem ibdata1 ibtmp1 mysql.sock.lock public_key.pem sys
8 ca-key.pem client-key.pem ib_logfile0 mysql performance_schema server-cert.pem
9 ca.pem ib_buffer_pool ib_logfile1 mysql.sock private_key.pem server-key.pem
10 [root@k8s-node3 ~]# ls /usr/share/mysql/
11 bulgarian french mysql-log-rotate romanian
12 charsets german mysql_security_commands.sql russian
13 czech greek mysql_sys_schema.sql serbian
14 danish hungarian mysql_system_tables_data.sql slovak
15 dictionary.txt innodb_memcached_config.sql mysql_system_tables.sql spanish
16 dutch install_rewriter.sql mysql_test_data_timezone.sql swedish
17 english italian norwegian ukrainian
18 errmsg-utf8.txt japanese norwegian-ny uninstall_rewriter.sql
19 estonian korean polish
20 fill_help_tables.sql magic portuguese
21 [root@k8s-node3 ~]# ls /usr/bin/

5、查看Mysql的字符集编码，如下所示：

1 [root@k8s-node3 ~]# mysql -uroot -p123456
2 mysql: [Warning] Using a password on the command line interface can be insecure.
3 Welcome to the MySQL monitor. Commands end with ; or \g.
4 Your MySQL connection id is 4
5 Server version: 5.7.30 MySQL Community Server (GPL)
6
7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
8
9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15 mysql> show variables like '%char%';
16 +--------------------------------------+----------------------------+
17 | Variable_name | Value |
18 +--------------------------------------+----------------------------+
19 | character_set_client | utf8 |
20 | character_set_connection | utf8 |
21 | character_set_database | latin1 |
22 | character_set_filesystem | binary |
23 | character_set_results | utf8 |
24 | character_set_server | latin1 |
25 | character_set_system | utf8 |
26 | character_sets_dir | /usr/share/mysql/charsets/ |
27 | validate_password_special_char_count | 1 |
28 +--------------------------------------+----------------------------+
29 9 rows in set (0.00 sec)
30
31 mysql>
设置字符集编码为utf8格式的，如下所示：

1 # For advice on how to change settings please see
2 # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
3
4 [mysqld]
5 init_connect='SET NAMES utf8'
6 character-set-server=utf8
7 # character_set_client=utf8
8 collation-server=utf8_general_ci
9 #
10 # Remove leading # and set to the amount of RAM for the most important data
11 # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
12 # innodb_buffer_pool_size = 128M
13 #
14 # Remove leading # to turn on a very important data integrity option: logging
15 # changes to the binary log between backups.
16 # log_bin
17 #
18 # Remove leading # to set options mainly useful for reporting servers.
19 # The server defaults are faster for transactions and fast SELECTs.
20 # Adjust sizes as needed, experiment to find the optimal values.
21 # join_buffer_size = 128M
22 # sort_buffer_size = 2M
23 # read_rnd_buffer_size = 2M
24 datadir=/var/lib/mysql
25 socket=/var/lib/mysql/mysql.sock
26
27 # Disabling symbolic-links is recommended to prevent assorted security risks
28 symbolic-links=0
29
30 log-error=/var/log/mysqld.log
31 pid-file=/var/run/mysqld/mysqld.pid
32
33 [mysql]
34 default-character-set=utf8
35
36 [mysql_safe]
37 default-character-set=utf8
38 "my.cnf" 41L, 1192C
修改内容，如下所示：

此时，需要重启mysql的，如下所示：

1 [root@k8s-node3 etc]# systemctl restart mysqld.service
2 [root@k8s-node3 etc]# systemctl status mysqld.service
3 ● mysqld.service - MySQL Server
4 Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
5 Active: active (running) since Wed 2020-07-15 18:49:25 CST; 2s ago
6 Docs: man:mysqld(8)
7 http://dev.mysql.com/doc/refman/en/using-systemd.html
8 Process: 26346 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
9 Process: 26322 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
10 Main PID: 26349 (mysqld)
11 Tasks: 27
12 Memory: 218.8M
13 CGroup: /system.slice/mysqld.service
14 └─26349 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
15
16 Jul 15 18:49:23 k8s-node3 systemd[1]: Starting MySQL Server...
17 Jul 15 18:49:25 k8s-node3 systemd[1]: Started MySQL Server.
再次查看字符集编码，如下所示：

1 mysql> SHOW VARIABLES LIKE 'character_set_%';
2 +--------------------------+----------------------------+
3 | Variable_name | Value |
4 +--------------------------+----------------------------+
5 | character_set_client | utf8 |
6 | character_set_connection | utf8 |
7 | character_set_database | utf8 |
8 | character_set_filesystem | binary |
9 | character_set_results | utf8 |
10 | character_set_server | utf8 |
11 | character_set_system | utf8 |
12 | character_sets_dir | /usr/share/mysql/charsets/ |
13 +--------------------------+----------------------------+
14 8 rows in set (0.01 sec)
15
16 mysql> SHOW VARIABLES LIKE 'collation_%';
17 +----------------------+-----------------+
18 | Variable_name | Value |
19 +----------------------+-----------------+
20 | collation_connection | utf8_general_ci |
21 | collation_database | utf8_general_ci |
22 | collation_server | utf8_general_ci |
23 +----------------------+-----------------+
24 3 rows in set (0.01 sec)
25
26 mysql>
注意，修改字符集编码，只对后面创建的数据库生效，之前创建的数据库字符集还是保持之前的不发生变化。

6、Mysql的命令行清屏命令system clear或者ctrl + L命令，如下所示：

1 mysql> system clear
2 mysql>
mysql的逻辑分层，分别是连接层、服务层、引擎层、存储层，如下所示：

查看mysql的存储引擎，首先查看mysql支持的引擎，如下所示：

1 [root@k8s-node3 ~]# mysql -uroot -p123456
2 mysql: [Warning] Using a password on the command line interface can be insecure.
3 Welcome to the MySQL monitor. Commands end with ; or \g.
4 Your MySQL connection id is 5
5 Server version: 5.7.30 MySQL Community Server (GPL)
6
7 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
8
9 Oracle is a registered trademark of Oracle Corporation and/or its
10 affiliates. Other names may be trademarks of their respective
11 owners.
12
13 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15 mysql> show engines;
16 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
17 | Engine | Support | Comment | Transactions | XA | Savepoints |
18 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
19 | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
20 | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
21 | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
22 | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
23 | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
24 | CSV | YES | CSV storage engine | NO | NO | NO |
25 | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
26 | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
27 | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
28 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
29 9 rows in set (0.01 sec)
30
31 mysql>
查看当前Mysql的存储引擎，如下所示：

1 mysql> show create table user_info;
2 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 | Table | Create Table |
4 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 | user_info | CREATE TABLE `user_info` (
6 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
7 `user_account` varchar(15) DEFAULT NULL COMMENT '用户账号',
8 `user_pw` varchar(15) DEFAULT NULL COMMENT '用户密码',
9 `user_number` varchar(15) DEFAULT NULL COMMENT '用户手机号',
10 `user_name` varchar(10) DEFAULT NULL COMMENT '用户姓名',
11 `user_age` int(11) DEFAULT NULL COMMENT '用户年龄',
12 `user_sex` varchar(5) DEFAULT NULL COMMENT '用户性别',
13 `user_mark` varchar(5) DEFAULT NULL COMMENT '用户标志',
14 `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
15 `is_sync` int(3) DEFAULT '0' COMMENT '用户标识',
16 `is_money` varchar(255) DEFAULT NULL COMMENT '是否缴纳押金',
17 PRIMARY KEY (`user_id`) USING BTREE,
18 KEY `user_id` (`user_id`) USING BTREE
19 ) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户信息表' |
20 +-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
21 1 row in set (0.00 sec)
22
23 mysql>

7、mysql的sql优化，原因可能是性能低，执行时间太长，等待时间太长，sql语句欠佳，比如连接查询，索引失效，服务器参数设置不佳。

Sql的编写过程、解析过程，如下所示：

1 编写过程：select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
2 解析过程：from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
　　SQL优化，主要就是在于优化索引，索引相当于是书的目录。索引，index是帮助Mysql高效获取数据的数据结构，索引是数据结构（树，常见的B树，默认是B树，Hash树）。

　　索引的弊端，索引本身很大，可以存放在内存或者硬盘中，通常为硬盘。索引不是所有情况均适用的，如果是少量数据不适用。如果是频繁更新的字段不适用。很少使用的列也不适合添加索引。索引会降低增删改的效率，提高查询的效率。

　　索引的优点，提高查询效率，降低IO使用率，降低CPU使用率，因为B树索引本事就是一个排好序的结构，因此在排序的时候，可以直接使用的。

8、mysql如何进行优化呢，查看使用索引的情况，查询执行计划，explain + SQL语句。

1 id ：编号
2 select_type ：查询类型
3 table ：数据表，指的查询的那张表。
4 partitions ：
5 type ：类型
6 possible_keys ：预测使用到的索引
7 key ：实际使用的索引
8 key_len ：实际使用索引的长度
9 ref ：表之间的引用
10 rows ：通过索引查询到的数据量
11 filtered ：
12 Extra ：额外的信息
8.1、id编号的用法，多表关联查询（备注：多表联查都可以转换为子查询得到想要的结果）特点：

　　1）、id编号值相同，数据小的表，优先查询。

　　2）、如果是id编号值不同，id编号值越大越优先查询（执行本质，在嵌套子查询的时候，先查询内层，再查询外层）。

　　3）、id值有相同，又有不同，id值越大越优先。id值相同从上往下，顺序执行。这里的id编号指定的是explain的查询结果字段id哦。

1 mysql> explain
2 -> select * from book_info bi,book_sort bs WHERE bi.book_sort_id = bs.book_sort_id and bi.book_name = 'Java';
3 +----+-------------+-------+------------+--------+------------------------+-----------+---------+----------------------+------+----------+-------------+
4 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
5 +----+-------------+-------+------------+--------+------------------------+-----------+---------+----------------------+------+----------+-------------+
6 | 1 | SIMPLE | bi | NULL | ref | book_sort_id,book_name | book_name | 48 | const | 1 | 100.00 | Using where |
7 | 1 | SIMPLE | bs | NULL | eq_ref | PRIMARY | PRIMARY | 4 | book.bi.book_sort_id | 1 | 100.00 | NULL |
8 +----+-------------+-------+------------+--------+------------------------+-----------+---------+----------------------+------+----------+-------------+
9 2 rows in set, 1 warning (0.00 sec)
10
11 mysql>
1 mysql> explain
2 -> select * from book_info bi WHERE bi.book_sort_id = (SELECT book_sort_id from book_sort bs WHERE bs.book_sort_name = '自然科学总论')
3 -> ;
4 +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
5 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
6 +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
7 | 1 | PRIMARY | bi | NULL | ref | book_sort_id | book_sort_id | 5 | const | 2 | 100.00 | Using where |
8 | 2 | SUBQUERY | bs | NULL | ALL | NULL | NULL | NULL | NULL | 21 | 10.00 | Using where |
9 +----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+
10 2 rows in set, 1 warning (0.00 sec)
11
12 mysql>
8.2）、select_type查询类型的用法，如下所示：

　　1）、PRIMARY：包含子查询SQL中的主查询，一般是SQL嵌套的最外层。
　　2）、SUBQUERY：包含子查询SQL中的子查询，一般是SQL嵌套的非最外层。
　　3）、SIMPLE：简单查询，不包含子查询，不包含union查询。
　　4）、DERIVED：衍生查询。在查询的时候使用到了临时表。两种情况，第一种是from子查询中只有一张表。第二种是在from子查询中，如果有两张表table1 union table2连接查询，则table1这个左表就是衍生查询。

8.3）、type成为索引类型，或者称为类型。system > const > eq_ref > ref > index > all，其中性能由左向右依次降低，但是system、const只是理想状态，实际可以达到的一般是ref、range。

　　1）、system，只有一条数据的系统表，基本达不到，或者衍生表只有一条数据的主查询，偶尔可以达到。
　　2）、const，仅仅可以查询到一条数据的sql，并且用于Primary key或者unique索引的时候就是const。必须针对Primary key或者unique索引的时候，与索引类型有关。
　　3）、eq_ref，唯一性索引，对于每个索引键的查询，返回匹配唯一行数据，唯一的意思是有且只有一个，不能多也不能为0。常见于唯一索引和主键索引。
　　4）、ref，非唯一性索引，对于每个索引键的查询，返回匹配的所有行（可以是0个或者多个）。
　　5）、range，检索指定范围的行，具体就是where后面是一个范围查询，比如between and、in、>、<等等。特殊的in有时候会失效，从而转换为无索引All，即in范围查询会导致索引失效，可以将in范围查询放到字段查询的后面。
　　6）、index，查询全部索引的数据。只需要扫描索引表，不需要扫描全部数据。
　　7）、all，查询全部表中数据。需要扫描全表，即需要所有表中的所有数据。

8.4）、possible_keys，可能用到的索引，是一种预测，不准。

8.5）、key，实际使用到的索引。如果possible_keys或者key为NULL，说明没有使用到索引。

　　1）、utf8中，一个字符占用三个字节。如果索引字段可以为null，则会使用1个字节用于标识。如果是固定长度char就是char(n)中的n乘以三，如果可以为null，再加一即可。

　　2）、utf8中，一个字符占用三个字节。如果索引字段可以为null，则会使用1个字节用于标识。如果是可变长度varchar，就是varchar(n)中的n乘以三，如果可以为null，再加一，然后varhar是可变长度，然后再加二，用两个字节来表示可变长度。比如book_name是varchar(15)，15 * 3 + 1 + 2 = 48。如果是复合索引，book_type是varchar(200)，组合book_name是varchar(15)，就是200 * 3 + 1 + 2 + 15 * 3 + 1 + 2 = 651。

8.7）、ref，注意与type中的ref值进行区分，作用是指明当前表所参照的字段。如果是常量，值就是const。

8.8）、rows，被索引优化查询的数据个数。

　　1）、using filesort：表示性能低，消耗比较大，当前的sql语句需要额外的一次排序查询。理解起来就是先根据这个字段查询，然后根据这个字段排序。如果是先根据一个字段查询，根据另外一个字段排序，那么当前的sql语句需要额外的一次排序查询。常见于order by语句中。

　　对于复合索引，不能跨列（最佳左前缀，即从左到右依次使用，不能跨列使用，比如三个字段的索引，不能跨过第二个索引）。where和order by按照复合索引的顺序使用，不要跨列或者无序使用。

　　3）、Using index：表示性能提升了，意思是索引覆盖，原因在于不读取原文件，只从索引文件中获取数据，不需要回表查询。只要使用到的列全部都在索引中，就是索引覆盖Using index。只要使用到的列不都在索引中，就不会出现索引覆盖Using index。

9、mysql的单表索引优化案例，如下所示：

1 SET NAMES utf8mb4;
2 SET FOREIGN_KEY_CHECKS = 0;
3
4 -- ----------------------------
5 -- Table structure for user_info
6 -- ----------------------------
7 DROP TABLE IF EXISTS `user_info`;
8 CREATE TABLE `user_info` (
9 `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
10 `user_account` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户账号',
11 `user_pw` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户密码',
12 `user_number` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户手机号',
13 `user_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户姓名',
14 `user_age` int(11) DEFAULT NULL COMMENT '用户年龄',
15 `user_sex` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户性别',
16 `user_mark` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户标志',
17 `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '用户创建时间',
18 `is_sync` int(3) DEFAULT 0 COMMENT '用户标识',
19 `is_money` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '是否缴纳押金',
20 PRIMARY KEY (`user_id`) USING BTREE
21 ) ENGINE = InnoDB AUTO_INCREMENT = 42 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;
22
23 SET FOREIGN_KEY_CHECKS = 1;
索引一旦进行升级优化，需要将之前废弃的索引删除掉，防止干扰。

首先进行需求查询，如下所示：

1 编写过程：select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
2 解析过程：from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
如果索引顺序是这样的`user_age`, `user_name`, `user_account`，那么查询的时候是使用了索引的，符合sql的解析过程，首先是where后面的字段，然后才是select后面的字段。

如果索引顺序是这样的`user_account`, `user_age`, `user_name`，也使用到了索引，但是进行了Using filesort，查询行数rows由2变成了21，效率下降了。

　　此案例中同时出现了Using where需要回原表查询，Using index不需要回原表查询，是因为in范围查询可能会导致索引失效就需要回原表进行查询了，如果where后面的字段在索引中就不需要回原表，可以在索引表中查询到。

10、mysql的双表索引优化案例，如下所示：

1 SET NAMES utf8mb4;
2 SET FOREIGN_KEY_CHECKS = 0;
3
4 -- ----------------------------
5 -- Table structure for book_info
6 -- ----------------------------
7 DROP TABLE IF EXISTS `book_info`;
8 CREATE TABLE `book_info` (
9 `book_id` int(11) NOT NULL COMMENT '图书编号',
10 `book_sort_id` int(11) DEFAULT NULL COMMENT '图书类型编号',
11 `book_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书名称',
12 `book_author` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书作者',
13 `book_price` decimal(5, 2) DEFAULT NULL COMMENT '图书价格',
14 `book_type` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类型',
15 `book_publish` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书出版社',
16 `book_sum` int(255) DEFAULT NULL COMMENT '图书数量',
17 `book_mark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书标识',
18 `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '图书创建时间',
19 `book_money` decimal(5, 2) DEFAULT NULL COMMENT '图书租金',
20 `book_desc` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '图书说明'
21 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书信息表' ROW_FORMAT = Dynamic;
22
23 -- ----------------------------
24 -- Table structure for book_sort
25 -- ----------------------------
26 DROP TABLE IF EXISTS `book_sort`;
27 CREATE TABLE `book_sort` (
28 `book_sort_id` int(11) NOT NULL COMMENT '图书类别编号',
29 `book_sort_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类别名称',
30 `book_sort_extend` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类别扩展信息',
31 `book_sort_mark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '图书类别标识',
32 `create_time` datetime(0) DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
33 ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '图书类别表' ROW_FORMAT = Dynamic;
34
35 SET FOREIGN_KEY_CHECKS = 1;
两张表组合查询，进行左外关联，如下所示：

Using join buffer (Block Nested Loop)，extra中的一个选项，作用就是mysql的引擎使用了连接缓存，其实就是你的sql太差了，mysql给你添加了一个缓存。

此时可以将左表的查询字段添加上索引，增加查询速度。

11、避免mysql索引失效的一些原则。sql优化，是一种概率层面的优化，至于是否实际使用了我们的优化，需要通过explain进行推测。索引优化，是一个大部分情况适用的结论，但是由于SQL优化器等原因，该结论不是100%正确的。

　　1）、复合索引的时候不要跨列或者无序使用（最佳左前缀原则）。复合索引，尽量使用全索引匹配。对于复合索引，如果前面的字段索引失效，后面的字段索引就失效了。
　　2）、不要在索引上进行任何操作（进行计算，函数，类型转换），否则索引失效。
　　3）、复合索引不能使用不等于（!= 或者 <>）或者is null（is not null），否则自身以及右侧所有全部失效。复合索引中如果有>大于号，则自身和右侧索引全部失效。一般而言，范围查询>、<、in之后的索引失效。
　　4）、like尽量以"常量"开头，不要以'%'开头，可以将%加到常量的后面，否则索引失效。如果非要以%开头，可以使用索引覆盖，即模糊查询的字段作为查询出的字段，不用回表查询。
　　5）、尽量不要使用类型转换（显式转换、隐式转换），否则都会使索引失效。
　　6）、尽量不要使用or，否则索引失效。会将or左侧的索引失效了。
　　7）、对于exist和in，如果主查询的数据集大，则使用In，此时In的效率高些。如果子查询的数据集大，则使用exist，此时exist的效率高些。exist的含义就是将主查询的结果，放到子查询的结果中进行校验（看子查询是否有数据，如果有数据，则校验成功），如果复合校验，则保留数据。
　　8）、order by优化，using filesort有两种算法，双路排序、单路排序（根据IO的次数）。Mysql4.1之前默认使用的是双路排序，Mysql4.1之后使用的是单路排序。双路就是两次IO，会扫描两次磁盘，第一次从磁盘读取排序字段，对排序字段进行排序，第二次扫描其他字段，IO消耗性能。单路排序只读取一次，这一次就是全部的字段，在buffer中进行排序，但是单路排序，会有一定的隐患，不一定是真的一次IO，有可能是多次IO，如果数据量特别大，则无法将所有字段的数据一次性读取完毕，因此会进行分片读取。注意，单路排序会比双路排序占用更多的buffer，单路排序在使用的时候，如果数据量过大，可以考虑调大buffer的容量大小，set max_length_for_sort_data=1024，单位是字节。如果max_length_for_sort_data值太低，则mysql会自动从单路切换到双路，太低的条件是需要排序的列的总大小超过了max_length_for_sort_data定义的字节数。
　　总结：提高order by查询的策略，可以选择使用单路，双路，调整buffer的容量大小。避免select *的查询，只查询出需要的字段即可。复合索引，不要跨列使用，避免出现using filesort。保证全部的排序字段排序的一致性，都是升序排序或者都是降序排列。

12、mysql的慢查询日志，mysql提供的一种日志记录，用于记录mysql中响应时间超过阈值的sql语句long_query_time，默认是10秒。慢查询日志默认是关闭的，建议，在开发调优的时候打开，项目上线关闭即可。

检查是否开启了慢查询日志，如下所示：

1 mysql> show variables like '%slow_query_log%';
2 +---------------------+-----------------------------------+
3 | Variable_name | Value |
4 +---------------------+-----------------------------------+
5 | slow_query_log | OFF |
6 | slow_query_log_file | /var/lib/mysql/k8s-node3-slow.log |
7 +---------------------+-----------------------------------+
8 2 rows in set (0.00 sec)
9
10 mysql>
开启mysql的慢查询日志，临时开启，重启mysql就关闭mysql的慢查询了，在内存中开启，如下所示：

1 mysql> set global slow_query_log = 1;
2 Query OK, 0 rows affected (0.02 sec)
3
4 mysql> show variables like '%slow_query_log%';
5 +---------------------+-----------------------------------+
6 | Variable_name | Value |
7 +---------------------+-----------------------------------+
8 | slow_query_log | ON |
9 | slow_query_log_file | /var/lib/mysql/k8s-node3-slow.log |
10 +---------------------+-----------------------------------+
11 2 rows in set (0.01 sec)
12
13 mysql>
开启mysql的慢查询日志，永久开启，修改配置文件/etc/my.cnf，如下所示：

1 [mysqld]
2 slow_query_log=1
3 slow_query_log_file=/var/lib/mysql/localhost-slow.log
重启mysql，然后查看是否已经开启了慢查询，如下所示：

1 [root@k8s-node3 ~]# vim /etc/my.cnf
2 [root@k8s-node3 ~]# systemctl restart mysqld.service
3 [root@k8s-node3 ~]# mysql -uroot -h127.0.0.1 -p123456
4 mysql: [Warning] Using a password on the command line interface can be insecure.
5 Welcome to the MySQL monitor. Commands end with ; or \g.
6 Your MySQL connection id is 2
7 Server version: 5.7.30-log MySQL Community Server (GPL)
8
9 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
10
11 Oracle is a registered trademark of Oracle Corporation and/or its
12 affiliates. Other names may be trademarks of their respective
13 owners.
14
15 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
16
17 mysql> show variables like '%slow_query_log%';
18 +---------------------+-----------------------------------+
19 | Variable_name | Value |
20 +---------------------+-----------------------------------+
21 | slow_query_log | ON |
22 | slow_query_log_file | /var/lib/mysql/localhost-slow.log |
23 +---------------------+-----------------------------------+
24 2 rows in set (0.01 sec)
25
26 mysql>
查看慢查询的默认超时时间，默认超时时间是10秒钟，如下所示：

1 mysql> show variables like '%long_query_time%';
2 +-----------------+-----------+
3 | Variable_name | Value |
4 +-----------------+-----------+
5 | long_query_time | 10.000000 |
6 +-----------------+-----------+
7 1 row in set (0.00 sec)
8
9 mysql> set global long_query_time = 5.000000;
10 Query OK, 0 rows affected (0.00 sec)
11
12 mysql> show variables like '%long_query_time%';
13 +-----------------+-----------+
14 | Variable_name | Value |
15 +-----------------+-----------+
16 | long_query_time | 10.000000 |
17 +-----------------+-----------+
18 1 row in set (0.00 sec)
19
20 mysql> exit;
21 Bye
22 [root@k8s-node3 ~]# mysql -uroot -h127.0.0.1 -p123456
23 mysql: [Warning] Using a password on the command line interface can be insecure.
24 Welcome to the MySQL monitor. Commands end with ; or \g.
25 Your MySQL connection id is 3
26 Server version: 5.7.30-log MySQL Community Server (GPL)
27
28 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
29
30 Oracle is a registered trademark of Oracle Corporation and/or its
31 affiliates. Other names may be trademarks of their respective
32 owners.
33
34 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
35
36 mysql> show variables like '%long_query_time%';
37 +-----------------+----------+
38 | Variable_name | Value |
39 +-----------------+----------+
40 | long_query_time | 5.000000 |
41 +-----------------+----------+
42 1 row in set (0.00 sec)
43
44 mysql>
mysql的慢查询日志，永久超时阈值时间设置，需要重启mysql服务，如下所示：

1 [mysqld]
2 slow_query_log=1
3 slow_query_log_file=/var/lib/mysql/localhost-slow.log
4 long_query_time=3
重启mysql服务，如下所示：

1 [root@k8s-node3 ~]# systemctl restart mysqld.service
2 [root@k8s-node3 ~]# mysql -uroot -h127.0.0.1 -p123456
3 mysql: [Warning] Using a password on the command line interface can be insecure.
4 Welcome to the MySQL monitor. Commands end with ; or \g.
5 Your MySQL connection id is 2
6 Server version: 5.7.30-log MySQL Community Server (GPL)
7
8 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
9
10 Oracle is a registered trademark of Oracle Corporation and/or its
11 affiliates. Other names may be trademarks of their respective
12 owners.
13
14 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
15
16 mysql> show variables like '%long_query_time%';
17 +-----------------+----------+
18 | Variable_name | Value |
19 +-----------------+----------+
20 | long_query_time | 3.000000 |
21 +-----------------+----------+
22 1 row in set (0.00 sec)
23
24 mysql>
mysql的慢查询日志，查看慢查询的条数，如下所示：

1 [root@k8s-node3 ~]# cat /var/lib/mysql/localhost-slow.log
2 /usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
3 Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
4 Time Id Command Argument
5 /usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
6 Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
7 Time Id Command Argument
8 # Time: 2020-07-19T13:24:44.111649Z
9 # User@Host: root[root] @ localhost [127.0.0.1] Id: 2
10 # Query_time: 4.003324 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
11 SET timestamp=1595165084;
12 select sleep(4);
13 [root@k8s-node3 ~]#
也可以通过mysqldumpslow工具查看慢sql，此是mysql自带的工具，s代表了排序方式，r代表了逆序，l代表了锁定时间，g代表了正则匹配模式，如下所示：

1 [root@k8s-node3 ~]# mysqldumpslow --help
2 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
3
4 Parse and summarize the MySQL slow query log. Options are
5
6 --verbose verbose
7 --debug debug
8 --help write this text to standard output
9
10 -v verbose
11 -d debug
12 -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
13 al: average lock time
14 ar: average rows sent
15 at: average query time
16 c: count
17 l: lock time
18 r: rows sent
19 t: query time
20 -r reverse the sort order (largest last instead of first)
21 -t NUM just show the top n queries
22 -a don't abstract all numbers to N and strings to 'S'
23 -n NUM abstract numbers with at least n digits within names
24 -g PATTERN grep: only consider stmts that include this string
25 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
26 default is '*', i.e. match all
27 -i NAME name of server instance (if using mysql.server startup script)
28 -l don't subtract lock time from total time
29
30 [root@k8s-node3 ~]#
mysqldumpslow具体使用，如下所示：

1 [root@k8s-node3 ~]# mysqldumpslow -s -r -t 3 /var/lib/mysql/localhost-slow.log
2
3 Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
4 Count: 6 Time=4.33s (26s) Lock=0.00s (0s) Rows=1.0 (6), root[root]@localhost
5 select sleep(N)
6
7 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
8 [root@k8s-node3 ~]# mysqldumpslow -s -c -t 3 /var/lib/mysql/localhost-slow.log
9
10 Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
11 Count: 6 Time=4.33s (26s) Lock=0.00s (0s) Rows=1.0 (6), root[root]@localhost
12 select sleep(N)
13
14 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
15 [root@k8s-node3 ~]# mysqldumpslow -s t -t 3 "left join" /var/lib/mysql/localhost-slow.log
16
17 Reading mysql slow query log from left join /var/lib/mysql/localhost-slow.log
18 Can't open left join: No such file or directory at /usr/bin/mysqldumpslow line 97.
19 Count: 6 Time=4.33s (26s) Lock=0.00s (0s) Rows=1.0 (6), root[root]@localhost
20 select sleep(N)
21
22 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
23 [root@k8s-node3 ~]# mysqldumpslow -s t -t 3 -g "left join" /var/lib/mysql/localhost-slow.log
24
25 Reading mysql slow query log from /var/lib/mysql/localhost-slow.log
26 Died at /usr/bin/mysqldumpslow line 167, <> chunk 6.
27 [root@k8s-node3 ~]#

13、通过profiles分析mysql的海量数据。首先创建两个数据表，如下所示：

1 SET NAMES utf8mb4;
2 SET FOREIGN_KEY_CHECKS = 0;
3
4 -- ----------------------------
5 -- Table structure for dept
6 -- ----------------------------
7 DROP TABLE IF EXISTS `dept`;
8 CREATE TABLE `dept` (
9 `dno` int(11) NOT NULL,
10 `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
11 `location` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
12 PRIMARY KEY (`dno`) USING BTREE
13 ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
14
15 -- ----------------------------
16 -- Table structure for emp
17 -- ----------------------------
18 DROP TABLE IF EXISTS `emp`;
19 CREATE TABLE `emp` (
20 `eid` int(11) NOT NULL,
21 `ename` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
22 `job` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
23 `deptno` int(5) DEFAULT NULL,
24 PRIMARY KEY (`eid`) USING BTREE
25 ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
创建一个存储函数，方便海量插入数据，如下所示：

1 -- ----------------------------
2 -- Function structure for randstring
3 -- ----------------------------
4 DROP FUNCTION IF EXISTS `randstring`;
5 delimiter ;;
6 CREATE DEFINER=`root`@`localhost` FUNCTION `randstring`( n INT ) RETURNS varchar(255) CHARSET utf8
7 BEGIN
8 DECLARE
9 all_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
10 DECLARE
11 return_str VARCHAR ( 255 ) DEFAULT '';
12 DECLARE
13 i INT DEFAULT 0;
14 WHILE
15 i < n DO
16
17 SET return_str = CONCAT( return_str, SUBSTRING( all_str, FLOOR( 1 + RAND( ) * 52 ), 1 ) );
18
19 SET i = i + 1;
20
21 END WHILE;
22 RETURN return_str;
23
24 END
25 ;;
26 delimiter ;
27
28 SET FOREIGN_KEY_CHECKS = 1;
29
30 -- 执行，如下所示：
31
32
33 mysql> delimiter $
34 mysql> CREATE FUNCTION randstring ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
35 -> DECLARE
36 -> all_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
37 -> DECLARE
38 -> return_str VARCHAR ( 255 ) DEFAULT '';
39 -> DECLARE
40 -> i INT DEFAULT 0;
41 -> WHILE
42 -> i < n DO
43 ->
44 -> SET return_str = CONCAT( return_str, SUBSTRING( all_str, FLOOR( 1 + RAND( ) * 52 ), 1 ) );
45 ->
46 -> SET i = i + 1;
47 ->
48 -> END WHILE;
49 -> RETURN return_str;
50 ->
51 -> END $
52 Query OK, 0 rows affected (0.03 sec)
53
54 mysql>
创建一个生成随机数0-99的存储函数，如下所示：

1 -- 防止分号造成语义中断。
2
3 delimiter $
4 CREATE FUNCTION rand_num ( ) RETURNS INT ( 5 ) BEGIN
5 DECLARE
6 i INT DEFAULT 0;
7
8 SET i = FLOOR( rand( ) * 100 );
9 RETURN i;
10
11 END $
通过存储过程插入海量数据，把上面的两个存储函数使用上，注意，存储函数有返回值，存储过程没有返回值，如下所示：

1 mysql>
2 mysql> delimiter $
3 mysql> CREATE PROCEDURE insert_emp ( IN eid_start INT ( 10 ), IN data_times INT ( 10 ) ) BEGIN
4 -> DECLARE
5 -> i INT DEFAULT 0;
6 ->
7 -> SET autocommit = 0;
8 -> REPEAT
9 -> INSERT INTO emp
10 -> VALUES
11 -> ( eid_start + i, randstring ( 5 ), 'other', rand_num ( ) );
12 ->
13 -> SET i = i + 1;
14 -> UNTIL i = data_times
15 -> END REPEAT;
16 -> COMMIT;
17 ->
18 -> END $
19 Query OK, 0 rows affected (0.01 sec)
20
21 mysql>
1 mysql>
2 mysql> delimiter $
3 mysql> CREATE PROCEDURE insert_dept ( IN dno_start INT ( 10 ), IN data_times INT ( 10 ) ) BEGIN
4 -> DECLARE
5 -> i INT DEFAULT 0;
6 ->
7 -> SET autocommit = 0;
8 -> REPEAT
9 -> INSERT INTO dept
10 -> VALUES
11 -> ( dno_start + i, randstring ( 6 ), randstring ( 8 ) );
12 ->
13 -> SET i = i + 1;
14 -> UNTIL i = data_times
15 -> END REPEAT;
16 -> COMMIT;
17 ->
18 -> END $
19 Query OK, 0 rows affected (0.00 sec)
20
21 mysql>
编写好存储过程之后，就可以开始调用存储过程了，开始批量插入数据，如下所示：

1 mysql> delimiter ;
2 mysql> CALL insert_emp(1000,800000);
3 Query OK, 0 rows affected (1 min 8.33 sec)
4
5 mysql>
6
7 mysql> delimiter ;
8 mysql> CALL insert_dept(10,30);
9 Query OK, 0 rows affected (0.01 sec)
10
11 mysql>
12
13 mysql> select count(*) from emp;
14 +----------+
15 | count(*) |
16 +----------+
17 | 800000 |
18 +----------+
19 1 row in set (0.22 sec)
20
21 mysql> select count(*) from dept;
22 +----------+
23 | count(*) |
24 +----------+
25 | 30 |
26 +----------+
27 1 row in set (0.00 sec)
28
29 mysql>
海量数据已经生成了，这里通过profiles分析mysql的海量数据。默认关闭，需要打开，如下所示：

1 mysql> show profiles;
2 Empty set, 1 warning (0.00 sec)
3
4 mysql> show variables like '%profiling%';
5 +------------------------+-------+
6 | Variable_name | Value |
7 +------------------------+-------+
8 | have_profiling | YES |
9 | profiling | OFF |
10 | profiling_history_size | 15 |
11 +------------------------+-------+
12 3 rows in set (0.04 sec)
13
14 mysql> set profiling = on;
15 Query OK, 0 rows affected, 1 warning (0.00 sec)
16
17 mysql> show variables like '%profiling%';
18 +------------------------+-------+
19 | Variable_name | Value |
20 +------------------------+-------+
21 | have_profiling | YES |
22 | profiling | ON |
23 | profiling_history_size | 15 |
24 +------------------------+-------+
25 3 rows in set (0.01 sec)
26
27 mysql> show profiles;
28 +----------+------------+-----------------------------------+
29 | Query_ID | Duration | Query |
30 +----------+------------+-----------------------------------+
31 | 1 | 0.00209325 | show variables like '%profiling%' |
32 +----------+------------+-----------------------------------+
33 1 row in set, 1 warning (0.00 sec)
34
35 mysql>
show profiles会记录所有profiling打开之后的的所有sql查询语句。Query_ID是编号，Duration表示花费的时间，单位是秒，Query是查询的语句。

1 mysql> show profiles;
2 +----------+------------+-----------------------------------+
3 | Query_ID | Duration | Query |
4 +----------+------------+-----------------------------------+
5 | 1 | 0.00209325 | show variables like '%profiling%' |
6 +----------+------------+-----------------------------------+
7 1 row in set, 1 warning (0.00 sec)
8
9 mysql> select count(*) from dept;
10 +----------+
11 | count(*) |
12 +----------+
13 | 30 |
14 +----------+
15 1 row in set (0.00 sec)
16
17 mysql> show profiles;
18 +----------+------------+-----------------------------------+
19 | Query_ID | Duration | Query |
20 +----------+------------+-----------------------------------+
21 | 1 | 0.00209325 | show variables like '%profiling%' |
22 | 2 | 0.00075825 | select count(*) from dept |
23 +----------+------------+-----------------------------------+
24 2 rows in set, 1 warning (0.00 sec)
25
26 mysql>
缺点，是Duration显示的时间不够精准，只是将cpu、IO、内存等等消费的总和。只能看到总共消费的时间，不能看到各个硬件消费的时间。

如何进行精准分析呢，需要通过一个sql诊断show profile all for query 上一步查询的Query_ID。

1 mysql> show profiles;
2 +----------+------------+-----------------------------------+
3 | Query_ID | Duration | Query |
4 +----------+------------+-----------------------------------+
5 | 1 | 0.00209325 | show variables like '%profiling%' |
6 | 2 | 0.00075825 | select count(*) from dept |
7 | 3 | 0.00071025 | select * from dept |
8 +----------+------------+-----------------------------------+
9 3 rows in set, 1 warning (0.00 sec)
10
11 mysql> show profile all for query 3;
12 +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
13 | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line |
14 +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
15 | starting | 0.000102 | 0.000097 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL |
16 | checking permissions | 0.000017 | 0.000012 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_authorization.cc | 809 |
17 | Opening tables | 0.000061 | 0.000064 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 5781 |
18 | init | 0.000077 | 0.000077 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 128 |
19 | System lock | 0.000019 | 0.000016 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 330 |
20 | optimizing | 0.000007 | 0.000006 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 158 |
21 | statistics | 0.000054 | 0.000058 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 374 |
22 | preparing | 0.000022 | 0.000018 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize | sql_optimizer.cc | 482 |
23 | executing | 0.000005 | 0.000005 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 126 |
24 | Sending data | 0.000164 | 0.000169 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec | sql_executor.cc | 202 |
25 | end | 0.000013 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | handle_query | sql_select.cc | 206 |
26 | query end | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 4956 |
27 | closing tables | 0.000009 | 0.000009 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 5009 |
28 | freeing items | 0.000115 | 0.000118 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 5622 |
29 | cleaning up | 0.000037 | 0.000034 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 1931 |
30 +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+----------------------+-------------+
31 15 rows in set, 1 warning (0.01 sec)
32
33 mysql>
如果只关心部分字段，可以进行部分字段的查询，如下所示：

1 mysql> show profile cpu,block io for query 3;
2 +----------------------+----------+----------+------------+--------------+---------------+
3 | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
4 +----------------------+----------+----------+------------+--------------+---------------+
5 | starting | 0.000102 | 0.000097 | 0.000000 | 0 | 0 |
6 | checking permissions | 0.000017 | 0.000012 | 0.000000 | 0 | 0 |
7 | Opening tables | 0.000061 | 0.000064 | 0.000000 | 0 | 0 |
8 | init | 0.000077 | 0.000077 | 0.000000 | 0 | 0 |
9 | System lock | 0.000019 | 0.000016 | 0.000000 | 0 | 0 |
10 | optimizing | 0.000007 | 0.000006 | 0.000000 | 0 | 0 |
11 | statistics | 0.000054 | 0.000058 | 0.000000 | 0 | 0 |
12 | preparing | 0.000022 | 0.000018 | 0.000000 | 0 | 0 |
13 | executing | 0.000005 | 0.000005 | 0.000000 | 0 | 0 |
14 | Sending data | 0.000164 | 0.000169 | 0.000000 | 0 | 0 |
15 | end | 0.000013 | 0.000009 | 0.000000 | 0 | 0 |
16 | query end | 0.000009 | 0.000009 | 0.000000 | 0 | 0 |
17 | closing tables | 0.000009 | 0.000009 | 0.000000 | 0 | 0 |
18 | freeing items | 0.000115 | 0.000118 | 0.000000 | 0 | 0 |
19 | cleaning up | 0.000037 | 0.000034 | 0.000000 | 0 | 0 |
20 +----------------------+----------+----------+------------+--------------+---------------+
21 15 rows in set, 1 warning (0.00 sec)
22
23 mysql>
查看全局的查询日志（默认关闭的，需要手动开启），记录开启之后的，全部sql语句。这些全局的记录操作，仅仅在调优和开发过程中打开即可，在正式环境要进行关闭的，如下所示：

1 mysql> show variables like '%general_log%';
2 +------------------+------------------------------+
3 | Variable_name | Value |
4 +------------------+------------------------------+
5 | general_log | OFF |
6 | general_log_file | /var/lib/mysql/k8s-node3.log |
7 +------------------+------------------------------+
8 2 rows in set (0.01 sec)
9
10 mysql> set global general_log = 1;
11 Query OK, 0 rows affected (0.00 sec)
12
13 mysql> show variables like '%general_log%';
14 +------------------+------------------------------+
15 | Variable_name | Value |
16 +------------------+------------------------------+
17 | general_log | ON |
18 | general_log_file | /var/lib/mysql/k8s-node3.log |
19 +------------------+------------------------------+
20 2 rows in set (0.00 sec)
21
22 mysql>
1 mysql> set global log_output='table';
2 Query OK, 0 rows affected (0.00 sec)
开启之后，会记录所有的sql语句的，会被记录到mysql.general_log数据表中，如下所示：

1 [root@k8s-node3 ~]# cat /var/lib/mysql/k8s-node3.log
2 /usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
3 Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
4 Time Id Command Argument
5 2020-07-20T07:07:09.834131Z 12 Query show variables like '%general_log%'
6 2020-07-20T07:07:57.165503Z 12 Query SELECT DATABASE()
7 2020-07-20T07:07:57.166003Z 12 Init DB bigdata
8 2020-07-20T07:07:57.168232Z 12 Query show databases
9 2020-07-20T07:07:57.169327Z 12 Query show tables
10 2020-07-20T07:07:57.169786Z 12 Field List dept
11 2020-07-20T07:07:57.171001Z 12 Field List emp
12 2020-07-20T07:07:59.196072Z 12 Query show tables
13 2020-07-20T07:08:11.721455Z 12 Query select * from dept
14 2020-07-20T07:08:19.914896Z 12 Query select * from emp limit 3000
15 2020-07-20T07:08:32.237933Z 12 Query show variables like '%general_log%'
16 [root@k8s-node3 ~]#
如果开启了mysql> set global log_output='table';也可以查看mysql的数据表来查看sql语句，如下所示：

注意：这里既可以指定表也可以指定文件，看个人需求了：set global log_output='table';或者set global log_output='file';

1 mysql> select * from mysql.general_log;
2 +----------------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
3 | event_time | user_host | thread_id | server_id | command_type | argument |
4 +----------------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
5 | 2020-07-20 15:10:52.374558 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from mysql.general_log |
6 | 2020-07-20 15:11:27.112186 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from dept |
7 | 2020-07-20 15:11:30.430782 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from emp limit 3000 |
8 | 2020-07-20 15:11:32.430116 | root[root] @ localhost [127.0.0.1] | 12 | 0 | Query | select * from mysql.general_log |
9 +----------------------------+------------------------------------+-----------+-----------+--------------+---------------------------------+
10 4 rows in set (0.00 sec)
11
12 mysql>

14、锁机制，解决因资源共享而造成的并发问题。锁的分类，根据操作类型分，可以分为读锁（读锁也称为共享锁）、写锁（写锁也称为互斥锁）。根据操作范围来分，可以分为表级锁、行级锁、页级锁。

1 SET NAMES utf8mb4;
2 SET FOREIGN_KEY_CHECKS = 0;
3
4 -- ----------------------------
5 -- Table structure for user
6 -- ----------------------------
7 DROP TABLE IF EXISTS `user`;
8 CREATE TABLE `user` (
9 `id` int(11) NOT NULL AUTO_INCREMENT,
10 `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
11 PRIMARY KEY (`id`) USING BTREE
12 ) ENGINE = MyISAM AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
13
14 -- ----------------------------
15 -- Records of user
16 -- ----------------------------
17 INSERT INTO `user` VALUES (1, '张三');
18 INSERT INTO `user` VALUES (2, '李四');
19 INSERT INTO `user` VALUES (3, '王五');
20 INSERT INTO `user` VALUES (4, '赵六');
21 INSERT INTO `user` VALUES (5, '孙七');
22
23 SET FOREIGN_KEY_CHECKS = 1;
14.1、根据操作类型分，可以分为读锁、写锁。

　　1）、读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰。
　　2）、写锁（互斥锁）：如果当前写操作没有完毕，则无法进行其他的读操作，写操作。

14.2、根据操作范围来分，可以分为表级锁、行级锁、页级锁。

　　1）、表级锁：一次性对一张表整体加锁。MyISAM存储引擎（开销小，加锁快，缺点就是锁的范围大容易发生锁冲突，并发度低），是表级锁，性能优化。
　　2）、行级锁：一次性对一行数据进行加锁。InnoDB存储引擎（开销大，加锁慢，容易出现死锁，锁的范围较小，不易发生锁冲突，并发度高，很小的概率发生高并发问题，这些问题分别是脏读、幻读、不可重复读、丢失更新），是行级锁，事务优先，适合高并发操作。

14.3、如何查看加锁的表呢，如下所示：

1 mysql> show open tables;
2 +--------------------+------------------------------------------------------+--------+-------------+
3 | Database | Table | In_use | Name_locked |
4 +--------------------+------------------------------------------------------+--------+-------------+
5 | performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
6 | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
7 | performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
8 | performance_schema | replication_connection_status | 0 | 0 |
9 | performance_schema | events_waits_summary_by_account_by_event_name | 0 | 0 |
10 | mysql | engine_cost | 0 | 0 |
11 | performance_schema | metadata_locks | 0 | 0 |
12 | performance_schema | status_by_user | 0 | 0 |
13 | performance_schema | replication_group_member_stats | 0 | 0 |
14 | performance_schema | events_statements_summary_by_account_by_event_name | 0 | 0 |
15 | performance_schema | socket_summary_by_event_name | 0 | 0 |
16 | performance_schema | prepared_statements_instances | 0 | 0 |
17 | performance_schema | events_statements_history_long | 0 | 0 |
18 | performance_schema | objects_summary_global_by_type | 0 | 0 |
19 | performance_schema | file_instances | 0 | 0 |
20 | performance_schema | events_stages_summary_by_user_by_event_name | 0 | 0 |
21 | performance_schema | memory_summary_by_thread_by_event_name | 0 | 0 |
22 | performance_schema | events_stages_history_long | 0 | 0 |
23 | performance_schema | cond_instances | 0 | 0 |
24 | performance_schema | global_status | 0 | 0 |
25 | performance_schema | socket_summary_by_instance | 0 | 0 |
26 | book | user_info | 0 | 0 |
27 | performance_schema | session_status | 0 | 0 |
28 | performance_schema | session_connect_attrs | 0 | 0 |
29 | mysql | plugin | 0 | 0 |
30 | mysql | time_zone_name | 0 | 0 |
31 | performance_schema | events_statements_summary_by_program | 0 | 0 |
32 | performance_schema | events_stages_current | 0 | 0 |
33 | performance_schema | setup_instruments | 0 | 0 |
34 | book | book_sort | 0 | 0 |
35 | book | book_recommendation | 0 | 0 |
36 | mysql | func | 0 | 0 |
37 | performance_schema | events_waits_history_long | 0 | 0 |
38 | performance_schema | rwlock_instances | 0 | 0 |
39 | mysql | time_zone_leap_second | 0 | 0 |
40 | performance_schema | table_io_waits_summary_by_table | 0 | 0 |
41 | performance_schema | events_transactions_summary_by_account_by_event_name | 0 | 0 |
42 | mysql | time_zone_transition_type | 0 | 0 |
43 | performance_schema | events_waits_current | 0 | 0 |
44 | performance_schema | replication_connection_configuration | 0 | 0 |
45 | mysql | procs_priv | 0 | 0 |
46 | performance_schema | events_transactions_summary_by_user_by_event_name | 0 | 0 |
47 | performance_schema | replication_applier_configuration | 0 | 0 |
48 | performance_schema | events_statements_summary_by_user_by_event_name | 0 | 0 |
49 | performance_schema | events_stages_summary_global_by_event_name | 0 | 0 |
50 | performance_schema | replication_applier_status_by_worker | 0 | 0 |
51 | performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 |
52 | performance_schema | session_account_connect_attrs | 0 | 0 |
53 | performance_schema | performance_timers | 0 | 0 |
54 | performance_schema | setup_consumers | 0 | 0 |
55 | performance_schema | events_statements_history | 0 | 0 |
56 | bigdata | emp | 0 | 0 |
57 | performance_schema | global_variables | 0 | 0 |
58 | mysql | gtid_executed | 0 | 0 |
59 | mysql | columns_priv | 0 | 0 |
60 | performance_schema | events_transactions_summary_by_thread_by_event_name | 0 | 0 |
61 | performance_schema | replication_applier_status_by_coordinator | 0 | 0 |
62 | mysql | db | 0 | 0 |
63 | mysql | general_log | 0 | 0 |
64 | performance_schema | events_stages_summary_by_account_by_event_name | 0 | 0 |
65 | performance_schema | variables_by_thread | 0 | 0 |
66 | book | user_book | 0 | 0 |
67 | performance_schema | events_stages_history | 0 | 0 |
68 | bigdata | dept | 0 | 0 |
69 | performance_schema | socket_instances | 0 | 0 |
70 | performance_schema | table_lock_waits_summary_by_table | 0 | 0 |
71 | mysql | time_zone | 0 | 0 |
72 | performance_schema | events_statements_summary_by_thread_by_event_name | 0 | 0 |
73 | performance_schema | users | 0 | 0 |
74 | performance_schema | setup_timers | 0 | 0 |
75 | performance_schema | memory_summary_by_host_by_event_name | 0 | 0 |
76 | performance_schema | setup_objects | 0 | 0 |
77 | performance_schema | host_cache | 0 | 0 |
78 | performance_schema | status_by_account | 0 | 0 |
79 | mysql | proxies_priv | 0 | 0 |
80 | performance_schema | memory_summary_by_account_by_event_name | 0 | 0 |
81 | performance_schema | accounts | 0 | 0 |
82 | performance_schema | replication_group_members | 0 | 0 |
83 | mysql | tables_priv | 0 | 0 |
84 | performance_schema | events_stages_summary_by_host_by_event_name | 0 | 0 |
85 | performance_schema | events_statements_current | 0 | 0 |
86 | mysql | proc | 0 | 0 |
87 | performance_schema | events_waits_summary_by_instance | 0 | 0 |
88 | performance_schema | events_statements_summary_by_host_by_event_name | 0 | 0 |
89 | performance_schema | memory_summary_by_user_by_event_name | 0 | 0 |
90 | performance_schema | events_transactions_history | 0 | 0 |
91 | mysql | event | 0 | 0 |
92 | performance_schema | status_by_host | 0 | 0 |
93 | performance_schema | setup_actors | 0 | 0 |
94 | performance_schema | threads | 0 | 0 |
95 | performance_schema | events_statements_summary_global_by_event_name | 0 | 0 |
96 | performance_schema | status_by_thread | 0 | 0 |
97 | performance_schema | file_summary_by_event_name | 0 | 0 |
98 | performance_schema | mutex_instances | 0 | 0 |
99 | performance_schema | table_handles | 0 | 0 |
100 | performance_schema | session_variables | 0 | 0 |
101 | bigdata | user | 0 | 0 |
102 | book | book_info | 0 | 0 |
103 | performance_schema | events_transactions_current | 0 | 0 |
104 | performance_schema | user_variables_by_thread | 0 | 0 |
105 | mysql | time_zone_transition | 0 | 0 |
106 | performance_schema | table_io_waits_summary_by_index_usage | 0 | 0 |
107 | performance_schema | events_transactions_history_long | 0 | 0 |
108 | performance_schema | memory_summary_global_by_event_name | 0 | 0 |
109 | performance_schema | events_statements_summary_by_digest | 0 | 0 |
110 | performance_schema | events_transactions_summary_by_host_by_event_name | 0 | 0 |
111 | performance_schema | events_waits_history | 0 | 0 |
112 | mysql | user | 0 | 0 |
113 | performance_schema | events_waits_summary_by_host_by_event_name | 0 | 0 |
114 | performance_schema | events_stages_summary_by_thread_by_event_name | 0 | 0 |
115 | performance_schema | file_summary_by_instance | 0 | 0 |
116 | mysql | server_cost | 0 | 0 |
117 | performance_schema | hosts | 0 | 0 |
118 | performance_schema | replication_applier_status | 0 | 0 |
119 | mysql | servers | 0 | 0 |
120 +--------------------+------------------------------------------------------+--------+-------------+
121 115 rows in set (0.01 sec)
122
123 mysql>
如何加锁呢，如是，lock table 表1 read/write,表2 read/write...。首先研究的是MyISAM存储引擎的读锁，读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰。

1 mysql> lock table user read;
2 Query OK, 0 rows affected (0.00 sec)
3
4 mysql> select * from user;
5 +----+--------+
6 | id | name |
7 +----+--------+
8 | 1 | 张三 |
9 | 2 | 李四 |
10 | 3 | 王五 |
11 | 4 | 赵六 |
12 | 5 | 孙七 |
13 +----+--------+
14 5 rows in set (0.00 sec)
15
16 mysql>
加了读锁，是不可以进行写的（写操作就是增加、修改、删除），读就是查询。

1 mysql> delete from user where id = 1;
2 ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
3 mysql>
　　总结：MyISAM存储引擎，如果某一个会话，对A表加了read锁，则该会话可以对A表进行读操作，不能进行写操作，且该会话不能对其他表进行读、写操作。会话0给A表加了锁，其它会话，是可以对其他表（A表以外的表）进行读、写操作。对A表是可以读的，但是写操作的时候需要等待读锁的释放。

14.4、加写锁，会话0加写锁，如下所示：

　　总结：当前会话即会话0，可以对加了写锁的表进行任何操作（增删改查操作），但是不能操作（增删改查）其他表。对会话0中加写锁的表，可以进行增删改查的前提是，等待会话0释放写锁。

14.5、Mysql中表级锁的锁模式，MyISAM在执行查询语句Select前，会自动给涉及到的所有表加读锁，在执行更新操作DML前，会自动给涉及的表加写锁。所以对MyISAM表进行操作的时候会有以下情况：

　　a、对MyISAM表的读操作（加读锁），不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
　　b、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

15、表锁情况分析及行锁解析，分析表锁定。

　　1）、查看那些表加了锁：show open tables;其中1代表加了锁。
　　2）、分析表锁定的严重程度：show status like 'table%';其中Table_locks_immediate表示立刻可以获取到的锁。Table_locks_waited代表了需要等待的表锁数，如果该值越大说明存在越大的锁竞争。
　　3）、一般建议使用Table_locks_immediate/Table_locks_waited比率来看，如果大于5000采用InnoDB引擎，如果小于5000采用MyISAM存储引擎。表锁是通过unlock tables解锁的，也可以通过事务进行解锁的，行锁是通过事务commit/rollback解锁的。

1 mysql> show open tables;
2 +--------------------+------------------------------------------------------+--------+-------------+
3 | Database | Table | In_use | Name_locked |
4 +--------------------+------------------------------------------------------+--------+-------------+
5 | performance_schema | events_waits_summary_by_user_by_event_name | 0 | 0 |
6 | performance_schema | events_waits_summary_global_by_event_name | 0 | 0 |
7 | performance_schema | events_transactions_summary_global_by_event_name | 0 | 0 |
8 | performance_schema | replication_connection_status | 0 | 0 |
9 | performance_schema | events_waits_summary_by_account_by_event_name | 0 | 0 |
10 | mysql | engine_cost | 0 | 0 |
11 | performance_schema | metadata_locks | 0 | 0 |
12 | performance_schema | status_by_user | 0 | 0 |
13 | performance_schema | replication_group_member_stats | 0 | 0 |
14 | performance_schema | events_statements_summary_by_account_by_event_name | 0 | 0 |
15 | performance_schema | socket_summary_by_event_name | 0 | 0 |
16 | performance_schema | prepared_statements_instances | 0 | 0 |
17 | performance_schema | events_statements_history_long | 0 | 0 |
18 | performance_schema | objects_summary_global_by_type | 0 | 0 |
19 | performance_schema | file_instances | 0 | 0 |
20 | performance_schema | events_stages_summary_by_user_by_event_name | 0 | 0 |
21 | performance_schema | memory_summary_by_thread_by_event_name | 0 | 0 |
22 | performance_schema | events_stages_history_long | 0 | 0 |
23 | performance_schema | cond_instances | 0 | 0 |
24 | performance_schema | global_status | 0 | 0 |
25 | performance_schema | socket_summary_by_instance | 0 | 0 |
26 | book | user_info | 0 | 0 |
27 | performance_schema | session_status | 0 | 0 |
28 | performance_schema | session_connect_attrs | 0 | 0 |
29 | mysql | plugin | 0 | 0 |
30 | mysql | time_zone_name | 0 | 0 |
31 | performance_schema | events_statements_summary_by_program | 0 | 0 |
32 | performance_schema | events_stages_current | 0 | 0 |
33 | performance_schema | setup_instruments | 0 | 0 |
34 | book | book_sort | 0 | 0 |
35 | book | book_recommendation | 0 | 0 |
36 | mysql | func | 0 | 0 |
37 | performance_schema | events_waits_history_long | 0 | 0 |
38 | performance_schema | rwlock_instances | 0 | 0 |
39 | mysql | time_zone_leap_second | 0 | 0 |
40 | performance_schema | table_io_waits_summary_by_table | 0 | 0 |
41 | performance_schema | events_transactions_summary_by_account_by_event_name | 0 | 0 |
42 | mysql | time_zone_transition_type | 0 | 0 |
43 | performance_schema | events_waits_current | 0 | 0 |
44 | performance_schema | replication_connection_configuration | 0 | 0 |
45 | mysql | procs_priv | 0 | 0 |
46 | performance_schema | events_transactions_summary_by_user_by_event_name | 0 | 0 |
47 | performance_schema | replication_applier_configuration | 0 | 0 |
48 | performance_schema | events_statements_summary_by_user_by_event_name | 0 | 0 |
49 | performance_schema | events_stages_summary_global_by_event_name | 0 | 0 |
50 | performance_schema | replication_applier_status_by_worker | 0 | 0 |
51 | performance_schema | events_waits_summary_by_thread_by_event_name | 0 | 0 |
52 | performance_schema | session_account_connect_attrs | 0 | 0 |
53 | performance_schema | performance_timers | 0 | 0 |
54 | performance_schema | setup_consumers | 0 | 0 |
55 | performance_schema | events_statements_history | 0 | 0 |
56 | bigdata | emp | 0 | 0 |
57 | performance_schema | global_variables | 0 | 0 |
58 | mysql | gtid_executed | 0 | 0 |
59 | mysql | columns_priv | 0 | 0 |
60 | performance_schema | events_transactions_summary_by_thread_by_event_name | 0 | 0 |
61 | performance_schema | replication_applier_status_by_coordinator | 0 | 0 |
62 | mysql | db | 0 | 0 |
63 | mysql | general_log | 0 | 0 |
64 | performance_schema | events_stages_summary_by_account_by_event_name | 0 | 0 |
65 | performance_schema | variables_by_thread | 0 | 0 |
66 | book | user_book | 0 | 0 |
67 | performance_schema | events_stages_history | 0 | 0 |
68 | bigdata | dept | 0 | 0 |
69 | performance_schema | socket_instances | 0 | 0 |
70 | performance_schema | table_lock_waits_summary_by_table | 0 | 0 |
71 | mysql | time_zone | 0 | 0 |
72 | performance_schema | events_statements_summary_by_thread_by_event_name | 0 | 0 |
73 | performance_schema | users | 0 | 0 |
74 | performance_schema | setup_timers | 0 | 0 |
75 | performance_schema | memory_summary_by_host_by_event_name | 0 | 0 |
76 | performance_schema | setup_objects | 0 | 0 |
77 | performance_schema | host_cache | 0 | 0 |
78 | performance_schema | status_by_account | 0 | 0 |
79 | mysql | proxies_priv | 0 | 0 |
80 | performance_schema | memory_summary_by_account_by_event_name | 0 | 0 |
81 | performance_schema | accounts | 0 | 0 |
82 | performance_schema | replication_group_members | 0 | 0 |
83 | mysql | tables_priv | 0 | 0 |
84 | performance_schema | events_stages_summary_by_host_by_event_name | 0 | 0 |
85 | performance_schema | events_statements_current | 0 | 0 |
86 | mysql | proc | 0 | 0 |
87 | performance_schema | events_waits_summary_by_instance | 0 | 0 |
88 | performance_schema | events_statements_summary_by_host_by_event_name | 0 | 0 |
89 | performance_schema | memory_summary_by_user_by_event_name | 0 | 0 |
90 | performance_schema | events_transactions_history | 0 | 0 |
91 | mysql | event | 0 | 0 |
92 | performance_schema | status_by_host | 0 | 0 |
93 | performance_schema | setup_actors | 0 | 0 |
94 | performance_schema | threads | 0 | 0 |
95 | performance_schema | events_statements_summary_global_by_event_name | 0 | 0 |
96 | performance_schema | status_by_thread | 0 | 0 |
97 | performance_schema | file_summary_by_event_name | 0 | 0 |
98 | performance_schema | mutex_instances | 0 | 0 |
99 | performance_schema | table_handles | 0 | 0 |
100 | performance_schema | session_variables | 0 | 0 |
101 | bigdata | user | 1 | 0 |
102 | book | book_info | 0 | 0 |
103 | performance_schema | events_transactions_current | 0 | 0 |
104 | performance_schema | user_variables_by_thread | 0 | 0 |
105 | mysql | time_zone_transition | 0 | 0 |
106 | performance_schema | table_io_waits_summary_by_index_usage | 0 | 0 |
107 | performance_schema | events_transactions_history_long | 0 | 0 |
108 | performance_schema | memory_summary_global_by_event_name | 0 | 0 |
109 | performance_schema | events_statements_summary_by_digest | 0 | 0 |
110 | performance_schema | events_transactions_summary_by_host_by_event_name | 0 | 0 |
111 | performance_schema | events_waits_history | 0 | 0 |
112 | mysql | user | 0 | 0 |
113 | performance_schema | events_waits_summary_by_host_by_event_name | 0 | 0 |
114 | mysql | slow_log | 0 | 0 |
115 | performance_schema | file_summary_by_instance | 0 | 0 |
116 | mysql | server_cost | 0 | 0 |
117 | performance_schema | hosts | 0 | 0 |
118 | performance_schema | replication_applier_status | 0 | 0 |
119 | mysql | servers | 0 | 0 |
120 | performance_schema | events_stages_summary_by_thread_by_event_name | 0 | 0 |
121 +--------------------+------------------------------------------------------+--------+-------------+
122 116 rows in set (0.01 sec)
123
124 mysql> show status like 'table%';
125 +----------------------------+-------+
126 | Variable_name | Value |
127 +----------------------------+-------+
128 | Table_locks_immediate | 411 |
129 | Table_locks_waited | 0 |
130 | Table_open_cache_hits | 3 |
131 | Table_open_cache_misses | 1 |
132 | Table_open_cache_overflows | 0 |
133 +----------------------------+-------+
134 5 rows in set (0.01 sec)
135
136 mysql>

16、行锁的注意事项及使用情况分析：

　　1）、行锁的注意事项，如果没有索引，则行锁会转为表锁。如果索引列发生了类型转换，则索引失效。比如整数转换为字符串类型，导致索引失效，会导致行锁升级为表锁。
　　2）、行锁的一种特殊情况，间隙锁，值在范围内，但却不存在。行锁如果有where则实际加锁的范围就是where后面的范围，不是数据表中保存的实际的值。
　　3）、行锁的缺点比表锁性能损耗大，优点是并发能力强，效率高。InnoDB默认采用的是行锁。因此建议高并发采用InnoDB，否则使用MyISAM存储引擎。

17、行锁分析 show status like '%innodb_row_lock%';

　　1）、Innodb_row_lock_current_waits代表了当前正在等待锁的数量。
　　2）、Innodb_row_lock_time代表了等待的总时长，从系统启动到现在一共等待的时间。
　　3）、Innodb_row_lock_time_avg代表了平均等待时长，从系统启动到现在平均等待的时间。
　　4）、Innodb_row_lock_time_max代表了最大等待时长，从系统启动到现在最大一次等待的时间。
　　5）、Innodb_row_lock_waits代表了等待次数，从系统启动到现在一共等待的次数。

1 mysql> show status like '%innodb_row_lock%';
2 +-------------------------------+-------+
3 | Variable_name | Value |
4 +-------------------------------+-------+
5 | Innodb_row_lock_current_waits | 0 |
6 | Innodb_row_lock_time | 0 |
7 | Innodb_row_lock_time_avg | 0 |
8 | Innodb_row_lock_time_max | 0 |
9 | Innodb_row_lock_waits | 0 |
10 +-------------------------------+-------+
11 5 rows in set (0.00 sec)
12
13 mysql>
将Mysql的自动提交关闭的三种方式，第一种方式set autocommit=0;第二种方式start transaction;第三种方式begin；通过for update对query查询语句进行加锁。

weixin_47072624

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
CentOS7系统使用rpm方式安装MySQL5.7

参考：https://blog.csdn.net/wudinaniya/article/details/810945781、首先去mysql官网下载rpm包，一个是server包一个是client包。官网下载地址：https://www.mysql.com/downloads/这里用mysql community server里边的。这里使用的是5.7.x版本了，不使用Mysql8.x版本了。我这里使用的是Centos7.x版本的64位操作系统，使用mysql5.7版本的数据库。查看linux操
复制链接

扫一扫