replacement for mysqldump --all-databases

Discussion in 'Programming/Scripts' started by sjau, Apr 21, 2006.

  1. sjau

    sjau Local Meanie Moderator

    Hiya

    anyone knows a script that loops through all existing mysql dbs and makes a dump of them. So that you end up with multiple sql files e.g.

    dump_db1.sql
    dump_db2.sql
    dump_db3.sql
     
  2. falko

    falko Super Moderator Howtoforge Staff

  3. EvanCarroll

    EvanCarroll New Member HowtoForge Supporter

    The rightmost way to accomplish this -- how i would do it, is to querry on the information_schema http://dev.mysql.com/doc/refman/5.0/en/schemata-table.html CATALOG_NAME is database. that will get you a list of dbs, you can then easily dump in parellel to a like named file. using the command above.


     
  4. thelorax

    thelorax New Member

  5. sjau

    sjau Local Meanie Moderator

    if you want to have a unique sql for each database:

    Code:
    #!/bin/bash
    unset PATH
    # USER VARIABLES
    MYSQLUSER=root
    MYSQLPWD=**********************
    MYSQLHOST=localhost
    MYSQLBACKUPDIR=/mysql_backup
    # PATH VARIABLES
    MK=/bin/mkdir;
    RM=/bin/rm;
    GREP=/bin/grep;
    MYSQL=/usr/bin/mysql;
    MYSQLDUMP=/usr/bin/mysqldump;
    # CREATE MYSQL BACKUP
    # Remove existing backup dir
    $RM -Rf $MYSQLBACKUPDIR
    # Create new backup dir
    $MK $MYSQLBACKUPDIR
    #Dump new files
    for i in $(echo 'SHOW DATABASES;' | $MYSQL -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST|$GREP -v '^Database$'); do
      $MYSQLDUMP                                                    \
      -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST                         \
      -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
      $i > $MYSQLBACKUPDIR/$i.sql;
    done;
    
     
  6. drks

    drks New Member HowtoForge Supporter

    I do this with a one liner (for onetime backups).... you could break it up into a script if you wanted (or use the great suggestions above):

    Code:
    linuxbox /]# for database in $(mysql -e "show databases" | grep "^\|" | grep -v Database); \
    do echo -n "backing up $database ... "; \
    mysqldump $database > $database.sql && \
    echo "ok" || \
    echo "failed"; \
    done
    
     
  7. Ovidiu

    Ovidiu Active Member

    what would we need to change to make any of these scripts compress the dumps into .gz or similar?
     
  8. sjau

    sjau Local Meanie Moderator

    you just need to add at the end of my script the command to create tgz or whatever you want... with my script you have all the files in one folder so just zip that one.
     
  9. Ovidiu

    Ovidiu Active Member

    would that look something like this?

    I'd like to tar.gz the single DB dumps, not the whole folder.
    I am terribly bad with this stuff :-(

    ###edit###
    above code gives an error about removing a / so I changed to this:
     
    Last edited: Dec 5, 2008
  10. Ovidiu

    Ovidiu Active Member

    sorry for reviving this old thread but now I ran into a problem with this solution after upgrading from debian lenny to squeeze...

    the working script was this:

    Code:
    #!/bin/bash
    #Dump new files
    USER=root
    PASSWORD=myrootpw
    HOST=localhost
    
    for i in $(echo 'SHOW DATABASES;' | mysql -u$USER -p$PASSWORD -h$HOST|grep -v '^Database$'); do
      mysqldump                                                     \
      -u$USER -p$PASSWORD -h$HOST                                   \
      -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
      $i > /root/mysql_backup/$i.sql;
    #disabled for now  tar czvf /root/mysql_backup/$i.sql.tar.gz -C / root/mysql_backup/$i.sql;
    done;
    unfortunately after the upgrade I get this error:

    Code:
    Output: mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES
    mysqldump: Got error: 23: Out of resources when opening file './c1zice/wp_1067_terms.MYD' (Errcode: 24) when using LOCK TABLES
    I have googled the first error so far and had different suggestions but I thought I'd ask here first.

    using:

    mysqldump --version
    mysqldump Ver 10.13 Distrib 5.1.55, for debian-linux-gnu (x86_64)

    mysql Ver 14.14 Distrib 5.1.55, for debian-linux-gnu (x86_64) using readline 6.1
     
  11. sjau

    sjau Local Meanie Moderator

    the first is normal... not sure about the second one. Running out of diskspace? is tht db damaged?
     
  12. Ovidiu

    Ovidiu Active Member

    the second might be a hardware problem. just did a hardware test through my provider and they are going to replace some hardware.

    why would the first one be normal? I read that the information schema shouldn't be dumped by default anyway !?

    Does this mean the first error doesn't affect my backups?

    i.e.
     
  13. sjau

    sjau Local Meanie Moderator

    the command looks up all databases including the INFORMATION_SCHEMA and tries to dump it.

    You see, first is an command issued that build a list of the dbs in mysql and then it loops through that list and dumps each one to an own .sql file.
     
  14. sjau

    sjau Local Meanie Moderator

    the command looks up all databases including the INFORMATION_SCHEMA and tries to dump it.

    You see, first is an command issued that build a list of the dbs in mysql and then it loops through that list and dumps each one to an own .sql file.
     
  15. eyeoncomputers

    eyeoncomputers New Member

    this is a little shifty but...you may be able to do an ls of /var/lib/mysql and scoop up the dbs in there, and exclude the ones you don't want, but include everything else.
     

Share This Page