Mysql-Linux环境部署

mysql

 2024/07/22 

mysql参数

参数按照mysql的分发，分为服务器变量（静态、动态），服务器状态变量（运行统计）
静态参数：只读，在mysql服务运行时无法修改，只能通过my.cnf进行修改
动态参数：可以在mysql服务运行时进行修改

会话级别参数
只针对该会话，如果再有客户端建立连接，则造成影响。修改方法：
set session var_name = value;
set @@session.var_name = value;

全局级别
影响整个服务，重启后修改无效。修改方法：
set global var_name = value
set @@global.var_name = value

参数查看：

查看所有参数
show variables;

查看wangsheng开头的变量
show variables like "wangsheng_%";

查看当前会话的某个参数
select @@session.autocommit;

查看当前数据库的某个参数
select @@autocommit;

查看全局的某个参数
select @@global.autocommit;

结尾反斜杠用法
\g 相当于;
\G 相当于;并将查询结  果旋转90度，更加易读


例：修改innodb_buffer_pool_size参数
show variables like "innodb_buffer_pool%";
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 268435456      |
+-------------------------------------+----------------+
SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 268435456 |
+---------------------------+

在my.cnf中添加
innodb_buffer_pool_size=1G
重启生效

理想情况下，`innodb_buffer_pool_size` 应该设置为系统内存的 50% 到 80%，具体取决于您的具体需求和系统的其他内存使用情况。

例：收集日志文件、服务器状态信息
查看日志是否开启，以及位置
show variables like "%log_error%";
show variables like "slow_query_log";
show variables like "long_query_log";
show variables like "long_query_time";
show variables like "%log_bin%";
show variables like "%general%";
show variables like "%relay%";

查看数据库状态
show variables like 'version';
show databases;
status
show status;
show engines;
show plugins;
show engine innodb status;
show processlist;
show variables like 'collation%';
show collation like '%utf8%';
show master status;
show slave status;

mysql的默认数据库

1.mysql数据库

是mysql的核心数据库，负责存储数据库的用、权限设置等，mysql自己需要的控制和管理信息
无法删除

2.sys数据库

数据源来自performance_schema，目的是将其复杂度降低
使DBA可以更好阅读库中的内容，了解mysql的运行情况

3.performance_schema

用于收集数据库服务器性能参数

4.INFORMATION_SCHEMA

infomation_schema数据库是mysql自带的，提供了访问数据库元数据的方式
存放了数据库名和表名，列数据类型，访问权限等

mysql57安装与配置

#下载解压与安装
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz /usr/local/
cd /usr/local/
tar -xf mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.44-linux-glibc2.12-x86_64 mysql
mkdir /usr/local/mysql/data
mkdir /usr/local/mysql/logs 
useradd mysql

chown -R mysql:mysql /usr/local/mysql
chown mysql:mysql /etc/my.cnf

配置文件/etc/my.cnf
[mysql] 
default-character-set=utf8 # 客户端默认的字符集为utf8 
socket=/tmp/mysql.sock # 客户端连接的socket文件路径 

[mysqld] 
port=3306 # MySQL服务监听的端口
basedir=/usr/local/mysql # MySQL安装的基础目录
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/logs/mysql.sock # MySQL服务使用的socket文件路径 
character-set-server=utf8mb4 # 服务器默认的字符集为utf8mb4，支持更多Unicode字符 
default-storage-engine=INNODB # 默认存储引擎为InnoDB 
innodb-buffer-pool-size=200M # 修正了拼写错误，InnoDB缓冲池大小为200MB 
max_allowed_packet=16M # 最大允许的数据包大小 
explicit_defaults_for_timestamp=1 # 启用对TIMESTAMP和DATETIME列的明确默认值 
log-output=FILE # 日志输出到文件 
general_log=0 # 禁用通用查询日志 
general_log_file=/usr/local/mysql/logs/general.log # 通用查询日志的文件名
slow_query_log=ON # 启用慢查询日志 
slow_query_log_file=/usr/local/mysql/logs/slow_query.log # 慢查询日志的文件名
long_query_time=10 # 慢查询的阈值时间，单位为秒 
log-error=/usr/local/mysql/logs/mysqld.log # 错误日志的文件名 
default-authentication-plugin=mysql_native_password # 默认的认证插件为mysql_native_password

#初始化并记录密码，一会第一次登录使用
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ 

cat /usr/local/mysql/logs/mysqld.log
wrk_+gdtg1Cg

#系统方式自动启动mysql(制作systemd服务)
vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.conf
LimitNOFILE=65536
LimitNPROC=65536

