MySQL 5.7 cluster setup

MySQL installation
InnoDB cluster setup

Install MySQL using the Zip package

Pay attention to

Microsoft Visual C ++ 2013 Redistributable Package is required for MySQL Community 5.7 Server to run on Windows. Before installing MySQL Community 5.7 Server, check whether the operating system supports it.

Download MySQL 5.7 Community Edition
Select an Installation position

Pay attention to

Select the installation location and ensure that there are no Spaces in the installation path as much as possible. The installation location is selected at C:\MySQL
Create configuration file my.ini and save my.ini in MySQL installation directory
```
Basedir =C:/MySQL/ datadir=C:/MySQL/dataCopy the code
```
Initialize the data directory manually using mysqld
- Description of initialization options
  - --initializeInitialization with this option will generate a random password--consoleOption to use
  - --initialize-insecureInitialization with this option will not generate any passwords
  - --defaults-fileSpecifies the configuration file to read when the mysql data directory is initialized. This option must be set inmysqldpost-commandThe first
- Initialize the data directory
  
  Pay attention to
  
  The initialize option generates a random password in the last line
```
mysqld --defaults-file=C:/MySQL/my.ini ----initialize --console
Copy the code
```
Start MySQL server
```
mysqld --console
Copy the code
```
Connect to the MySQL server
- Use the –initialize option to initialize the data directory. Log in to the server with a random password
```
mysql -u root -p
Copy the code
```
- Initialize the data directory with the –initialize-insecure option. Connect to the server with the –skip-password option
```
mysql -u root --skip-password
Copy the code
```
After the connection, change the root password
```
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'
Copy the code
```
Pay attention to

Normally connecting to the server using 127.0.0.1 will be resolved to the localhost account. You can ensure that you are connected to the MySQL server in the following ways
```
CREATE USER 'username'@'hostname' IDENTIFIED BY 'new_password'
Copy the code
```

InnoDB cluster setup

Introduction to InnoDB cluster

MySQL InnoDB cluster provides a high availability solution for MySQL. The MySQL Shell includes AdminAPI that allows you to easily configure and manage clusters of at least 3 MySQL server instances. Each MySQL server instance runs the MySQL Group Replication module, which provides InnoDB cluster data Replication mechanism and has built-in failover capabilities.

Pay attention to

InnoDB Cluster does not support MySQL NDB Cluster. NDB Cluster relies on the NDB storage engine, and many NDB Cluster programs are not shipped with MySQL Server 5.7. In addition, mysqld provided by MySQL Server 5.7 cannot be used with NDB Cluster.

The following figure shows what technologies MySQL InnoDB Cluster uses to work together

Using AdminAPI

The MySQL Shell provides AdminAPI functionality, through which dba global variables and methods are provided to enable you to deploy, configure, and manage InnoDB clusters. For example, use the dba.createcluster () method to create an InnoDB cluster.

Pay attention to

The MySQL Shell allows you to connect to the server over sockets, but AdminAPI requires a TCP connection to the server instance. Do not use socket-based connections in AdminAPI.

The MySQL Shell provides online help for the AdminAPI. To list all available DBA commands, use the dba.help() method. For help with a particular method, use the format object.help(‘ methodName ‘).

Create the InnoDB Cluster
- Sandbox deployment: If you test InnoDB Clusters prior to production deployment, the Sandbox feature allows you to quickly create clusters on local machines.
- Production deployment: If you want to use InnoDB Cluster in a production environment, you need to configure all MySQL Server instances and then deploy the Server to a computer.
Pay attention to

