Skip to Content

EGLUG backup script

Conceptor's picture
Sat, 18/03/2006 - 7:25pm — Conceptor
#!/bin/bash
#mysqlbackup is script writen to dump eglug databases
#+ Diaa Radwan

DATE=$(date +%d-%m-%y)
BACKUP_PATH='path/to/backups'
DBACKUP_PATH='/path/to/ocument_root'
DB_NAME='dbname'
DB_USER='dbuser'
PASSWORD='pass'
DOCUMENTROOT='/path/to/docroot'
MYDUMP_OPTIONS='--add-drop-table -acv  --compatible=mysql40 --host dbhost'

      case "$1" in

        databases)
        for db in $DB_NAME ;do
        mkdir -p $BACKUP_PATH/$DB_NAME/$DATE
        mysqldump $MYDUMP_OPTIONS -u$DB_USER -p${PASSWORD} $db > $BACKUP_PATH/$DB_NAME/$DATE/${DB_NAME}.mysql
        tar cjvf $BACKUP_PATH/$DB_NAME/$DATE/${DB_NAME}.mysql.tar.bz2 $BACKUP_PATH/$DB_NAME/$DATE/${DB_NAME}.mysql
        rm -f $BACKUP_PATH/$DB_NAME/$DATE/${DB_NAME}.mysql

        done

           ;;
          tables)
          for db in $DB_NAME
          do
                mkdir -p $BACKUP_PATH/$DB_NAME/$DATE/dbtables
          for tables in `echo "show tables" | mysql -u$DB_USER -p${PASSWORD} $DB_NAME | grep -v "Tables_in_"`
          do
        mysqldump $MYDUPM_OPTIONS -u$DB_USER -p${PASSWORD} $DB_NAME $tables > $BACKUP_PATH/$DB_NAME/$DATE/dbtables/$tables.mysql
          tar cjvf $BACKUP_PATH/$DB_NAME/$DATE/dbtables/$tables.mysql.tar.bz2 $BACKUP_PATH/$DB_NAME/$DATE/dbtables/$tables.mysql
          rm -f $BACKUP_PATH/$DB_NAME/$DATE/dbtables/$tables.mysql
          done
          done
           ;;
        docroot)
        mkdir -p $DBACKUP_PATH
        tar cjvf $DBACKUP_PATH/eglug.org_docroot_${DATE}.tar.bz2 $DOCUMENTROOT
        ;;

        dt)
           sh $PWD/$0 databases
           sh $PWD/$0 tables
           ;;
                *)
             echo "USAGE: $0 {tables|databases|dt|docroot}"
        esac

Comments

DarKnesS_WolF's picture

To use it

DarKnesS_WolF - 11/10/2006 - 3:51pm

to use this script to backup eglug mysql do we need any more mysqldumb options ?

peace

Live Free Or Die Trying... GPG Key ID:0x6FD809F4

DarKnesS_WolF's picture

nah it's okay

DarKnesS_WolF - 11/10/2006 - 4:28pm

i saw the real script we need to test the backs from it and why we are using --compatible=mysql40 if we are using mysql5 ? and for ex. i'm using mysql 5 too .

peace

Live Free Or Die Trying... GPG Key ID:0x6FD809F4

Conceptor's picture

we need to have test for

Conceptor - 12/10/2006 - 9:55am

we need to have test for the dump,it will be found at ~/backup/mysql/eglug/$DATE/

NB: the script is updated to backup the document root

Diaa Radwan

Looks good IMO

alienbrain - 23/10/2006 - 7:37pm

The script is fine, it could use some minor improvements code wise, but otherwise it does the job right. Anyone have comments? I hope we start using this asap.

Alright, some things that won't prevent us from using the script, but just for the sake of code quality..

sh $PWD/$0 databases; sh $PWD/$0 tables is so ugly. why not wrap each step in a function and call both instead of invoking two more shells.

Process Killer on Server prevents script

alienbrain - 27/10/2006 - 8:52pm

