How to mysqldump last 10 rows for EVERY TABLE in a database?

Question

I have seen a lot of articles about "how to mysqldump last 'n' rows from a table in a database".

For example: mysqldump --user=superman --password=batman --host=gothamcity.rds.com --where="1=1 ORDER BY id DESC LIMIT 10" DB_NAME TABLE_NAME ./path/to/dump/file.sql as found from these answers in StackOverflow and ServerFault

But, how do I tell mysqldump to export last 'n' rows for EVERY TABLE in a database?

Answer 1

You can get a list of the tables in a database using something like the snippet below which gives a list without any headers or formatting.

mysql --user=superman --password=batman --host=gothamcity.rds.com --silent  joker.db -B -e "show tables"

Penguin
LexLuthor
Brainiac
CatWoman

So now you know how to get a list of table it's trivia to feed that into a loop and do whatever you want.

Answer 2

I've written a script for this task, to integrate mysqldump with Bacula/Bareos:

#!/bin/bash

CLIENT_CONFIG_PATH="${1}"
DUMP_DIR="${2}"
DUMP_FILENAME_SUFFIX='.sql'
declare -i COMPRESS_BZIP2=${3:-1}

PATH="/usr/bin:${PATH}"

declare -i EXIT_STATUS=1

if ! [ -n "${CLIENT_CONFIG_PATH}" ]; then
        echo "ERROR: missing argument #1 - mysql client config path" >&2
        exit 1
fi
if ! [ -f "${CLIENT_CONFIG_PATH}" ]; then
        echo "ERROR: no such file '${CLIENT_CONFIG_PATH}'" >&2
        exit 1
fi

if ! [ -n "${DUMP_DIR}" ]; then
        echo "ERROR: missing argument #2 - dump dir path" >&2
        exit 1
fi
if ! [ -d "${DUMP_DIR}" ]; then
        echo "ERROR: no such dir '${DUMP_DIR}'" >&2
        exit 1
fi

TABLES_LIST_FILE=$(mktemp)
mysql --defaults-file="${CLIENT_CONFIG_PATH}" --batch --no-auto-rehash --skip-column-names --execute='SELECT DISTINCT `table_schema` FROM `information_schema`.`tables` WHERE `table_schema` NOT IN ("information_schema","performance_schema");' >"${TABLES_LIST_FILE}"
for T_DBNAME in $(cat ${TABLES_LIST_FILE}); do
        if [ -n "${T_DBNAME}" ]; then
                    T_DUMPFILE="${DUMP_DIR}/${T_DBNAME}${DUMP_FILENAME_SUFFIX}"
                    echo "Dumping database: '${T_DBNAME}' to file: '${T_DUMPFILE}' ..." >&2
                    mysqldump --defaults-file="${CLIENT_CONFIG_PATH}" --force --hex-blob --databases "${T_DBNAME}" 1>"${T_DUMPFILE}" &
        fi
done
while [ $(jobs -p |wc -l) -gt 0 ]; do
        wait -n
        if [ ${?} -eq 0 ]; then
                    EXIT_STATUS=0
        fi
done

if [ ${COMPRESS_BZIP2} -gt 0 ]; then
        bzip2 --best --force --verbose "${DUMP_DIR}"/*"${DUMP_FILENAME_SUFFIX}"
fi

exit ${EXIT_STATUS}

You should call it this way: mysqldump-per-db.sh CLIENT_CONFIG TEMP_DIR COMPRESS_BZIP

Arguments:

CLIENT_CONFIG - substitute a MySQL config file containing host, credentials and dump options.

TEMP_DIR - temporary dir for dumpe files, one per database.

COPRESS_BZIP - 0 or 1

Answer 3

If you can use 'xargs', then this can all be combined into one command. It will take the list of table names from the mysql command and input them into the mysqldump command:

mysql --user=superman --password=batman \
    --host=gothamcity.rds.com --port=3306 --database=jokersbox \
    --silent --batch --execute="show tables" | \
    xargs -I {} mysqldump --user=superman --password=batman --host=gothamcity.rds.com \
    --where="1=1 ORDER BY id DESC LIMIT 10" \
    --no-create-info jokersbox {}  >> ./path/to/dump/file.sql

I have added --no-create-info and used the append ('>>') instead of overwrite ('>'). Those changes may not fit what you intend to do with your sql file.

Answer 4

Here is what i did in the terminal. The idea is basically to get a list of all tablenames, and then pipe it into a while loop in bash where each of those tables are dumped into a separate dumpfile individually.

mysql --user=superman --password=batman --host=gothamcity.rds.com --port=3306 --database=jokersbox --execute="show tables" --silent --batch | while read tablename ; do mysqldump --user=superman --password=batman --host=gothamcity.rds.com --port=3306 --where="1=1 ORDER BY id DESC LIMIT 10" jokersbox $tablename --add-drop-table > $tablename.sql ; done

It worked. Only issue is, it dumped each table into it's own individual SQL file - not all tables were dumped to a single file. But i guess the contents of those individual files could also be joined together into a single file via some other bash commands.

EDIT::: you can use the >> operator instead of the > operator to append each mysqldump to a single file instead of overwriting the file with the last mysqldump. If you choose to use >> you must ensure that you write to a fixed filename (like dumps.sql) instead of writing to a dynamic filename (like $tablename.sql). You must also make sure that there is no dumps.sql file present prior to running the script - otherwise, your mysqldumps will get appended to a file which already has other contents in them.