MySQL-01 入门与安装

最新推荐文章于 2024-07-17 11:30:10 发布

惊天动地猪儿虫

最新推荐文章于 2024-07-17 11:30:10 发布

阅读量763

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qui910/article/details/122577318

版权

MySQL 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

文章目录

1 概述
- 1.1 MySQL
- 1.2 MySQL数据库版本与分支
2 安装
3 MySQL体系结构
4 MySQL基础管理

1 概述

数据库技术从传统IT技术开始到现在互联网时代，经历了几个发展阶段。

第一阶段，传统IT行业发展阶段，主要就是RDBMS（关系型数据库），其中包括 Oracle，DB2，SQLServer，MySQL等关系型数据库为主。
第二阶段，是互联网快速发展阶段，传统关系型数据库已经不能满足业务发展，这时开始兴起NoSQL数据库，其中有Redis，MongoDB，ElaticSearch等。
第三阶段，是现在互联网大发展阶段，以前分布在各个子数据中的数据，都合并到一个新型NewSQL数据库中，其中有阿里系的 PalorDB，OB。腾讯的TBSQL，还有pincap的tidb。

网站（http://db-engines.com/en）中有数据库的排名介绍。下面重点讲述：MySQL。

1.1 MySQL

MySQL主要有几个类型的产品，如Oracle的MySQL，开源的MariaDB，和Perconadb。

MySQL现在主流的版本是5.7 和8.0。本次介绍主要使用5.7版本。

官方地址
 中文地址

1.2 MySQL数据库版本与分支

Oracle MySQL 官方MySQL数据库版本用户首先版本
Percona 由前MySQL性能团队成员创建的一家MySQL技术服务公司，拥有自己基于MySQL的分支版本Percona数据库
MariaDB Monty于2009年创建的分支版本
Drizzle 由前MySQL CTO创建的MySQL分支版本，采用标准C++重构各模块

2 安装

MySQL的安装主要有以下几种方式：二进制版本，yum源（ubuntu下可以使用apt-get安装），rpm包，源码包。

2.1 二进制安装

2.1.1 下载

选择：Product Version: 5.7.29 Operating System:Linux - Generic

在ubuntu中使用命令下载:

curl -O https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz

2.1.2 环境准备

sudo mkdir -p /data/mysql/data
sudo mkdir -p /data/mysql/binlog
sudo useradd mysql

2.1.3 安装

sudo mv mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz /usr/local
#解压软件
sudo tar -zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
#建立软连接
sudo ln -s mysql-5.7.29-linux-glibc2.12-x86_64/ mysql
#配置环境变量
sudo vi /etc/profile
	export PATH=/usr/local/mysql/bin:$PATH
source /etc/profile

2.1.4 初始化

#创建无密码root
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
#创建有密码root，注意这里的密码是临时密码，在控制台显示
mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data

# 配置MySQL，MySQL的配置文件一般放置在 /etc目录
cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
log_bin=/data/mysql/binlog/mysql-bin
server_id=7
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
EOF

#准备启动脚本
cp -a ./support-files/mysql.server  /etc/init.d/mysqld
sudo chmod +x /etc/init.d/mysqld

2.1.5 启动

Centos7

使用命令启动

/etc/init.d/mysqld [status|restart|stop|start]

设置开机启动

chkconfig --add mysqld
chkconfig --level 35 mysqld on   
systemctl [status|restart|stop|start] mysql.service

Ubuntu

使用命令启动

/etc/init.d/mysqld [status|restart|stop|start]

ubuntu16.04后， systemctl 代替了 chkconfig，需要将mysqld添加到systemctl服务中

# 设置开机自动启动
sudo update-rc.d -f mysqld defaults 
# 查看mysql运行状态
sudo service mysql status
# 运行mysql
sudo service mysql start
# 结束mysql
sudo service mysql stop
# 取消开机启动
sudo update-rc.d -f mysqld remove

2.1.6 登录检测

使用root用户登录，并修改root密码。

使用mysqladmin命令在命令行指定新密码

root 用户可以使用mysqladmin命令来修改密码，mysqladmin 的语法格式如下：

mysqladmin -u username -h hostname -p password "newpwd"

例如：
mysqladmin -uroot -p passwd 123456

语法参数说明如下：

usermame 指需要修改密码的用户名称，在这里指定为 root 用户；
hostname 指需要修改密码的用户主机名，该参数可以不写，默认是 localhost；
password 为关键字，而不是指旧密码；
newpwd 为新设置的密码，必须用双引号括起来。如果使用单引号会引发错误，可能会造成修改后的密码不是你想要的

修改MySQL数据库的user表

因为所有账户信息都保存在 user 表中，因此可以直接通过修改 user 表来改变 root 用户的密码。root 用户登录到 MySQL 服务器后，可以使用 UPDATE 语句修改 MySQL 数据库的 user 表的 authentication_string 字段，从而修改用户的密码。

