MySQL – How to List Database Names in a mysqldump File

mariadbMySQLmysqldump

I have a large mysqldump and I need to create the databases before importing them one-by-one. How can I search and list only the database names using the linux or mysql terminal?

Best Answer

PROPOSED SOLUTION

DUMPFILE=mydump.sql
grep "^USE " ${DUMPFILE} | sed 's/;//' | sed 's/`//g' | awk '{print $2}'

EXAMPLE

# grep "^USE " dump.sql | sed 's/;//' | sed 's/`//g' | awk '{print $2}'
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
CR1281653
CR1289379
MasterDB
ProjectDB
TPLB23
a110107
a110107_copy
cardfree_orders_lab
mysql
nulldatetest
part_lab
part_test
partition_engine
redwards
scmp_lab
topup_lab
wsms2_lab
zenith_lab
#

You could collect them in a variable (Note: You must escape the backquote)

# DBLIST=`grep "^USE " dump.sql | sed 's/;//' | sed 's/\`//g' | awk '{print $2}'`
# echo ${DBLIST}
CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab CR1281653 CR1289379 MasterDB ProjectDB TPLB23 a110107 a110107_copy cardfree_orders_lab mysql nulldatetest part_lab part_test partition_engine redwards scmp_lab topup_lab wsms2_lab zenith_lab
#

GIVE IT A TRY !!!

Related Solutions

MySQL Crash – During mysqldump of Specific Database

You will have to mysqldump around moments_production_wp

I have old posts to bypass databases and tables

Dec 16, 2011 : How do you mysqldump specific table(s)?
Feb 20, 2013 : Any option for mysqldump to ignore databases for backup?

Using the same concepts, here is how to mysqldump all except moments_production_wp

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DATABASES_TO_EXCLUDE="moments_production_wp"
EXCLUSION_LIST="'information_schema','mysql','performance_schema'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > GoodDatabases.sql

After dumping all the other databases, mysqldump the one database

mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} moments_production_wp > OneDatabase.sql

It will mysqldump tables in alphabetical order. It should break near the bad table.

You can then mysqldump that database one table at a time

Let's say you know tables tb1 and tb2 makes mysqld crash

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DBTODUMP=moments_production_wp
SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('tb1','tb2')"
TBLIST=`mysql ${MYSQL_CONN} -AN -e"${SQL}"`
MYSQLDUMP_OPTIONS="--hex-blob --max-allowed-packet=1073741824"
for TB in `echo "${TBLIST}"`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DBTODUMP} ${TB} > ${DBTODUMP}-${TB}.sql
done

This should help narrow the beam as to which table is causing the issue.

I do have one further thing to share. I have a 4-year-old post on the silent killer of mysqldumps : MySQL server has gone away obstructing import of large dumps. You will need to set your max_allowed_packet to 1G in my.cnf and restart mysqld. If you cannot change the max_allowed_packet at this time, you should mysqldump one row at a time using --skip-extended-insert to throttle the amount of data passing into your current MySQL Packet.

Mysql – Importing MySQL tables from other database in live site with thesqldump can cause trouble

If they are on the same server, I'd do the table 'copy/replicate' this way:

CREATE TABLE detinationDB.new_table like sourceDB.TblName;
INSERT INTO detinationDB.new_table SELECT * FROM sourceDB.TblName;
RENAME TABLE detinationDB.TblName TO detinationDB.DropMe, detinationDB.new_table TO detinationDB.TblName;
DROP TABLE detinationDB.DropMe;

This way, you avoid dropping the old table before making sure the new table is there.

This is good if you intend to keep booth DBs on the same server.