个人中心
gpt4 book ai didi

mysql主从复制读写分离的配置方法详解

转载 作者：qq735679552 更新时间：2022-09-29 22:32:09 25 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章mysql主从复制读写分离的配置方法详解由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

1、说明 。

前面我们说了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 。

架构图

mysql主从复制读写分离的配置方法详解

说明

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

2、主从复制配置 。

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

master配置

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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

创建从节点同步账号

?
1
2
3
4
5
6
7
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节点

  。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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均要同步并启动 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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 中的数据是否一致,

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[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从节点上查看 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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来做读写分离,

3、proxysql之读写分离 。

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

?
1
2
3
4
5
6
7
[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配置 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
[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 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
模拟通过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并过滤包 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
主节点:
类似如下:
[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命令行管理接口:支持运行时修改 。

?
1
2
3
4
5
6
7
8
9
10
11
[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运行时修改;而不需要重启,

至此我们基于proxysql主从复制读写分离架构已经完成; 双主或多主模型是无须实现读写分离，仅需要负载均衡：haproxy, nginx, lvs等; proxysql并没有解决,当主数据岩机时的问题;此时就需要量MHA来解决 ;后续再介绍,

原文链接：http://blog.51cto.com/dyc2005/2094382 。

最后此篇关于mysql主从复制读写分离的配置方法详解的文章就讲到这里了,如果你想了解更多关于mysql主从复制读写分离的配置方法详解的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

25 4 0
文章推荐： MySQL 字符串截取相关函数小结
文章推荐： MySQL 截取字符串函数的sql语句
文章推荐： 导致MySQL做全表扫描的几种情况
文章推荐： MySQL多实例配置方案
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com