Detailed explanation of the configuration method of MySQL master-slave replication read-write separation
|
1. Description Earlier we talked about the installation and configuration of MySQL, the use of MySQL statements, and the backup and recovery of MySQL data; this time we will introduce MySQL's master-slave replication, read-write separation, and high availability MHA; The environment is as follows: master:CentOS7_x64 mysql5.721 172.16.3.175 db1 Architecture diagram:
illustrate: When configuring the test, the firewall header and selinux security policy were turned off for convenience; 2. Master-slave replication configuration One master database, N slave nodes; two threads are started on the slave node, and binlog logs are copied from the master database node to the local through the Slave_IO_Running thread and the account with permissions on the master node, and binlog logs can be executed locally through the Slave_SQL_Running thread to achieve master-slave node content synchronization; Master configuration: egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf [mysqld] datadir=/data1/mysqldb socket=/tmp/mysql.sock key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 query_cache_limit = 1M query_cache_size = 64M query_cache_type = 1 symbolic-links=0 innodb_file_per_table=ON skip_name_resolve=ON server-id = 1 log_bin = /data1/mysqldb/mysql-bin.log [mysqld_safe] log-error=/usr/local/mysql/logs/error.log pid-file=/data1/mysqldb/mysql.pid !includedir /usr/local/mysql/etc/my.cnf.d Create a slave node synchronization account: mysql > grant replication client,replication slave on *.* to 'repluser'@'172.16.3.%' identified by 'replpass'; mysql > flush privileges; mysql >show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ |mysql-bin.000001 | 622 | Slave node: egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf [mysqld] datadir=/data1/mysqldb socket=/data1/mysqldb/mysql.sock key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 query_cache_limit = 1M query_cache_size = 64M query_cache_type = 1 symbolic-links=0 innodb_file_per_table=ON skip_name_resolve=ON server-id = 11 #Slave node ID. Each slave node has a different relay_log = relay-log read_only=ON [mysqld_safe] log-error=/usr/local/mysql/log/error.log pid-file=/var/run/mysql/mysql.pid !includedir /usr/local/mysql/etc/my.cnf.d Start the mysql database Note: The server-id values of the two slave nodes are different; everything else is the same; therefore, only one configuration file is displayed on the slave node; Both slaves must be synchronized and started
mysql > CHANGE MASTER TO MASTER_HOST="172.16.3.175",MASTER_USER="repluser",MASTER_PASSWORD="replpass",MASTER_PORT=3306,MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=622;
mysql > start slave; #Start slave node ()
#View the slave node statusmysql > SHOW SLAVE STATUS;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.3.175 #Master nodeMaster_User: repluser #Synchronization accountMaster_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000001
Read_Master_Log_Pos: 622
Relay_Log_File: relay-log.000001
Relay_Log_Pos: 582
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #Synchronization thread is normal Slave_SQL_Running: Yes #Local write thread is normal Replicate_Do_DB: #Synchronization filter is empty (you can synchronize only one or some libraries)
Replicate_Ignore_DB: #Unsynchronized database Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0 #The last synchronization error 0 indicates normal synchronization Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 622
Relay_Log_Space: 615
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 57017c43-36e3-11e8-ac76-080027393fc7
Master_Info_File: /data1/mysqldb/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay logs; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
Test master-slave synchronization Import test data on the master; modify the data and check whether the data in the slave is consistent; [root@db1 ~]# mysql < Testdb.sql Log in to the database [root@db1 ~]# mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ study | +--------------------+ 5 rows in set (0.00 sec) ###study test database imported successfullymysql> use study; Database changed mysql> show tables; +-----------------+ | Tables_in_study | +-----------------+ | class | | course | | part | | score | | student | |tb31| |tb32| | teacher | | test1 | | test2 | | user_info | +-----------------+ 11 rows in set (0.00 sec) #Delete the test1 test2 tables Slave view from the node mysql> show tables; +-----------------+ | Tables_in_study | +-----------------+ | class | | course | | part | | score | | student | |tb31| |tb32| | teacher | | user_info | +-----------------+ 9 rows in set (0.00 sec) The data can be synchronized normally. Note that the master-slave synchronization only needs to be started manually for the first time. After that, it will be automatically started with the MySQL service. The master-slave synchronization architecture only facilitates data synchronization. If there is no third-party tool involved, you need to do it in the program to achieve read-write separation, which is inevitable. If an error occurs, you need to synchronize the data manually. Here, proxysql is used to do read-write separation. 3. ProxySQL read-write separation The master-slave replication configuration has been completed above; however, this is only a basic configuration, plus a proxysql to achieve MySQL read-write separation. Proxysql is similar to the seven-layer proxy routing function of haproxy and supports the database proxy of MySQL protocol; it is developed by DBAs for DBAs; user requests are sent to proxysql, and if it is a write request, it is sent to the master node; read requests are sent to the slave node group; in this way, read-write separation is achieved; to a certain extent, the IO pressure of the master database is reduced; [root@proxysql ~]# yum install proxysql-1.3.6-1-centos7.x86_64.rpm -y [root@proxysql ~]# rpm -ql proxysql /etc/init.d/proxysql /etc/proxysql.cnf #Main configuration file /usr/bin/proxysql /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl The configuration is as follows: Before configuring ProxySQL, you need to configure an authorized account on the master node for ProxySQL to operate on the master and slave nodes; in addition, the MySQL client tool on ProxySQL needs to be consistent with that on the master and slave nodes; proxysql.cnf configuration
[root@proxysql ~]# egrep -v '(^$|^#)' /etc/proxysql.cnf
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin" #proxysql's own administrative username and password mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
threads=4 #Number of threads, it is recommended to be consistent with the number of CPU cores max_connections=2048 #Maximum connection default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:3306;/tmp/proxysql.sock" #External interface default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
#####Configuration of master and slave nodesmysql_servers =
(
{
address = "172.16.3.175" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 1 # Set the group number status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_connections = 200 ###Define the maximum number of connections},
{
address = "172.16.3.235" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 2 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_connections=1000
},
{
address = "172.16.3.241" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 2 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_connections=1000
}
)
mysql_users:
(
{
username = "myadmin" # no default , required
password = "mypass" # default: ''
default_hostgroup = 1 # default: 0
max_connections=1000
default_schema="test"
active = 1 #Activate or not}
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
{
writer_hostgroup=1 #define write group number 1
reader_hostgroup=2 #define read group number 2
comment="test repl 1" #comment content}
)
Start the proxysql service
Testing ProxySQL Simulate the use of the database through proxysql [root@proxysql ]# mysql -h172.16.3.175 -umyadmin -pmypass mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17406 Server version: 5.7.21-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, 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. mysql> mysql > show databases; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | |mysql | | performance_schema | | study | |sys| +--------------------+ 5 rows in set (0.00 sec) ###Delete the data between 6 and 12 in the study database user_info Before deletion: mysql> select * from user_info; +-----+-------+------+--------+----------+ | nid | name | age | gender | part_nid | +-----+-------+------+--------+----------+ | 1 | san | 20 | male | 1 | | 2 | dong | 29 | male | 2 | | 4 | Ling | 28 | Male | 4 | | 5 | ling | 28 | male | 3 | | 6 | dong | 30 | male | 1 | | 7 | b | 11 | female | 1 | | 8 | c | 12 | Female | 1 | | 9 | d | 18 | Female | 4 | | 10 | e | 22 | Male | 3 | | 11 | f | 23 | male | 2 | | 12 | dongy | 22 | male | 1 | +-----+-------+------+--------+----------+ 11 rows in set (0.00 sec) After deletion: mysql> delete from user_info where nid >6 and nid <12; Query OK, 5 rows affected (0.03 sec) mysql> select * from user_info; +-----+-------+------+--------+----------+ | nid | name | age | gender | part_nid | +-----+-------+------+--------+----------+ | 1 | san | 20 | male | 1 | | 2 | dong | 29 | male | 2 | | 4 | Ling | 28 | Male | 4 | | 5 | ling | 28 | male | 3 | | 6 | dong | 30 | male | 1 | | 12 | dongy | 22 | male | 1 | +-----+-------+------+--------+----------+ 6 rows in set (0.00 sec) Checking on the master and slave nodes, you will find that the above query and modification data are correctly proxied to the backend for processing by proxysql; Master Node: Similar to the following: [root@db1 ~]# tcpdump -i enp0s3 -nn tcp port 3306 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes 18:04:34.678861 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [S], seq 3385407732, win 29200, options [mss 1460,sackOK,TS val 17576713 ecr 0,nop,wscale 7], length 0 18:04:34.678908 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [S.], seq 1579426335, ack 3385407733, win 28960, options [mss 1460,sackOK,TS val 29413673 ecr 17576713,nop,wscale 7], length 0 18:04:34.680902 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [.], ack 1, win 229, options [nop,nop,TS val 17576715 ecr 29413673], length 0 18:04:34.681264 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [P.], seq 1:83, ack 1, win 227, options [nop,nop,TS val 29413675 ecr 17576715], length 82 .... From the node: Similar to the following: [root@db2 data1]# tcpdump -i enp0s3 -nn tcp port 3306 tcpdump: verbose output suppressed, use -v or -vv for full protocol decode listening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes 18:02:57.932043 IP 172.16.3.254.42733 > 172.16.3.235.3306: Flags [S], seq 76520456, win 29200, options [mss 1460,sackOK,TS val 17479189 ecr 0,nop,wscale 7], length 0 ........... ProxySQL command line management interface: supports runtime modification [root@proxysql]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' Admin> show databases; +-----+---------+-------------------------------+ | seq | name | file | +-----+---------+-------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | +-----+---------+-------------------------------+ 4 rows in set (0.00 sec) The above stats, monitor, and main are all obtained from the configuration file database; they can be modified in a similar way to MySQL runtime without restarting; So far, our read-write separation architecture based on proxysql master-slave replication has been completed; You may also be interested in:
|
<<: Detailed explanation of this pointing problem in JavaScript function
>>: A brief introduction to protobuf and installation tutorial in Ubuntu 16.04 environment
Recommend
JS+CSS to realize dynamic clock
This article example shares the specific code of ...
Detailed explanation of how to customize the style of CSS scroll bars
This article introduces the CSS scrollbar selecto...
Solution to the problem that the virtualbox virtual machine cannot connect to the external network in NAT mode
background Two network cards are configured for t...
JavaScript Document Object Model DOM
Table of contents 1. JavaScript can change all HT...
Angular Cookie read and write operation code
Angular Cookie read and write operations, the cod...
js to write the carousel effect
This article shares the specific code of js to ac...
Detailed explanation of Javascript basics
Table of contents variable Data Types Extension P...
Maven project remote deployment && How to configure database connection using tomcat
one. Remote deployment using tomcat 1.1 Problems ...
How to migrate the data directory in mysql8.0.20
The default storage directory of mysql is /var/li...
Details of function nesting and closures in js
Table of contents 1. Scope 2. Function return val...
A very detailed tutorial on installing rocketmq under Docker Desktop
Install Docker Desktop Download address: Docker D...
Solution to input cursor misalignment in Chrome, Firefox, and IE
Detailed explanation of the misplacement of the in...
Reasons and solutions for not being able to detect array changes in Vue2
Table of contents Workaround Why can't I moni...
Install mysql5.7 on Ubuntu 18.04
Ubuntu 18.04 installs mysql 5.7 for your referenc...
js uses cookies to remember user page operations
Preface During the development process, we someti...