Mysql – Dump single rows (with blobs) to single files

blobMySQLmysqldump

I am creating MySQLDumps for the sake of versioning of a database. The problem currently is a table with blobs.

My first approach would be to dump single tables to single files. But next, I want on this specific table to dump each row to a single file.

How can I accomplish this using MySQLdump?

Best Answer

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.

Related Solutions

Mysql – Database Files-Related Issue

Answer to your first question: Each file contains info for the table

cars.frm has the table structure
cars.MYD has the table data
cars.MYI has the table indexes

Given this layout, the cars table uses the storage engine MyISAM.

Answer to your second question: Yes, provided the following circumstances

every table uses the storage engine MyISAM
no INSERTs, UPDATEs, or DELETEs are running
DB Connections using TCP/IP are disabled

To check to see if all the tables are MyISAM, run this query:

SELECT COUNT(1) TableCount,engine
FROM information_schema.tables
WHERE table_schema='db_test_1'
GROUP BY engine;

You should only see just MyISAM. If you see other storage engines, you cannot just copy.

If your root@localhost password is mypass, here is all you need to to safely move all the tables regardless of storage engine:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 | mysql ${USERPASS} -A -Ddb_test_2
# service mysql restart

If the data is huge, do this instead:

# USERPASS="-uroot -pmypass"
# service mysql restart --skip-networking
# mysql ${USERPASS} -A -e"CREATE DATABASE db_test_2"
# mysqldump ${USERPASS} --routines --triggers db_test_1 > /root/MyData.sql
# mysql ${USERPASS} -A -Ddb_test_2 < /root/MyData.sql
# service mysql restart

Give it a Try !!!

Mysql – Compare two MySQL dump files

Unfortunately, you cannot force the ordering of columns in a mysqldump. You can, however, use the table INFORMATION_SCHEMA.COLUMNS. You will need the following:

MySQL Instance with the old data loaded
MySQL Instance with the new data loaded
Some Patience and Scripting

With this setup in mind, here is what you need to do:

STEP 01) Use Query to Present Columns Ordered Per Database/Table

Here is the Query:

SELECT CONCAT(table_schema,'.',table_name),
GROUP_CONCAT(column_name ORDER BY column_name)
FROM information_schema.columns WHERE table_schema NOT IN
('information_schema','performance_schema')
GROUP BY table_schema,table_name;

This will give you every table followed by a CSV list of alphabetically-sorted columns

STEP 02) Launch this Column Query From Both MySQL Instances

IPADDR_OLD=10.1.20.30
IPADDR_NEW=10.1.20.40
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name),"
SQLSTMT="${SQLSTMT} GROUP_CONCAT(column_name ORDER BY column_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.columns WHERE table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema')"
SQLSTMT="${SQLSTMT} GROUP BY table_schema,table_name"
mysql -u... -p... -h${IPADDR_OLD} -ANe"${SQLSTMT}"  > column_listing1.txt
mysql -u... -p... -h${IPADDR_NEW} -ANe"${SQLSTMT}" >> column_listing2.txt
cat column_listing1.txt  > column_listing.txt
cat column_listing2.txt >> column_listing.txt

Run the Column Query and Collect the Results in column_listing.txt

STEP 03) Perform Unique Sort ; Check For Duplicate Table Names

sort -u < column_listing.txt > column_listing.unq
UNQ_LINES=`wc -l < column_listing.unq`
TXT_LINES=`wc -l < column_listing.txt`
(( DIF_LINES = (UNQ_LINES + UNQ_LINES - TXT_LINES) / 2))
echo ${DIF_LINES}

If everything is identical, DIF_LINES should 0. Otherwise, there are duplicates present.

STEP 04) If There Are Duplicates, Find Them

diff column_listing1.txt column_listing2.txt

Give it a Try !!!

UPDATE 2013-03-22 13:04 EDT

You can also check other metadata tables such as

TABLE_CONSTRAINTS

mysql> desc TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

STATISTICS

mysql> desc STATISTICS;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

REFERENTIAL_CONSTRAINTS

mysql> desc REFERENTIAL_CONSTRAINTS;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG        | varchar(512) | NO   |     |         |       |
| CONSTRAINT_SCHEMA         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME           | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | NO   |     |         |       |
| UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |     | NULL    |       |
| MATCH_OPTION              | varchar(64)  | NO   |     |         |       |
| UPDATE_RULE               | varchar(64)  | NO   |     |         |       |
| DELETE_RULE               | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                | varchar(64)  | NO   |     |         |       |
| REFERENCED_TABLE_NAME     | varchar(64)  | NO   |     |         |       |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

mysql>

Setup queries that perform the same styled comparison