From: Christophe DUMONET (Christophe.Dumonetifma.fr)
Date: Fri Nov 19 2010 - 03:46:12 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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.Dumonetifma.fr
----------------------------------------------------

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]