After a bit of investigation, turns out there is a process killer on eglug server, kills process after they exceed some limits (limits that I don't know of). This happens especially with tar. May be it consumes more memeory than others. Anyway..

Result of this that most of backups are corrupted

Even renicing the process didn't make it go uncovered, the killer shut it down :-(.

Any ideas?

bzip2--

alienbrain - 27/10/2006 - 9:53pm

The backup script was compressing with bzip2, after changing that to gzip, the process killer started to like us. So far backups are performed successfully.

So it works? yes. Cool? no. That's not a solution. If for any reason gzip takes more processing time (e.g. larger db), it might be killed as well. I kept the nicing in the script, that should make things cooler but doesn't mean we should mark this problem as fixed.

MSameer suggested that we could split large files before gziping. Indeed a good idea. Any other ideas?

DarKnesS_WolF's picture

we can try this

DarKnesS_WolF - 28/10/2006 - 8:12pm

we can try to renice to to 19 or something and see what will happen

peace

Live Free Or Die Trying... GPG Key ID:0x6FD809F4

that's how it's already..

alienbrain - 28/10/2006 - 9:54pm

that's how it's already..

For the sake of documentation..

alienbrain - 29/10/2006 - 10:06pm

I've found that the mysql dumps completely ignore the auto_increment bit for all primary keys!!

Mystery resolved, thanks to MSameer, he pointed me to this bug: http://bugs.mysql.com/bug.php?id=14515 which says this is a bug triggered when you use the --compatible=mysql40

Likely though, the fix is easy. We just need to run these SQLs against the DB after restoring it from the backup:

ALTER TABLE access CHANGE aid aid tinyint(10) NOT NULL auto_increment;
ALTER TABLE accesslog CHANGE aid aid int(10) NOT NULL auto_increment;
ALTER TABLE aggregator_category CHANGE cid cid int(10) NOT NULL auto_increment;
ALTER TABLE aggregator_feed CHANGE fid fid int(10) NOT NULL auto_increment;
ALTER TABLE aggregator_item CHANGE iid iid int(10) NOT NULL auto_increment;
ALTER TABLE authmap CHANGE aid aid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE boxes CHANGE bid bid tinyint(4) NOT NULL auto_increment;
ALTER TABLE comments CHANGE cid cid int(10) NOT NULL auto_increment;
ALTER TABLE node_comment_statistics CHANGE nid nid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE filter_formats CHANGE format format int(4) NOT NULL auto_increment;
ALTER TABLE locales_source CHANGE lid lid int(11) NOT NULL auto_increment;
ALTER TABLE moderation_filters CHANGE fid fid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE moderation_votes CHANGE mid mid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE node CHANGE nid nid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE profile_fields CHANGE fid fid int(10) NOT NULL auto_increment;
ALTER TABLE url_alias CHANGE pid pid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE poll_choices CHANGE chid chid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE role CHANGE rid rid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE term_data CHANGE tid tid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE vocabulary CHANGE vid vid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE watchdog CHANGE wid wid int(5) NOT NULL auto_increment;
ALTER TABLE help CHANGE hid hid int(10) unsigned NOT NULL auto_increment;
ALTER TABLE privatemsg CHANGE id id int(10) unsigned not null auto_increment;
ALTER TABLE privatemsg_folder CHANGE fid fid int(10) unsigned not null auto_increment;
ALTER TABLE privatemsg_archive CHANGE id id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE project_issue_state CHANGE sid sid int(10) unsigned NOT NULL auto_increment;

Note for the lazy who don't wanna read the full bug thread: this bug is fixed in 4.1.21 and 5.0.23.

Code (note for self)

find . -iname "*mysql" | xargs cat | grep -B1 auto_increment | grep CREATE | grep -v "\-\-" | cut -d' ' -f 3 > tables
find . -iname "*mysql" | xargs cat | grep auto_increment | sed 's/,//' | sed 's/^ *//' > fields
paste tables fields | while read l; do echo $l | \
sed 's/\([^ ]*\) \([^ ]*\) \(.*\)$/ALTER TABLE \1 CHANGE \2 \2 \3;/'; \
done
MSameer's picture

Another solution

MSameer - 30/10/2006 - 5:58am
  1. mysqldump without compatible=mysql40
  2. create the new database with default charset latin1
  3. set names latin1;
  4. source backup.sql

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.


Dr. Radut | book