mysql必知必_Mysql必知必会学习笔记（二）安装mysql

通过yum方式安装mysql5.7

一、获取官方yum

[root@localhost mysql_dmp]# wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

--2020-03-04 23:36:46-- http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:80... connected.

HTTP request sent, awaiting response...301Moved Permanently

Location: https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm [following]

--2020-03-04 23:36:47-- https://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm

Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.

HTTP request sent, awaiting response...302Found

Location: https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm [following]

--2020-03-04 23:36:48-- https://repo.mysql.com//mysql57-community-release-el7-10.noarch.rpm

Resolving repo.mysql.com (repo.mysql.com)... 72.247.97.40Connecting to repo.mysql.com (repo.mysql.com)|72.247.97.40|:443... connected.

HTTP request sent, awaiting response...200OK

Length:25548 (25K) [application/x-redhat-package-manager]

Saving to: ‘mysql57-community-release-el7-10.noarch.rpm’100%[===================================================================================================================>] 25,548 --.-K/s in0s2020-03-04 23:36:50 (133 MB/s) - ‘mysql57-community-release-el7-10.noarch.rpm’ saved [25548/25548]-c: No such fileor directory

No URLs foundin -c.

FINISHED--2020-03-04 23:36:50--Totalwall clock time: 4.0s

Downloaded:1 files, 25K in 0s (133 MB/s)

[root@localhost mysql_dmp]#lsmysql57-community-release-el7-10.noarch.rpm

yum安装rpm源

[root@localhost mysql_dmp]# yum -y install mysql57-community-release-el7-10.noarch.rpm

Loaded plugins: fastestmirror, langpacks

Examining mysql57-community-release-el7-10.noarch.rpm: mysql57-community-release-el7-10.noarch

Marking mysql57-community-release-el7-10.noarch.rpm to be installed

Resolving Dependencies-->Running transaction check---> Package mysql57-community-release.noarch 0:el7-10will be installed--> Processing Conflict: mysql80-community-release-el7-3.noarch conflicts mysql57-community-release

Loading mirror speeds from cached hostfile*base: mirrors.aliyun.com*extras: mirrors.aliyun.com*updates: mirrors.aliyun.com

No package matched to upgrade: mysql57-community-release-->Finished Dependency Resolution

Error: mysql80-community-release conflicts with mysql57-community-release-el7-10.noarch

You could try using--skip-broken to work around the problem** Found 5 pre-existing rpmdb problem(s), 'yum check'output follows:

containerd.io-1.2.6-3.3.el7.x86_64 has installed conflicts containerd: containerd.io-1.2.6-3.3.el7.x86_64

containerd.io-1.2.6-3.3.el7.x86_64 has installed conflicts runc: containerd.io-1.2.6-3.3.el7.x86_64

ipa-client-4.4.0-12.el7.centos.x86_64 has installed conflicts freeipa-client: ipa-client-4.4.0-12.el7.centos.x86_64

ipa-client-common-4.4.0-12.el7.centos.noarch has installed conflicts freeipa-client-common: ipa-client-common-4.4.0-12.el7.centos.noarch

ipa-common-4.4.0-12.el7.centos.noarch has installed conflicts freeipa-common: ipa-common-4.4.0-12.el7.centos.noarch

yum安装mysql

[root@localhost mysql_dmp]# yum -y install mysql-community-server

Loaded plugins: fastestmirror, langpacks

Loading mirror speeds from cached hostfile*base: mirrors.aliyun.com*extras: mirrors.aliyun.com*updates: mirrors.aliyun.com

