数据库日常维护

1. 修改连接数

1
2
3
set global max_connections=500;
mysql  -h 192.168.64.33 -P 6002 -uroot -p -e  "select @@hostname"
mysql  -h 192.168.64.33 -P 3306 -uroot -p -e  "set global max_connections=500;"

2. mysql备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
## 脚本全备到一个sql文件
sh /home/script/mysql_k8s-prod_bak-all-databases.sh
## 脚本全备到每个库一个sql文件（定时任务）
sh /home/script/mysql_k8s-prod_bak-all.sh
## 脚本可选择单库备份
sh /home/script/mysql_k8s-prod_bak.sh

# 备份全库
mysqldump -h 192.168.64.33 -P 6002 -uroot -pxxxx --all-databases --single-transaction --quick >all_databases-$(date +%Y%m%d-%H%M%S).sql

#导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mkdir -p /mysql_back/tables_bak/`date +%Y%m%d%H`/数据库名
mysqldump -h 192.168.64.33 -P 6001 -u root -p 数据库名 表名 > /mysql_back/tables_bak/`date +%Y%m%d%H`/数据库名/表名-$(date +%Y%m%d-%H%M%S).sql

# 导出一个表为csv格式
SELECT * FROM 表名 INTO OUTFILE '/var/lib/mysql-files/表名.csv' CHARACTER SET utf8mb4  FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

# 导出一个数据库结构
mysqldump -u root -p -d --add-drop-table 表名 > 表名.sql

# 导入一个表数据 
mysql -h 192.168.64.33 -P 6002 -u root -p 数据库名 表名 < /home/sql_init/库名/test-202311172019-1.sql

2.1 全库备份文件中恢复一张到数据到其它

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
1. 解压备份文件
unzip mysql_backup-202407060330.zip
# 由于文件解压后250G,会报：sed: regex input buffer length larger than INT_MAX
# 分隔成member_aa,member_ab,member_ac,member_ad,member_ae
split -b 50G  mysql_backup-202407060330.sql member_

2. 从全备备份文件中，挖出表名cm_t、bb_t的表结构,并输出到文件cm_t-create.sql、bb_t-create.sql
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `cm_t`/!d;q' mysql_backup-202407060330.sql > cm_t-create.sql
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `bb_t`/!d;q' mysql_backup-202407060330.sql > bb_t-create.sql

3.1 第2步找出来很慢，可以手动创建表结构

4. 从全备备份文件中，挖出表名cm_t、bb_t所有insert语句,并输出到文件cm_t-data.sql、bb_t-data.sql
grep -i 'INSERT INTO `cm_t`' mysql_backup-202407060330.sq > cm_t-data.sql
grep -i 'INSERT INTO `bb_t`' mysql_backup-202407060330.sql > bb_t-data.sql

4.1 从分隔后的文件找到对应的insert语句
grep -i -n 'INSERT INTO `cm_t`'  member_ad |more
    # 26073:INSERT INTO `cm_t`
    # 54507
grep -i 'INSERT INTO `cm_t`' member_ad >> cm_t-data.sql

grep -n 'INSERT INTO `bb_t`'  member_aa |more
  # 17383:INSERT INTO `bb_t`
wc -l < member_aa
  # 55735
grep -i 'INSERT INTO `bb_t`' member_aa > bb_t-data.sql

5. 新建临时库repl，将新表恢复到临时库中
# 恢复表结构
mysql -h 192.168.64.33 -P 3306 -u root -p repl  < cm_t-create.sql
mysql -h 192.168.64.33 -P 3306 -u root -p repl  < bb_t-create.sql
# 恢复表数据
mysql -h 192.168.64.33 -P 3306 -u root -p repl  < cm_t-data.sql
mysql -h 192.168.64.33 -P 3306 -u root -p repl  < bb_t-data.sql

3. sql常用语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# 清空表
TRUNCATE 表名;

# 从一个表中复制所有的列插入到另一个已存在的表中：
INSERT INTO table2 SELECT * FROM table1;

# 查看表字段字符
SHOW FULL COLUMNS FROM your_table_name;

# 查看变量
SHOW GLOBAL VARIABLES like "group_concat_max_len"

# 设置密码过期
ALTER USER 'user'@'host' PASSWORD EXPIRE INTERVAL N DAY;

# 查询过期设置
select User,password_last_changed,password_lifetime,password_expired from mysql.user;

#  查询
SELECT * FROM 表名 WHERE id = '10000000002';

