【运维专线】线上运维累积

# 1、数据库相关

1.1、 查看进程的技巧

1.1.1、MySQL show processlist

show processlist 显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限

show PROCESSLIST
或
SELECT * FROM INFORMATION_SCHEMA.`PROCESSLIST`

应用场景：

• 执行时间特别长的线程
• 等待的线程
• 死锁的线程

如存在上述情况，我们杀掉对应线程

kill id

1.1.2、MySQL 控制台登陆

mysql -h 主机名 -P 端口号 -u 用户名 -p密码

mysql -h localhost -P 3306 -u root -proot

1.1.3、Oracle 查看进程

• 查看正在运行的进程

SELECT * FROM V$SESSION;

• 查看所有锁的信息

SELECT * FROM V$LOCK;

• 查询被锁的表

SELECT * 
  FROM V$LOCK_OBJECT t1 
  JOIN DBA_OBJECTS t2 
    ON t1.OBJECT_ID = t2.OBJECT_ID
 WHERE t1.OBJECT_ID = 5187

• 杀掉对应进程即解锁

ALERT SYSTEM KILL SESSION '5187'

1.2、查看数据库哪些表使用了分区

一般接手新项目或者运维新数据库时需查询对应数据库是否使用分区，如使用分区那就需要查看对应是否超出对应分区

1.2.1、MySQL查看数据库哪些表使用了分区

• 查询数据库中那些表使用了分区

SELECT DISTINCT TABLE_NAME 
  FROM INFORMATION_SCHEMA.PARTITIONS 
 WHERE PARTITION_NAME IS NOT NULL 
   AND TABLE_SCHEMA = 'jsdlshow_org'

• 查询数据库中分区表中最新的分区
  如分区是按时间分区，而最新分区时间又快不够时，可添加新的分区

WITH PARTITIONS_TEMP AS (
	SELECT TABLE_NAME,
		     PARTITION_NAME,
		     ROW_NUMBER() OVER ( PARTITION BY TABLE_NAME ORDER BY PARTITION_ORDINAL_POSITION DESC ) ROW_NUM 
	  FROM INFORMATION_SCHEMA.PARTITIONS 
	 WHERE PARTITION_NAME IS NOT NULL 
		 AND TABLE_SCHEMA = 'jsdlshow_org')
SELECT * FROM PARTITIONS_TEMP 
  WHERE ROW_NUM = 1

1.2.2、Oracle查看数据库哪些表使用了分区

• 查询数据库中那些表使用了分区

SELECT * FROM DBA_TABLES WHERE PARTITIONED = 'YES' AND OWNER = 'jsdlshow_org'

• 查询数据库中分区表中最新的分区
  如分区是按时间分区，而最新分区时间又快不够时，可添加新的分区

WITH DBA_TAB_PARTITIONS_TEMP AS (
	SELECT
		tl.TABLE OWNER,
		t1.TABLE NAME,
		tl.PARTITION NAME,
		ROW_NUMBER() OVER ( PARTITION BY TABLE_NAME ORDER BY PARTITION_NAME DESC ) row_num 
	FROM
		DBA_TAB_PARTITIONS t 
	WHERE
	TABLE_NAME IN ( SELECT a.TABLE_NAME FROM dba TABLES a WHERE PARTITIONED = 'YES' )) 
SELECT * FROM DBA_TAB_PARTITIONS_TEMP WHERE row_num = 1;

1.3、创建分区

1.3.1、MySQL 创建分区

Maximum number of partitions. The maximum possible number of partitions for a given table not using the NDB storage engine is 8192. This number includes subpartitions. 大概以上就是不是用NDB存储引擎单表最大的分区数量最多不超过8192，如下SQL查询当前数据库设置最大的分区数设置的数量

SHOW GLOBAL VARIABLES LIKE 'open_files_limit'

超过设置的分区数量在创建分区会出现错误Too many partitions (including subpartitions) were defined

分区字段为主键或主键的一部分,否则会报错，因为MySQL需要通过分区字段的值来确定每一行应该属于哪个分区,如果分区字段不是主键,在进行重组或划分分区时就无法快速准确地确定行所属的分区