Sandbox Deployment is not suitable for full production deployment.
InnoDB Cluster Cluster environment requirements
- InnoDB clusters use the Group Replication module, and you must ensure that your servers meet the same requirements. Instance validation AdminAPI provides the dba. CheckInstanceConfiguration () whether meet the requirements of group of replication, the dba. ConfigureLocalInstance instance configuration () in order to satisfy the requirement set of copy.
  
  Pay attention to
  
  With Sandbox Deployment, the instance will be configured to automatically meet these conditions.
  
  Group Replication Basic requirements
  - InnoDB Storage Engine, data must use InnoDB transaction Storage Engine.
  - Primary Keys, to ensure that each table replicated using a group must have a defined Primary key or its equivalent.
  - IPv4 Network, the MySQL Group Replication module supports only IPv4 networks.
  - Network Performance, group replication requires server instances to be in a cluster environment very close to each other, because group replication is greatly affected by Network latency and bandwidth.
  Server Configuration
  - Binary Log Active--log-bin[=log_file_name].
  - Slave Updates Logged, the server needs to update binary logs through the group replication application,--log-slave-updates.
  - Binary Log Row Format (Binary Log Row Format)--binlog-format=row
  - Use Global Transaction Identifiers to accurately track transactions committed On each server instance so that you can infer which servers are executing transactions that might conflict with transactions committed elsewhere.--gtid-mode=on.
  - Replication Information Repositories, set--master-info-repository=TABLEand--relay-log-info-repository=TABLE. For group replication, information about the master and slave nodes is written to the system tablemysql.slave_master_infoandmysql.slave_relay_log_infoSystem tables. This approach ensures that the group replication plug-in has consistent replicability and transaction management capabilities for copying metadata.
  - Transaction Write Set Extraction, Set--transaction-write-set-extraction=XXHASH64In order to collect rows and record them to the binary log, the server also collects writes. The write set, based on the primary key of each row, is a simplified and compact view of tags that uniquely identify the row that has changed. This tag is then used to detect conflicts.
  - Multithreaded Appliers. Group replication members can be configured for Multithreaded applications so that transactions can be applied in parallel. Set up the--slave-parallel-workers=N(N identifies the number of concurrent application threads),--slave-preserve-commit-order=1and--slave-parallel-type=LOGICAL_CLOCK. Group replication relies on a consistent mechanism for accepting and applying committed transactions in the same order around all participating members, and therefore must be set up--slave-preserve-commit-order=1Ensure that the final commit of the parallel transaction is in the same order as the original transaction. Finally, in order to determine which transactions can be executed in parallel, the slave server must contain information about the parent that generated the transaction.
- MySQL Shell is used to configure InnoDB Cluster. The configuration script requires access to Python 2.7.
Installation method

How users install InnoDB Cluster depends on the deployment mode used. For Sandbox Deployment, the cluster components are installed on a single machine. The Sandbox Deployment deployment is a local deployment of a single machine, so the installation only needs to be done once on the local machine. For production deployments, install components on each computer.
Sandbox deployment

You can create and manage InnoDB clusters using the MySQL Shell that includes AminAPI. You can use the Sandbox Deployment InnoDB Cluster as the best way to explore clusters prior to production deployment. The MySQL Shell comes with Sandbox Deployment, which can be used to create a properly configured InnoDB Cluster Cluster.

Pay attention to

Sandbox Deployment is only suitable for deployment and testing on a local machine. In a production environment, MySQL Server instances are deployed to various hosts on the network.

Deploy the Sandbox instance

MySQL dba provides a global variable in the Shell provides AdminAPI Sandbox deployment function, you can use the dba. DeploySandboxInstance () to create and deploy the Sandbox instance.

Start the MySQL Shell
```
> mysqlsh
Copy the code
```
In addition to the SQL schema, the MySQL Shell provides two scripting language schemas, Javascript and Python. In this example, the Sandbox InnoDB Cluster is created mainly through Javascript mode. In the MySQL Shell, modes can be toggled with \js, \py, and \ SQL commands, which default to Javascript mode.
```
msyql-js> dba.deploySandboxInstance(3310)
Copy the code
```
Pay attention to

In Javascript and Python mode, a semicolon is not required to terminate a command.

The parameter passed to deploySandboxInstance() is the MySQL port number and the MySQL instance listens for connections from port 3310. After the command is executed, you will be prompted to enter the root password of the instance.

Pay attention to

