mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')
    -> ;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it

# df -h /tmp/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 2.0G   12K  2.0G   1% /tmp
# df -i /tmp/
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
tmpfs                2041621       7 2041614    1% /tmp
# mount | grep /tmp
tmpfs on /tmp type tmpfs (rw,size=2048M)

# cd /var/lib/mysql ; ls -lR | grep -c ".frm$"
1876765

# cd /var/lib/mysql ; ls -lR | grep -c ".frm$"
49514

# ./backup.sh
Backing up database number: 1
Completed in: 0.016
Backing up database number: 2
Completed in: 0.078
Backing up database number: 3
Completed in: 0.074
Backing up database number: 4
Completed in: 0.068
Backing up database number: 5
Completed in: 0.071
Backing up database number: 6
Completed in: 0.060
Backing up database number: 7
Completed in: 0.067
Backing up database number: 8
Completed in: 0.070
Backing up database number: 9
Completed in: 0.065
.....
Backing up database number: 107
Completed in: 10.749
Backing up database number: 108
Completed in: 12.125
Backing up database number: 109
Completed in: 11.313
Backing up database number: 110
Completed in: 11.572
Backing up database number: 111
Completed in: 11.371
.....

#!/usr/bin/env bash

DATA_DIR="/tmp/mysqlbackup"
LOCKFILE=/tmp/backup.lock
NOW=$(date +%Y%m%d)
COUNT=1

if [ -f "$LOCKFILE" ]; then
        echo "$(basename $0) is already running: PID $(cat $LOCKFILE)"
        exit 0

else

        echo $$ > $LOCKFILE

        if [ ! -d $DATA_DIR/$NOW ]; then
                mkdir -m 700 -p $DATA_DIR/$NOW
        fi

        while read DB; do

                (( COUNT++ ))

                echo "Backing up database number: $COUNT"

                START=$(date +%s.%N)

                mydumper -e -o $DATA_DIR/$NOW/$DB -B "$DB"

                ELAPSED=$(date +%s.%N)

                printf "Completed in: %.3Fn" $(echo $ELAPSED - $START | bc)

        done <<< "$(mysql -A -B -N -e "SHOW DATABASES" | egrep -v '(mysql|*_schema|log)')"

        echo "Removing backup dir...";

        rm -rf $DATA_DIR/$NOW

        rm -f $LOCKFILE

        exit 0

fi

mysql> show create table information_schema.tablesG
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

DROP TABLE IF EXISTS mysql.dbtbcount;
CREATE TABLE mysql.dbtbcount
(
    table_schema VARCHAR(64) NOT NULL,
    table_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (table_schema)
) ENGINE=MEMORY;
INSERT INTO mysql.dbtbcount (table_schema)
SELECT schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql');
INSERT INTO mysql.dbtbcount (table_schema)
SELECT table_schema FROM information_schema.tables
ON DUPLICATE KEY UPDATE table_count = table_count + 1;
SELECT * FROM mysql.dbtbcount;
SELECT COUNT(1) DistinctDBs FROM mysql.dbtbcount WHERE table_count > 0;

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT="SELECT db FROM (SELECT table_schema db,SUM(data_length) datasize"
SQLSTMT-"${SQLSTMT} FROM information_schema.tables"
SQLSTMT-"${SQLSTMT} WHERE table_schema NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(table_schema,7)<>'_schema'"
SQLSTMT-"${SQLSTMT} GROUP BY table_schema) A"
SQLSTMT-"${SQLSTMT} ORDER BY datasize DESC"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT-"${SQLSTMT} SELECT schema_name FROM information_schema.schemata"
SQLSTMT-"${SQLSTMT} WHERE schema_name NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(schema_name ,7)<>'_schema'"

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi