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.

 

 

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

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

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

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).

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

Make this file as a swap file using mkswap command.

Enable the newly created swapfile.

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

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

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

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

1
Leave a Reply

1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
1 Comment authors

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

  Subscribe  
Notify of
Tianjia

leave my name before on fire.