TL;DR: Make sure to run “SET persist_only disabled_storage_engines=’MyISAM’, persist sql_generate_invisible_primary_key=ON;” on all instances and restart each one in your MySQL InnoDB Cluster.

Ok, what does “safe from naughtiness” mean?:
– Anyone creating tables that aren’t InnoDB, as this doesn’t make sense, after all, it is an “InnoDB” cluster.
– Making sure all tables have a Primary Key (invisible or not).
– Making sure that my (invisible) primary keys are visible to the cluster as it will rightfully complain if they aren’t!

This basically means that once you’ve got it all up and running you won’t run into those horrible situations whereby someone, somewhere, creates a MyISAM table that didn’t have a Primary Key and thus leave you with a broken cluster.

Eg.

MySQL rtnode-01:3306 ssl JS > vlc.status()
{
 "clusterName": "VLC",
 "clusterRole": "PRIMARY", …

This is just a note to help out anyone that might want to use the mysql_config_editor command in their automation tools. 

the mysql_config_editor does not take a password argument so automation tools that might have before set your password in the .my.cnf file trying to use mysql_config_editor fails. 

It is possible and quite simple though with the expect tool. 

 yum -y install expect  

it works for apt-get also. 

So in this example, I will show a simple bash script version. 

1st.. my login path does not work... 

mysql --login-path=local

ERROR 1045 (28000): Access denied for user

Set this with expect 

You would execute this via your bash script.  

expect <<EOD

spawn mysql_config_editor set --login-path=local …

So over the years, we all learn more and more about what we like and use often in MySQL. 

Currently, I step in and out of a robust about of different systems. I love it being able to see how different companies use MySQL.  I also see several aspect and settings that often get missed. So here are a few things I think should always be set and they not impact your MySQL database. 

At a high level:

• >Move the Slow log to a table 
• Set report_host_name 
• Set master & slaves to use tables
• Turn off log_queries_not_using_indexes until needed 
• Side note -- USE  ALGORITHM=INPLACE
• Side note -- USE mysql_config_editor
• Side note -- USE  mysql_upgrade  --upgrade-system-tables

Move the Slow log to a …

While working with different clients I happen to run across very large slow log files from time to time.  While several opinions on how they should be rotated exist. Many of these opinions use log rotate and the flush logs command,  I prefer not to flush my binary logs though. This is why I agree with Ronald Bradford's blog post from years ago on  how to do this.

• http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/

I have taken it a little further and scripted the steps. The bash script is built with MySQL 5.6 and the mysql_config_editor in mind it can be used on older versions of MySQL as well.

The script …