Requirement: Import mysql5.7.27 database to 8.0.13
Preparation before importing
MySQL8.0.13 database has been installed
--1. Increase the max_binlog_cache_size value and restart the library [root@localhost ys]# grep max_binlog /data/mysql813/my8.cnf max_binlog_size = 1G #Limit the size of a single file. The default size is: 1,073,741,824, which is 1G, which is too big. max_binlog_cache_size = 1024M #The original 50M is increased to 1024M, which can be flexibly adjusted according to the server memory size. --2. Change the sql_mode parameter (comment out the original parameter and add a new sql_mode value) Avoid Invalid default value for 'create_time' Reason: The exported version is not 8 and DEFAULT '0000-00-00 00:00:00' exists 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 --Restart mysql systemctl restart mysqld8 systemctl status mysqld8
Import steps
Method 1: When entering the library (writing logs)
--Import mysql -uroot -p firly mysql> tee /data/test_exp_$(date + %F).log; mysql> source /data/hbs_20230908.sql mysql> exit --Import content The firly20230908.sql.sql file is as follows: cat firly20230908.sql.sql source /data/hbs_20230908.sql; exit --View import log tail /gs/firly_imp20230908.log Query OK, 0 rows affected (0.00 sec) mysql> mysql> exit
Method 2: When logging into the database (writing logs)
mysql -u root -p -h192.168.3.130 --tee=/data/test_exp_$(date + %F).log
Parameter restoration after import
--1. Reduce the max_binlog_cache_size value [root@localhost ys]# grep max_binlog /data/mysql813/my8.cnf max_binlog_size = 1G #Limit the size of a single file. The default size is: 1,073,741,824, which is 1G, which is too big. max_binlog_cache_size = 50M #The original 50M is increased to 1024M, which can be flexibly adjusted according to the server memory size. --2. Change the sql_mode parameter (comment or delete the new sql_mode parameter and restore the original sql_mode value) Avoid Invalid default value for 'create_time' Reason: The exported version is not 8 and DEFAULT '0000-00-00 00:00:00' exists 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 --Restart mysql systemctl restart mysqld8 systemctl status mysqld8
Problem handling
Problem 1: max_binlog_cache_size is too small
Problem description
Import prompt [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, 'Zhengzhou Chaoli Lubricant Co., Ltd.', '91410104330078927L', 'Super Wet ", Null,' F ',' F ',' 5175 ', 'Li Xiaoduo', 100.000000 , NULL, NULL, NULL, NULL, NULL, 'No. 1403, Floor 14, Unit 1, Building 4, No. 37 Chengdongnan Road, Guancheng District, Zhengzhou City', '35675679', '450000', NULL, NULL, 'Sales: Lubricating Oil (Excluding repackaging), chemical products (except flammable, explosive and hazardous chemicals), hardware products, general mechanical equipment and accessories. ', NULL, '1100', '1150', '1151', '2015-02-10 00:00:00', NULL, '410104000130978', NULL, NULL, NULL, 241010401005000000, '2015-02-10 00 ; 00000000, 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, 'Zhengzhou Violet Hotel Management Consulting Co., Ltd.', NULL, 'Violet', NULL, '410104 ', 'L', '7249', 'Marin', 5.000000, NULL, NULL, NULL, NULL, NULL, 'No. 1002, 1st Floor, Unit 2, Yard 8, Shangcheng Road, Guancheng District, Zhengzhou City', '37910063', '450000', NULL, NULL, 'Hotel business management consulting.', 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, 'Agree', NULL, '06', 241010401000000000, 141000010001067897, 0 , 1, '01', 241010401000000000, 241010401000000000, 241010401000000000, '03', '9900', null, null, '22', null, null, '01', null, null, n Ull, null, 0 , NULL, 1, '1151', '1', NULL, NULL, NULL, NULL, NULL, '2016-01-19 11:18:46'), ('141000012151203330', 141000012144165510, 'Henan Qiaozhi Industrial Co., Ltd.', '91410100326886841M', 'Qiaozhi', NULL, '410100', 'F', '5135', 'Zhao Hui', 3001.000000, NULL, 0.000000, NULL, NULL, NULL, 'Dongfeng, Jinshui District, Zhengzhou City No. 1807, Floor 18, Building 21, No. 28 Road, '18037800679', '450000', NULL, NULL, 'Sales: electrical appliances, furniture, stationery, agricultural and sideline products, hotel supplies, kitchen equipment, electronic equipment; computer software development; Agent and publishing domestic advertising business; corporate marketing planning; corporate management consulting; engaged in import and export business of goods and technology. ', 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, 'agree', NULL, '06', 241010001000000000, 241010003104000039, 0, 1, '01', 2410100010 00000000, 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, 'Henan Chengban Industrial Co., Ltd.', '91410100330117551H', 'Chengban', NULL, '410101 ', 'L', '7297', 'Yang Binghui', 1008.000000, NULL, 0.000000, NULL, NULL, NULL, 'No. 1620, Floor 16, Building 2, No. 1 Shangdu Road, Zhengdong New District, Zhengzhou City', '13071723333', '450000', NULL, NULL, 'Wholesale and retail: office supplies, daily necessities, Wujijiaodian, building materials, electronic products, mechanical equipment, electrical equipment; engineering bidding agency, engineering manufacturing
[ERR] 48> 1197 - Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again
Cause of the problem
The mysql parameter max_binlog_cache_size is set too small, the default is 1M
max_binlog_cache_size represents the maximum cache memory size that binlog can use.
Analysis process
--1. Check the usage of binlog_cache_size root@localhost :servescience 09:55:47>show global status like 'bin%'; + ---------------------------- + ------- + | Variable_name | Value | + ---------------------------- + ------- + | Binlog_cache_disk_use | 9 | #The number of times temporary files are used to cache binary logs due to insufficient memory designed by binlog_cache_size | Binlog_cache_use | 72782 | #Number of times to cache using binlog_cache_size | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 149 | + ---------------------------- + ------- + Parameter Description: Binlog_cache_disk_use: The number of times temporary files are used to cache binary logs due to insufficient memory designed by binlog_cache_size. Binlog_cache_use: Number of times cached with binlog_cache_size When the value of Binlog_cache_disk_use is relatively large, you should consider appropriately increasing the value corresponding to binlog_cache_size. --2. Check the setting size [root@localhost ys]# grep max_binlog /data/mysql813/my8.cnf max_binlog_size = 1G #Limit the size of a single file. The default size is: 1,073,741,824, which is 1G, which is too big. max_binlog_cache_size = 50M or root@localhost :servescience 09:56:02>show global variables like '%binlog_cache%'; + ----------------------- + ---------- + | Variable_name | Value | + ----------------------- + ---------- + | binlog_cache_size | 4194304 | | max_binlog_cache_size | 52428800 | + ----------------------- + ---------- +
Problem handling
1.max_binlog_cache_size represents the maximum cache memory size that binlog can use.
When executing a multi-statement transaction, the memory used by all sessions exceeds the value of max_binlog_cache_size
An error will be reported: “Multi-statement transaction required more than max_binlog_cache_size’ bytes ofstorage”
2. If the setting is too large, it will consume more memory resources, depending on the size of the server memory; if the setting is too small, temporary files, namely disk, will be used.
--Increase max_binlog_cache_size vi /data/mysql813/my8.cnf max_binlog_cache_size = 1024M #The original 50M is increased to 1024M, which can be flexibly adjusted according to the server memory size. --Restart the library systemctl restart mysqld8 systemctl status mysqld8
Question 2: mysql8 date defaults to 0000 and reports an error Invalid default value for
Cause of the problem
Invalid default value for create_time’ Reason: The exported version is not 8 and DEFAULT 0000-00-00 00:00:00’ exists
Problem handling
--1. Change the sql_mode parameter in the mysql configuration file my.cnf 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. Restart mysql systemctl restart mysqld8 systemctl status mysqld8 --3. Check the sql_mode parameter 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 | + --------------- + ---------------------------------- -------------------------------------------------- ------- +
Supplement: If only individual tables are imported
--Scenario 1: timestamp field `CREATETIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Creation time', change to `CREATETIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time', --Scenario 2: date field `CREATETIME` date NOT NULL DEFAULT 0 COMMENT 'Creation time', change to `CREATETIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',