一.mysql 结构
![54b89f4f4c99fbd25a6b0c7b9406e6b1.png](https://i-blog.csdnimg.cn/blog_migrate/fc5322dd438cbe58d59f5f9434a2f3b5.png)
1. 连接层
1.1 提供连接协议
Socket
TCPIP
1.2 验证用户名(root@localhost)密码合法性,进行匹配专门的授权表。
1.3 派生一个专用连接线程(接收SQL,返回结果,具体事务由后台线程执行)
mysql> show processlist;
2. SQL层 (优化方面至关重要的)
2.1 验证SQL语法和SQL_MODE
2.2 验证语义
2.3 验证权限
2.4 解析器进行语句解析,生成执行计划(解析树)
2.5 优化器(各种算法,基于执行代价),根据算法,找到代价最低的执行计划。
代价:CPU IO MEM
2.6 执行器按照优化器选择执行计划,执行SQL语句,得出获取数据的方法。
2.7 提供query cache(默认不开),一般不开,会用redis
2.8 记录操作日志(binlog),默认没开
3. 存储引擎层(innodb)
3.1 innodb引擎介绍:
![8bce022b43f7a08ed2ea4370356bf9c3.png](https://i-blog.csdnimg.cn/blog_migrate/f1c43a0064c52650e47b7045d7486e4d.jpeg)
3.2 InnoDB存储引擎物理存储结构 *****
(1).共享表空间(ibdataxxx) :存储系统数据字典信息(所有库所有表的元数据信息),UNDO 等数据
(2) 独立表空间( xxx.ibd) ,每个表一个ibd,存储表的数据行和索引(段区页)
(3) 列文件(frm):存储表的列的信息,每个表一frm.
(4) 结论:一张InnoDB表= frm+idb+ibdata1
3.3 MySQL的存储引擎日志:*****
(1). Redo Log: ib_logfile0 ib_logfile1,前滚日志
(2). Undo Log: ibdata1 (存储在共享表空间中8.0独立出来),回滚日志
3.4 临时表
(1)ibtmp1: 在做join 等操作产生的临时表,在内存.
(2)auto.cnf: server_uuid 保存文件
3.5. InnoDB 存储引擎核心特性说明 *****
(1) 外键
(2) 行锁
(3) 事务
(4) 复制(多线程,GTID,MTS)
(5) 热备
(6) ACSR: 自动故障恢复 前滚+CKPT
(7) MVCC: undo 快照功能,实现同数据读和写的操作不会互相阻塞,
3.6 innodb引擎存数方式
(1) 一个表就是一个段 ; 一个段由多个区构成; 一个区(1M)由64个连续的页(16kb)组成
(2)页存储innodb表中的行数据
3.7 InnoDB核心参数的介绍(存储引擎默认设置) *****
default_storage_engine=innodb
innodb_log_buffer_size=16777216 #redo buff大小字节
innodb_log_file_size=50331648 #redo log 大小字节
innodb_log_files_in_group = 3 #redo log 个数
innodb_file_per_table=1 #开启独立表空间模式(ibd)
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend #共享表空间文件个数和大小
innodb_max_dirty_pages_pct=75 #buff pool的使用75%写入到磁盘
innodb_flush_log_at_trx_commit=1 # redo buff刷写频率:
##1(推荐)事务commit时立即将redo buff(已/未提交事务打标签) 写入到磁盘(redo log)
##0 每秒将redo buff 写入到磁盘
Innodb_flush_method=fsync #reddo buff刷写方式:
#fsync(默认) : redo buffer 和buffer pool都是 先written(写入OS buff) →sync(写入磁盘)
#O_DIREC( 推荐)) :buffer pool使用到75%时直接sync, redo buffer 先 written(写入到osbuffer )再sync
#O_DSYNC 和O_DIREC相反
#最高安全模式
# innodb_flush_log_at_trx_commit=1
# Innodb_flush_method=O_DIRECT
#最节省性能模式:
# innodb_flush_log_at_trx_commit=0
# Innodb_flush_method=fsync
二. mysql安装注意事项
1.建议使用二进制包安装
2.不建议使用最新的版本进行安装
3.不建议用跟新周期频繁的版本
三.多实例安装
1.安装依赖的libaio
yum install -y libaio
2. 下载软件到 /server/tools
MySQL
3. 解压mysql二进制包到 自定义的 /application/的目录,
mkdir /application/
cd /server/tools &&tar -zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /application/
4. 创建软连接
ln -s /application/mysql-5.7.26-linux-glibc2.12-x86_64 /application/mysql
5. 处理原始环境
yum remove mariadb-libs-5.5.60-1.el7_5.x86_64 -y
6. 设置环境变量,检查环境变量
echo 'export PATH=/application/mysql/bin:$PATH' >>/etc/profile &&source /etc/profile
mysql -V
7. 创建数据路径并,添加一块新磁盘模拟数据盘, 格式化并挂载磁盘
mkdir /data
mkfs -t xfs /dev/sdb && partprobe /dev/sdb
##UUID挂载
echo "$(blkid /dev/sdb |sed -nr 's#.*(UUID.*)TYPE.*#1#g'p) /data xfs defaults 0 0" >>/etc/fstab
mount -a && df -h
mkdir /data/{3306..3312}/{data,error,binlog,slow,relaylog} -p
8. 授权
useradd -s /sbin/nologin mysql
chown -R mysql.mysql /application/ && chown -R mysql.mysql /data
9. 初始化数据(创建系统数据)
9.1 #5.6 版本 初始化命令
/application/mysql/scripts/mysql_install_db
9.2 # 5.7 版
for num in {3306..3312}; do mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/$num/data; done
10. 创建启动方式 (多实例可创建多个)
cat >/tmp/mysqld3306.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
#就改ExecStart中的defaults-file,其他不用
ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf
LimitNOFILE = 5000
EOF
for num in {3306..3312};do cp /tmp/mysqld3306.service /etc/systemd/system/mysqld$num.service; done
for num in {3306..3312}; do sed -i "s#3306#$num#g" /etc/systemd/system/mysqld$num.service; done
10 .批量创建配置文件: 1)先创建 /tmp/my.cnf
vim /tmp/my.cnf (配置文件参考mysql优化篇章的参数)
11. 修改配置文件
for num in {3306..3312};do cp /tmp/my.cnf /data/$num/my.cnf; done
for num in {3306..3312}; do sed -i "s#3306*#$num#g" /data/$num/my.cnf; done
declare -i a a=0 && for num in {3306..3312}; do a=$a+1;sed -i "s#server_id=.*#server_id=$a#g" /data/$num/my.cnf; done
chown -R mysql.mysql /application/ && chown -R mysql.mysql /data
12. mysql 控制命令
12.1 启动
systemctl restart mysqld
12.2 查看状态
systemctl status mysqld
12.3 测试
for num in {3306..3312};do mysql -S /data/$num/mysql.sock -e "select @@server_id"; done
12.4 连接数据库
mysql -uroot -p213 -P 3306 -h 10.0.0.1
-D 指定数据库
-S 本地登录(socket登录)
-e 免交互执行sql语句
12.5 mysql 指定配置文件启动方式(不推荐使用)
mysqld_safe --defaults-file=/tmp/aa.txt &
四. 忘记root密码解决方式(跳过授权表登录)
1. mysqld_safe --skip-grant-tables --skip-networking &
#跳过授权表 #跳过远程登录
2.重新加载授权表
flush privileges;
alter user root@'localhost' identified by 'snow';
3. 重启数据库
systemctl restart mysqld