# 删除ord_id为空的
DELETE FROM 表名 WHERE ord_id is NULL;

# 查询用户授权
select user,host,PLUGIN from mysql.user

4. 修改授权

1
2
3
4
5
6
# 修改授权
mysql> show grants for 'god'@'192.168.64.%'\G    # 查看权限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM god@'192.168.64.%';  # 收回权限
mysql> REVOKE ALL PRIVILEGES ON 库名.*  FROM god@'192.168.64.%';  # 收回权限
mysql> flush privileges;
mysql> drop user 'god'@'192.168.64.%';  # 删除用户

5. 查看连接数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 查看当前的连接数
show GLOBAL STATUS like 'Threads_connected'
show status like 'Threads%';

# 查看最大连接数
show variables like 'max_connections';

# 响应的最大连接数
show status like 'max_used_connections'
show global status like '%max%';

# 查询当前用户连接数和最大连接数
SELECT @@max_connections AS '最大连接数',
       (SELECT COUNT(*) FROM information_schema.processlist WHERE db IS NOT NULL) AS '当前连接数';
      
# 查询每个不同ip的连接数      
SELECT host, COUNT(*) AS '连接数' FROM information_schema.`PROCESSLIST` WHERE command != 'sleep' AND user != 'system user' GROUP BY HOST;

6. 设置pool_size

1
2
show  variables  like '%innodb_buffer_pool_size%';
SET GLOBAL innodb_buffer_pool_size=134217728;  #128M -- 单位kb 16777216=16M  8388608=8M

7. 清理内存

1
sync && echo 1 > /proc/sys/vm/drop_caches

8. 事务大执行不了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
show global variables like '%group_replication_transaction_size_limit%';
+------------------------------------------+-----------+
| Variable_name                            | Value     |
+------------------------------------------+-----------+
| group_replication_transaction_size_limit | 600000000 |
+------------------------------------------+-----------+
1 row in set (0.00 sec)

set global group_replication_transaction_size_limit=6000000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

show global variables like '%group_replication_transaction_size_limit%';  --2G
+------------------------------------------+------------+
| Variable_name                            | Value      |
+------------------------------------------+------------+
| group_replication_transaction_size_limit | 2147483647 |
+------------------------------------------+------------+
1 row in set (0.00 sec)

9. mysql kill id

1
mysql  -h 192.168.64.33 -P 6001 -uroot -pxxxx -e  "show processlist" |grep "executing" |awk '{print $1}' |xargs -i mysql  -h 192.168.64.33 -P 6001 -uroot -pxxxx -e "kill {}"

10. 大事务造成延迟

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
当Primary上有大事务产生时，很容易造成Secondary在应用大事务过程中存在延迟。
因此，要尽量避免执行大事务。可以将大事务拆分成多个小事务，例如当执行load data导入大批数据时，就可以将导入文件切分成多个小文件。
此外，还可以适当调低
group_replication_transaction_size_limit 阈值，限制事务大小。
 
还可以通过监控事务状态，防止有个别事务运行时间过久：
# 活跃时间最长的事务
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ASC LIMIT 10;
 
# 等待时间最长的事务
SELECT * FROM sys.innodb_lock_waits ORDER BY wait_age_secs DESC LIMIT 10;
 
# 要特别关注的大事务
-- 超过5把锁
-- 超过100行被锁
-- 超过100行被修改
SELECT * FROM information_schema.innodb_trx WEHRE
  trx_lock_structs >= 5 OR
  trx_rows_locked >= 100 OR
  trx_rows_modified >= 100 OR
  TIME_TO_SEC(TIMEDIFF(NOW(),trx_started)) > 100;

11 优化参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
# 如果单个事务太大，以至于在5秒钟内无法通过网络在组成员之间复制消息，则可能会怀疑成员失败，然后被移出组。由于内存分配问题，大型事务也可能导致系统速度变慢。要避免这些问题，使用以下缓解措施：
1. 尽可能尝试限制事务规模。例如，将与LOAD DATA一起使用的文件拆分为较小的块。
2. 使用系统变量group_replication_transaction_size_limit指定组接收的最大事务大小。超过此大小的事务将回滚，不会发送到组。在MySQL 8.0中，此系统变量缺省值为150000000字节（大约143 MB）。
3. 从MySQL 8.0.13开始，可以使用系统变量group_replication_member_expel_timeout来允许在怀疑失败的成员在被移出之前有更多的时间。可以在最初的5秒检测期后最多延长一个小时。
4. 从MySQL 8.0.16开始，大型消息会自动分段，这意味着大型消息不会触发引发怀疑的5秒检测周期，除非此时存在其它网络问题。为了使复制组使用分段，所有组成员必须处于MySQL 8.0.16或更高版本，并且组使用的组复制通信协议版本必须允许分段。如果MySQL版本不支持消息分段，可以使用系统变量group_replication_communication_max_message_size来调整最大消息大小，缺省值为10485760字节（10 MB），或通过指定零值来关闭分段