Each instance has its own password. It is recommended that passwords be set as simple and identical as possible in a test environment, but in a production deployment use a different password for each instance whenever possible.

To deploy additional Sandbox instances, repeat the command above:
```
msyql-js> dba.deploySandboxInstance(3320)
msyql-js> dba.deploySandboxInstance(3330)
Copy the code
```
Create Sandbox InnoDB Cluster

Next connect to the MySQL Server master Server (this Server is the instance that contains other instances to replicate data) to create the InnoDB Cluster. In this example, the Sandbox instance is empty, so you can select any instance.

Use the MySQL Shell to connect to the primary instance, in this case the instance of port 3310:
```
mysql-js> \connect root@localhost:3310
Copy the code
```
\connect is a shortcut to the shell.connect() method in MySQL Shell:
```
mysql-js> shell.connect('root@localhost:3310')
Copy the code
```
Once connected, AdminAPI can write to the configuration file for this instance. This is different from using a production deployment, where you need to connect to a remote instance and run the MySQL Shell application locally on the instance before AdminAPI can write to the instance’s configuration file.

Create an InnoDB cluster using the dba.createcluster () method and make the currently connected instance the primary server:
```
mysql-js> var cluster = dba.createCluster('testCluster')
Copy the code
```
CreateCluster () configures InnoDB Cluster metadata to the selected instance and makes the currently connected instance the primary instance. The createCluster() method returns the created cluster, which in the above example was assigned to the Cluster variable. CreateCluster () takes the name of the InnoDB Cluster.

Add an instance to an InnoDB Cluster

Transactions executed by the master instance are re-executed when the slave instance is added.

Because the sample master server data is empty. In the production environment, the active instance has a lot of data. You are advised to use database backup software to back up the existing data and restore the data on other instances to reduce the delay caused by slave database data replication.

Add a second instance InnoDB Cluster:
```
mysql-js> cluster.addInstance('root@localhost:3320')
Copy the code
```
You are prompted to enter the password of user root.

Add a third instance
```
mysql-js> cluster.addInstance('root@localhost:3330')
Copy the code
```
prompt

For instance Sandbox instance, only localhost instance can be specified.

Storage Cluster Configuration

To add a Sandbox instance to a cluster, the configuration required for an InnoDB cluster must be retained in the configuration file for each instance.

Use the dba. ConfigureLocalInstance (‘ instance) instance configuration can be saved to each instance of the configuration file.
```
mysql-js> \connect instance
mysql-js> dba.configureLocalInstance('instance')
Copy the code
```
Pay attention to

If the dba. ConfigureLocalInstance () are not performed successfully, the instance will not be able to rejoin the cluster after the next reboot.

Repeat the preceding steps to ensure that the configuration of each Sandbox instance is saved. For this example, you need to store the configuration on port 3310, 3320, and 3330.
```
mysql-js> \connect root@localhost:port_number)
mysql-js> dba.configureLocalInstance('root@localhost:port_number)
Copy the code
```
To check whether a cluster has been created, use the status() function of the cluster instance.

After deploying the cluster, you can configure the MySQL Router to improve high availability.

Production deployment

In a production environment, MySQL server instances that make up an InnoDB cluster run on multiple hosts as part of a network, rather than on a single machine.

The following diagram illustrates the scenario you use in this section:

Pay attention to

With the Sandbox deployment, for production deployment, you must connect each computer and use MySQL Shell provide AdminAPI dba. ConfigureLocalInstance () to save the configuration of each instance. You can also control access to the cluster through the MySQL Shell.

User permissions

The user account of the user management instance may not be root, but must be assigned full read permission by the MySQL administrator and written into the metadata table of the cluster (SUPER, GRANT OPTION, CREATE, DROP, etc.). Give your_user the permissions needed to manage InnoDB clusters:

GRANT ALL PRIVILEGES ON mysql_innodb_cluster_metadata.* TO your_user@'%' WITH GRANT OPTION;
GRANT RELOAD, SHUTDOWN, PROCESS, FILE, SUPER, REPLICATION SLAVE, REPLICATION CLIENT, \
CREATE USER ON *.* TO your_user@'%' WITH GRANT OPTION;
GRANT SELECT ON *.* TO your_user@'%' WITH GRANT OPTION;
Copy the code

