Contact Info
43 Street 3, Hiep Binh Chanh Ward
Thu Duc District, HCM City, Vietnam
[email protected]
+84 866.09.02.09
Support

How should we call you?

    Follow Us

    Zodinet Technology Co.,Ltd

    MySQL Database

    Import & Export all MySQL databases

    This tutorial explains how to backup and restore MySQL or MariaDB databases from the command line using the mysqldump utility.

    I. Export Databases

    We have 2 options to export all databases

    1. Export all databases into only 1 file

    mysqldump -u root -p --all-databases > alldb.sql

    2. Export all databases into files for each database

    #!/bin/bash 
    
    USER="zend" PASSWORD="" #OUTPUT="/Users/rabino/DBs" 
    
    #rm "$OUTPUTDIR/*gz" > /dev/null 2>&1 
    
    databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database` 
    
    for db in $databases; do
         if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
             echo "Dumping database: $db"
             mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
            # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
         fi
     done

    II. Import Database

    Split database into standalone file

    sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

    Run following command to import database

    mysql -u <username> -p <dbname> < output.sql

    Post a Comment