How to exclude a database from your dump with ZRM (MySQL Community help needed)

Last month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database.
This mysqldump option doesn’t exist and these three guys have given us various helpful solutions.

But for those of us who use ZRM community to make backup with mysqldump, the –exclude-pattern seems to do the job :

--exclude-pattern "pattern" 
All databases or tables that match the pattern are not backed up. 
If --all-databases or --databases are specified, the exclude pattern applies
to database names. If --database is specified, the exclude pattern applies
to table names. Wildcard characters * (match one or more characters),
? (match one character), [] (match one of the characters specified
within parenthesis) and | (match one of the patterns) are supported.
For example: Exclude pattern "*_db|dbase[123]|abcd" will match names
tom_db, dbase1, dbase2, abcd. All tables or databases that match this pattern
will be excluded. Character # is not permitted in the exclude pattern.

But this option seems to not work properly and I remember that I’ve used a hack to force ZRM to exclude a database from the database list since I used this tools.
I’m not talented enough in perl to really hack the code of this tool, so, I made it simple.

I made ​​a simple change to this file : /usr/bin/mysql-zrm-backup
By adding this new line after the row number 1597 :  $params = “–databases @pdbs”

To obtain that :

} else {
 $params = "--all-databases ";
 @pdbs = &enumAllDatabases();
 if( defined $inputs{"exclude-pattern"} ){
 @pdbs = &filterPattern( @pdbs );
 my $l = @pdbs;
 if( $l == 0 ){
 &printAndDie( "Nothing to backup after exclude-pattern is applied\n" );
 }
 $params = "--databases @pdbs"
 }
 if( $verbose ){
 &printLog( "backup of the following databases will be done @pdbs\n" );
 }

I can now exclude a database from my dump (but not a table).

But I’m sure a talented person could try to review the code to improve it in a right way.
If you are this person, you know what needs to be done…

If you are a ZRM Community edition practicer, share your thoughts about that.

Temporary file behavior… (and how lsof save my life)

I would like to share this story based on a true event about the temporary files behavior in MySQL.

MONyog reports this error to my already full mailbox several times a day :


 

1 – Catch the query (if you can) !

 

I don’t have access to the client logs but I would like to know which query is involved in this error.

Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :

  •  Enable the query sniffer in MONyog (based on processlist) : Edit server -> Advanced settings -> Sniffer settings

 

  • Monitor your MySQL TMPDIR directory with this simple shell loop :
  • [ Use this command to retrieve the MySQL temporary directory : show variables like ‘tmpdir’; ]
  • [ In this case : tmpdir=/database/tmp ]
while [ 1 = 1 ]; do { date;ls -artlh /database/tmp ; df -h /database/tmp;
  lsof | grep mysql | grep /database/tmp;sleep 1; }; done
 
With this loop, I can follow in real time the informations about the files created in the MySQL temporary directory and it will be very useful to find how exactly the error happens (see below).

With the MONyog query sniffer, I found the query involved by comparing the two emails sent by MONyog (Error message above and the emails about long queries).
The explain plan of this query was as follow :

 

And finaly, the real error was :
ERROR 3 (HY000): Error writing file '/database/tmp/MYakhJC5' (Errcode: 28)
 Error (Code 3): Error writing file '/database/tmp/MYakhJC5' (Errcode: 28)
 Error (Code 1028): Sort aborted
# perror 28
 OS error code  28:  No space left on device

 

2 – No space left on device, really ?!

 

Let see the filesystem definition for /database/tmp with the df command :
And here is a list view of the files in the directory when the error occured :
 total 2,3G
 drwxrwx--- 11 mysql mysql 4,0K 16 févr. 10:37 ..
 -rw-rw----  1 mysql mysql    6  6 avril 15:39 mysql.pid
 srwxrwxrwx  1 mysql mysql    0  6 avril 15:39 mysql.sock
 -rw-rw----  1 mysql mysql 1,0K 12 avril 15:41 #sql_7237_0.MYI
 -rw-rw----  1 mysql mysql 2,3G 12 avril 15:42 #sql_7237_0.MYD
 -rw-rw----  1 mysql mysql 1,0K 12 avril 15:42 #sql_7237_5.MYI
 -rw-rw----  1 mysql mysql    0 12 avril 15:42 #sql_7237_5.MYD
 drwxrwx---  2 mysql mysql 4,0K 12 avril 15:43 .
The /database/tmp filesystem has a size of 6,5Go and there was a total of 2,3Go of files when the error occured !

 

3 – “Was passiert ?!”

 

The lsof command was my very good friend to discover what happened.
Let see the trace of my tiny loop when the error occured (lsof part only) :

 

 

I can see a 2,3Go temporary file but there are two other temporary files which continue to grow until the end of the world :-(
And these two (deleted) files appears to be the cause of my issue.

 

4 – What next ?

 

The killer question : why did you have only 6.5 GB for your temporary space ?
Answer : Why not :-)

You have to consider this behavior to set your temporary space properly, specially if this space is a ramdisk filesystem.

Tschüs !

PS : I let the experts explains why these two additional files are created (hint : look at the Exta column)