I created an AMI from my running EC2 Ubuntu instance. I then brought it up, and MySql is not running the same way on this copied instance.
1) Changes to my my.cnf file are not getting picked up.
2) I am unable to query my database as it gives me the following error:
ERROR 1286 (42000): Unknown table engine 'InnoDB'
I guess MySql is not configured correctly? I'm new to MySql, so can you tell me what I'm doing wrong please?
Thanks in advance.
Check /var/log/syslog for any errors that occurred during MySQL's startup. It may be that your /etc/mysql/my.cnf file includes a skip-innodb statement, which would disable InnoDB tables.
I learnt (after several hours of troubleshooting) that if something goes wrong in your setup, none of the settings are updated.
For eg. in my case,the replication was broken. However, Instead of
Slave_IO_Running: No
Slave_SQL_Running: No
Turning up as "NO", the name of the master host also didn't get updated, which I had changed in my .cnf file. Also, skip-innoDB was not set in my .cnf file, but innoDB was still not getting enabled. This led me to suspect that the problem was related to the .cnf file.
It turned out, however, to be two separate things.
InnoDB could not come up due to a shortage of memory. The memory I was allocating was more than the new EC2 instance offered. This is a lesson creating images : pay attention to the size of the instance you are creating.
The second problem was that replication was not coming up. This is because I had not:
RESET SLAVE
And then appropriately modified:
MASTER_LOG_POS
I got the answer to this on another SF question:
Why is MySQL Replication so complicated?
Great guide posted as an answer there.
I got pointers to all this in my SQL log located in:
/var/log/mysql/error.log
It had the following entries:
InnoDB: Error: cannot allocate 1048592384 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 38079360 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: Fatal error: cannot allocate the memory for the buffer pool
110616 18:41:58 [ERROR] Plugin 'InnoDB' init function returned error.
110616 18:41:58 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110616 18:41:58 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may
break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-
log=ip-xxxx-relay-bin' to avoid this problem.
110616 18:41:58 [ERROR] Failed to open the relay log 'xxxx' (relay_log_pos 251)
110616 18:41:58 [ERROR] Could not find target log during relay log initialization
110616 18:41:58 [ERROR] Failed to initialize the master info structure
I really hope this helps someone some day!
