MySQL 5.6 mysql_install_db script problem

We’re always testing the latest versions of MySQL with most of the environments to make sure that we can find the critical issues before it goes to production. This wasn’t different with the 5.6 MySQL neither. We already started to play with this version in the summer. The first news were very promising. The performance gain is significant. However we have run into couple of errors. One of them was the

mysql_install_db problem with replicating environment

The important part is this:

Info table is not ready to be used. Table ‘mysql.slave_master_info’ cannot be opened.
Info table is not ready to be used. Table ‘mysql.slave_relay_log_info‘ cannot be opened.

A new feature in MySQL 5.6 is to store the replication info in a table rather than the master.info and relay.info files like the previous versions did. This makes the slave more durable (the D from the ACID).

Nevertheless this only happens with configured slave this is quite annoying. In a centrally configured environment (puppet for example) the my.cnf is already in place when you want to run the mysql_install_db.

Solution A

Backup the my.cnf file and remove all the replication related config from it. After the successful run you can put back to old file.

Solution B

The only problem with the previous solution is that it cannot be easily scripted and the mysql_install_db cannot be integrated smoothly in an automated provisioning process. Running mysql_install_db without the my.cnf file won’t look for slave_info so the problem solved. However there are some magic still need to be added (explained below).

The datadir and innodb_log_file_size are the only parameters that are really essential. Without the log_file_size the myslq_install_db will create the default sized file which is usually (hopefully) different from the configured size. I think I don’t need the explain the necessity of the datadir variable.

Downside of this solution is that the mysql_install_db will run with very different settings than the MySQL afterwards.

Note: Check innodb_data_file_path variable if the autoextend is not in it you should define it for the script just like you did with the log_file_size.

You might like these too

MySQL Benchmark – updates by primary vs seco... (Note: when I'm talking about MySQL I usually assume InnoDB storage engine. Any other case I explicitly tell this is MyISAM or Memory etc.) I've he...
How to troubleshoot MySQL replication issues? In MySQL a big portion of the problems you're facing is different replication delays so there's no surprise this is one of the most common interview q...
Full text search in MySQL Full text is a critical point when it comes to mysql. It used to have that feature in MyISAM but that's not really maintained anymore nor it is advise...