Shulou(Shulou.com)06/01 Report--

-text- -

Scene:

I have some free time. I want to test Group Replication and MySQL Router~\ (≧▽≦) / ~

Conclusion:

Usability is good.

The performance is actually quite good, but the writing ability is still an old problem. Businesses that read more and write less can pay attention to Innodb Cluster.

Test environment:

MySQL 8.0.3-rc

MySQL Router 8.0

MySQL shell

Sysbench 1. 0 (add another test data later.)

Virtual machines:

4C24G, 2.00GHz x 5 (MySQL 8.0.3-rc)

4C24G, 2.40GHz x 4 (MySQL 8.0.3-rc)

8C24G, 2.40GHz x 1 (MySQL Router)

Build process:

1. The official source directly installs all packages related to MySQL, apt-get/yum

2. For the configuration of MySQL, in addition to the regular configuration, it is recommended to add the following section. The parameter at the beginning of loose indicates that if there is no group_replication plug-in, these configurations are ignored.

Click (here) to collapse or open

Report_host = 192.168.0.100 # # the GR plug-in will use this report_host to mark each MySQL instance in Group to ensure that the hostname of each MySQL instance is different, or that this report_host is different

Log_bin = / home/mysql/mysql-binlog # # must be enabled

Log_slave_updates = ON # # must be enabled

Master_info_repository = TABLE # # must use Table

Relay_log_info_repository = TABLE # # must use Table

Gtid_mode = ON # # GTID must be enabled

Enforce_gtid_consistency = ON # # must be enabled

Slave_preserve_commit_order = ON # # ensure that GR_applier repeats transactions in a consistent order

Binlog_checksum = NONE # # must be set to NONE

Transaction_write_set_extraction=XXHASH64 # # must be set to XXHASH64

# group_replication = ON # # enable the Group Replication plug-in, and then open it after installing the plug-in

Loose-group_replication_start_on_boot=off # # is disabled by default to prevent the automatic creation of a new Group after restart

Loose-group_replication_local_address= "192.168.0.100 13306" # # official tools use port 13306 as the port of the plug-in by default, which is set to 13306 to facilitate testing

Loose-group_replication_group_seeds= "192.168.0.100 13306192.168.1.100" 13306 "# # official tools do not seem to actively manage this parameter, manually add

Loose-group_replication_bootstrap_group=off # # is disabled by default to prevent the automatic creation of new Group

Loose-group_replication_ip_whitelist = '192.168.0.0 ip 24192.168.1.0 Universe 24' # it is recommended to manually add it before initialization to control which Group machines can be added to it.

PS: in the test, the performance of double 1 is really weak when the settings of innodb_flush_log_at_trx_commit = 2 and sync_binlog = 0 are used throughout the test. _ (: compare "∠) _

3. Start all MySQL instances and install plug-ins

Click (here) to collapse or open

INSTALL PLUGIN group_replication SONAME 'group_replication.so'

4. To create a user temp in mysql, the test chart is convenient. Just use ALL on *. *. In the official document, you can list the specific permissions that this user needs, as follows

Click (here) to collapse or open

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 performance_schema.* TO your_user@'%' WITH GRANT OPTION

GRANT SELECT ON *. * TO your_user@'%' WITH GRANT OPTION

GRANT SELECT, INSERT, UPDATE, DELETE ON mysql.* TO your_user@'%' WITH GRANT OPTION

Another thing to note is that it is officially recommended to modify / etc/hosts. In practice, point the localhost to the private network IP or the user with the same permission to create the temp@localhost. The official tool will use the @ localhost login method.

PS: with grant option is important because the tool creates a new mysql_route_xxxx user and a new database where meta is stored

4. Starting MySQL shell on Group Primary's machine is important because, as mentioned in point 3: official tools use the @ localhost login method

PS: it is recommended to use mysqlsh-- log-level=DEBUG3 to start mysql shell, and do not close wizard

5. After entering MySQL shell, you can choose whether to use javascript or python. Here you are used to python, using python, which is basically the same as js.

6. The dba.help () command can see all the supported commands, and these are the main commands used in the construction process:

Check_instance_configuration: check whether the parameter configuration of the instance meets the requirements for building Group

Configure_local_instance: persist the configuration parameters of the instance to the configuration file

Create_cluster: creating innodb cluster

Get_cluster: get the information of the innodb cluster to which this instance belongs

\ connect user name @ ip:port: log in to an instance, which is usually local

7. Use dba.check_instance_configuration ('temp@192.168.0.100:3306') to detect the configuration parameters. After setting the configuration parameters according to the first step, you should return OK directly. If there are any deficiencies, you can manually make up for them.

PS: this tool can automatically modify the parameters to meet the requirements. If you are interested, you can study it. Here we will stop nagging "(▔, ▔)".

8. The next step is to use create_cluster to create innodb cluster. Here are some commonly used setting information.

Briefly describe some parameters:

MultiMaster: if you want to create a multi-master cluster, you need to specify this parameter separately, otherwise a single master cluster is created by default (similar to the replication architecture of one master and multiple slaves)

AdoptFromGR: if you have manually set up the Group and need to create a new innodb cluster on this Group, you need to explicitly specify this parameter, and turn off the wizard mode. After turning off the wizard mode, you need to add an additional user name and password to create the cluster as the parameter. It is recommended to enable wizard, and use MySQL shell to build the innodb cluster throughout the process.

IpWhitelist: during testing, the tool may not manage the parameter seed, so even if you specify a whitelist when creating it, you cannot add a new instance to Group.

PS: in python mode, if you need to add parameters, write this

Click (here) to collapse or open

Cluster = cluster.create_cluster ('temp@192.168.0.100:3306', {' multiMaster':1,'adoptFromGR':1})

9. When wizard is enabled (this document is enabled by default), use the following command to create a Cluster:

Click (here) to collapse or open

Cluster = dba.create_cluster ('testCluster') if there is no problem, there will be the following prompt

10. After the cluster is created successfully, use the add_instance command to add all the instances to the group. In the official lecture, it is not supported to have more than 9 instances to form a Group. In fact, no more than 7 instances are better. The default setting of auto_increament is 7.

11. When you log in to the database, you will see that there is an extra library called mysql_innodb_cluster_metadata, which contains some information about cluster. If you are interested, you can take a look at it.

twelve。 Install MySQL Router on other virtual machines and then use bootstrap to automatically generate the configuration

Click (here) to collapse or open

Mysqlrouter-- bootstrap temp@192.168.0.100:3306-- directory / home/mysqlrouter/-- user=mysqlrouter will have start.sh and stop.sh in the corresponding directory to start and shut down mysqlrouter

PS: stop.sh may not be able to turn off mysqlrouter. Pay attention to the script problem.

13. The default is 6446 for reading and writing, and 6447 for reading only. At this time, you can test at will.

14. If you want to create a multi-master mode, you need to adjust two parameters

Click (here) to collapse or open

Group_replication_single_primary_mode = OFF

Group_replication_enforce_update_everywhere_checks = ON

PPS: MySQLRouter dynamically adjusts read-write and read-only connections based on meta data. For example, when Primary is down and meta has changed, MySQLRouter will discover and switch traffic to the new Primary in a very short time.

PPPPPS: performance test data to be sorted out

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.