mysql主从复制读写别离与高可用装备51CTO博客 - 亚美娱乐

mysql主从复制读写别离与高可用装备51CTO博客

2019年03月06日13时07分27秒 | 作者: 曜文 | 标签: 装备,主从,读写 | 浏览: 2400

一、阐明

前面咱们说了mysql的装置装备(并供给一键装置脚本),mysql句子运用以及备份康复mysql数据;本非必须介绍的是mysql的主从仿制,读写别离;及高可用MHA;
环境如下:
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

架构图:

阐明:
装备测验时为了便利封闭了防火墙头,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

datadir="/var/lib/proxysql"
admin_variables=
{ 
  admin_credentials="admin:admin"   #proxysql自己的办理用户名暗码
  mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
}
mysql_variables=
{
  threads=4   #线程数,建议和cpu核心数共同
  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_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
}

#主从节点的装备
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   # 设置组号
  status = "ONLINE" # default: ONLINE
  weight = 1  # default: 1
  compression = 0 # default: 0
  max_connections = 200  #界说最大的衔接
  },
  {
  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  #是否激活
  }
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
  {
  writer_hostgroup=1 #界说写组号1
  reader_hostgroup=2  #界说读组号2
  comment="test repl 1" #注释内容
 }
)

发动proxysql效劳
[root@proxysql ~]# service proxysql start

测验proxysql

模仿经过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)
#删去study数据库user_info中6 -12之间的数据
删去之前:
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来处理 ;后续再介绍;

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表亚美娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章