mysql - 安装

王小栋857

已于 2022-10-09 16:39:26 修改

阅读量1.4k

点赞数

分类专栏：数据库文章标签： mysql 数据库服务器

于 2022-07-19 22:51:30 首次发布

本文链接：https://blog.csdn.net/qq_25794513/article/details/125882776

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

磁盘

raid 类型

raid 0 镜像两个相同的盘互相备份，安全性高

raid 1 两个盘随机存储，io快安全性差

raid 10 四块盘，raid1 和raid0的组合体（费用高）

raid 5 三块盘，两块盘用于存储一块盘用于数据校验，属于折中方法推荐使用

如果做了raid 5感觉还是没有很大提升，建议查看是否启用raid卡的缓存功能，默认没有开启的

磁盘调度算法应设为Deadline

echo deadline > /sys/block/sda/queue/scheduler

cat /sys/block/sda/queue/scheduler

分类：

HDD: 机械硬盘，通过盘片旋转，磁头定位读取数据。顺序读取性能好，随机读取性能差。

一般iops（每秒随机读写次数）200

性能受转速影响，常见转速笔记本5400、常用硬盘7200、服务器10000、 15000

SSD:固态硬盘，由flash memory组成，读写速度快。

一般iops（每秒随机读写次数）50000

对比发现4K性能要优于8K的性能，几乎是2倍的差距，当然16K就更明显，所以当使用SSD时，建议数据库页大小设置成4K或者是8K， innodb_page_size=8K

文件系统

XFS/EXT4 选择ext4 存储容量比xfs大

关闭swap 内存速度比磁盘快，

数据存储概况

安装

1 下载

https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz

2 配置文件

# /etc/my.cnf
# 此配置文件多数系统自带

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/tmp/mysql.sock

# 绑定到网卡上
bind_address=192.168.1.100

log-error=error.log
pid-file=mysql.pid

[client]
port=3306
socket=/tmp/mysql.sock

3 安装

# 安装命令
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 解压
cd /usr/local
tar zxvf /path/to/mysql-VERSION-OS.tar.gz


# 增加环境变量
vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
source /etc/profile


# 初始化此时控制台会输出密码如果没有可以去配置文件中配置的日志文件去查看
mysqld --initialize --user=mysql
# 开启ssl认证
mysql_ssl_rsa_setup
# 启动命令   ---类似于守护进程，杀死mysql后会自动重启，自动重启mysql
mysqld_safe --user=mysql &

# 配置开机启动文件
cp support-files/mysql.server /etc/init.d/mysql.server
systemctl enable mysql
# 如执行上一步启动命令此命令不需要执行
systemctl start mysql

编译安装

# 预备工作
shell> yum install gcc glibc gcc-c++ ncurses-devel bison
# 下载安装
shell> wget http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.tar.gz
shell> wget http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-boost-5.7.tar.gz
shell> tar zxf mysql-5.7.tar.gz
shell> tar zxvf mysql-boost-5.7..tar.gz
shell> cd mysql-5.7.
shell> mkdir mybuild
shell> cmake .. -DWITH_BOOST=../boost/
shell> make # or make -j cpu数目
shell> make package # 生成了mysql-5.7-linux-x86_64.tar.gz
# 后续就是把他当作一个二进制包，和之前一样进行安装即可

配置文件

文件名：my.cnf

可以有多个配置文件，参数替换原则

查看帮助命令 mysql --help

安装后发现配置的选项跟自己设置的不一样请检查是否有其他配置文件

参数生效范围

session - 本次会话生效

set long_query_timeout = 15

global - 已存在非本次连接不生效，其他生效

set global long_query_time=5;

以上两种参数配置在mysql 重启后会失效，要想永久保存可配置到my.cnf配置文件中

查看参数

# 查看所有
show variables;

# 参数筛选
show variables like "%long_time%"

权限管理

# 查看表的结构
desc table;

# 用户信息存放我位置
mysql.user # 全局所有库的权限
mysql.db # 查看指定库的权限
mysql.table_priv # 查看指定表的权限
mysql.columns_priv # 查看指定列的权限

# 查看用户权限
# 查看登录用户的权限
show grants;
# 查看指定用户的权限
show grants for "bob"@"127.0.0.1";

