Mysql Master-Slave replication read-write Separation and High availability configuration

I. explanation

Earlier, we talked about the installation configuration of mysql (and provide one-click installation script), the use of mysql statements and backup to restore mysql data; this time we will introduce mysql 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

Slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2

Slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3

Proxysql/MHA:CentOS7_x64 mysql5.7.21 172.16.3.235 proxysql

Architecture diagram:

Description:

During the configuration test, the fire wall was closed for convenience, and the selinux security policy

In reality, please open the firewall policy; the installation of myslqdb has been installed and configured with one click of the script; the configuration is not repeated here; only the corresponding configuration is posted to the corresponding role or the relevant software is installed.

Second, master-slave replication configuration

One master database, N slave node; start two threads from the slave node, copy the binlog log from the master database node to the local through the Slave_IO_ running thread and the authorized account on the master node, and execute the binlog log locally through the Slave_SQL_ running thread to achieve content synchronization between the master and slave nodes.

Master configuration:

Egrep-v'(^ $| ^ #)'/ usr/local/mysql/etc/my.cnf

[mysqld] datadir=/data1/mysqldbsocket=/tmp/mysql.sockkey_buffer_size = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size = 8query_cache_limit = 1Mquery_cache_size = 64Mquery_cache_type = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id = 1log_bin = / data1/mysqldb/mysql- bin.log [mysqld _ safe] log- ErroraccountLocalUniqlUnix logsUniverse error. LogpidMusure fileholders and data1

Create an account to synchronize from the node:

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 | binlog log file and location on the master node; please make a note; it is needed for the first synchronization of the slave node.

Slave node:

Egrep-v'(^ $| ^ #)'/ usr/local/mysql/etc/my.cnf

[mysqld] datadir=/data1/mysqldbsocket=/data1/mysqldb/mysql.sockkey_buffer_size = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size = 8query_cache_limit = 1Mquery_cache_size = 64Mquery_cache_type = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id = 11 # Slave Node ID ID none of the slave nodes The same relay_log = relay-logread_only= on [mysqld _ safe] logqld _ safe] logqlqqqqqlqql is the same as USR on [mysqld _ log] log qlql.

Start the mysq database

Note: the server-id values of the two slave nodes are different; all others are the same; therefore, only one configuration file is displayed for the slave node.

Log in to the database and synchronize the data to start slave

Both slave should 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;MASTER_CONNECT_RETRY=10,MASTER_HEARTBEAT_PERIOD=2; mysql > start slave; # launch slave node () # View slave node status mysql > SHOW SLAVE STATUS * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 172.16.3.175 # Master node Master_User : repluser # Sync account Master_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 normal Slave_SQL_Running: Yes # local write thread normal Replicate_Do_DB: # synchronization filtering is empty (only one or some libraries can be synchronized) Replicate_ Ignore_DB: # out-of-sync library Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 # 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: 0Master_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 log 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 in master; modify the data and see if the data in slave is consistent

[root@db1 ~] # mysql

< Testdb.sql登录数据库[root@db1 ~]# mysql -uroot -pmysql>

Show databases;+-+ | Database | +-+ study | +-+ 5 rows in set (0.00 sec) # study test database was successfully imported mysql > use study;Database changedmysql > 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 test1 test2 table

Slave views 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)

Data can already be synchronized normally; note that master-slave synchronization only needs to be started manually for the first time, and then automatically started with the mysql service; the master-slave synchronization architecture only facilitates data synchronization, so if there is no third-party tool to intervene and want to achieve read-write separation, you need to do it in the program, which is bound to make mistakes; if there is an error, you need to manually synchronize data. Here, read-write separation is done through proxysql.

III. Separation of reading and writing of proxysql

The master-slave replication configuration has been completed above; however, this is only a basic configuration, plus a proxysql database agent that implements mysql read-write separation, proxysql is similar to haproxy7-layer proxy routing function and supports MySQL protocol; it is developed by dba for use by dba; the user request is sent to proxysql, if the write request is sent to the master node; the read request is sent to the node group; the read and write separation is realized; the io pressure on the master database is reduced to a certain extent

Download and install proxysql

The latest version is 1.4.7-1 (due to a problem with the latest version)

We download the CentOS7-based rpm package using 1.3.6-1 here; download it locally and install it with yum

[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-slave node; in addition, the mysql client tools on the proxysql need to be consistent with those on the master-slave node.

Authorize the login account on the primary node master:

Mysql > GRANT ALL ON *. * TO 'myadmin'@'172.16.3.%' identified by' mypass'

Proxysql.cnf configuration

[root@proxysql ~] # egrep-v'(^ $| ^ #)'/ etc/proxysql.cnf

Datadir= "/ var/lib/proxysql" admin_variables= {admin_credentials= "admin:admin" # proxysql your own administrative username password mysql_ifaces= "127.0.0.1 admin_variables= 6032 / tmp/proxysql_admin.sock "} mysql_variables= {threads=4 # threads, it is recommended that the number of cores in max_connections=2048 # is the same as 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.0threads=4 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 nodes mysql_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 group number status = "ONLINE" # default: ONLINE weight = 1 # default: 1 compression = 0 # default: 0 max_connections = 200 # define the maximum connection}, {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}) mysql_query_rules: () scheduler= () mysql_replication_hostgroups= ({writer_hostgroup=1 # define write group number 1 reader_hostgroup=2 # define the read group number 2 comment= "test repl 1" # comment content})

Start the proxysql service

[root@proxysql ~] # service proxysql start

Test proxysql

Simulate the use of database [root@proxysql] # mysql-h272.16.3.175-umyadmin-pmypassmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor through proxysql. Commands end with; or\ g.Your MySQL connection id is 17406Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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) # number of study deleted According to the data between 6-12 in the library 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 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) |

Looking at the master-slave node, you will find that the above query and modification data have been processed by the correct agent of proxysql to the backend.

The above is not intuitive; in order to view the communication between proxysql and master-slave nodes, we install tcpdump on master-slave nodes and filter packets

Master node: similar to the following: [root@db1] # tcpdump-I enp0s3-nn tcp port 3306tcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes18:04:34.678861 IP 172.16.3.254.42191 > 172.16.3.175.3306: Flags [S], seq 3385407732, win 29200, options [mss 1460 sackOKMagneTS val 17576713 ecr 0mnopre WScale 7] Length 018 Flags 04seq 34.678908 IP 172.16.3.175.3306 > 172.16.3.254.42191: Flags [S.], seq 1579426335, ack 3385407733, win 28960, options [mss 1460 sackOKLIT TS val 29413673 ecr 17576713 ecr 17576713 WScale 7], length 0184Fran 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 018 length 04D 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.... Slave nodes are similar to the following: [root@db2 data1] # tcpdump-I enp0s3-nn tcp port 3306tcpdump: verbose output suppressed, use-v or-vv for full protocol decodelistening on enp0s3, link-type EN10MB (Ethernet), capture size 262144 bytes18:02:57.932043 IP 172.16.3.254.42733 > 172.16.3.235.3306: Flags [S], seq 76520456, win 29200, options [mss 1460 mss 1460 val 17479189 ecr 0mnopjournal 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,main is the database obtained from the configuration file; it can be modified by similar mysql runtime without reboot; for more command line configuration, please refer to github

So far, we have completed the read-write separation architecture based on proxysql master-slave replication.

The dual-master or multi-master model does not need to achieve read-write separation, but only needs load balancing: haproxy, nginx, lvs, etc.

Proxysql did not solve the problem when the master data machine; at this time, we need MHA to solve; we will introduce it later.