systemctl stop mysqld
systemctl daemon-reload
systemctl enable mysqld --now
systemctl status mysqld.service

#命令行方式启动
nohup /usr/local/mysql/bin/mysqld_safe -defaults-file=/etc/my.cnf & mysqladmin -uroot -p shutdown -S /usr/local/mysql/logs/mysql.sock

数据库初始化操作；
#第一次修改root密码
set password=password("Admin@123!");

#修改root密码
mysql -u root -p
use mysql;
update user set authentication_string=password("Admin@123!")where user="root";
flush privileges;
exit;

#允许root远程登录
mysql -u root -p
use mysql;
select host,user from user where user='root';
grant all privileges on *.* to 'root'@'%' identified by 'Admin@123!' with grant option;
flush privileges;
exit;

数据库简单操作

mysql -u root -p
#创建数据库
create database wsdb DEFAULT CHARSET utf8;
#创建新用户
CREATE USER 'wangsheng'@'%' IDENTIFIED BY 'wangsheng';
#查看所有用户
SELECT HOST,USER FROM mysql.user;
#给新用户分配权限(所有主机都可以访问)
grant all privileges on wsdb.* to 'wangsheng'@'%';
#给新用户分配权限(仅本地可以访问)
grant all privileges on wsdb.* to 'wangsheng'@'localhost';

#创建表
use wsdb;
create table ws01(
    -> id int auto_increment primary key,
    -> name varchar(15)
    -> )engine=InnoDB;

#插入数据
INSERT INTO ws01 VALUES (1,'xhy01');
INSERT INTO ws01 VALUES (2,'xhy02');
INSERT INTO ws01 VALUES (3,'xhy03');
commit;

#查询数据
select * from wsdb.ws01;
+----+-------+
| id | name  |
+----+-------+
|  1 | xhy01 |
|  2 | xhy02 |
|  3 | xhy03 |
+----+-------+

use wsdb;
select * from ws01;
+----+-------+
| id | name  |
+----+-------+
|  1 | xhy01 |
|  2 | xhy02 |
|  3 | xhy03 |
+----+-------+

mysql80安装与配置

版本8.0.15

groupadd mysql
useradd -r -g mysql -s /bin/false mysql
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz
tar -xf mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.15-linux-glibc2.12-x86_64 mysql
mv mysql /usr/local/
mkdir /usr/local/mysql/logs -p
mkdir /usr/local/mysql/data -p
chown -R mysql:mysql /usr/local/mysql

vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

my.cnf参数文件
[mysql]
default-character-set=utf8mb4        # 客户端默认的字符集为utf8
socket=/usr/local/mysql/logs/mysql.sock            # 客户端连接的socket文件路径

[mysqld]
port=3306                         # MySQL服务监听的端口
basedir=/usr/local/mysql          # MySQL安装的基础目录
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/logs/mysql.sock            # MySQL服务使用的socket文件路径
character-set-server=utf8mb4      # 服务器默认的字符集为utf8mb4，支持更多Unicode字符
default-storage-engine=INNODB     # 默认存储引擎为InnoDB
innodb-buffer-pool-size=1G      #InnoDB缓冲池大小为1G
max_allowed_packet=16M            # 最大允许的数据包大小
explicit_defaults_for_timestamp=1 # 启用对TIMESTAMP和DATETIME列的明确默认值
log-output=FILE                   # 日志输出到文件
general_log=0                     # 禁用通用查询日志
general_log_file=/usr/local/mysql/logs/general.log  # 通用查询日志的文件名（注意这里是.log，不是.err）
slow_query_log=ON                 # 启用慢查询日志
slow_query_log_file=/usr/local/mysql/logs/slow_query.log  # 慢查询日志的文件名（建议使用更明确的文件
名）
long_query_time=10                # 慢查询的阈值时间，单位为秒
log-error=/usr/local/mysql/logs/mysqld.log  # 错误日志的文件名
default-authentication-plugin=mysql_native_password  # 默认的认证插件为mysql_native_password

初始化
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

cat /usr/local/mysql/logs/mysqld.log
获得初始密码
9+S+PhjE-HUU

自动启动
vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.conf
LimitNOFILE=65536
LimitNPROC=65536

systemctl daemon-reload
pkill mysql
systemctl restart mysqld.service
systemctl enable mysqld.service --now

mysql -uroot -p
set password="root";
use mysql;
update user set host='%' where user='root';

mysql调优与故障诊断原则

应用程序调优
sql语句优化、管理变化优化
实际调优
内存
数据结构
实例配置
操作系统优化
I/O
SWAP
Parameters

