MySQL dump – how to order tables

backupMySQLmysqldump

It seems that mysqldump dumps tables in sequential order – using the same order tables are shown when running:

show tables;

I would like to reverse that order – is it possible to do without getting the list of all tables from database; reordering that list, and specifying all tables in cmdline to mysqldump?

Best Answer

What you are asking for can be done by getting the list of all tables from the database. However, you should use the INFORMATION_SCHEMA database. In your particular case, you can use the INFORMATION_SCHEMA.TABLES table.

TABLES IN DESCENDING TABLE_NAME ORDER (Database mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Choose Ordering
# Descending Order By Table Name
#
ORDER_BY="table_name DESC"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

TABLES IN DESCENDING SIZE ORDER (Database mydb)

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Choose Ordering
# Descending Order By Table Size
#
ORDER_BY="(data_length+index_length) DESC"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

GIVE IT A TRY !!!

I have suggested techniques like this before

Sep 09, 2011 : How can I optimize a mysqldump of a large database?
Dec 16, 2011 : How do you mysqldump specific table(s)?
Aug 23, 2012 : Creating dump file with use command

UPDATE 2014-12-20 21:09 EST

Your last comment

It just seems inflexible to me that mysqldump doesn't offer any kind of table ordering. Reason is I have a table that I want it to be restored last, but in the dump order its among the first couple of tables. It's app dependence that this specific table is not present until the whole dump is fully restored.

TABLES IN ORDER BY tb1,tb2,tb3 BEING LAST (Database mydb)

Let's say you want tables tb1,tb2,tb3 to be last

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Choose Database
#
DB_TO_DUMP=mydb
#
# Order By List of Tables to Make Last
#
LAST_TABLES=tb1,tb2,tb3
ORDER_BY="IF(LOCATE(CONCAT(',',table_name,','),',${LAST_TABLES},')>0,1,0)"
#
#
#
SQL="SET group_concat_max_len = 1048576;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name ORDER BY ${ORDER_BY} SEPARATOR ' ')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DB_TO_DUMP}'"
DBLIST=`mysql ${MYSQL_CONN} -ANe"${SQL}"`
MYSQLDUMP_OPTIONS="--single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --triggers"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} -B ${DBLIST} > ${DB_TO_DUMP}_Tables_Desc.sql

UPDATE 2014-12-21 23:02 EST

In your comment you said mysqldump doesn't offer any kind of ordering unless you specify table names as arguments, and there are not going to. Why ? Please keep in mind that the question MySQL dump - how to order tables? is very broad. There are many ways to ask for tables from a mysqldump.

In my old post How can I monitor the progress of an import of a large .sql file? (May 02, 2012) : When you execute a mysqldump of a single database, all tables are dumped in alphabetical order. Naturally, the reload of the mysqldump into a database would also be in alphabetical order.

The mysqldump client program sees a list of options and names names

Syntax

There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:
shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

Given the first example of syntax, MySQL (now Oracle) expect us developers and DBAs to give the database names and table names.

Since the mysqldump client program has the --databases (-B) option, we are given the opportunity to dump the databases in a specific order or with a specific list.

The point I am making is that it is left up to us to order the tables when running mysqldump. Since MySQL (now Oracle) uses storage engines that use plug-in frameworks, MySQL was not going to make mysqldump compliant to ordering anything for us. Imagine trying to use a storage engine whose physical files you cannot get access to. That leaves no choices but to use the INFORMATION_SCHEMA as I just demonstrated.

Unless someone downloads the source code for mysqldump and creates a version of mysqldump that can do custom ordering, this is all that mysqldump client program gives us to work with. We just have to do the ordering.

Related Solutions

MySQL – How to Detect a Running mysqldump

Your only hope in this scenario is to have the PROCESS privilege.

The SUPER privilege allows you to kill processes. Naturally, you don't want that. On the other hand, the PROCESS privilege allows you to see the processlist.

According to the MySQL Documentation on the PROCESS Privilege

The PROCESS privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use of SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads.

OK, Big Deal. The PROCESS Privilege lets you see the Process List. How does that help?

You can quickly detect a mysqldump in progress when you run SHOW PROCESSLIST; and see a pattern like this in the Info field:

SELECT /*!40001 SQL_NO_CACHE */ * FROM

If you have the PROCESS Privilege and are running MySQL 5.1+, you can run this query:

select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';

Here is a sample output:

mysql> select COUNT(1) mysqldumpThreads
    -> from information_schema.processlist
    -> where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

All you need to do is query for that number. If you get a nonzero, mysqldump must be running.

Give it a Try !!!

MySQL – MySqlDump uses \n instead of null

There are no options for handling the output of NULLs.

You may want to experiment with --hex-blob. This will dump BINARY, VARBINARY, BLOB fields in hexadecimal format. This should make the data portable. You can see what this option produces for NULL values.

mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

Give it a Try and let us all know if this helps.

UPDATE 2012-01-13 18:26 EDT

This may sound very gross but you can pipe the output of mysqldump into sed as follows:

mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n | sed 's/\\N/NULL/g'

Of course, you cannot import that into MySQL until you want the string NULL to be the value to be imported.