# 授权
# 创建用户(创建用户bob,tcp/ip访问，密码123)
create user 'bob'@'127.0.0.1' identified by '123';
# 授权（授权test库下所有权限，给bob用户）
grant all on test.* to "bob"@"127.0.0.1";
# tom这个用户可以访问所有库所有表，如果没有这个用户则会创建这个用户
# tom这个用户还可以授予别人权限 WITH GRANT OPTION
# 如果 *.* 改为一个指定的非user库，该用户只对这个库拥有授予权限，但没有创建用户的权限了
# % 表示非本机IP
GRANT ALL PRIVILEGES ON *.* TO 'tom'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
# 如果不带 IDENTIFIED BY 账户为匿名用户密码为空
GRANT ALL PRIVILEGES ON *.* TO 'tom'@'%' IDENTIFIED BY 'root'

# 撤销权限
# revoke与grant语法一致
revoke select PRIVILEGES ON *.* from 'tom'@'%'

# 删除用户
dorp user 'tom'@'%';

日志

错误日志

参数 ： error_log
默认机器名，可配置文件中指定
error_log = mysql.err

也可以将日志输出到系统日志文件中
syslog_tag=stock

慢查询日志

将运行超过某个时间阈值的sql语句记录到文件中,为数据库优化做准备

查看参数
show variables like "%slow%";
相关参数

# 是否开启慢查询
slow_query_log
# 慢查询日志名
slow_query_log_file
# 指定慢查询阈值，大于阈值的语句都会被记录（不包括死锁等待时间）
long_query_time
# 扫描记录少于该值得sql 不记录到慢查询日志
min_examined_row_limit
# 将没有使用索引的sql记录到慢查询日志
log-queries-not-using-indexes
# 限制每分钟记录没有使用索引sql语句的次数---5.6
log_throttle_queries_not_using_indexes
# 记录管理操作，如ALTER/ANALYZE TABLE
log-slow-admin-statements
# 慢查询日志格式  ---5.5
log_output 
# 在从服务器上开启慢查询日志
log_slow_slave_statements
# 写入时区信息  --5.7
log_timestamps



# 配置文件中启用
slow_query_log = 1
slow_query_log_file = slow.log
long_query_time = 10
min_examined_row_limit = 100
log-queries-not-using-indexes
log_throttle_queries_not_using_indexes = 10
log-slow-admin-statements
log_slow_slave_statements
log_timestamps = system

# 慢查询日志热写入到新文件
set global slow_query_log = 0;
set global slow_query_log_file = 'slow_new.log';
set global slow_query_log = 1;
flush slow logs;

输出到表中

# 配置
# mysql库中有个slow_log表可以配置慢查询日志存储到启动，但是不建议开启，会影响性能
set global log_output = 'table';

通用日志

可以记录数据库所有的相关操作

参数：
general_log
默认文件名：
机器名.log
同样可以把日志保存到表中
mysql.general_log

开启后性能明显下降，不建议开启




配置文件配置
# general log
general_log = 1
general_log_file = general.log

存储引擎

不要有任何疑问，选innodb就对了

引擎针对的是每个表

附录：

一、

当切换数据库时有一下提示，数据库的自动补全功能自动开启的，如果磁盘性能不好会影响到查询体验，建议关闭。

# 配置文件
[mysql]
no-auto-rehash

二、

字符集选择：

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8字符。

基于现在越来越多的字符（最常见的就是Emoji 表情）已经远远无法满足

那么utf8mb4比utf8多了什么的呢?
多了emoji编码支持.
如果实际用途上来看,可以给要用到emoji的库或者说表,设置utf8mb4.

如果不设置字符集mysql默认字符集是latin1

字符集设置

# 配置文件中设置
[mysqld]
# 如果设置为utf8mb4, 直接修改就行了
character-set-server = utf8

排序优化

# 排序查询缓存可以生效与会话中
set sort_buffer_size = 32 * 1024 * 1024;
# 设置全局排序缓存，重启后会消失
set global sort_buffer_size = 32 * 1024 * 1024;


# 配置文件中设置
[mysqld]
sort_buffer_size = 32M