# 组接收的最大事务大小 已修改为：1G    600000000字节=572M  2147483648=2G  1073741824=1G
show global variables like '%group_replication_transaction_size_limit%';
STOP GROUP_REPLICATION;
set global group_replication_transaction_size_limit=1073741824;    # 设置为1G
START GROUP_REPLICATION;

# 允许在怀疑失败的成员在被移出之前有更多的时间,默认5s
show global variables like '%group_replication_member_expel_timeout%';
STOP GROUP_REPLICATION;
set global group_replication_member_expel_timeout=5;
START GROUP_REPLICATION;

# 调整最大消息大小，缺省值为10485760字节（10 MB）
show global variables like '%group_replication_communication_max_message_size%';
STOP GROUP_REPLICATION;
set global group_replication_communication_max_message_size=10485760;
START GROUP_REPLICATION;

# 故障节点尝试自动加入集群的次数，默认是3次
show global variables like '%group_replication_autorejoin_tries%';
STOP GROUP_REPLICATION;
set global group_replication_autorejoin_tries=3;
START GROUP_REPLICATION;

# 预估 XCom Cache, 5s检测时间段内的预期消息量,目前是1G
show global variables like '%group_replication_message_cache_size%';
STOP GROUP_REPLICATION;
set global group_replication_message_cache_size=1073741824;
START GROUP_REPLICATION;

# 未研究通其功能,目前是OFF
show global variables like '%group_replication_paxos_single_leader%';
set global group_replication_paxos_single_leader=ON;

# 使用单主模式,目前是ON
show global variables like '%group_replication_single_primary_mode%';
set global group_replication_single_primary_mode=ON;

# 流控制
show global variables like '%group_replication_flow_control_mode%';
group_replication_flow_control_mode = QUOTA 
 
# 键缓存的大小,默认8M=8388608
show global variables like '%key_buffer_size%';
set global key_buffer_size=16777216;

# 连接缓冲区的大小，默认256kb=262144
show global variables like '%join_buffer_size%';
set global join_buffer_size=524288;

12. mysql内存使用率高且不释放问题排查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';  -- 128M

SHOW VARIABLES LIKE 'query_cache_size';  -- NULL

SHOW VARIABLES LIKE 'tmp_table_size';  -- 16M

SHOW VARIABLES LIKE 'key_buffer_size';  -- 8M

SHOW VARIABLES LIKE 'table_cache';  -- NULL

SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size'; -- NULL

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; -- 等到innodb_log_buffer_size列队满后再统一储存,默认为1


SHOW VARIABLES LIKE 'innodb_log_buffer_size';   -- 16M

show global status like '%tmp%'

show global variables like '%tmp_table%'; -- 16M -- 发现数据库创建了大量的临时表，并且仍在频繁的创建


# gdb --batch --pid ‘pidof mysqld’ --ex 'call malloc_trim(0)'； -- gdb回收内存碎片，生产环境慎用
pidof mysqld
gdb --batch --ex 'file /usr/sbin/mysqld' --pid 3803632 --ex 'call (void)malloc_trim(0)'

-- MySQL进程配置jemalloc内存管理模块
1
2
3
4
5
6
7
8
9
10
11
# 测试环境内存告警
1. 减少innodb_buffer_pool_size大小
SET GLOBAL innodb_buffer_pool_size= 16777216; -- 单位kb =16M  8388608=8M
2. 设置了连接等待时间
-- 服务器关闭交互式连接前等待活动的秒数
set global interactive_timeout=1200;
-- 服务器关闭非交互连接之前等待活动的秒数
SET GLOBAL wait_timeout=1200;    -- 20分钟

mysql  -h 192.168.64.33 -P 6001 -uroot -pxxxxxx -e  "SET GLOBAL tmp_table_size=8388608;"
mysql  -h 192.168.64.33 -P 6001 -uroot -pxxxxxx -e  "SET GLOBAL innodb_buffer_pool_size=8388608;"

