mysql基础能做运维吗_MySQL的基础运维

最新推荐文章于 2023-09-16 08:00:00 发布

图灵的追随者

最新推荐文章于 2023-09-16 08:00:00 发布

阅读量124

点赞数

文章标签： mysql基础能做运维吗

本文链接：https://blog.csdn.net/weixin_36415235/article/details/113463201

版权

面对一台陌生的MySQL服务器，首先就是要确认MySQL实例的部署结构和各个实力的运行环境：

1.有多少个实例？

2.MySQL的安装目录？

3.MySQL的数据目录？

4.参数文件的路径？

所有信息都可以通过#ps -ef | grep mysqld得到

MySQL的参数文件：

MySQL的参数文件以my.cnf命名，默认路径为/etc/my.cnf，可以手动指定路径进行加载。

MySQL初始化时创建参数文件指定规格的文件结构。

MySQL启动时，根据参数文件生成实例(没有参数文件则以默认参数启动实例)，修改参数文件需要重启实例生效。

MySQL参数文件分为两个部分：client部分和server部分。在不同的标签中编辑。

指定参数文件进行加载：

mysqld_safe --defaults-file=$my.cnf & //$my.cnf 为参数文件的路径。

需要注意的是，MySQL有默认的参数文件寻找路径，需要确保使用的参数文件只有一个，这种方式可以保证MySQL总是以正确的参数文件进行加载。

参数可以分为动态参数和静态参数，所谓动态参数就是指可以在MySQL实例运行过程中进行修改。而静态参数指的是在整个实例的生命期内，该类参数只读，只能通过参数文件修改或者启动命令行选项指定该参数的值。

参数的有效级别：

session，参数修改在当前会话生效。 global，参数修改在整个实例的生命周期生效。

MySQL的物理文件结构：

MySQL物理文件结构-Table

MyISAM Table：

.frm文件：存放MyISAM表的结构定义

.MYD文件：存储myisam表的数据

.MYI文件：存储myisam表的索引相关信息

InnoDB Table：

.frm文件：存放InnoDB存储引擎的表结构定义

.ibd文件：独享表空间存储方式下，.ibd文件存储InnoDB的表数据和索引数据

ibdata文件：共享表空间存储方式下，所有InnoDB表的表数据与索引数据全部存储在一个ibdata文件中

MyISAM与InnoDB的区别：

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会很影响速度，所以处理这种情况最好把多条SQL放在begin和commit之间，组成一个事务。

InnoDB支持外键，而MyISAM不支持。对于一个包含外键的InnoDB表转换为MyISAM时会失败。

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构)，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引应该是独立的。InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值。而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

MySQL物理文件结构-日志

logfile文件

InnoDB引擎默认开启，对数据库DML操作的物理记录，保证食物的持久性。

logfile也称为redolog(重做日志)，MySQL中的物理日志主要有以下作用：

快速提交，支持脏块，崩溃恢复。

由于没有归档机制，故不支持介质恢复

binlog 文件

所有引擎支持，对数据库DML操作进行逻辑记录，可用来进行介质恢复。

开启方式：

1)参数文件通过参数log-bin指定binlog的路径和文件名前缀。

2)通过log-bin-index指定索引文件的路径以及文件名前缀。

log-bin=/home/my3301/log/mysql-bin

log-bin-index=/home/my3301/log/mysql-bin.index

binlog 文件可以通过mysqlbinlog工具进行解析。通过mysqlbinlog --help获取命令帮助。

slowlog 文件

记录MySQL数据库的慢查询语句，当SQL执行时间超过一定阈值进行记录。

开启方式：

1)参数文件中通过设置slow_query_log参数为1开启慢日志。

2)通过slow_query_log_file参数指定慢日志路径以及文件名。

3)通过参数long_query_time设置慢查询阈值时间。例如，slow_query_log_file=/home/my3301/log/slow.log

long_query_time=1

slow_query_log=1

慢日志可通过格式化工具mysqkdumpslow查看，mysqldumpslow -s t按平均执行时间进行排序，mysqldumpslow -s r 按平均返回行数进行排序。

error log文件：

MySQL server 的错误日志文件，对mysql的启动、运行、关闭过程进行记录。

不仅记录了所有的错误信息，也记录了一些警告信息或一些正确的信息

命令行查看error log路径 select @@log_error

参数文件通过log-error参数指定error log的路径及文件名

general log 查询日志

记录所有的sql语句，默认不开启

执行的sql不论成功还是失败都会被记录

PID文件：

进程使用PID文件标识自己的存在，重复启动时根据这个文件来判断是否已经启用进程。

查看PID文件路径 select @@pid_file

socket 文件：

