Export and import all Mysql database except schema

·

1 min read

Export All Database

Create export_db.sh

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','phpmyadmin','sys')"

DBLISTFILE=/tmp/db_todump.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} > alldb.sql

Add permission file with

$ chmod +x export_db.sh

Then run the script

$ ./export_db.sh

The exported file is all-database.sql

Import Database from file

$ mysql -u username -p database_name < file.sql

Thanks to [RonaldoMYSQLDBA] for snippet Export Database (http://dba.stackexchange.com/questions/69598/how-can-i-mysqldump-all-databases-except-the-mysql-schema) on Stackexchange