mysql innodb 主主_MySQL InnoDB Cluser 多主模式实现高可用负载均衡

核心架构

MySQL 的 Group Replication 功能，可以在一组 MySQL 服务器之间实现自动主机选举，形成一主多从结构。经过高级配置后，可以实现多主多从结构。

MySQL Router 是一个轻量级透明中间件，可以自动获取上述集群的状态，规划 SQL 语句，分配到合理的 MySQL 后端进行执行。

MySQL Shell 是一个同时支持 JavaScript 和 SQL 的交互程序，可以快速配置 InnoDB Cluster。

准备工作

te

本次共 5 台机器，服务器均采用 Windows Server 2012，服务器地址如下：

172.16.220.10

172.16.220.11

172.16.220.12

172.16.220.13

172.16.220.14

下载 MySQL、Shell、Router 软件

https://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.15-winx64.zip

https://cdn.mysql.com/Downloads/MySQL-Router/mysql-router-8.0.15-winx64.zip

https://cdn.mysql.com/Downloads/MySQL-Shell/mysql-shell-8.0.15-windows-x86-64bit.zip

安装 MySQL

首先配置 my.ini，以下 my.ini 为 172.16.2201.10 上的配置：

# Other default tuning values

# MySQL Server Instance Configuration File

# ----------------------------------------------------------------------

# Generated by the MySQL Server Instance Configuration Wizard

#

#

# Installation Instructions

# ----------------------------------------------------------------------

#

# On Linux you can copy this file to /etc/my.cnf to set global options,

# mysql-data-dir/my.cnf to set server-specific options

# (@localstatedir@ for this installation) or to

# ~/.my.cnf to set user-specific options.

#

# On Windows you should keep this file in the installation directory

# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To

# make sure the server reads the config file use the startup option

# "--defaults-file".

#

# To run the server from the command line, execute this in a

# command line shell, e.g.

# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

#

# To install the server as a Windows service manually, execute this in a

# command line shell, e.g.

# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"

#

# And then execute this in a command line shell to start the server, e.g.

# net start MySQLXY

#

#

# Guidelines for editing this file

# ----------------------------------------------------------------------

#

# In this file, you can use all long options that the program supports.

# If you want to know the options a program supports, start the program

# with the "--help" option.

#

# More detailed information about the individual options can also be

# found in the manual.

#

# For advice on how to change settings please see

# https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

#

#

# CLIENT SECTION

# ----------------------------------------------------------------------

#

# The following options will be read by MySQL client applications.

# Note that only client applications shipped by MySQL are guaranteed

# to read this section. If you want your own MySQL client program to

# honor these values, you need to specify it as an option during the

# MySQL client library initialization.

#

[client]

# pipe=

socket=MYSQL

port=3346

# ----------------------------------------------------------------------

# 1、替换PCSS为PCSS10，PCSS11，PCSS12，PCSS13，PCSS14等

# 2、server-id 为指定值，需要不同

# 3、report-host 为指定值，需要不同

# ----------------------------------------------------------------------

[mysql]

no-beep

default-character-set=UTF-8

# SERVER SECTION

# ----------------------------------------------------------------------

#

# The following options will be read by the MySQL Server. Make sure that

# you have installed the server correctly (see above) so it reads this

# file.

#

# server_type=1

[mysqld]

# The next three options are mutually exclusive to SERVER_PORT below.

# skip-networking

enable-named-pipe

shared-memory

shared-memory-base-name=MYSQL

# The Pipe the MySQL Server will use

socket=MYSQL

# The TCP/IP Port the MySQL Server will listen on

port=3346

# Path to installation directory. All paths are usually resolved relative to this.

# basedir="C:/Program Files/MySQL/MySQL Server 8.0/"

# Path to the database root

basedir=D:/PhpWamp/phpwamp/server/mysql-8.1

datadir=D:/PhpWamp/phpwamp/server/mysql-8.1/data

# The default character set that will be used when a new schema or table is

# created and no character set is defined

# character-set-server=

# The default authentication plugin to be used when connecting to the server

default_authentication_plugin=mysql_native_password

# The default storage engine that will be used when create new tables when

default-storage-engine=INNODB

# Set the SQL mode to strict

# sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

# General and Slow logging.

log-output=FILE

general-log=0

general_log_file="PCSS.log"

slow-query-log=1

slow_query_log_file="PCSS-slow.log"

long_query_time=5

report-host=172.16.220.10

report-password=123456

report-port=3346

report-user=root

# relay-log

relay-log=PCSS-relay-bin

# Binary Logging.

log-bin="PCSS-bin"

# Error Logging.

log-error="PCSS.err"

# Server Id.

server-id=310

# Indicates how table and database names are stored on disk and used in MySQL.

# Value = 0: Table and database names are