CREATE TABLE orders (
  id INT,
  order_date DATE,
  amount DECIMAL(10,2),
  constraint PK_ORDERS(id,order_date)
)
PARTITION BY RANGE(TO_DAYS(order_date)) (
  PARTITION p20191231 VALUES LESS THAN (TO_DAYS('2020-01-01')),
  PARTITION p20200101 VALUES LESS THAN (TO_DAYS('2020-01-02')),
  PARTITION p20200102 VALUES LESS THAN (TO_DAYS('2021-01-03')),
  PARTITION p20200103 VALUES LESS THAN (TO_DAYS('2022-01-04'))
);

【强调】MySQL分区表添加分区时,不要每次只加一个分区,这样会非常慢而且对服务器造成压力，建议的按半年或一年的时间范围,一次性合并添加分区的做法,可以大大优化这个过程

• 减少多次单个添加分区造成的开销
• 减少ALTER TABLE对运行时性能的影响
• 可以提前规划分区范围,合并后批量添加
• 一定时间周期做一次,便于运维管理

错误示例脚本如下：

ALTER TABLE orders ADD PARTITION ( PARTITION p4 VALUES LESS THAN (TO_DAYS('2022-07-01')));
ALTER TABLE orders ADD PARTITION ( PARTITION p5 VALUES LESS THAN (TO_DAYS('2022-07-02')));
ALTER TABLE orders ADD PARTITION ( PARTITION p6 VALUES LESS THAN (TO_DAYS('2022-07-03')));
ALTER TABLE orders ADD PARTITION ( PARTITION p7 VALUES LESS THAN (TO_DAYS('2022-07-04')));
ALTER TABLE orders ADD PARTITION ( PARTITION p8 VALUES LESS THAN (TO_DAYS('2022-07-05')));
......

正确示例脚本如下：

ALTER TABLE orders ADD PARTITION (
  PARTITION p4 VALUES LESS THAN (TO_DAYS('2022-07-01')),
  PARTITION p5 VALUES LESS THAN MAXVALUE
);

1.3.1、Oracle 创建分区

参考：Oracle分区

1.4、MySQL 8.0新特性，大表快速添加列

在线DDL之 快速增加列（秒级别的），并不会造成业务抖动。该功能自 MySQL 8.0.12 版本引入，是由腾讯游戏DBA团队贡献，我国程序员还是挺厉害的嘛。注意一下，此功能只适用于 InnoDB 表。实际上MySQL 5.7就已支持 Online DDL，虽说大部分 DDL 不影响对表DML操作，但是依然会消耗非常多的时间，且占用额外的磁盘空间，并会造成主从延迟，或者影响表的查询速度。有了这个ALGORITHM=INSTANT 就可应对瞬息万变的需求了

• 使用 mysql5.7的INPLACE 算法

ALTER TABLE hr_user_info ADD COLUMN job_type_code varchar(255) NULL ,algorithm=INPLACE;

• MySQL8.0 Instant Add Column 秒级别添加

ALTER TABLE hr_user_info  ADD COLUMN job_type_code varchar(255) NULL,algorithm=instant;

1.5、MySQL 导出dump文件及导入dump文件

• 导出dump文件
  多个表名之间用空格隔开、–add-drop-table （如果表存在，需要删除原有表的话，加上这个）

mysqldump -uygdwtest -pJsdl1234! -h20.46.91.142 -P33066 --set-gtid-purged=OFF --no-tablespaces --add-drop-table --single-transaction --skip-tz-utc jsdlshow base_dt_cons base_dt_cons_certi >/opt/dump.sql

如果表太多，表可以存在在文件中

mysqldump -uygdwtest -pJsdl1234! -h20.46.91.142 -P33066 --set-gtid-purged=OFF --no-tablespaces --add-drop-table --single-transaction --add-drop-table --skip-tz-utc jsdlshow $(cat /opt/table_list.txt) > /opt/dump.sql

table_list.txt文件中写表示例如下

base_dt_cons
base_dt_cons_certificate
......

• 导入dump文件

mysql -uygdwtest -pJsdl1234! -h20.46.91.142 -P33066 jsdlshow < /opt/dump.sql

