Question 21958 in Mysql Question posted in
The official MySQL documentation can be found here.

Can't take mysqldump of long argument list

I’ve tried using previously accepted solutions for my problem, but they aren’t working:

Example "id_list"

[jlefler@server ~]$ cat id_list | tail -10
17900018
17900019
17900020
17900021
17900022
17900023
17900024
17900025
17900026
17900027[jlefler@server ~]$ cat id_list |wc -l
51624

Command I’m trying to execute to dump 2000 id’s at a time:

xargs -a id_list -d 'n' -n 2000 sh -c 'mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in ($0 $@)" >> dump.sql' x

However I get this unexpected output:

[jlefler@server ~]$ xargs -a id_list -d 'n' -n 2000 sh -c 'mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in ($0 $@)" >> dump.sql' x
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17851966
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17853966
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17855967
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17857967
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17859975
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17861978
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17863978
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17865978
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17867978
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17869978
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17871978
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17873979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17875979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17877979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17879979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17881979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17883979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17885979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17887979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17889979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17891979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17893979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17895979
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17897980
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17899986
Warning: Using a password on the command line interface can be insecure.
"ysqldump: Couldn't find table: "17850378

Basically, the end result should be something like this:

mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in (17900018,17900019,17900020...)" >> dump.sql

It takes 2000 IDs at a time adds them to dump.sql and then goes to the next 2000 until all the IDs are read from the file.

With echo command to display output, I get the following:

)17853963ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17851964
)17855964ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17853964
)17857964ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17855965
)17859972ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17857965
)17861975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17859973
)17863975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17861976
)17865975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17863976
)17867975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17865976
)17869975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17867976
)17871975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17869976
)17873976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17871976
)17875976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17873977
)17877976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17875977
)17879976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17877977
)17881976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17879977
)17883976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17881977
)17885976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17883977
)17887976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17885977
)17889976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17887977
)17891976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17889977
)17893976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17891977
)17895976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17893977
)17897977ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17895977
)17899983ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17897978
)17850375ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (17899984
 17900027)mp -uuser -ppassword --compact --no-create-info db table_name --where=id in (17850376
)17853963ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17851964
)17855964ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17853964
)17857964ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17855965
)17859972ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17857965
)17861975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17859973
)17863975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17861976
)17865975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17863976
)17867975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17865976
)17869975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17867976
)17871975ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17869976
)17873976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17871976
)17875976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17873977
)17877976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17875977
)17879976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17877977
)17881976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17879977
)17883976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17881977
)17885976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17883977
)17887976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17885977
)17889976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17887977
)17891976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17889977
)17893976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17891977
)17895976ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17893977
)17897977ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17895977
)17899983ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17897978
)17850375ump -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17899984
 17900027)mp -uuser -ppassword --compact --no-create-info db table_name --where=id in (x 17850376
Tags:

2

Answers


  1. 0

    NOTES:

    • OP’s current syntax issue appears to be due to dos/windows line endings (rn) in the input file (id_list)
    • assuming OP can remove the r characters the next issue will likely be an invalid in() list … which is what this answer will focus on …

    Sample input file:

    $ cat id_list
1
2
3
4
5
6
7
8
9
10

    Modified xargs call:

    $ xargs -a id_list -n 3
1 2 3
4 5 6
7 8 9
10

    Adding echo to see what the current code is generating:

    $ xargs -a id_list -n 3 bash -c 'echo mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in ($0 $@)"'
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where=id in (1 2 3)
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where=id in (4 5 6)
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where=id in (7 8 9)
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where=id in (10 )

    NOTES:

    • OP has tagged the question with bash so I’ve replaced sh -c with bash -c
    • from this output it appears we need to replace spaces with commas for the --where/in() clause
    • it also appears we need to add a pair of escaped double quotes in order for them to show up around the --where/in() clause

    One idea for addressing these issues:

    $ xargs -a id_list -n 3 bash -c 'list=$(sed "s/ /,/g;s/,$//g" <<< "$0 $@"); echo mysqldump -uuser -ppassword --compact --no-create-info db table_name --where=""id in ($list)""'
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where=id mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in (1,2,3)"
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in (4,5,6)"
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in (7,8,9)"
mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in (10)"
    Login or Signup to reply.
  2. 0

    I have a different way of approaching to this problem (without xargs).
    I tried to re-create your scenario and it is working for me.
    Have a look below.

    Generating Sample file for Ids:

    for i in {1000..15000};do echo "${i}" >> /tmp/ids.txt;done

    The Code:

    #!/bin/bash

# Get the total count of Ids present in the file.
filecount=`wc -l /tmp/ids.txt | awk '{print $1}'`

while [ "${filecount}" != "0" ]
do
    # Get the first 2000 ids from the file and make them comma-separated.
    ids=`head -2000 /tmp/ids.txt | sed -ze 's/n/,/g' -e 's/,$//g'`
    # Take the dump
    mysqldump -uuser -ppassword --compact --no-create-info db table_name --where="id in (${ids})" >> /tmp/dump.sql
    # Remove the 2000 ids so that we can proceed to the next ids 
    sed -i '1,2000d' /tmp/ids.txt

    # Necessary to end the while loop :)
    filecount=`wc -l /tmp/ids.txt | awk '{print $1}'`
    echo "${filecount}"
done

    The Output:

    mysqldump: [Warning] Using a password on the command line interface can be insecure.
12001
mysqldump: [Warning] Using a password on the command line interface can be insecure.
10001
mysqldump: [Warning] Using a password on the command line interface can be insecure.
8001
mysqldump: [Warning] Using a password on the command line interface can be insecure.
6001
mysqldump: [Warning] Using a password on the command line interface can be insecure.
4001
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2001
mysqldump: [Warning] Using a password on the command line interface can be insecure.
1
mysqldump: [Warning] Using a password on the command line interface can be insecure.
0

    I was able to successfully take the dump with this. The numbers in the output are nothing but echo "${filecount}" from the above script.

    Note:
    I would recommend to make a copy of your id_list file before proceeding with this approach, since we are removing ids from the file for which backup is taken. (For me it was just a sample self-generated file)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search