A solution to running out of memory while executing mysqldump

Are you trying to perform a mysqldump on a large table and running out of memory every time? This can be a frustrating experience. Even if you try to use the –quick parameter, you may still run out of memory. In this blog post, we will discuss a solution to this problem.

One option is to create a swap file to add more swap space. A swap file differs from a swap partition but can be accessible and dynamic. In the following steps, we will show you how to create a swap file.

First, create an empty file. This file will contain virtual memory contents, so make sure to create a file big enough for your needs. The following command will create a 1GiB file, which means +1GiB swap space for your system:

dd if=/dev/zero of=/media/tux/bigdisk/swapfile.img bs=1024 count=1M;

If you want to create a 3GiB file, change the count value to count=3M. Refer to the man dd for more information.

Next, make a “swap filesystem” inside your new swap file using the following command:

mkswap /media/tux/bigdisk/swapfile.img;
chmod 600 /media/tux/bigdisk/swapfile.img;
chown root:root /media/tux/bigdisk/swapfile.img;

To ensure that your new swap space is activated while booting up your computer, add it to the filesystem configuration file /etc/fstab. Add the following line to the end of the file:

/media/tux/bigdisk/swapfile.img swap swap sw 0 0

This is recommended because other filesystems (at least one that contains a swap file) must be mounted in read-write mode before we can access any files.

Finally, you can either reboot your computer or activate the new swap file manually with the following command:

swapon /media/tux/bigdisk/swapfile.img;

If everything goes well, you should see that more swap space is available for use. You can use the following commands to check your new swap and confirm that it is active:

cat /proc/swaps;

This should display something like:

Filename                           Type       Size    Used    Priority
/swapfile                          file       16777212 1048796    -2
/media/tux/bigdisk/swapfile.img    file       67108860 0          -3

You can also use the following command to check your swap usage:

grep 'Swap' /proc/meminfo;

This should display something like:

SwapCached:         132456 kB
SwapTotal:        83886072 kB
SwapFree:         82837276 kB

Creating a swap file can be an effective solution to running out of memory while performing a mysqldump on a large table. It is a simple, dynamic solution that can be implemented easily on most Linux systems. Following the steps outlined in this post, you should be able to create a swap file and add more swap space to your system.

This post is also available in: Greek

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.