目錄
- 01 ReplicaSet的架構(gòu)
- 02 MySQL Shell的介紹以及安裝
- 03 MySQL Shell連接數(shù)據(jù)庫(kù)并創(chuàng)建ReplicaSet
01 ReplicaSet的架構(gòu)
前面的文章中，我們說(shuō)了ReplicaSet的基本概念和限制以及部署前的基本知識(shí)。今天我們來(lái)看InnoDB ReplicaSet部署過(guò)程中的兩個(gè)重要組件之一的MySQL Shell，為了更好的理解MySQL Shell，畫(huà)了一張圖，如下：
通過(guò)上面的圖，不難看出，MySQL Shell是運(yùn)維人員管理底層MySQL節(jié)點(diǎn)的入口，也就是DBA執(zhí)行管理命令的地方，而MySQL Router是應(yīng)用程序連接的入口，它的存在，讓底層的架構(gòu)對(duì)應(yīng)用程序透明，應(yīng)用程序只需要連接MySQL Router就可以和底層的數(shù)據(jù)庫(kù)打交道，而數(shù)據(jù)庫(kù)的主從架構(gòu)，都是記錄在MySQL Router的原信息里面的。
今天，我們主要來(lái)看MySQL Shell的搭建過(guò)程。
02 MySQL Shell的介紹以及安裝
MySQL Shel是一個(gè)客戶(hù)端工具，用于管理Innodb Cluster或者Innodb ReplicaSet，可以簡(jiǎn)單理解成ReplicaSet的一個(gè)入口。
它的安裝過(guò)程比較簡(jiǎn)單：在MySQL官網(wǎng)下載對(duì)應(yīng)版本的MySQL Shell即可。地址如下：
https://downloads.mysql.com/archives/shell/
這里使用8.0.20版本
下載完畢之后，在Linux服務(wù)器進(jìn)行解壓，然后就可以通過(guò)這個(gè)MySQL Shell來(lái)連接線(xiàn)上的MySQL服務(wù)了。
我的線(xiàn)上MySQL地址分別是：
192.168.1.10 5607
192.168.1.20 5607
可以直接通過(guò)下面的命令來(lái)連接MySQL服務(wù)：
/usr/local/mysql-shell-8.0.20/bin/mysqlsh '$user'@'$host':$port --password=$pass
成功連接之后的日志如下：
MySQL Shell 8.0.20
Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\&;' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'superdba@10.185.13.195:5607'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 831
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use schema> to set one.
MySQL 192.168.1.10:5607 ssl JS >
MySQL 192.168.1.10:5607 ssl JS >
MySQL 192.168.1.10:5607 ssl JS >
MySQL 192.168.1.10:5607 ssl JS >
03 MySQL Shell連接數(shù)據(jù)庫(kù)并創(chuàng)建ReplicaSet
上面已經(jīng)介紹了使用MySQL Shell連接數(shù)據(jù)庫(kù)的方法了，現(xiàn)在我們來(lái)看利用MySQL Shell來(lái)創(chuàng)建ReplicaSet的方法：
1、首先使用dba.configureReplicaSetInstance命令來(lái)配置副本集，并創(chuàng)建副本集的管理員。
MySQL 192.168.1.10:5607 ssl JS > dba.configureReplicaSetInstance('root@192.168.1.10:5607',{clusterAdmin:"'rsadmin'@'%'"})
Configuring MySQL instance at 192.168.1.10:5607 for use in an InnoDB ReplicaSet...
This instance reports its own address as 192.168.1.10:5607
WARNING: User 'rsadmin'@'%' already exists and will not be created. However, it is missing privileges.
The account 'rsadmin'@'%' is missing privileges required to manage an InnoDB cluster:
GRANT REPLICATION_APPLIER ON *.* TO 'rsadmin'@'%' WITH GRANT OPTION;
Dba.configureReplicaSetInstance: The account 'root'@'192.168.1.10' is missing privileges required to manage an InnoDB cluster. (RuntimeError)
可以看到，上面的命令中，我們配置了副本集的一個(gè)實(shí)例：192.168.1.10:5607，并創(chuàng)建了一個(gè)管理員賬號(hào)rsadmin，同時(shí)這個(gè)管理員擁有clusterAdmin的權(quán)限。
返回的結(jié)果中，有一個(gè)報(bào)錯(cuò)信息，它提示我們登陸的root賬號(hào)少了replication_applier的權(quán)限，因此無(wú)法使用root賬號(hào)對(duì)rsadmin賬號(hào)授權(quán)。我們給root賬號(hào)補(bǔ)充replication_applier權(quán)限之后，重新執(zhí)行上面的命令，結(jié)果如下：
MySQL 192.168.1.10:5607 ssl JS > dba.configureReplicaSetInstance('root@192.168.1.10:5607',{clusterAdmin:"'rsadmin'@'%'"})
Configuring MySQL instance at 192.168.1.10:5607 for use in an InnoDB ReplicaSet...
This instance reports its own address as 192.168.1.10:5607
User 'rsadmin'@'%' already exists and will not be created.
The instance '192.168.1.10:5607' is valid to be used in an InnoDB ReplicaSet.
The instance '192.168.1.10:5607' is already ready to be used in an InnoDB ReplicaSet.
這次執(zhí)行成功了。
我們登陸到底層的192.168.1.10上，查看rsadmin賬號(hào)，可以發(fā)現(xiàn)，賬號(hào)已經(jīng)生成了，信息如下：
select user,host,concat(user,"@'",host,"'"),authentication_string from mysql.user where user like "%%rsadmin";
+---------+------+----------------------------+-------------------------------------------+
| user | host | concat(user,"@'",host,"'") | authentication_string |
+---------+------+----------------------------+-------------------------------------------+
| rsadmin | % | rsadmin@'%' | *2090992BE9B9B27D89906C6CB13A8512DF49E439 |
+---------+------+----------------------------+-------------------------------------------+
1 row in set (0.00 sec)
show grants for rsadmin@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for rsadmin@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER ON *.* TO `rsadmin`@`%` WITH GRANT OPTION |
| GRANT BACKUP_ADMIN,CLONE_ADMIN,PERSIST_RO_VARIABLES_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `rsadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE ON `mysql`.* TO `rsadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata`.* TO `rsadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_bkp`.* TO `rsadmin`@`%` WITH GRANT OPTION |
| GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysql_innodb_cluster_metadata_previous`.* TO `rsadmin`@`%` WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
注意，如果我們加入的副本集實(shí)例是當(dāng)前連接的實(shí)例，那么也可以使用簡(jiǎn)單的寫(xiě)法：
dba.configureReplicaSetInstance('',{clusterAdmin:"'rsadmin'@'%'"})
2、使用dba.createReplicaSet命令創(chuàng)建副本集，并將結(jié)果保存在一個(gè)變量里面，如下：
MySQL 192.168.1.10:5607 ssl JS > var rs = dba.createReplicaSet("yeyz_test")
A new replicaset with instance '192.168.1.10:5607' will be created.
* Checking MySQL instance at 192.168.1.10:5607
This instance reports its own address as 192.168.1.10:5607
192.168.1.10:5607: Instance configuration is suitable.
* Updating metadata...
ReplicaSet object successfully created for 192.168.1.10:5607.
Use rs.addInstance() to add more asynchronously replicated instances to this replicaset and rs.status() to check its status.
可以看到，我們創(chuàng)建了一個(gè)yeyz_test的副本集，并將結(jié)果保存在變量rs當(dāng)中。
3、使用rs.status()查看當(dāng)前的副本集成員
MySQL 192.168.1.10:5607 ssl JS > rs.status()
{
"replicaSet": {
"name": "yeyz_test",
"primary": "192.168.1.10:5607",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"192.168.1.10:5607": {
"address": "192.168.1.10:5607",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
這里面，可以看到，當(dāng)前ReplicaSet里面已經(jīng)有192.168.1.10：5607這個(gè)實(shí)例的，他的狀態(tài)是available，他的角色是Primary。
4、此時(shí)我們使用rs.addInstance命令加入第2個(gè)節(jié)點(diǎn)，并使用rs.status查看狀態(tài)。
這里需要注意，加入第二個(gè)節(jié)點(diǎn)的時(shí)候，有一個(gè)數(shù)據(jù)同步的過(guò)程，這個(gè)數(shù)據(jù)同步有2中策略：
策略一：全量恢復(fù)
使用MySQL Clone組件，然后使用克隆快照來(lái)覆蓋新實(shí)例上面的所有數(shù)據(jù)。這種方法非常適合空白實(shí)例加入到Innodb 副本集中。
策略二：增量恢復(fù)
它依賴(lài)MySQL的復(fù)制功能，將所有的丟失的事務(wù)復(fù)制到新實(shí)例上，如果新實(shí)例上的事務(wù)很少，則這個(gè)過(guò)程會(huì)很快。這個(gè)方法需要保證集群中至少存在一個(gè)實(shí)例，它保存了這些缺失事務(wù)的binlog，如果缺失的事務(wù)的binlog已經(jīng)清理，則這個(gè)方法不能使用。
當(dāng)一個(gè)實(shí)例加入一個(gè)集群的時(shí)候，MySQL Shell會(huì)自動(dòng)嘗試挑選一個(gè)合適的策略來(lái)同步數(shù)據(jù)，不需要人為干預(yù)，如果它無(wú)法安全的選擇同步方法，則會(huì)提供給DBA一個(gè)選項(xiàng)，讓你選擇是通過(guò)Clone或者增量同步的方法來(lái)實(shí)現(xiàn)數(shù)據(jù)同步。
下面的例子中，就是通過(guò)自動(dòng)選擇增量同步的方法來(lái)同步數(shù)據(jù)的：
MySQL 192.168.1.10:5607 ssl JS > rs.addInstance("192.168.1.20:5607")
WARNING: Concurrent execution of ReplicaSet operations is not supported because the required MySQL lock service UDFs could not be installed on instance '10.41.28.127:5607'.
Make sure the MySQL lock service plugin is available on all instances if you want to be able to execute some operations at the same time. The operation will continue without concurrent execution support.
Adding instance to the replicaset...
* Performing validation checks
This instance reports its own address as 192.168.1.20:5607
192.168.1.20:5607: Instance configuration is suitable.
* Checking async replication topology...
* Checking transaction state of the instance...
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of '192.168.1.20:5607' with a physical snapshot from an existing replicaset member. To use this method by default, set the 'recoveryMethod' option to 'clone'.
WARNING: It should be safe to rely on replication to incrementally recover the state of the new instance if you are sure all updates ever executed in the replicaset were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the replicaset or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.
Incremental state recovery was selected because it seems to be safely usable.
* Updating topology
** Configuring 192.168.1.20:5607 to replicate from 192.168.1.10:5607
** Waiting for new instance to synchronize with PRIMARY...
The instance '192.168.1.20:5607' was added to the replicaset and is replicating from 192.168.1.20:5607.
MySQL 192.168.1.10:5607 ssl JS >
MySQL 192.168.1.10:5607 ssl JS > rs.status()
{
"replicaSet": {
"name": "yeyz_test",
"primary": "192.168.1.10:5607",
"status": "AVAILABLE",
"statusText": "All instances available.",
"topology": {
"192.168.1.10:5607": {
"address": "192.168.1.10:5607",
"instanceRole": "PRIMARY",
"mode": "R/W",
"status": "ONLINE"
},
"192.168.1.20:5607": {
"address": "192.168.1.20:5607",
"instanceRole": "SECONDARY",
"mode": "R/O",
"replication": {
"applierStatus": "APPLIED_ALL",
"applierThreadState": "Slave has read all relay log; waiting for more updates",
"receiverStatus": "ON",
"receiverThreadState": "Waiting for master to send event",
"replicationLag": null
},
"status": "ONLINE"
}
},
"type": "ASYNC"
}
}
加入第二個(gè)節(jié)點(diǎn)之后，可以看到，再次使用rs.status來(lái)查看副本集的結(jié)構(gòu)，可以看到Secondary節(jié)點(diǎn)已經(jīng)出現(xiàn)了，就是我們新加入的192.168.1.20：5607
當(dāng)然我們可以分別使用下面的命令查看更詳細(xì)的輸出：
rs.status({extended:0})
rs.status({extended:1})
rs.status({extended:2})
不同的級(jí)別，顯示的信息有所不同，等級(jí)越高，信息約詳細(xì)。
這里不得不說(shuō)一個(gè)小的bug，官方文檔建議寫(xiě)法是：
ReplicaSet.status(extended=1)
原文如下：
The output of ReplicaSet.status(extended=1) is very similar to Cluster.status(extended=1), but the main difference is that the replication field is always available because InnoDB ReplicaSet relies on MySQL Replication all of the time, unlike InnoDB Cluster which uses it during incremental recovery. For more information on the fields, see Checking a cluster's Status with Cluster.status().
但是實(shí)際操作過(guò)程中，這種寫(xiě)法會(huì)報(bào)錯(cuò)，如下：
MySQL 192.168.1.10:5607 ssl JS > sh.status(extended=1)
You are connected to a member of replicaset 'yeyz_test'.
ReplicaSet.status: Argument #1 is expected to be a map (ArgumentError)
不知道算不算一個(gè)bug。
5.搭建好副本集之后，查看primary節(jié)點(diǎn)的元信息庫(kù)表，并在primary寫(xiě)入數(shù)據(jù)，查看數(shù)據(jù)是否可以同步。
[(none)] 17:41:10>show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
| zjmdmm |
+-------------------------------+
6 rows in set (0.01 sec)
[(none)] 17:41:29>use mysql_innodb_cluster_metadata
Database changed
[mysql_innodb_cluster_metadata] 17:45:12>show tables;
+-----------------------------------------+
| Tables_in_mysql_innodb_cluster_metadata |
+-----------------------------------------+
| async_cluster_members |
| async_cluster_views |
| clusters |
| instances |
| router_rest_accounts |
| routers |
| schema_version |
| v2_ar_clusters |
| v2_ar_members |
| v2_clusters |
| v2_gr_clusters |
| v2_instances |
| v2_router_rest_accounts |
| v2_routers |
| v2_this_instance |
+-----------------------------------------+
15 rows in set (0.00 sec)
[mysql_innodb_cluster_metadata] 17:45:45>select * from routers;
Empty set (0.00 sec)
[(none)] 17:45:52>create database yeyazhou;
Query OK, 1 row affected (0.00 sec)
可以看到，Primary節(jié)點(diǎn)上有一個(gè)元信息數(shù)據(jù)庫(kù)mysql_innodb_cluster_metadata，里面保存了一些原信息，我們查看了router表，發(fā)現(xiàn)里面沒(méi)有數(shù)據(jù)，原因是我們沒(méi)有配置MySQL Router。后面的文章中會(huì)寫(xiě)到MySQL Router的配置過(guò)程。
在Primary上創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)yeyazhou，可以發(fā)現(xiàn)，在從庫(kù)上也已經(jīng)出現(xiàn)了對(duì)應(yīng)的DB，
192.168.1.20 [(none)] 17:41:41>show databases;
+-------------------------------+
| Database |
+-------------------------------+
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| sys |
| yeyazhou |
| zjmdmm |
+-------------------------------+
7 rows in set (0.00 sec)
說(shuō)明副本集的復(fù)制關(guān)系無(wú)誤。
至此，整個(gè)MySQL Shell連接MySQL實(shí)例并創(chuàng)建ReplicatSet的過(guò)程搭建完畢。
下一篇文章講述MySQL Router的搭建過(guò)程，以及如何使用MySQL Router來(lái)訪(fǎng)問(wèn)底層的數(shù)據(jù)庫(kù)。
以上就是MySQL Shell的介紹以及安裝的詳細(xì)內(nèi)容，更多關(guān)于MySQL Shell的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
您可能感興趣的文章:- 監(jiān)控MySQL主從狀態(tài)的shell腳本
- shell腳本一鍵安裝MySQL5.7.29的方法
- mysql常用備份命令和shell備份腳本分享
- shell腳本定時(shí)備份MySQL數(shù)據(jù)庫(kù)數(shù)據(jù)并保留指定時(shí)間
- shell腳本自動(dòng)化創(chuàng)建虛擬機(jī)的基本配置之tomcat--mysql--jdk--maven
- shell腳本實(shí)現(xiàn)mysql定時(shí)備份、刪除、恢復(fù)功能
- 一個(gè)Shell小腳本精準(zhǔn)統(tǒng)計(jì)Mysql每張表的行數(shù)實(shí)現(xiàn)
- 通過(guò)Shell腳本批量創(chuàng)建服務(wù)器上的MySQL數(shù)據(jù)庫(kù)賬號(hào)
- 使用shell腳本來(lái)給mysql加索引的方法
- 干掉一堆mysql數(shù)據(jù)庫(kù)，僅需這樣一個(gè)shell腳本（推薦）
- 使用shell腳本每天對(duì)MySQL多個(gè)數(shù)據(jù)庫(kù)自動(dòng)備份的講解
