更新mysql一张3千万大表 报错/tmp空间不足

测试环境全表跟新一个大表报错如下:

登录后复制

root@testdb 19:36:  [test001]> update test001.s_depreciation_data_table set original_bill_date =bill_date where bill_date < '202203';

ERROR 3 (HY000): Error writing file '/tmp/MLrLelNS' (Errcode: 28 - No space left on device)
1.
2.
3.

查看mysql的临时磁盘目录是 /tmp:

登录后复制

root@testdb 19:58:  [(none)]> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.01 sec)
1.
2.
3.
4.
5.
6.
7.

修改mysql的tmpdir目录为空间更大的目录:

登录后复制

root@testdb 20:52:  [(none)]> set global tmpdir = '/data1/3307/mysql/tmp';
ERROR 1238 (HY000): Variable 'tmpdir' is a read only variable
1.
2.

于是只能修改MySQL的配置文件 my.cnf ,重启mysql服务

登录后复制

vim /etc/my.cnf
tmpdir=/data1/3307/mysql/tmp
chown -R mysql.mysql  /data1/3307/mysql/tmp
1.
2.
3.

重启mysql服务,重新执行全表更新的sql:

登录后复制

root@testdb 20:00:  [(none)]> show variables like 'tmpdir';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| tmpdir        | /data1/3307/mysql/tmp |
+---------------+-----------------------+
1 row in set (0.01 sec)

root@testdb 19:59:  [test001]> update test001.s_depreciation_data_table set original_bill_date =bill_date where bill_date < '202203'; 

Query OK, 32920928 rows affected (38 min 21.40 sec)
Rows matched: 32920932  Changed: 32920928  Warnings: 0
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

总结:
1.类似这样的静态参数设置 在初始化mysql时，就要提前配置好。否则需要重启mysql服务才能配置参数生效。代价非常的大
2.生产环境严禁大表全表update更新 会产生大的事务，同时如果操作的库下面存在从库的话 从库的配置文件 参数max_binlog_cache_size 一旦设置的非常的小的话，从库的复制会报如下错:

登录后复制

transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again, Error_code: 1197; handler error HA_ERR_RBR_LOGGING_FAILED; 
1.

从库复制报错的解决办法:就是调大 max_binlog_cache_size 这个参数的数值

3.生产环境如果大表全表update更新，通常单个binlog文件默认是1G，但是此时单个binlog文件会非常的大,一条update语句居然生成了一个16G mysql-bin.000152 文件。生产上务必不要这么干。

友情提示：刚开通微信公众号,欢迎大家扫描关注