There are three(3) things you need to do
- Provide a list to tables that you want to dump row by row
- Use --skip-extended-insert, which forces each
INSERT
to be one row - 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.