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 : Julian's | 47 views