Resolving Dependencies-->Running transaction check---> Package mysql-community-server.x86_64 0:8.0.19-1.el7 will be installed--> Processing Dependency: mysql-community-common(x86-64) = 8.0.19-1.el7 for package: mysql-community-server-8.0.19-1.el7.x86_64--> Processing Dependency: mysql-community-client(x86-64) >= 8.0.11 for package: mysql-community-server-8.0.19-1.el7.x86_64-->Running transaction check---> Package mysql-community-client.x86_64 0:8.0.19-1.el7 will be installed--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.19-1.el7.x86_64---> Package mysql-community-common.x86_64 0:8.0.19-1.el7 will be installed-->Running transaction check---> Package mariadb-libs.x86_64 1:5.5.52-1.el7 will be obsoleted--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64---> Package mysql-community-libs.x86_64 0:8.0.19-1.el7 will be obsoleting-->Running transaction check---> Package mysql-community-libs-compat.x86_64 0:8.0.19-1.el7 will be obsoleting---> Package postfix.x86_64 2:2.10.1-6.el7 will be updated---> Package postfix.x86_64 2:2.10.1-7.el7 will be an update--> Processing Dependency: libcrypto.so.10(OPENSSL_1.0.2)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64-->Running transaction check---> Package openssl-libs.x86_64 1:1.0.1e-60.el7 will be updated--> Processing Dependency: openssl-libs(x86-64) = 1:1.0.1e-60.el7 for package: 1:openssl-1.0.1e-60.el7.x86_64---> Package openssl-libs.x86_64 1:1.0.2k-19.el7 will be an update-->Running transaction check---> Package openssl.x86_64 1:1.0.1e-60.el7 will be updated---> Package openssl.x86_64 1:1.0.2k-19.el7 will be an update-->Finished Dependency Resolution

Dependencies Resolved=============================================================================================================================================================Package Arch Version Repository Size=============================================================================================================================================================Installing:

mysql-community-libs x86_64 8.0.19-1.el7 mysql80-community 4.0M

replacing mariadb-libs.x86_64 1:5.5.52-1.el7

mysql-community-libs-compat x86_64 8.0.19-1.el7 mysql80-community 1.3M

replacing mariadb-libs.x86_64 1:5.5.52-1.el7

mysql-community-server x86_64 8.0.19-1.el7 mysql80-community 436M

Installingfordependencies:

mysql-community-client x86_64 8.0.19-1.el7 mysql80-community 41M

mysql-community-common x86_64 8.0.19-1.el7 mysql80-community 605k

Updatingfordependencies:

openssl x86_641:1.0.2k-19.el7 base 493k

openssl-libs x86_64 1:1.0.2k-19.el7 base 1.2M

postfix x86_642:2.10.1-7.el7 base 2.4M

Transaction Summary=============================================================================================================================================================Install3 Packages (+2Dependent packages)

Upgrade (3Dependent packages)

Total size:487M

Total download size:485M

Downloading packages:

No Presto metadata availableforbase

warning:/var/cache/yum/x86_64/7/mysql80-community/packages/mysql-community-common-8.0.19-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Public keyfor mysql-community-common-8.0.19-1.el7.x86_64.rpm is not installed

(1/7): mysql-community-common-8.0.19-1.el7.x86_64.rpm | 605 kB 00:00:12(2/7): mysql-community-libs-8.0.19-1.el7.x86_64.rpm | 4.0 MB 00:06:14(3/7): mysql-community-libs-compat-8.0.19-1.el7.x86_64.rpm | 1.3 MB 00:01:34(4/7): openssl-1.0.2k-19.el7.x86_64.rpm | 493 kB 00:00:00(5/7): openssl-libs-1.0.2k-19.el7.x86_64.rpm | 1.2 MB 00:00:01mysql-community-client-8.0.19- FAILED ] 12 kB/s | 20 MB 11:05:37ETA

http://repo.mysql.com/yum/mysql-8.0-community/el/7/x86_64/mysql-community-client-8.0.19-1.el7.x86_64.rpm: [Errno 12] Timeout onhttp://repo.mysql.com/yum/mysql-8.0-community/el/7/x86_64/mysql-community-client-8.0.19-1.el7.x86_64.rpm: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds')

Trying other mirror.

(6/7): mysql-community-server-8.0.19-1.el7.x86_64.rpm | 436 MB 07:47:27(7/7): mysql-community-client-8.0.19-1.el7.x86_64.rpm | 41 MB 00:59:17

-------------------------------------------------------------------------------------------------------------------------------------------------------------Total15 kB/s | 485 MB 08:54:46Retrieving key fromfile:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Importing GPG key 0x5072E1F5:

Userid :"MySQL Release Engineering "Fingerprint: a4a94068 76fc bd3c 4567 70c8 8c71 8d3b 5072e1f5

Package : mysql80-community-release-el7-3.noarch (installed)

From :/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Warning: RPMDB altered outside ofyum.** Found 5 pre-existing rpmdb problem(s), 'yum check'output follows:

containerd.io-1.2.6-3.3.el7.x86_64 has installed conflicts containerd: containerd.io-1.2.6-3.3.el7.x86_64

containerd.io-1.2.6-3.3.el7.x86_64 has installed conflicts runc: containerd.io-1.2.6-3.3.el7.x86_64

ipa-client-4.4.0-12.el7.centos.x86_64 has installed conflicts freeipa-client: ipa-client-4.4.0-12.el7.centos.x86_64

ipa-client-common-4.4.0-12.el7.centos.noarch has installed conflicts freeipa-client-common: ipa-client-common-4.4.0-12.el7.centos.noarch

ipa-common-4.4.0-12.el7.centos.noarch has installed conflicts freeipa-common: ipa-common-4.4.0-12.el7.centos.noarch

Updating :1:openssl-libs-1.0.2k-19.el7.x86_64 1/12Installing : mysql-community-common-8.0.19-1.el7.x86_64 2/12Installing : mysql-community-libs-8.0.19-1.el7.x86_64 3/12Installing : mysql-community-libs-compat-8.0.19-1.el7.x86_64 4/12Installing : mysql-community-client-8.0.19-1.el7.x86_64 5/12Installing : mysql-community-server-8.0.19-1.el7.x86_64 6/12Updating :2:postfix-2.10.1-7.el7.x86_64 7/12Updating :1:openssl-1.0.2k-19.el7.x86_64 8/12Cleanup :2:postfix-2.10.1-6.el7.x86_64 9/12Erasing :1:mariadb-libs-5.5.52-1.el7.x86_64 10/12Cleanup :1:openssl-1.0.1e-60.el7.x86_64 11/12Cleanup :1:openssl-libs-1.0.1e-60.el7.x86_64 12/12Verifying : mysql-community-libs-8.0.19-1.el7.x86_64 1/12Verifying :2:postfix-2.10.1-7.el7.x86_64 2/12Verifying : mysql-community-libs-compat-8.0.19-1.el7.x86_64 3/12Verifying : mysql-community-client-8.0.19-1.el7.x86_64 4/12Verifying :1:openssl-1.0.2k-19.el7.x86_64 5/12Verifying : mysql-community-common-8.0.19-1.el7.x86_64 6/12Verifying : mysql-community-server-8.0.19-1.el7.x86_64 7/12Verifying :1:openssl-libs-1.0.2k-19.el7.x86_64 8/12Verifying :1:openssl-libs-1.0.1e-60.el7.x86_64 9/12Verifying :1:mariadb-libs-5.5.52-1.el7.x86_64 10/12Verifying :2:postfix-2.10.1-6.el7.x86_64 11/12Verifying :1:openssl-1.0.1e-60.el7.x86_64 12/12Installed:

mysql-community-libs.x86_64 0:8.0.19-1.el7 mysql-community-libs-compat.x86_64 0:8.0.19-1.el7 mysql-community-server.x86_64 0:8.0.19-1.el7

Dependency Installed:

mysql-community-client.x86_64 0:8.0.19-1.el7 mysql-community-common.x86_64 0:8.0.19-1.el7

Dependency Updated:

openssl.x86_641:1.0.2k-19.el7 openssl-libs.x86_64 1:1.0.2k-19.el7 postfix.x86_64 2:2.10.1-7.el7

Replaced:

mariadb-libs.x86_64 1:5.5.52-1.el7

Complete![root@localhost mysql_dmp]#

惨了，安装错版本了 rpm -e 卸载掉吧。

也可以通过rpm包的方式安装，我的虚拟机是centos7 安装mysql5.7，需挑选版本mysql-5.7.23-1.el7.x86_64.rpm-bundle

一开始不清楚，选择了mysql-5.7.27-1.fc30.x86_64.rpm-bundle，结果安装时提示各种依赖包，比如：