本机进程与MySQL进程进行通信的载体，使用unix domain socket协议

查看socket文件路径：select @@pid_file；

ibdata 文件：

MySQL数据库实例初始化时根据参数innodb_data_file_path创建

innodb引擎使用共享表空间时，ibdata文件用于存储表数据以及索引数据

undo段，double wrote等都是使用ibdata文件作为物理存储

MySQL常用管理工具

mysql：

MySQL server安装时集成安装的。$basedir /bin。是连接MySQL数据库的客户端工具，提供命令行界面。

mysql的三种连接方式：

1)使用TCP/IP协议，通过ip+port连接。mysql -u -p -h -p [库名]

2)使用Unix Socket协议。在服务器本机上指定socket文件连接。mysql -u -p -S [库名]

3)图形化工具：navicat，SQLyog，workbench

mysqladmin：

提供给mysql管理员的客户端工具，支持部分mysql数据库的SQL操作并且不需要进入交互界面，提供服务管理和交互选项。

命令结构：mysqladmin [options]

常用命令：ping -> 检查MySQL服务的可用性

processlist -> 查看连接线程状态，相当于交互界面执行show processlist

reload/flush-privileges -> 重载权限表

shutdown -> 关闭数据库实例

variables -> 输出可用的服务器参数，相当于交互界面show variables

extended-status -> 输出mysql服务的状态参数，相当于show status

kill id1,id2.. -> 杀掉mysql连接线程，需要指定thread id

flush-tables -> 将缓冲池里的表刷入磁盘

flush-logs -> 将日志刷新进磁盘

mysqlbinlog：

服务器binlog日志的文本格式化工具，通过binlog做介质恢复的重要工具。

命令结构：mysqlbinlog [options] 常用选项：-d，--database=name -> 只列出指定数据库的相关操作

-r，--result-file=filename -> 将文本格式日志输出到指定文件

--start-datetime -> 指定起始日期

--stop-datetime -> 指定结束日期

--start-position -> 指定起始位点

--stop-position -> 指定结束位点

-v，--verbose -> 重现SQL语句

--base64-output -> 当使用row模式的binlog时。需要指定这个值为DECODE-ROWS

mysqlshow：

客户端对象查找工具，查找对象包括库，表，列或索引

命令结构：mysqlshow [options] [db_name[tab_name[col_name]]]，连接的选项与mysql工具相同。

常用选项：--count -> 显示数据库和表的统计信息

-k，--keys -> 显示指定表中的所有索引

-i，--status -> 显示表的一些状态信息

mysqldumpslow:

慢日志格式化工具

命令结构：mysqldumpslow [options] 常用选项：mysqldumpslow -s t 按平均执行时间排序

mysqldumpslow -s r 按平均返回的行数排序

perror：

错误代码查看工具

命令结构：perror [options]

MySQL备份与恢复

xtrabackup:

1)概念：

开源备份工具需要额外安装。MySQL冷备，mysqldump，MySQL热拷贝都无法实现对数据库的增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabackup有两个工具，xtrabackup和innobackup。

物理热备工具，支持MySQL、Percona server和MariaDB。开源免费，只支持xtraDB和innoDB两种数据引擎不支持MyISAM引擎。

2)优点：

备份速度快，物理备份可靠；

备份过程不会打断正在执行的事务，不需要锁表；

能够基于压缩等功能节约磁盘空间和流量；

自动备份校验；

还原速度快；

可以将备份传输到另外一台机器上；

在不增加服务器负载的情况下备份数据。

3)备份原理：

(1)innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；

(2)xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态(等待文件被创建)

(4)xtrabackup拷贝完成ibd数据文件后，会通知innobackupex(通过创建文件)，同时xtrabackup进入等待状态(redo线程依旧在拷贝redo.log)

(5)innobackupex收到xtrabackup通知后，执行FLUSH TABLES WITH READ LOCK(FTWRL)，取得一致性位点，然后开始备份非InnoDB文件(如frm、MYD、MYI、CSV、opt、par等格式的文件)，在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。

(6)当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；

(7)xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；

(8)innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；

(9)最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

4)安装流程：

rpm包安装：

https://www.percona.com/downloads/XtraBackup/LATEST/

mysqldump：

MySQL的逻辑备份工具，不需要额外安装。容易使用，支持细粒度备份恢复方便。缺点是备份和恢复速度比较慢。

常用选项：--single-transaction -> InnoDB引擎使用，begin work开始一个全局性的事务，保证了整个备份的一致性，同时又没有锁表操作

--lock-all-tables -> 备份期间为所有表施加读锁，会阻塞DML操作，适合MyISAM引擎。

--master-data=2 -> 在备份文件中记录当前二进制日志的位置