Thesqldump 5.5.25 use swap instead of memory

backupMySQLmysql-5.5mysqldump

mysql version 5.5.25, while use mysqldump to dump big tables, it eat lots of swap while there is lots of free memory.

# free -m
             total       used       free     shared    buffers     cached
Mem:         32177      31427        749          0        208      10747
-/+ buffers/cache:      20471      11705
Swap:        16002      12952       3049

# cat /proc/sys/vm/swappiness 
5

#[mysqldump]
quick
max_allowed_packet = 16M

Best Answer

Your problem is, that mysqldump normally caches the results it produces.
You have to use the --quick option, to prevent this.

 --quick, -q

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

Related Solutions

MySQL – MySqlDump uses \n instead of null

There are no options for handling the output of NULLs.

You may want to experiment with --hex-blob. This will dump BINARY, VARBINARY, BLOB fields in hexadecimal format. This should make the data portable. You can see what this option produces for NULL values.

mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n

Give it a Try and let us all know if this helps.

UPDATE 2012-01-13 18:26 EDT

This may sound very gross but you can pipe the output of mysqldump into sed as follows:

mysqldump --user=root --port=3306 --password=topsecret --hex-blob some_database --tab=C:\ARCHIVE\some_database --triggers --routines --fields-enclosed-by=\"" --fields-terminated-by=, --lines-terminated-by=\r\n | sed 's/\\N/NULL/g'

Of course, you cannot import that into MySQL until you want the string NULL to be the value to be imported.

Thesqldump not generating USE statement

Try putting the USE db_name manually

echo "USE db_name" > output.sql
mysqldump -u root db_name tbl_1 tbl_2 tbl_3 >> output.sql

I can see the wisdom of not mixing the USE command with select mysqldumping of tables. You do not want to risk creating database with the name of a production database. You also do not want the developer to become aware of not the database that will be the target. In this way, the mysqldump, in itself, can never be blamed for overwriting target tables.

Best Answer

Related Solutions

MySQL – MySqlDump uses \n instead of null

UPDATE 2012-01-13 18:26 EDT

Thesqldump not generating USE statement

Related Question