If you want to back up your MySQL databases you’ll be familiar with
1 | mysqldump -uroot -p --all-databases > dump.sql |
However this includes the information_schema, mysql and performance_schema databases. These are often not only unwanted in the dump, but can potentially cause issues on import.
To exclude these databases from your dump use the following script courtesy of user RolandoMySQLDBA on the StackOverflow forums.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | MYSQL_USER=rootMYSQL_PASS=rootpasswordMYSQL_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 |