MySQL master-slave copy read-write separation and highly available configuration

I. Description

Earlier we said MySQL installation configuration (and provide a one-click installation script), MySQL statement use and backup to restore MySQL data; this is mainly about MySQL master-slave replication, read and 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
Configure the test to facilitate the shutdown of the firewall header, 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, and only the corresponding roles are affixed to the relevant configuration or installation of related software;

Second, master-slave replication configuration

A master database, n slave node, two threads from a node, copy Binlog logs from the primary database node through the slave_io_running thread and the account with permissions on the master node to local, can slave_sql_running thread to execute Binlog log locally, Achieve master-slave node content synchronization;

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-error=/usr/local/mysql/logs/error.logpid-file=/data1/mysqldb/mysql.pid!includedir  /usr/local/mysql/etc/my.cnf.d

To create a sync account from a 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日志文件及位置;请记下;从节点第一次同步时需要用;

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         #从节点标识ID  各从节点均不一样 relay_log = relay-logread_only=ON[mysqld_safe]log-error=/usr/local/mysql/log/error.logpid-file=/var/run/mysql/mysql.pid!includedir /usr/local/mysql/etc/my.cnf.d

Start the MYSQ database
Note: The Server-id values from the two nodes are different; the other is the same; therefore, only one configuration file is displayed from the node;
Log in to the database and synchronize data startup slave
Both slave are 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; #启动从节点 () #查看从节点状态mysql > SHOW SLAVE status;*************************** 1. Row *************************** slave_io_state:waiting for master to send event Master_hos                  t:172.16.3.175 #主节点 Master_user:repluser #同步账号 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 #同步线程正常 Slave_sql_running:yes          #本地写线程正常 replicate_do_db: #同步过滤为空 (You can sync only one or some libraries)replicate_ignore_db: #不同步的库 replicate_do_table:replicate               _ignore_table:replicate_wild_do_table:replicate_wild_ignore_table:last_errno:0              #最后同步的错误 0 indicates normal sync 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_ser               Ver_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-36e 3-11e8-ac76-080027393fc7             Master_info_file:/data1/mysqldb/master.info sql_delay:0 sql_remaining_delay:nu LL Slave_sql_running_state:slave have 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_n Ame: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 the slave is consistent;

[[email protected] ~]# mysql < Testdb.sql登录数据库[[email protected] ~]# mysql -uroot -pmysql> show databases;+--------------------+| Database           |+--------------------+ study              |+--------------------+5 rows in set (0.00 sec)###study测试数据库导入成功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)#删除test1 test2表

Slave Viewing from a 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; Note that the master-slave synchronization requires only the first manual start, and then automatically start with the MySQL service, the master-slave synchronization architecture is only convenient for data synchronization, if there is no third-party tools involved in the process of reading and writing separation needs to be done in the program, inevitably error; Here, through the proxysql to do the reading and writing separation;

Iii. reading and writing separation of Proxysql

The above has completed the master-slave replication configuration, however this is only a basic configuration, plus a proxysql implementation of MySQL read-write separation, proxysql similar to the Haproxy seven-tier proxy routing function and support the MySQL protocol of the database agent, is the DBA developed for DBA use; The user request sends to the PROXYSQL, if is writes the request sends to the master node, reads the request to send from the node group, realizes reads and writes separates, has reduced the main database the IO pressure;
Download and install Proxysql
Currently the latest version is 1.4.7-1 (due to the latest version of the problem)
We download the CentOS7-based RPM package using 1.3.6-1; Download to local and yum install

[[email protected] ~]# yum install   proxysql-1.3.6-1-centos7.x86_64.rpm -y[[email protected] ~]# rpm -ql proxysql/etc/init.d/proxysql/etc/proxysql.cnf       #主配置文件/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 you configure Proxysql, you need to configure an authorized account on the master node for Proxysql to master and slave nodes, and the MySQL client tool on Proxysql needs to be consistent with the master and slave nodes;
to authorize login on master node :

mysql > GRANT ALL ON *.* TO ‘myadmin‘@‘172.16.3.%‘ identified by ‘mypass‘;

PROXYSQL.CNF Configuration
[Email protected] ~]# egrep-v ' (^$|^#) '/etc/proxysql.cnf

Datadir= "/var/lib/proxysql" admin_variables={admin_credentials= "Admin:admin" #proxysql自己的管理用户名密码 my Sql_ifaces= "127.0.0.1:6032;/tmp/proxysql_admin.sock"}mysql_variables={threads=4 #线程数, number of recommendations and CPU cores    Consistent max_connections=2048 #最大连接 default_query_delay=0 default_query_timeout=36000000 have_compress=true     poll_timeout=2000 interfaces= "0.0.0.0:3306;/tmp/proxysql.sock" #对外接口 default_schema= "Information_schema" stacksize=1048576 server_version= "5.5.30" connect_timeout_server=3000 monitor_username= "Monitor" Monitor_pas sword= "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 com                        Mands_stats=true sessions_sort=true connect_retries_on_failure=10}#### #主从节点的配置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_conn Ections = $ # # #定义最大的连接}, {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 s         Tatus = "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 s         Tatus = "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 #是否激活}) Mysql_quer                Y_rules: () scheduler= () mysql_replication_hostgroups= ({writer_hostgroup=1 #定义写组号1 reader_hostgroup=2 #定义读组号2 comment= "test repl 1" #注释内容})

Start the Proxysql service
[[email protected] ~]# service proxysql start

Test Proxysql

Simulation using the database via Proxysql [[email protected]]# mysql-h172.16.3.175-umyadmin-pmypassmysql: [Warning] Use a password on t He command line interface can insecure.  Welcome to the MySQL Monitor. Commands End With; or \g.your MySQL connection ID is 17406Server version:5.7.21-log mysql Community Server (GPL) Copyright (c) #, 2018, or Acle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names 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) # # #删除study数据库user_info中6-12 Data deletion before: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 |        Women |   1 | | 8 |   C | 12 |        Women |   1 | | 9 |   D | 18 |        Women |  4 | | 10 |   e | 22 |        Male |  3 | | 11 |   f | 23 |        Male |  2 | | 12 |   Dongy | 22 |        Male |  1 |+-----+-------+------+--------+----------+11 rows in Set (0.00 sec) after:mysql> delete from User_info where Nid >6 <12; nid 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)

To the master-slave node view, you will find that the above check and modify data are proxysql the correct agent to the backend processing;
The above is not intuitive; In order to view proxysql communication with each master and slave node we install tcpdump and filter packages on the master and slave nodes

Master node: Similar to the following: [[email protected] ~]# 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,sackok,ts Val 17576713 ECR 0,nop,wscale 7], length 018:04:34.678908 IP 172.16.3.175.3306 > 172.16.3.254.42191:flags [S.], seq 1579426335, ACK 338 5407733, Win 28960, options [MSS 1460,sackok,ts val 29413673 ECR 17576713,nop,wscale 7], length 018: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 018 : 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 node: Similar to the following: [[email protected] 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,sackok,ts VA L 17479189 ECR 0,nop,wscale 7], length 0 .....

Proxysql command-line management interface: supports runtime modification

 [[email protected]]#  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 are retrieved from the configuration file, can be modified by a similar MySQL runtime, and no restart is required; For more command line configuration refer to GitHub;

At this point, we have completed the Proxysql master-slave copy read/write separation architecture.
Dual-master or multi-master model is not required to implement read-write separation, only need load balancing: Haproxy, Nginx, LVS, etc.;
Proxysql does not solve the problem when the main data rock machine, the need for the amount of MHA to solve, follow-up re-introduction;

MySQL master-slave copy read-write separation and highly available configuration