29 October 2012

MySQL errors: Error 'Unknown table engine 'InnoDB'' on query.

Again, while playing with Percona MySQL and their backup tool xtrabackup, I noticed the following error:
Error 'Unknown table engine 'InnoDB'' on query. Default database: 'database_name'. Query: 'sql_query'
The solution is very easy. I always have to check on master and slave the innodb_log_file_size option. It should be the same. Then restart the slave mysql and it should work.

MySQL replication: ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

I'm using a lot of replication lately, so one of the issues that I've noticed is:
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=53306, MASTER_USER='username', MASTER_PASSWORD = 'password', MASTER_LOG_FILE='mysql-bin.000000', MASTER_LOG_POS=12345;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
In the error log i didn't see anything, so after googling some time I found the solution:
mysql> flush slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=53306, MASTER_USER='username', MASTER_PASSWORD = 'pass', MASTER_LOG_FILE='mysql-bin.000000', MASTER_LOG_POS=12345;
Query OK, 0 rows affected (0.06 sec)