MySQL F&Q

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 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