unable to connect to mysql server, change mysql buffer pool size 1


When I am trying to fix a bug in tianjia’s server, I faced the following problem:

Mysql can not open connection, socket /var/run/mysql/…..

Following is the solution steps and what I was thinking when solving this problem,

check the log file of mysql

vim /var/log/mysql/error.log

the following lines in log file triggered my attention:

160106 16:40:13 [Note] Plugin ‘FEDERATED’ is disabled.
160106 16:40:13 InnoDB: The InnoDB memory heap is disabled
160106 16:40:13 InnoDB: Mutexes and rw_locks use GCC atomic builtins
160106 16:40:13 InnoDB: Compressed tables use zlib 1.2.8
160106 16:40:13 InnoDB: Using Linux native AIO
160106 16:40:13 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
160106 16:40:13 InnoDB: Completed initialization of buffer pool
160106 16:40:13 InnoDB: Fatal error: cannot allocate memory for the buffer pool
160106 16:40:13 [ERROR] Plugin ‘InnoDB’ init function returned error.
160106 16:40:13 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.
160106 16:40:13 [ERROR] Unknown/unsupported storage engine: InnoDB
160106 16:40:13 [ERROR] Aborting

160106 16:40:13 [Note] /usr/sbin/mysqld: Shutdown complete

Then I use commend ‘top’ to check the memory use of the server

top

Only 100M memory or so is free in the server whose memory is 512M in total.

So the problem is mysql server does not have enough memory to start.

Now we have two solution approaches.

1. reduce the size of pool buffer in mysql

The pool buffer size of mysql can be modified in the configuration file located at ‘/etc/mysql/my.conf’

vim /etc/mysql/my.conf

add following line under [mysqld] section

innodb_buffer_pool_size=64M

Actually, you can change the size of pool buffer freely by assigning different values to the variable.

2. add swap space in server

 

Method 1: Use a Hard Drive Partition for Additional Swap Space

If you have an additional hard disk, (or space available in an existing disk), create a partition using fdisk command. Let us assume that this partition is called /dev/sdc1

Now setup this newly created partition as swap area using the mkswap command as shown below.

 

 

# mkswap /dev/sdc1

Enable the swap partition for usage using swapon command as shown below.

# swapon /dev/sdc1

To make this swap space partition available even after the reboot, add the following line to the /etc/fstab file.

# cat /etc/fstab
/dev/sdc1               swap                    swap    defaults        0 0

Verify whether the newly created swap area is available for your use.

# swapon -s
Filename                        Type            Size    Used    Priority
/dev/sda2                       partition       4192956 0       -1
/dev/sdc1                       partition       1048568 0       -2

# free -k
             total       used       free     shared    buffers     cached
Mem:       3082356    3022364      59992          0      52056    2646472
-/+ buffers/cache:     323836    2758520
Swap:      5241524          0    5241524

Note: In the output of swapon -s command, the Type column will say “partition” if the swap space is created from a disk partition.

Method 2: Use a File for Additional Swap Space

If you don’t have any additional disks, you can create a file somewhere on your filesystem, and use that file for swap space.

The following dd command example creates a swap file with the name “myswapfile” under /root directory with a size of 1024MB (1GB).

# dd if=/dev/zero of=/root/myswapfile bs=1M count=1024
1024+0 records in
1024+0 records out

# ls -l /root/myswapfile
-rw-r--r--    1 root     root     1073741824 Aug 14 23:47 /root/myswapfile

Change the permission of the swap file so that only root can access it.

# chmod 600 /root/myswapfile

Make this file as a swap file using mkswap command.

# mkswap /root/myswapfile
Setting up swapspace version 1, size = 1073737 kB

Enable the newly created swapfile.

# swapon /root/myswapfile

To make this swap file available as a swap area even after the reboot, add the following line to the /etc/fstab file.

# cat /etc/fstab
/root/myswapfile               swap                    swap    defaults        0 0

Verify whether the newly created swap area is available for your use.

# swapon -s
Filename                        Type            Size    Used    Priority
/dev/sda2                       partition       4192956 0       -1
/root/myswapfile                file            1048568 0       -2

# free -k
             total       used       free     shared    buffers     cached
Mem:       3082356    3022364      59992          0      52056    2646472
-/+ buffers/cache:     323836    2758520
Swap:      5241524          0    5241524

Note: In the output of swapon -s command, the Type column will say “file” if the swap space is created from a swap file.

If you don’t want to reboot to verify whether the system takes all the swap space mentioned in the /etc/fstab, you can do the following, which will disable and enable all the swap partition mentioned in the /etc/fstab

# swapoff -a

# swapon -a

reference: http://www.thegeekstuff.com/2010/08/how-to-add-swap-space/


Leave a comment

Your email address will not be published. Required fields are marked *

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

One thought on “unable to connect to mysql server, change mysql buffer pool size