使用 UPDATA 语句修改 root 用户密码的语法格式如下：

UPDATE mysql.user set authentication_string = PASSWORD ("rootpwd) WHERE User = "root" and Host="localhost";

新密码必须使用 PASSWORD() 函数来加密。执行UPDATE语句后，需要执行FLUSH PRIVILEGES语句重新加载用户权限。

mysql> update mysql.user set authentication_string = password('123456') where user = 'root';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 
mysql> alter user user() identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye
root@pang-HP:~#

最后测试，直接使用 alter user user() identified by '123456';修改即可。

设置root账号可以远程登录

mysql> use mysql;
Database changed
mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
3 rows in set (0.01 sec)

mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql>

2.2 源码安装

2.3 在线安装

2.4 启动问题

2.4.1 问题1

root@pang-HP:/usr/local/mysql# mysql -uroot -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
root@pang-HP:/usr/local/mysql#

解决：

sudo apt install libncurses5

3 MySQL体系结构

MySQL是典型的C/S架构类型，

3.1 客户端组件

客户端程序：mysql,mysqladmin,mysqldump…
API接口方式：C，Java，.Net，Python…

3.2 连接池组件

提供连接协议：网络Socket（TCP/IP）,Unix套接字文件（/tmp/mysql.sock）
验证模块：验证用户身份（mysql_native_password）
连接线程：接收SQL语句（不处理直接转给SQL层），返回执行结果

# 查看连接会话
mysql> show processlist;
+-------+--------+---------------------+-----------+---------+------+----------+------------------+
| Id    | User   | Host                | db        | Command | Time | State    | Info             |
+-------+--------+---------------------+-----------+---------+------+----------+------------------+
| 22291 | root   | localhost           | NULL      | Query   |    0 | starting | show processlist |
| 22292 | wechat | 171.88.178.71:30977 | NULL      | Sleep   |   13 |          | NULL             |
| 22293 | wechat | 171.88.178.71:29760 | wechat_db | Sleep   |   11 |          | NULL             |
+-------+--------+---------------------+-----------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

3.2.1 TCP/IP

TCP/IP套接字方式时MySQL在任何平台上都提供的连接方式，也是网络中使用最多的一种方式。

mysql -h127.0.0.1 -u root -p

在通过TCP/IP连接到MySQL实例时，MySQL会先检查一张权限表，用来判断发起请求的客户端IP是否允许连接到MySQL实例。

use mysql;
select host,user,password from user;

host为%时，表示允许所有IP通过user用户访问MySQL实例

3.2.2 Unix域套接字

在Linux和Unix环境下，还可以使用Unix域套接字。Unix域套接字其实不是一个网络协议，所以只能在MySQL客户端和数据库实例时同一台服务器上的情况下使用。

3.3 管理服务和工具组件

3.4 SQL接口组件

语法，语义，权限检查
语句解析
优化基于cost的进行优化
执行器执行SQL

3.5 查询分析器组件

3.6 优化器组件

3.7 缓冲（Cache）组件

3.8 插件式存储引擎（plugins storage engine）

由于MySQL开源特性，存储引擎可以分为MySQL官方存储引擎和第三方存储引擎。

3.8.1 InnoDB存储引擎

InnDB存储引擎支持事物，主要面向在线事物处理（OLTP）方面的应用。其特点时行锁设计，支持外键，并支持类似Oracle的非锁定读，即默认情况下读取操作不会产生锁。

InnoDB存储引擎将数据放在一个逻辑的表空间中，从4.1版本开始，可以将每个InnoDB存储引擎的表单独存放到一个独立的ibd文件中。

InnoDB使用多版本并发控制MVCC来获取高并发性，实现了SQL标准的4种隔离级别，默认为REPEATABLE级别。

InnoDB同时使用一种被称为next-key locking的策略来避免幻读现象的产生。

InnoDB还提供了插入缓冲（insert buffer），二次写（double write），自适应哈希索引（adaptive hash index），预读（read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnoDB采用聚集的方式，每张表的存储都按主键的顺序存储，如果没有显示地定义主键，则会为每行生成一个6字节的ROWID，并以此作为主键。

3.8.2 MyISAM存储引擎

MyISAM存储引擎是MySQL官网提供的存储引擎，其不支持事物，表锁和全文索引。

3.9 物理文件

3.10 专用线程介绍

MySQL属于单进程（mysqld），多线程（master thread,IO,SQL,purge）的工作模式。（Oracle就是属于多进程工作模式）

# 显示工作线程
mysql> select * from performance_schema.threads;
+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------
--------+---------------------+------------------+-------------------+---------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+| THREAD_ID | NAME                                   | TYPE       | PROCESSLIST_ID | PROCESSLIST_USER | PROCESSLIST_HOST | PROCESS
LIST_DB | PROCESSLIST_COMMAND | PROCESSLIST_TIME | PROCESSLIST_STATE | PROCESSLIST_INFO                            | PARENT_THREAD_ID | ROLE | INSTRUMENTED | HISTORY | CONNECTION_TYPE | THREAD_OS_ID |+-----------+----------------------------------------+------------+----------------+------------------+------------------+--------
--------+---------------------+------------------+-------------------+---------------------------------------------+------------------+------+--------------+---------+-----------------+--------------+|         1 | thread/sql/main                        | BACKGROUND |           NULL | NULL             | NULL             | NULL   
        | NULL                |          7638913 | NULL              | NULL                                        |             NULL | NULL | YES          | YES     | NULL            |         1367 ||         2 | thread/sql/thread_timer_notifier       | BACKGROUND |           NULL | NULL             | NULL             | NULL   
        | NULL                |             NULL | NULL              | NULL                                        |            
....

4 MySQL基础管理

4.1 用户管理

4.1.1 用户的定义

格式：

# whitelist（白名单）：能否访问MySQL的地址列表
用户名@'whitelist'    
# 举例
testuser@'localhost' --> 本地能登录用户
testuser@'192.168.10.1' --> 指特定IP等登录用户
testuser@'192.168.10.%' --> 指特定IP地址段等登录用户
testuser@'192.168.10.0/255.255.254.0' --> 指特定IP地址段等登录用户
testuser@'%' -->允许所有地址均可登录

4.1.2 用户的存储位置

mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+-------------------------------------------+-----------------------+
| user             | host      | authentication_string（密码）               | plugin（加密方式）      |
+------------------+-----------+-------------------------------------------+-----------------------+
| root             | %         | *02D43CC451497F30127CCCB9A09892CADB5498B8 | mysql_native_password |
...
| debian-sys-maint | localhost | *0F9275073ED7F7E9E0EDCA638955393655200717 | mysql_native_password |
| wechat           | %         | *62141D8A00803D99A862B8C2F01804D95384CB02 | mysql_native_password |
+------------------+-----------+-------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

4.1.3 用户管理操作

# 创建用户，但是密码长度不合规
mysql> create user 'testuser'@'localhost' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
# 查看 mysql 初始的密码策略
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.00 sec)
# validate_password_length  固定密码的总长度
# validate_password_dictionary_file 指定密码验证的文件路径
# validate_password_mixed_case_count  整个密码中至少要包含大/小写字母的总个数
# validate_password_number_count  整个密码中至少要包含阿拉伯数字的个数
# validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM
## 		关于 validate_password_policy 的取值：
## 		0/LOW：只验证长度
## 		1/MEDIUM：验证长度、数字、大小写、特殊字符
## 		2/STRONG：验证长度、数字、大小写、特殊字符、字典文件
# validate_password_special_char_count 整个密码中至少要包含特殊字符的个数
# set global validate_password_length=6; 修改配置方式
mysql> create user 'testuser'@'localhost' identified by '1234@Abc';

