3

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?

6
  • 1
    Why do you want to do this? Dec 20 '14 at 15:36
  • 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. Dec 20 '14 at 22:20
  • 1
    Table Ordering can be done with the INFORMATION_SCHEMA. I am posting an answer on how to do it by table name and table size. Dec 20 '14 at 22:28
  • I just updated my answer with the way to make certain tables last in dumping Dec 21 '14 at 2:12
  • 1
    Two thoughts - 1st) rename your table :-) and 2nd (more serious this time) - would disabling constraints not do what you want? Dec 21 '14 at 4:38
3

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

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.

1
  • Thank you for your answer. I developed a similar (bash-alike) solution my self. I will accept this answer but I think you should probably state at the top of your post that mysqldump doesn't offer any kind of ordering, unless you specify table names as arguments - because I explicitly asked if it's possible to do it without getting list of tables from database (and that was obviously aimed at sending that list as parameters to mysqldump). Dec 22 '14 at 2:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.