MySQL介绍及安装

数据库的类别与管理

数据库软件DBMS(数据库管理系统)

  1. 数据库通过DBMS来创建和操作,不直接访问数据库通过DBMS访问数据,让DBMS帮助我们访问数据库。

关系型数据库RDMS

  1. RDBMS 建立在关系型模型上的数据库
  2. 关系型数据库的主要产品:
    1. Oracle:在以前的大型项目中使用,例如银行、电信等项目
    2. mysql:web时代使用最广泛的关系型数据库
    3. ms sql server:在微软的项目中使用
    4. sqlite:轻量级数据库,主要应用在移动平台
  3. 非关系型数据库:Radis、MongoDB、Hbase……
关系型数据库的核心元素
  1. 数据行(一条记录)
  2. 数据列(字段)
  3. 数据表(数据行的集合)
  4. 数据库(数据库表的集合,一个数据库中能够有N多个数据库)

MySQL版本

  1. MySQL版本有5.6 5.7
  2. 后来被Oracle收购后就从5.7版本直接跳到8版本,直接跳过中间的6、7两个版本
MySQL 8的新特性
  1. 数据字典MySQL 8.0包含一个事务数据字典,用于存储有关数据库对象的信息。在MySQL 8.0之前的版本中,字典数据存储在元数据文件和非事务表中。
  2. 原子数据定义语句,MySQL 8.0支持原子数据定义语言(DDL)语句。
  3. 安全和账户管理
    1. MySQL 8.0通过以下功能增强数据库的安全,并在账户管理中实现更高的DBA灵活性。
    2. MySQL 8.0开始支持角色,角色可以看成是一些权限的集合,为用户赋予统一的角色,权限的修改直接通过角色来进行,无须为每个用户单独授权。管理员可以创建和删除角色。
    3. MySQL 8.0 允许账户具有双密码,从而在多服务器系统中无缝执行分阶段密码更改,无需停机。
  4. 默认字符集已经由latin1更改为utf8mb4
  5. zabbix 指定字符集
  6. 增强JSON功能

MySQL以外的数据库

Mariadb
  1. Mariadb由MySQL创始人麦克尔.维德纽斯主导开发的
  2. Mariadb在使用方面与MySQL5.1相似,Mariadb比较少企业使用,主打还是MySQL和Oracle
Percona
  1. Percona是由领先的MySQL咨询公司Percona发布的,其可以完全兼容MySQL

MySQL的架构

  1. MySQL是由c++语言编译的,使用了多种编译器测试,能够保证源码的移植性

数据库系统的构成

  1. 数据库
  2. 数据库管理系统(及其应用开发工具)
  3. 应用程序
  4. 数据库管理员

数据库配置文件

  1. /etc/my.cnf:主配置文件
  2. /var/lig/mysql:yum安装下就是数据库目录
  3. /usr/local/:用二进制安装下默认的数据库目录
  4. 进程名:
    1. yum安装的进程名是mysqld
    2. 二进制安装的进程名是mysql.server
  5. 传输协议:TCP
  6. 进程所有者:mysql
  7. 进程所有组:mysql
  8. 错误日志:
    1. 错误日志一般会在/var/log/mysql.log
    2. 二进制的错误日志是在 /usr/local/mysql/err.log

数据库升级注意事项

  1. 升级数据库只能小版本升级,版本之间不能跨度太大
    例如:
    5.5.57->5.6.48->5.6.37->5.7.19->5.7.30->8.0.19 小版本逐步升级

数据库工具

  1. sqlyog
  2. nevicat

MySQL 5.7安装

实验

实验环境

  1. 系统:CentOS7
  2. MySQL版本:5.7
  3. 端口号:3306
  4. 本地源换成阿里源
  5. 安装前的检查
    1. 系统当中有没有之前遗留的MySQL或者Mariadb
    2. 系统当中有没有遗留与老版本有关的MySQL文件
  6. 下载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

处理报错方法一:
  1. 出现报错后,在"/etc/yum.repos.d/mysql-community.repo"下修改
  2. 修改后重新安装 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
处理报错方法二:
  1. 把源更新到最新的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>
  1. 修改密码后重新登录,新密码不需要加引号
[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 安装

安装步骤

  1. 下载MySQL 8 的源
  2. 更新MySQL源
  3. 安装MySQL 8软件包
  4. 登录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

二进制包安装步骤

  1. 下载二进制包
  2. 通过wget下载二进制MySQL源
  3. 创建用户及组
  4. 授权用户和组
  5. 在/etc/profile/ 配置文件中添加字段,并使其生效
  6. 初始化数据库
  7. 把启动脚本移动到对应的启动目录
  8. 启动后日志会默认输出到"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 327 2020 bin
drwxr-xr-x.  2 mysql mysql     55 327 2020 docs
drwxr-xr-x.  3 mysql mysql   4096 327 2020 include
drwxr-xr-x.  6 mysql mysql    201 327 2020 lib
-rw-r--r--.  1 mysql mysql 404604 326 2020 LICENSE
drwxr-xr-x.  4 mysql mysql     30 327 2020 man
-rw-r--r--.  1 mysql mysql    687 326 2020 README
drwxr-xr-x. 28 mysql mysql   4096 327 2020 share
drwxr-xr-x.  2 mysql mysql     77 327 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/
请添加图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值