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 and 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

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...
Difference between DISTINCT and GROUP BY Today we had an interesting situation where the same query was executed significantly slower when it was written with GROUP BY instead of DISTINCT and...
How to decrease IOPS when running MySQL on ZFS Recordsize on data volume This is the first thing you read when it comes to running MySQL on ZFS. InnoDB is using 16k pages so in theory this makes a...