This is how I solved this problem:
- I ask
mysqldump --skip-extended-insert
to produce one INSERT INTO
per line:
INSERT INTO `action_events` VALUES (1,'8cecd83d-76b7-44d3-ad6a-5ad3ff6410dd');
INSERT INTO `action_events` VALUES (2,'8cecd8ba-465c-4d2e-b9f0-caf504817e3a');
INSERT INTO `action_events` VALUES (3,'8cecd961-03d1-461f-8425-067c1d01d238');
INSERT INTO `action_events` VALUES (4,'8cf08433-f262-443c-90d1-629dcd11c5aa');
INSERT INTO `xx` VALUES (5,'8cf08433-f262-443c-90d1-629dcd11c5aa');
INSERT INTO `yy` VALUES (6,'8cf08433-f262-443c-90d1-629dcd11c5aa');
- Using
awk
I join consecutive lines with INSERT INTO
by commma:
INSERT INTO `action_events` VALUES
(1,'8cecd83d-76b7-44d3-ad6a-5ad3ff6410dd'),
(2,'8cecd8ba-465c-4d2e-b9f0-caf504817e3a'),
(3,'8cecd961-03d1-461f-8425-067c1d01d238'),
(4,'8cf08433-f262-443c-90d1-629dcd11c5aa');
INSERT INTO `xx` VALUES
(5,'8cf08433-f262-443c-90d1-629dcd11c5aa');
INSERT INTO `yy` VALUES
(6,'8cf08433-f262-443c-90d1-629dcd11c5aa');
Here is the script join-insert-into
:
#!/bin/bash
# 1. print $0
# 2. print INSERT INTO
# 3. print (1,2,3),
# 4. print (1,2,3);
awk '
BEGIN { FS = "\n"; OFS = ""; max_allowed_packet = 25*1024*1024; }
END { if (prev_values) print prev_values ";" }
{
if (match($0, /^INSERT INTO [^ ]+ VALUES /)) {
table = substr($0, RSTART, RLENGTH - 1)
values = substr($0, RLENGTH + 1, length($0) - RLENGTH - 1)
if (prev_table != table) {
if (prev_values) {
print prev_values ";"
}
print table
packet = length(table) + 1
}
else {
packet += length(prev_values) + 2
if (packet >= max_allowed_packet) {
print prev_values ";"
print table
packet = length(table) + 1
}
else {
print prev_values ","
}
}
prev_table = table
prev_values = values
}
else {
if (prev_values) {
print prev_values ";"
prev_table = null
prev_values = null
}
print $0
}
}
'
It can be used like that:
# https://stackoverflow.com/a/34670902/1478566
MYSQL_PWD="$DB_PASSWORD" mysqldump \
--no-tablespaces --quick --skip-extended-insert \
-h$DB_HOST --port=${DB_PORT:-3306} \
-u$DB_USERNAME $DB_DATABASE "$@" | join-insert-into