MySQL5.7逻辑导入MySQL8.0提示Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage

原创福娃筱欢恩墨学院 2023-10-15

201

需求：将mysql5.7.27数据库导入到8.0.13

导入前准备

已安装好MySQL8.0.13数据库

--1.增大max_binlog_cache_size值重启库
[root@localhost ys]# grep max_binlog /data/mysql813/my8.cnf
max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了
max_binlog_cache_size = 1024M  #原来的50M增大至1024M，根据服务器内存大小灵活调整

--2.更改sql_mode参数(注释掉原来的参数，添加新sql_mode值)
避免Invalid default value for 'create_time'  原因：导出版本不是8  存在DEFAULT '0000-00-00 00:00:00' 
vi /data/mysql813/my8.cnf
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

--重启mysql
systemctl restart mysqld8
systemctl status mysqld8
复制

导入步骤

法一：进入库时(写日志)

--导入
mysql -uroot -p firly
mysql> tee /data/test_exp_$(date +%F).log;
mysql> source /data/hbs_20230908.sql
mysql> exit

--导入内容
firly20230908.sql.sql文件如下：
cat firly20230908.sql.sql
source /data/hbs_20230908.sql;
exit

--查看导入日志
tail /gs/firly_imp20230908.log
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> exit
复制

法二：登录库时(写日志)

mysql -u root -p -h192.168.3.130 --tee=/data/test_exp_$(date +%F).log
复制

导入后参数恢复

--1.缩小max_binlog_cache_size值
[root@localhost ys]# grep max_binlog /data/mysql813/my8.cnf
max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了
max_binlog_cache_size = 50M   #原来的50M增大至1024M，根据服务器内存大小灵活调整

--2.更改sql_mode参数(注释或删除新的sql_mode参数，恢复原sql_mode值)
避免Invalid default value for 'create_time'  原因：导出版本不是8  存在DEFAULT '0000-00-00 00:00:00' 
vi /data/mysql813/my8.cnf
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

--重启mysql
systemctl restart mysqld8
systemctl status mysqld8
复制

问题处理

问题1：max_binlog_cache_size过小

问题描述

导入提示[ERR] 48> 1197 - Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes of storage; increase this mysqld variable and try again

