Mysql crash problems on EC2 AWS micro instance

If you have the default mysql config probably you’ll have some crash problems after a while running the instance.

I’m running a micro instance, with Ubuntu 12.04 and default mysql installation.

I’ve this logs reports:

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

[Note] /usr/sbin/mysqld: Shutdown complete

 

To solve it you need to add some lines on the /etc/mysql/my.cnf file after the [mysql] line:

innodb_buffer_pool_size = 40M
key_buffer_size=10M
max_connections=5

Also it’s recommended to run a repair table on each of your databases after changing the mysql settings and restart the mysqld service.

How to backup MySQL in diferent formats

The best way to have mysql backed up in different formats is to use PhpMyAdmin, you need to go to a database and the tab Export.

You can setup a Quick backup in different formats such as:

  • CodeGen
  • CSV
  • CSV for Excel
  • Microsoft Word 2000
  • JSON
  • Latex
  • MediaWiki Table
  • Open Document Spreadsheet
  • Open Document Text
  • PDF
  • PHP Array
  • SQL
  • Texty! text
  • XML
  • YAML

The best way to backup and be sure you can restore it fully functional is to use SQL backup, also you can backup your store procedures, trigger, useras and any other particular configuration of the database.

 

Screenshot for PhpMyAdmin:

13 09_51

How to backup MySQL database from the command line

Backup your MySQL database to an sql file with your command line. Using native mysql client and tools you can perform all the backup and restore operations you need.

First you need to have installed the command line tools, in this case mysqldump.

$ mysqldump --opt -u [uname] -p [dbname] > [backupfile.sql]
  • [uname] Your database username
  • [pass] The password for your database (note there is no space between -p and the password)
  • [dbname] The name of your database
  • [backupfile.sql] The filename for your database backup
  • [–opt] The mysqldump option

Example if you have a database called wordpress_blog you only have to do this:

$ mysqldump --opt -u root -p wordpress_blog > wordpress_blog_bk.sql

If you want to backup all the databases in only one sql file you can use –all-databases option

$ mysqldump -u root -p --all-databases > alldb_backup.sql