GreatSQL社区

搜索

[待回复] MySQL 5.6.24导入600g数据报错

453 6 2025-3-13 10:30
本帖最后由 jian.tang 于 2025-3-13 10:52 编辑

使用mysql -uroot -p <all.sql方式导入

第一次报错:
ERROR 1317 (70100) at line 191526: Query execution was interrupted
Terminal close -- sending "KILL QUERY 6" to server ...
Terminal close -- query aborted.

第二次报错

ERROR 2006 (HY000) at line 8688: MySQL server has gone away
Terminal close -- sending "KILL QUERY 6" to server ...
Terminal close -- query aborted.


第三次导入:2025-03-10 9点35分-

调节参数
max_allowed_packet = 1024M



目前的参数如下:


[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
port = 3306
socket = /tmp/mysql3306.sock
basedir = /usr/local/mysql
datadir = /data/mysqldata
open_files_limit = 65535
back_log = 103
event_scheduler=on
max_connections =2000
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 128M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 51
query_cache_size = 32M
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysqldata/slow.log
log-error =/data/mysqldata/error.log
log_bin_trust_function_creators=1
long_query_time = 10
server-id = 35
log-bin = /data/mysqldata/mysql-bin
relay-log = /data/mysqldata/mysql-relay-bin
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 128M
max_binlog_size = 1024M
expire_logs_days = 14
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
character-set-server=utf8
default-storage-engine=InnoDB
binlog_format=row
gtid_mode=on
log_slave_updates=1
enforce_gtid_consistency=1
interactive_timeout=28800
wait_timeout=28800
transaction_isolation = READ-COMMITTED
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G
innodb_log_files_in_group = 3
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_open_files = 65534
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_status_file = 1
innodb_adaptive_hash_index = 0
#innodb_numa_interleave = ON

[mysqldump]
max_allowed_packet = 1024M



第三次报错:
Terminal close -- sending "KILL QUERY 4" to server ...
Terminal close -- query aborted.
全部回复(6)
KAiTO 2025-3-13 10:47:40
1. 一些参数可以适当调整下(仅供参考)

  1. # 调整连接相关超时参数(单位:秒)
  2. wait_timeout = 28800
  3. interactive_timeout = 28800
  4. net_read_timeout = 7200
  5. net_write_timeout = 7200
  6. connect_timeout = 60

  7. # 调整数据包和缓存设置
  8. max_allowed_packet = 1024M
  9. bulk_insert_buffer_size = 512M

  10. # InnoDB引擎优化
  11. innodb_buffer_pool_size = 物理内存的60-80%
  12. innodb_log_buffer_size = 256M
  13. innodb_log_file_size = 2G
  14. innodb_flush_log_at_trx_commit = 0
  15. innodb_autoinc_lock_mode = 2

  16. # 其他关键参数
  17. max_connections = 300
  18. thread_cache_size = 100
复制代码


2. 或者拆分后,可以分批次导入

3. 建议换GreatSQL 可以使用GreatSQL的并行 LOAD DATA https://greatsql.cn/docs/8.0.32- ... -parallel-load.html
yejr 2025-3-13 11:00:09
请补充
1. MySQL错误日志文件
2. 系统层dmesg输出结果
猜测是某些方面导致MySQL实例发生OOM被os kill了,所以需要日志辅助

另外,你的all.sql是从哪来的,可以考虑从源头规避问题,改成select into outfile导出文件,然后用GreatSQL的并行load data提高导入速度

也可以参考我这篇旧文,把sql文件切分成多个小文件,再分成多次导入 https://imysql.com/2009/04/16/ex ... mysqldump-file.html
jian.tang 2025-3-13 11:05:32
yejr 发表于 2025-3-13 11:00
请补充
1. MySQL错误日志文件
2. 系统层dmesg输出结果

1.all的文件是从window 上导出的,现在要迁移到linux上。
导出命令:mysqldump --all-databases -uroot -h 192.168.126.1 -S /tmp/mysql3306.sock -p'123456' -P 3307  --master-data=2  --single-transaction -R --triggers -A --set-gtid-purged=ON > all.sql
导入命令:使用这个脚本导入的,使用 nohup sh xx.sh &  
#!/bin/bash
DB_USER="root"
OUTPUT_FILE="all.sql"
/usr/local/mysql/bin/mysql  -u $DB_USER  -S /tmp/mysql3306.sock   < $OUTPUT_FILE

报错的内容是nohup 输出。 然后 数据库服务器也没有oom也没有导入异常导致数据库挂了的情况发生。
yejr 2025-3-13 11:19:42
jian.tang 发表于 2025-3-13 11:05
1.all的文件是从window 上导出的,现在要迁移到linux上。
导出命令:mysqldump --all-databases -uroot -h ...

先提供我说的日志
jian.tang 2025-3-13 11:31:17
yejr 发表于 2025-3-13 11:19
先提供我说的日志

dmesg日志: 最新的

[    2.511341] [drm]   Screen Object 2.
[    2.511341] [drm]   Command Buffers.
[    2.511342] [drm]   Command Buffers 2.
[    2.511342] [drm]   Guest Backed Resources.
[    2.511342] [drm]   DX Features.
[    2.511343] [drm]   HP Command Queue.
[    2.511343] [drm] Capabilities2:
[    2.511343] [drm]   Grow oTable.
[    2.511344] [drm]   IntraSurface copy.
[    2.511344] [drm] Max GMR ids is 64
[    2.511345] [drm] Max number of GMR pages is 65536
[    2.511345] [drm] Max dedicated hypervisor surface memory is 0 kiB
[    2.511346] [drm] Maximum display memory size is 4096 kiB
[    2.511347] [drm] VRAM at 0xf0000000 size is 4096 kiB
[    2.511347] [drm] MMIO at 0xff000000 size is 256 kiB
[    2.511348] [drm] global init.
[    2.511396] [TTM] Zone  kernel: Available graphics memory: 32322058 kiB
[    2.511396] [TTM] Zone   dma32: Available graphics memory: 2097152 kiB
[    2.511397] [TTM] Initializing pool allocator
[    2.511400] [TTM] Initializing DMA pool allocator
[    2.511429] vmwgfx 0000:00:0f.0: BAR 1: can't reserve [mem 0xf0000000-0xf7ffffff pref]
[    2.511430] [drm] It appears like vesafb is loaded. Ignore above error if any.
[    2.511573] [drm] Supports vblank timestamp caching Rev 2 (21.10.2013).
[    2.511574] [drm] No driver support for vblank timestamp query.
[    2.511743] [drm] Screen Target Display device initialized
[    2.511774] [drm] width 1024
[    2.511781] [drm] height 768
[    2.511787] [drm] bpp 32
[    2.514018] [drm] Fifo max 0x00040000 min 0x00001000 cap 0x0000077f
[    2.514554] [drm] Using command buffers with DMA pool.
[    2.514558] [drm] DX: no.
[    2.514558] [drm] Atomic: yes.
[    2.514559] [drm] SM4_1: no.
[    2.516048] checking generic (f0000000 300000) vs hw (f0000000 400000)
[    2.516049] fb: switching to svgadrmfb from EFI VGA
[    2.516067] Console: switching to colour dummy device 80x25
[    2.516193] fbcon: svgadrmfb (fb0) is primary device
[    2.516814] Console: switching to colour frame buffer device 128x48
[    2.526074] [drm] Initialized vmwgfx 2.15.0 20180704 for 0000:00:0f.0 on minor 0
[    2.642997] tsc: Refined TSC clocksource calibration: 2095.081 MHz
[    2.643087] clocksource: tsc: mask: 0xffffffffffffffff max_cycles: 0x1e3309d7adc, max_idle_ns: 440795300192 ns
[    2.643180] clocksource: Switched to clocksource tsc
[    2.986777] SGI XFS with ACLs, security attributes, no debug enabled
[    2.989084] XFS (dm-0): Mounting V5 Filesystem
[    3.028365] XFS (dm-0): Ending clean mount
[    3.401514] systemd-journald[281]: Received SIGTERM from PID 1 (systemd).
[    3.446852] systemd: 18 output lines suppressed due to ratelimiting
[    3.560084] systemd[1]: systemd v243-67.p05.ky10 running in system mode. (+PAM +AUDIT +SELINUX +IMA -APPARMOR +SMACK +SYSVINIT +UTMP +LIBCRYPTSETUP +GCRYPT +GNUTLS +ACL +XZ +LZ4 +SECCOMP +BLKID +ELFUTILS +KMOD +IDN2 -IDN +PCRE2 default-hierarchy=legacy)
[    3.560109] systemd[1]: Detected virtualization vmware.
[    3.560127] systemd[1]: Detected architecture x86-64.
[    3.562310] systemd[1]: Set hostname to <sgev-dzpjdb03sgxycom>.
[    3.582654] systemd-rc-local-generator[704]: /etc/rc.d/rc.local is not marked executable, skipping.
[    3.713292] systemd[1]: /run/systemd/generator.late/ds_agent.service:23: PIDFile= references a path below legacy directory /var/run/, updating /var/run/ds_agent.pid → /run/ds_agent.pid; please update the unit file accordingly.
[    3.761423] systemd[1]: /usr/lib/systemd/system/sssd.service:13: PIDFile= references a path below legacy directory /var/run/, updating /var/run/sssd.pid → /run/sssd.pid; please update the unit file accordingly.
[    3.776076] systemd[1]: /usr/lib/systemd/system/cups.socket:6: ListenStream= references a path below legacy directory /var/run/, updating /var/run/cups/cups.sock → /run/cups/cups.sock; please update the unit file accordingly.
[    3.785840] systemd[1]: /usr/lib/systemd/system/dbus.socket:5: ListenStream= references a path below legacy directory /var/run/, updating /var/run/dbus/system_bus_socket → /run/dbus/system_bus_socket; please update the unit file accordingly.
[    3.858892] systemd[1]: initrd-switch-root.service: Succeeded.
[    3.859222] systemd[1]: Stopped Switch Root.
[    3.875209] VFS: Open an write opened block device exclusively dm-1 [717 swapon].
[    3.877055] Adding 8273916k swap on /dev/mapper/klas-swap.  Priority:-2 extents:1 across:8273916k FS
[    3.928029] systemd-journald[729]: Received client request to flush runtime journal.
[    4.165625] input: PC Speaker as /devices/platform/pcspkr/input/input3
[    4.168342] piix4_smbus 0000:00:07.3: SMBus base address uninitialized - upgrade BIOS or use force_addr=0xaddr
[    4.173031] vmw_vmci 0000:00:07.7: enabling device (0000 -> 0003)
[    4.173931] vmw_vmci 0000:00:07.7: Found VMCI PCI device at 0x10800, irq 16
[    4.174074] vmw_vmci 0000:00:07.7: Using capabilities 0x8000003c
[    4.174997] Guest personality initialized and is active
[    4.175046] VMCI host device registered (name=vmci, major=10, minor=52)
[    4.175047] Initialized host personality
[    4.175181] input: VirtualPS/2 VMware VMMouse as /devices/platform/i8042/serio1/input/input5
[    4.175928] input: VirtualPS/2 VMware VMMouse as /devices/platform/i8042/serio1/input/input4
[    4.178485] RAPL PMU: API unit is 2^-32 Joules, 3 fixed counters, 10737418240 ms ovfl timer
[    4.178486] RAPL PMU: hw unit of domain pp0-core 2^-0 Joules
[    4.178487] RAPL PMU: hw unit of domain package 2^-0 Joules
[    4.178488] RAPL PMU: hw unit of domain dram 2^-16 Joules
[    4.184930] sd 0:0:0:0: Attached scsi generic sg0 type 0
[    4.184978] sr 1:0:0:0: Attached scsi generic sg1 type 5
[    4.265583] EDAC sbridge: Seeking for: PCI ID 8086:6fa0
[    4.265588] EDAC sbridge:  Ver: 1.1.2
[    4.353534] XFS (sda2): Mounting V5 Filesystem
[    4.377695] XFS (sda2): Ending clean mount
[    4.504784] RPC: Registered named UNIX socket transport module.
[    4.504785] RPC: Registered udp transport module.
[    4.504786] RPC: Registered tcp transport module.
[    4.504786] RPC: Registered tcp NFSv4.1 backchannel transport module.
[    5.160918] NET: Registered protocol family 40
[    5.253759] dsa_filter: loading out-of-tree module taints kernel.
[    5.254179] dsa_filter: module verification failed: signature and/or required key missing - tainting kernel
[    5.256204] dsa: loading filter 20.0.1.3447
[    5.260469] dsa: registered on device 10:50
[    5.260515] dsa: registered on ssl device 10:49
[    5.264288] dsa: filter loaded OK
[    5.556039] vmxnet3 0000:02:01.0 ens33: intr type 3, mode 0, 9 vectors allocated
[    5.557315] vmxnet3 0000:02:01.0 ens33: NIC Link is Up 10000 Mbps
[    9.565860] Redirecting File System Framework Version 0.10 <www.redirfs.org> with TrendMicro Patch 20.0.1.3447
[    9.580283] register_chrdev(gsch_dev) done: 241
[    9.580284] register_chrdev(ac_dev) done: 240
[    9.580300] gsch: loading vfs-filter 20.0.1.3447: OK
[    9.591970] gsch_dev_open(gsch_dev) doing
[    9.591972] gsch_dev_open() done: pid:2332(pool-ds_am)
[    9.950772] gsch_install_hooks(&gsch_hooks) done: 0
[ 3284.557650] [3956(mandb)]: gsch_scan(inode=4585863,type=1,flags=0x0) - interrupted & wait(timeout=1000)
[ 3284.568263] [3956(mandb)]: gsch_scan(inode=4585863,type=1,flags=0x0) - interrupted & wait: done
[492768.565893] systemd-rc-local-generator[235084]: /etc/rc.d/rc.local is not marked executable, skipping.
[493771.845434] systemd-rc-local-generator[236065]: /etc/rc.d/rc.local is not marked executable, skipping.
[578832.462448] [drm:vmw_kms_check_display_memory [vmwgfx]] *ERROR* Combined output size too large.
[578843.107843] fuse init (API version 7.27)
[578845.043177] [273225(mate-settings-d)]: gsch_scan(inode=268579500,type=1,flags=0x0) - interrupted & wait(timeout=1000)
[578845.045680] [273225(mate-settings-d)]: gsch_scan(inode=268579500,type=1,flags=0x0) - interrupted & wait: done
[578845.771547] ISO 9660 Extensions: Microsoft Joliet Level 3
[578845.772164] ISO 9660 Extensions: RRIP_1991A
[578848.416569] VFS: Open an exclusive opened block device for write sr0 [273238 caja].
[578848.450184] VFS: Open an exclusive opened block device for write sr0 [273238 caja].
[578848.473514] VFS: Open an exclusive opened block device for write sr0 [273238 caja].
[578848.495283] VFS: Open an exclusive opened block device for write sr0 [273238 caja].
[578848.523296] VFS: Open an exclusive opened block device for write sr0 [273238 caja].
[578849.403127] VFS: Open an exclusive opened block device for write sr0 [273238 caja].
[579241.486184] [drm:vmw_kms_check_display_memory [vmwgfx]] *ERROR* Combined output size too large.
[579241.937556] [drm:vmw_kms_check_display_memory [vmwgfx]] *ERROR* Combined output size too large.
[579242.489198] [drm:vmw_kms_check_display_memory [vmwgfx]] *ERROR* Combined output size too large.
jian.tang 2025-3-13 11:38:35
jian.tang 发表于 2025-3-13 11:31
dmesg日志: 最新的

[    2.511341] [drm]   Screen Object 2.

这个日志就是数据库启动后,进行数据导入的所有日志。

250311 17:05:48 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata
2025-03-11 17:05:48 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2025-03-11 17:05:
Table status:
Opened tables:         70
Open tables:           63
Open files:            19
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 0
Next alarm time: 0



Events status:
LLA = Last Locked At  LUA = Last Unlocked At
WOC = Waiting On Condition  DL = Data Locked

Event scheduler status:
State      : RUNNING
Thread id  : 1
LLA        : is_running:618
LUA        : is_running:620
WOC        : NO
Workers    : 0
Executed   : 0
Data locked: NO

Event queue status:
Element count   : 0
Data locked     : NO
Attempting lock : NO
LLA             : get_top_for_execution_if_time:572
LUA             : get_top_for_execution_if_time:593
WOC             : YES
Next activation : never
2025-03-12 17:14:14 471898 [Note] @@GLOBAL.GTID_PURGED was changed from '' to 'aed7e5d2-fe34-11ea-a1d2-00155d0d552d:1-14824'.
2025-03-12 17:14:14 471898 [Note] @@GLOBAL.GTID_EXECUTED was changed from '' to 'aed7e5d2-fe34-11ea-a1d2-00155d0d552d:1-14824'.

write:               4
delete               0
update:              0

Table status:
Opened tables:         70
Open tables:           63
Open files:            19
Open streams:           0

Alarm status:
Active alarms:   0
Max used alarms: 0
Next alarm time: 0



Events status:
LLA = Last Locked At  LUA = Last Unlocked At
WOC = Waiting On Condition  DL = Data Locked

Event scheduler status:
State      : RUNNING
Thread id  : 1
LLA        : is_running:618
LUA        : is_running:620
WOC        : NO
Workers    : 0
Executed   : 0
Data locked: NO

Event queue status:
Element count   : 0
Data locked     : NO
Attempting lock : NO
LLA             : get_top_for_execution_if_time:572
LUA             : get_top_for_execution_if_time:593
WOC             : YES
Next activation : never
2025-03-12 17:14:14 471898 [Note] @@GLOBAL.GTID_PURGED was changed from '' to 'aed7e5d2-fe34-11ea-a1d2-00155d0d552d:1-14824'.
2025-03-12 17:14:14 471898 [Note] @@GLOBAL.GTID_EXECUTED was changed from '' to 'aed7e5d2-fe34-11ea-a1d2-00155d0d552d:1-14824'.
jian.tang

5

主题

0

博客

26

贡献

新手上路

Rank: 1

积分
40

合作电话:010-64087828

社区邮箱:greatsql@greatdb.com

社区公众号
社区小助手
QQ群
GMT+8, 2025-4-2 01:06 , Processed in 0.025327 second(s), 19 queries , Redis On.
快速回复 返回顶部 返回列表