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

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.

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?

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