假设导出文件过大会导致导入数据库时候会出现错误，mysql:1153 Got a packet bigger than ‘max_allowed_packet’，此时我们就需要查看执行块大小的配置项即可

## max_allowed_packet参数是指mysql服务器端和客户端在一次传送数据包的过程当中最大允许的数据包大小
show variables like '%max_allowed_packet%'

## 发现默认大小不满足要求时设置满足的数据包大小即可，示例中临时设置512M
set global max_allowed_packet=524288000;

## 永久性设置在mysql配置文件中设置
max_allowed_packet=512M

1.6、MySQL 磁盘剩余空间不足情况

一般出现错误：Cause: java.sql.SQLException: The table ‘table_name’ is full情况下才会排查磁盘空间是否已满
/var/lib/mysql是MySQL数据库的默认数据目录,用于存储MySQL的数据文件
具体来说,/var/lib/mysql中包含了以下文件和目录:

• 数据文件夹:存放各个数据库的数据文件,如test数据库的test文件夹。
• ibdata文件:InnoDB存储引擎的表空间文件。
• 日志文件:错误日志、二进制日志、慢查询日志等。
• pid文件:MySQL正在运行时的进程ID号。
• socket文件:用于MySQL客户端连接的socket文件。
  将MySQL的数据目录设置为/var/lib/mysql是Linux系统上的默认位置。MySQL启动时会从此目录读取数据文件、日志文件等,以及一些配置信息

1.6.1、检查磁盘情况

df -h /var/lib/mysql

-- 执行效果如下，
Filesystem                Size  Used Avail Use% Mounted on
/dev/mapper/datavg-datalv 20G   20G  13M   100% /datafs

可以看到 /dev/mapper/datavg-datalv 分区使用率已经100%,需要扩磁盘或者删除没用的信息，例如日志啥。

1.6.2 卸载该分区

umount /dev/mapper/datavg-datalv

1.6.3 使用 fdisk 修改分区大小,比如扩展到100G

fdisk /dev/mapper/datavg-data
( dentro fdisk 按 n, p, 1, 设定起始柱面, 按 +100G 设定新的结束值 )  

1.6.4 格式化扩容后的分区

mkfs.ext4 /dev/mapper/datavg-datalv

1.6.5 挂载分区

mount /dev/mapper/datavg-datalv /

1.6.6 修改 MySQL 的 my.cnf, datadir 改为扩展后的分区

[mysqld]
datadir=/data1/mysql

1.6.7 重启 MySQL

service mysql restart

以上是具体的扩展 MySQL 磁盘空间的操作步骤,将根分区 /dev/sda1 扩展到100G来解决磁盘满的问题。可以根据实际情况调整分区和文件系统参数

如磁盘空间未满情况下，可修改如下参数

tem_table_size  = 128M
max_heap_table_size  = 128M

配置参数值改为:64M或者128M都行
tmp_table_size:

• 控制内存中临时表的最大大小,单位是字节。
• 当查询中的联结表或聚合运算结果超过此大小,会转换为磁盘临时表。
• 默认值是16MB。
  max_heap_table_size:
• 控制内存中堆表的最大大小,单位是字节。
• 堆表是一种MEMORY存储引擎的临时表,用于复杂查询。
• 当内存临时表超过tmp_table_size pero又小于max_heap_table_size时,会转成堆表。
• 默认值是16MB。
• 它的大小不能超过tmp_table_size。
  简单来说:
• tmp_table_size控制转储到磁盘临时表的阈值。
• max_heap_table_size控制转成内存堆表的阈值。
  合理设置这两个参数,可以控制好内存临时表的使用,防止大查询造成内存溢出

1.7、查询语句中CLOB看不到内容

Oracle中，可以通过使用DBMS_LOB包中的CLOB转换函数来将CLOB类型的数据转换为字符串。示例代码如下：

SELECT DBMS_LOB.SUBSTR(clob_column, 4000, 1) AS varchar_column
FROM your_table;

在这个示例中，clob_column是CLOB类型的列，使用DBMS_LOB.SUBSTR函数可以将其转换为VARCHAR2类型的字符串。函数的第一个参数是CLOB列，第二个参数是要转换的长度，第三个参数是偏移量。