If you only need to read, you can use an account with more restricted privileges. Give your_user the right to monitor InnoDB clusters:

GRANT SELECT ON mysql_innodb_cluster_metadata.* TO your_user@'%';
GRANT SELECT ON performance_schema.global_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_coordinator TO your_user@'%';
GRANT SELECT ON performance_schema.replication_applier_status_by_worker TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_configuration TO your_user@'%';
GRANT SELECT ON performance_schema.replication_connection_status TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_member_stats TO your_user@'%';
GRANT SELECT ON performance_schema.replication_group_members TO your_user@'%';
GRANT SELECT ON performance_schema.threads TO your_user@'%' WITH GRANT OPTION;
Copy the code

Configuring host Names

The production instances that make up the cluster run on different machines, so each machine must have a unique hostname and be able to resolve the hostname of the other machines running the server instance in the cluster. If this is not the case, you can

Configure each computer to map the IP of each other to the host name.
Configuring the DNS Service
willreport_hostEach instance is configured as an externally accessible address

To verify that the host name of the MySQL server is correctly configured, run the following query to see how the instance reports its address to another server and tries to connect to the MySQL server from another host using the returned address:

SELECT coalesce(@@report_host, @@hostname);
Copy the code

Detailed log

Configuring detailed logging for MySQL Shell can help you find and resolve any problems that occur when preparing server instances as part of an InnoDB cluster when using a production deployment. Use the –log-level option to set:

ps> mysqlsh --log-level=DEBUG3
Copy the code

In addition to enabling MySQL Shell logging, you can see the output after each API call:

mysql-js> dba.verbose=2
Copy the code

This is the most complete output of the AdminAPI call. Available output options:

0 or OFF indicates the recommended value and the default value when the fault is not rectified.
Each call to 1 or ON prints the details
2 will debug content complete data.

Checking instance Configuration

Before the production server is deployed, you can use the dba. CheckInstanceConfiguration () function checks whether the MySQL in each instance meet InnoDB cluster configuration, the operation will not check any data on the instance, the following presentation data:

mysql-js> dba.checkInstanceConfiguration('ic@ic-1:3306')

Please provide the password for 'ic@ic-1:3306':
Validating instance...

The instance 'ic-1:3306' is not valid forCluster usage. The following issues were encountered: - Some configuration options need to be fixed. +----------------------------------+---------------+----------------+--------------------------------------------------+  | Variable | Current Value | Required Value | Note | +----------------------------------+---------------+----------------+--------------------------------------------------+  | binlog_checksum | CRC32 | NONE | Update the server variable or restart the server | | enforce_gtid_consistency | OFF | ON | Restart the server | | gtid_mode | OFF | ON | Restart the server | | log_bin | 0 | 1 | Restart the server | | log_slave_updates | 0 | ON | Restart the server | | master_info_repository | FILE | TABLE | Restart the server | | relay_log_info_repository | FILE | TABLE | Restart the server | | transaction_write_set_extraction | OFF | XXHASH64 | Restart the server | +----------------------------------+---------------+----------------+--------------------------------------------------+  Please fix these issues , restart the server and try again. {"config_errors": [{"action": "server_update"."current": "CRC32"."option": "binlog_checksum"."required": "NONE"
    },
    {
      "action": "restart"."current": "OFF"."option": "enforce_gtid_consistency"."required": "ON"
    },
    {
      "action": "restart"."current": "OFF"."option": "gtid_mode"."required": "ON"
    },
    {
      "action": "restart"."current": "0"."option": "log_bin"."required": "1"
    },
    {
      "action": "restart"."current": "0"."option": "log_slave_updates"."required": "ON"
    },
    {
      "action": "restart"."current": "FILE"."option": "master_info_repository"."required": "TABLE"
    },
    {
      "action": "restart"."current": "FILE"."option": "relay_log_info_repository"."required": "TABLE"
    },
    {
      "action": "restart"."current": "OFF"."option": "transaction_write_set_extraction"."required": "XXHASH64"}]."errors": []."restart_required": true."status": "error"
}
mysql-js>
Copy the code

