1.关系型数据库相关概念
关系Relational ：关系就是二维表，其中：表中的行、列次序并不重要
行row：表中的每一行，又称为一条记录record
列column：表中的每一列，称为属性，字段，域field
主键Primary key：PK ,一个或多个字段的组合, 用于惟一确定一个记录的字段，一张表只有一个主 键, 主键字段不能为空NULL
唯一键Unique key: 一个或多个字段的组合,用于惟一确定一个记录的字段,一张表可以有多个UK,而 且UK字段可以为NULL
域domain：属性的取值范围，如，性别只能是'男'和'女'两个值，人类的年龄只能0-150
2.常用关系数据库
MySQL: MySQL, MariaDB, Percona Server
PostgreSQL: 简称为pgsql，EnterpriseDB
Oracle
MSSQL Server
DB2
3联系类型
一对一联系(1:1)
一对多联系(1:n)：外键
多对多联系(m:n)：增加第三张表3联系类型
4.数据库规划流程
1. 收集数据，得到字段 收集必要且完整的数据项 转换成数据表的字段
2. 把字段分类，归入表，建立表的关联 关联：表和表间的关系 分割数据表并建立关联的优点 节省空间 减少输入错误 方便数据修改
3. 规范化数据库
5.数据库的正规化
在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类 推。一般数据库只需满足第三范式(3NF）即可 规则是死的,人是活的,所以范式是否必须遵守,要看业务需要而定 掌握范式的目的是为了在合适的场景下违反范式
第一范式：1NF
无重复的列，每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有 多个值或者不能有重复的属性，确保每一列的原子性。除去同类型的字段，就是无重复的列 说明：第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据 库
第二范例：2NF
第二范式必须先满足第一范式，属性完全依赖于主键，要求表中的每个行必须可以被唯一地区分，通常 为表加上每行的唯一标识PK，非PK的字段需要与整个PK有直接相关性,即非PK的字段不能依赖于部分主键
第三范式：3NF
满足第三范式必须先满足第二范式属性，非主键属性不依赖于其它非主键属性。第三范式要求一个数据 表中不包含已在其它表中已包含的非主关键字信息，非PK的字段间不能有从属关系
6.MySQL安装方式介绍和快速安装
安装方式
程序包管理器管理的程序包
源代码编译安装
二进制格式的程序包：展开至特定路径，并经过简单配置后即可使用
centos8：安装光盘直接提供
mysql-server：8.0
mariadb-server：10.3.17
centos7：安装光盘直接提供
mariadb-server：5.5 服务包
mariadb 客户端工具包
centos6：
mysql-serve：5.1 服务器包
mysql 客户端工具包
范例
[14:07:38 root@centos7 yum.repos.d]$vim mysql.repo 创建仓库 [mysql] name=mysql5.7 baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/ gpgcheck=0 配置清华大学仓库路径 [14:10:44 root@centos7 yum.repos.d]$yum list |grep 'mysql*' 可以看到包 [14:10:44 root@centos7 yum.repos.d]$yum -y install mysql-community-server [14:19:40 root@centos7 yum.repos.d]$systemctl enable --now mysqld ss -ntl 发现端口3306开启 [14:22:34 root@centos7 yum.repos.d]$mysql 5.7版本会默认创建数据库用户root'@'localhost ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) [14:27:43 root@centos7 yum.repos.d]$grep password /var/log/mysqld.log 临时密码位置 2021-01-28T06:20:26.300014Z 1 [Note] A temporary password is generated for root@localhost: AK:1dY&92qyb 2021-01-28T06:21:55.139910Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO) 2021-01-28T06:22:45.441398Z 3 [Note] Access denied for user 'root'@'localhost' (using password: NO) [14:28:11 root@centos7 yum.repos.d]$mysql -uroot -p'AK:1dY&92qyb' 初始密码登录后会要求你修改密码 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.33 Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER root@'localhost' identified by 'Yangbo!0'; 注意密码要设置的复杂一些 Query OK, 0 rows affected (0.00 sec) mysql> status -------------- mysql Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using EditLine wrapper Connection id: 5 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.33 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 24 min 13 sec Threads: 1 Questions: 22 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 100 Queries per second avg: 0.015 -------------- mysql> exit 修改初始密码吗方法二 [14:45:28 root@centos7 yum.repos.d]$mysqladmin -uroot -p'AK:1dY&92qyb' password 'Yangbo!0'
Ubuntu默认启用，空密码登录
7初始化脚本提高安全性
运行脚本：mysql_secure_installation
设置数据库管理员root口令
禁止root远程登录
删除anonymous用户帐号
删除test数据库
范例：针对5.6版本安全加固
[15:06:20 root@centos7 yum.repos.d]$mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.6.51 MySQL Community Server (GPL) Copyright (c) 2000, 2021, 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> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | | centos7 | | root | centos7 | | | localhost | 匿名账号 | root | localhost | +------+-----------+ 6 rows in set (0.00 sec) 代表6个用户 [15:12:11 root@centos7 yum.repos.d]$mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MySQL to secure it, we'll need the current password for the root user. If you've just installed MySQL, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): 没有密码回车 OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MySQL root user without the proper authorisation. Set root password? [Y/n] y 是否设置root口令 New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y 是否移除匿名用户 ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y 禁用远程登录 ... Success! By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist ... Failed! Not critical, keep moving... - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y 是否生效 ... Success! All done! If you've completed all of the above steps, your MySQL installation should now be secure. Thanks for using MySQL! Cleaning up... [15:18:19 root@centos7 yum.repos.d]$mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 再次空口令登录失败 [15:17:56 root@centos7 yum.repos.d]$mysql -uroot -p'123456' 登录成功 mysql> select user,host from mysql.user; +------+-----------+ | user | host | +------+-----------+ | root | 127.0.0.1 | | root | ::1 | | root | localhost | +------+-----------+ 3 rows in set (0.00 sec) 删除了之前的不安全登录
8.MYSQL组成
客户端程序
mysql: 交互式或非交互式的CLI工具
mysqldump：备份工具，基于mysql协议向mysqld发起查询请求，并将查得的所有数据转换成
insert等写操作语句保存文本文件中
mysqladmin：基于mysql协议管理mysqld
[15:35:39 root@centos7 yum.repos.d]$mysqladmin -uroot -p'Yangbo!0' 用法帮助 ping [15:29:05 root@centos7 yum.repos.d]$mysqladmin -uroot -p'Yangbo!0' ping mysqladmin: [Warning] Using a password on the command line interface can be insecure. mysqld is alive 验证数据库是否正常运行 shutdown [15:29:05 root@centos7 yum.repos.d]$mysqladmin -uroot -p'Yangbo!0' shutdown 安全的关闭数据库，要耐心等待
mysqlimport：数据导入工具
服务器端程序
mysqld_safe
mysqld
mysqld_multi 多实例 ，示例：mysqld_multi --example
用户账户
mysql用户账号由两部分组成：
'USERNAME'@'HOST'
说明： HOST限制此用户可通过哪些远程主机连接mysql服务器 支持使用通配符：
% 匹配任意长度的任意字符,相当于shell中*, 示例: 172.16.0.0/255.255.0.0 或 172.16.%.% _ 匹配任意单个字符
9.mysql 客户端命令
客户端命令：本地执行，每个命令都完整形式和简写格式
mysql> \h, help mysql> \u，use mysql> \s，status mysql> \!，system
服务端命令：通过mysql协议发往服务器执行并取回结果，命令末尾都必须使用命令结束符号，默 认为分号
-A, --no-auto-rehash 禁止补全 -u, --user= 用户名,默认为root -h, --host= 服务器主机,默认为localhost -p, --passowrd= 用户密码,建议使用-p,默认为空密码 -P, --port= 服务器端口 -S, --socket= 指定连接socket文件路径 -D, --database= 指定默认数据库 -C, --compress 启用压缩 -e “SQL“ 执行SQL命令 -V, --version 显示版本 -v --verbose 显示详细信息 --print-defaults 获取程序默认使用的配置
范例：mysql的配置文件，修改提示符
[16:15:51 root@centos7 yum.repos.d]$mysql -uroot -p'Yangbo!0' --prompt="(\\u@\\h) [\\d]>\\_" 临时修改mysql提示符 [16:17:49 root@centos7 ~]$vim /etc/my.cnf [mysql] prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_" 写入配置文件永久保存
范例：配置所有MySQL客户端的自动登录
[16:17:49 root@centos7 ~]$vim /etc/my.cnf [mysql] user=root password='Yangbo!0'
服务器端配置
服务器端配置文件：
/etc/my.cnf #Global选项
/etc/mysql/my.cnf #Global选项
~/.my.cnf #User-specific 选项
配置文件格式：
[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]
格式：
parameter = value
socket地址
ip socket: 监听在tcp的3306端口，支持远程通信 ，侦听3306/tcp端口可以在绑定有一个或全部接 口IP上 unix sock: 监听在sock文件上，仅支持本机通信, 如：/var/lib/mysql/mysql.sock
范例：关闭远程连接，通过socket文件连接
vim /etc/my.cnf [mysqld] skip-networking=1
10.通用二进制格式安装MySQL 5.7
安装相关包
[16:45:03 root@centos7 ~]$yum -y install libaio numactl-libs
用户和组
[16:45:03 root@centos7 ~]$groupadd mysql [16:45:43 root@centos7 ~]$useradd -r -g mysql -s /bin/false mysql
准备程序文件
[16:54:22 root@centos7 ~]$wget http://mirrors.163.com/mysql/Downloads/MySQL-5.7/mysql-5.7.31-linux-glibc2.12 [16:58:44 root@centos7 ~]$tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ [16:59:46 root@centos7 ~]$cd /usr/local/ [16:59:56 root@centos7 local]$ln -s mysql-5.7.31-linux-glibc2.12-x86_64/ mysql [17:03:30 root@centos7 local]$chown -R root:root /usr/local/mysql/
准备环境变量
[17:05:43 root@centos7 local]$echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [17:09:09 root@centos7 local]$. /etc/profile.d/mysql.sh
准备配置文件
[17:13:41 root@centos7 local]$cp /etc/my.cnf{,.bak} 备份原文件 [17:14:31 root@centos7 local]$vim /etc/my.cnf [mysqld] datadir=/data/mysql skip_name_resolve=1 socket=/data/mysql/mysql.sock log-error=/data/mysql/mysql.log pid-file=/data/mysql/mysql.pid [client] socket=/data/mysql/mysql.sock
生成数据库文件，并提取root密码
[17:46:49 root@centos7 /]$mkdir data [17:47:00 root@centos7 /]$mysqld --initialize --user=mysql --datadir=/data/mysql 5.6版本生成数据库/usr/local/mysql/scripts/mysql_install_db --user=mysql -- datadir=/data/mysql --basedir=/usr/local/mysql/ [17:48:51 root@centos7 /]$grep password /data/mysql/mysql.log 2021-01-28T09:47:16.362119Z 1 [Note] A temporary password is generated for root@localhost: R&d0iVsQ,564 初始密码 [17:53:37 root@centos7 /]$awk '/temporary password/{print $11}' /data/mysql/mysql.log R&d0iVsQ,564
准备服务脚本和启动
[17:53:54 root@centos7 /]$cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [18:01:31 root@centos7 /]$chkconfig --add mysqld [18:01:40 root@centos7 /]$service mysqld start
修改口令
[18:01:51 root@centos7 /]$mysqladmin -uroot -p'R&d0iVsQ,564' password yangbo [18:05:06 root@centos7 /]$mysql -uroot -p'yangbo' 登录成功
11.源码编译安装mysql-5.6.51
安装相关依赖包
yum -y install gcc gcc-c++ cmake bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl-Data-Dumper
准备用户和数据目录
useradd -r -s /sbin/nologin -d /data/mysql mysql
准备用户和数据目录
[18:25:56 root@centos7 ~]$mkdir -p /data/mysql [18:26:24 root@centos7 data]$chown mysql:mysql /data/mysql
下载源码包并解压缩源码包
[18:21:06 root@centos7 ~]$tar -xf mysql-5.6.51.tar.gz -C /usr/local/src [18:27:03 root@centos7 data]$cd /usr/local/src/mysql-5.6.51/ [18:52:44 root@centos7 mysql-5.6.51]$cmake . \ -DCMAKE_INSTALL_PREFIX=/apps/mysql \ -DMYSQL_DATADIR=/data/mysql/ \ -DSYSCONFDIR=/etc/ \ -DMYSQL_USER=mysql \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 \ -DWITHOUT_MROONGA_STORAGE_ENGINE=1 \ -DWITH_DEBUG=0 \ -DWITH_READLINE=1 \ -DWITH_SSL=system \ -DWITH_ZLIB=system \ -DWITH_LIBWRAP=0 \ -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci [18:52:44 root@centos7 mysql-5.6.51]$make && make install 如果出错rm -rf CMakeCache.txt
准备环境变量
[18:52:44 root@centos7 mysql-5.6.51]$echo 'PATH=/apps/mysql/bin:$PATH' > /etc/profile.d/mysql.sh [18:59:11 root@centos7 mysql-5.6.51]$. /etc/profile.d/mysql.sh
生成数据库文件
[18:59:33 root@centos7 mysql-5.6.51]$cd /apps/mysql/ [19:01:20 root@centos7 mysql]$scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql
准备配置文件
[19:01:20 root@centos7 mysql]$cp -b /apps/mysql/support-files/my-default.cnf /etc/my.cnf cp: overwrite ‘/etc/my.cnf’? y
准备启动脚本，并启动服务
[19:03:28 root@centos7 mysql]$cp /apps/mysql/support-files/mysql.server /etc/init.d/mysqld [19:09:23 root@centos7 mysql]$chkconfig --add mysqld [19:09:32 root@centos7 mysql]$service mysqld start
安全初始化
mysql_secure_installation