Description:
Hello ,
I am new here. I have done the setup of MySQL InnoDB Cluster using 3 Machines. And connect all of them. On one of the machine, i have installed MySQL router.
Now i an trying to connect my application it is working fine for single connection. But when i start Multiple instances the Query in cluster is stuck and queued up.
Ex if i start the application from 10 System with same table access then it will stop to respond.
I have checked the processlistby using.
mysql> show processlist;
This will shows the result as
+-----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 7 | system user | | NULL | Connect | 114540 | executing | NULL |
| 10 | system user | | NULL | Connect | 114540 | Slave has read all relay log; waiting for more updates | NULL |
| 701 | wise | 10.32.181.34:37921 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 754 | wise | 10.32.181.34:37980 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 762 | wise | 10.32.181.34:37988 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 767 | wise | 10.32.181.34:37993 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 787 | wise | 10.32.181.34:38013 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 796 | wise | 10.32.181.34:38022 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 820 | wise | 10.32.181.34:38046 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 822 | wise | 10.32.181.34:38048 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 824 | wise | 10.32.181.34:38050 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 836 | wise | 10.32.181.34:38062 | wise | Query | 697 | query end | INSERT INTO `lime_survey_559683` (`startlanguage`, `datestamp`, `startdate`, `ipaddr`) VALUES ('en', |
| 842 | root | localhost | NULL | Query | 134 | checking permissions | GRANT ALL PRIVILEGES ON *.* TO 'wise'@'%' |
| 844 | root | localhost | NULL | Query | 0 | starting | show processlist |
+-----+-------------+--------------------+------+---------+--------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
Can you please help me to resolve this. As i am not aware why this will happened.
May be i have done some wrong configurations or i need to do some extra configurations.
Please Help...
How to repeat:
Install MysQL InnoDB Cluster.
Steps i Followed:
sudo rpm -i ./mysql57-community-release-el7-10.noarch.rpm
sudo yum update
sudo yum install mysql-community-server mysql-shell
sudo systemctl start mysqld.service
sudo systemctl enable mysqld.service
sudo grep 'A temporary password is generated for root@localhost' /var/log/mysqld.log |tail -1
ALTER USER 'root'@'localhost' IDENTIFIED BY '######';
===========================================
Configure Cluster
sudo -i mysqlsh
dba.configureLocalInstance();
\q
sudo systemctl restart mysqld.service
mysqlsh
dba.checkInstanceConfiguration('root@localhost:3306');
sudo systemctl restart mysqld.service
mysql -u root -######
===============================================
GRANT PRIVILLAGES
GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO wise@'%' WITH GRANT OPTION;
GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT ON performance_schema.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT ON sys.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO wise@'%' WITH GRANT OPTION;
GRANT SELECT ON mysql_innodb_cluster_metadata.* TO wise@'%';
GRANT SELECT ON performance_schema.global_status TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO wise@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO wise@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO wise@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO wise@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO wise@'%';
GRANT SELECT ON performance_schema.replication_group_members TO wise@'%';
GRANT ALL ON *.* TO 'wise'@'%';
GRANT REPLICATION SLAVE ON *.* TO wise@'%';
FLUSH PRIVILEGES;
SET GLOBAL innodb_strict_mode =0;
SET GLOBAL innodb_file_per_table=ON;
SET GLOBAL innodb_file_format=Barracuda;
==============================================
Create Cluster and Add instance
[root@hk02mysqlpoc2 testuser]# mysqlsh
mysql-js> shell.connect('wise@10.32.181.31:3306');
mysql-js> var cluster = dba.createCluster('WiseCluster');
mysql-js> cluster.status();
mysql-js> cluster.addInstance('wise@10.32.181.32:3306');