[ERR] 48> INSERT INTO `reg_marpripinfo` (`ID`, `NAMEPREAPPRID`, `ENTNAME`, `UNISCID`, `ENTTRA`, `GRPSHFORM`, `OPLOCDISTRICT`, `INDUSTRYPHY`, `INDUSTRYCO`, `LEREP`, `REGCAP`, `REGCAPCUR`, `RECCAP`, `FORRECCAP`, `FORREGCAP`, `CONGRO`, `DOM`, `TEL`, `POSTALCODE`, `EMAIL`, `ABUITEMCO`, `OPSCOPE`, `PTBUSSCOPE`, `ENTTYPE`, `ENTTYPEITEM`, `ENTTYPEMINU`, `OPFROM`, `OPTO`, `REGNO`, `OLDREGNO`, `FORREGNO`, `SUPERVPER`, `SUPERORGID`, `ESTDATE`, `APPRDATE`, `PERID`, `ACCOPIN`, `REMARK`, `STATE`, `ORGID`, `JOBID`, `ADBUSIGN`, `TOWNSIGN`, `REGTYPE`, `PRIORGID`, `SUPERPRIORGID`, `APPRORGID`, `ENTTYPEPRO`, `OPTYPE`, `EMPNUM`, `COMPFORM`, `SUPDISTRICT`, `VENIND`, `PARNUM`, `EXENUM`, `OPFORM`, `INSFORM`, `HYPOTAXIS`, `FORCAPINDCODE`, `MIDPREINDCODE`, `PROTYPE`, `IMPDATESIGN`, `OPLOC`, `COPYNUM`, `ENTTYPEGB`, `COMPFORMGB`, `HOTINDFOCUS`, `PARFORM`, `INDUSTRYPHYGB`, `INDUSTRYCOGB`, `APPPERID`, `TIMESTAMP`) VALUES ('141000012194329608', 141000012150972587, '郑州超惠润滑油有限公司', '91410104330078927L', '超惠', NULL, '410104', 'F', '5175', '李小多', 100.000000, NULL, NULL, NULL, NULL, NULL, '郑州市管城区城东南路37号院4号楼1单元14层1403号', '35675679', '450000', NULL, NULL, '销售：润滑油（不含分装）、化工产品（易燃易爆及危险化学品除外）、五金产品、普通机械设备及配件。', NULL, '1100', '1150', '1151', '2015-02-10 00:00:00', NULL, '410104000130978', NULL, NULL, NULL, 241010401005000000, '2015-02-10 00:00:00', '2015-02-10 00:00:00', 141000010001127345, '同意', NULL, '06', 241010401000000000, 141000010001067897, 0, 1, '01', 241010401000000000, 241010401000000000, 241010401000000000, '03', '9900', NULL, NULL, NULL, '22', NULL, NULL, NULL, '01', NULL, NULL, NULL, NULL, 0, NULL, 1, '1151', '1', NULL, NULL, NULL, NULL, NULL, '2017-07-12 13:02:48'), ('141000012195083945', 141000012148239821, '郑州紫罗兰酒店管理咨询有限公司', NULL, '紫罗兰', NULL, '410104', 'L', '7249', '马林', 5.000000, NULL, NULL, NULL, NULL, NULL, '郑州市管城区商城路8号院2单元1楼附1002号', '37910063', '450000', NULL, NULL, '酒店企业管理咨询。', NULL, '1100', '1150', '1151', '2015-02-10 00:00:00', NULL, '410104000131028', NULL, NULL, NULL, 241010401002000000, '2015-02-10 00:00:00', '2015-02-10 00:00:00', 141000010001127345, '同意', NULL, '06', 241010401000000000, 141000010001067897, 0, 1, '01', 241010401000000000, 241010401000000000, 241010401000000000, '03', '9900', NULL, NULL, NULL, '22', NULL, NULL, NULL, '01', NULL, NULL, NULL, NULL, 0, NULL, 1, '1151', '1', NULL, NULL, NULL, NULL, NULL, '2016-01-19 11:18:46'), ('141000012151203330', 141000012144165510, '河南省桥秩实业有限公司', '91410100326886841M', '桥秩', NULL, '410100', 'F', '5135', '赵辉', 3001.000000, NULL, 0.000000, NULL, NULL, NULL, '郑州市金水区东风路28号院21号楼18层1807号', '18037800679', '450000', NULL, NULL, '销售：电器、家具、文具、农副产品、酒店用品、厨房设备、电子设备；计算机软件开发；代理、发布国内广告业务；企业营销策划；企业管理咨询；从事货物及技术的进出口业务。', NULL, '1100', '1150', '1151', '2015-01-14 00:00:00', NULL, '410100000140913', NULL, NULL, NULL, 241010501000000000, '2015-01-14 00:00:00', '2018-07-05 00:46:39', 141000010001181470, '同意', NULL, '06', 241010001000000000, 241010003104000039, 0, 1, '01', 241010001000000000, 241010501000000000, 241010001000000000, '03', '9900', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '01', NULL, NULL, NULL, NULL, 0, NULL, 1, '1151', '1', NULL, NULL, NULL, NULL, NULL, '2019-03-07 14:28:35'), ('141000012194102609', 141000012178849127, '河南诚伴实业有限公司', '91410100330117551H', '诚伴', NULL, '410101', 'L', '7297', '杨炳辉', 1008.000000, NULL, 0.000000, NULL, NULL, NULL, '郑州市郑东新区商都路1号卖场二号楼16层1620号', '13071723333', '450000', NULL, NULL, '批发零售：办公用品、日用百货、五金交电、建筑材料、电子产品、机械设备、电气设备；工程招标代理、工程造
[ERR] 48> 1197 - Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
复制

问题原因

mysql参数max_binlog_cache_size设置过小导致，默认1M
max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小

分析过程

--1.检查binlog_cache_size的使用情况
root@localhost :servescience 09:55:47>show global status like 'bin%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Binlog_cache_disk_use      | 9     | #因binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数
| Binlog_cache_use           | 72782 | #用binlog_cache_size缓存的次数
| Binlog_stmt_cache_disk_use | 0     |
| Binlog_stmt_cache_use      | 149   |
+----------------------------+-------+

参数说明：
Binlog_cache_disk_use：因binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数
Binlog_cache_use：用binlog_cache_size缓存的次数
Binlog_cache_disk_use 值比较大的时候要考虑适当的调高 binlog_cache_size 对应的值

--2.查看设置大小
[root@localhost ys]# grep max_binlog /data/mysql813/my8.cnf
max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了
max_binlog_cache_size = 50M
或
root@localhost :servescience 09:56:02>show global variables like '%binlog_cache%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| binlog_cache_size     | 4194304  |
| max_binlog_cache_size | 52428800 |
+-----------------------+----------+

复制

问题处理

1.max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小
当执行多语句事务的时候所有session的使用的内存超过max_binlog_cache_size的值时
就会报错：“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes ofstorage”
2.设置太大的话，会比较消耗内存资源，视服务器内存大小扩大;设置太小又会使用到临时文件即disk

--增大max_binlog_cache_size
vi /data/mysql813/my8.cnf
max_binlog_cache_size = 1024M  #原来的50M增大至1024M，根据服务器内存大小灵活调整

--重启库
systemctl restart mysqld8
systemctl status mysqld8
复制

问题2：mysql8 日期默认为0000 报错 Invalid default value for

问题原因

Invalid default value for ‘create_time’ 原因：导出版本不是8 存在DEFAULT ‘0000-00-00 00:00:00’

问题处理

--1.更改mysql的配置文件my.cnf 中sql_mode参数
vi /data/mysql813/my8.cnf
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

--2.重启mysql
systemctl restart mysqld8
systemctl status mysqld8

--3.查看sql_mode参数
root@localhost :(none) 10:53:13>show session variables like '%sql_mode%';
+---------------+------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                    |
+---------------+------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------+
复制

补充：如果只是个别表导入

--场景1：timestamp字段
`CREATETIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
更改为
`CREATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

--场景2：date字段
`CREATETIME` date NOT NULL DEFAULT 0 COMMENT '创建时间',
更改为
`CREATETIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
复制