来源

W3C
菜鸟教程
C语言中文网

基本知识

mysql是一种关系型数据库管理系统,
MySQL是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。

什么是数据库
rebms术语

安装

由于centos7不支持免费mysql ,

卸载mariadb

[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64
[root@localhost ~]# rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64
[root@localhost ~]# rpm -e mariadb-libs-5.5.64-1.el7.x86_64
错误:未安装软件包 mariadb-libs-5.5.64-1.el7.x86_64
[root@localhost ~]# rpm -qa | grep mariadb
[root@localhost ~]#

安装依赖

vim ,libaio ,net-tools

下载安装软件

下载地址 :https://dev.mysql.com/downloads/mysql/5.7.html#downloads
下载

安装

[root@diudiu ~]# wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
--2020-02-16 00:59:14--  https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
正在解析主机 dev.mysql.com (dev.mysql.com)... 137.254.60.11
正在连接 dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 302 Found
位置:https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar [跟随至新的 URL]
--2020-02-16 00:59:17--  https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar
正在解析主机 cdn.mysql.com (cdn.mysql.com)... 184.26.157.95
正在连接 cdn.mysql.com (cdn.mysql.com)|184.26.157.95|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:545832960 (521M) [application/x-tar]
正在保存至: “mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar.1”

 0% [                                                                                       ] 756,961     16.4KB/s 剩余 3h 50m ^

[root@diudiu mysql]# tar -xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar 
mysql-community-embedded-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-test-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-5.7.29-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-5.7.29-1.el7.x86_64.rpm
mysql-community-client-5.7.29-1.el7.x86_64.rpm
mysql-community-server-5.7.29-1.el7.x86_64.rpm
mysql-community-devel-5.7.29-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpm
mysql-community-common-5.7.29-1.el7.x86_64.rpm

    依次安装
1.[root@diudiu mysql]# rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm 
警告:mysql-community-common-5.7.29-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-5.7.29-1.e################################# [100%]
[root@diudiu mysql]# 
2.[root@diudiu mysql]# rpm -ivh mysql-community-libs-5.7.29-1.el7.x86_64.rpm 
警告:mysql-community-libs-5.7.29-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-5.7.29-1.el7################################# [100%]
3.[root@diudiu mysql]# rpm -ivh mysql-community-client-5.7.29-1.el7.x86_64.rpm 
警告:mysql-community-client-5.7.29-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-5.7.29-1.e################################# [100%]
[root@diudiu mysql]# 
4.[root@diudiu mysql]# rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm 
警告:mysql-community-server-5.7.29-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-server-5.7.29-1.e################################# [100%]

数据库目录:/var/lib/mysql/
命令配置:/usr/share/mysql  --- mysql.server命令及配置文件
相关命令:/usr/bin   --- mysqladmin mysqldump等命令
启动脚本:/etc/rc.d/init.d/ --- 启动脚本文件mysql的目录
系统配置:/etc/my.conf

初始化

查看/var/lib/mysql目录,如果目录为空则说明数据库没有初始化

[root@diudiu mysql]# ll /var/lib/mysql/
总用量 0

启动MySQL
[root@diudiu mysql]# systemctl start mysqld.service

运行MySQL,报错:

[root@diudiu ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

解决办法

[root@diudiu ~]# cat /var/log/mysqld.log |grep password
2020-02-15T18:04:27.453933Z 1 [Note] A temporary password is generated for root@localhost: &hT99y9pUNy%
2020-02-15T18:08:34.774583Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2020-02-15T18:10:41.681089Z 0 [Note] Shutting down plugin 'validate_password'
2020-02-15T18:10:43.095175Z 0 [Note] Shutting down plugin 'sha256_password'
2020-02-15T18:10:43.095177Z 0 [Note] Shutting down plugin 'mysql_native_password'
2020-02-15T18:13:42.533638Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2020-02-15T18:13:43.938739Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2020-02-15T18:13:45.490207Z 4 [Note] Access denied for user 'root'@'localhost' (using password: NO)
……

网上说,为了加强安全性,MySQL5.7为root用户随机生成了一个密码,在error log中,关于errorlog的位置,如果安装的是RPM包,则默认是/var/log/mysqld.log。

A temporary password is generated for root@localhost: &hT99y9pUNy%

[root@diudiu ~]# mysql -u root -p 
Enter password: &hT99y9pUNy%
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.29

Copyright (c) 2000, 2020, 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> 

修改root密码

报错,必须马上修改新的密码
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

报错,提示密码不符合要求
mysql> alter user 'root'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

更改密码复杂程度

更改validate_password_policy的值
值
zhi

mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

成功。

验证安装

[root@diudiu ~]# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.7.29, for Linux on x86_64

简单sql命令

[root@diudiu ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.29 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> 

MySQL管理

关闭 MySQL 服务器

查看进程

[root@diudiu ~]# ps -ef |grep mysqld
root       3770 114574  0 15:32 pts/0    00:00:00 grep --color=auto mysqld
mysql     62565      1  0 14:31 ?        00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

关闭目前运行的MySQL服务器

[root@diudiu ~]# cd /usr/bin/
[root@diudiu bin]# ./mysqladmin -u root -p shutdown
Enter password: 
[root@diudiu bin]# ps -ef |grep mysqld
root       5395 114574  0 15:34 pts/0    00:00:00 grep --color=auto mysqld