MySQL F&Q

发布时间： 更新时间： 总字数：1147 阅读时间：3m 作者：IP：上海 网址

MySQL 常见问题整理

从节点落后主节点很大

  • show slave status\G; 中 Seconds_Behind_Source 的值很大，mysql 占用磁盘 IO 过高，可以优化
# sync_binlog = 1 表示每次事务提交都会将binlog的缓存写入磁盘，优化设置为 1000
mysql> show variables like '%sync_binlog%'
mysql> set global sync_binlog=1000;

# 设置 innodb_flush_log_at_trx_commit MySql 日志何时写入硬盘
mysql> show variables like '%innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
rows in set (0.00 sec)

mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)
  • innodb_flush_log_at_trx_commit 参数的值
    • 0 log buffer 将每秒一次地写入 log file 中，并且 log file 的 flush(刷到磁盘)操作同时进行
      • 该模式下在事务提交的时候，不会主动触发写入磁盘的操作
    • 1 每次事务提交，都会将 innodb 日志缓存写入磁盘，此时对磁盘效率影响很大
    • 2 每次事务提交时 mysql 都会把 log buffer 的数据写入 log file，flush(刷到磁盘)操作并不会同时进行，MySQL 会每秒执行一次 flush(刷到磁盘)操作

Error 1005 (HY000): Can’t create table ‘#sql-1_1a43f93’ (errno: 28)

  • 可能磁盘空间满了

Error 1114 (HY000): The table ’’ is full

  • 磁盘满了导致的

Error 1129 Host ‘xxx’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

错误链接数太多导致，比如主设置为 read_only 导致写入失败

mysqladmin flush-hosts
# 或
mysql -u [username] -p
> FLUSH HOSTS;

默认为 100，调整方法

show variables like "max_connect_errors";

SET GLOBAL max_connect_errors=10000;

# 写 my.cnf
[mysqld]
max_connect_errors=10000

Error 1366 (HY000): Incorrect string value: ‘\xFC\x00\x00\x00\x00\x00…’ for column

  • 原因字符集不兼容，解决方法（扩展Unicode 和 UTF-8 介绍）：
    • 调整 MySQL 字符集为 utf8mb4
    • 或剔除出入字符中包含的非 UTF-8 字符

Error 2006: MySQL server has gone away

  • 配置 my.cnf 并重启
[mysqld]
wait_timeout=90000

docker rootless cannot access ‘/var/lib/mysql-files’: Permission denied

  • secure_file_priv
> show global variables like '%secure_file_priv%';
# 启动时，使用 --secure-file-priv="/var/lib/mysql-files" 参数指定
  • keyring_file_data
> show global variables like '%keyring_file_data%';
+-------------------+--------------------------------+
| Variable_name | Value |
+-------------------+--------------------------------+
| keyring_file_data | /var/lib/mysql-keyring/keyring |
+-------------------+--------------------------------+
1 row in set (0.00 sec)
[mysqld]
keyring_encrypted_file_data = /var/lib/mysql-keyring

Package for query is too large (xxxxxxxx > 4194304). You can change this value on the server by setting the max_allowed_package variable

  • 查看
show VARIABLES like '%max_allowed_packet%';
  • 临时提升
# 100M
set global max_allowed_packet = 100*1024*1024;
  • 永久配置 my.cnf 中
[mysqld]
# max_allowed_packet=100*1024*1024
max_allowed_packet=100M

ibdata1 文件过大问题

  • File-Per-Table Tablespace Configuration
  • ibdata1 是一个用来构建 innodb 系统表空间的文件（也称为共享表空间），对应 MySQL 也提供独立表空间的存储方式，配置方法（生效需要重启 MySQL 服务）：
[mysqld]
innodb_file_per_table=1

MySQL 日志归档

sudo chmod -R 755 /var/log/mysql/
  • /etc/logrotate.d/mysql-log
/var/log/mysql/*.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 644 mysql mysql  # create 644 999 999
    sharedscripts
    postrotate
				# mysql -uroot -p"$MYSQL_ROOT_PASSWORD" -e "FLUSH ERROR LOGS; FLUSH GENERAL LOGS; FLUSH SLOW LOGS; FLUSH BINARY LOGS;"
				# docker exec -it <container-name> mysqladmin -p\${MYSQL_ROOT_PASSWROD} flush-logs
        /usr/bin/mysqladmin flush-logs
    endscript
}

测试

logrotate -vf /etc/logrotate.d/mysql-log

如果是 docker 容器中，mysqladmin 可以使用命令

docker exec -it xxx mysqladmin -p\${MYSQL_ROOT_PASSWROD} xxx

正在执行的事务

mysql 查询正在执行的事务：

SELECT * FROM information_schema.INNODB_TRX

MySQL如何定位一直存在的长事务

Select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t;

长事务带来的危害

  1. Undo Log（回滚段）无限膨胀： MySQL 的 MVCC（多版本并发控制）机制要求保留旧版本数据，以便这个 73 天前的事务能看到当时的数据视图。这会导致系统表空间（ibdata1）或 undo 表空间急剧增大，撑爆磁盘，且查询性能会因为需要扫描长长的版本链而大幅下降。
  2. 潜在的锁阻塞： 如果这两个事务之前执行过 UPDATE/DELETE（哪怕当前 trx_query 是 NULL），它们依然持有着行锁（Row Locks）。同时它们也会持有元数据锁（MDL），如果你此时对表执行 ALTER TABLE 加字段等 DDL 操作，会导致整个表被锁死。
  3. 消耗连接资源： 白白占用了数据库的连接数。

该如何处理？

第一步：定位来源（Kill 之前先查明真凶）

sql 
SELECT
    id, user, host, db, command, time, state, info
FROM
    information_schema.PROCESSLIST
WHERE
    id IN (17821, 17822);

第二步：紧急清理（杀掉长事务）

在 MySQL 命令行执行：

sql 
KILL 17821;
KILL 17822;
    最新评论