28

I want to backup all 200+ databases on a MySQL server to all-dbs.sql.

I want to exclude the mysql schema.

How can I do that?

the
  • 343
  • 1
  • 3
  • 12
haulpd
  • 383
  • 2
  • 4
  • 7
  • No doubt about the possible - it's a dupe! Vote to close and point users to the original answer. – Vérace Aug 28 '15 at 15:51

1 Answers1

45

You need to collect all the database names into a space delimited list. Use that for mysqldump

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"

DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}

DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done

MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > all-dbs.sql

Give it a Try !!!

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 1
    Just for anyone here reading that is in a similar position to me - you can also just dump everything and manually remove the mysql schema from the dump file, to save time writing/running scripts – Leonard Challis Jul 02 '15 at 11:27
  • 1
    "Why should we care implementing a simple --ignore-database argument, look at this tiny script, it will do just fine." – Gras Double Aug 28 '15 at 04:45
  • 2
    It says mysqldump: unknown option '--ignore-database' don't know why, but when I used --ignore-table=information_schema.* it worked. – Hussain Behestee Nov 13 '15 at 09:38
  • What language is the above script??! – TheStoryCoder Jan 19 '16 at 18:44
  • 4
    It's always lovely to read the comments :))) – Ivan Nikolchov May 25 '16 at 07:01
  • 3
    @TheStoryCoder It's written in english, but the programming language is bash :D – Denys Vitali Jun 27 '16 at 13:35
  • My MySQL instance has a sys database in it that looks like another DB for internal use that should be ignored. – Stephen Ostermiller Dec 26 '17 at 18:12
  • @StephenOstermiller you are right. In fact, innodb would also need to be added for RDS instances (although it has no actual tables) – RolandoMySQLDBA Dec 26 '17 at 18:14
  • @RolandoMySQLDBA hello Sir, do we need to create empty databases before import the all-dbs.sql file? Thanks! – Dung Dec 05 '19 at 19:51
  • @RolandoMySQLDBA If it is Windows, string usually has \r (carriage return) which make s it overwrite values in concatenation. So, something like DB=$(echo "$DB" | tr -d '\r') before concatenation should be used. – MARK002-MAB Jan 11 '21 at 09:39
  • You can skip the temp file in the middle with group_concat(schema_name separator ' ') (and make the discussion about EOL characters irrelevant) – cbmanica Sep 17 '21 at 01:42
  • @GrasDouble I don't know if your sarcastic. This 'tiny script' will not dump all dbs in single transaction – nl-x Dec 07 '21 at 13:20
  • I was ranting at MySQL because I precisely had to dump all dbs excluding a few ones, which I suppose is a very common use case, and because of the lack of a --ignore-database argument we have to rely on complicated scripts as a workaround. The above script is fine, as it's the best we can do… – Gras Double Dec 08 '21 at 03:54
  • Just to expand a bit on the accepted answer above: SELECT schema_name could become: SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') and you completly skip the for loop. – Alpha2k Oct 15 '22 at 16:10