2 Ответы Последний ответ: 10.07.2020 19:15, автор: user619241

    Create Database Issue in InnoDB Cluster

    user619241

      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

        • 1. Re: Create Database Issue in InnoDB Cluster
          3237077

          Hello,

           

          You have multiple issues or misunderstandings here.

           

          1.  you are trying to create a database with your clusteradmin account. The accound doesn't have the permission to do so:

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

           

          2. you then try with the root account but certainly on a Secondary node, which by default is read-only. With InnoDB Cluster, the default is Single Primary, you can write only on one member, the Primary one. Run in mysqldh cluster.status() to see which one it is.

           

          3. you handle super-read only manually, you should never do that !

          I think you miss some conceptions on how the solution works.

          Please check documentation and check out some presentations.

           

          Cheers,

          1 пользователь считает эту информацию полезной
          • 2. Re: Create Database Issue in InnoDB Cluster
            user619241

            Hello Dave,

             

            I think you are all right. I find all of my issues yesterday. I then created a application specific user account to handle read/write operations to innotest_rw database. And I was actually doing read-write on a read-only node. After I successfully created my user account on a read-write node, everything works great.

             

            Thank you for help!

             

            - Xinhuan