List:General Discussion« Previous MessageNext Message »
From:Christophe DUMONET Date:November 19 2010 9:46am
Subject:need Help - Mysqldump issue
View as plain text  
Hello,
Starting today,  my daily database backup script does not work :-( with 
mysqldump typically Out of memory error.
So, I try to change max_allowed_packet option value, but I don't succeed
(mysql run on ubuntu 10.04 OS with  5.1.41-3ubuntu12.7 0 mysql version.)

On the last successfull backup, database size was : 2,59 Go

Here is some of my test :

(with  --max_allowed_packet option  = 512M )
/usr/bin/mysqldump  -A  --max_allowed_packet=512M 
--default-character-set=UTF8 -u root -p >  /tmp/testbackup01.sql
Enter password:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes 
when dumping table `jahia_sl2_version_content` at row: 0

(with  --max_allowed_packet = 1024M or 2048 option : )
/usr/bin/mysqldump  -A  --max_allowed_packet=1024M 
--default-character-set=UTF8 -u root -p >  /tmp/testbackup01.sql
Enter password:
mysqldump: Out of memory (Needed 1405796107 bytes)
mysqldump: Couldn't allocate memory

(with  --max_allowed_packet = 4096M  option : )
/usr/bin/mysqldump  -A  --skip-quick --max_allowed_packet=4096M 
--default-character-set=UTF8 -u root -p >  /tmp/testbackup01.sql
Warning: option 'max_allowed_packet': unsigned value 4294967296 adjusted 
to 2147483648
Enter password:
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when 
retrieving data from server

Adding --skip-opt --quick option does not solve the issue

Adding  --skip-quick, the error is :
mysqldump: Out of memory (Needed 702898104 bytes)
mysqldump: Got error: 2008: MySQL client ran out of memory when 
retrieving data from server

Here is my config : /etc/mysql/my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 4096M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M

log_error                = /var/log/mysql/error.log

expire_logs_days        = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

includedir /etc/mysql/conf.d/

Any help would be appreciate !!
Bests
-- 

----------------------------------------------------
  Christophe Dumonet
  Centre de Ressources Informatiques
  Institut Francais de Mecanique Avancee (IFMA)
  Campus des Cezeaux
  BP 265
  63175 AUBIERE Cedex
  Tel : +33 - 4.73.28.80.64
  Fax : +33 - 4.73.28.81.00
  Mail : Christophe.Dumonet@stripped
----------------------------------------------------


Thread
need Help - Mysqldump issueChristophe DUMONET19 Nov
  • Re: need Help - Mysqldump issueChristophe DUMONET22 Nov