MySQL 5.6.24导入600g数据报错
本帖最后由 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
目前的参数如下：
default-character-set=utf8
default-character-set=utf8
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
max_allowed_packet = 1024M
第三次报错：
Terminal close -- sending "KILL QUERY 4" to server ...
Terminal close -- query aborted.
1. 一些参数可以适当调整下(仅供参考)
# 调整连接相关超时参数（单位：秒）
wait_timeout = 28800
interactive_timeout = 28800
net_read_timeout = 7200
net_write_timeout = 7200
connect_timeout = 60
# 调整数据包和缓存设置
max_allowed_packet = 1024M
bulk_insert_buffer_size = 512M
# InnoDB引擎优化
innodb_buffer_pool_size = 物理内存的60-80%
innodb_log_buffer_size = 256M
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 0
innodb_autoinc_lock_mode = 2
# 其他关键参数
max_connections = 300
thread_cache_size = 100
2. 或者拆分后，可以分批次导入
3. 建议换GreatSQL 可以使用GreatSQL的并行 LOAD DATA https://greatsql.cn/docs/8.0.32- ... -parallel-load.html
请补充
1. MySQL错误日志文件
2. 系统层dmesg输出结果
猜测是某些方面导致MySQL实例发生OOM被os kill了，所以需要日志辅助
另外，你的all.sql是从哪来的，可以考虑从源头规避问题，改成select into outfile导出文件，然后用GreatSQL的并行load data提高导入速度
也可以参考我这篇旧文，把sql文件切分成多个小文件，再分成多次导入 https://imysql.com/2009/04/16/extracting-a-database-from-a-mysqldump-file.html 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也没有导入异常导致数据库挂了的情况发生。
jian.tang 发表于 2025-3-13 11:05
1.all的文件是从window 上导出的，现在要迁移到linux上。
导出命令：mysqldump --all-databases -uroot -h ...
先提供我说的日志 yejr 发表于 2025-3-13 11:19
先提供我说的日志
dmesg日志： 最新的
[ 2.511341] Screen Object 2.
[ 2.511341] Command Buffers.
[ 2.511342] Command Buffers 2.
[ 2.511342] Guest Backed Resources.
[ 2.511342] DX Features.
[ 2.511343] HP Command Queue.
[ 2.511343] Capabilities2:
[ 2.511343] Grow oTable.
[ 2.511344] IntraSurface copy.
[ 2.511344] Max GMR ids is 64
[ 2.511345] Max number of GMR pages is 65536
[ 2.511345] Max dedicated hypervisor surface memory is 0 kiB
[ 2.511346] Maximum display memory size is 4096 kiB
[ 2.511347] VRAM at 0xf0000000 size is 4096 kiB
[ 2.511347] MMIO at 0xff000000 size is 256 kiB
[ 2.511348] global init.
[ 2.511396] Zonekernel: Available graphics memory: 32322058 kiB
[ 2.511396] Zone dma32: Available graphics memory: 2097152 kiB
[ 2.511397] Initializing pool allocator
[ 2.511400] Initializing DMA pool allocator
[ 2.511429] vmwgfx 0000:00:0f.0: BAR 1: can't reserve
[ 2.511430] It appears like vesafb is loaded. Ignore above error if any.
[ 2.511573] Supports vblank timestamp caching Rev 2 (21.10.2013).
[ 2.511574] No driver support for vblank timestamp query.
[ 2.511743] Screen Target Display device initialized
[ 2.511774] width 1024
[ 2.511781] height 768
[ 2.511787] bpp 32
[ 2.514018] Fifo max 0x00040000 min 0x00001000 cap 0x0000077f
[ 2.514554] Using command buffers with DMA pool.
[ 2.514558] DX: no.
[ 2.514558] Atomic: yes.
[ 2.514559] 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] 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: Received SIGTERM from PID 1 (systemd).
[ 3.446852] systemd: 18 output lines suppressed due to ratelimiting
[ 3.560084] systemd: 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: Detected virtualization vmware.
[ 3.560127] systemd: Detected architecture x86-64.
[ 3.562310] systemd: Set hostname to <sgev-dzpjdb03sgxycom>.
[ 3.582654] systemd-rc-local-generator: /etc/rc.d/rc.local is not marked executable, skipping.
[ 3.713292] systemd: /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: /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: /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: /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: initrd-switch-root.service: Succeeded.
[ 3.859222] systemd: Stopped Switch Root.
[ 3.875209] VFS: Open an write opened block device exclusively dm-1 .
[ 3.877055] Adding 8273916k swap on /dev/mapper/klas-swap.Priority:-2 extents:1 across:8273916k FS
[ 3.928029] systemd-journald: 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] : gsch_scan(inode=4585863,type=1,flags=0x0) - interrupted & wait(timeout=1000)
[ 3284.568263] : gsch_scan(inode=4585863,type=1,flags=0x0) - interrupted & wait: done
systemd-rc-local-generator: /etc/rc.d/rc.local is not marked executable, skipping.
systemd-rc-local-generator: /etc/rc.d/rc.local is not marked executable, skipping.
] *ERROR* Combined output size too large.
fuse init (API version 7.27)
: gsch_scan(inode=268579500,type=1,flags=0x0) - interrupted & wait(timeout=1000)
: gsch_scan(inode=268579500,type=1,flags=0x0) - interrupted & wait: done
ISO 9660 Extensions: Microsoft Joliet Level 3
ISO 9660 Extensions: RRIP_1991A
VFS: Open an exclusive opened block device for write sr0 .
VFS: Open an exclusive opened block device for write sr0 .
VFS: Open an exclusive opened block device for write sr0 .
VFS: Open an exclusive opened block device for write sr0 .
VFS: Open an exclusive opened block device for write sr0 .
VFS: Open an exclusive opened block device for write sr0 .
] *ERROR* Combined output size too large.
] *ERROR* Combined output size too large.
] *ERROR* Combined output size too large.
jian.tang 发表于 2025-3-13 11:31
dmesg日志： 最新的
[ 2.511341] Screen Object 2.
这个日志就是数据库启动后，进行数据导入的所有日志。
250311 17:05:48 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata
2025-03-11 17:05:48 0 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 AtLUA = Last Unlocked At
WOC = Waiting On ConditionDL = 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 @@GLOBAL.GTID_PURGED was changed from '' to 'aed7e5d2-fe34-11ea-a1d2-00155d0d552d:1-14824'.
2025-03-12 17:14:14 471898 @@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 AtLUA = Last Unlocked At
WOC = Waiting On ConditionDL = 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 @@GLOBAL.GTID_PURGED was changed from '' to 'aed7e5d2-fe34-11ea-a1d2-00155d0d552d:1-14824'.
2025-03-12 17:14:14 471898 @@GLOBAL.GTID_EXECUTED was changed from '' to 'aed7e5d2-fe34-11ea-a1d2-00155d0d552d:1-14824'.
页:
[1]