Installation and deployment of MySQL Router
01 Introduction to MySQL RouterIn the previous article, we talked about the basic concept of ReplicaSet, the MySQL Shell tool, and how to use MySQL Shell to build Innodb Replicaset. Today we will look at MySQL Router, another important component in the InnoDB ReplicaSet deployment process. What is MySQL Router?To better understand Innodb Replicaset, we move the previous picture here, as follows:
From the above figure, it is not difficult to see that MySQL Router is the entrance for application connection. Its existence makes the underlying architecture transparent to the application. The application only needs to connect to MySQL Router to interact with the underlying database, and the master-slave architecture of the database is recorded in the original information of MySQL Router. Version MySQL Router is a lightweight, high-performance middleware officially developed by MySQL. It is between applications and MySQL Server. Applications communicate by connecting to MySQL Router and the underlying MySQL Server. It is transparent to the applications. The official documentation recommends using it with MySQL 8.0 or MySQL 5.7 Server. The latest version is MySQL Router 8. If you have used MySQL Router 2.0 or 2.1, it is strongly recommended to upgrade to MySQL Router 8. What are the features of MySQL Router? 1. Transparent to applications. For MySQL Router to be transparent to the application, it must connect to the underlying MySQL and know which node is currently the Primary, so that it can perform failover when a failure occurs. 2. Usage scenarios. Based on this feature, it can be used in Innodb Cluster, Innodb Replicaset or MGR environment. 3. MySQL Router will keep a cache list of online MySQL instances or the topology of the configured Innodb Cluster. In addition, when it is started, this information will be obtained from the MySQL Router configuration table. 4. To ensure that the metadata in the cache can be updated immediately, MySQL Router needs to ensure that it can communicate with at least one normal node in the cluster. It will obtain the original information and real-time status of the database from the Performance_schema table of the current instance. 5. When the node in the cluster that communicates with MySQL Router is shut down, MySQL Router will try to access other nodes in the cluster. And re-obtain relevant metadata information. 02 Installation and deployment of MySQL RouterTo achieve better performance, MySQL Router is usually deployed together with the application. This is usually based on the following considerations: 1. You can connect to MySQL Router via socket instead of tcp/ip method 2. Reduced network latency 3. You can configure a specified account to access the database, such as myapp@'host' instead of a full-network account like myapp@'%', which helps improve security 4. Generally, application servers are easier to scale than database servers. The official deployment architecture diagram is as follows:
Download and installation process: 1. Log in to the official website directly to download the corresponding version of MySQL Router. https://downloads.mysql.com/archives/router/ 2. After downloading, decompress it. Taking 8.0.20 as an example, since it is a tar.xz file, the decompression command is as follows: xz -d xxx.tar.xz (decompress into tar format) tar xvf xxx.tar (that's it) Next is the initialization process. During the initialization, our MySQL Server needs to be deployed. In the previous article, we have deployed an Innodb Replicaset architecture with one master and one slave. The IP addresses are: 192.168.1.10 5607 Primary 192.168.1.20 5607 Secondary Initialization process: 1. Initialize MySQL Router using the initialization command:
mysqlrouter
--bootstrap superdba@'10.13.3.129':5607
--directory /data1/yazhou5/mysql/mysql-router
--conf-use-sockets
--account routerfriend
--account-create alwaysHere, we need to explain several of the parameters: --bootstrap represents the bootstrap instance, followed by a URL for connection information; --directory represents the generated configuration directory --conf-use-sockets represents whether to enable socket connection (whether to generate socket file) --account indicates the account used by MySQL Router to connect to MySQL Server after initialization --account-create represents the account creation strategy. Always means that the bootstrap operation is performed only when the account does not exist. After typing this command, the error message returned is as follows: Error: You are bootstraping as a superuser. This will make all the result files (config etc.) privately owned by the superuser. Please use --user=username option to specify the user that will be running the router. Use --user=root if this really should be the superuser. The system detects that we are using the root account to initialize MySQL Router, and prompts us that if we use root operations, we need to add --user=root at the end 2. After adding --user=root, re-execute the command, the result is as follows: [root mysql-router]# /usr/local/mysql-router-8.0.20/bin/mysqlrouter --bootstrap [email protected]:5607 --directory /data1/yazhou5/mysql/mysql-router --conf-use-sockets --account routerfriend --account-create always --user=root --force Please enter MySQL password for superdba: # Enter the superdba account password we know here# Bootstrapping MySQL Router instance at '/data1/yazhou5/mysql/mysql-router'... Please enter MySQL password for routerfriend: # Create new account password here - Creating account(s) - 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 /data1/yazhou5/mysql/mysql-router/mysqlrouter.conf # MySQL Router configured for the InnoDB ReplicaSet 'yeyz_test' After this MySQL Router has been started with the generated configuration $ /usr/local/mysql-router-8.0.20/bin/mysqlrouter -c /data1/yazhou5/mysql/mysql-router/mysqlrouter.conf the cluster 'yeyz_test' can be reached by connecting to: ## MySQL Classic protocol - Read/Write Connections: localhost:6446, /data1/yazhou5/mysql/mysql-router/mysql.sock - Read/Only Connections: localhost:6447, /data1/yazhou5/mysql/mysql-router/mysqlro.sock ## MySQL X protocol - Read/Write Connections: localhost:64460, /data1/yazhou5/mysql/mysql-router/mysqlx.sock - Read/Only Connections: localhost:64470, /data1/yazhou5/mysql/mysql-router/mysqlxro.sock As you can see, after prompting us to enter the password twice, the bootstrap operation is considered successful. 3. At this time, we enter the --directory directory specified in the parameter and view the generated initialization file, and we can see: drwx------ 2 root root 4096 Apr 12 23:15 data drwx------ 2 root root 4096 Apr 12 23:15 log -rw------ 1 root root 1532 Apr 12 23:15 mysqlrouter.conf -rw------ 1 root root 104 Apr 12 23:15 mysqlrouter.key drwx------ 2 root root 4096 Apr 12 23:15 run -rwx------ 1 root root 353 Apr 12 23:15 start.sh -rwx------ 1 root root 209 Apr 12 23:15 stop.sh Some configuration files and start and stop scripts are generated. Let's open the configuration file mysqlrouter.conf and take a look at the contents: # File automatically generated during MySQL Router bootstrap [DEFAULT] user=root logging_folder=/data1/yazhou5/mysql/mysql-router/log runtime_folder=/data1/yazhou5/mysql/mysql-router/run data_folder=/data1/yazhou5/mysql/mysql-router/data keyring_path=/data1/yazhou5/mysql/mysql-router/data/keyring master_key_path=/data1/yazhou5/mysql/mysql-router/mysqlrouter.key connect_timeout=15 read_timeout=30 dynamic_state=/data1/yazhou5/mysql/mysql-router/data/state.json [logger] level = INFO [metadata_cache:yeyz_test] cluster_type=rs router_id=1 user=routerfriend metadata_cluster=yeyz_test ttl=0.5 auth_cache_ttl=-1 auth_cache_refresh_interval=2 [routing:yeyz_test_rw] bind_address=0.0.0.0 bind_port=6446 socket=/data1/yazhou5/mysql/mysql-router/mysql.sock destinations=metadata-cache://yeyz_test/?role=PRIMARY routing_strategy=first-available protocol=classic [routing:yeyz_test_ro] bind_address=0.0.0.0 bind_port=6447 socket=/data1/yazhou5/mysql/mysql-router/mysqlro.sock destinations=metadata-cache://yeyz_test/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=classic [routing:yeyz_test_x_rw] bind_address=0.0.0.0 bind_port=64460 socket=/data1/yazhou5/mysql/mysql-router/mysqlx.sock destinations=metadata-cache://yeyz_test/?role=PRIMARY routing_strategy=first-available protocol=x [routing:yeyz_test_x_ro] bind_address=0.0.0.0 bind_port=64470 socket=/data1/yazhou5/mysql/mysql-router/mysqlxro.sock destinations=metadata-cache://yeyz_test/?role=SECONDARY routing_strategy=round-robin-with-fallback protocol=x 4. Analyzing the generated configuration file, it is not difficult to find that MySQL Router is configured with 4 ports, namely 6446, 6447, 64460, 64470 and the corresponding socket files. Of course, we can change the default port and socket by configuring some parameters, for example: --conf-use-sockets: (Optional) Enable UNIX domain sockets for all four connection types. --conf-skip-tcp: (Optional) Disable the TCP port. If you only want to use sockets, you can pass this option via --conf-use-sockets. --conf-base-port: (Optional) Change the port range instead of using the default ports. The default is 6446. --conf-bind-address: (Optional) Change the bind_address value for each route. 5. Use the command to start MySQL Router locally and specify the locally generated configuration file. The command is as follows: [root@mysql-router]# /usr/local/mysql-router-8.0.20/bin/mysqlrouter -c /data1/yazhou5/mysql/mysql-router/mysqlrouter.conf & After startup, we use the routerfriend account we just created and the read-write port 6446 to connect to MySQL Router: [root@mysql-router]# mysql -u routerfriend -h 127.0.0.1 -P 6446 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 95696 Server version: 8.0.19 MySQL Community Server - GPL Copyright (c) 2000, 2013, 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 '\h' for help. Type '\c' to clear the current input statement. [email protected] [(none)] 23:42:00> [email protected] [(none)] 23:42:01>select @@port; +--------+ | @@port | +--------+ | 5607 | +--------+ 1 row in set (0.00 sec) After we connect to MySQL Router through port 6446, we execute the select @@port command in MySQL Router to view the current port number information. We can see that the return value is 5607, indicating that MySQL Router has helped us route to the underlying MySQL Server. The routerfriend account may not have sufficient permissions. We can also use the superdba high-privilege account to connect to mysqlrouter, so that we can read and write the database tables in the MySQL Server. 03 View the meta information of MySQL RouterAfter MySQL Router is built, you can view the table information in the meta-information library mysql_innodb_cluster_metadata, including the cluster table, router table, and instances table, which correspond to the following: [email protected] [mysql_innodb_cluster_metadata] 23:51:20>select * from instances; +-------------+--------------------------------------+--------------------+--------------------------------------+--------------------+----------------------------------------+------------+-------------+ | instance_id | cluster_id | address | mysql_server_uuid | instance_name | addresses | attributes | description | +-------------+--------------------------------------+--------------------+--------------------------------------+--------------------+----------------------------------------+------------+-------------+ | 1 | 94d5f935-990e-11eb-8832-fa163ebd2444 | 192.168.1.10:5607 | 0609f966-690f-11eb-bd89-fa163ebd2444 | 192.168.1.10:5607 | {"mysqlClassic": "192.168.1.10:5607"} | {} | NULL | | 2 | 94d5f935-990e-11eb-8832-fa163ebd2444 | 192.168.1.20:5607 | c6ba0bf0-6d4d-11eb-aa4b-b00875209c1c | 192.168.1.20:5607 | {"mysqlClassic": "192.168.1.20:5607"} | {} | NULL | +-------------+--------------------------------------+--------------------+--------------------------------------+--------------------+----------------------------------------+------------+-------------+ 2 rows in set (0.00 sec) [email protected] [mysql_innodb_cluster_metadata] 23:51:30> [email protected] [mysql_innodb_cluster_metadata] 23:51:30>select * from clusters; +--------------------------------------+--------------+--------------------+---------+------------------------------------------------+--------------+--------------+----------------+ | cluster_id | cluster_name | description | options | attributes | cluster_type | primary_mode | router_options | +--------------------------------------+--------------+--------------------+---------+------------------------------------------------+--------------+--------------+----------------+ | 94d5f935-990e-11eb-8832-fa163ebd2444 | yeyz_test | Default ReplicaSet | NULL | {"adopted": 0, "opt_gtidSetIsComplete": false} | ar | pm | NULL | +--------------------------------------+--------------+--------------------+---------+------------------------------------------------+--------------+--------------+----------------+ 1 row in set (0.00 sec) [email protected] [mysql_innodb_cluster_metadata] 23:51:57> [email protected] [mysql_innodb_cluster_metadata] 23:51:58>select * from routers; +-----------+-------------+--------------+-------------+---------+---------+---------------------+--------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+ | router_id | router_name | product_name | address | version | last_check_in | attributes | cluster_id | options | +-----------+-------------+--------------+-------------+---------+---------+---------------------+--------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+ | 1 | | MySQL Router | 10.13.3.129 | 8.0.20 | 2021-04-12 23:52:29 | {"ROEndpoint": "6447", "RWEndpoint": "6446", "ROXEndpoint": "64470", "RWXEndpoint": "64460", "MetadataUser": "routerfriend"} | 94d5f935-990e-11eb-8832-fa163ebd2444 | NULL | +-----------+-------------+--------------+-------------+---------+---------+---------------------+--------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+ 1 row in set (0.00 sec) You can also view the current primary node information from the table. If the primary_master field is 1, it is the primary node. [email protected] [mysql_innodb_cluster_metadata] 23:52:29>select * from async_cluster_members; +--------------------------------------+---------+-------------+--------------------+----------------+------------------------------------------------------------------------------------------------------------------+ | cluster_id | view_id | instance_id | master_instance_id | primary_master | attributes | +--------------------------------------+---------+-------------+--------------------+----------------+------------------------------------------------------------------------------------------------------------------+ | 94d5f935-990e-11eb-8832-fa163ebd2444 | 2 | 1 | NULL | 1 | {"instance.address": "192.168.1.10:5607", "instance.mysql_server_uuid": "0609f966-690f-11eb-bd89-fa163ebd2444"} | | 94d5f935-990e-11eb-8832-fa163ebd2444 | 3 | 1 | NULL | 1 | {"instance.address": "192.168.1.10:5607", "instance.mysql_server_uuid": "0609f966-690f-11eb-bd89-fa163ebd2444"} | | 94d5f935-990e-11eb-8832-fa163ebd2444 | 3 | 2 | 1 | 0 | {"instance.address": "192.168.1.20:5607", "instance.mysql_server_uuid": "c6ba0bf0-6d4d-11eb-aa4b-b00875209c1c"} | +--------------------------------------+---------+-------------+--------------------+----------------+------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.01 sec) The above is the detailed content of MySQL Router installation and deployment. For more information about MySQL Router, please pay attention to other related articles on 123WORDPRESS.COM! You may also be interested in:
|
<<: Vue large screen data display example
>>: CSS easily implements fixed-ratio block-level containers
Recommend
setup+ref+reactive implements vue3 responsiveness
Setup is used to write combined APIs. The interna...
Detailed explanation of using INS and DEL to mark document changes
ins and del were introduced in HTML 4.0 to help au...
Summary of common knowledge points required for MySQL
Table of contents Primary key constraint Unique p...
HTML table markup tutorial (39): The bright border color attribute of the header BORDERCOLORLIGHT
In the table header, you can define the light bor...
How to solve the problem of command failure caused by overwriting the original PATH and prompting command not found
A colleague asked me to help him figure out why m...
Simplify complex website navigation
<br />Navigation design is one of the main t...
Detailed explanation of the installation and configuration process of mysql8.018 on linux
Installation introduction under Windows: Check ou...
Detailed explanation of the properties and instance usage of hasOwnProperty in js
1. js will not protect hasOwnProperty from being ...
How to create your own Docker image and upload it to Dockerhub
1. First register your own dockerhub account, reg...
Nginx request limit configuration method
Nginx is a powerful, high-performance web and rev...
Vue Getting Started with Weather Forecast
This article example shares the specific code of ...
Detailed explanation of the use principle and calculation method of the umask command under Linux
Table of contents umask Umask usage principle 1. ...
Solution to MySQL startup successfully but not listening to the port
Problem Description MySQL is started successfully...
Vue.js style layout Flutter business development common skills
Correspondence between flutter and css in shadow ...
Javascript combined with Vue to achieve automatic path finding for any maze image
Table of contents Preface Two-dimensional array, ...