MySQL主从复制读写分离与高可用配置

MySQL主从复制读写分离与高可用配置

一、说明

前面我们说了mysql的安装配置(并提供一键安装脚本)，mysql语句使用以及备份恢复mysql数据；本次要介绍的是mysql的主从复制，读写分离；及高可用MHA。环境如下：master:CentOS7_x64 mysql5.721 172.16.3.175 db1slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3proxysql/MHA:CentOS7_x64 mysql5.7.21 172.16.3.235 proxysql

架构图:

说明:配置测试时为了方便关闭了防火墙头，selinux安全策略；现实中请开放防火墙策略；myslqdb的安装已经有脚本一键安装并配置好；这里就不在重复配置；只对对应的角色贴出对应的配置或安装与之相关的软件；

二、主从复制配置

一台主数据库，N从节点；从节点开启两个线程，通过Slave_IO_Running线程和主节点上有权限的账号从 主数据库节点复制binlog日志到本地，能过Slave_SQL_Running线程在本地执行binlog日志,达到主从节点内容同步；

master配置: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

创建从节点同步账号:

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节点: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         #从节点标识ID  各从节点均不一样  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

启动mysq数据库注意:两台从节点的server-id 值不一样;其他的都一样;因此从节点只展示一个配置文件;登录数据库并同步数据启动slave两台slave均要同步并启动

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_Host: 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:                              #同步过滤为空(可以只同步某个或某些库)           Replicate_Ignore_DB:                             #不同步的库            Replicate_Do_Table:                             Replicate_Ignore_Table:        Replicate_Wild_Do_Table:    Replicate_Wild_Ignore_Table:                     Last_Errno: 0               #最后同步的错误 0表示正常同步                    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 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

测试主从同步在master导入测试数据;修改数据并查看slave 中的数据是否一致;

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

slave从节点上查看

mysql> show tables; +-----------------+ | Tables_in_study | +-----------------+ | class           | | course          | | part            | | score           | | student         | | tb31            | | tb32            | | teacher         | | user_info       | +-----------------+ 9 rows in set (0.00 sec)

数据已经可以正常同步;注意主从同步只需要第一次手动启动;之后都随mysql服务自动启动;主从同步架构只方便了数据的同步,此时如果没有 第三方工具介入想做到读写分离就需要在程序中去做,难免出错;而出错了,就需要手动同步数据；这里通过proxysql来做读写分离;

三、proxysql之读写分离

以上已经完成了主从复制配置;然而这只是一个基本配置,加上一个proxysql实现mysql读写分离,proxysql类似haproxy七层代理路由功能且支持MySQL 协议的的数据库代理；是dba开发给dba使用的;用户请求发向proxysql,如果是写请求发往主节点;读请求发下从节点组中;以此实现读写分离;一定程序上减轻了主数据库的io压力;下载安装proxysql目前最新版本是1.4.7-1(由于最新版本有问题)我们这里下载使用1.3.6-1的基于CentOS7的rpm包;下载到本地并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       #主配置文件 /usr/bin/proxysql /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl

配置如下:在配置proxysql之前需要在主节点配置授权账号以作proxysql对主从节点操作;另外proxysql上的mysql客户端工具需要和主从节点上的保持一致;在主节点master上授权登录账号：

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

proxysql.cnf配置

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

启动proxysql服务

[root@proxysql ~]# service proxysql start

测试proxysql

模拟通过proxysql使用数据库

删除之前:

mysql> select * from user_info; +-----+-------+------+--------+----------+ | nid | name  | age  | gender | part_nid | +-----+-------+------+--------+----------+ |   1 | san   |   20 | 男     |        1 | |   2 | dong  |   29 | 男     |        2 | |   4 | Ling  |   28 | 男     |        4 | |   5 | ling  |   28 | 男     |        3 | |   6 | dong  |   30 | 男     |        1 | |   7 | b     |   11 | 女     |        1 | |   8 | c     |   12 | 女     |        1 | |   9 | d     |   18 | 女     |        4 | |  10 | e     |   22 | 男     |        3 | |  11 | f     |   23 | 男     |        2 | |  12 | dongy |   22 | 男     |        1 | +-----+-------+------+--------+----------+ 11 rows in set (0.00 sec)

删除之后:

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 | 男     |        1 | |   2 | dong  |   29 | 男     |        2 | |   4 | Ling  |   28 | 男     |        4 | |   5 | ling  |   28 | 男     |        3 | |   6 | dong  |   30 | 男     |        1 | |  12 | dongy |   22 | 男     |        1 | +-----+-------+------+--------+----------+ 6 rows in set (0.00 sec)

到主从节点上查看,会发现以上的查和修改数据都被proxysql正确的代理到后端处理了;以上看了并不直观;为了查看proxysql与各主从节点通讯我们在主从节点上安装tcpdump并过滤包

主节点:类似如下:

[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 ....

从节点:类似如下:

[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命令行管理接口:支持运行时修改

[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)

以上stats,monitor,main都从配置文件中获取的数据库;可以通过类似mysql运行时修改;而不需要重启;更多命令行的配置请参考github;

至此我们基于proxysql主从复制读写分离架构已经完成;双主或多主模型是无须实现读写分离，仅需要负载均衡：haproxy, nginx, lvs等;

proxysql并没有解决,当主数据岩机时的问题;此时就需要量MHA来解决 ;后续再介绍;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。