注意：
查询缓存对应的是每一次回话中的查询缓存大小，如果有100个会话同时排序占用的内存就是10*32M,请慎重设置缓存大小

# 查看查询缓存状态
show status like '%sort%';
# 查看全局缓存状态
show global status like '%sort%';
# 刷新缓存
flush status;

查看数据库缓存设置

设置缓存前排序，耗时1分15秒

设置排序缓存为32M

执行查询，耗时22秒

查看排序状态

sort_merge_passes 如果排序内存不足会借用硬盘完成排序，记录的是磁盘io的次数

这只是单次排序的状态，show global status like '%sort%'可以查看全局的排序状态，如果 sort_merge_passes 值过大可以适当增加排序缓存

排序跟磁盘性能有关系，如果内存不足时会借用磁盘完成排序，但是如果磁盘性能很强，内存调大后性能也不会有太强的提升

join 优化

join_buffer_size

用于关联条件没有索引的时候，优先关联条件增加索引。如果存在索引此项配置会失效。

生效范围单个线程中，10个线程都用到此参数，10*1G就会使用10G内存。

两张表关联条件一定要加索引，简单粗暴

join的算法

simple nested_loop join:

从下图中可以看出，r表为驱动表，s匹配表，从r表中逐行取出数据与s表进行匹配，匹配次数为r行数*行数开销巨大

index nested_loop join:

根据b+tree索引,找到一个索引的根b+树高度有关，100w条数据如果b+tree高度为4仅需要4次io就能找到这条数据（不考虑回表）

s 表join列为索引列

r 表为驱动表

匹配次数为r的行数 * s 的索引高度

block nested_loop join:

创建一个内存空间，把驱动表读取到内存中，与内表匹配，扫描次数变为一次，比较次数不变。

此算法用在join列不是索引列时，优化第一种算法的扫描次数。

优化此算法：

1 使用index join 匹配表条件增加索引

2 调大 join_buffer_size 大小

磁盘

文件写入规则：

数据库文件 -> 系统文件系统 -> 磁盘

可以通过参数配置直接写入到磁盘中

O_DIRECT

fwrite / fsync
fwrite 是把数据写入文件系统层（Filesystem）（可能有cache），并不能保证写入Disk
fsync 可以保证把数据写入到Disk（数据落盘）
只通过 fwrite 写入数据特别快（因为有缓存），但随后调用 fsync 就会很慢，这个速度取决于磁盘的 IOPS
如果不手工执行 fysnc ，当Filesystem的 cache 小于 10% 时，操作系统才会将数据刷入磁盘。所以可能存在数据丢失的风险，比如掉电

O_DIRECT 的设置参数是告诉系统直接将数据写入磁盘，跳过文件系统的缓存。等同于使用裸设备的效果

innodb_flush_method = O_DIRECT

磁盘相关参数设置

# MySQL参数
# 该参数设置的尽可能大
innodb_log_file_size=4G 
innodb_flush_neighbors=0

MRR(Multi-Range Read)

MRR：针对物理访问，随机转顺序，空间换时间。

1. 开辟一块内存空间作为cache, 默认为 32M ，注意是线程级的，不建议设置的很大；

2. 将需要回表的主键放入上述的内存空间中（空间换时间），放满后进行排序（随机转顺序）；

3. 将排序好数据（主键）一起进行回表操作，以提高性能；
◦ 在 IO Bound 的SQL场景下，使用MRR比不使用MRR系能提高将近 10倍（磁盘性能越低越明显）；
◦ 如果数据都在内存中，MRR的帮助不大，已经在内存中了，不存在随机读的概念了（随机读主要针对物理访问）

SSD 仍然需要开启该特性，多线程下的随机读确实很快，但是我们这里的操作是一条SQL语句，是单线程的，所以顺序的访问还是比随机访问要更快。

其中MRR默认是打开的 mrr=on，不建议关闭

mysql 有自己成本计算方式

将该值off，不让MySQL对MRR进行成本计算（强制使用MRR）

set optimizer_switch='mrr_cost_based=off'

优化命令

查看执行计划

explain

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

查看注意事项：1 id相同从上往下看

2 id不同从下往上看

快速了解通道 Mysql查看执行计划_风的追溯的博客-CSDN博客_mysql查看执行计划语句