数据库的类别与管理
数据库软件DBMS(数据库管理系统)
- 数据库通过DBMS来创建和操作,不直接访问数据库通过DBMS访问数据,让DBMS帮助我们访问数据库。
关系型数据库RDMS
- RDBMS 建立在关系型模型上的数据库
- 关系型数据库的主要产品:
1. Oracle:在以前的大型项目中使用,例如银行、电信等项目
2. mysql:web时代使用最广泛的关系型数据库
3. ms sql server:在微软的项目中使用
4. sqlite:轻量级数据库,主要应用在移动平台 - 非关系型数据库:Radis、MongoDB、Hbase……
关系型数据库的核心元素
- 数据行(一条记录)
- 数据列(字段)
- 数据表(数据行的集合)
- 数据库(数据库表的集合,一个数据库中能够有N多个数据库)
MySQL版本
- MySQL版本有5.6 5.7
- 后来被Oracle收购后就从5.7版本直接跳到8版本,直接跳过中间的6、7两个版本
MySQL 8的新特性
- 数据字典MySQL 8.0包含一个事务数据字典,用于存储有关数据库对象的信息。在MySQL 8.0之前的版本中,字典数据存储在元数据文件和非事务表中。
- 原子数据定义语句,MySQL 8.0支持原子数据定义语言(DDL)语句。
- 安全和账户管理
1. MySQL 8.0通过以下功能增强数据库的安全,并在账户管理中实现更高的DBA灵活性。
2. MySQL 8.0开始支持角色,角色可以看成是一些权限的集合,为用户赋予统一的角色,权限的修改直接通过角色来进行,无须为每个用户单独授权。管理员可以创建和删除角色。
3. MySQL 8.0 允许账户具有双密码,从而在多服务器系统中无缝执行分阶段密码更改,无需停机。 - 默认字符集已经由latin1更改为utf8mb4
- zabbix 指定字符集
- 增强JSON功能
MySQL以外的数据库
Mariadb
- Mariadb由MySQL创始人麦克尔.维德纽斯主导开发的
- Mariadb在使用方面与MySQL5.1相似,Mariadb比较少企业使用,主打还是MySQL和Oracle
Percona
- Percona是由领先的MySQL咨询公司Percona发布的,其可以完全兼容MySQL
MySQL的架构
- MySQL是由c++语言编译的,使用了多种编译器测试,能够保证源码的移植性
数据库系统的构成
- 数据库
- 数据库管理系统(及其应用开发工具)
- 应用程序
- 数据库管理员
数据库配置文件
- /etc/my.cnf:主配置文件
- /var/lig/mysql:yum安装下就是数据库目录
- /usr/local/:用二进制安装下默认的数据库目录
- 进程名:
- yum安装的进程名是mysqld
- 二进制安装的进程名是mysql.server
- 传输协议:TCP
- 进程所有者:mysql
- 进程所有组:mysql
- 错误日志:
- 错误日志一般会在/var/log/mysql.log
- 二进制的错误日志是在 /usr/local/mysql/err.log
数据库升级注意事项
- 升级数据库只能小版本升级,版本之间不能跨度太大
例如:
5.5.57->5.6.48->5.6.37->5.7.19->5.7.30->8.0.19 小版本逐步升级
数据库工具
- sqlyog
- nevicat
MySQL 5.7安装
实验
实验环境
- 系统:CentOS7
- MySQL版本:5.7
- 端口号:3306
- 本地源换成阿里源
- 安装前的检查
1. 系统当中有没有之前遗留的MySQL或者Mariadb
2. 系统当中有没有遗留与老版本有关的MySQL文件 - 下载rpm包,生成MySQL.repo源
[root@sunlit5 ~]# rpm -qa mysql
[root@sunlit5 ~]# rpm -qa mariadb
[root@sunlit5 yum.repos.d]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@sunlit5 yum.repos.d]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64
错误:依赖检测失败:
libmysqlclient.so.18()(64bit) 被 (已安裝) postfix-2:2.10.1-9.el7.x86_64 需要
libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-9.el7.x86_64 需要
[root@sunlit5 yum.repos.d]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps #当rpm -e删除不了时,可以使用nodeps把相关文件全删除
[root@sunlit5 ~]# find / -name mysql #查看有没与MySQL有关或名为MySQL的文件
find: ‘/run/user/1000/gvfs’: 权限不够
/etc/selinux/targeted/active/modules/100/mysql
/usr/lib64/mysql
/usr/share/mysql
[root@sunlit5 ~]# rm -rf /etc/selinux/targeted/active/modules/100/mysql
[root@sunlit5 ~]# rm -rf /usr/lib64/mysql
[root@sunlit5 ~]# rm -rf /usr/share/mysql
[root@sunlit5 ~]# echo $?
0
[root@sunlit5 ~]#
安装MySQL源和MySQL包
[root@sunlit5 yum.repos.d]# wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
#下载MySQL源
[root@sunlit5 yum.repos.d]# ls mysql57-community-release-el7-9.noarch.rpm
mysql57-community-release-el7-9.noarch.rpm
[root@sunlit5 yum.repos.d]#
[root@sunlit5 yum.repos.d]# rpm -ivh mysql57-community-release-el7-9.noarch.rpm
#安装MySQL源
[root@sunlit5 yum.repos.d]# ls mysql
mysql57-community-release-el7-9.noarch.rpm
mysql-community.repo
mysql-community-source.repo
[root@sunlit5 yum.repos.d]#
[root@sunlit5 ~]# yum install mysql-community-server -y
#安装MySQL5.7
解决安装MySQL时的报错
安装MySQL时出现以下报错
mysql-community-client-5.7.40-1.el7.x86_64.rpm 的公钥尚未安装
失败的软件包是:mysql-community-client-5.7.40-1.el7.x86_64
GPG 密钥配置为:file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
处理报错方法一:
- 出现报错后,在"/etc/yum.repos.d/mysql-community.repo"下修改
- 修改后重新安装 mysql-community-server
[root@sunlit5 ~]#
[root@sunlit5 yum.repos.d]#
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0 #把MySQL5.7的检查改为0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
处理报错方法二:
- 把源更新到最新的2022,再重新安装即可
[root@sunlit5 ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
启动MySQL及查看密码
启动及查看密码
[root@sunlit5 yum.repos.d]# systemctl start mysqld
[root@sunlit5 yum.repos.d]# systemctl status mysqld | grep Active
Active: active (running) since 一 2022-11-28 15:58:00 CST; 1min 11s ago
[root@sunlit5 yum.repos.d]#
[root@sunlit5 ~]# grep "temporary password" /var/log/mysqld.log
# 在对应的MySQL日志中查看登录密码
2022-11-28T07:57:58.379894Z 1 [Note] A temporary password is generated for root@localhost: #&+Vs?k6RSk=
[root@sunlit5 ~]#
PS:MySQL不能像Mariadb那样直接敲MySQL进入数据库,直接敲MySQL会出现报错。
[root@sunlit5 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# 会显示密码未使用
[root@sunlit5 ~]#
进入MySQL的方式
方式一:
[root@sunlit5 ~]# grep "temporary password" /var/log/mysqld.log
# 在对应的MySQL日志中查看登录密码
2022-11-28T07:57:58.379894Z 1 [Note] A temporary password is generated for root@localhost: #&+Vs?k6RSk=
[root@sunlit5 ~]#
[root@sunlit5 ~]# mysql -uroot -p
Enter password:
#进入MySQL时,因日志上的密码有空格接在-p参数后面会出现报错;所以只能回车当询问密码时才能黏贴
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.40
Copyright (c) 2000, 2022, 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>
方式二:
[root@sunlit5 ~]# mysql -uroot -p"#&+Vs?k6RSk="
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 2
Server version: 5.7.40
Copyright (c) 2000, 2022, 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>
修改登录密码
mysql> alter user 'root'@'localhost' identified by 'Admin@123';
Query OK, 0 rows affected (0.00 sec)
mysql>
- 修改密码后重新登录,新密码不需要加引号
[root@sunlit5 ~]# mysql -pAdmin@123
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 3
Server version: 5.7.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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>
不同的登录方式
登录指定的数据库
[root@sunlit5 ~]# mysql -p sunlit #这样登录是直接登录到指定的数据库
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.40 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> show tables;
Empty set (0.00 sec)
mysql>
登录密码错误
[root@sunlit5 ~]# mysql -p sunlit
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@sunlit5 ~]#
MySQL 8 安装
安装步骤
- 下载MySQL 8 的源
- 更新MySQL源
- 安装MySQL 8软件包
- 登录MySQL8
[root@sunlit5 yum.repos.d]# wget https://repo.mysql.com//mysql80-community-release-el7-7.noarch.rpm
[root@sunlit5 ~]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
[root@sunlit5 ~]# yum install mysql-community-server -y
[root@sunlit5 ~]# cat /var/log/mysqld.log | grep password
2022-11-28T10:44:42.970454Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: K6rD5k#H(n9>
[root@sunlit5 ~]# mysql -uroot -p"K6rD5k#H(n9>"
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 8
Server version: 8.0.31
Copyright (c) 2000, 2022, 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>
二进制安装MySQL
二进制包安装步骤
- 下载二进制包
- 通过wget下载二进制MySQL源
- 创建用户及组
- 授权用户和组
- 在/etc/profile/ 配置文件中添加字段,并使其生效
- 初始化数据库
- 把启动脚本移动到对应的启动目录
- 启动后日志会默认输出到"error.log"当中
[root@sunlit5 ~]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@sunlit5 ~]# tar -xf mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@sunlit5 ~]# ls
anaconda-ks.cfg mysql-8.0.20-linux-glibc2.12-x86_64
initial-setup-ks.cfg mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz
[root@sunlit5 ~]#
[root@sunlit5 ~]# mv mysql-8.0.20-linux-glibc2.12-x86_64 mysql-8020
[root@sunlit5 ~]# mv mysql-8020 /usr/local/mysql
[root@sunlit5 ~]# cd !$
cd /usr/local/mysql
[root@sunlit5 mysql]# ls
bin docs include lib LICENSE man README share support-files
[root@sunlit5 mysql]#
[root@sunlit5 mysql]# groupadd mysql
[root@sunlit5 mysql]# useradd -r -g mysql -s /bin/false mysql
[root@sunlit5 mysql]# id mysql
uid=988(mysql) gid=1001(mysql) 组=1001(mysql)
[root@sunlit5 mysql]#
[root@sunlit5 mysql]# chown -R mysql:mysql /usr/local/mysql
[root@sunlit5 mysql]# ll
总用量 412
drwxr-xr-x. 2 mysql mysql 4096 3月 27 2020 bin
drwxr-xr-x. 2 mysql mysql 55 3月 27 2020 docs
drwxr-xr-x. 3 mysql mysql 4096 3月 27 2020 include
drwxr-xr-x. 6 mysql mysql 201 3月 27 2020 lib
-rw-r--r--. 1 mysql mysql 404604 3月 26 2020 LICENSE
drwxr-xr-x. 4 mysql mysql 30 3月 27 2020 man
-rw-r--r--. 1 mysql mysql 687 3月 26 2020 README
drwxr-xr-x. 28 mysql mysql 4096 3月 27 2020 share
drwxr-xr-x. 2 mysql mysql 77 3月 27 2020 support-files
[root@sunlit5 mysql]#
[root@sunlit5 ~]# cat !$ | tail -2
cat /etc/profile | tail -2
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
[root@sunlit5 ~]#
[root@sunlit5 ~]# source /etc/profile
[root@sunlit5 ~]# cd /usr/local/mysql
[root@sunlit5 mysql]# mkdir data
[root@sunlit5 mysql]# ls
bin docs lib man share
data include LICENSE README support-files
[root@sunlit5 mysql]# cd bin/
[root@sunlit5 bin]# mysql
mysql mysqldump
mysqladmin mysqldumpslow
mysqlbinlog mysqlimport
mysqlcheck mysqlpump
mysql_config mysql_secure_installation
mysql_config_editor mysqlshow
mysqld mysqlslap
mysqld-debug mysql_ssl_rsa_setup
mysqld_multi mysql_tzinfo_to_sql
mysqld_safe mysql_upgrade
[root@sunlit5 bin]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
[root@sunlit5 ~]# cd /usr/local/mysql
[root@sunlit5 mysql]# ls
bin docs lib man share
data include LICENSE README support-files
[root@sunlit5 mysql]# cd support-files/
[root@sunlit5 support-files]# ls
mysqld_multi.server mysql-log-rotate mysql.server
[root@sunlit5 support-files]# cp mysql.server /etc/init.d/mysql.server
[root@sunlit5 support-files]#
[root@sunlit5 support-files]# service mysql.server start
Starting MySQL.Logging to '/usr/local/mysql/data/error.log'.
SUCCESS!
[root@sunlit5 support-files]# cat /usr/local/mysql/data/error.log
[root@sunlit5 support-files]# mysql -uroot -p"i#Xj3F-9Uyql"
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 8
Server version: 8.0.20
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@sunlit5 support-files]# netstat -antup | grep mysql
tcp6 0 0 :::3306 :::* LISTEN 5307/mysqld
tcp6 0 0 :::33060 :::* LISTEN 5307/mysqld
[root@sunlit5 support-files]#
二进制包下载地址
https://dev.mysql.com/downloads/mysql/