mysql体系结构
一、连接层
思想
为解决资源的频繁分配-释放所造成的问题,为数据库连接建立一个“缓冲池”。
原理
预先在缓冲池中放入一定数量的连接,当需要建立数据库连接时,只需从“缓冲池”中取出一个,使用完毕之后再放回去。
作用
进行身份验证、线程重用,连接限制,检查内存,数据缓存;管理用户的连接,线程处理等需要缓存的需求
查看最大连接数
show variables like '%max_connections%';
二、SQL层
管理服务和工具组件
从备份和恢复的安全性、复制、集群、管理、配置、迁移和元数据等方面管理数据库
sQL接口组件
进行DML、DDL,存储过程、视图、触发器等操作和管理;用户sQL命令接口
解析器组件
验证和解析sQL命令
查询优化器组件
对sQL语句查询进行优化“选取、投影和连接”
缓存和缓冲区
由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等
查看缓存配置情况
show variables like '%query_cache%';
三、插件式存储引擎
MySQL的存储引擎是插件式的。它根据MySQL AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制,这种访问机制就叫存储引擎
MylSAM
它查询速度快,有较好的索引优化和数据压缩技术。但是它不支持事务。
lnnoDB
它支持事务,并且提供行级的锁定,应用也相当广泛。
BDB
不再从底层支持BDB
Memory
适合存储临时数据
Archive
适合存储历史数据
四、物理文件层
支持的文件类型
EXT3、EXT4、NTFS、NFS
文件内容
数据文件日志文件配置文件其他文件
MySQL安装部署
四种安装Mysql软件的文档仓库安装
rpm -i https://repo.mysql.com//mysql80-community-release-el8-4.noarch.rpm
dnf install mysql-server
rpm -qa | grep mysql
systemctl start mysqld
systemctl status mysqld
1
2
3
4
5
6
本地安装
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.30-1.el8.x86_64.rpm-bundle.tar
tar xvf mysql-8.0.30-1.el8.x86_64.rpm-bundle.tar
dnf localinstall mysql-community-server-8.0.30-1.el8.x86_64.rpm \
mysql-community-client-8.0.30-1.el8.x86_64.rpm \
mysql-community-common-8.0.30-1.el8.x86_64.rpm \
mysql-community-icu-data-files-8.0.30-1.el8.x86_64.rpm \
mysql-community-client-plugins-8.0.30-1.el8.x86_64.rpm \
mysql-community-libs-8.0.30-1.el8.x86_64.rpm
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# systemctl status mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
容器安装
yum-config-manager --add-repo https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.repo
ll /etc/yum.repos.d/
dnf install docker-ce docker-ce-cli
systemctl start docker
systemctl status docker
docker version
docker run hello-world
docker run mysql
docker ps
docker run --name mysqltest -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker ps
docker run --name mysqltest2 -e MYSQL_ROOT_PASSWORD=123456 -d mysql
netstat -lntup | grep 3306
docker ps
docker --help
docker exec mysqltest2 /bin/bash
dnf install bash-com*
docker exec -it mysqltest2 /bin/bash
docker ps
docker run --name mysqltest3 -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker ps
docker run --name mysqltest5 -e MYSQL_ROOT_PASSWORD=123456 -d mysql
docker ps
history
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
源码安装
卸载数据库软件
dnf remove mysql-server
rpm -e mysql80-community-release
rpm -qa | grep mysql
rm -rf /var/lib/mysql/
rm -rf /var/log/mysql/
1
2
3
4
5
6
7
8
9
2.1 用shell写安装脚本
[root@localhost ~]# cat install_mysql.sh
#!/bin/bash
#author:jzl
#date:2022-9-11
#action:install_mysql
rpm -qa | grep mysql
if [ $? != 0 ]
then
echo "没有mysql包"
else
rpm -e mysql80-community-release
rm -rf /var/lib/mysql/
rm -rf /var/log/mysql/
echo "正在卸载mysql"
echo "正在安装mysql"
fi
wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.30-1.el8.x86_64.rpm-bundle.tar
tar xvf mysql-8.0.30-1.el8.x86_64.rpm-bundle.tar
dnf localinstall mysql-community-server-8.0.30-1.el8.x86_64.rpm \
mysql-community-client-8.0.30-1.el8.x86_64.rpm \
mysql-community-common-8.0.30-1.el8.x86_64.rpm \
mysql-community-icu-data-files-8.0.30-1.el8.x86_64.rpm \
mysql-community-client-plugins-8.0.30-1.el8.x86_64.rpm \
mysql-community-libs-8.0.30-1.el8.x86_64.rpm
systemctl start mysqld
if [ $? -eq 0 ]
then
echo "安装成功"
else
echo "安装失败"
fi
oldpasswd=`grep "password" /var/log/mysqld.log | awk '{print $NF}'`
newpasswd='Redhat123.'
mysqladmin -uroot -p$oldpasswd password $newpasswd
echo "密码修改成功"
2.2 用ansible写安装脚本
清单文件(inventory):
[local]
192.168.10.128
[test1]
192.168.10.129
[test2]
192.168.10.130
[test:children]
test1
test2
[all:vars]
ansible_user=root
ansible_password=redhat
配置文件(ansible.cfg):
[defaults]
inventory=/etc/testansible/inventory
remote_user=root
ask_pass=false
playbook(代码)
---
- name: install and start mysql
hosts: test2
tasks:
- name: download mysql packages
get_url:
url: https://repo.mysql.com//mysql80-community-release-el8-4.noarch.rpm
dest: /root/class_packages
- name: install mysql rpm
yum:
name: /root/class_packages/mysql80-community-release-el8-4.noarch.rpm
state: present
- name: install service mysql
yum:
name: mysql-server
state: present
- name: restart service mysql
service:
name: mysqld
state: restarted
Mysql的卸载(del_mysql.yml)
---
- name: stop and remove service mysql
hosts: test2
tasks:
- name: stop service mysql
service:
name: mysqld
state: stopped
- name: remove service mysql
yum:
name: mysql*
state: absent