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.11 Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:80... connected. HTTP request sent, awaiting response... 301 Moved 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... 302 Found 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.40 Connecting to repo.mysql.com (repo.mysql.com)|72.247.97.40|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 25548 (25K) [application/x-redhat-package-manager] Saving to: ‘mysql57-community-release-el7-10.noarch.rpm’ 100%[===================================================================================================================>] 25,548 --.-K/s in 0s 2020-03-04 23:36:50 (133 MB/s) - ‘mysql57-community-release-el7-10.noarch.rpm’ saved [25548/25548] -c: No such file or directory No URLs found in -c. FINISHED --2020-03-04 23:36:50-- Total wall clock time: 4.0s Downloaded: 1 files, 25K in 0s (133 MB/s) [root@localhost mysql_dmp]# ls mysql57-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-10 will 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.0 M 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.3 M replacing mariadb-libs.x86_64 1:5.5.52-1.el7 mysql-community-server x86_64 8.0.19-1.el7 mysql80-community 436 M Installing for dependencies: mysql-community-client x86_64 8.0.19-1.el7 mysql80-community 41 M mysql-community-common x86_64 8.0.19-1.el7 mysql80-community 605 k Updating for dependencies: openssl x86_64 1:1.0.2k-19.el7 base 493 k openssl-libs x86_64 1:1.0.2k-19.el7 base 1.2 M postfix x86_64 2:2.10.1-7.el7 base 2.4 M Transaction Summary ============================================================================================================================================================= Install 3 Packages (+2 Dependent packages) Upgrade ( 3 Dependent packages) Total size: 487 M Total download size: 485 M Downloading packages: No Presto metadata available for base 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 key for 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:01 mysql-community-client-8.0.19- FAILED ] 12 kB/s | 20 MB 11:05:37 ETA 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 on http://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 ------------------------------------------------------------------------------------------------------------------------------------------------------------- Total 15 kB/s | 485 MB 08:54:46 Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql Importing GPG key 0x5072E1F5: Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>" Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5 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 of yum. ** 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/12 Installing : mysql-community-common-8.0.19-1.el7.x86_64 2/12 Installing : mysql-community-libs-8.0.19-1.el7.x86_64 3/12 Installing : mysql-community-libs-compat-8.0.19-1.el7.x86_64 4/12 Installing : mysql-community-client-8.0.19-1.el7.x86_64 5/12 Installing : mysql-community-server-8.0.19-1.el7.x86_64 6/12 Updating : 2:postfix-2.10.1-7.el7.x86_64 7/12 Updating : 1:openssl-1.0.2k-19.el7.x86_64 8/12 Cleanup : 2:postfix-2.10.1-6.el7.x86_64 9/12 Erasing : 1:mariadb-libs-5.5.52-1.el7.x86_64 10/12 Cleanup : 1:openssl-1.0.1e-60.el7.x86_64 11/12 Cleanup : 1:openssl-libs-1.0.1e-60.el7.x86_64 12/12 Verifying : mysql-community-libs-8.0.19-1.el7.x86_64 1/12 Verifying : 2:postfix-2.10.1-7.el7.x86_64 2/12 Verifying : mysql-community-libs-compat-8.0.19-1.el7.x86_64 3/12 Verifying : mysql-community-client-8.0.19-1.el7.x86_64 4/12 Verifying : 1:openssl-1.0.2k-19.el7.x86_64 5/12 Verifying : mysql-community-common-8.0.19-1.el7.x86_64 6/12 Verifying : mysql-community-server-8.0.19-1.el7.x86_64 7/12 Verifying : 1:openssl-libs-1.0.2k-19.el7.x86_64 8/12 Verifying : 1:openssl-libs-1.0.1e-60.el7.x86_64 9/12 Verifying : 1:mariadb-libs-5.5.52-1.el7.x86_64 10/12 Verifying : 2:postfix-2.10.1-6.el7.x86_64 11/12 Verifying : 1:openssl-1.0.1e-60.el7.x86_64 12/12 Installed: 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_64 1: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 Mar 08 18:56:12 localhost.localdomain systemd[1]: Starting MySQL Server... Mar 08 18:56:46 localhost.localdomain systemd[1]: Started MySQL Server. Mar 08 20:41:45 localhost.localdomain systemd[1]: Stopping MySQL Server... Mar 08 20:41:47 localhost.localdomain systemd[1]: Stopped MySQL Server. [root@localhost mysqlrpm]# systemctl start mysqld Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details. [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:37 PDT; 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) Mar 09 02:41:37 localhost.localdomain systemd[1]: mysqld.service: control process exited, code=exited status=1 Mar 09 02:41:37 localhost.localdomain systemd[1]: Failed to start MySQL Server. Mar 09 02:41:37 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state. Mar 09 02:41:37 localhost.localdomain systemd[1]: mysqld.service failed. Mar 09 02:41:37 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart. Mar 09 02:41:37 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service Mar 09 02:41:37 localhost.localdomain systemd[1]: Failed to start MySQL Server. Mar 09 02:41:37 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state. Mar 09 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.log 2020-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 AIO 2020-03-09T09:52:57.178869Z 0 [Note] InnoDB: Number of pools: 1 2020-03-09T09:52:57.178932Z 0 [Note] InnoDB: Using CPU crc32 instructions 2020-03-09T09:52:57.180265Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2020-03-09T09:52:57.264042Z 0 [Note] InnoDB: Completed initialization of buffer pool 2020-03-09T09:52:57.312551Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page 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 942 InnoDB: 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: corruption in the 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 Mon 2020-03-09 03:13:36 PDT; 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 Mar 09 03:13:30 localhost.localdomain systemd[1]: Starting MySQL Server... Mar 09 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: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [root@localhost mysql_dmp]# mysql -uroot -p Enter password: ERROR 1045 (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.log 2020-03-09T01:56:40.997761Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kSZ#Q:-kp3wX 2020-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. Your MySQL connection id is 7 Server version: 5.7.25 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
随机密码很复杂，改成简单密码
mysql> use mysql; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.#必须要先修改密码。。。 mysql> SET PASSWORD = PASSWORD('123456');#MySQL版本5.7.6版本以前用户可以使用 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements#密码策略 mysql> ALTER USER USER() IDENTIFIED BY '123456';#MySQL版本5.7.6版本开始的用户可以使用 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements#密码策略 mysql> SET PASSWORD = PASSWORD('1q2w3e4r');#这也不行。。。 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> SHOW VARIABLES LIKE 'validate_password%'; ERROR 1820 (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.00 sec) mysql> flush privileges;#刷新权限表 Query OK, 0 rows affected (0.00 sec) 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.01 sec) mysql> set global validate_password_policy=LOW;#改成Low，那mysql就只校验长度了 Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=6;#把密码长度改成6 Query OK, 0 rows affected (0.00 sec) mysql> use mysql;#总算进入mysql库了 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set password=password('123456') where user='root';#修改root密码，记得用password函数加密 ERROR 1054 (42S22): Unknown column 'password' in 'field list' #没有password这个field mysql> 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.00 sec) mysql> select t.* from user t where t.User=root; ERROR 1054 (42S22): Unknown column 'root' in 'where clause' mysql> select t.* from user t where t.User='root'; +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | 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.00 sec) 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.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
接下来使用数据库连接工具，在交互界面看了乱（不过有些生产环境，为了安全考虑，只允许在交互界面查询sql）。
mysql默认不允许远程主机，修改下，%表示允许所有主机连接。
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> update user set host ='%' where user ='root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql>
OK，搞定，可以接下来的学习了~
