搭建MySQL高可用集群 MySQL InnoDB Cluster 搭建过程基于Ubuntu 20.04系统搭建系统防火墙均已关闭为了方便管理，主节点和从节点root密码统一使用一个模式：单主多从graph LR A[Application/Client] --> B[Mysql Router] B --> A B --> C[MySQL Master R/W] C --> |Group Replication| D[Slave01 R/O] C --> |Group Replication| E[Slave02 R/O] C --> |Group Replication| F[Slave02 R/O] C --> |Group Replication| G[Slave02 R/O] 节点IP作用node01192.168.121.135主node02192.168.121.136从node03192.168.121.137从node04192.168.121.138路由管理系统配置(所有服务器)在每台服务器上配置hosts，编辑/etc/hosts文件，加入以下内容192.168.121.135 node01 192.168.121.136 node02 192.168.121.137 node03 192.168.121.138 node04软件安装主节点、从节点、路由管理节点初始化，安装MySQL官方apt源sudo wget https://repo.mysql.com/mysql-apt-config_0.8.19-1_all.deb && sudo dpkg -i mysql-apt-config_0.8.19-1_all.deb && sudo apt-get update主节点、从节点安装mysql-server、mysql-shellsudo apt install mysql-server mysql-shell -y路由管理节点安装mysql-shell、mysql-routersudo apt install mysql-router mysql-shell -yMySQL配置(主、从服务器)修改MySQL配置文件/etc/mysql/mysql.conf.d/mysqld.cnf(主节点)[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log default-storage-engine = INNODB character-set-server = utf8mb4 port = 3306 #复制框架 server_id = 1 gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay_log_info_repository = TABLE binlog_checksum = NONE log_slave_updates = ON log_bin = binlog binlog_format = ROW max_connections = 100 max_allowed_packet = 100M #组复制设置 #server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列 transaction_write_set_extraction = XXHASH64 #告知插件加入或创建组命名，UUID loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。 loose-group_replication_start_on_boot = off #告诉插件使用IP地址，端口33061用于接收组中其他成员转入连接 loose-group_replication_local_address = "node01:33061" #启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意 loose-group_replication_group_seeds = "node01:33061,node02:33061,node03:33061" loose-group_replication_ip_whitelist = "node01,node02,node03,node04" loose-group_replication_bootstrap_group = off # 使用MGR的单主模式 loose-group_replication_single_primary_mode = on loose-group_replication_enforce_update_everywhere_checks = off disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE从节点也需要修改，只需要把配置文件中的server_id、loose-group_replication_local_address修改为对应的值例如node02server_id = 2 loose-group_replication_local_address = "node02:33061"以此类推重启MySQLsudo service mysql restart使root用户支持远程登录update mysql.user set host='%' where user='root';赋予管理集群所需的权限GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;刷新权限flush privileges;配置本地实例在所有节点服务器上配置本地实例，使用mysqlsh工具dba.configureLocalInstance('root@localhost:3306', {'password': 'root', 'interactive': false});检查实例状态dba.checkInstanceConfiguration("root@localhost:3306");如果输出以下内容说明配置成功。The instance 'node01:3306' is valid to be used in an InnoDB cluster. { "status": "ok" }创建集群在主节点上创建集群，同样使用mysqlsh工具# 连接实例 shell.connect('root@localhost:3306'); # 创建一个集群，命名为 'myCluster' var cluster = dba.createCluster('myCluster'); # 查看集群状态 cluster.status();输出以下内容就表示创建成功{ "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "node01:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "node01:3306": { "address": "node01:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.27" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "node01:3306" }从节点加入集群在主节点中打开mysqlsh工具# 连接实例 shell.connect('root@localhost:3306'); # 根据集群名字获取集群对象 var cluster = dba.getCluster('myCluster'); # 将node02节点加入集群 cluster.addInstance('root@node02:3306');如果出现WARNING: A GTID set check of the MySQL instance at 'node02:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster. node02:3306 has the following errant GTIDs that do not exist in the cluster: bbdb0716-32e7-11ec-86e0-000c29040a05:1-2 WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of node02:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'. Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method. Please select a recovery method [C]lone/[A]bort (default Abort):输入C继续，之后提示出下面内容说明加入成功The instance 'node02:3306' was successfully added to the cluster.其他从节点加入的方式一致查询集群状态cluster.status()输出下面内容{ "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "node01:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "node01:3306": { "address": "node01:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.27" }, "node02:3306": { "address": "node02:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.27" }, "node03:3306": { "address": "node03:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.27" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "node01:3306" }可以看到，所有节点均已加入集群，并且正常运行配置 MySQL Router创建配置文件保存目录sudo mkdir /mysqlrouter将创建的目录授权给mysqlrouter用户编辑/etc/apparmor.d/usr.bin.mysqlrouter文件，加入以下内容# Allow config file access /mysqlrouter/ rw, /mysqlrouter/** rw,重启apparmorsudo /etc/init.d/apparmor restart配置 MySQL Router，生成配置文件到/data/mysqlrouter目录sudo mysqlrouter --bootstrap node01:3306 --directory /mysqlrouter --user=root --conf-use-sockets --force--conf-use-sockets 表示使用 unix sockets 通信--directory 指定配置文件保存目录输出下面内容表示创建成功# Bootstrapping MySQL Router instance at '/mysqlrouter'... - Creating account(s) (only those that are needed, if any) - Verifying account (using it to run SQL queries that would be run by Router) - Storing account in keyring - Adjusting permissions of generated files - Creating configuration /mysqlrouter/mysqlrouter.conf # MySQL Router configured for the InnoDB Cluster 'myCluster' After this MySQL Router has been started with the generated configuration $ mysqlrouter -c /mysqlrouter/mysqlrouter.conf InnoDB Cluster 'myCluster' can be reached by connecting to: ## MySQL Classic protocol - Read/Write Connections: localhost:6446, /mysqlrouter/mysql.sock - Read/Only Connections: localhost:6447, /mysqlrouter/mysqlro.sock ## MySQL X protocol - Read/Write Connections: localhost:6448, /mysqlrouter/mysqlx.sock - Read/Only Connections: localhost:6449, /mysqlrouter/mysqlxro.sock启动 MySQL Routersudo /mysqlrouter/start.sh启动完成后会监听下面几个端口端口socket文件协议服务6446/mysqlrouter/mysql.sockMySQLMySQL 读写6447/mysqlrouter/mysqlro.sockMySQLMySQL 只读6448/mysqlrouter/mysqlx.sockMySQL XMySQL X 读写6449/mysqlrouter/mysqlxro.sockMySQL XMySQL X 只读8443无HTTPSWeb 管理验证集群登录后，新建一个数据库和表，往里面写进数据，查看从节点数据会不会同步；关闭node01的mysql服务，route将主节点自动切换到node02，node02从只读变为可读写，重新启动node01 mysql后，node01变为只读模式。

MYSQL | 最左匹配原则 最左匹配原则最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。例如某表现有索引(a,b,c)，现在你有如下语句：

MySQL分区表 分区：分区的功能不是在存储引擎层实现的。因此不只是InnoDB才支持分区。MyISAM、NDB都支持分区操作。分区的过程是将一个表或者索引分解为多个更小、更可管理的部分。从逻辑上将，只有一个表或者索引，但是在物理上这个表或索引可能由数十个物理分区组成。