Dump and tar.gz db's?

Discussion in 'General' started by ikrudolf, Nov 11, 2015.

  1. ikrudolf

    ikrudolf Member

    Whats the best command to dump all db's and dan zip them to 1 tar.gz file?
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    On Debian and Ubuntu systems you can use this command to dump all databases into one file:

    mysqldump --defaults-file=/etc/mysql/debian.cnf -cCeQ --hex-blob --quote-names --routines --events --triggers --all-databases -r all_databases.sql

    Then you can gzip it or you pipe the output to gzip instead of using the -r all_databases.sql switch.
  3. ikrudolf

    ikrudolf Member

    Is it also possible to dump them in ceperate files and add them to 1 tar.gz file?
  4. matthias

    matthias New Member Moderator

    That's only possible with a bit of additional magic on the shell and multiple commands.

    for database in $( mysql --defaults-extra-file=/etc/mysql/debian.cnf -N -e "SHOW DATABASES;" ); do mysqldump --defaults-file=/etc/mysql/debian.cnf -cCeQ --hex-blob --quote-names --routines --events --triggers $database > ${database}.sql; done; tar -czf databases.tar.gz *.sql
    Logic behind this: Get a list of all databases; loop over them and dump each in a file; compress the files into an archive.

    This assumes you're using Debian, Ubuntu or a derivate thereof as well. On other systems you need to replace both instances of "--defaults-extra-file=/etc/mysql/debian.cnf" with "-u root -p'<password>'". Of course you can use the latter on Debian-based systems as well - it might even avoid one or two warnings when dumping the system tables.
    This also assumes that your shell is Bash. Most, if not all Linux shells support loops and variables, the syntax may vary, though. You may have to consult the manpage or Google, if you don't use Bash.
  5. ikrudolf

    ikrudolf Member

    If I use this on Ubuntu I get acces denied errors

  6. till

    till Super Moderator Staff Member ISPConfig Developer

    Looks as if the password of the debian-sys-maint user in /etc/mysql/debian.cnf is wrong.

Share This Page