Repeat this process for each instance in the planned cluster to check that the instance meets the conditions for cluster operation.

Configure the instance

You can according to the dba. CheckInstanceConfiguration () inspection report above configuration option in the configuration file to ensure the database service conform to the requirements of the InnoDB cluster. You can also through the dba. ConfigureLocalInstance () automatic configuration instance in accordance with the requirements of the cluster. Sample data is as follows:

mysql-js> dba.configureLocalInstance('root@localhost:3306')

Please provide the password for 'root@localhost:3306':

Please specify the path to the MySQL configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf
Validating instance...

The configuration has been updated but it is required to restart the server.
{
  "config_errors": [{"action": "restart"."current": "OFF"."option": "enforce_gtid_consistency"."required": "ON"
    },
    {
      "action": "restart"."current": "OFF"."option": "gtid_mode"."required": "ON"
      },
    {
      "action": "restart"."current": "0"."option": "log_bin"."required": "1"
    },
    {
      "action": "restart"."current": "0"."option": "log_slave_updates"."required": "ON"
    },
    {
      "action": "restart"."current": "FILE"."option": "master_info_repository"."required": "TABLE"
    },
    {
      "action": "restart"."current": "FILE"."option": "relay_log_info_repository"."required": "TABLE"
    },
    {
      "action": "restart"."current": "OFF"."option": "transaction_write_set_extraction"."required": "XXHASH64"}]."errors": []."restart_required": true."status": "error"
}
mysql-js>
Copy the code

Create the cluster

Connect to the primary database server using the MySQL Shell and create a cluster on that server.

shell> mysqlsh --uri username@hostname:port
mysql-js> var cluster = dba.createCluster('prodCluster')

      A new InnoDB cluster will be created on instance 'ic@ic-1:3306'.

      Creating InnoDB cluster 'prodCluster' on 'ic@ic-1:3306'. Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are neededfor the cluster to be able to withstand up to
      one server failure.
Copy the code

Add additional instances to InnoDB cluster:

mysql-js> cluster.addInstance('username@hostname:port')
Copy the code

Pay attention to

In the second phase, the server instance has been added to the cluster, but the metadata changes to the InnoDB cluster are only valid for the current connection. You must use the dba. In no instance configureLocalInstance () to ensure the instance configuration saved to the server, so that the next reboot after use.

Group replication deployment is used

If your server instance already has group replication capability and you want to use it to create clusters, pass the adoptFromGR option to dBA.createcluster (). The InnoDB cluster created matches whether the replication group is running on a single or multiple primary database.

prompt

If the group replication instance contains tables created by the MyISAM engine, all such tables are converted to the InnoDB storage engine to create the cluster
```
mysql-js> var cluster = dba.createCluster('prodCluster', {adoptFromGR: true});

A new InnoDB cluster will be created on instance 'root@gr-member-2:3306'.

Creating InnoDB cluster 'prodCluster' on 'root@gr-member-2:3306'. Adding Seed Instance... Cluster successfully created. Use cluster.addInstance() to add MySQL instances. At least 3 instances are neededfor the cluster to be able to withstand up to
one server failure.
Copy the code
```

Install MySQL using the Zip package

InnoDB cluster setup

Using AdminAPI

Group Replication Basic requirements

Server Configuration

Deploy the Sandbox instance

Create Sandbox InnoDB Cluster

Add an instance to an InnoDB Cluster

Storage Cluster Configuration

User permissions

Configuring host Names

Detailed log

Checking instance Configuration

Configure the instance

Create the cluster

Related Posts

After graduation in 985, I worked in a big factory for 2 years and was laid off. I set foot on the road of interview again and got an offer from Tencent

Golang SOAP client experience summary

MySQL kills 15 questions, which questions can you stick to?