How to mysqldump All Databases Excluding Information_Schema

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.

 

MYSQL_USER=root
MYSQL_PASS='password'
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')" #nama DB yg akan dihapus
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-db1.sql

source : https://www.flynsarmy.com/2018/06/how-to-mysqldump-all-databases-excluding-information_schema/

Posted on: August 16, 2022, by :  | 15 views