mysql innodb cluster(生產(chǎn)環(huán)境部署)

https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-working-with-production-deployment.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-limitations.html

install (3 nodes)

yum install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm -y
yum install mysql-community-server -y
yum install mysql-shell -y
yum install mysql-router -y

[xzx@cloud4ourself-kube1 ~]$ mysql --version
mysql Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using EditLine wrapper
[xzx@cloud4ourself-kube1 ~]$ mysqlrouter --version
MySQL Router v2.1.3 on Linux (64-bit) (GPL community edition)
[xzx@cloud4ourself-kube1 ~]$ mysqlsh --version
MySQL Shell Version 1.0.9

configure (3 nodes)

service mysqld start
grep 'temporary password' /var/log/mysqld.log
mysql -uroot -p
mysql> set global validate_password_policy = 0;
mysql> set password=PASSWORD('myrootpwd');
mysql> set global validate_password_policy = 0;

CREATE USER myuser IDENTIFIED BY 'new_password';
GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO myuser;
GRANT SELECT ON performance_schema.* TO myuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO myuser;
GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.*  TO myuser WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.*  TO myuser WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.*  TO myuser;

/etc/my.cnf (3 nodes )

server_id = 1  # 2 , 3
enforce_gtid_consistency = ON
gtid_mode = ON
log_bin = 1
log_slave_updates = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
transaction_write_set_extraction = XXHASH64
binlog_checksum = NONE
validate_password_policy = 0

cluster

mysqlsh --log-level=DEBUG3
mysql-js> dba.checkInstanceConfiguration('myuser@10.9.5.91:3306')
mysql-js> dba.checkInstanceConfiguration('myuser@10.9.5.90:3306')
mysql-js> dba.checkInstanceConfiguration('myuser@10.9.5.71:3306')


mysql-js> shell.connect('myuser@10.9.5.91:3306')
mysql-js> var cluster = dba.createCluster('myCluster')

mysql-js> cluster.checkInstanceState('myuser@10.9.5.91:3306')
mysql-js> cluster.checkInstanceState('myuser@10.9.5.90:3306')
mysql-js> cluster.checkInstanceState('myuser@10.9.5.71:3306')


mysql-js>  cluster.checkInstanceState('myuser@10.9.5.90:3306')
Please provide the password for 'myuser@10.9.5.90:3306':
Analyzing the instance replication state...

The instance '10.9.5.90:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new",
    "state": "ok"
}

mysql-js>  cluster.checkInstanceState('myuser@10.9.5.71:3306')
Please provide the password for 'myuser@10.9.5.71:3306':
Analyzing the instance replication state...

The instance '10.9.5.71:3306' is valid for the cluster.
The instance is new to Group Replication.

{
    "reason": "new",
    "state": "ok"
}
cluster.status()

cluster.addInstance('myuser@10.9.5.90:3306')
cluster.addInstance('myuser@10.9.5.71:3306')


mysql-js> cluster.status()
{
    "clusterName": "myCluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "10.9.5.91:3306",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "10.9.5.71:3306": {
                "address": "10.9.5.71:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "10.9.5.90:3306": {
                "address": "10.9.5.90:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "10.9.5.91:3306": {
                "address": "10.9.5.91:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    }
}

mysql router

[root@cloud4ourself-kube1 ~]# mysqlrouter --bootstrap myuser@10.9.5.91:3306 --user=root
Please enter MySQL password for myuser:

Reconfiguring system MySQL Router instance...
MySQL Router  has now been configured for the InnoDB cluster 'myCluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'myCluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447

X protocol connections to cluster 'myCluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

Existing configurations backed up to /etc/mysqlrouter/mysqlrouter.conf.bak

[root@cloud4ourself-kube1 ~]# mysqlrouter &
[1] 7524

error

mysql-js> cluster.addInstance('myuser@10.9.5.90:3306')
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Please provide the password for 'myuser@10.9.5.90:3306':
Adding instance to the cluster ...

Cluster.addInstance: ERROR:
Group Replication join failed.
ERROR: Group Replication plugin failed to start. Server error log contains the following errors:
 2017-05-04T09:08:59.075925Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: cff92742-3097-11e7-a452-fa163ecea498:1-2 > Group transactions: 7738d877-30a8-11e7-a7c6-fa163e72ab1f:1-17,
2017-05-04T09:08:59.075977Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

ERROR: Error joining instance to cluster: '10.9.5.90@3306' - Query failed. 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication (RuntimeError)

fix

mysql> set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.01 sec)

貌似 cluster.rejoinInstance('myuser@10.9.5.71:3306') 此方法存在bug,其使用root去連目標(biāo)數(shù)據(jù)庫貌夕,因此建議還是使用root用戶做操作律歼。

test

  • create table
