ちゃんるいすのブログ

オタクエンジニアの雑記

俺的 MySQL Group Replication まとめ


既存のテーブルが Group Replication に準拠してるか

  • エンジンが InnoDB であることが必要
  • Primary Key が必須だよ

エンジンが InnoDB かどうか

SELECT table_schema, table_name, engine, table_rows, 
       (index_length+data_length)/1024/1024 AS sizeMB 
FROM information_schema.tables 
WHERE engine != 'innodb' 
  AND table_schema NOT IN 
    ('information_schema', 'mysql', 'performance_schema');

テーブル構造が Group Replication 準拠か

SELECT tables.table_schema , tables.table_name , tables.engine 
FROM information_schema.tables 
LEFT JOIN ( 
   SELECT table_schema , table_name 
   FROM information_schema.statistics 
   GROUP BY table_schema, table_name, index_name HAVING 
     SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks 
 ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name 
 WHERE puks.table_name is null 
   AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";

lefred さんありがたやー
lefred.be