13 binlog恢复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 1. 找到binlog日志目录及文件
mysql> show binlog events;   #只查看第一个binlog文件的内容
mysql> show binlog events in 'mysqlbinlog.000002';#查看指定binlog文件的内容
mysql> show binary logs;  #获取binlog文件列表
mysql> show master status; #查看当前正在写入的binlog文件
# 2. 查看这个binlog的内容
show binlog events in 'mysqlbinlog.000004';
- 清空表数据的记录行，Event_type是Delete_rows；
- 新增数据的Event_type是Write_rows；
- 分析这个binlog，比如还原刚才被删除的数据，因此找到create table语句后的第一个Event_type是Query的行，它的Pos为460；再找到Event_type是Delete_rows上面最接近的、Info中是COMMIT的行，它的Event_type是Xid，End_log_pos是1199
# 3. 还原数据
mysqlbinlog --no-defaults --start-position=460 --stop-position=1199 "/var/lib/mysql/mysqlbinlog.000004" -d test | mysql -uroot -proot test
或者，把binlog日志中，指定范围内的日志导出，然后自己执行source命令还原数据
mysqlbinlog "/var/lib/mysql/mysqlbinlog.000004" -d test  --skip-gtids  --start-position=460 --stop-position=1199 > test.sql
mysql> use test; 
mysql> source test.sql;
1
2
3
4
5
6
7
8
9
10
11
# 获取binlog文件列表 
mysql> show binary logs;
# 对一个二进制文件的事务做过滤查询
SHOW BINLOG EVENTS IN 'mysql-binlog.000015' FROM 194 LIMIT 2;
show binlog events in 'mysql-binlog.000015' from 666 limit 1, 2;

# 过滤一天的某表的日志
mysqlbinlog --start-datetime="2024-01-17 00:00:00" --stop-datetime="2024-01-17 23:59:59" /home/mysql/data1/binlog_data/mysql-binlog.000015 -vv |grep -A 10 userapp
mysqlbinlog --start-datetime="2024-01-17 00:00:00" --stop-datetime="2024-01-17 23:59:59" /home/mysql/data1/binlog_data/mysql-binlog.000015 -vv |grep -w '### INSERT INTO `store`.`userapp`' |wc -l
/usr/bin/mysqlbinlog --start-datetime="2024-01-17 00:00:00" --stop-datetime="2024-01-17 23:59:59" /var/lib/mysql/binlog.000001 -vv |grep -w '### INSERT INTO' |wc -l
mysqlbinlog --start-datetime="2024-01-17 00:00:00" --stop-datetime="2024-01-17 23:59:59" /home/mysql/data1/binlog_data/mysql-binlog.000015 -vv |grep -A 10 -w '### INSERT INTO `store`.`userapp`' |grep -w 'server id' |awk '{print $1}' |sed 's/#//g'
1
2
3
4
5
6
7
8
9
10
11
12
# 查看binlog日志
## 显示二进制
mysqlbinlog --start-datetime="2024-07-09 11:36:00" --stop-datetime="2024-07-09 11:38:00" mgr-bin.002522 |more
## 显示正常的sql
mysqlbinlog --base64-output=decode-rows --verbose --start-datetime="2024-07-11 05:02:00" --stop-datetime="2024-07-11 05:04:00" mgr-bin.002590 |more >/tmp/output.sql

# 统计今天创建的文件数
find . -type f -newermt $(date +%Y-%m-%d) -print | wc -l

# 删除10天的binlog日志
find /home/mysql/binlog_data/* -mtime +10 |wc -l
find /home/mysql/binlog_data/* -mtime +10 -exec rm -rf {} \;

14. Error 1260 (HY000): Row 3672700 was cut by GROUP_CONCAT()

1
2
3
4
5
6
7
8
9
-- GROUP_CONCAT是MySQL中的一个聚合函数,它用于将多行数据按照指定的顺序连接成一个字符串并返回结果
-- group_concat函数是mysql中非常实用的函数，它可以将同一个分组下的行拼接在一起
 
show variables like "group_concat_max_len";
SET GLOBAL group_concat_max_len = 10240000;

-- 生产三台数据库均执行，由1024000修改为10240000
mysql  -h 192.168.64.33 -P 3306 -uroot -p -e  "show variables like 'group_concat_max_len';"
mysql  -h 192.168.64.33 -P 3306 -uroot -p -e "SET GLOBAL group_concat_max_len = 10240000;"