效果从好到差：
调整业务功能、数据设计、流程设计
调整sql语句
调整物理结构内存分配I/O设计

排查方式
1.查看系统状态top iostat等
2.查看mysql连接数与sql语句 show processlist
3.查看innodb状态show engine innodb status
4.查看状态show global status
5.看慢日志，分析sql与优化sql
6.查看错误日志

大表优化
1.看执行计划优化sql和索引
2.在前端使用缓存memcache和redis
3.主从复制，读写分离
4.mysql表分区
5.mysql分表分库垂直拆分、水平拆分

sql调优的步骤
1.捕获sql语句
2.产生sql语句的执行计划
3.验证统计信息，表信息，字段的数据分布特点
4.根据统计信息，选择合适的执行计划
5.检查sql语法，业务逻辑

mysql执行计划explain
能够输出优化器是如何执行sql语句的
使用方法：
执行某个语句select xxx之后
使用explain select xxx

索引
使用索引是为了提高数据的查询效率
能够在表中快速查找到一个行，合理使用索引能够大大提高数据库的运行效率
1.快速存取数据
2.改善数据库性能，保证列值的唯一性
3.实现表与表之间参照完整性
4.使用order by，group by子句进行数据检索时，使用索引可以减少排序分组的时间

索引分类
UNIQUE唯一索引：不允许出现相同的值，可以有NULL
INDEX普通索引：允许相同的索引内容
PRIMARY KEY主键索引：不允许出现相同的值，且不能为NULL，一个表只有一个主键索引
fulltext index全文索引：↑都是针对列的值发挥作用，全文索引可以针对值中某个单词如文章中的词

索引优化规则
1.避免在索引列中计算，包括逻辑判断，比如NOT IS NULL或者IS NOT NULL
2.索引在查询结果超过30%的表记录时效果不好
3.有时索引比全表慢，比如小表
4.使用>=替代>
5.使用索引来避免排序
6.where子句建议使用and而不是or
7.避免在索引使用函数
8.建议使用精确的通配符，如%或*
9.避免改变索引列的类型，如本身是个varchar，你查数字，就会很慢
10.用UNION ALL替换OR
11.order by排序语句最好作用于主键
12.避免子查询的使用
13.使用truncate替代delete
14.建议每次事务都使用commit，及时释放资源
15.单个表索引控制在5个以内
16.join操作，不要超过三张表
17.优化分页查询
18.避免大对象操作

mysql故障排查、信息收集

show variables like '%log_error%';
show variables like '%log_bin%';
show variables like '%slow_query_log%';
show variables like '%log_query_time%';
show variables like '%general%';
show variables like '%relay%';
show variables like 'version';
sow databases;
status;
show status;
show engines;
show plugins;
show engine innodb status;
show processlist;
show variables like 'charact';
show variables like 'collation%';
show master status;
show slave status;

诊断与调优工具使用

mysqltuner-perl性能优化建议者
[major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. (github.com)](https://github.com/major/MySQLTuner-perl)

wget https://github.com/major/MySQLTuner-perl/archive/refs/tags/v2.5.2.tar.gz
tar -xf v2.5.2.tar.gz
cd MySQLTuner-perl-2.5.2/
诊断本地
perl mysqltuner.pl
perl mysqltuner.pl --verbose
诊断远程数据库
perl mysqltuner.pl --host xxx --user xxx --pass xxx ./mysqltuner.pl --host 本机 --user 本机 --pass 本机 --forcemem 50
输出到文件
perl mysqltuner.pl --outputfile ~/result_mysqltuner.txt



tuning-primer脚本
[MySQL Tuning Primer Script in Launchpad](https://launchpad.net/mysql-tuning-primer/)
wget https://launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/tuning-primer.sh
chmod +x tuning-primer.sh

在my.cnf中添加
[client]
port=3306
socket=/usr/local/mysql/logs/mysql.sock
user=root
password=root

./tuning-primer.sh
./tuning-primer.sh > ~/result_tuning.txt

蓝色：总指标
绿色：表示参数正常
红色：表示参数严重问题
深红色：表示有问题参数
黄色：一些警告

原文作者：王盛

原文链接：https://akemi.zj.cn/2024/07/22/MySQL-Install/

发表日期：July 22nd 2024, 1:38:44 pm

更新日期：February 20th 2025, 6:37:27 pm

Next Post

mysql安全审计之Mcafee Mysql-Audit
Previous Post

Mysql用户权限与密码管理、角色管理

CATALOG

1. mysql参数
2. mysql的默认数据库
3. mysql57安装与配置
1. 3.1. 数据库简单操作
4. mysql80安装与配置
5. mysql调优与故障诊断原则