Skip to Main Content

Create Database Issue in InnoDB Cluster

user619241Jul 7 2020 — edited Jul 10 2020

I'm having trouble creating database in InnoDB Cluster set-up. Here is how I set-up my InnoDB Cluster:

  • Create (3) Compute instances installed CentOS 7
  • Install mysql-community-server & mysql-shell package to each instance
  • Configure host entries to each instance. The entries are:
    • 192.168.10.1 myserver1.example.com myserver1
    • 192.168.10.2 myserver2.example.com myserver2
    • 192.168.10.3 myserver3.example.com myserver3
  • Add 3306/tcp and 33061/tcp firewall ports to allow
  • On each instance, run dba.configureLocalInstance("root@localhost:3306") from mysqlsh. Choose option 2) Create a new admin account for InnoDB cluster with minimal required grants. The admin account name is icadmin
  • Create cluster and add Instance from mysqlsh on myserver1: cluster = dba.createCluster('TestCluster'); cluster.addInstance('icadmin@myserver2:3306'); cluster.addInstance('icadmin@myserver3:3306') Cluster is created successful

The problem is I cannot create a database after creating TestCluster. I tried to use mysqlsh to connect to primary cluster instance, and mysql to connect to localhost instance. Neither works:

mysqlsh --uri=icadmin@myserver1:3306

MySQL  myserver1:3306 ssl  JS > create database innotest_rw;

ERROR: 1044 (42000): Access denied for user 'icadmin'@'%' to database 'innotest_rw'

mysql -uroot -p

Enter password:

mysql> create database innotest_rw;

ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

I also tried to install mysqlrouter on a remote client, and created additional router account in bootstrap. Still, I cannot create database from remote client either.

[root@client mysqlrouter]# mysqlrouter --bootstrap icadmin@myserver1:3306 --directory /mysqlrouter --user=root --account=router

[root@client mysqlrouter]# ./start.sh

[root@client mysqlrouter]# mysql -urouter -P6446 -h127.0.0.1 -p

mysql> create database innotest_rw;

ERROR 1044 (42000): Access denied for user 'router'@'%' to database 'innotest_rw'

I figured to set super-read-only = 0, then use mysql connect to localhost instance. I can create a database, but I don't feel it is right. After creating database, I still cannot create tables from mysqlsh.

mysql -uroot -p

Enter password:

mysql> set global super-read-only = 0;

mysql> create database innotest_rw;

mysql> use innotest_rw;

mysql> create table t1 (name varchar(100));

mysql> insert into t1 values ('My Name');

ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.

What is correct way to create database, tables in InnoDB Cluster?

How do I do insert/update/delete records in InnoDB Cluster?

Thanks,

- Xinhuan

Comments
Post Details
Added on Jul 7 2020
2 comments
194 views