[root@localhost mysql_dmp]# rpm -ivh mysql-community-server-5.7.27-1.fc30.x86_64.rpm

error: Failed dependencies:

libc.so.6(GLIBC_2.28)(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libcrypt.so.2()(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libcrypt.so.2(XCRYPT_2.0)(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libm.so.6(GLIBC_2.29)(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libmecab.so.2()(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libstdc++.so.6(GLIBCXX_3.4.20)(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libstdc++.so.6(GLIBCXX_3.4.21)(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libstdc++.so.6(GLIBCXX_3.4.26)(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libtirpc.so.3()(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

libtirpc.so.3(TIRPC_0.3.0)(64bit) is needed by mysql-community-server-5.7.27-1.fc30.x86_64

mecab-ipadic is needed by mysql-community-server-5.7.27-1.fc30.x86_64

mysql-community-client(x86-64) >= 5.7.9 is needed by mysql-community-server-5.7.27-1.fc30.x86_64

网上查资料，好像GLIBC升级很麻烦，而且可能会出问题。

使用mysql-5.7.23-1.el7.x86_64.rpm-bundle就没这些问题了

安装顺序如下：

[root@localhost mysqlrpm]# rpm -ivh mysql-community-common-5.7.25-1.el7.x86_64.rpm

Preparing... ################################# [100%]

Updating/installing...1:mysql-community-common-5.7.25-1.e################################# [100%]

[root@localhost mysqlrpm]# rpm-ivh mysql-community-libs-5.7.25-1.el7.x86_64.rpm

Preparing... ################################# [100%]

Updating/installing...1:mysql-community-libs-5.7.25-1.el7################################# [100%]

[root@localhost mysqlrpm]# rpm-ivh mysql-community-client-5.7.25-1.el7.x86_64.rpm

Preparing... ################################# [100%]

Updating/installing...1:mysql-community-client-5.7.25-1.e################################# [100%]

[root@localhost mysqlrpm]# rpm-ivh mysql-community-server-5.7.25-1.el7.x86_64.rpm

Preparing... ################################# [100%]

Updating/installing...1:mysql-community-server-5.7.25-1.e################################# [100%]

启动报错了：

[root@localhost mysqlrpm]# systemctl status mysqld

● mysqld.service-MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: inactive (dead)

Docs:man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Mar08 18:56:12 localhost.localdomain systemd[1]: Starting MySQL Server...

Mar08 18:56:46 localhost.localdomain systemd[1]: Started MySQL Server.

Mar08 20:41:45 localhost.localdomain systemd[1]: Stopping MySQL Server...

Mar08 20:41:47 localhost.localdomain systemd[1]: Stopped MySQL Server.

[root@localhost mysqlrpm]# systemctl start mysqld

Jobfor mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" fordetails.

[root@localhost mysqlrpm]# systemctl status mysqld

● mysqld.service-MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: failed (Result: start-limit) since Mon 2020-03-09 02:41:37PDT; 1min 2s ago

Docs:man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 9276 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)

Process:9255 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Mar09 02:41:37 localhost.localdomain systemd[1]: mysqld.service: control process exited, code=exited status=1Mar09 02:41:37 localhost.localdomain systemd[1]: Failed to start MySQL Server.

Mar09 02:41:37 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.

Mar09 02:41:37 localhost.localdomain systemd[1]: mysqld.service failed.

Mar09 02:41:37 localhost.localdomain systemd[1]: mysqld.service holdoff timeover, scheduling restart.

Mar09 02:41:37 localhost.localdomain systemd[1]: start request repeated too quickly formysqld.service

Mar09 02:41:37 localhost.localdomain systemd[1]: Failed to start MySQL Server.

Mar09 02:41:37 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.

Mar09 02:41:37 localhost.localdomain systemd[1]: mysqld.service failed.

[root@localhost mysqlrpm]#

这个报错日志没看出什么，还是得看mysql日志rpm安装，一般在/var/log/mysqld.log

[root@localhost mysqlrpm]# vim /var/log/mysqld.log2020-03-09T09:52:57.178704Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2020-03-09T09:52:57.178707Z 0[Note] InnoDB: Using Linux native AIO2020-03-09T09:52:57.178869Z 0 [Note] InnoDB: Number of pools: 1

2020-03-09T09:52:57.178932Z 0[Note] InnoDB: Using CPU crc32 instructions2020-03-09T09:52:57.180265Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size =128M2020-03-09T09:52:57.264042Z 0[Note] InnoDB: Completed initialization of buffer pool2020-03-09T09:52:57.312551Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the manpage of setpriority().2020-03-09T09:52:57.337858Z 0 [ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata1 are 0x4800!

2020-03-09 02:52:57 0x7f95658b7740 InnoDB: Assertion failure in thread 140279630493504 in file ut0ut.cc line 942InnoDB: We intentionally generate a memory trap.

InnoDB: Submit a detailed bug report to http://bugs.mysql.com.

InnoDB: If you get repeated assertion failures or crashes, even

InnoDB: immediately after the mysqld startup, there may be

InnoDB: corruptioninthe InnoDB tablespace. Please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

InnoDB: about forcing recovery.09:52:57 UTC - mysqld got signal 6;

This could be because you hit a bug. It is also possible that this binary

or one of the libraries it was linked against is corrupt, improperly built,

or misconfigured. This error can also be caused by malfunctioning hardware.

Attempting to collect some information that could help diagnose the problem.

As this is a crash and something is definitely wrong, the information

collection process might fail.

把error拿到百度搜下，好像是因为我之前安装了高版本卸载后安装低版本造成的。

被说对了，我原来安装了mysql8.0，安装卸载改装mysql5.7，然后数据目录存了一些数据，我换成5.7的时候没有把数据删掉，再重新启动的时候，数据目录还是指向原来的数据，版本的数据格式不一样，清空数据目录的数据，数据目录默认是/var/lib/mysql/，找不到可以看看配置文件

[root@localhost mysql_dmp]# mkdir /var/lib/mysql_bak

[root@localhost mysql_dmp]#mv /var/lib/mysql/auto.cnf ca.pem ibdata1 mysql/public_key.pem undo_001

binlog.000001 client-cert.pem ib_logfile0 mysql.ibd server-cert.pem undo_002

binlog.index client-key.pem ib_logfile1 performance_schema/ server-key.pem

ca-key.pem ib_buffer_pool #innodb_temp/ private_key.pem sys/[root@localhost mysql_dmp]#mv /var/lib/mysql/*/var/lib/mysql_bak/

[root@localhost mysql_dmp]# ls /var/lib/mysql

[root@localhost mysql_dmp]# ls /var/lib/mysql_bak/

auto.cnf ca-key.pem client-key.pem ib_logfile0 mysql private_key.pem server-key.pem undo_002

binlog.000001 ca.pem ib_buffer_pool ib_logfile1 mysql.ibd public_key.pem sys

binlog.index client-cert.pem ibdata1 #innodb_temp performance_schema server-cert.pem undo_001

[root@localhost mysql_dmp]#

情况数据后，便启动成功了！

[root@localhost mysql_dmp]# systemctl start mysqld

[root@localhost mysql_dmp]#ls /var/lib/mysql

auto.cnf ca.pem client-key.pem ibdata1 ib_logfile1 mysql mysql.sock.lock private_key.pem server-cert.pem sys

ca-key.pem client-cert.pem ib_buffer_pool ib_logfile0 ibtmp1 mysql.sock performance_schema public_key.pem server-key.pem

[root@localhost mysql_dmp]# systemctl status mysqld

● mysqld.service-MySQL Server

Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)

Active: active (running) since Mon2020-03-09 03:13:36PDT; 12s ago

Docs:man:mysqld(8)

http://dev.mysql.com/doc/refman/en/using-systemd.html

Process: 10286 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)

Process:10205 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)

Main PID:10289(mysqld)

CGroup:/system.slice/mysqld.service

└─10289 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Mar09 03:13:30 localhost.localdomain systemd[1]: Starting MySQL Server...

Mar09 03:13:36 localhost.localdomain systemd[1]: Started MySQL Server.

[root@localhost mysql_dmp]#

有个小尴尬的地方，using password：YES or NO，其实是说明有没输入密码，一开始还以为是输入的密码正不正确。

[root@localhost mysql_dmp]# mysql -uroot -p

Enter password:

ERROR1045 (28000): Access denied for user 'root'@'localhost'(using password: NO)

[root@localhost mysql_dmp]# mysql-uroot -p

Enter password:

ERROR1045 (28000): Access denied for user 'root'@'localhost'(using password: YES)

[root@localhost mysql_dmp]#

log里有root随机密码，登陆成功！

[root@localhost mysql_dmp]# grep 'temporary password' /var/log/mysqld.log2020-03-09T01:56:40.997761Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kSZ#Q:-kp3wX2020-03-09T10:13:31.622511Z 1 [Note] A temporary password is generated for root@localhost: >05:NUhz.>++[root@localhost mysql_dmp]# mysql-uroot -p>05:NUhz.>++Enter password:

[root@localhost mysql_dmp]# mysql-uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

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 clearthe current input statement.

mysql>

随机密码很复杂，改成简单密码

mysql>use mysql;

ERROR1820(HY000): You must reset your password using ALTER USER statement before executing this statement.#必须要先修改密码。。。mysql> SET PASSWORD = PASSWORD('123456');#MySQL版本5.7.6版本以前用户可以使用

ERROR1819(HY000): Your password does not satisfy the current policy requirements#密码策略

mysql> ALTER USER USER() IDENTIFIED BY '123456';#MySQL版本5.7.6版本开始的用户可以使用

ERROR1819(HY000): Your password does not satisfy the current policy requirements#密码策略

mysql> SET PASSWORD = PASSWORD('1q2w3e4r');#这也不行。。。

ERROR1819(HY000): Your password does not satisfy the current policy requirements

mysql> SHOW VARIABLES LIKE 'validate_password%';

ERROR1820(HY000): You must reset your password using ALTER USER statement before executing this statement.#还不给修改策略了

mysql> SET PASSWORD = PASSWORD('kSZ#Q:-kp3wX');#先修改个复杂的吧。

Query OK,0 rows affected, 1 warning (0.00sec)

mysql>flush privileges;#刷新权限表

Query OK,0 rows affected (0.00sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';+--------------------------------------+--------+

| Variable_name | Value |

+--------------------------------------+--------+

| validate_password_check_user_name | OFF |

| validate_password_dictionary_file | |

| validate_password_length | 8 |

| validate_password_mixed_case_count | 1 |

| validate_password_number_count | 1 |

| validate_password_policy | MEDIUM |

| validate_password_special_char_count | 1 |

+--------------------------------------+--------+

7 rows in set (0.01sec)

mysql> set global validate_password_policy=LOW;#改成Low，那mysql就只校验长度了

Query OK,0 rows affected (0.00sec)

mysql> set global validate_password_length=6;#把密码长度改成6

Query OK,0 rows affected (0.00sec)

mysql>use mysql;#总算进入mysql库了

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> update user set password=password('123456') where user='root';#修改root密码，记得用password函数加密

ERROR1054 (42S22): Unknown column 'password' in 'field list' #没有password这个fieldmysql>show tables;+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

31 rows in set (0.00sec)

mysql> select t.* from user t where t.User=root;

ERROR1054 (42S22): Unknown column 'root' in 'where clause'mysql> select t.* from user t where t.User='root';+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

| localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *236C13AD6F120849B1AB0C6C131A37F8C15AF3F1 | N | 2020-03-09 03:29:44 | NULL | N |

+-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+

1 row in set (0.00sec)

mysql> update user set authentication_string = password('123456'),password_last_changed=now() where user='root';#原来改成authentication_string了

Query OK,1 row affected, 1 warning (0.00sec)

Rows matched:1 Changed: 1 Warnings: 1mysql>flush privileges;

Query OK,0 rows affected (0.00 sec)

接下来使用数据库连接工具，在交互界面看了乱(不过有些生产环境，为了安全考虑，只允许在交互界面查询sql)。

mysql默认不允许远程主机，修改下，%表示允许所有主机连接。

mysql>use mysql;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> update user set host ='%' where user ='root';