mysql-js> \sql
Switching to SQL mode... Commands end with ;
mysql-sql> create database b;
Query OK, 1 row affected (0.08 sec)
mysql-sql> use b;
Query OK, 0 rows affected (0.00 sec)
mysql-sql> create table a (c int);
Query OK, 0 rows affected (0.29 sec)
mysql-sql> insert into a values(1);
ERROR: 3098 (HY000): The table does not comply with the requirements by an external plugin.
mysql-sql> alter table a add column a_id int(4);
Query OK, 0 rows affected (0.81 sec)
mysql-sql> alter table a add primary key pk (a_id);
Query OK, 0 rows affected (0.30 sec)
mysql-sql> insert into a values (1,2);
Query OK, 1 row affected (0.06 sec)
  • check mysql slave nodes
mysql> select * from b.a;
+------+------+
| c    | a_id |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)
  • stop one of mysql slave nodes
[root@cloud4ourself-kube3 ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service
  • check cluster status
        "topology": {
            "10.9.5.71:3306": {
                "address": "10.9.5.71:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "(MISSING)"
            },
            "10.9.5.90:3306": {
                "address": "10.9.5.90:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "10.9.5.91:3306": {
                "address": "10.9.5.91:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
  • insert one recode
mysql-sql> insert into a values (2,3);
Query OK, 1 row affected (0.05 sec)

# and the online slave node 

mysql> select * from b.a;
+------+------+
| c    | a_id |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

  • start 10.9.5.71:3306 and check the table
mysql> select * from b.a;
+------+------+
| c    | a_id |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)
  • rejoininstance 10.9.5.71:3306
mysql-sql> \js
Switching to JavaScript mode...
mysql-js> cluster.rejoinInstance('root@10.9.5.71:3306')
Rejoining the instance to the InnoDB cluster. Depending on the original
problem that made the instance unavailable, the rejoin operation might not be
successful and further manual steps will be needed to fix the underlying
problem.

Please monitor the output of the rejoin operation and take necessary action if
the instance cannot rejoin.

Please provide the password for 'root@10.9.5.71:3306':
Rejoining instance to the cluster ...

The instance 'root@10.9.5.71:3306' was successfully rejoined on the cluster.

The instance '10.9.5.71:3306' was successfully added to the MySQL Cluster.
  • check 10.9.5.71:3306 again
mysql> select * from b.a;
+------+------+
| c    | a_id |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市啡专,隨后出現(xiàn)的幾起案子险毁,更是在濱河造成了極大的恐慌,老刑警劉巖们童,帶你破解...
    沈念sama閱讀 211,639評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件畔况,死亡現(xiàn)場離奇詭異,居然都是意外死亡病附,警方通過查閱死者的電腦和手機(jī)问窃,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,277評論 3 385
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來完沪,“玉大人域庇,你說我怎么就攤上這事「不” “怎么了听皿?”我有些...
    開封第一講書人閱讀 157,221評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長宽档。 經(jīng)常有香客問我尉姨,道長,這世上最難降的妖魔是什么吗冤? 我笑而不...
    開封第一講書人閱讀 56,474評論 1 283
  • 正文 為了忘掉前任又厉,我火速辦了婚禮,結(jié)果婚禮上椎瘟,老公的妹妹穿的比我還像新娘覆致。我一直安慰自己,他們只是感情好肺蔚,可當(dāng)我...
    茶點故事閱讀 65,570評論 6 386
  • 文/花漫 我一把揭開白布煌妈。 她就那樣靜靜地躺著,像睡著了一般宣羊。 火紅的嫁衣襯著肌膚如雪璧诵。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,816評論 1 290
  • 那天仇冯,我揣著相機(jī)與錄音之宿,去河邊找鬼。 笑死苛坚,一個胖子當(dāng)著我的面吹牛澈缺,可吹牛的內(nèi)容都是我干的坪创。 我是一名探鬼主播炕婶,決...
    沈念sama閱讀 38,957評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼姐赡,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了柠掂?” 一聲冷哼從身側(cè)響起项滑,我...
    開封第一講書人閱讀 37,718評論 0 266
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎涯贞,沒想到半個月后枪狂,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,176評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡宋渔,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,511評論 2 327
  • 正文 我和宋清朗相戀三年州疾,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片皇拣。...
    茶點故事閱讀 38,646評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡严蓖,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出氧急,到底是詐尸還是另有隱情颗胡,我是刑警寧澤,帶...
    沈念sama閱讀 34,322評論 4 330
  • 正文 年R本政府宣布吩坝,位于F島的核電站毒姨,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏钉寝。R本人自食惡果不足惜弧呐,卻給世界環(huán)境...
    茶點故事閱讀 39,934評論 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望嵌纲。 院中可真熱鬧俘枫,春花似錦、人聲如沸疹瘦。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,755評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽言沐。三九已至邓嘹,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間险胰,已是汗流浹背汹押。 一陣腳步聲響...
    開封第一講書人閱讀 31,987評論 1 266
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點兒被人妖公主榨干…… 1. 我叫王不留起便,地道東北人棚贾。 一個月前我還...
    沈念sama閱讀 46,358評論 2 360
  • 正文 我出身青樓窖维,卻偏偏與公主長得像,于是被迫代替她去往敵國和親妙痹。 傳聞我的和親對象是個殘疾皇子铸史,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,514評論 2 348

推薦閱讀更多精彩內(nèi)容