# 修改用户密码
mysql> alter user testuser@'localhost' identified by '1234@ABc';
Query OK, 0 rows affected (0.00 sec)
# 查看命令帮助
mysql> help alter user;
# 修改用户名
mysql> rename user testuser@'localhost' to test@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 删除用户
mysql> drop user test@'localhost';
Query OK, 0 rows affected (0.00 sec)

# 查看mysql系统中所有的权限列表
mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
...
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
# 查询用户的权限列表
mysql> show grants for testuser@'localhost';
+----------------------------------------------+
| Grants for testuser@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' |
+----------------------------------------------+
1 row in set (0.01 sec)
# 授予wechat_db库所有表的查询权限给用户testuser
mysql> grant select on  wechat_db.* to testuser@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for testuser@'localhost';
+---------------------------------------------------------+
| Grants for testuser@localhost                           |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost'            |
| GRANT SELECT ON `wechat_db`.* TO 'testuser'@'localhost' |
+---------------------------------------------------------+
2 rows in set (0.00 sec)
# 删除权限
mysql> revoke select on wechat_db.* from testuser@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for testuser@'localhost';
+----------------------------------------------+
| Grants for testuser@localhost                |
+----------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'localhost' |
+----------------------------------------------+
1 row in set (0.00 sec)

4.2 MySQL参数文件

当MySQL实例启动时，MySQL数据库会读取配置文件，根据配置文件的参数来启动数据库实例。这与Oracle的参数文件（（spfile）相似，不同的时，在Oracle中，如果没有参数文件，启动时会提示找不到参数文件，数据库启动失败。而在MySQL数据库中，可以没有配置文件，在这种情况下，MySQL会按照编译时的默认参数设置启动实例（mysqld --initialize命令）。

MySQL会按照 /etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf的顺序读取文件，如果这几个文件均存在配置文件，则相同的参数以最后一个文件为准。Linux环境中，配置文件一般放在/etc/my.cnf，在Windows平台下，配置文件的后缀可以是.cnf，也可以是.ini。通过以下命令，可以查询配置文件检索路径：

root@pang-HP:~# mysql --help
...
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf