Skip to main content
added 81 characters in body
Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

There are three(3) things you need to do

  1. Provide a list to tables that you want to dump row by row
  2. Use --skip-extended-insert, which forces each INSERT to be one row
  3. Use --hex-blob

Here is such a script

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-h127.0.0.1 -P3306 --protocol=tcp -u${MYSQL_USER} -p${MYSQL_PASS}"

DB_TO_DUMP=mydb
TBLISTFILE=/tmp/tables_in_${DB_TO_DUMP}.txt

TABLES_TO_SPLIT_BY_ROW="tb1 tb2 tb3"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${TBLISTFILE}

TBLIST=""
for TB in `cat ${TBLISTFILE}` ; do TBLIST="${TBLIST} ${TB}" ; done

DT=`date +"%Y%m%d%H%M%S"`
for TB1 in `echo "${TBLIST}"`
do
    ROW_BY_ROW=0
    for TB2 in `echo "${TABLES_TO_SPLIT_BY_ROW}"`
    do
        if [ "${TB1}" == "${TB2}" ] ; then ROW_BY_ROW=1 ; fi
    done
    MYSQLDUMP_OPTIONS=""
    if [ ${ROW_BY_ROW} -eq 1 ]
    then
        MYSQLDUMP_OPTIONS="--hex-blob --skip-extended-insert"
    fi
    DUMPFILE=${TB1}_${DT}.sql
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB_TO_DUMP} ${TB1} > ${DUMPFILE}
done

Just supply the name of the database in the DB_TO_DUMP along with the tables whose rows are to be one by one in the TABLES_TO_SPLIT_BY_ROW variable.

Please note how the dumpfile name has a datetime appended to the table name so you can do some versioning based on the datetime. You can change that to be versioned any way you wish.

You did ask that each row be in its own file. I will leave that up to you to script.

There are three(3) things you need to do

  1. Provide a list to tables that you want to dump row by row
  2. Use --skip-extended-insert, which forces each INSERT to be one row
  3. Use --hex-blob

Here is such a script

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-h127.0.0.1 -P3306 --protocol=tcp -u${MYSQL_USER} -p${MYSQL_PASS}"

DB_TO_DUMP=mydb
TBLISTFILE=/tmp/tables_in_${DB_TO_DUMP}.txt

TABLES_TO_SPLIT_BY_ROW="tb1 tb2 tb3"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${TBLISTFILE}

TBLIST=""
for TB in `cat ${TBLISTFILE}` ; do TBLIST="${TBLIST} ${TB}" ; done

DT=`date +"%Y%m%d%H%M%S"`
for TB1 in `echo "${TBLIST}"`
do
    ROW_BY_ROW=0
    for TB2 in `echo "${TABLES_TO_SPLIT_BY_ROW}"`
    do
        if [ "${TB1}" == "${TB2}" ] ; then ROW_BY_ROW=1 ; fi
    done
    MYSQLDUMP_OPTIONS=""
    if [ ${ROW_BY_ROW} -eq 1 ]
    then
        MYSQLDUMP_OPTIONS="--hex-blob --skip-extended-insert"
    fi
    DUMPFILE=${TB1}_${DT}.sql
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB_TO_DUMP} ${TB1} > ${DUMPFILE}
done

Just supply the name of the database in the DB_TO_DUMP along with the tables whose rows are to be one by one in the TABLES_TO_SPLIT_BY_ROW variable.

Please note how the dumpfile name has a datetime appended to the table name so you can do some versioning based on the datetime. You can change that to be versioned any way you wish.

There are three(3) things you need to do

  1. Provide a list to tables that you want to dump row by row
  2. Use --skip-extended-insert, which forces each INSERT to be one row
  3. Use --hex-blob

Here is such a script

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-h127.0.0.1 -P3306 --protocol=tcp -u${MYSQL_USER} -p${MYSQL_PASS}"

DB_TO_DUMP=mydb
TBLISTFILE=/tmp/tables_in_${DB_TO_DUMP}.txt

TABLES_TO_SPLIT_BY_ROW="tb1 tb2 tb3"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${TBLISTFILE}

TBLIST=""
for TB in `cat ${TBLISTFILE}` ; do TBLIST="${TBLIST} ${TB}" ; done

DT=`date +"%Y%m%d%H%M%S"`
for TB1 in `echo "${TBLIST}"`
do
    ROW_BY_ROW=0
    for TB2 in `echo "${TABLES_TO_SPLIT_BY_ROW}"`
    do
        if [ "${TB1}" == "${TB2}" ] ; then ROW_BY_ROW=1 ; fi
    done
    MYSQLDUMP_OPTIONS=""
    if [ ${ROW_BY_ROW} -eq 1 ]
    then
        MYSQLDUMP_OPTIONS="--hex-blob --skip-extended-insert"
    fi
    DUMPFILE=${TB1}_${DT}.sql
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB_TO_DUMP} ${TB1} > ${DUMPFILE}
done

Just supply the name of the database in the DB_TO_DUMP along with the tables whose rows are to be one by one in the TABLES_TO_SPLIT_BY_ROW variable.

Please note how the dumpfile name has a datetime appended to the table name so you can do some versioning based on the datetime. You can change that to be versioned any way you wish.

You did ask that each row be in its own file. I will leave that up to you to script.

Source Link
RolandoMySQLDBA
  • 184.3k
  • 33
  • 323
  • 531

There are three(3) things you need to do

  1. Provide a list to tables that you want to dump row by row
  2. Use --skip-extended-insert, which forces each INSERT to be one row
  3. Use --hex-blob

Here is such a script

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-h127.0.0.1 -P3306 --protocol=tcp -u${MYSQL_USER} -p${MYSQL_PASS}"

DB_TO_DUMP=mydb
TBLISTFILE=/tmp/tables_in_${DB_TO_DUMP}.txt

TABLES_TO_SPLIT_BY_ROW="tb1 tb2 tb3"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${TBLISTFILE}

TBLIST=""
for TB in `cat ${TBLISTFILE}` ; do TBLIST="${TBLIST} ${TB}" ; done

DT=`date +"%Y%m%d%H%M%S"`
for TB1 in `echo "${TBLIST}"`
do
    ROW_BY_ROW=0
    for TB2 in `echo "${TABLES_TO_SPLIT_BY_ROW}"`
    do
        if [ "${TB1}" == "${TB2}" ] ; then ROW_BY_ROW=1 ; fi
    done
    MYSQLDUMP_OPTIONS=""
    if [ ${ROW_BY_ROW} -eq 1 ]
    then
        MYSQLDUMP_OPTIONS="--hex-blob --skip-extended-insert"
    fi
    DUMPFILE=${TB1}_${DT}.sql
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB_TO_DUMP} ${TB1} > ${DUMPFILE}
done

Just supply the name of the database in the DB_TO_DUMP along with the tables whose rows are to be one by one in the TABLES_TO_SPLIT_BY_ROW variable.

Please note how the dumpfile name has a datetime appended to the table name so you can do some versioning based on the datetime. You can change that to be versioned any way you wish.