Export and Import all MySQL databases at one time

Question

I want to keep a backup of all my MySQL databases  I have more than 100 MySQL databases  I want to export all of them at the same time and again import all of them into my MySQL server at one time  How can I do that

User · Answer

Be careful when exporting from and importing to different MySQL versions as the mysql tables may have different columns  Grant privileges may fail to work if you re out of luck  I created this script  mysql export grants sql   to dump the grants for importing into the new database  just in case      bin sh stty -echo printf  Password     gt  amp 2 read PASSWORD stty echo printf   n  if   -z   PASSWORD     then         echo  No password given           exit 1 fi MYSQL CONN  -uroot -p PASSWORD  mysql   MYSQL CONN  --skip-column-names -A -e SELECT CONCAT  SHOW GRANTS FOR     user         host        FROM mysql user WHERE user lt  gt       mysql   MYSQL CONN  --skip-column-names -A   sed  s     g

User · Answer

I wrote this comment already more than 4 years ago and decided now to make it to an answer  The script from jruzafa can be a bit simplified     bin bash  USER  quot zend quot  PASSWORD  quot  quot  ExcludeDatabases  quot Database information schema performance schema mysql quot  databases  mysql -u  USER -p PASSWORD -e  quot SHOW DATABASES  quot    tr -d  quot    quot    egrep -v  ExcludeDatabases   for db in  databases  do     echo  quot Dumping database   db quot      mysqldump -u  USER -p PASSWORD --databases  db  gt   date   Y m d   db sql       gzip  OUTPUT date   Y m d   db sql done  Note   The excluded databases - prevalently the system tables - are provided in the variable ExcludeDatabases Please be aware that the password is provided in the command line  This is considered as insecure  Study this question

User · Answer

Why parsing formatted output while the mysql command can do directly what you want   databases  mysql -u  USER -p PASSWORD --batch --skip-column-names -e  SHOW DATABASES     grep -E -v   information performance  schema     Lists the database names and only this

User · Answer

Export   mysqldump -u root -p --all-databases  gt  alldb sql   Look up the documentation for mysqldump  You may want to use some of the options mentioned in comments   mysqldump -u root -p --opt --all-databases  gt  alldb sql mysqldump -u root -p --all-databases --skip-lock-tables  gt  alldb sql   Import   mysql -u root -p  lt  alldb sql

User · Answer

Export all databases in Ubuntu  1 - mysqldump -u root -p --databases database1 database2  gt    Desktop databases 1 2 sql  OR  2 - mysqldump -u root -p --all databases  gt    Desktop all databases sql

User · Answer

When you are dumping all database  Obviously it is having large data  So you can prefer below for better   Creating Backup   mysqldump -u  user  -p password --single-transaction --quick --all-databases   gzip  gt  alldb sql gz   If error  -- Warning  Skipping the data of table mysql event  Specify the --events option explicitly   Use   mysqldump -u  user  -p --events --single-transaction --quick --all-databases   gzip  gt  alldb sql gz   Restoring Backup   gunzip  lt  alldb sql gz   mysql -u  user  -p password    Hope it will help

User · Answer

Other solution   It backs up each database into a different file     bin bash  USER  zend  PASSWORD     OUTPUT   Users rabino DBs    rm   OUTPUTDIR  gz   gt   dev null 2 gt  amp 1  databases  mysql -u  USER -p PASSWORD -e  SHOW DATABASES     tr -d        grep -v Database   for db in  databases  do     if      db      information schema      amp  amp       db      performance schema      amp  amp       db      mysql      amp  amp       db             then         echo  Dumping database   db          mysqldump -u  USER -p PASSWORD --databases  db  gt   date   Y m d   db sql          gzip  OUTPUT  date   Y m d   db sql     fi done

User · Answer

All the answers I see on this question can have problems with the character sets in some databases due to the problem of redirecting the exit of mysqldump to a file within the shell operator  gt    To solve this problem you should do the backup with a command like this  mysqldump -u root -p --opt --all-databases -r backup sql   To do a good BD restore without any problem with character sets  Obviously you can change the default-character-set as you need   mysql -uroot -p --default-character-set utf8 mysql gt  SET names  utf8   mysql gt  SOURCE backup sql

User · Answer

mysqldump -uroot -proot --all-databases   allDB sql  note  -u your username  -p your password

User · Answer

Based on these answers I ve made script which backups all databases into separate files  but then compress them into one archive with date as name   This will not ask for password  can be used in cron  To store password in  my cnf check this answer https   serverfault com a 143587 62749  Made also with comments for those who are not very familiar with bash scripts      bin bash    This script will backup all mysql databases into    compressed file named after date  ie   var backup mysql 2016-07-13 tar bz2    Setup variables used later    Create date suffix with  F ull date format suffix   date   F    Retrieve all database names except information schemas  Use sudo here to skip root password  dbs   sudo mysql --defaults-extra-file  root  my cnf --batch --skip-column-names -e  SHOW DATABASES     grep -E -v   information performance  schema     Create temporary directory with  -d  option tmp   mktemp -d    Set output dir here   var backups  is used by system     so intentionally used  var backup  for user backups  outDir   var backup mysql    Create output file name out   outDir  suffix tar bz2     Actual script    Check if output directory exists if     -d   outDir    then     Create directory with parent   -p  option  directories   sudo mkdir -p   outDir  fi    Loop through all databases for db in  dbs  do     Dump database to temporary directory with file name same as database name   sql suffix   sudo mysqldump --defaults-extra-file  root  my cnf --databases   db   gt    tmp  db sql  done    Go to tmp dir cd  tmp    Compress all dumps with bz2  discard any output to  dev null sudo tar -jcf   out     gt    dev null     Cleanup cd   tmp   sudo rm -rf   tmp

[mysql] Export and Import all MySQL databases at one time

Examples related to mysql

Examples related to database-backups

Examples related to mysql-backup