需求：将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 '创建时间',
